Go language ORM framework: the use of shortcut query functions

Quick query function

In most cases, you are using the Model method provided by the worm support (supporting structure field mapping) to access the database, but sometimes using the Model method is obviously cumbersome. For example, you may only need to query the value of one field of a record, in which case it is cumbersome to use the Model method. Worm provides some quick query functions to meet this demand, such as DbTable's GetString() function, which is used to query a string type field of a record. These shortcut query functions can be used in native SQL and SQLBuilder modes. The usage of these shortcut functions is basically the same in the above two modes. This article uses the SQLBuilder mode to illustrate the use of these shortcut functions.

main function

 package main
import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    log "github.com/haming123/wego/dlog"
    "github.com/haming123/wego/worm"
)
func mysql_open(cnnstr string) (*sql.DB, error) {
    db, err := sql.Open("mysql", cnnstr)
    if err != nil {
        return nil, err
    }
    err = db.Ping()
    if err != nil {
        return nil, err
    }
    return db, nil
}
func main() {
    //创建数据连接池
    cnnstr := "user:passwd@tcp(127.0.0.1:3306)/dbname?charset=utf8&parseTime=True"
    db_cnn, err := mysql_open(cnnstr)
    if err != nil {
        log.Error(err)
        return
    }
    //初始化ORM
    worm.InitMysql(db_cnn)
    //显示SQL语句log
    worm.ShowSqlLog(true)
}

illustrate:

• worm code download
  go get github.com/haming123/wego
• worm.ShowSqlLog
  worm.ShowSqlLog is used to control the display of sql log. It is recommended to turn on the display switch of sql log in the test environment, so that you can see the sql statement and execution time of each database operation, which is convenient to quickly locate the problem.
• Database Support Currently, the databases supported by worm are: mysql, postgres, sqlite, and sqlserver. In this example, the mysql database is used.

Database Table

 //建表语句
CREATE 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`)
);

Query whether a record exists

In some cases, you don't need to get the data of a record, but only need to query whether a record exists in the database. In this case, you can use the Exist() function:

 func SQLBuilderExist() {
    has, err := worm.Table("user").ID(1).Exist()
    if err != nil {
        log.Error(err)
        return
    }
    //select * from user where id=? limit 1
}

Query a specified field of a record

In some cases, you may only need to obtain certain fields of a record. In this case, it is not necessary to define a struct, and then obtain the data of the fields through the Model method. You can use DbTable's Get function:

 func SQLBuilderGetValue() {
    name := ""; age := 0
    _, err := worm.Table("user").Select("name,age").ID(1).Get(&name, &age)
    if err != nil {
        log.Error(err)
        return
    }
    //select name,age from user where id=? limit 1
}

illustrate:

• The parameters of the Get function are variable-length parameters, and the number and type of parameters must correspond one-to-one with the fields in Select().
• The first return value of the Get function is a bool type that indicates whether the record exists.

query a record for a string field

The GetString() function is used to query a string field of a record

 func SQLBuilderGetValue() {
    val, err := worm.Table("user").Select("name").ID(1).GetString()
    if err != nil {
        log.Error(err)
        return
    }
    //select name from user where id=? limit 1
}

illustrate:

• There must be only one field in Select(), and the field's type is string.
• The first return value of GetString() is of type sql.NullString, and the Valid variable is used to indicate whether it is a valid value.

Query an integer field of a record

The GetInt() function is used to query an integer field of a record

 func SQLBuilderGetInt() {
    val, err := worm.Table("user").Select("age").ID(1).GetInt()
    if err != nil {
        log.Error(err)
        return
    }
    //select age from user where id=? limit 1
}

illustrate:

• There must be only one field in Select(), and the field type is integer.
• The first return value of GetInt() is sql.NullInt64, and the Valid variable is used to indicate whether it is a valid value.

Query a float field of a record

The GetFloat() function is used to query a float field of a record

 func SQLBuilderGetFloat() {
    val, err := worm.Table("user").Select("weight").ID(1).GetFloat()
    if err != nil {
        log.Error(err)
        return
    }
    //select weight from user where id=? limit 1
}

illustrate:

• There must be only one field in Select(), and the field's type is float.
• The first return value of GetFloat() is sql.NullFloat64, and the Valid variable is used to indicate whether it is a valid value.

query a record

The GetFloat() function is used to query a float field of a record

 func SQLBuilderGetFloat() {
    val, err := worm.Table("user").Select("weight").ID(1).GetFloat()
    if err != nil {
        log.Error(err)
        return
    }
    //select weight from user where id=? limit 1
}

illustrate:

• There must be only one field in Select(), and the field's type is float.
• The first return value of GetFloat() is sql.NullFloat64, and the Valid variable is used to indicate whether it is a valid value.

Query the number of records that meet the condition

The Count() function is used to query the number of records that meet the conditions

 func SQLBuilderCount() {
    val, err := worm.Table("user").Where("id>?", 0).Count()
    if err != nil {
        log.Error(err)
        return
    }
    //select count(1) from user where id>?
}

illustrate:

• Count() If there is no parameter, the count of count(1) is used. You can also specify the field name to count if necessary:

   func SQLBuilderCount() {
    val, err := worm.Table("user").Where("id>?", 0).Count("name")
    if err != nil {
        log.Error(err)
        return
    }
    //select count(name) from user where id>?
  }

count_distinct query

The DistinctCount() function is used to count the number of sorted fields that meet the conditions:

 func SQLBuilderDistinctCount() {
    val, err := worm.Table("user").Where("id>?", 0).DistinctCount("name")
    if err != nil {
        log.Error(err)
        return
    }
    //select count(distinct name) from user where id>?
}

query string field to array

The FindString() function is used to query records that meet the condition and add the value of the string field to the string array:

 func SQLBuilderFindString() {
    val, err := worm.Table("user").Select("name").Where("id>?", 0).FindString()
    if err != nil {
        log.Error(err)
        return
    }
    // select name from user where id>?
}

illustrate:

• There must be only one field in Select(), and the field's type is string.

query integer field to array

The FindInt() function is used to query the records that satisfy the condition and add the value of the integer field to the integer array:

 func SQLBuilderFindInt() {
    val, err := worm.Table("user").Select("age").Where("id>?", 0).FindInt()
    if err != nil {
        log.Error(err)
        return
    }
    log.Debug(val)
    // select age from user where id>?
}

illustrate:

• There must be only one field in Select(), and the field type is integer.

query float field to array

The FindInt() function is used to query records that satisfy the condition and add the value of the float field to the float array:

 func SQLBuilderFindFloat() {
    val, err := worm.Table("user").Select("weight").Where("id>?", 0).FindFloat()
    if err != nil {
        log.Error(err)
        return
    }
    // select weight from user where id>?
}

illustrate:

• There must be only one field in Select(), and the field's type is float.