如何优雅的解决SQL注入如何摆脱编写繁琐的gorm model GORM/GEN来了

赫连正初

2023-12-01

GORM/GEN

GORM/GEN是一个 GORM 的增强工具，在 GORM 的基础上只做增强不做改变，为简化开发、提高效率而生。初期版本迭代较快，可以直接访问这里查看最新教程GORM/GEN 。

1. 安装

1.下载

go get -u gorm.io/gen

2.导包

import "gorm.io/gen"

2. 代码生成

package main

import "gorm.io/gen"

// generate code
func main() {
    // 指定生成代码的具体目录，默认为：./dal/query
    g := gen.NewGenerator(gen.Config{OutPath: "./dal/query"})
    // 复用工程原本使用的SQL连接配置
    g.UseDB(db)
  
   // 所有需要实现查询方法的结构体
    g.ApplyBasic(model.User{}, g.GenerateModel("company"), g.GenerateModelAs("people", "Person"),)
    
   // 为指定的数据库表实现除基础方法外的相关方法
    g.ApplyInterface(func(method model.Method) {}, model.User{}, g.GenerateModel("company"))

   // 执行并生成代码
    g.Execute()
}

3. 使用Demo

3.1 创建字段

import "gorm.io/gen/field"

// create a new generic field map to `generic_a`
a := field.NewField("table_name", "generic_a")

// create a field map to `id`
i := field.NewInt("user", "id")

// create a field map to `address`
s := field.NewString("user", "address")

// create a field map to `create_time`
t := field.NewTime("user", "create_time")

3.2 CRUD API

// generated code
// generated code
// generated code
package query

import "gorm.io/gen"

// struct map to table `users` 
type user struct {
    gen.DO
    ID       field.Uint
    Name     field.String
    Age      field.Int
    Address  field.Field
    Birthday field.Time
}

// struct collection
type DB struct {
    db       *gorm.DB
    User     *user
}

3.2.1 Create

// u refer to query.user
user := model.User{Name: "Modi", Age: 18, Birthday: time.Now()}

u := query.Query.User
err := u.Create(&user) // pass pointer of data to Create

指定字段创建

u := query.Query.User
u.Select(u.Name, u.Age).Create(&user)
// INSERT INTO `users` (`name`,`age`) VALUES ("modi", 18)

忽略字段创建

u := query.Query.User
u.Omit(u.Name, u.Age).Create(&user)
// INSERT INTO `users` (`Address`, `Birthday`) VALUES ("2021-08-17 20:54:12.000", 18)

批量创建

var users = []model.User{{Name: "modi"}, {Name: "zhangqiang"}, {Name: "songyuan"}}
query.Query.User.Create(&users)

for _, user := range users {
    user.ID // 1,2,3
}

var users = []User{{Name: "modi_1"}, ...., {Name: "modi_10000"}}

// batch size 100
query.Query.User.CreateInBatches(users, 100)

3.2.2 Query

简单查询

u := query.Query.User

// Get the first record ordered by primary key
user, err := u.First()
// SELECT * FROM users ORDER BY id LIMIT 1;

// Get one record, no specified order
user, err := u.Take()
// SELECT * FROM users LIMIT 1;

// Get last record, ordered by primary key desc
user, err := u.Last()
// SELECT * FROM users ORDER BY id DESC LIMIT 1;

// check error ErrRecordNotFound
errors.Is(err, gorm.ErrRecordNotFound)

指定条件查询

u := query.Query.User

// Get first matched record
user, err := u.Where(u.Name.Eq("modi")).First()
// SELECT * FROM users WHERE name = 'modi' ORDER BY id LIMIT 1;

// Get all matched records
users, err := u.Where(u.Name.Neq("modi")).Find()
// SELECT * FROM users WHERE name <> 'modi';

// IN
users, err := u.Where(u.Name.In("modi", "zhangqiang")).Find()
// SELECT * FROM users WHERE name IN ('modi','zhangqiang');

// LIKE
users, err := u.Where(u.Name.Like("%modi%")).Find()
// SELECT * FROM users WHERE name LIKE '%modi%';

// AND
users, err := u.Where(u.Name.Eq("modi"), u.Age.Gte(17)).Find()
// SELECT * FROM users WHERE name = 'modi' AND age >= 17;

// Time
users, err := u.Where(u.Birthday.Gt(birthTime).Find()
// SELECT * FROM users WHERE birthday > '2000-01-01 00:00:00';

// BETWEEN
users, err := u.Where(u.Birthday.Between(lastWeek, today)).Find()
// SELECT * FROM users WHERE birthday BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

u := query.Query.User

user, err := u.Not(u.Name.Eq("modi")).First()
// SELECT * FROM users WHERE NOT name = "modi" ORDER BY id LIMIT 1;

// Not In
users, err := u.Not(u.Name.In("modi", "zhangqiang")).Find()
// SELECT * FROM users WHERE name NOT IN ("modi", "zhangqiang");

u := query.Query.User

users, err := u.Where(u.Role.Eq("admin")).Or(u.Role.Eq("super_admin")).Find()
// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';

复杂查询

p := query.Query.Pizza

pizzas, err := p.Where(
    p.Where(p.Pizza.Eq("pepperoni")).Where(p.Where(p.Size.Eq("small")).Or(p.Size.Eq("medium"))),
).Or(
    p.Where(p.Pizza.Eq("hawaiian")).Where(p.Size.Eq("xlarge")),
).Find()

// SELECT * FROM `pizzas` WHERE (pizza = "pepperoni" AND (size = "small" OR size = "medium")) OR (pizza = "hawaiian" AND size = "xlarge")

u := query.Query.User

users, err := u.Order(u.Age.Desc(), u.Name).Find()
// SELECT * FROM users ORDER BY age DESC, name;

// Multiple orders
users, err := u.Order(u.Age.Desc()).Order(u.Name).Find()
// SELECT * FROM users ORDER BY age DESC, name;

u := query.Query.User

// Cancel limit condition with -1
users, err := u.Limit(10).Limit(-1).Find()
// SELECT * FROM users;

users, err := u.Offset(3).Find()
// SELECT * FROM users OFFSET 3;

users, err := u.Limit(10).Offset(5).Find()
// SELECT * FROM users OFFSET 5 LIMIT 10;

u := query.Query.User

type Result struct {
    Date  time.Time
    Total int
}

var result Result

err := u.Select(u.Name, u.Age.Sum().As("total")).Where(u.Name.Like("%modi%")).Group(u.Name).Scan(&result)
// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name`

err := u.Select(u.Name, u.Age.Sum().As("total")).Group(u.Name).Having(u.Name.Eq("group")).Scan(&result)
// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"

u := query.Query.User
e := query.Query.Email
c := query.Query.CreditCard

type Result struct {
    Name  string
    Email string
}

var result Result

err := u.Select(u.Name, e.Email).LeftJoin(e, e.UserId.EqCol(u.ID)).Scan(&result)
// SELECT users.name, emails.email FROM `users` left join emails on emails.user_id = users.id

// multiple joins with parameter
users := u.Join(e, e.UserId.EqCol(u.id), e.Email.Eq("modi@example.org")).Join(c, c.UserId.EqCol(u.ID)).Where(c.Number.Eq("411111111111")).Find()

子查询

o := query.Query.Order
u := query.Query.User

orders, err := o.Where(gen.Gt(o.Amount, o.Select(u.Amount.Avg())).Find()
// SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders");

subQuery := u.Select(u.Age.Avg()).Where(u.Name.Like("name%"))
users, err := u.Select(u.Age.Avg().As("avgage")).Group(u.Name).Having(gen.Gt(u.Age.Avg(), subQuery).Find()
// SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")

u := query.Query.User
p := query.Query.Pet

users, err := gen.Table(u.Select(u.Name, u.Age).As("u")).Where(u.Age.Eq(18)).Find()
// SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18

subQuery1 := u.Select(u.Name)
subQuery2 := p.Select(p.Name)
users, err := gen.Table(subQuery1.As("u"), subQuery2.As("p")).Find()
db.Table("(?) as u, (?) as p", subQuery1, subQuery2).Find(&User{})
// SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p

3.2.3 Update

单字段更新

u := query.Query.User

// Update with conditions
u.Where(u.Activate.Is(true)).Update(u.Name, "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE active=true;

// Update with conditions
u.Where(u.Activate.Is(true)).Update(u.Age, u.Age.Add(1))
// UPDATE users SET age=age+1, updated_at='2013-11-17 21:34:10' WHERE active=true;

多字段更新

u := query.Query.User

// Update attributes with `map`
u).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

指定字段更新

u := query.Query.User

// Select with Map
// User's ID is `111`:
u.Select(u.Name).Where(u.ID.Eq(111)).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET name='hello' WHERE id=111;

u.Omit(u.Name).Where(u.ID.Eq(111)).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})
// UPDATE users SET age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;

3.2.4 Delete

e := query.Query.Email

// Email's ID is `10`
e.Where(e.ID.Eq(10)).Delete()
// DELETE from emails where id = 10;

// Delete with additional conditions
e.Where(e.ID.Eq(10), e.Name.Eq("modi")).Delete()
// DELETE from emails where id = 10 AND name = "modi";

3.3 自定义方法

`If` clause

{{if cond1}}
    // do something here
{{else if cond2}}
    // do something here
{{else}}
    // do something here
{{end}}

// select * from users where {{if name !=""}} name=@name{{end}}
methond(name string) (gen.T,error)

select * from @@table where
{{if age>60}}
    status="older"
{{else if age>30}}
    status="middle-ager"
{{else if age>18}}
    status="younger"
{{else}}
    {{if sex=="male"}}
        status="boys"
    {{else}}
        status="girls"
    {{end}}
{{end}}

`Where` clause

{{where}}
    // do something here
{{end}}

Use case in raw SQL

// select * from {{where}}id=@id{{end}}
methond(id int) error

Use case in raw SQL template

select * from @@table 
{{where}}
    {{if cond}}id=@id {{end}}
    {{if name != ""}}@@key=@value{{end}}
{{end}}

`Set` clause

{{set}}
    // sepecify update expression here
{{end}}

Use case in raw SQL

// update users {{set}}name=@name{{end}}
methond() error

Use case in raw SQL template

update @@table 
{{set}}
    {{if name!=""}} name=@name {{end}}
    {{if age>0}} age=@age {{end}}
{{end}}
where id=@id

示例

type User struct {
  ID     uint
  Name   string
  Age    int
  Gender string
  // hundreds of fields
}

type APIUser struct {
  ID   uint
  Name string
}

type Method interface{
    // select * from user
    FindSome() ([]APIUser, error)
}

apiusers, err := u.Limit(10).FindSome()
// SELECT `id`, `name` FROM `users` LIMIT 10

完整教程持续更新（开源地址）：https://github.com/go-gorm/gen 欢迎使用吐槽，跪求Star！

如何优雅的解决SQL注入如何摆脱编写繁琐的gorm model GORM/GEN来了

GORM/GEN

1. 安装

2. 代码生成

3. 使用Demo

3.1 创建字段

3.2 CRUD API

3.2.1 Create

3.2.2 Query

3.2.3 Update

3.2.4 Delete

3.3 自定义方法

`If` clause

`Where` clause

`Set` clause

示例

相关阅读

相关文章

相关问答

相关文档

如何优雅的解决SQL注入 如何摆脱编写繁琐的gorm model GORM/GEN来了

GORM/GEN

1. 安装

2. 代码生成

3. 使用Demo

3.1 创建字段

3.2 CRUD API

3.2.1 Create

3.2.2 Query

3.2.3 Update

3.2.4 Delete

3.3 自定义方法

If clause

Where clause

Set clause

示例

相关阅读

相关文章

相关问答

相关文档

如何优雅的解决SQL注入如何摆脱编写繁琐的gorm model GORM/GEN来了

`If` clause

`Where` clause

`Set` clause