sql-builder/db/add_sql.go

package db

import (
	"database/sql"
	"fmt"
	"reflect"
	"regexp"
	"strings"

	"git.botann.com/lijun/sql-builder/util"
)

func (s *SqlBuilder[T]) SetSql(sql string) {
	s.Sql = sql
}

// 如果添加的条件是in条件,则格式为"id in (?)",args参数为slice类型，并且每次调用该方法只能有一个in条件
func (s *SqlBuilder[T]) Conditions(cond string, args ...interface{}) {
	if strings.HasPrefix(cond, " and") {
		cond = strings.Join(strings.Split(cond, " and")[1:], " and ")
	}
	if s.Driver == util.DriverOracle {
		cond, args = dealOralceInCondition(cond, args)
	} else {
		cond, args = dealInCondition(cond, args)
	}
	s.Opts.Conditions = append(s.Opts.Conditions, cond)
	s.Opts.Args = append(s.Opts.Args, args...)
	s.buildCondition(util.ConditionPlaceholder, cond)
}

func (s *SqlBuilder[T]) InsertConditions(symbol string, cond string, args ...interface{}) {
	if strings.HasPrefix(cond, " and") {
		cond = strings.Join(strings.Split(cond, " and")[1:], " and ")
	}
	// if s.Driver == util.DriverOracle {
	// 	cond, args = dealOralceInCondition(cond, args)
	// } else {
	// 	cond, args = dealInCondition(cond, args)
	// }
	cond, args = dealInCondition(cond, args)
	s.Opts.Conditions = append(s.Opts.Conditions, cond)
	s.Opts.Args = append(s.Opts.Args, args...)
	s.buildCondition(symbol, cond)
}

func (s *SqlBuilder[T]) Selects(selects ...string) {
	s.Opts.selects = append(s.Opts.selects, selects...)
	s.buildSelect(util.SelectPlaceholder, selects...)
}

func (s *SqlBuilder[T]) InsertSelects(symbol string, selects ...string) {
	s.Opts.selects = append(s.Opts.selects, selects...)
	s.buildSelect(symbol, selects...)
}

func (s *SqlBuilder[T]) Groups(groups ...string) {
	s.Opts.groups = append(s.Opts.groups, groups...)
	s.buildGroup(util.GroupPlaceholder, groups...)
}

func (s *SqlBuilder[T]) InsertGroups(symbol string, groups ...string) {
	s.Opts.groups = append(s.Opts.groups, groups...)
	s.buildGroup(symbol, groups...)
}

func (s *SqlBuilder[T]) Orders(orders ...string) {
	s.Opts.orders = append(s.Opts.orders, orders...)
	s.buildOrder(util.OrderPlaceholder, orders...)
}

func (s *SqlBuilder[T]) InsertOrders(symbol string, orders ...string) {
	s.Opts.orders = append(s.Opts.orders, orders...)
	s.buildOrder(symbol, orders...)
}

func (s *SqlBuilder[T]) Partitions(partitions ...string) {
	s.Opts.partitions = append(s.Opts.partitions, partitions...)
	s.buildPartition(util.PartitionPlaceholder, partitions...)
}

func (s *SqlBuilder[T]) InsertPartitions(symbol string, partitions ...string) {
	s.Opts.partitions = append(s.Opts.partitions, partitions...)
	s.buildPartition(symbol, partitions...)
}

func dealInCondition(cond string, args []interface{}) (string, []interface{}) {
	if ok := strings.Contains(cond, "(?)"); !ok {
		return cond, args
	}
	var newArgs []interface{}
	for i := 0; i < len(args); i++ {
		//判断args参数是否为slice
		if reflect.TypeOf(args[i]).Kind() == reflect.Slice {
			var str []string
			for j := 0; j < reflect.ValueOf(args[i]).Len(); j++ {
				//如果是slice，则将cond里的?替代为args的长度个?
				str = append(str, "?")
				//将args[i]里的元素添加到new_args里
				newArgs = append(newArgs, reflect.ValueOf(args[i]).Index(j).Interface())
			}
			cond = strings.Replace(cond, "(?)", fmt.Sprintf("(%s)", strings.Join(str, ",")), -1)
		} else {
			newArgs = append(newArgs, args[i])
		}
	}
	return cond, newArgs
}

func dealOralceInCondition(cond string, args []interface{}) (string, []interface{}) {
	if ok := strings.Contains(cond, "(:in)"); !ok {
		return dealOracleCondition(cond, args)
	}
	var newArgs []interface{}
	for i := 0; i < len(args); i++ {
		//判断args参数是否为slice
		if reflect.TypeOf(args[i]).Kind() == reflect.Slice {
			var str []string
			for j := 0; j < reflect.ValueOf(args[i]).Len(); j++ {
				//如果是slice，则将cond里的?替代为args的长度个?
				str = append(str, fmt.Sprintf(":in%d", j))
				//将args[i]里的元素添加到new_args里
				newArgs = append(newArgs, sql.Named(fmt.Sprintf("in%d", j), reflect.ValueOf(args[i]).Index(j).Interface()))
			}
			cond = strings.Replace(cond, "(:in)", fmt.Sprintf("(%s)", strings.Join(str, ",")), -1)
		} else {
			newArgs = append(newArgs, args[i])
		}
	}
	return cond, newArgs
}

func dealOracleCondition(cond string, args []interface{}) (string, []interface{}) {
	re := regexp.MustCompile(`:(\w+)`)
	matches := re.FindAllStringSubmatch(cond, -1)
	var result []string
	for _, match := range matches {
		if len(match) > 1 {
			result = append(result, match[1])
		}
	}
	newArgs := make([]interface{}, 0)
	for i := 0; i < len(args); i++ {
		if reflect.ValueOf(args[i]).Kind() == reflect.Slice {
			for j := 0; j < reflect.ValueOf(args[i]).Len(); j++ {
				newArgs = append(newArgs, sql.Named(fmt.Sprintf("%s", result[j]), reflect.ValueOf(args[i]).Index(j).Interface()))
			}
		} else {
			newArgs = append(newArgs, sql.Named(fmt.Sprintf("%s", result[i]), args[i]))
		}
	}
	return cond, newArgs
}