SQLBuilder

• 为什么需要SQLBuilder？
  1. 写一句很长的sql容易出错，且出错后不好定位。
  2. 函数式编程可以直接定位到是哪个函数的问题。
  3. 函数式编程比一长串sql更容易编写和理解。

Go-SQLBuilder

• Go-SQLBuilder 是一个用于创建SQL 语句的工具函数库，提供一系列灵活的、与原生SQL 语法一致的链式函数。归属于艾润物联公司。安装方式

go get -u github.com/parkingwang/go-sqlbuilder

• Go-SQLBuilder通过函数链来构造sql语句，比如select语句的构造

func query() {
	sql := gsb.NewContext().Select("id", "name", "score", "city").
		From("student").
		OrderBy("score").DESC().
		Column("name").ASC().
		Limit(10).Offset(20).
		ToSQL()
	fmt.Println(sql)
}

Gendry

• Gendry 是一个用于辅助操作数据库的Go 包。基于go-sql-driver/mysql，它提供了一系列的方法来为你调用标准库database/sql 中的方法准备参数。安装方式

go get –u github.com/didi/gendry

• Gendry 倾向于把复杂的筛选条件放在map 中，并且跟stmt 技术结合得比较紧密。

    import(
        "github.com/didi/gendry/builder"
	"github.com/didi/gendry/manager"
        )
        
        dbName := "test"
	user := "tester"
	password := "123"
	host := "localhost"
	db, err := manager.New(dbName, user, password, host).Set(manager.SetCharset("utf8"),).Port(3306).Open(true)
        
        func insert(db *sql.DB) {
	data := []map[string]interface{}{
		{"name": "王五", "province": "河南", "city": "郑州", "enrollment": "2021-05-01"},
		{"name": "大王", "province": "浙江", "city": "杭州", "enrollment": "2021-04-01"},
	}
	table := "student"
	template, values, err := builder.BuildReplaceInsert(table, data) //使用replace
	database.CheckError(err)
	exec(db,template,values,"insert")
}

自定义SQL Builder

Builder 接口

    type Builder interface { 
	toString() string
	getPrev() Builder
    ToString() string
    }
 
    //后面每个Builder都是这么实现接口的(含strings.Builder 即sb)
    func(self *xxxBuilder) toString()string{
        return self.sb.String() 
    }
    func (self *xxxBuilder) getPrev() Builder{
        return self.prev
    }
    func (self *xxxBuilder) ToString() string{
        var root = Builder
        root = self
        for root.getPrev() != nil{
            root = root.getPrev()
        }
        return root.toString()
    }

各类型Builder：相当于层层嵌套

1. LimitBuilder：

type LimitBuilder struct {
	sb   strings.Builder
	prev Builder 
}

//构造函数
func newLimitBuilder(offset, n int) *LimitBuilder {
	builder := &LimitBuilder{}
	//通过strings.Builder实现高效的字符串连接
	builder.sb.WriteString(" limit ")
	builder.sb.WriteString(strconv.Itoa(offset))
	builder.sb.WriteString(",")
	builder.sb.WriteString(strconv.Itoa(n))
	return builder
}

2. OrderBuilder：

type OrderByBuilder struct {
	sb    strings.Builder
	limit *LimitBuilder
	prev  Builder
}

//构造函数
func newOrderByBuilder(column string) *OrderByBuilder {
	builder := &OrderByBuilder{}
	builder.sb.WriteString(" order by ")
	builder.sb.WriteString(column)
	return builder
}

//升降序
func (self *OrderByBuilder) Asc() *OrderByBuilder {
	self.sb.WriteString(" asc")
	return self
}
 
func (self *OrderByBuilder) Desc() *OrderByBuilder {
	self.sb.WriteString(" desc")
	return self
}

//嵌套函数
func (self *OrderByBuilder) Limit(offset, n int) *LimitBuilder {
	limit := newLimitBuilder(offset, n)
	limit.prev = self
	self.limit = limit
	return limit
}

3. WhereBuilder：

type WhereBuilder struct {
	sb      strings.Builder
	orderby *OrderByBuilder
	limit   *LimitBuilder
	prev    Builder
}

//构造函数
func newWhereBuilder(condition string) *WhereBuilder {
	builder := &WhereBuilder{}
	builder.sb.WriteString(" where ")
	builder.sb.WriteString(condition)
	return builder //sb: where score>30
}

//嵌套函数
func (self *WhereBuilder) OrderBy(column string) *OrderByBuilder {
	orderby := newOrderByBuilder(column)
	self.orderby = orderby
	orderby.prev = self
	return orderby
}
 
func (self *WhereBuilder) Limit(offset, n int) *LimitBuilder {
	limit := newLimitBuilder(offset, n)
	limit.prev = self
	self.limit = limit
	return limit
}

// AND OR 实现，And和Or都是where里的可选部分，它们的地位平等，都返回WhereBuilder
func (self *WhereBuilder) And(condition string) *WhereBuilder {
	self.sb.WriteString(" and ")
	self.sb.WriteString(condition)
	return self
}
 
func (self *WhereBuilder) Or(condition string) *WhereBuilder {
	self.sb.WriteString(" or ")
	self.sb.WriteString(condition)
	return self
}

4. SelectBuilder：

type SelectBuilder struct {
	sb    strings.Builder
	table string
	where *WhereBuilder
    orderby *OrderByBuilder
	limit   *LimitBuilder
}

//构造函数
func NewSelectBuilder(table string) *SelectBuilder {
	builder := &SelectBuilder{
		table: table,
	}
	builder.sb.WriteString("select ")
	return builder
}

//参数函数：（通过select查询哪几列）
func (self *SelectBuilder) Column(columns string) *SelectBuilder {
	self.sb.WriteString(columns)
	self.sb.WriteString(" from ")
	self.sb.WriteString(self.table)
	return self
}

//嵌套函数
func (self *SelectBuilder) Where(condition string) *WhereBuilder {
	where := newWhereBuilder(condition)
	self.where = where
	where.prev = self
	return where
}
func (self *SelectBuilder) OrderBy(column string) *OrderByBuilder {
	orderby := newOrderByBuilder(column)
	self.orderby = orderby
	orderby.prev = self
	return orderby
}
func (self *SelectBuilder) Limit(offset, n int) *LimitBuilder {
	limit := newLimitBuilder(offset, n)
	limit.prev = self
	self.limit = limit
	return limit
}

• Where、OrderBy、Limit有没有都不影响调用ToString();Where里的And和Or有没有都不影响调用ToString()