Golang DB.Prepare方法代碼示例

func doSelect(res http.ResponseWriter, db *sql.DB) error {
	var stmt *sql.Stmt
	var err error

	stmt, err = db.Prepare(selectStatement)
	if err != nil {
		fmt.Printf("db.Prepare error: %v\n", err)
		return err
	}

	var rows *sql.Rows

	rows, err = stmt.Query()
	if err != nil {
		fmt.Printf("stmt.Query error: %v\n", err)
		return err
	}

	defer stmt.Close()

	for rows.Next() {
		var firstname string
		var lastname string

		err = rows.Scan(&firstname, &lastname)
		if err != nil {
			fmt.Printf("rows.Scan error: %v\n", err)
			return err
		}

		fmt.Fprintln(res, "firstname: "+firstname+"    lastname: "+lastname+"\n")
	}

	return nil
}

func push(db *sql.DB, iter, routine int, queryStr string) {
	defer timeTracker(time.Now(), "pump", routine)
	//fmt.Printf("StartTime: %v\n", time.Now())
	stmt, prepErr := db.Prepare(queryStr)
	if prepErr != nil {
		log.Fatal(prepErr)
	}
	defer stmt.Close()

	//var rowIds = []int64{}
	var rowNumCount int64
	for i := 0; i < iter; i++ {
		res, resErr := stmt.Exec("tester", time.Now(), i)
		if resErr != nil || res == nil {
			log.Fatal(resErr)
		}

		// count number of rows efected
		numRow, _ := res.RowsAffected()
		rowNumCount += numRow
		//lins, _ := res.LastInsertId()
		//rowIds = append(rowIds, lins)
		//lins, errLin := res.LastInsertId()
		//numRow, errRow := res.RowsAffected()
	}

	//fmt.Printf("Last ID: %d Number of rows: %d\n", rowIds[:len(rowIds)-1], rowNumCount)
	fmt.Printf("Number of rows: %d\n", rowNumCount)
	//fmt.Printf("StopTime: %v\n", time.Now())
	// cant do this cause we'll exit when first goroutine is done!
	//os.Exit(0)
}

func insert_session(db *sql.DB, user_id int, sid string) {
	row := db.QueryRow("select count(id) from auth_session  where user_id=?", user_id)
	var count int
	err := row.Scan(&count)
	if err != nil {
		fmt.Println(err)
	}
	if count > 0 {
		stmt, err := db.Prepare("update auth_session set sid=? where user_id=?")
		if err != nil {
			fmt.Println(err)
			return
		}
		stmt.Exec(sid, user_id)
		defer stmt.Close()
		return
	}
	stmt, err := db.Prepare("insert into auth_session(user_id, sid) values (?,?)")
	if err != nil {
		fmt.Println(err)
	}
	defer stmt.Close()
	if _, err := stmt.Exec(user_id, sid); err != nil {
		fmt.Println("smt.Exec failed: ", err)
	}
}

func FundHandler(rw http.ResponseWriter, r *http.Request, db *sql.DB, params martini.Params) {
	id := params["id"]
	sqlStr := "SELECT * FROM funds WHERE id=?"
	stmt, err := db.Prepare(sqlStr)
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()
	fund := new(Fund)
	err = stmt.QueryRow(id).Scan(&fund.Id, &fund.Name)
	if err != nil {
		if err == sql.ErrNoRows {
			rw.Header().Set("Status", "404")
			rw.Header().Set("Content-Type", "application/json; charset=utf-8")
			notFound := map[string]string{"error": "Not Found"}
			b, _ := json.Marshal(notFound)
			rw.Write(b)
		} else {
			log.Fatal(err)
		}
	} else {
		rw.Header().Set("Content-Type", "application/json; charset=utf-8")
		b, err := json.Marshal(fund)
		if err != nil {
			log.Fatal(err)
		}
		rw.Write(b)
	}
}

// Save the User data to the DB
func SaveUser(db *sql.DB, u User) (id int64, err error) {

	// Prepare Insert Statement
	stmt, err := db.Prepare(`
		INSERT
			tbl_users
		SET
			steam64=?,
			steam32=?,
			name=?
	`)
	if err != nil {
		fmt.Println(err)
	}

	defer stmt.Close()

	res, err := stmt.Exec(
		u.Steam64,
		u.Steam32,
		u.Name,
	)

	if err != nil {
		fmt.Println(err)
	}

	id, err = res.LastInsertId()
	if err != nil {
		fmt.Println(err)
	}

	return id, err

}

func mustPrepare(db *sql.DB, query string) *sql.Stmt {
	stmt, err := db.Prepare(query)
	if err != nil {
		log.Fatalf("Error when preparing statement %q: %s", query, err)
	}
	return stmt
}

//tries to create the schema and ignores failures to do so.
//versions after Postgres 9.3 support the "IF NOT EXISTS" sql syntax
func tryCreateSchema(db *sql.DB, importSchema string) {
	createSchema, err := db.Prepare(fmt.Sprintf("CREATE SCHEMA %s", importSchema))

	if err == nil {
		createSchema.Exec()
	}
}

func returnRideHistory(db *sql.DB, u int) (map[string]int, map[string]int, error) {
	result := make(map[string]int)
	result2 := make(map[string]int)

	stmt, err := db.Prepare(`
		SELECT year, sum(rides_given), sum(rides_taken)
			FROM ride_history 
			WHERE user_id = ? 
			GROUP BY year;
	`)

	if err != nil {
		return result, result2, util.NewError(err, "Error de la base de datos", 500)
	}
	defer stmt.Close()

	rows, err := stmt.Query(u)
	if err != nil {
		return result, result2, util.NewError(err, "Error de la base de datos", 500)
	}
	defer rows.Close()

	for rows.Next() {
		year := ""
		var given, taken int
		err := rows.Scan(&year, &given, &taken)
		if err != nil {
			return result, result2, util.NewError(err, "Error de la base de datos", 500)
		}
		result[year] = given
		result2[year] = taken
	}

	return result, result2, nil
}

func returnUserComments(db *sql.DB, u int) ([]Comment, error) {
	results := make([]Comment, 0)

	stmt, err := db.Prepare(`
		SELECT positive, DATE_FORMAT(date,'%d/%m/%Y'), comment
			FROM comments 
			WHERE user = ? 
			AND public = true;
	`)

	if err != nil {
		return results, util.NewError(err, "Error de la base de datos", 500)
	}
	defer stmt.Close()

	rows, err := stmt.Query(u)
	if err != nil {
		return results, util.NewError(err, "Error de la base de datos", 500)
	}
	defer rows.Close()

	for rows.Next() {
		comment := Comment{}
		err := rows.Scan(&comment.Positive, &comment.Date, &comment.Text)
		if err != nil {
			return results, util.NewError(err, "Error de la base de datos", 500)
		}
		results = append(results, comment)
	}

	return results, nil
}

//create table with a single JSON or JSONB column data
func createJSONTable(db *sql.DB, schema string, tableName string, column string, dataType string) (*sql.Stmt, error) {
	fullyQualifiedTable := fmt.Sprintf("%s.%s", schema, tableName)
	tableSchema := fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (%s %s)", fullyQualifiedTable, column, dataType)

	statement, err := db.Prepare(tableSchema)
	return statement, err
}

func dbInit(db *sql.DB) error {
	var err error
	_, err = db.Exec(initSQL)
	if err != nil {
		glog.Errorln("error intializing database")
		return err
	}
	q = map[string]*sql.Stmt{}
	prepare := map[string]string{
		"getUser":          "SELECT name, pubkey, creation, admin FROM users WHERE id = $1",
		"getUserIdByName":  "SELECT id FROM users WHERE name = $1",
		"getGroup":         "SELECT name, userGroup FROM groups WHERE id = $1",
		"getGroupIdByName": "SELECT id FROM groups WHERE name = $1",
		"checkToken":       "SELECT EXISTS (SELECT 1 FROM session WHERE id = $1 AND token = $2 AND expire > current_timestamp);",
		"checkChallenge":   "SELECT EXISTS (SELECT 1 FROM session WHERE id = $1 AND challenge = $2 AND expire > current_timestamp);",
	}
	for name, query := range prepare {
		x, err := db.Prepare(query)
		if err != nil {
			glog.Errorf("error preparing: %s\t%s\n", name, query)
			return err
		}
		q[name] = x
	}
	return nil
}

func LoadAllGroup(db *sql.DB) (map[int64]*Group, error) {
	stmtIns, err := db.Prepare("SELECT id, appid, super FROM `group`")
	if err != nil {
		log.Info("error:", err)
		return nil, nil
	}

	defer stmtIns.Close()
	groups := make(map[int64]*Group)
	rows, err := stmtIns.Query()
	for rows.Next() {
		var id int64
		var appid int64
		var super int8
		rows.Scan(&id, &appid, &super)
		members, err := LoadGroupMember(db, id)
		if err != nil {
			log.Info("error:", err)
			continue
		}

		if super != 0 {
			group := NewSuperGroup(id, appid, members)
			groups[group.gid] = group
		} else {
			group := NewGroup(id, appid, members)
			groups[group.gid] = group
		}
	}
	return groups, nil
}

func WritePsql(username string, db *sql.DB) {
	statement, err := db.Prepare("INSERT INTO entry(username,created) VALUES($1,$2)")
	checkErr(err)
	_, err = statement.Exec(username, time.Now())
	checkErr(err)
	counts.Add("sql write", 1)
}

func (self *catalogResource) executeInsert(db *sql.DB, query *string, args ...interface{}) (id int64, err error) {
	l := NewLogger("catalog_resource")

	defer func() {
		if r := recover(); r != nil {
			id = 0
			err = r.(error)

			l.Error("Could not insert record", "err", err)
		}
	}()

	stmt, err := db.Prepare(*query)
	if err != nil {
		panic(err)
	}

	res, err := stmt.Exec(args...)
	if err != nil {
		panic(err)
	}

	id, err = res.LastInsertId()
	if err != nil {
		panic(err)
	}

	return id, nil
}

// Link a Server's channels together
func populateChannelLinkInfo(server *Server, db *sql.DB) (err error) {
	stmt, err := db.Prepare("SELECT channel_id, link_id FROM channel_links WHERE server_id=?")
	if err != nil {
		return err
	}

	rows, err := stmt.Query(server.Id)
	if err != nil {
		return err
	}

	for rows.Next() {
		var (
			ChannelId int
			LinkId    int
		)
		if err := rows.Scan(&ChannelId, &LinkId); err != nil {
			return err
		}

		channel, exists := server.Channels[ChannelId]
		if !exists {
			return errors.New("Attempt to perform link operation on non-existant channel.")
		}

		other, exists := server.Channels[LinkId]
		if !exists {
			return errors.New("Attempt to perform link operation on non-existant channel.")
		}

		server.LinkChannels(channel, other)
	}

	return nil
}