package main

import (
	"RDSMySQL/common"
	"database/sql"
	"fmt"
	"github.com/pkg/errors"
	"log"
)

func checkError(err error) {
	if err != nil {
		log.Printf("%+v", err)
	}
}

//查询存储结构体
type animals struct {
	id   int
	age  int
	name string
}

func main() {
	db := common.GetDb()
	defer db.Close()
	//创建表
	err := createTable(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	//插入数据
	err = insertRowDemo(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	//更新数据
	err = updateRowDemo(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	//删除数据
	err = deleteRowDemo(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	//单行查询数据
	err = queryRowDemo(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
	//多行查询数据
	err = queryMultiRowDemo(db)
	if err != nil {
		log.Printf("%+v", err)
		return
	}
}

//创建表
func createTable(db *sql.DB) error {
	_, err := db.Exec("CREATE TABLE animals (id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,name VARCHAR(20) DEFAULT NULL,age NUMERIC(2) DEFAULT 0,);")
	if err != nil {
		return errors.Wrap(err, "ERR: fail to createTable")
	}
	return nil
}

// 插入数据
func insertRowDemo(db *sql.DB) error {
	sqlStr := "insert into animals(name, age) values (?,?)"
	_, err := db.Exec(sqlStr, "老虎", 3)
	if err != nil {
		return errors.Wrap(err, "ERR: fail to insertRow")
	}
	return nil
}

//更新数据
func updateRowDemo(db *sql.DB) error {
	sqlStr := "update animals set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 39, 1)
	if err != nil {
		return errors.Wrap(err, "ERR: fail to updateRow")
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		return errors.Wrap(err, "ERR: fail to get rows")
	}
	fmt.Printf("update success, affected rows:%d\n", n)
	return nil
}

// 删除数据
func deleteRowDemo(db *sql.DB) error {
	sqlStr := "delete from animals where id = ?"
	ret, err := db.Exec(sqlStr, 3)
	if err != nil {
		return errors.Wrap(err, "ERR: fail to deleteRow")
	}
	n, err := ret.RowsAffected() // 操作影响的行数
	if err != nil {
		return errors.Wrap(err, "ERR: fail to get rows")
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
	return nil
}

// 查询单条数据示例
func queryRowDemo(db *sql.DB) error {
	sqlStr := "select id, name, age from animals where id=?"
	var u animals
	// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
	err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)
	if err != nil {
		return errors.Wrap(err, "ERR: fail to queryRow")
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	return nil
}

// 查询多条数据示例
func queryMultiRowDemo(db *sql.DB) error {
	sqlStr := "select id, name, age from animals where id > ?"
	rows, err := db.Query(sqlStr, 0)
	if err != nil {
		return errors.Wrap(err, "ERR: fail to queryRow")
	}
	// 非常重要：关闭rows释放持有的数据库链接
	defer rows.Close()

	// 循环读取结果集中的数据
	for rows.Next() {
		var u animals
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			return errors.Wrap(err, "ERR: fail to scan row")
		}
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
	return nil
}