Golang database-sql.Result类(方法)实例源码

func NewOrUpdateUser(db *sql.DB, user *User) error {
	var err error
	var res sql.Result
	if user.Id == "" {
		res, err = db.Exec("INSERT INTO users(firstName, lastName) VALUES(?,?)", user.FirstName, user.LastName)
	} else {
		res, err = db.Exec("UPDATE users SET firstName=?, lastName=? WHERE id=?", user.FirstName, user.LastName, user.Id)
	}
	if err != nil {
		return err
	}
	lastId, err := res.LastInsertId()
	if err != nil {
		return err
	}
	rowCnt, err := res.RowsAffected()
	if err != nil {
		return err
	}
	if rowCnt != 1 {
		return errors.New(fmt.Sprintf("Row count (%s) and/or lastId (%s) are wrong.", lastId, rowCnt))
	}

	return nil
}

// Insert 向指定table插入数据
func (this *builder) Insert(table string, model interface{}) int {
	query := "insert into `" + table + "`"
	value := reflect.ValueOf(model).Elem()
	data := make(map[string]interface{})
	mapStructToMap(value, data)
	keys := " ("
	values := " ("
	params := make([]interface{}, 0, 0)
	for k, v := range data {
		keys += "`" + k + "`,"
		values += "?,"
		params = append(params, v)
	}
	query += keys[:len(keys)-1] + ") values"
	query += values[:len(values)-1] + ")"
	var result sql.Result
	var err error
	this.reset()
	result, err = this.db.Exec(query, params...)
	if err != nil {
		return -1
	}
	var id int64
	id, err = result.LastInsertId()
	if err != nil {
		return -1
	}
	return int(id)
}

func (database *Database) CreateTables(tableConf *TableConf) {
	db, err := sql.Open(database.Driver, database.dataSource)
	if err != nil {
		panic(err)
	}
	var result sql.Result

	var auto_increment_syntax = "INT AUTO INCREMENT "
	if database.IsPostgres() {
		auto_increment_syntax = "SERIAL "
	}
	fmt.Printf("creating table '%s'... \n", tableConf.Name)
	query := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (id %s PRIMARY KEY, %s varchar(255), %s varchar(255), created_at timestamp DEFAULT CURRENT_TIMESTAMP, updated_at timestamp DEFAULT CURRENT_TIMESTAMP)", tableConf.Name, auto_increment_syntax, tableConf.Hash, tableConf.Url)
	result, err = db.Exec(query)
	if err != nil {
		panic(err)
	}
	result.RowsAffected()

	fmt.Printf("creating table 'goahead_statistics'... \n")
	query = fmt.Sprintf("CREATE TABLE IF NOT EXISTS goahead_statistics (id %s PRIMARY KEY, created_at timestamp DEFAULT CURRENT_TIMESTAMP)", auto_increment_syntax)
	result, err = db.Exec(query)
	if err != nil {
		panic(err)
	}
	result.RowsAffected()
}

//Update ...
func (vs VictualService) Update(victual *Victual) *Victual {
	db, err := sql.Open("postgres", "host=localhost port=5432 user=pober dbname=pober sslmode=disable")
	checkErr(err)
	defer db.Close()

	var statement *sql.Stmt
	statement, err = db.Prepare("UPDATE dc.victual SET description = $1, brand = $2, source = $3, energyCal = $4, energyJoule = $5, fat = $6, saturatedFat = $7, carbohydrates = $8, sugars = $9, protein = $10, salt = $11, fiber = $12, barCode = $13, weightVolumeRatio = $14 WHERE ID = $1")
	checkErr(err)

	var result sql.Result
	result, err = statement.Exec(victual.ID,
		victual.Description,
		victual.Brand,
		victual.Source,
		victual.EnergyCal,
		victual.EnergyJoule,
		victual.Fat,
		victual.SaturatedFat,
		victual.Carbohydrates,
		victual.Sugars,
		victual.Protein,
		victual.Salt,
		victual.Fiber,
		victual.BarCode,
		victual.WeightVolumeRatio)
	checkErr(err)
	var rowsAffected int64
	rowsAffected, err = result.RowsAffected()
	checkErr(err)
	statement.Close()
	if rowsAffected == 1 {
		return victual
	}
	return nil
}

func (this *Config) Insert() error {
	this.Recordtime = getNow()
	var (
		err error
		id  int64
		res sql.Result
	)
	if this.Cat != nil {
		tran := this.Cat.NewTransaction(DBTITLE, "Config.Insert")
		defer func() {
			if err != nil {
				tran.SetStatus(err)
			} else {
				tran.SetStatus("0")
			}
			tran.Complete()
		}()
	}
	o := orm.NewOrm()
	res, err = o.Raw("INSERT INTO config(channelCode,`key`,value,recordTime)VALUES(?,?,?,?)", this.ChannelCode, this.Key, this.Value, this.Recordtime).Exec()
	if err != nil {
		return err
	}
	id, err = res.LastInsertId()
	if err != nil {
		return err
	}
	this.Id = id
	return nil
}

func check_result(result sql.Result) {
	rows, err := result.RowsAffected()
	check_error(err)
	if rows == 0 {
		fmt.Printf("No rows affected by query")
	}
}

func fieldMetricTagPut(r *http.Request, h http.Header, b *bytes.Buffer) *weft.Result {
	v := r.URL.Query()

	var err error
	var result sql.Result

	if result, err = db.Exec(`INSERT INTO field.metric_tag(devicePK, typePK, tagPK)
				SELECT devicePK, typePK, tagPK
				FROM field.device, field.type, mtr.tag
				WHERE deviceID = $1
				AND typeID = $2
				AND tag = $3`,
		v.Get("deviceID"), v.Get("typeID"), v.Get("tag")); err != nil {
		if err, ok := err.(*pq.Error); ok && err.Code == errorUniqueViolation {
			// ignore unique constraint errors
			return &weft.StatusOK
		} else {
			return weft.InternalServerError(err)
		}
	}

	var i int64
	if i, err = result.RowsAffected(); err != nil {
		return weft.InternalServerError(err)
	}
	if i != 1 {
		return weft.BadRequest("Didn't create row, check your query parameters exist")
	}

	return &weft.StatusOK
}

// ChangeUserPassword ...
func ChangeUserPassword(u models.User, oldPassword ...string) (err error) {
	if len(oldPassword) > 1 {
		return errors.New("Wrong numbers of params.")
	}

	o := GetOrmer()

	var r sql.Result
	if len(oldPassword) == 0 {
		//In some cases, it may no need to check old password, just as Linux change password policies.
		r, err = o.Raw(`update user set password=?, salt=? where user_id=?`, utils.Encrypt(u.Password, u.Salt), u.Salt, u.UserID).Exec()
	} else {
		r, err = o.Raw(`update user set password=?, salt=? where user_id=? and password = ?`, utils.Encrypt(u.Password, u.Salt), u.Salt, u.UserID, utils.Encrypt(oldPassword[0], u.Salt)).Exec()
	}

	if err != nil {
		return err
	}
	c, err := r.RowsAffected()
	if err != nil {
		return err
	}
	if c == 0 {
		return errors.New("No record has been modified, change password failed.")
	}

	return nil
}

func (m *Mysql) Exec(query string, param ...interface{}) (rowsAffected int64, lastInsertId int64) {
	if !m.IsOpen() {
		panic(errors.New("[mysql555]It is not connected to the host"))
	}

	var res sql.Result = nil
	var err error = nil

	if m.IsTransaction() {
		res, err = m.tx.Exec(query, param...)
	} else {
		res, err = m.db.Exec(query, param...)
	}

	if err != nil {
		message := err.Error()
		message = message + "\n" + "[ SQL ] " + query + "\n" + fmt.Sprintf("[ PRM ] %#v\n", param)
		panic(errors.New(message))
	}

	lastInsertId, err = res.LastInsertId()
	if err != nil {
		panic(err)
	}
	rowsAffected, err = res.RowsAffected()
	if err != nil {
		panic(err)
	}

	return
}

// task.ID will be ignored.
func CreateTask(task icarus.TaskData) (ID int, err error) {
	InitStorage()
	var res sql.Result
	res, err = db.Exec("INSERT INTO task (handle) VALUES (?)", task.Handle)
	if err != nil {
		log.Warnf("storage.CreateTask(): Failed to insert task %v: %s.", task, err.Error())
		return
	}
	newID, err := res.LastInsertId()
	if err != nil {
		log.Warnf("storage.CreateTask(): Failed to get ID for task %v: %s.", task, err.Error())
	}
	ID = int(newID)

	_, err = db.Exec("INSERT INTO user (userid, password, task_id) VALUES (?, ?, ?)", task.User.UserID, task.User.Password, ID)
	if err != nil {
		log.Warnf("storage.CreateTask(): Failed to insert user for task %v: %s.", task, err.Error())
		return
	}

	for _, c := range task.Courses {
		_, err = db.Exec("INSERT INTO course (name, desc, token, task_id) VALUES (?, ?, ?, ?)", c.Name, c.Desc, c.Token, ID)
		if err != nil {
			log.Warnf("storage.CreateTask(): Failed to insert course %v for task %v: %s.", c, task, err.Error())
			return
		}
	}

	return
}

func (p *TxScope) getAffectedRows(r sql.Result) int64 {
	if count, err := r.RowsAffected(); err != nil {
		panic(err)
	} else {
		return count
	}
}

func (a *Account) Save() error {
	db, err := connect()
	if err != nil {
		return err
	}
	defer db.Close()

	stmt, err := db.Prepare("insert or replace into T_ACCOUNTS (ID, NAME, EMAIL, PASSWORD, SALT, ROLE, LAST_AUTH) values (?,?,?,?,?,?,?)")
	if err != nil {
		return err
	}
	defer stmt.Close()

	var result sql.Result
	if a.Id < 1 {
		result, err = stmt.Exec(nil, a.Name, a.Email, a.Password, a.Salt, a.Role, a.LastAuth)
	} else {
		result, err = stmt.Exec(a.Id, a.Name, a.Email, a.Password, a.Salt, a.Role, a.LastAuth)
	}
	if err != nil {
		return err
	}

	id, err := result.LastInsertId()
	if err != nil {
		return err
	}
	a.Id = int(id)

	return nil
}

func (self *MysqlDriver) Create(container string, query driver.Query) (id interface{}, requestError error) {
	fmt.Println("mysql driver create:", container, query)
	// Create mysql insert string from given query
	var (
		queryFields []string
		queryValues []string
	)
	for k, v := range query {
		fmt.Println("key/value:", k, v)
		// TODO: check for sql injection
		queryFields = append(queryFields, k)
		queryValues = append(queryValues, fmt.Sprintf("'%s'", v))
	}
	fmt.Println("fields:", queryFields)
	fmt.Println("values:", queryValues)
	var result sql.Result
	if result, requestError = self.db.Exec(fmt.Sprintf("INSERT INTO `users`(%s) VALUES(%s)", strings.Join(queryFields, ","), strings.Join(queryValues, ","))); requestError != nil {
		// Failure
		return
	} else {
		// Success
		if id, requestError = result.LastInsertId(); requestError != nil {
			// Failure
			return
		}
	}
	// Success
	fmt.Println("success?:", id, requestError)
	return
}

func (this *Database) ExecSql(mailbox rpc.Mailbox, msg *rpc.Message) (errcode int32, reply *rpc.Message) {
	var sqlstr string
	var callback string
	var callbackparams share.DBParams

	if server.Check(server.ParseArgs(msg, &sqlstr, &callback, &callbackparams)) {
		return 0, nil
	}

	sqlconn := db.sql
	var r sql.Result
	var err error

	app := server.GetAppById(mailbox.App)
	if app == nil {
		log.LogError(server.ErrAppNotFound)
		return 0, nil
	}

	if r, err = sqlconn.Exec(sqlstr); err != nil {
		log.LogError("sql:", sqlstr)
		server.Check(app.Call(nil, callback, callbackparams, 0, err.Error()))
		return 0, nil
	}

	eff, _ := r.RowsAffected()
	server.Check(app.Call(nil, callback, callbackparams, eff, ""))
	return 0, nil
}

func InsertPost(title []byte, slug string, markdown []byte, html []byte, featured bool, isPage bool, published bool, image []byte, created_at time.Time, created_by int64) (int64, error) {

	status := "draft"
	if published {
		status = "published"
	}
	writeDB, err := readDB.Begin()
	if err != nil {
		writeDB.Rollback()
		return 0, err
	}
	var result sql.Result
	if published {
		result, err = writeDB.Exec(stmtInsertPost, nil, uuid.Formatter(uuid.NewV4(), uuid.FormatCanonical), title, slug, markdown, html, featured, isPage, status, image, created_by, created_at, created_by, created_at, created_by, created_at, created_by)
	} else {
		result, err = writeDB.Exec(stmtInsertPost, nil, uuid.Formatter(uuid.NewV4(), uuid.FormatCanonical), title, slug, markdown, html, featured, isPage, status, image, created_by, created_at, created_by, created_at, created_by, nil, nil)
	}
	if err != nil {
		writeDB.Rollback()
		return 0, err
	}
	postId, err := result.LastInsertId()
	if err != nil {
		writeDB.Rollback()
		return 0, err
	}
	return postId, writeDB.Commit()
}

/* **
执行更新操作
param1 表名
param2 柱头
parma3 条件
param4 参数
return 影响行数，错误
** */
func (self *MDB) execUpdate(tableName, cols, condition string, args ...interface{}) (int64, error) {
	tx, err := self.Begin()
	if err != nil {
		return 0, err
	}
	if condition == "" || len(args) == 0 {
		return 0, errors.New("条件和参数都不能为空！")
	}
	stmt, err := tx.Prepare(fmt.Sprintf("UPDATE %s SET %s WHERE %s", tableName, cols, condition))
	if err != nil {
		return 0, err
	}
	var result sql.Result
	if result, err = stmt.Exec(args...); err != nil {
		return 0, err
	}
	defer stmt.Close()
	if err = tx.Commit(); err != nil {
		return 0, err
	}
	if affected, err := result.RowsAffected(); err != nil {
		return 0, err
	} else {
		return affected, nil
	}
}

func (d *DBAlias) BatchExec(sqlbuf string, argsv [][]interface{}) (affectedRows int64, err error) {
	var result sql.Result
	if argsv == nil || len(argsv) == 0 {
		result, err = d.db.Exec(sqlbuf)
		if err == nil {
			affectedRows, err = result.RowsAffected()
		}
		return
	} else {
		var stmt *sql.Stmt
		stmt, err = d.db.Prepare(sqlbuf)
		if err != nil {
			return
		}

		defer stmt.Close()
		affectedRows = 0
		for i, _ := range argsv {
			r, err1 := stmt.Exec(argsv[i]...)
			if err1 != nil {
				err = err1
				return
			}
			num, _ := r.RowsAffected()
			affectedRows += num
		}

	}

	return

}

func (this *tableMap) Update2(exec SQLExecutor, where string, data map[string]string, except []string) (rows int64, err error) {
	dialect := this.dbmap.dialect
	updSQL := dialect.UpdateSQL(this.schemaName, this.tableName)
	//
	L := len(data)
	setFields := make([]string, L)
	quote := ""
	noQuotes := array2dict(except)
	var i int
	for key, val := range data {
		if _, ok := noQuotes[key]; ok {
			quote = ""
		} else {
			quote = "'"
		}
		setFields[i] = fmt.Sprintf("%s = %s%s%s", dialect.QuoteField(key), quote, val, quote)
		i++
	}
	//
	query := fmt.Sprintf(updSQL, strings.Join(setFields, ", "), setWhere(where))
	if exec == nil {
		exec = this
	}
	var res sql.Result
	if res, err = exec.Exec(query); err == nil {
		rows, err = res.RowsAffected()
	}
	return
}

func (this *mysql) Exec(query string, args ...interface{}) (afftectedRows int64,
	lastInsertId int64, err error) {
	if this.db == nil {
		return 0, 0, ErrNotOpen
	}
	if this.breaker.Open() {
		return 0, 0, ErrCircuitOpen
	}

	var result sql.Result
	result, err = this.db.Exec(query, args...)
	if err != nil {
		if this.isSystemError(err) {
			log.Warn("mysql exec breaks: %s", err.Error())
			this.breaker.Fail()
		}

		return 0, 0, err
	}

	afftectedRows, err = result.RowsAffected()
	if err != nil {
		if this.isSystemError(err) {
			log.Warn("mysql exec2 breaks: %s", err.Error())
			this.breaker.Fail()
		}
	} else {
		this.breaker.Succeed()
	}

	lastInsertId, _ = result.LastInsertId()
	return
}

func (this *tableMap) update(vptr reflect.Value, exec SQLExecutor, execVal []reflect.Value) (rows int64, err error) {
	var (
		bind *bindObj
		res  sql.Result
	)
	if err = triggerRun("PreUpdate", vptr, execVal); err != nil {
		return
	}
	if bind, err = this.bindUpdate(); err != nil {
		return
	}
	if err = bind.bindArgs(vptr.Elem()); err != nil {
		return
	}
	if res, err = exec.Exec(bind.query, bind.argValues...); err != nil {
		return
	}
	if rows, err = res.RowsAffected(); err != nil {
		return
	}
	if err = triggerRun("PostUpdate", vptr, execVal); err != nil {
		return
	}
	return
}