记录mysql.go

//mysql.go
package mysql

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
)


type Operater interface {

	SelectDB(t string) *sql.Rows
	InsertDB(t string) bool
	DeleteDB(t string) bool
	UpdateDB(t string) bool

}

func openDB() (*sql.DB, error) {
	db, err := sql.Open("mysql",dsn())
	if err != nil{
		log.Printf("open database err\n")
		return nil, err
	}else {
		log.Print("open database success\n")
		return db,err
	}

}

/*
	InsertDB：插入数据
	参数：sql Selectdata		map[string]string 插入字段名和值 m["name"] = "zywj"
		 t   sting			字符串，数据表名
	返回值：true|false		成功/失败
*/
func (sql *Selectdata)InsertDB(t string) bool {
	db,_ := openDB()
	k, v := paramsinsert(t,sql)
	res, err := db.Exec(k + v)
	if err != nil{
		db.Close()
		log.Println("insert error")
		return false
	}else {
		_, err := res.RowsAffected()
		if err != nil{
			db.Close()
			return false
		}else {
			log.Println("insert success")
			return true
		}
	}

}

/*
	DeleteDB：删除数据
	参数：sql Selectdata		map[string]string 删除条件字段名和值 m["name"] = "zywj"
		 t   sting			字符串，数据表名
	返回值：true|false		成功/失败
*/
func (sql *Selectdata)DeleteDB(t string) bool {
	fmt.Println(paramdelete(t,sql))
	db, _ := openDB()
	res, err := db.Exec(paramdelete(t,sql))
	if err != nil{
		log.Println("delete error")
		db.Close()
		return false
	}else {
		 _,err := res.RowsAffected()
		if err != nil{
			db.Close()
			return false
		}
		 log.Println("delete success")
	}
	return true
}

/*
	UpdateDB：更新数据
	参数：sql Selectdata		 []string{"name","123","id","345"} 更新字段名和字段值，名在前，值在后，map[string]string 更新条件字段名和值 m["name"] = "zywj"
		 t   sting			字符串，数据表名
	返回值：true|false		成功/失败
*/
func (sql *Selectdata)UpdateDB(t string) bool {

	db,_ := openDB()
	fmt.Println(paramsupdate(t,sql))
	res, err := db.Exec(paramsupdate(t,sql))
	if err != nil{
		log.Println("update false, close db")
		 db.Close()
		return false
	}else {
		_,err := res.RowsAffected()
		if err != nil{
			log.Println("update error")
			db.Close()
			return false
		}
		log.Println("update success")
	}
	return true
}

/*
	SelectDB：查询数据
	参数：sql Selectdata		[]string{"name","id"} 查询字段名（建议写字段名，不要用*），map[string]string 查询条件字段名和值 m["name"] = "zywj"
		 t   sting			字符串，数据表名
	返回值：true|false		成功/失败
*/
func (sql *Selectdata)SelectDB(t string) *sql.Rows {
	db,_ := openDB()

	rows, err := db.Query(paramsselect(sql,t))
	if err != nil{
		log.Print("select err")
		rows.Close()
		return nil
	}
	log.Println("select success")
	return rows

}

func paramdelete(t string,sql *Selectdata) (s string) {
	s = "DELETE FROM " + t + " WHERE "

	if i := 0; len(sql.Term) > 0 {
		for k, v := range sql.Term {
			if i == len(sql.Term)-1 {
				s = s + k + " = '" + v + "'"
			} else {
				s = s + k + " = '" + v + "' AND "
			}
			i++
		}
	}else{
		s = s + "1"
	}
	return s
}

//查询参数解析
func paramsselect(sql *Selectdata,t string) string {

	sel := "SELECT "
	for i := 0; i < len(sql.Display); i++{
		if i == len(sql.Display) - 1{
			sel = sel + sql.Display[i] + " FROM " + t
		}else {
			sel = sel + sql.Display[i] + ","
		}
	}

	sel2 :=" WHERE "
	if i := 0; len(sql.Term) > 0 {
		for k, v := range sql.Term {
			if i == len(sql.Term)-1 {
				sel2 = sel2 + k + " = '" + v + "'"
			} else {
				sel2 = sel2 + k + " = '" + v + "' AND "
			}
			i++
		}
	}else{
		sel2 = sel2 + "1"
	}
	return sel + sel2
}

//插入参数解析
func paramsinsert(t string,sql *Selectdata) (sel string ,value string) {
	sel = "INSERT INTO " + t + "( "
	value = " VALUES( '"
	if i := len(sql.Term); i>0 {
		for k, v := range sql.Term {
			if i == 1 {
				sel = sel + k + " )"
				value = value + v + "' )"
			} else {
				sel = sel + k + ", "
				value = value + v + "', '"
			}
			i--

		}
		return sel, value
	}else {
		return "",""
	}
}

func paramsupdate(t string,sql *Selectdata) (s string)  {
	s = "UPDATE " + t + " SET "
	w := "' WHERE "
	if i := len(sql.Display); i > 0{
		for k,v := range sql.Display{
			if k%2==0{
				s = s + v + " = '"
			}else {
				if k == i - 1{
					s = s + v
				}else {
					s = s + v + "', "
				}
			}
		}
	}else {
		return ""
	}
	if i := len(sql.Term); i > 0{
		for k, v := range sql.Term{
			if i == 1 {
				w = w + k + " = '" + v + "'"
			}else {
				w = w + k + " = '" + v + "', "
			}
			i--
		}
		s = s + w
		return s
	}
	return ""
}

//config
package mysql

const (
	dataName = "xxxx"
	userName = "xxxx"
	password = "xxxxxxxx"
	address = "127.0.0.1"
	port = "3306"
	charset = "utf8"
	protocol = "tcp"
)


type Selectdata struct {
	Display []string
	Term map[string]string

}




func dsn() string {
	return userName + ":" + password + "@" + protocol + "(" + address + ":" + port + ")" + "/" + dataName + "?charset=" + charset
}