db.go 5.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271
  1. package db
  2. import (
  3. "database/sql"
  4. "errors"
  5. "fmt"
  6. "sync"
  7. "time"
  8. // PostgreSQL
  9. _ "github.com/lib/pq"
  10. // MySQL
  11. _ "github.com/go-sql-driver/mysql"
  12. "github.com/jmoiron/sqlx"
  13. )
  14. var (
  15. config Config
  16. db *sqlx.DB
  17. err error
  18. once sync.Once
  19. )
  20. // DB define
  21. type DB struct {
  22. Driver string
  23. conn *sqlx.DB
  24. tx *sqlx.Tx
  25. }
  26. // SetConfig set
  27. func SetConfig(cfg Config) {
  28. config.Driver = cfg.Driver
  29. config.DNS = cfg.DNS
  30. config.MaxOpenConns = cfg.MaxOpenConns
  31. config.MaxIdle = cfg.MaxIdle
  32. config.MaxLifetime = cfg.MaxLifetime * time.Second
  33. }
  34. // New new DB object
  35. func New() *DB {
  36. return &DB{Driver: config.Driver}
  37. }
  38. // Release free db connect
  39. func Release() {
  40. if db != nil {
  41. db.Close()
  42. }
  43. }
  44. // NewConfig new DB dynamic object
  45. func NewConfig(config Config) (dbx *DB, err error) {
  46. dbx = &DB{}
  47. dbx.Driver = config.Driver
  48. dbx.conn, err = sqlx.Connect(config.Driver, config.DNS)
  49. if err == nil {
  50. dbx.conn.SetMaxOpenConns(config.MaxOpenConns)
  51. dbx.conn.SetMaxIdleConns(config.MaxIdle)
  52. dbx.conn.SetConnMaxLifetime(config.MaxLifetime)
  53. dbx.conn.Ping()
  54. }
  55. return
  56. }
  57. // ReleaseConfig free db connect
  58. func ReleaseConfig(dbx *DB) {
  59. if dbx.conn != nil {
  60. dbx.conn.Close()
  61. }
  62. }
  63. func connect() (dbx *sqlx.DB, err error) {
  64. once.Do(func() {
  65. db, err = sqlx.Connect(config.Driver, config.DNS)
  66. if err == nil {
  67. db.DB.SetMaxOpenConns(config.MaxOpenConns)
  68. db.DB.SetMaxIdleConns(config.MaxIdle)
  69. db.DB.SetConnMaxLifetime(config.MaxLifetime)
  70. db.Ping()
  71. }
  72. })
  73. dbx = db
  74. return
  75. }
  76. // Connect connect to database
  77. func (d *DB) Connect() (err error) {
  78. if d.conn != nil {
  79. return
  80. }
  81. d.conn, err = connect()
  82. return
  83. }
  84. // Close close database connect
  85. func (d *DB) Close() {
  86. //d.conn.Close()
  87. }
  88. // BeginTrans begin trans
  89. func (d *DB) BeginTrans() (err error) {
  90. d.conn, err = connect()
  91. if err != nil {
  92. return
  93. }
  94. d.tx = d.conn.MustBegin()
  95. return
  96. }
  97. // Commit commit
  98. func (d *DB) Commit() error {
  99. return d.tx.Commit()
  100. }
  101. // Rollback rollback
  102. func (d *DB) Rollback() error {
  103. return d.tx.Rollback()
  104. }
  105. // TransExec trans execute
  106. func (d *DB) TransExec(query string, args interface{}) (LastInsertId, RowsAffected int64, err error) {
  107. if rs, err := d.tx.NamedExec(query, args); err == nil {
  108. RowsAffected, _ = rs.RowsAffected()
  109. LastInsertId, _ = rs.LastInsertId()
  110. }
  111. return
  112. }
  113. // TransUpdate trans update
  114. func (d *DB) TransUpdate(query string, args interface{}) (reply Reply) {
  115. var (
  116. err error
  117. rs sql.Result
  118. )
  119. if rs, err = d.tx.NamedExec(query, args); err == nil {
  120. a, _ := rs.RowsAffected()
  121. reply = ReplyOk(a, 0)
  122. } else {
  123. reply = ReplyFaild(ErrException, err, errors.New(`数据执行错误`))
  124. }
  125. return
  126. }
  127. // Rows get rows
  128. func (d *DB) Rows(dest interface{}, query string, args interface{}) error {
  129. err := d.Connect()
  130. if err != nil {
  131. return err
  132. }
  133. defer d.Close()
  134. nstmt, err := d.conn.PrepareNamed(query)
  135. if err != nil {
  136. return err
  137. }
  138. defer nstmt.Close()
  139. err = nstmt.Select(dest, args)
  140. return err
  141. }
  142. // Row get row
  143. func (d *DB) Row(dest interface{}, query string, args interface{}) error {
  144. err := d.Connect()
  145. if err != nil {
  146. return err
  147. }
  148. defer d.Close()
  149. nstmt, err := d.conn.PrepareNamed(query)
  150. if err != nil {
  151. return err
  152. }
  153. defer nstmt.Close()
  154. err = nstmt.Get(dest, args)
  155. return err
  156. }
  157. // InsertReply insert and return DbReply
  158. func (d *DB) InsertReply(query string, args interface{}) (reply Reply) {
  159. var (
  160. err error
  161. rs sql.Result
  162. )
  163. err = d.Connect()
  164. if err != nil {
  165. reply = ReplyFaild(ErrNotConnect, err, errors.New(`数据库连接错误`))
  166. return
  167. }
  168. defer d.Close()
  169. if rs, err = d.conn.NamedExec(query, args); err == nil {
  170. a, _ := rs.RowsAffected()
  171. n, _ := rs.LastInsertId()
  172. reply = ReplyOk(a, n)
  173. } else {
  174. reply = ReplyFaild(ErrException, err, errors.New(`数据执行错误`))
  175. }
  176. return
  177. }
  178. // UpdateReply update/delete and return DbReply
  179. func (d *DB) UpdateReply(query string, args interface{}) (reply Reply) {
  180. var (
  181. err error
  182. rs sql.Result
  183. )
  184. err = d.Connect()
  185. if err != nil {
  186. reply = ReplyFaild(ErrNotConnect, err, errors.New(`数据库连接错误`))
  187. return
  188. }
  189. defer d.Close()
  190. if rs, err = d.conn.NamedExec(query, args); err == nil {
  191. a, _ := rs.RowsAffected()
  192. reply = ReplyOk(a, 0)
  193. } else {
  194. reply = ReplyFaild(ErrException, err, errors.New(`数据执行错误`))
  195. }
  196. return
  197. }
  198. // Insert insert into
  199. func (d *DB) Insert(query string, args interface{}) (LastInsertId, RowsAffected int64, err error) {
  200. err = d.Connect()
  201. if err != nil {
  202. return
  203. }
  204. defer d.Close()
  205. var rs sql.Result
  206. if rs, err = d.conn.NamedExec(query, args); err == nil {
  207. LastInsertId, _ = rs.LastInsertId()
  208. RowsAffected, _ = rs.RowsAffected()
  209. }
  210. return
  211. }
  212. // Update update/delete
  213. func (d *DB) Update(query string, args interface{}) (RowsAffected int64, err error) {
  214. err = d.Connect()
  215. if err != nil {
  216. return
  217. }
  218. defer d.Close()
  219. var rs sql.Result
  220. if rs, err = d.conn.NamedExec(query, args); err == nil {
  221. RowsAffected, _ = rs.RowsAffected()
  222. }
  223. return
  224. }
  225. // Limit MySQL limit
  226. func (d *DB) Limit(page, pagesize int) string {
  227. // MySQL limit 0, size
  228. if d.Driver == `mysql` {
  229. return fmt.Sprintf(" limit %d, %d", (page-1)*pagesize, pagesize)
  230. }
  231. // // PostgreSQL limit size offset 0
  232. return fmt.Sprintf(" limit %d offset %d", pagesize, (page-1)*pagesize)
  233. }