GORM中执行原始SQL语句的方法与技巧
GORM中执行原始SQL语句的方法与技巧
- GORM更新单一字段
func (repo *repository) ModifySingleField(entity *Entity) bool {
updateErr := repo.Database.Model(Entity{}).Where("entity_id = ?", entity.EntityID).Updates(map[string]interface{}{
"status": entity.Status,
}).Error
if updateErr != nil {
repo.Logger.Error(updateErr)
return false
}
return true
}
- GORM基本数据查询
func (repo *repository) FetchUserByUserID(userID int) (result User) {
if userID <= 0 {
repo.Logger.Error("Invalid parameter")
return
}
queryErr := repo.Database.Model(User{}).Where("user_id = ?", userID).First(&result).Error
if queryErr != nil {
repo.Logger.Error(queryErr)
}
return
}
type User struct {
UserID int `json:"user_id"`
UserName string `json:"user_name"`
}
func (User) TableName() string {
return "user_info"
}
- GORM多表关联与条件筛选
import (
"database/sql"
"github.com/jinzhu/gorm"
)
type dataHandler struct {
database *sql.DB
}
var handler *dataHandler
var Database *gorm.DB
func (*dataHandler) RetrieveComplexData(currentPage, limit int, searchName, searchAge, category string, statusType int) (results []UserData, totalCount int) {
if limit > 20 {
limit = 20
}
if currentPage <= 0 {
currentPage = 1
}
query := Database.Table("main_table as mt").Select(
"mt.user_name as user_name," +
"ma.age as age," +
"mc.created_time as created_time," +
"mc.state as state")
if searchName != "" {
query = query.Where("mt.identifier = ?", searchName)
}
if searchAge != "" {
query = query.Where("mt.shipment_ref = ?", searchAge)
}
if category != "" {
query = query.Where("mt.tracking_num = ?", category)
}
query = query.Where("mt.transport_type = ?", statusType)
query = query.Joins("left join secondary_table on secondary_table.ref_id = mt.main_id")
query = query.Joins("right join user_details on mc.tracking_num = mt.tracking_num")
query = query.Order("transport_time").Limit(limit).Offset((currentPage - 1) * limit)
scanErr := query.Count(&totalCount).Scan(&results).Error
if scanErr != nil {
app.Logger.Errorf("Query failed: %s", scanErr)
return
}
return
}
- GORM执行原始SQL语句
方法一：使用Database.Raw
type DeviceAlert struct {
DeviceID string `json:"device_id"`
AlertType int `json:"alert_type"`
}
func (repo *repository) FetchAlertsByDeviceIDs(companyID string, deviceList []string) (alerts []DeviceAlert) {
if len(deviceList) == 0 {
repo.Logger.Error("Empty device list")
return
}
deviceClause := "('" + strings.Join(deviceList, "','") + "')"
queryString := fmt.Sprintf(`
SELECT
payload::json->>'device_id' as device_id,
alert_category as alert_type
FROM notification_table where record_id in (
SELECT max(record_id) as record_id FROM notification_table WHERE company_id = '%s' and payload::json->>'device_id' in %s GROUP BY payload::json->>'device_id'
)
`, companyID, deviceClause)
executionResult := repo.Database.Raw(queryString).Scan(&alerts)
if executionResult.Error != nil {
repo.Logger.Error(executionResult.Error)
return
}
return
}
方法二：使用Database.QueryRow
func (repo repository) GetDataByRef(refCode string) *ReferenceData {
record := new(ReferenceData)
var queryBuilder string
switch {
case len(refCode) > 0:
queryBuilder += fmt.Sprintf(" and \"reference_code\" = '%s'", refCode)
default:
repo.Logger.Error("Parameter validation failed")
return nil
}
baseSQL := `
select
coalesce("reference_code",'') as ReferenceCode,
coalesce(description,'') as Description,
coalesce(title,'') as Title,
coalesce(model,'') as Model,
coalesce(color,'') as Color
from reference_table
where 1=1
`
baseSQL += queryBuilder
scanErr := repo.DatabaseReadOnly.QueryRow(baseSQL).Scan(&record.ReferenceCode, &record.Description, &record.Title, &record.Model, &record.Color)
if scanErr != nil {
repo.Logger.Errorf("Parameters=%s, SQL=%s, Error=%v", refCode, baseSQL, scanErr)
return nil
}
return record
}
方法三：使用Database.Raw配合rows.next
// 执行原始SQL查询
resultSet, queryErr := database.Raw("select username, user_age, contact_email from user_profiles where username = ?", "john_doe").Rows()
defer resultSet.Close()
for resultSet.Next() {
var userName string
var userAge int
var email string
resultSet.Scan(&userName, &userAge, &email)
// 处理每行数据
}
- SQL分组后获取首条记录
SELECT
*
FROM
( SELECT *, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY last_updated DESC ) AS row_num FROM customer_data WHERE customer_id IN ( 2,3,4 ) and active_status = 1 ) subquery
WHERE
subquery.row_num = 1
- GORM执行原始UPDATE语句
rawSQL := `UPDATE target_table SET column_name = 'new_value' WHERE condition_column = 'condition_value'`
executionErr := dbContext.Exec(rawSQL).Error
if executionErr != nil {
logger.WithContext(contextInstance).Error(executionErr)
return executionErr
}
