InfoSphereSQL Builder
qianmoQ· 更新于 2026-09-13· 阅读 24 分钟· 0 次阅读Raw SQL
Generics API
Query Raw SQL with Scan
type Result struct {
ID int
Name string
Age int
}
result, err := gorm.G[Result](db).Raw("SELECT id, name, age FROM users WHERE id = ?", 3).Find(context.Background())
age, err := gorm.G[int](db).Raw("SELECT SUM(age) FROM users WHERE role = ?", "admin").Find(context.Background())
users, err := gorm.G[User](db).Raw("UPDATE users SET name = ? WHERE age = ? RETURNING id, name", "jinzhu", 20).Find(context.Background())Exec with Raw SQL
result := gorm.WithResult()
err := gorm.G[any](db, result).Exec(context.Background(), "DROP TABLE users")
err = gorm.G[any](db).Exec(context.Background(), "UPDATE orders SET shipped_at = ? WHERE id IN ?", time.Now(), []int64{1, 2, 3})
err = gorm.G[any](db).Exec(context.Background(), "UPDATE users SET money = ? WHERE name = ?", gorm.Expr("money * ? + ?", 10000, 1), "jinzhu")Traditional API
Query Raw SQL with Scan
type Result struct {
ID int
Name string
Age int
}
var result Result
db.Raw("SELECT id, name, age FROM users WHERE id = ?", 3).Scan(&result)
db.Raw("SELECT id, name, age FROM users WHERE name = ?", "jinzhu").Scan(&result)
var age int
db.Raw("SELECT SUM(age) FROM users WHERE role = ?", "admin").Scan(&age)
var users []User
db.Raw("UPDATE users SET name = ? WHERE age = ? RETURNING id, name", "jinzhu", 20).Scan(&users)Exec with Raw SQL
db.Exec("DROP TABLE users")
db.Exec("UPDATE orders SET shipped_at = ? WHERE id IN ?", time.Now(), []int64{1, 2, 3})
db.Exec("UPDATE users SET money = ? WHERE name = ?", gorm.Expr("money * ? + ?", 10000, 1), "jinzhu")NOTE GORM allows cache prepared statement to increase performance, checkout Performance for details
Named Argument
Generics API
GORM supports named arguments with sql.NamedArg, map[string]interface{}{} or struct, for example:
users, err := gorm.G[User](db).Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(context.Background())
result3, err := gorm.G[User](db).Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu2"}).First(context.Background())
users, err := gorm.G[User](db).Raw("SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name",
sql.Named("name", "jinzhu1"), sql.Named("name2", "jinzhu2")).Find(context.Background())
err := gorm.G[any](db).Exec(context.Background(), "UPDATE users SET name1 = @name, name2 = @name2, name3 = @name",
sql.Named("name", "jinzhunew"), sql.Named("name2", "jinzhunew2"))
users, err := gorm.G[User](db).Raw("SELECT * FROM users WHERE (name1 = @name AND name3 = @name) AND name2 = @name2",
map[string]interface{}{"name": "jinzhu", "name2": "jinzhu2"}).Find(context.Background())
type NamedArgument struct {
Name string
Name2 string
}
users, err := gorm.G[User](db).Raw("SELECT * FROM users WHERE (name1 = @Name AND name3 = @Name) AND name2 = @Name2",
NamedArgument{Name: "jinzhu", Name2: "jinzhu2"}).Find(context.Background())
Traditional API
GORM supports named arguments with sql.NamedArg, map[string]interface{}{} or struct, for example:
db.Where("name1 = @name OR name2 = @name", sql.Named("name", "jinzhu")).Find(&user)
db.Where("name1 = @name OR name2 = @name", map[string]interface{}{"name": "jinzhu2"}).First(&result3)
db.Raw("SELECT * FROM users WHERE name1 = @name OR name2 = @name2 OR name3 = @name",
sql.Named("name", "jinzhu1"), sql.Named("name2", "jinzhu2")).Find(&user)
db.Exec("UPDATE users SET name1 = @name, name2 = @name2, name3 = @name",
sql.Named("name", "jinzhunew"), sql.Named("name2", "jinzhunew2"))
db.Raw("SELECT * FROM users WHERE (name1 = @name AND name3 = @name) AND name2 = @name2",
map[string]interface{}{"name": "jinzhu", "name2": "jinzhu2"}).Find(&user)
type NamedArgument struct {
Name string
Name2 string
}
db.Raw("SELECT * FROM users WHERE (name1 = @Name AND name3 = @Name) AND name2 = @Name2",
NamedArgument{Name: "jinzhu", Name2: "jinzhu2"}).Find(&user)
DryRun Mode
Generate SQL and its arguments without executing, can be used to prepare or test generated SQL, Checkout Session for details
stmt := db.Session(&gorm.Session{DryRun: true}).First(&user, 1).Statement
stmt.SQL.String()
stmt.Vars ToSQL
Returns generated SQL without executing.
During normal query execution, GORM uses database/sql argument placeholders to keep arguments separate from the SQL statement, which helps prevent SQL injection. However, the SQL generated by ToSQL does not provide the same safety guarantees and should only be used for debugging.
sql := db.ToSQL(func(tx *gorm.DB) *gorm.DB {
return tx.Model(&User{}).Where("id = ?", 100).Limit(10).Order("age desc").Find(&[]User{})
})
sql Row & Rows
Generics API
Get result as *sql.Row
row := gorm.G[any](db).Table("users").Where("name = ?", "jinzhu").Select("name", "age").Row(context.Background())
row.Scan(&name, &age)
row := gorm.G[any](db).Raw("select name, age, email from users where name = ?", "jinzhu").Row(context.Background())
row.Scan(&name, &age, &email)Get result as *sql.Rows
rows, err := gorm.G[User](db).Where("name = ?", "jinzhu").Select("name, age, email").Rows(context.Background())
defer rows.Close()
for rows.Next() {
rows.Scan(&name, &age, &email)
}
rows, err := gorm.G[any](db).Raw("select name, age, email from users where name = ?", "jinzhu").Rows(context.Background())
defer rows.Close()
for rows.Next() {
rows.Scan(&name, &age, &email)
}Traditional API
Get result as *sql.Row
row := db.Table("users").Where("name = ?", "jinzhu").Select("name", "age").Row()
row.Scan(&name, &age)
row := db.Raw("select name, age, email from users where name = ?", "jinzhu").Row()
row.Scan(&name, &age, &email)Get result as *sql.Rows
rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows()
defer rows.Close()
for rows.Next() {
rows.Scan(&name, &age, &email)
}
rows, err := db.Raw("select name, age, email from users where name = ?", "jinzhu").Rows()
defer rows.Close()
for rows.Next() {
rows.Scan(&name, &age, &email)
}Checkout FindInBatches for how to query and process records in batch
Checkout Group Conditions for how to build complicated SQL Query
Scan *sql.Rows into struct
Use ScanRows to scan a row into a struct, for example:
rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows()
defer rows.Close()
var user User
for rows.Next() {
db.ScanRows(rows, &user)
}Connection
Run mutliple SQL in same db tcp connection (not in a transaction)
db.Connection(func(tx *gorm.DB) error {
tx.Exec("SET my.role = ?", "admin")
tx.First(&User{})
})Advanced
Clauses
GORM uses SQL builder generates SQL internally, for each operation, GORM creates a *gorm.Statement object, all GORM APIs add/change Clause for the Statement, at last, GORM generated SQL based on those clauses
For example, when querying with First, it adds the following clauses to the Statement
var limit = 1
clause.Select{Columns: []clause.Column{{Name: "*"}}}
clause.From{Tables: []clause.Table{{Name: clause.CurrentTable}}}
clause.Limit{Limit: &limit}
clause.OrderBy{Columns: []clause.OrderByColumn{
{
Column: clause.Column{
Table: clause.CurrentTable,
Name: clause.PrimaryKey,
},
},
}}Then GORM build finally querying SQL in the Query callbacks like:
Statement.Build("SELECT", "FROM", "WHERE", "GROUP BY", "ORDER BY", "LIMIT", "FOR")Which generate SQL:
SELECT * FROM `users` ORDER BY `users`.`id` LIMIT 1You can define your own Clause and use it with GORM, it needs to implements Interface
Check out examples for reference
Clause Builder
For different databases, Clauses may generate different SQL, for example:
db.Offset(10).Limit(5).Find(&users)
Which is supported because GORM allows database driver register Clause Builder to replace the default one, take the Limit as example
Clause Options
GORM defined Many Clauses, and some clauses provide advanced options can be used for your application
Although most of them are rarely used, if you find GORM public API can’t match your requirements, may be good to check them out, for example:
db.Clauses(clause.Insert{Modifier: "IGNORE"}).Create(&user)
StatementModifier
GORM provides interface StatementModifier allows you modify statement to match your requirements, take Hints as example
import "gorm.io/hints"
db.Clauses(hints.New("hint")).Find(&User{})
评论
登录后参与评论