github.com/bingoohuang/gg@v0.0.0-20240325092523-45da7dee9335/pkg/sqx/README.md (about)

     1  # sqx
     2  
     3  helper to generate sql on various conditions.
     4  
     5  `go get github.com/bingoohuang/gg/pkg/sqx/...`
     6  
     7  ```go
     8  type Cond struct {
     9  	Name string `cond:"name like ?" modifier:"%v%"` // like的示例
    10  	Addr string `cond:"addr = ?"`
    11  	Code int    `cond:"code > ?" zero:"-1"` // Code == -1时，忽略本字段的条件
    12  	Nooo string `cond:"-"`                  // 忽略本字段作为条件
    13  }
    14  
    15  ret, err := sqx.CreateSQL(`select code, name, addr, email from person order by code`, Cond{
    16  	Name: "天问一号",
    17  	Addr: "火星基地",
    18  	Code: -1,
    19  })
    20  fmt.Println(fmt.Sprintf("%+v", ret), err)
    21  // &{Query:select code, name, addr, email from person where name like ? and addr = ? order by code Vars:[%天问一号% 火星基地]}
    22  
    23  ret, err = sqx.CreateSQL(`select code, name, addr, email from person order by code`, Cond{
    24  	Name: "嫦娥",
    25  	Addr: "广寒宫",
    26  	Code: 100,
    27  })
    28  fmt.Println(fmt.Sprintf("%+v", ret), err)
    29  // &{Query:select code, name, addr, email from person where name like ? and addr = ? and code > ? order by code Vars:[%嫦娥% 广寒宫 100]}
    30  
    31  ret.Append("limit ?, ?", 1, 10) // 手动附加语句
    32  fmt.Println(fmt.Sprintf("%+v", ret), err)
    33  // &{Query:select code, name, addr, email from person where name like ? and addr = ? and code > ? order by code limit ?, ? Vars:[%嫦娥% 广寒宫 100 1 10]}
    34  ```
    35  
    36  create counting sql:
    37  
    38  ```go
    39  ret, err := sqx.SQL{
    40  	Query: `select a, b, c from t where a = ? and b = ? order by a limit ?, ?`,
    41  	Vars:  []interface{}{"地球", "亚洲", 0, 100},
    42  }.CreateCount()
    43  fmt.Println(fmt.Sprintf("%+v", ret), err) 
    44  // &{Query:select count(*) from t where a = ? and b = ? Vars:[地球 亚洲]}
    45  ```
    46  
    47  do update or query to database:
    48  
    49  ```go
    50  // 创建数据库连接池
    51  db, _ := sql.Open("sqlite3", ":memory:")
    52  
    53  sqx.NewSQL("create table person(id varchar(100), age int)").Update(db)
    54  sqx.NewSQL("insert into person(id, age) values(?, ?)", "嫦娥", 1000).Update(db)
    55  sqx.NewSQL("insert into person(id, age) values(?, ?)", "悟空", 500).Update(db)
    56  
    57  m, _ := sqx.NewSQL("select id, age from person where id=?", "嫦娥").QueryAsMap(db)
    58  fmt.Println(m) // // map[age:1000 id:嫦娥]
    59  
    60  r, _ := sqx.NewSQL("select id, age from person where id=?", "嫦娥").QueryAsRow(db)
    61  fmt.Println(r) // // [嫦娥 1000]
    62  
    63  type Person struct {
    64  	ID string
    65  	Ag int `col:"AGE"`
    66  }
    67  
    68  var ps []Person
    69  sqx.NewSQL("select id, age from person where id=?", "嫦娥").QueryAsBeans(db, &ps)
    70  fmt.Printf("%+v\n", ps) // [{ID:嫦娥 Ag:1000}]
    71  
    72  var p Person
    73  sqx.NewSQL("select id, age from person where id=?", "嫦娥").QueryAsBeans(db, &p)
    74  fmt.Printf("%+v\n", p) // {ID:嫦娥 Ag:1000}
    75  
    76  age, _ := sqx.NewSQL("select age from person where id=?", "嫦娥").QueryAsNumber(db)
    77  fmt.Println(age) // 1000
    78  
    79  id, _ := sqx.NewSQL("select id from person where id=?", "嫦娥").QueryAsString(db)
    80  fmt.Println(id) // 嫦娥
    81  ```