golang 操作mysql的CRUD

golang 操作mysql的CRUD

// test2 project main.go
package main

import (
	"container/list"
	"database/sql"
	"fmt"
	"log"
	"strconv"

	_ "github.com/go-sql-driver/mysql" //mysql驱动
)

type customer struct {
	id    string
	pname string
	price float32
	pnum  int
}

func main() {
	ct := customer{"2", "dabao", 123, 23}
	result := updateCustomer(ct)
	//b := insert()
	//query()
	//deleteCustomer("0")
	fmt.Println("mmmmm", result)
}

func getDB() (*sql.DB, error) {
	db, err := sql.Open("mysql", "root:root@tcp(192.168.126.129:8066)/mycatTest?charset-utf-8")
	if err != nil {
		log.Println(err)
		return nil, err
	}
	db.SetMaxOpenConns(30) //设置最大打开的连接数
	db.SetMaxIdleConns(1)  // 设置最大闲置连接数
	db.Ping()
	return db, nil
}

func query() {
	customers := list.New()
	db, err := getDB()
	rows, err := db.Query("select * from deal_product")
	if err != nil {
		log.Println("error-44 : ", err)
	}
	for rows.Next() {
		var id string
		var pname string
		var price float32
		var pnum int
		if err := rows.Scan(&id, &pname, &price, &pnum); err != nil {
			log.Println("error-52 : ", err)
		}
		c := customer{id, pname, price, pnum}
		customers.PushBack(c)
	}
	defer rows.Close()
	defer db.Close()
	for cs := customers.Front(); cs != nil; cs = cs.Next() {
		cu := cs.Value.(customer)
		fmt.Println("custromer : ", cu.id, cu.pname, cu.price, cu.pnum)

	}
}

func insert() (b bool) {
	db, err := getDB()
	db.Begin() // 开启事务
	stm, err := db.Prepare("insert into deal_product(id, pname, price, pnum) values(?, ?, ?, ?)")
	if err != nil {
		log.Println(err)
		return false
	}
	for i := 0; i < 10; i++ {
		stm.Exec(strconv.Itoa(i), "name"+strconv.Itoa(i), i, i)
	}
	stm.Close()
	defer db.Close()
	return true
}

func deleteCustomer(id string) (b bool) {
	db, err := getDB()
	db.Begin()
	stm, err := db.Prepare("delete from deal_product where id = ?")
	if err != nil {
		log.Println("error-86 : ", err)
		return false
	}
	result, err1 := stm.Exec(id)
	if err1 != nil {
		log.Println("error-92", err1)
		return false
	}
	fmt.Println("result: ", result)
	defer stm.Close()
	defer db.Close()
	return true
}

func updateCustomer(c customer) (b bool) {
	db, err := getDB()
	db.Begin()
	stm, err := db.Prepare("update deal_product set pname=?, price=? where id = ?")
	if err != nil {
		log.Println("error-107", err)
		return false
	}
	stm.Exec(c.pname, c.price, c.id)
	if err != nil {
		log.Println("error-112", err)
		return false
	}
	stm.Close()
	db.Close()
	return true
}