Construct query conditions
worm is a convenient and easy-to-use Go language ORM library. Worm supports three modes: Model method (holding structure field mapping), native SQL and SQLBuilder to operate the database, and Model method, native SQL and SQLBuilder can be used in a mixed manner.
Model method and SQL builder support chain APIs, and can use Where, And, Or, ID, In, Limit, GroupBy, OrderBy, Having and other functions to construct query conditions. You can also use Join, LeftJoin, and RightJoin to perform association query between database tables.
This article uses some examples to illustrate how to use worm to construct query conditions.
main function
package main
import (
"database/sql"
_ "/go-sql-driver/mysql"
log "/haming123/wego/dlog"
"/haming123/wego/worm"
)
func mysql_open(cnnstr string) (*, error) {
db, err := ("mysql", cnnstr)
if err != nil {
return nil, err
}
err = ()
if err != nil {
return nil, err
}
return db, nil
}
func main() {
//Create a data connection pool cnnstr := "user:passwd@tcp(127.0.0.1:3306)/dbname?charset=utf8&parseTime=True"
db_cnn, err := mysql_open(cnnstr)
if err != nil {
(err)
return
}
//Initialize the ORM (db_cnn)
//Show SQL statement log (true)
}illustrate:
- Download worm code
go get /haming123/wego
It is recommended to turn on the display of SQL logs in the test environment to see the SQL statements and execution time of each database operation, which is convenient and quick to locate problems.
- Database support
Currently, the databases supported by worm are: mysql, postgres, sqlite, and sqlserver. The example in this article uses the mysql database.
Database tables and data models
//Table creation statementCREATE TABLE `user` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `age` int(11) DEFAULT NULL, `passwd` varchar(32) DEFAULT NULL, `created` datetime DEFAULT NULL, PRIMARY KEY (`id`) );
The definition of the entity class corresponding to the database table user is as follows:
type User struct {
Id int64 `db:"id;autoincr"`
Name string `db:"name"`
Age int64 `db:"age"`
Passwd string `db:"passwd"`
Created `db:"created;n_update"`
}
func (ent *User) TableName() string {
return "user"
}illustrate:
- worm uses a Tag to map the database field with the name "db", followed by the name of the field, autoincr is used to indicate that the field is an autoincrement ID, and n_update is used to indicate that the field cannot be used in the update statement.
Query data through ID
If the database table existsidFor fields, you can query a data record through the ID function:
func DemoGetById() {
var user
_, err := (&user).ID(1).Get()
if err != nil {
(err)
return
}
(user)
}
//select id,name,age,passwd,created from user where id=? limit 1The SQL log after executing this function is:
[S] select id,name,age,passwd,created from user where id=1 limit 1 [S] DB: time=18.816ms
Query data through Where function
The use of the Where function is similar to the Sprintf function. The first parameter of the function is the SQL statement (where statement) template, and the subsequent parameter is the value of the template variable.
func DemoWhere() {
var users []
err := (&{}).Where("id>? and age>?", 1, 10).Find(&users)
if err != nil {
(err)
return
}
}
//The corresponding sql statement is://select id,name,age,passwd,created from user where id>? and age>?illustrate:
- worm placeholders are used uniformly. worm will automatically replace placeholders according to the database type. For example, the postgresql database replaces ? with $1, $2...
- You can use multiple variables to query in the Where function. This method is more intuitive and is similar to the writing method of SQL statements in database queries. However, when there are many query conditions, it is recommended to use And and OR functions for appropriate segmentation to prevent errors in correspondence between query variables and variable values. For example:
func DemoWhere2() {
var users []
err := (&{}).Where("id>?", 1).And("age>?", 10).Find(&users)
if err != nil {
(err)
return
}
}
//The corresponding sql statement is://select id,name,age,passwd,created from user where id>? and age>?How to write like query
For example, the query user's name contains:demoDatabase records:
func DemoWhereLike() {
var users []
err := (&{}).Where("name like ?", "%demo%").Find(&users)
if err != nil {
(err)
return
}
}
//The corresponding sql statement is://select id,name,age,passwd,created from user where name like '%demo%'XXXIf query
In some cases, we will judge based on the value of the variable to use a variable as a query condition to query the library. For example, if the user's name is not empty, the database is queryed by the user's name. The conventional writing method is as follows:
func DemoWhereIf(name string) {
var users []
var err error
if name == "" {
err = (&{}).Find(&users)
} else {
err = (&{}).Where("name=?", name).Find(&users)
}
if err != nil {
(err)
return
}
}worm provides a simpler method (providing WhereIf, AndIf, OrIf, OrIf functions) to support this query requirement:
func DemoWhereIf(name string) {
var users []
err := (&{}).WhereIf(name != "", "name=?", name).Find(&users)
if err != nil {
(err)
return
}
}illustrate:
- A bool variable is used for the first parameter of the WhereIf function. If the variable is true, a query condition will be added, otherwise the query condition will be ignored.
in, not in query
worm provides AndIn, AndNotIn, OrIn, and OrNotIn functions to support in and not in queries in SQL statements. For example:
func DemoWhereIn() {
var users []
err := (&{}).Where("").AndIn("id", 11, 12, 13, 14).Find(&users)
if err != nil {
(err)
return
}
}
//The corresponding sql statement is:select id,name,age,passwd,created from user where id in (?,?,?,?)The second parameter of XXXIn and XXXNotIn is a variable-length parameter. You can pass the value you want to query as a variable-length parameter, or you can put the query value into an array for query:
func DemoWhereIn() {
var users []
arr_id := []int64{11, 12, 13, 14}
err := (&{}).Where("").AndIn("id", arr_id).Find(&users)
if err != nil {
(err)
return
}
}illustrate:
- If the array method is used, then a variable-length parameter can be used as a parameter, and the parameter is an array type.
Nested query statements
WORM supports nested query statements, for example, the query is:age>10 and (name='demo1' or name='demo2'), the method of using worm is as follows:
func DemoWhereExp() {
var users []
sqlw := ("name=?", "demo1").Or("name=?", "demo2")
err := (&{}).Where("age>?", 10).AndExp(sqlw).Find(&users)
if err != nil {
(err)
return
}
}
//The corresponding sql statement is://select id,name,age,passwd,created from user where age>? and (name=? or name=?)Limit and Offset
Limit and Offset can be used in MySQL statements to query the database. This kind of query is usually used in paging queries of WEB.
worm also supports mysql's Limit and Offset statements:
func DemoQueryPage(plen int64, pcur int64) {
var users []
err := (&{}).Where("age>?", 10).Limit(plen).Offset(plen * pcur).Find(&users)
if err != nil {
(err)
return
}
}
//The corresponding sql statement is://select id,name,age,passwd,created from user where age>? limit ?, ?orderby query
The OrderBy function corresponds to the order by statement in the SQL statement:
func DemoQueryOrderBy(orderby string) {
var users []
err := (&{}).Where("age>?", 10).OrderBy(orderby).Find(&users)
if err != nil {
(err)
return
}
}
//The corresponding sql statement is://select id,name,age,passwd,created from user where age>? order by created descThe above is the detailed explanation of the Go language ORM framework structure query conditions example. For more information about Go ORM structure query conditions, please pay attention to my other related articles!