db.go 7.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402
  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. defaultDB *DB
  20. )
  21. // DB define
  22. type DB struct {
  23. Driver string
  24. conn *sqlx.DB
  25. tx *sqlx.Tx
  26. }
  27. // SetConfig set
  28. func SetConfig(cfg Config) {
  29. config.Driver = cfg.Driver
  30. config.DNS = cfg.DNS
  31. config.MaxOpenConns = cfg.MaxOpenConns
  32. config.MaxIdle = cfg.MaxIdle
  33. config.MaxLifetime = cfg.MaxLifetime * time.Second
  34. defaultDB = &DB{Driver: config.Driver}
  35. }
  36. // New new DB object
  37. func New() *DB {
  38. return &DB{Driver: config.Driver}
  39. }
  40. // Release free db connect
  41. func Release() {
  42. if db != nil {
  43. db.Close()
  44. }
  45. }
  46. // NewConfig new DB dynamic object
  47. func NewConfig(config Config) (dbx *DB, err error) {
  48. //dbx = &DB{}
  49. dbx.Driver = config.Driver
  50. dbx.conn, err = sqlx.Connect(config.Driver, config.DNS)
  51. if err != nil {
  52. return
  53. }
  54. dbx.conn.SetMaxOpenConns(config.MaxOpenConns)
  55. dbx.conn.SetMaxIdleConns(config.MaxIdle)
  56. dbx.conn.SetConnMaxLifetime(config.MaxLifetime)
  57. dbx.conn.Ping()
  58. return
  59. }
  60. // ReleaseConfig free db connect
  61. func ReleaseConfig(dbx *DB) {
  62. if dbx.conn != nil {
  63. dbx.conn.Close()
  64. }
  65. }
  66. func connect() (dbx *sqlx.DB, err error) {
  67. once.Do(func() {
  68. db, err = sqlx.Connect(config.Driver, config.DNS)
  69. if err == nil {
  70. db.DB.SetMaxOpenConns(config.MaxOpenConns)
  71. db.DB.SetMaxIdleConns(config.MaxIdle)
  72. db.DB.SetConnMaxLifetime(config.MaxLifetime)
  73. db.Ping()
  74. }
  75. })
  76. dbx = db
  77. return
  78. }
  79. // Stats Stats returns database statistics.
  80. func (d *DB) Stats() (s sql.DBStats) {
  81. s = d.conn.DB.Stats()
  82. return
  83. }
  84. // Connect connect to database
  85. func (d *DB) Connect() (err error) {
  86. if d.conn != nil {
  87. return
  88. }
  89. d.conn, err = connect()
  90. return
  91. }
  92. // Close close database connect
  93. func (d *DB) Close() {
  94. // use pool
  95. //d.conn.Close()
  96. }
  97. // BeginTrans begin trans
  98. func (d *DB) BeginTrans() (err error) {
  99. d.conn, err = connect()
  100. if err != nil {
  101. return
  102. }
  103. d.tx = d.conn.MustBegin()
  104. return
  105. }
  106. // Commit commit
  107. func (d *DB) Commit() error {
  108. return d.tx.Commit()
  109. }
  110. // Rollback rollback
  111. func (d *DB) Rollback() error {
  112. return d.tx.Rollback()
  113. }
  114. // TransExec trans execute
  115. func (d *DB) TransExec(query string, args interface{}) (LastInsertId, RowsAffected int64, err error) {
  116. if rs, err := d.tx.NamedExec(query, args); err == nil {
  117. RowsAffected, _ = rs.RowsAffected()
  118. LastInsertId, _ = rs.LastInsertId()
  119. }
  120. return
  121. }
  122. // TransUpdate trans update
  123. func (d *DB) TransUpdate(query string, args interface{}) (reply Reply) {
  124. var (
  125. err error
  126. rs sql.Result
  127. )
  128. if rs, err = d.tx.NamedExec(query, args); err == nil {
  129. a, _ := rs.RowsAffected()
  130. reply = ReplyOk(a, 0)
  131. } else {
  132. reply = ReplyFaild(ErrException, err, errors.New(`数据执行错误`))
  133. }
  134. return
  135. }
  136. // TransRow trans get row
  137. func (d *DB) TransRow(dest interface{}, query string, args interface{}) error {
  138. nstmt, err := d.tx.PrepareNamed(query)
  139. if err != nil {
  140. return err
  141. }
  142. defer nstmt.Close()
  143. err = nstmt.Get(dest, args)
  144. //err = d.tx.Get(dest, query, args)
  145. return err
  146. }
  147. // Select select
  148. func (d *DB) Select(dest interface{}, query string, args ...interface{}) error {
  149. err := d.Connect()
  150. if err != nil {
  151. return err
  152. }
  153. defer d.Close()
  154. err = d.conn.Select(dest, query, args...)
  155. return err
  156. }
  157. // Rows get rows
  158. func (d *DB) Rows(dest interface{}, query string, args interface{}) error {
  159. err := d.Connect()
  160. if err != nil {
  161. return err
  162. }
  163. defer d.Close()
  164. nstmt, err := d.conn.PrepareNamed(query)
  165. if err != nil {
  166. return err
  167. }
  168. defer nstmt.Close()
  169. err = nstmt.Select(dest, args)
  170. return err
  171. }
  172. // Get get
  173. func (d *DB) Get(dest interface{}, query string, args ...interface{}) error {
  174. err := d.Connect()
  175. if err != nil {
  176. return err
  177. }
  178. defer d.Close()
  179. err = d.conn.Get(dest, query, args...)
  180. return err
  181. }
  182. // Row get row
  183. func (d *DB) Row(dest interface{}, query string, args interface{}) error {
  184. err := d.Connect()
  185. if err != nil {
  186. return err
  187. }
  188. defer d.Close()
  189. nstmt, err := d.conn.PrepareNamed(query)
  190. if err != nil {
  191. return err
  192. }
  193. defer nstmt.Close()
  194. err = nstmt.Get(dest, args)
  195. return err
  196. }
  197. // InsertReply insert and return DbReply
  198. func (d *DB) InsertReply(query string, args interface{}) (reply Reply) {
  199. var (
  200. err error
  201. rs sql.Result
  202. )
  203. err = d.Connect()
  204. if err != nil {
  205. reply = ReplyFaild(ErrNotConnect, err, errors.New(`数据库连接错误`))
  206. return
  207. }
  208. defer d.Close()
  209. if rs, err = d.conn.NamedExec(query, args); err == nil {
  210. a, _ := rs.RowsAffected()
  211. n, _ := rs.LastInsertId()
  212. reply = ReplyOk(a, n)
  213. } else {
  214. reply = ReplyFaild(ErrException, err, errors.New(`数据执行错误`))
  215. }
  216. return
  217. }
  218. // UpdateReply update/delete and return DbReply
  219. func (d *DB) UpdateReply(query string, args interface{}) (reply Reply) {
  220. var (
  221. err error
  222. rs sql.Result
  223. )
  224. err = d.Connect()
  225. if err != nil {
  226. reply = ReplyFaild(ErrNotConnect, err, errors.New(`数据库连接错误`))
  227. return
  228. }
  229. defer d.Close()
  230. if rs, err = d.conn.NamedExec(query, args); err == nil {
  231. a, _ := rs.RowsAffected()
  232. reply = ReplyOk(a, 0)
  233. } else {
  234. reply = ReplyFaild(ErrException, err, errors.New(`数据执行错误`))
  235. }
  236. return
  237. }
  238. // Insert insert into
  239. func (d *DB) Insert(query string, args interface{}) (LastInsertId, RowsAffected int64, err error) {
  240. err = d.Connect()
  241. if err != nil {
  242. return
  243. }
  244. defer d.Close()
  245. var rs sql.Result
  246. if rs, err = d.conn.NamedExec(query, args); err == nil {
  247. LastInsertId, _ = rs.LastInsertId()
  248. RowsAffected, _ = rs.RowsAffected()
  249. }
  250. return
  251. }
  252. // Update update/delete
  253. func (d *DB) Update(query string, args interface{}) (RowsAffected int64, err error) {
  254. err = d.Connect()
  255. if err != nil {
  256. return
  257. }
  258. defer d.Close()
  259. var rs sql.Result
  260. if rs, err = d.conn.NamedExec(query, args); err == nil {
  261. RowsAffected, _ = rs.RowsAffected()
  262. }
  263. return
  264. }
  265. // Exec exec
  266. func (d *DB) Exec(query string, args ...interface{}) (LastInsertId, RowsAffected int64, err error) {
  267. err = d.Connect()
  268. if err != nil {
  269. return
  270. }
  271. defer d.Close()
  272. var rs sql.Result
  273. if rs, err = d.conn.Exec(query, args...); err == nil {
  274. LastInsertId, _ = rs.LastInsertId()
  275. RowsAffected, _ = rs.RowsAffected()
  276. }
  277. return
  278. }
  279. // Limit MySQL limit
  280. func (d *DB) Limit(page, pagesize int) string {
  281. // MySQL limit 0, size
  282. if d.Driver == `mysql` {
  283. return fmt.Sprintf(" limit %d, %d", (page-1)*pagesize, pagesize)
  284. }
  285. // // PostgreSQL limit size offset 0
  286. return fmt.Sprintf(" limit %d offset %d", pagesize, (page-1)*pagesize)
  287. }
  288. // Select select
  289. func Select(dest interface{}, query string, args ...interface{}) (err error) {
  290. defaultDB.conn, err = connect()
  291. if err != nil {
  292. return err
  293. }
  294. err = defaultDB.conn.Select(dest, query, args...)
  295. return
  296. }
  297. // Rows get rows
  298. func Rows(dest interface{}, query string, args interface{}) (err error) {
  299. defaultDB.conn, err = connect()
  300. if err != nil {
  301. return err
  302. }
  303. nstmt, err := defaultDB.conn.PrepareNamed(query)
  304. if err != nil {
  305. return
  306. }
  307. defer nstmt.Close()
  308. err = nstmt.Select(dest, args)
  309. return
  310. }
  311. // Get get
  312. func Get(dest interface{}, query string, args ...interface{}) (err error) {
  313. defaultDB.conn, err = connect()
  314. if err != nil {
  315. return
  316. }
  317. err = defaultDB.conn.Get(dest, query, args...)
  318. return
  319. }
  320. // Row get row
  321. func Row(dest interface{}, query string, args interface{}) (err error) {
  322. defaultDB.conn, err = connect()
  323. if err != nil {
  324. return
  325. }
  326. nstmt, err := defaultDB.conn.PrepareNamed(query)
  327. if err != nil {
  328. return err
  329. }
  330. defer nstmt.Close()
  331. err = nstmt.Get(dest, args)
  332. return
  333. }
  334. func Exec(query string, args ...interface{}) (LastInsertId, RowsAffected int64, err error) {
  335. defaultDB.conn, err = connect()
  336. if err != nil {
  337. return
  338. }
  339. LastInsertId, RowsAffected, err = defaultDB.conn.Exec(query, args...)
  340. return
  341. }