It is the library to work with "database/sql" of GO SDK to offer some advantages:
Some use cases that we optimize the performance:
You can refer to Generic CRUD Repository and Data Processing for more details.
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mappers">
<select id="user">
select *
from users
where
<if test="username != null">
username like #{username} and
</if>
<if test="displayName != null">
displayName like #{displayName} and
</if>
<if test="status != null">
status in (#{status}) and
</if>
<if test="q != null">
(username like #{q} or displayName like #{q} or email like #{q}) and
</if>
1 = 1
<if test="sort != null">
order by {sort}
</if>
<if test="sort == null">
order by userId
</if>
</select>
</mapper>
Repository is like CrudRepository of Spring, it provides these advantages:
package model
type UserId struct {
CompanyId string `json:"companyId" gorm:"column:company_id;primary_key"`
UserId string `json:"userId" gorm:"column:user_id;primary_key"`
}
The flow for search/paging:
func BuildFilter(
filter *model.UserFilter) (string, []interface{}) {
buildParam := s.BuildDollarParam
var where []string
var params []interface{}
i := 1
if len(filter.Id) > 0 {
params = append(params, filter.Id)
where = append(where,
fmt.Sprintf(`id = %s`, buildParam(i)))
i++
}
if filter.DateOfBirth != nil {
if filter.DateOfBirth.Min != nil {
params = append(params, filter.DateOfBirth.Min)
where = append(where,
fmt.Sprintf(`date_of_birth >= %s`, buildParam(i)))
i++
}
if filter.DateOfBirth.Max != nil {
params = append(params, filter.DateOfBirth.Max)
where = append(where,
fmt.Sprintf(`date_of_birth <= %s`, buildParam(i)))
i++
}
}
if len(filter.Username) > 0 {
q := filter.Username + "%"
params = append(params, q)
where = append(where,
fmt.Sprintf(`username like %s`, buildParam(i)))
i++
}
if len(filter.Email) > 0 {
q := filter.Email + "%"
params = append(params, q)
where = append(where,
fmt.Sprintf(`email like %s`, buildParam(i)))
i++
}
if len(filter.Phone) > 0 {
q := "%" + filter.Phone + "%"
params = append(params, q)
where = append(where,
fmt.Sprintf(`phone like %s`, buildParam(i)))
i++
}
if len(where) > 0 {
return strings.Join(where, " and "), params
}
return "", params
}
buildQuery := query.UseQuery[
model.User,
*model.UserFilter](db, "users")
query, args := buildQuery(filter)
func (r *UserAdapter) Load(
ctx context.Context,
id string) (*User, error) {
query := `
select
id,
username,
email,
phone,
date_of_birth
from users where id = ?`
rows, err := r.DB.QueryContext(ctx, query, id)
if err != nil {
return nil, err
}
defer rows.Close()
for rows.Next() {
var user User
err = rows.Scan(
&user.Id,
&user.Username,
&user.Phone,
&user.Email,
&user.DateOfBirth)
return &user, nil
}
return nil, nil
}
import q "github.com/core-go/sql"
func (r *UserAdapter) Load(
ctx context.Context,
id string) (*User, error) {
var users []User
query := fmt.Sprintf(`
select
id,
username,
email,
phone,
date_of_birth
from users where id = %s limit 1`,
q.BuildParam(1))
err := q.Select(ctx, r.DB, &users, query, id)
if err != nil {
return nil, err
}
if len(users) > 0 {
return &users[0], nil
}
return nil, nil
}
func (r *UserAdapter) Create(
ctx context.Context,
user *User) (int64, error) {
query := `
insert into users (
id,
username,
email,
phone,
date_of_birth)
values (
?,
?,
?,
?,
?)`
tx := GetTx(ctx)
stmt, err := tx.Prepare(query)
if err != nil {
return -1, err
}
res, err := stmt.ExecContext(ctx,
user.Id,
user.Username,
user.Email,
user.Phone,
user.DateOfBirth)
if err != nil {
return -1, err
}
return res.RowsAffected()
}
import q "github.com/core-go/sql"
func (r *UserAdapter) Create(
ctx context.Context,
user *User) (int64, error) {
query, args := q.BuildToInsert("users", user, q.BuildParam)
tx := q.GetTx(ctx)
res, err := tx.ExecContext(ctx, query, args...)
return q.RowsAffected(res, err)
}
func (s *userService) Create(
ctx context.Context,
user *User) (int64, error) {
tx, err := s.db.Begin()
if err != nil {
return -1, nil
}
ctx = context.WithValue(ctx, "tx", tx)
res, err := s.repository.Create(ctx, user)
if err != nil {
er := tx.Rollback()
if er != nil {
return -1, er
}
return -1, err
}
err = tx.Commit()
return res, err
}
func (s *userService) Create(
ctx context.Context,
user *User) (int64, error) {
ctx, tx, err := q.Begin(ctx, s.db)
if err != nil {
return -1, err
}
res, err := s.repository.Create(ctx, user)
return q.End(tx, res, err)
}
func (s *userService) Create(
ctx context.Context,
user *User) (int64, error) {
tx, err := s.db.Begin()
if err != nil {
return -1, nil
}
ctx = context.WithValue(ctx, "tx", tx)
res, err := s.repository.Create(ctx, user)
if err != nil {
er := tx.Rollback()
if er != nil {
return -1, er
}
return -1, err
}
err = tx.Commit()
return res, err
}
func (s *UserUseCase) Create(
ctx context.Context, user *model.User) (int64, error) {
return tx.Execute(ctx, s.db, func(ctx context.Context) (int64, error) {
return s.repository.Create(ctx, user)
})
}
Please make sure to initialize a Go module before installing core-go/sql:
go get -u github.com/core-go/sql
Import:
import "github.com/core-go/sql"