golang-操作sqlite3增删改查

需要下载sqlite3环境：

go get github.com/mattn/go-sqlite3

"github.com/mattn/go-sqlite3"

go操作sqlite3的示例代码：

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/mattn/go-sqlite3"
	"log"
)

const (
	dbDriverName = "sqlite3"
	dbname       = "./data.db"
)

type user struct {
	Username string
	Age      int
	Job      string
	Hobby    string
}

//封装一个处理error的函数，处理程序中的 err，
//有err的地方直接调用这个函数就可以了
func checkErr(e error) bool {
	if e != nil {
		log.Fatal(e)
		return true
	}
	return false
}

//创建数据表的函数
func createTable(db *sql.DB) error {
	sql := `create table if not exists "users" (
		"id" integer primary key autoincrement,
		"username" text not null,
		"age" integer not null,
		"job" text,
		"hobby" text
	)`
	_, err := db.Exec(sql)
	return err
}

//########插入数据#########
func insertData(db *sql.DB, u user) error {
	sql := `insert into users (username, age, job, hobby) values(?,?,?,?)`
	stmt, err := db.Prepare(sql)
	if err != nil {
		return err
	}
	_, err = stmt.Exec(u.Username, u.Age, u.Job, u.Hobby)
	return err
}

//#########查询数据并且返回切片列表########

//查询所有数据
func queryDataAll(db *sql.DB) (listusers []user, e error) {
	sql := `select * from users`
	stmt, err := db.Prepare(sql)
	if err != nil {
		return nil, err
	}
	rows, err := stmt.Query()
	if err != nil {
		return nil, err
	}
	var result = make([]user, 0)
	for rows.Next() {
		var username, job, hobby string
		var age, id int
		rows.Scan(&id, &username, &age, &job, &hobby)
		result = append(result, user{username, age, job, hobby})
	}
	return result, nil
}

//查询带有条件的数据函数
func queryData(db *sql.DB, name string) (listusers []user, e error) {
	//sql := `select * from users `
	sql := `select * from users where username=?`
	stmt, err := db.Prepare(sql)
	if err != nil {
		return nil, err
	}
	rows, err := stmt.Query(name)
	if err != nil {
		return nil, err
	}
	var result = make([]user, 0)
	for rows.Next() {
		var username, job, hobby string
		var age, id int
		rows.Scan(&id, &username, &age, &job, &hobby)
		result = append(result, user{username, age, job, hobby})
	}
	return result, nil
}

//########删除数据#########

func delByID(db *sql.DB, id int) (bool, error) {
	sql := `delete from users where id=?`
	stmt, err := db.Prepare(sql)
	if err != nil {
		return false, err
	}
	res, err := stmt.Exec(id)
	if err != nil {
		return false, err
	}
	_, err = res.RowsAffected()
	if err != nil {
		return false, err
	}
	return true, nil
}

func editdata(db *sql.DB, u user, editid int) (bool, error) {
	//sql := `update users set username=? age=? job=? hobby=? where id=?`
	sql := `update users set username=? where id=?`
	stmt, err := db.Prepare(sql)
	if err != nil {
		return false, err
	}
	res, err := stmt.Exec(u.Username, editid)
	if err != nil {
		return false, err
	}
	_, err = res.RowsAffected()
	if err != nil {
		return false, err
	}
	return true, nil
}

func editdata_bak(db *sql.DB, u user, editid int) (bool, error) {
	//sql := `update users set username=? age=? job=? hobby=? where id=?`
	sql := `update users set username=? age=? job=? hobby=? where id=?`
	stmt, err := db.Prepare(sql)
	if err != nil {
		return false, err
	}
	res, err := stmt.Exec(u.Username, u.Age, u.Job, u.Hobby, editid)
	if err != nil {
		return false, err
	}
	_, err = res.RowsAffected()
	if err != nil {
		return false, err
	}
	return true, nil
}

//#################
//######主函数###########

func main() {

	//初始sqlite3数据库文件
	db, err := sql.Open(dbDriverName, dbname)
	if checkErr(err) {
		return
	}
	//#################
	调用创建数据表的语句
	//err = createTable(db)
	//if checkErr(err) {
	//	return
	//}

	#######插入数据的方式##########
	//user1 := user{
	//	Username: "tom",
	//	Age:      50,
	//	Job:      "manager",
	//	Hobby:    "跑步，跳绳，编程",
	//}

	插入数据的方式：
	//err = insertData(db, user{"jerry", 51, "医药开发", "运动，学习"})
	//err = insertData(db, user1)
	//if checkErr(err) {
	//	return
	//}
	//#################
	//查询所有学生数据
	resall, err := queryDataAll(db)

	if checkErr(err) {
		return
	}

	fmt.Println(len(resall)) //结果中几个学生信息的长度
	fmt.Println("======查询全部数据的结果===========")
	for _, val := range resall {
		fmt.Println(val)
	}

	/*
			查询结果：
		======查询全部数据的结果===========
			{tom 50 manager 跑步，跳绳，编程}
			{jerry 51 医药开发 运动，学习}
			{tom 50 manager 跑步，跳绳，编程}
			=======过滤条件的查询=========
	*/

	fmt.Println("=======过滤条件的查询=========")

	//查询带有过滤条件的数据
	resnames, err := queryData(db, "tom")
	if checkErr(err) {
		return
	}

	fmt.Println(len(resnames)) //结果中几个学生信息的长度
	fmt.Println("=======过滤名字的结果：==========")
	for _, val := range resnames {
		fmt.Println(val)
	}

	/*
		=======过滤名字的结果：==========
		{tom 50 manager 跑步，跳绳，编程}
		{tom 50 manager 跑步，跳绳，编程}

	*/

	//#####删除指定的id的编号############
	//r, err := delByID(db, 1)
	//if checkErr(err) {
	//	return
	//}
	//if r {
	//	fmt.Println("删除数据成功！")
	//	//fmt.Println("delete row success")
	//}

	//########更新数据#########
	//更新数据内容信息
	//创建一个更新对象
	useredit := user{
		Username: "tomupdate",
		Age:      49,
		Job:      "manager--pm",
		Hobby:    "跑步，跳绳，编程",
	}

	b, err := editdata(db, useredit, 2)
	if checkErr(err) {
		return
	}
	if b {
		fmt.Println("更新数据成功")
	}

	//#################

	//#################

}