go实现mysql curd

package main

import (
   "crypto/md5"
   "encoding/hex"
   "encoding/json"
   "errors"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
   "github.com/mitchellh/mapstructure"
   "strconv"
   "time"
)

type User struct {
   UserId      int    `db:"userId"`
   UserAccount string `db:"userAccount"`
   UserName    string `db:"userName"`
   Password    string `db:"password"`
   AddTime     int    `db:"addTime"`
}

var Db *sqlx.DB

func init() {
   database, err := sqlx.Open("mysql", "root:123456@tcp(127.0.0.1:3300)/test")
   if err != nil {
      fmt.Println("open mysql failed,", err)
      return
   }
   Db = database
}

func (user *User) insertData(Db *sqlx.DB) error {
   password := md5.Sum([]byte(user.Password))

   r, err := Db.Exec("insert into user_list(userName,userAccount,password,addTime)values(?,?,?,?)", user.UserName, user.UserAccount, hex.EncodeToString(password[:]), time.Now().Unix())

   if err != nil {
      return errors.New("exec failed, " + err.Error())
   }
   id, err := r.LastInsertId()
   if err != nil {
      fmt.Println("exec failed ", err)
      return errors.New("exec failed, " + err.Error())
   }
   user.UserId = int(id)

   return nil
}

func selectUserList(Db *sqlx.DB, whereMap map[string]interface{}) ([]User, error) {
   query := "select * from user_list where 1"
   for k, v := range whereMap {
      switch v.(type) {
      case int:
         it := v.(int)
         query += " and " + k + " = " + strconv.Itoa(it)
         break
      default:
         newValue, _ := json.Marshal(v)
         query += " and " + k + " = " + "\"" + string(newValue) + "\""
      }
   }
   var user []User
   err := Db.Select(&user, query)
   if err != nil {
      return user, errors.New("exec failed" + err.Error())
   }
   fmt.Println(user)
   return user, nil
}

func (user *User) update(Db *sqlx.DB, updateMap map[string]interface{}) error {
   if len(updateMap) == 0 {
      return errors.New("updateMap don't empty")
   }
   query := "update user_list set "
   i := false
   for k, v := range updateMap {
      if i {
         query += ", "
      }
      i = true
      switch v.(type) {
      case int:
         it := v.(int)
         query += k + " = " + strconv.Itoa(it)
         break
      default:
         newValue, _ := json.Marshal(v)
         query += k + " = " + string(newValue)
      }
   }
   query += "  where userId=?"
   _, err := Db.Exec(query, user.UserId)
   if err != nil {
      return errors.New("exec failed" + err.Error())
   }
   err = mapstructure.Decode(updateMap, &user)
   if err != nil {
      return errors.New("exec failed" + err.Error())
   }
   return nil
}

func (user User) delete(Db *sqlx.DB) error {
   query := "delete from user_list where userId=?"
   _, err := Db.Exec(query, user.UserId)
   if err != nil {
      return errors.New("exec failed:" + err.Error())
   }
   return nil
}

func main() {
}

func insertDemo() {
   user := User{UserAccount: "tioncico", UserName: "仙士可", Password: "123456"}
   err := user.insertData(Db)
   if err != nil {
      fmt.Println(err)
   }
   fmt.Println("insert success:", user.UserId)
}

func updateDemo() {
   user := User{UserId: 1}
   updateMap := make(map[string]interface{}, 10)
   //updateMap["userId"] = 1
   updateMap["userName"] = "tioncico333"
   updateMap["password"] = "123"
   err := user.update(Db, updateMap)
   if err != nil {
      fmt.Println(err)
   }
   fmt.Println(user)
}

func selectDemo() {
   whereMap := make(map[string]interface{}, 10)
   whereMap["userId"] = 1
   //whereMap["userName"] = "test"
   userList, err := selectUserList(Db, whereMap)
   if err != nil {
      fmt.Println(err)
   }
   //fmt.Println(userList)
   for v := range userList {
      fmt.Println(v)
   }
}

func deleteDemo() {
   user := User{UserId: 1}
   err := user.delete(Db)
   if err != nil {
      fmt.Println(err)
   }
   fmt.Println(user)
}
复制