Golang implementing the REST Server Framework (II)

The second article: Golang database deletion and modification operation implementation (MySQL)

Background

This article is Golang for database additions and deletions (non-query result set, query statement automatic generation more complex, the next article specifically parsing) operation specific implementation, including the automatic generation of SQL and custom SQL related functions, and refers to the insertion and update, while implementing exception handling.

Some key points

1. Using panic and recover to implement database exception handling.
2. Parsing of variable parameters of a function.
3. Bulk INSERT and update use the same function.
4. parameterization of all UPDATE SQL statements.

Code parsing

Description of core code by function module

Exception handling

Golang languages do not have exception handling, but can be achieved through panic, recover, and defer, and it is important to note how to return the appropriate information to the upper function in defer.

                                              //rs要在这定义，defer中修改rs的信息才能返回到上层调用函数func execute(sql string, values []interface{}) (rs map[string]interface{}) {    log.Println(sql, values)    rs = make(map[string]interface{})        //我原本rs是在这声明并定义的，结果返回为空    defer func() {        if r := recover(); r != nil {            rs["code"] = 500                //仔细想来，两个返回路径，一个是正常return，一个是声明中的rs返回值            rs["err"] = "Exception, " + r.(error).Error()        }    }()    ...    //这其中的代码若引发了panic，在返回上层调用函数前会执行defer    ...    return rs}

The underlying wrapper function (execute) for non-query operations

Golang database operations to return query result set and no query result set, no unified processing API, like Java, node. js, I can only separate encapsulation, here to implement execute.

func execute(sql string, values []interface{}) (rs map[string]interface{}) {    log.Println(sql, values)    ...    //异常处理与数据配置文件读取    ...    //连接数据库    dao, err := mysql.Open(dialect, dbUser+":"+dbPass+"@tcp("+dbHost+":"+dbPort+")/"+dbName+"?charset="+dbCharset)    stmt, _ := dao.Prepare(sql)        //预处理    ers, err := stmt.Exec(values...)   //提供参数并执行    if err != nil {        rs["code"] = 204                //错误处理        rs["err"] = err.Error()    } else {        id, _ := ers.LastInsertId()        //自动增长ID的最新值，若插入        affect, _ := ers.RowsAffected()    //影响的行数        rs["code"] = 200        rs["info"] = sql[0:6] + " operation success."        rs["LastInsertId"] = id        rs["RowsAffected"] = affect    }    return rs                         //成功返回}

parameter Description:

• SQL, when this function is called, either the standard SQL has been generated automatically, or the custom SQL, all the statement requirements are parameterized forms of
• Values, parameter list, corresponding to the placeholder one by one in SQL

Implementation of the new function (Insert)

A

concrete implementation of the data additions, which is a function that automatically generates standard SQL based on the JSON data submitted by the user.

func Insert(tablename string, params map[string]interface{}) map[string]interface{} {    values := make([]interface{}, 0)    sql := "INSERT INTO `" + tablename + "` (" //+strings.Join(allFields, ",")+") VALUES ("    var ks []string    var vs []string    for k, v := range params {            //注意：golang中对象的遍历，字段的排列是随机的        ks = append(ks, "`" + k + "`")    //保存所有字段        vs = append(vs, "?")              //提供相应的占位符        values = append(values, v)        //对应保存相应的值    }    //生成正常的插入语句    sql += strings.Join(ks, ",") + ") VALUES (" + strings.Join(vs, ",") + ")"    return execute(sql, values)}

Modifying the implementation of a function (Update)

The

specific implementation of the data modification operation, which is a function of automatically generating standard SQL based on the JSON data submitted by the user.

func Update(tablename string, params map[string]interface{}, id string) map[string]interface{} {    values := make([]interface{}, 0)    sql := "UPDATE `" + tablename + "` set " //+strings.Join(allFields, ",")+") VALUES ("    var ks string    index := 0    psLen := len(params)    for k, v := range params {        //遍历对象        index++        values = append(values, v)    //参数        ks += "`" + k + "` =  ?"      //修改一个key的语句        if index < psLen {            //非最后一个key，加逗号            ks += ","        }    }    values = append(values, id)      //主键ID是单独的    sql += ks + " WHERE id = ? "    return execute(sql, values)}

Remove the implementation of the function (delete)

the specific implementation of the data deletion operation.

func Delete(tablename string, id string) map[string]interface{} {    sql := "DELETE FROM " + tablename + " where id = ? "        //只支持单个ID操作，这是自动化的接口，批量操作走其它接口    values := make([]interface{}, 0)    values = append(values, id)    return execute(sql, values)}

Implementation of batch new and modified functions (Insertbatch)

Data Batch New and modify the specific implementation of the operation, two ways in the same interface implementation.

Func Insertbatch (tablename string, Els []map[string]interface{}) map[string]interface{} {values: = Make ([]interface{}    , 0) sql: = "INSERT INTO" + tablename var upstr string var Firstel map[string]interface{}//First inserted or modified object Lenels: = Len (ELS)//Because of the randomness of the Golang object traversal, we take out the first object to parse first, remove the randomness if Lenels > 0 {firstel = E Ls[0]}else {//An element is not, obviously the call parameter is not rs: = Make (map[string]interface{}) rs["        Code "] = 301 rs[" Err "] =" Params is wrong, element must not being empty. "    Return RS} var Allkey []string//Save all fields of an object, eleholder in this order when the object is accessed: = "(" Index: = 0                          Pslen: = Len (Firstel) for k, V: = range Firstel {index++ Eleholder + = "?"                        Placeholder Upstr + = k + "= values (" + K + ")"//Update operation when the field corresponds to the value if index < Pslen {  Not the last key eleholder + = "," Upstr + = ","      }else{Eleholder + = ")"} Allkey = Append (Allkey, k)//key values = AP Pend (values, v)//value}//Bulk operations The first object statement is automatically generated by SQL + = "(" +strings. Join (Allkey, ",") + ") values" + eleholder for I: = 1; i < lenels; i++ {//based on the analysis of the first object, generate all subsequent objects SQL + = "," + Eleholder for _, Key: = Range Allkey {val ues = Append (values, Els[i][key])}}//When the primary key or unique index is present, the SQL statement that makes the update operation generates SQL + = "on DUPLICATE key Update" + Up STR return execute (SQL, values)}

Bock.go (Program Entry)

Some code for testing is provided here.

Data table structure for testing

CREATE TABLE `books` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '唯一性索引',  `name` varchar(64) DEFAULT '' COMMENT '名称',  `isbn` varchar(64) DEFAULT '' COMMENT '图书ISBN',  `u_id` int(11) DEFAULT '0' COMMENT '用户ID',  `status` tinyint(4) DEFAULT '1' COMMENT '状态：0-禁；1-有效；9删除',  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  PRIMARY KEY (`id`),  UNIQUE KEY `uuid` (`id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='表';

New test

    params := make(map[string] interface{})    args := make(map[string] interface{})    session := make(map[string] interface{})    session["userid"] = "112"    args["session"] = session    params["name"] = "golang实战"    params["isbn"] = "41563qtrs5-X"    params["status"] = 1    db := &table    rs := db.Create(params, args)    fmt.Println(rs)

Modify Test

    params = make(map[string] interface{})    args = make(map[string] interface{})    args["id"] = 2    params["name"] = "golang实战，修改了"    params["status"] = 3    rs = db.Update(params, args)    fmt.Println(rs)

Delete Test

    args = make(map[string] interface{})    args["id"] = 1    rs = db.Delete(nil, args)    fmt.Println(rs)

Batch Test

    vs := make([]map[string]interface{}, 0)    params := make(map[string] interface{})    params["name"] = "golang批量11213"        //第一个对象    params["isbn"] = "4156s5"    params["status"] = 5    params["id"] = 9    vs = append(vs, params)    params = make(map[string] interface{})    params["name"] = "golang批量22af24"        //第二个对象    params["isbn"] = "xxfqwt325rqrf45"    params["status"] = 2    params["id"] = 10    vs = append(vs, params)    db := &table    rs := db.InsertBatch("books", vs)    fmt.Println(rs)

Project Address

https://github.com/zhoutk/goTools

How to use

git clone https://github.com/zhoutk/goToolscd goToolsgo getgo run bock.gogo buid bock.go./bock        

Summary

Through a variety of scenarios compared to find that the go language as a network service throughput is the best, so with the previous experience on other platforms (Node.js,java,python3), with go to achieve, expect surprises, write code I am serious.