myql操作
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

502 lines
9.6 KiB

  1. package tencentdb
  2. import (
  3. "database/sql"
  4. "errors"
  5. "log"
  6. "strconv"
  7. "strings"
  8. )
  9. var stmt *sql.Stmt
  10. var err error
  11. type Query struct {
  12. dbname string
  13. table string
  14. alias string
  15. title string
  16. where []string
  17. where_or []string
  18. join [][]string //[["tablea as a","a.id=b.id","left"]]
  19. data []string
  20. value []interface{}
  21. orderby string
  22. page int
  23. page_size int
  24. stmt *sql.Stmt
  25. conn *sql.DB
  26. debug bool
  27. dbtype string
  28. }
  29. func NewQuery(t ...string) *Query {
  30. return &Query{
  31. conn: DB,
  32. dbtype: "mysql",
  33. }
  34. }
  35. func (this *Query) Conn(conn *sql.DB) *Query {
  36. this.conn = conn
  37. return this
  38. }
  39. func (this *Query) ConnClose() error {
  40. return this.conn.Close()
  41. }
  42. func (this *Query) Db(dbname string) *Query {
  43. this.dbname = dbname
  44. return this
  45. }
  46. func (this *Query) Table(tablename string) *Query {
  47. this.table = tablename
  48. return this
  49. }
  50. func (this *Query) Alias(tablename string) *Query {
  51. this.alias = tablename
  52. return this
  53. }
  54. func (this *Query) Title(title string) *Query {
  55. this.title = title
  56. return this
  57. }
  58. func (this *Query) Page(page int) *Query {
  59. this.page = page
  60. return this
  61. }
  62. func (this *Query) PageSize(page_num int) *Query {
  63. this.page_size = page_num
  64. return this
  65. }
  66. func (this *Query) Orderby(orderby string) *Query {
  67. this.orderby = orderby
  68. return this
  69. }
  70. func (this *Query) Where(where string) *Query {
  71. this.where = append(this.where, where)
  72. return this
  73. }
  74. func (this *Query) Wheres(wheres []string) *Query {
  75. if len(wheres) > 0 {
  76. this.where = append(this.where, wheres...)
  77. }
  78. return this
  79. }
  80. func (this *Query) WhereOr(where string) *Query {
  81. this.where_or = append(this.where_or, where)
  82. return this
  83. }
  84. func (this *Query) Value(value interface{}) *Query {
  85. this.value = append(this.value, value)
  86. return this
  87. }
  88. func (this *Query) Values(values []interface{}) *Query {
  89. this.value = append(this.value, values...)
  90. return this
  91. }
  92. func (this *Query) Join(join []string) *Query {
  93. this.join = append(this.join, join)
  94. return this
  95. }
  96. func (this *Query) Data(data string) *Query {
  97. this.data = append(this.data, data)
  98. return this
  99. }
  100. func (this *Query) Datas(datas []string) *Query {
  101. this.data = append(this.data, datas...)
  102. return this
  103. }
  104. func (this *Query) Debug(debug bool) *Query {
  105. this.debug = debug
  106. return this
  107. }
  108. /*
  109. * 清理上次查询
  110. */
  111. func (this *Query) Clean() *Query {
  112. this.title = ""
  113. this.where = this.where[0:0]
  114. this.where_or = this.where_or[0:0]
  115. this.join = this.join[0:0]
  116. this.data = this.data[0:0]
  117. this.value = this.value[0:0]
  118. this.orderby = ""
  119. this.page = 0
  120. this.page_size = 0
  121. return this
  122. }
  123. // 拼查询sql
  124. func (this *Query) QueryStmt() error {
  125. if this.dbname == "" && this.table == "" {
  126. return errors.New("参数错误,没有数据表")
  127. }
  128. table := getTableName(this.dbname, this.table, this.dbtype)
  129. // var err error
  130. var sql, title string
  131. if this.title != "" {
  132. title = this.title
  133. } else {
  134. title = "*"
  135. }
  136. sql = StringJoin("/*slave*/ select ", title)
  137. if this.alias != "" {
  138. table = StringJoin(table, " as ", this.alias)
  139. }
  140. sql = StringJoin(sql, " from ", table)
  141. if len(this.join) > 0 {
  142. for _, joinitem := range this.join {
  143. if len(joinitem) < 2 {
  144. continue
  145. }
  146. if len(joinitem) == 3 {
  147. sql = StringJoin(sql, " ", joinitem[2], " join ", getTableName(this.dbname, joinitem[0], this.dbtype), " on ", joinitem[1])
  148. } else { //默认左连接
  149. sql = StringJoin(sql, " left join ", getTableName(this.dbname, joinitem[0], this.dbtype), " on ", joinitem[1])
  150. }
  151. }
  152. }
  153. if len(this.where) > 0 || len(this.where_or) > 0 {
  154. sql = StringJoin(sql, " where ")
  155. }
  156. if len(this.where) > 0 {
  157. sql = StringJoin(sql, " (", strings.Join(this.where, " and "), " ) ")
  158. }
  159. if len(this.where_or) > 0 {
  160. if len(this.where) > 0 {
  161. sql = StringJoin(sql, " or ", strings.Join(this.where_or, " or "))
  162. } else {
  163. sql = StringJoin(sql, strings.Join(this.where_or, " or "))
  164. }
  165. }
  166. if this.orderby != "" {
  167. sql = StringJoin(sql, " order by ", this.orderby)
  168. }
  169. if this.page > 0 || this.page_size > 0 {
  170. if this.page < 1 {
  171. this.page = 1
  172. }
  173. if this.page_size < 1 {
  174. this.page_size = 10
  175. }
  176. from := strconv.Itoa((this.page - 1) * this.page_size)
  177. offset := strconv.Itoa(this.page_size)
  178. if from != "" && offset != "" {
  179. sql = StringJoin(sql, " limit ", from, " , ", offset)
  180. }
  181. }
  182. if this.debug {
  183. log.Println("query sql:", sql, this.value)
  184. }
  185. condition_len := 0 //所有条件数
  186. for _, ch2 := range sql {
  187. if string(ch2) == "?" {
  188. condition_len++
  189. }
  190. }
  191. if condition_len != len(this.value) {
  192. return errors.New("参数错误,条件值错误")
  193. }
  194. if this.conn == nil {
  195. this.conn = DB
  196. }
  197. stmt, err = this.conn.Prepare(sql)
  198. if err != nil {
  199. return err
  200. }
  201. this.stmt = stmt
  202. return nil
  203. }
  204. // 拼更新sql
  205. func (this *Query) UpdateStmt() error {
  206. if this.dbname == "" && this.table == "" {
  207. return errors.New("参数错误,没有数据表")
  208. }
  209. if len(this.where) < 1 {
  210. return errors.New("参数错误,缺少条件")
  211. }
  212. dbName := getTableName(this.dbname, this.table, this.dbtype)
  213. var sql string
  214. sql = StringJoin("update ", dbName, " set ", strings.Join(this.data, " , "))
  215. sql = StringJoin(sql, " where ", strings.Join(this.where, " and "))
  216. if this.debug {
  217. log.Println("update sql:", sql, this.value)
  218. }
  219. condition_len := 0 //所有条件数
  220. for _, ch2 := range sql {
  221. if string(ch2) == "?" {
  222. condition_len++
  223. }
  224. }
  225. if condition_len != len(this.value) {
  226. return errors.New("参数错误,条件值错误")
  227. }
  228. if this.conn == nil {
  229. this.conn = DB
  230. }
  231. stmt, err = this.conn.Prepare(sql)
  232. if err != nil {
  233. return err
  234. }
  235. this.stmt = stmt
  236. return nil
  237. }
  238. // 拼插入sql
  239. func (this *Query) CreateStmt() error {
  240. if this.dbname == "" && this.table == "" {
  241. return errors.New("参数错误,没有数据表")
  242. }
  243. dbName := getTableName(this.dbname, this.table, this.dbtype)
  244. var sql string
  245. sql = StringJoin("insert into ", dbName, " set ", strings.Join(this.data, " , "))
  246. if this.debug {
  247. log.Println("insert sql:", sql, this.value)
  248. }
  249. condition_len := 0 //所有条件数
  250. for _, ch2 := range sql {
  251. if string(ch2) == "?" {
  252. condition_len++
  253. }
  254. }
  255. if condition_len != len(this.value) {
  256. return errors.New("参数错误,条件值错误")
  257. }
  258. if this.conn == nil {
  259. this.conn = DB
  260. }
  261. stmt, err = this.conn.Prepare(sql)
  262. if err != nil {
  263. return err
  264. }
  265. this.stmt = stmt
  266. return nil
  267. }
  268. // 拼删除sql
  269. func (this *Query) DeleteStmt() error {
  270. if this.dbname == "" && this.table == "" {
  271. return errors.New("参数错误,没有数据表")
  272. }
  273. if len(this.where) < 1 {
  274. return errors.New("参数错误,缺少条件")
  275. }
  276. dbName := getTableName(this.dbname, this.table, this.dbtype)
  277. var sql string
  278. sql = StringJoin("delete from ", dbName, " where ", strings.Join(this.where, " and "))
  279. if this.page_size > 0 {
  280. sql = StringJoin(sql, " limit ", strconv.Itoa(this.page_size))
  281. }
  282. if this.debug {
  283. log.Println("delete sql:", sql, this.value)
  284. }
  285. condition_len := 0 //所有条件数
  286. for _, ch2 := range sql {
  287. if string(ch2) == "?" {
  288. condition_len++
  289. }
  290. }
  291. if condition_len != len(this.value) {
  292. return errors.New("参数错误,条件值错误")
  293. }
  294. if this.conn == nil {
  295. this.conn = DB
  296. }
  297. stmt, err = this.conn.Prepare(sql)
  298. if err != nil {
  299. return err
  300. }
  301. this.stmt = stmt
  302. return nil
  303. }
  304. /**
  305. * 执行查询列表
  306. * return list error
  307. */
  308. func (this *Query) Select() ([]map[string]string, error) {
  309. conn, err := connectDb(dbhost_slaver, dbuser_slaver, dbpwd_slaver, dbname_slaver, dbport_slaver, 1, 0)
  310. if err != nil {
  311. log.Println("new query error:", err)
  312. }
  313. if conn == nil {
  314. log.Println("Err: no conn")
  315. }
  316. _, rows, err := FetchRows(conn, this.dbname, this.table, this.alias, this.title, this.join,
  317. this.where, this.where_or, this.value, this.orderby, this.page, this.page_size, this.debug)
  318. conn.Close()
  319. return rows, err
  320. }
  321. /**
  322. * 执行查询多条数据
  323. * return row error
  324. * 2022/01/05
  325. */
  326. func (this *Query) List() ([]map[string]string, error) {
  327. err := this.QueryStmt()
  328. if err != nil {
  329. return []map[string]string{}, err
  330. }
  331. if this.stmt == nil {
  332. return []map[string]string{}, errors.New("缺少必要参数")
  333. }
  334. list, err := StmtForQueryList(this.stmt, this.value)
  335. return list, err
  336. }
  337. /**
  338. * 执行查询一条数据
  339. * return row error
  340. */
  341. func (this *Query) Find() (map[string]string, error) {
  342. conn, err := connectDb(dbhost_slaver, dbuser_slaver, dbpwd_slaver, dbname_slaver, dbport_slaver, 1, 0)
  343. if err != nil {
  344. log.Println("new query error:", err)
  345. }
  346. if conn == nil {
  347. log.Println("Err: no conn")
  348. }
  349. _, row, err := GetRow(conn, this.dbname, this.table, this.alias, this.title, this.join,
  350. this.where, this.where_or, this.value, this.orderby, this.debug)
  351. conn.Close()
  352. return row, err
  353. }
  354. /**
  355. * 执行查询一条数据
  356. * return row error
  357. * 2022/01/05
  358. */
  359. func (this *Query) Get() (map[string]string, error) {
  360. this.page = 1
  361. this.page_size = 1
  362. err := this.QueryStmt()
  363. if err != nil {
  364. return map[string]string{}, err
  365. }
  366. if this.stmt == nil {
  367. return nil, errors.New("缺少必要参数")
  368. }
  369. row, err := StmtForQueryRow(this.stmt, this.value)
  370. return row, err
  371. }
  372. /**
  373. * 执行更新
  374. * return is_updated error
  375. */
  376. func (this *Query) Update() (int64, error) {
  377. err := this.UpdateStmt()
  378. if err != nil {
  379. return 0, err
  380. }
  381. ret, err := StmtForUpdateExec(this.stmt, this.value)
  382. return ret, err
  383. }
  384. /**
  385. * 执行删除
  386. * return is_delete error
  387. */
  388. func (this *Query) Delete() (int64, error) {
  389. err := this.DeleteStmt()
  390. if err != nil {
  391. return 0, err
  392. }
  393. ret, err := StmtForUpdateExec(this.stmt, this.value)
  394. return ret, err
  395. }
  396. /**
  397. * 执行写入
  398. * return is_insert error
  399. */
  400. func (this *Query) Create() (int64, error) {
  401. err := this.CreateStmt()
  402. if err != nil {
  403. return 0, err
  404. }
  405. ret, err := StmtForInsertExec(this.stmt, this.value)
  406. return ret, err
  407. }