语句准备

• 1) 使用db类型

func prepareStatement1(db * sql.DB){
        var err       error
        var stmt    * sql.Stmt
        var rows    * sql.Rows
        var val1      int      = 10
        var querySQL  string   = "select col2 from testcase1 where col1 = :1"
        //准备查询语句
        stmt, err = db.Prepare(querySQL)
        getError(err)
        defer stmt.Close()
        rows, err = stmt.Query(val1)
        getError(err)
        for rows.Next() {
                var val2 string
                rows.Scan(&val2)
                fmt.Println(val2)
        }
}

• 2) 使用conn类型

func prepareStatement2(db * sql.DB){
        var err       error
        var stmt    * sql.Stmt
        var rows    * sql.Rows
        var conn    * sql.Conn
        var val1      int      = 10
        var querySQL  string   = "select col2 from testcase1 where col1 = :1"
        ctx, cancel := context.WithTimeout(context.Background(), 55*time.Second)
        defer cancel()
        //从连接池中返回一个连接
        conn, err    = db.Conn(ctx)
        getError(err)
        //将连接返回到连接池中
        defer conn.Close()
        //准备语句
        stmt, err = conn.PrepareContext(ctx, querySQL)
        getError(err)
        defer stmt.Close()
        //给定参数查询
        rows, err = stmt.Query(val1)
        getError(err)
        defer rows.Close()
        for rows.Next() {
                var val2 string
                rows.Scan(&val2)
                fmt.Println(val2)
        }
}

• 3) 使用Tx类型

func prepareStatement3(db * sql.DB){
        var err       error
        var stmt    * sql.Stmt
        var result    sql.Result
        var tx      * sql.Tx
        var val1      int      = 10
        var val2      string   = "hello"
        var insertSQL string   = "insert into testcase1(col1, col2) values(:1, :2)"
        //从连接池中开启一个事务
        tx, err    = db.Begin()
        getError(err)
        //准备语句
        stmt, err = tx.Prepare(insertSQL)
        getError(err)
        defer stmt.Close()
        //给定参数查询
        result, err = stmt.Exec(val1, val2)
        if err != nil{
                if rollbackErr := tx.Rollback(); rollbackErr != nil {
                        log.Fatalf("unable to rollback: %b", rollbackErr)
                }
                log.Fatalln(err)
                return
        }
        rowsAffected, err := result.RowsAffected()
        getError(err)
        fmt.Println(rowsAffected)
        if err = tx.Commit(); err != nil {
                log.Fatalln(err)
        }
}