SQLX

数据库连接

func initDB()(db *sqlx.DB,err error){
   db, err = sqlx.Connect("mysql", dataSourceName)
   if err != nil{
   	log.Fatal("mysql连接出现错误，请检查dataSource是否正确",err.Error())
   	return _,err
   }
   db.SetMaxOpenConns(20)
   db.SetMaxIdleConns(10)
   return db,err
}

查询单行数据示例代码如下：

func queryRowDemo(){
    sqlstr="select * from user where id=?"
    var u user
    err := db.Get(&u,sqlStr,1)
    if err!=nil{
        fmt.Printf("get failed,err:%v\n",err)
        return
    }
    fmt.Println(user)
}

查询多行数据示例代码如下：

func queryMultiRowDemo(){
    sqlstr="select * from user where id>?"
    var users []user
    err := db.Select(&users,sqlStr,1)
    if err!=nil{
        fmt.Printf("query failed,err:%v\n",err)
        return
    }
    fmt.Println(user)
}

插入数据

//插入数据
func insertRowDemo(){
    sql="insert into user(name,age) values (?,?)"
    result,err := db.Exec(sql,"张三",10)
    if err!=nil{
        fmt.printf("insert falied,err:%v\n",err)
        return 
    }
    theID,err:=result.LastInserId()
    if err!=nil{
        fmt.Printf("get lastinsert id fialed,err:%v\n",err)
        return
    }
    fmt.Printf("insert success,the id is %d.\n",theID)
}

更新数据

func insertRowDemo(){
    sql="update user set age=? where id=?"
    result,err := db.Exec(sql,39,10)
    if err!=nil{
        fmt.printf("update falied,err:%v\n",err)
        return 
    }
    n,err:=result.RowsAffected()	//影响的行数
    if err!=nil{
        fmt.Printf("get RowsAffected fialed,err:%v\n",err)
        return
    }
    fmt.Printf("update success,the RowsAffected are %d.\n",n)
}

删除数据

func insertRowDemo(){
    sql="delete from user where id=?"
    result,err := db.Exec(sql,6)
    if err!=nil{
        fmt.printf("delete falied,err:%v\n",err)
        return 
    }
    n,err:=result.RowsAffected()	//影响的行数
    if err!=nil{
        fmt.Printf("get RowsAffected  fialed,err:%v\n",err)
        return
    }
    fmt.Printf("delete success,affected rows %d.\n",n)
}

事务操作

func transactionDemo(){
	tx,err:=db.Beginx()	//开启事务
	if err!=nil{
		if tx!=nil{
			tx.Rollback()
		}
		fmt.Printf("begin trans failed,err:%v\n",err)
		return
	}
	sql:="update user set age=40 where id=?"
	tx.MustExec(sql,2)
	sql:="update user set age=40 where id=?"
	tx.MustExec(sql,4)
	err = tx.Commit()	//提交事务
	if err!=nil{
		tx.Rollback()	//回滚
		fmt.Printf("commit failed,err:%v\n",err)
		return
	}
	fmt.Println("exec trans success!")
}

数据库	占位符语法
MySQL	?
PostgreSQL	$1,$2
SQLLite	?和$1
Oracle	:name