Golang DB.Select方法代碼示例

// GetAgeModelData does the search and modifies the results struct pointer
// Not placed in the above function to allow this to be called by stand along apps like ocdBulk
func GetAgeModelData(db *sqlx.DB, sqlstring string, results **[]AgeModel) {
	db.MapperFunc(strings.ToUpper)
	err := db.Select(*results, sqlstring)
	if err != nil {
		log.Printf(`Error v2 with: %s`, err)
	}
}

func queryWhereToStructTX(db *sqlx.DB) {

	log.Println("Query with Where Map to Struct TX...")
	log.Println("====================================")

	tx := db.MustBegin()
	now := time.Now()
	t := Todo{
		Subject:     sql.NullString{String: "Mow Lawn"},
		Description: sql.NullString{String: "Yuck!"},
		CreatedAt:   now,
		UpdatedAt:   now,
	}

	tx.Exec("Insert into todos(subject, description, created_at, updated_at) values ($1, $2, $3, $4)", t.Subject, t.Description, t.CreatedAt, t.UpdatedAt)

	tx.Commit()

	todos := []Todo{}
	err := db.Select(&todos, "select * from todos")

	if err != nil {
		log.Fatal(err)
	}

	for _, todo := range todos {
		log.Printf("Subject is %s\n", todo.CreatedAt)
	}

}

func KillThreads(db *sqlx.DB) {
	var ids []int
	db.Select(&ids, "SELECT Id FROM information_schema.PROCESSLIST WHERE Command != 'binlog dump' AND User != 'system user' AND Id != CONNECTION_ID()")
	for _, id := range ids {
		db.Exec("KILL ?", id)
	}
}

func (s *Slugs) GetAllUrls(db *sqlx.DB) {

	err := db.Select(s, "SELECT slug,modified,status FROM posts WHERE status=1 ORDER BY created")
	if err != nil {
		panic(err)
	}
}

func (p *Posts) GetAll(db *sqlx.DB) {

	err := db.Select(p, "SELECT * FROM posts ORDER BY created DESC")
	if err != nil {
		panic(err)
	}
}

func QueryQuestions(db *sqlx.DB, authorId int, query string, offset int) ([]Question, error) {
	qs := []Question{}
	qry := sq.Select("*").From("questions")

	if authorId != 0 {
		qry = qry.Where("author_id = ?", authorId)
	}
	if query != "" {
		word := fmt.Sprint("%", query, "%")
		qry = qry.Where("(title LIKE ? OR question LIKE ?)", word, word)
	}
	if offset > 0 {
		qry = qry.Offset(uint64(offset))
	}

	qry = qry.OrderBy("created_at DESC")
	qry = qry.PlaceholderFormat(sq.Dollar)
	sql, params, err := qry.ToSql()

	if err != nil {
		return qs, err
	} else {
		err := db.Select(&qs, sql, params...)
		dbErr := dbError(err)
		return qs, dbErr
	}
}

func contentServersSection(cdnName string, ccrDomain string, db *sqlx.DB) (map[string]ContentServer, error) {
	csQuery := "select * from content_servers where cdn='" + cdnName + "'"
	cServers := []CrContentServer{}
	err := db.Select(&cServers, csQuery)
	if err != nil {
		log.Println(err)
		err = fmt.Errorf("contentServersSection error selecting content_servers: %v", err)
		return nil, err
	}
	dsServerQuery := "select * from cr_deliveryservice_server"
	dsServers := []CrDeliveryserviceServer{}
	err = db.Select(&dsServers, dsServerQuery)
	if err != nil {
		log.Println("ERROR: >> ", err)
		err = fmt.Errorf("contentServersSection error selecting cr_deliveryservice_server: %v", err)
		return nil, err
	}
	dsMap := make(map[string]ContentServerDsMap)
	for _, row := range dsServers {
		if dsMap[row.ServerName] == nil {
			dsMap[row.ServerName] = make(ContentServerDsMap)
		}
		// if dsMap[row.ServerName][row.Name] == nil {
		// 	dsMap[row.ServerName][row.Name] = make(ContentServerDomainList, 0, 10)
		// }
		pattern := row.Pattern
		if strings.HasSuffix(pattern, "\\..*") {
			pattern = strings.Replace(pattern, ".*\\.", "", 1)
			pattern = strings.Replace(pattern, "\\..*", "", 1)
			if strings.HasPrefix(row.DsType, "HTTP") {
				pattern = row.ServerName + "." + pattern + "." + ccrDomain
			} else {
				pattern = "edge." + pattern + "." + ccrDomain
			}
		}
		dsMap[row.ServerName][row.Name] = append(dsMap[row.ServerName][row.Name], pattern)
	}

	retMap := make(map[string]ContentServer)
	for _, row := range cServers {
		hCount, _ := strconv.Atoi(row.HashCount)
		hCount = hCount * 1000 // TODO JvD
		retMap[row.HostName] = ContentServer{
			Fqdn:             row.Fqdn,
			HashCount:        hCount,
			HashID:           row.HostName,
			InterfaceName:    row.InterfaceName,
			IP:               row.Ip,
			IP6:              row.Ip6,
			LocationID:       row.CacheGroup,
			Port:             row.Port,
			Profile:          row.Profile,
			Status:           row.Status,
			Type:             row.Status,
			DeliveryServices: dsMap[row.HostName],
		}
	}

	return retMap, nil
}

func search(db *sqlx.DB, query string) ([]person, error) {
	people := []person{}
	tokens := strings.Split(query, " ")
	cryptoTokens := []string{}

	for _, token := range tokens {
		token = strings.TrimSpace(strings.ToLower(token))
		crypted := fmt.Sprintf("%x", sha256.Sum256([]byte(token)))
		cryptoTokens = append(cryptoTokens, crypted)
	}

	q, args, err := sqlx.In(`SELECT DISTINCT p.id, p.name
                                 FROM people p 
                                 JOIN hashes_people hp ON hp.person_id = p.id
                                 JOIN hashes h ON h.id = hp.hash_id AND h.hash IN (?)
                             GROUP BY p.name`, cryptoTokens)

	if err != nil {
		return people, err
	}

	err = db.Select(&people, q, args...)

	if err != nil {
		return people, err
	}

	return people, err
}

func GetProcesslist(db *sqlx.DB) []Processlist {
	pl := []Processlist{}
	err := db.Select(&pl, "SELECT id, user, host, `db` AS `database`, command, time_ms as time, state FROM INFORMATION_SCHEMA.PROCESSLIST")
	if err != nil {
		log.Fatalln("ERROR: Could not get processlist", err)
	}
	return pl
}

// GetChannelLists returns a list of ChannelList items.
func GetChannelLists(db *sqlx.DB, limit, offset int) ([]models.ChannelList, error) {
	var channelLists []models.ChannelList
	err := db.Select(&channelLists, "select * from channel_list order by name limit $1 offset $2", limit, offset)
	if err != nil {
		return nil, fmt.Errorf("get channel-list list error: %s", err)
	}
	return channelLists, nil
}

// GetChannelsForChannelList returns the Channels for the given ChannelList id.
func GetChannelsForChannelList(db *sqlx.DB, channelListID int64) ([]models.Channel, error) {
	var channels []models.Channel
	err := db.Select(&channels, "select * from channel where channel_list_id = $1 order by channel", channelListID)
	if err != nil {
		return nil, fmt.Errorf("get channels for channel-list %d error: %s", channelListID, err)
	}
	return channels, nil
}

// GetVehiclesByID returns all vehicles with a given ID
func GetVehiclesByID(db *sqlx.DB, id string) ([]*muni.Vehicle, error) {
	v := []*muni.Vehicle{}
	err := db.Select(&v, `SELECT * FROM vehicles WHERE vehicle_id=$1`, id)
	if err != nil {
		return nil, err
	}
	return v, nil
}

func GetSlaveHostsArray(db *sqlx.DB) []SlaveHosts {
	sh := []SlaveHosts{}
	err := db.Select(&sh, "SHOW SLAVE HOSTS")
	if err != nil {
		log.Fatalln("ERROR: Could not get slave hosts array", err)
	}
	return sh
}

// GetVehiclesAfterTime returns all vehicles logged after limit
func GetVehiclesAfterTime(db *sqlx.DB, limit time.Time) ([]*muni.Vehicle, error) {
	v := []*muni.Vehicle{}
	err := db.Select(&v, `SELECT * FROM vehicles WHERE time_received >= $1 ORDER BY time_received DESC`, limit)
	if err != nil {
		return nil, err
	}
	return v, nil
}

// GetNodesForAppEUI returns a slice of nodes, sorted by DevEUI, for the given AppEUI.
func GetNodesForAppEUI(db *sqlx.DB, appEUI lorawan.EUI64, limit, offset int) ([]models.Node, error) {
	var nodes []models.Node
	err := db.Select(&nodes, "select * from node where app_eui = $1 order by dev_eui limit $2 offset $3", appEUI[:], limit, offset)
	if err != nil {
		return nodes, fmt.Errorf("get nodes error: %s", err)
	}
	return nodes, nil
}