|
|
- package tencentdb
-
- import (
- "database/sql"
- "errors"
- "log"
- "strconv"
- "strings"
- )
-
- var stmt *sql.Stmt
- var err error
-
- type Query struct {
- dbname string
- table string
- alias string
- title string
- where []string
- where_or []string
- join [][]string //[["tablea as a","a.id=b.id","left"]]
- data []string
- value []interface{}
- orderby string
- page int
- page_size int
- stmt *sql.Stmt
- conn *sql.DB
- debug bool
- dbtype string
- }
-
- func NewQuery(t ...string) *Query {
-
- return &Query{
- conn: DB,
- dbtype: "mysql",
- }
- }
-
- func (this *Query) Conn(conn *sql.DB) *Query {
- this.conn = conn
- return this
- }
-
- func (this *Query) ConnClose() error {
- return this.conn.Close()
- }
-
- func (this *Query) Db(dbname string) *Query {
- this.dbname = dbname
- return this
- }
-
- func (this *Query) Table(tablename string) *Query {
- this.table = tablename
- return this
- }
- func (this *Query) Alias(tablename string) *Query {
- this.alias = tablename
- return this
- }
-
- func (this *Query) Title(title string) *Query {
- this.title = title
- return this
- }
- func (this *Query) Page(page int) *Query {
- this.page = page
- return this
- }
- func (this *Query) PageSize(page_num int) *Query {
- this.page_size = page_num
- return this
- }
-
- func (this *Query) Orderby(orderby string) *Query {
- this.orderby = orderby
- return this
- }
- func (this *Query) Where(where string) *Query {
- this.where = append(this.where, where)
- return this
- }
- func (this *Query) Wheres(wheres []string) *Query {
- if len(wheres) > 0 {
- this.where = append(this.where, wheres...)
- }
- return this
- }
- func (this *Query) WhereOr(where string) *Query {
- this.where_or = append(this.where_or, where)
- return this
- }
- func (this *Query) Value(value interface{}) *Query {
- this.value = append(this.value, value)
- return this
- }
- func (this *Query) Values(values []interface{}) *Query {
- this.value = append(this.value, values...)
- return this
- }
- func (this *Query) Join(join []string) *Query {
- this.join = append(this.join, join)
- return this
- }
- func (this *Query) Data(data string) *Query {
- this.data = append(this.data, data)
- return this
- }
- func (this *Query) Datas(datas []string) *Query {
- this.data = append(this.data, datas...)
- return this
- }
-
- func (this *Query) Debug(debug bool) *Query {
- this.debug = debug
- return this
- }
-
- /*
- * 清理上次查询
- */
- func (this *Query) Clean() *Query {
- this.title = ""
- this.where = this.where[0:0]
- this.where_or = this.where_or[0:0]
- this.join = this.join[0:0]
- this.data = this.data[0:0]
- this.value = this.value[0:0]
- this.orderby = ""
- this.page = 0
- this.page_size = 0
- return this
- }
-
- // 拼查询sql
- func (this *Query) QueryStmt() error {
-
- if this.dbname == "" && this.table == "" {
- return errors.New("参数错误,没有数据表")
- }
-
- table := getTableName(this.dbname, this.table, this.dbtype)
-
- // var err error
-
- var sql, title string
-
- if this.title != "" {
- title = this.title
- } else {
- title = "*"
- }
- sql = StringJoin("/*slave*/ select ", title)
-
- if this.alias != "" {
- table = StringJoin(table, " as ", this.alias)
- }
-
- sql = StringJoin(sql, " from ", table)
-
- if len(this.join) > 0 {
- for _, joinitem := range this.join {
- if len(joinitem) < 2 {
- continue
- }
- if len(joinitem) == 3 {
- sql = StringJoin(sql, " ", joinitem[2], " join ", getTableName(this.dbname, joinitem[0], this.dbtype), " on ", joinitem[1])
- } else { //默认左连接
- sql = StringJoin(sql, " left join ", getTableName(this.dbname, joinitem[0], this.dbtype), " on ", joinitem[1])
- }
- }
- }
- if len(this.where) > 0 || len(this.where_or) > 0 {
- sql = StringJoin(sql, " where ")
- }
- if len(this.where) > 0 {
- sql = StringJoin(sql, " (", strings.Join(this.where, " and "), " ) ")
- }
- if len(this.where_or) > 0 {
- if len(this.where) > 0 {
- sql = StringJoin(sql, " or ", strings.Join(this.where_or, " or "))
- } else {
- sql = StringJoin(sql, strings.Join(this.where_or, " or "))
- }
- }
-
- if this.orderby != "" {
- sql = StringJoin(sql, " order by ", this.orderby)
- }
-
- if this.page > 0 || this.page_size > 0 {
-
- if this.page < 1 {
- this.page = 1
- }
- if this.page_size < 1 {
- this.page_size = 10
- }
- from := strconv.Itoa((this.page - 1) * this.page_size)
- offset := strconv.Itoa(this.page_size)
- if from != "" && offset != "" {
- sql = StringJoin(sql, " limit ", from, " , ", offset)
- }
- }
- if this.debug {
- log.Println("query sql:", sql, this.value)
- }
- condition_len := 0 //所有条件数
- for _, ch2 := range sql {
- if string(ch2) == "?" {
- condition_len++
- }
- }
- if condition_len != len(this.value) {
- return errors.New("参数错误,条件值错误")
- }
-
- if this.conn == nil {
- this.conn = DB
- }
-
- stmt, err = this.conn.Prepare(sql)
-
- if err != nil {
- return err
- }
-
- this.stmt = stmt
-
- return nil
- }
-
- // 拼更新sql
- func (this *Query) UpdateStmt() error {
-
- if this.dbname == "" && this.table == "" {
- return errors.New("参数错误,没有数据表")
- }
- if len(this.where) < 1 {
- return errors.New("参数错误,缺少条件")
- }
-
- dbName := getTableName(this.dbname, this.table, this.dbtype)
-
- var sql string
-
- sql = StringJoin("update ", dbName, " set ", strings.Join(this.data, " , "))
-
- sql = StringJoin(sql, " where ", strings.Join(this.where, " and "))
-
- if this.debug {
- log.Println("update sql:", sql, this.value)
- }
- condition_len := 0 //所有条件数
-
- for _, ch2 := range sql {
- if string(ch2) == "?" {
- condition_len++
- }
- }
- if condition_len != len(this.value) {
- return errors.New("参数错误,条件值错误")
- }
-
- if this.conn == nil {
- this.conn = DB
- }
-
- stmt, err = this.conn.Prepare(sql)
-
- if err != nil {
- return err
- }
-
- this.stmt = stmt
-
- return nil
- }
-
- // 拼插入sql
- func (this *Query) CreateStmt() error {
-
- if this.dbname == "" && this.table == "" {
- return errors.New("参数错误,没有数据表")
- }
-
- dbName := getTableName(this.dbname, this.table, this.dbtype)
-
- var sql string
-
- sql = StringJoin("insert into ", dbName, " set ", strings.Join(this.data, " , "))
-
- if this.debug {
- log.Println("insert sql:", sql, this.value)
- }
- condition_len := 0 //所有条件数
-
- for _, ch2 := range sql {
- if string(ch2) == "?" {
- condition_len++
- }
- }
- if condition_len != len(this.value) {
- return errors.New("参数错误,条件值错误")
- }
-
- if this.conn == nil {
- this.conn = DB
- }
-
- stmt, err = this.conn.Prepare(sql)
-
- if err != nil {
- return err
- }
-
- this.stmt = stmt
-
- return nil
- }
-
- // 拼删除sql
- func (this *Query) DeleteStmt() error {
-
- if this.dbname == "" && this.table == "" {
- return errors.New("参数错误,没有数据表")
- }
- if len(this.where) < 1 {
- return errors.New("参数错误,缺少条件")
- }
-
- dbName := getTableName(this.dbname, this.table, this.dbtype)
-
- var sql string
-
- sql = StringJoin("delete from ", dbName, " where ", strings.Join(this.where, " and "))
-
- if this.page_size > 0 {
- sql = StringJoin(sql, " limit ", strconv.Itoa(this.page_size))
- }
-
- if this.debug {
- log.Println("delete sql:", sql, this.value)
- }
- condition_len := 0 //所有条件数
-
- for _, ch2 := range sql {
- if string(ch2) == "?" {
- condition_len++
- }
- }
- if condition_len != len(this.value) {
- return errors.New("参数错误,条件值错误")
- }
-
- if this.conn == nil {
- this.conn = DB
- }
-
- stmt, err = this.conn.Prepare(sql)
-
- if err != nil {
- return err
- }
-
- this.stmt = stmt
-
- return nil
- }
-
- /**
- * 执行查询列表
- * return list error
- */
- func (this *Query) Select() ([]map[string]string, error) {
-
- conn, err := connectDb(dbhost_slaver, dbuser_slaver, dbpwd_slaver, dbname_slaver, dbport_slaver, 1, 0)
-
- if err != nil {
- log.Println("new query error:", err)
- }
- if conn == nil {
- log.Println("Err: no conn")
- }
-
- _, rows, err := FetchRows(conn, this.dbname, this.table, this.alias, this.title, this.join,
- this.where, this.where_or, this.value, this.orderby, this.page, this.page_size, this.debug)
-
- conn.Close()
-
- return rows, err
- }
-
- /**
- * 执行查询多条数据
- * return row error
- * 2022/01/05
- */
- func (this *Query) List() ([]map[string]string, error) {
-
- err := this.QueryStmt()
- if err != nil {
- return []map[string]string{}, err
- }
-
- if this.stmt == nil {
- return []map[string]string{}, errors.New("缺少必要参数")
- }
-
- list, err := StmtForQueryList(this.stmt, this.value)
-
- return list, err
- }
-
- /**
- * 执行查询一条数据
- * return row error
- */
- func (this *Query) Find() (map[string]string, error) {
- conn, err := connectDb(dbhost_slaver, dbuser_slaver, dbpwd_slaver, dbname_slaver, dbport_slaver, 1, 0)
-
- if err != nil {
- log.Println("new query error:", err)
- }
- if conn == nil {
- log.Println("Err: no conn")
- }
-
- _, row, err := GetRow(conn, this.dbname, this.table, this.alias, this.title, this.join,
- this.where, this.where_or, this.value, this.orderby, this.debug)
-
- conn.Close()
-
- return row, err
- }
-
- /**
- * 执行查询一条数据
- * return row error
- * 2022/01/05
- */
- func (this *Query) Get() (map[string]string, error) {
- this.page = 1
- this.page_size = 1
- err := this.QueryStmt()
- if err != nil {
- return map[string]string{}, err
- }
-
- if this.stmt == nil {
- return nil, errors.New("缺少必要参数")
- }
- row, err := StmtForQueryRow(this.stmt, this.value)
- return row, err
- }
-
- /**
- * 执行更新
- * return is_updated error
- */
- func (this *Query) Update() (int64, error) {
-
- err := this.UpdateStmt()
- if err != nil {
- return 0, err
- }
-
- ret, err := StmtForUpdateExec(this.stmt, this.value)
- return ret, err
- }
-
- /**
- * 执行删除
- * return is_delete error
- */
- func (this *Query) Delete() (int64, error) {
-
- err := this.DeleteStmt()
- if err != nil {
- return 0, err
- }
-
- ret, err := StmtForUpdateExec(this.stmt, this.value)
- return ret, err
- }
-
- /**
- * 执行写入
- * return is_insert error
- */
- func (this *Query) Create() (int64, error) {
-
- err := this.CreateStmt()
- if err != nil {
- return 0, err
- }
-
- ret, err := StmtForInsertExec(this.stmt, this.value)
- return ret, err
- }
|