Go uses xorm to operate MySQL

Time:2021-11-25

Go uses xorm to operate MySQL

This article introduces the golang ORM libraryxormUse of and project structure.

xorm

  1. Official introduction: xorm is a simple and powerfulGoLanguage ORM library.
  2. It can make the database operation very simple. The goal of xorm is not to stop you from learning SQL at all,
  3. We don’t think SQL will be replaced by ORM, but ORM will be able to solve most simple SQL requirements.
  4. Xorm supports the mixing of two styles.

Xorm also provides a tool to generate the corresponding struct according to the table structure of the database through the reverse command, which saves the work of manually organizing the code and is very convenient. Official address: https://xorm.io/

install

Browse the GitHub address of xorm. We need to download two packages, https://github.com/go-xorm

Go uses xorm to operate MySQL

1. Xorm driver package, we use the core package of xorm

2. CMD toolkit, which is used to generate the struct corresponding to the data table using the reverse command

Download two packages through the go get command. Go get github.com/go-xorm/xormgo get github.com/go-xorm/cmd/xorm after downloading, the go xorm package will appear in the github.com folder

Go uses xorm to operate MySQL

Generate data structure struct

The local database test has two data tables, doctor_ TB and user_ TB, the data structure is as follows:

Go uses xorm to operate MySQL

Go uses xorm to operate MySQL

Let’s now generate the structure model of these two data tables.

1. Create a new folder under any project xorm_ Models, the file name is not specified, in order to store the generated code file.

2. Copy the template directory in the CMD toolkit to xorm_ Under models, in the file directory GitHub. Com \ go xorm \ CMD \ xorm \ templates \ goxorm

Go uses xorm to operate MySQL

Config is the generated configuration information, and struct.go.tpl is the data template, which allows customization. You can modify the template according to your project requirements. Generally, it does not need to be modified.

Go uses xorm to operate MySQL

3. Open the CMD command line window and enter xorm_ In the models directory, execute the reverse command: xorm reverse [database type] [database connection string] [template directory]

  1. xormreversemysqlroot:[email protected](127.0.0.1:3305)/test?charset=utf8templates/goxorm

Go uses xorm to operate MySQL

4. The data structure code is automatically generated in xorm_ Models / models directory.

Go uses xorm to operate MySQL

We can see that two data structure files doctor with the same name as the table name are generated_ Tb.go and user_ tb.go

  1. packagemodels
  2. import(
  3. “time”
  4. )
  5. typeDoctorTbstruct{
  6. Idint`xorm:”notnullpkautoincrINT(11)”`
  7. Namestring ` xorm: “default ” comment (‘name ‘) varchar (50)”`
  8. Ageint ` xorm: “default0comment (‘age ‘) int (11)”`
  9. Sexint ` xorm: “default0comment (‘gender ‘) int (11)”`
  10. Addtimetime.Time`xorm:”DATETIME”`
  11. }

Using xorm

Xorm supports chained writing engine. Where (“age >?”, 40). Or (“name like?”, “forest%”). Orderby (“ID desc”). Find (& doclist2) also supports direct execution of SQL statements engine. SQL (“select * from doctor_tb where age >?”, 40). Find (& doclist4)

Attach a demo example of adding, deleting, modifying and querying transactions. There are comments in the code, which is easy to understand. The encapsulation of xorm is friendly. As long as you are familiar with SQL statements, you can use various keywords smoothly even without looking at the documents.

  1. packagemain
  2. import(
  3. “fmt”
  4. _”github.com/go-sql-driver/mysql”
  5. “github.com/go-xorm/xorm”
  6. “goShare/xorm_models/models”
  7. “time”
  8. )
  9. funcmain(){
  10. varengine*xorm.Engine
  11. //Connect to database
  12. engine,err:=xorm.NewEngine(“mysql”,”root:[email protected](127.0.0.1:3305)/test?charset=utf8″)
  13. iferr!=nil{
  14. fmt.Println(err)
  15. return
  16. }
  17. //Connection test
  18. iferr:=engine.Ping();err!=nil{
  19. fmt.Println(err)
  20. return
  21. }
  22. Define. Close() / / delay closing the database
  23. Fmt.println (“database link succeeded”)
  24. //Query a single piece of data
  25. vardocmodels.DoctorTb
  26. b,_:= Engine. Where (“name =?”, Zhong Nanshan “). Get & doc
  27. ifb{
  28. fmt.Println(doc)
  29. }else{
  30. Fmt.println (“data does not exist”)
  31. }
  32. //Method 2 of querying a single piece of data will be based on the structure
  33. DOC2: = models. Doctortb {Name: “Zhong Nanshan”}
  34. b,_=engine.Get(&doc2)
  35. fmt.Println(doc2)
  36. //New data
  37. Doc3: = models. Doctortb {0, “Dr. Wang”, 48,1, time. Now()}
  38. i3,_:=engine.InsertOne(doc3)
  39. FMT. Println (“new result:”, I3)
  40. //Query list
  41. docList:=make([]models.DoctorTb,0)
  42. Engine. Where (“age >? Ornamelike?”, 40, “forest%”). Find (& doclist)
  43. fmt.Println(“docList:”,docList)
  44. //Query list method 2
  45. docList2:=make([]models.DoctorTb,0)
  46. Engine. Where (“age >?”, 40). Or (“namelike?”, “forest%”). Orderby (“iddesc”). Find (& doclist2)
  47. fmt.Println(“docList2:”,docList2)
  48. //Query paging
  49. docList3:=make([]models.DoctorTb,0)
  50. Page: = 0 / / page index
  51. PageSize: = 2 / / data per page
  52. limit:=pageSize
  53. start:=page*pageSize
  54. Totalcount, err: = engine. Where (“age >? Ornamelike?”, 40, “forest%”). Limit (limit, start). Findandcount (& doclist3)
  55. FMT. Println (“total records:”, totalcount, “doclist3:”, doclist3)
  56. //Query directly with statements
  57. docList4:=make([]models.DoctorTb,0)
  58. engine.SQL(“select*fromdoctor_tbwhereage>?”,40).Find(&docList4)
  59. fmt.Println(“docList4:”,docList4)
  60. //Delete
  61. Docdel: = models. Doctortb {Name: “Dr. Wang”}
  62. iDel,_:=engine.Delete(&docDel)
  63. FMT. Println (“delete result:”, idel)
  64. //Delete method 2
  65. engine.Exec(“deletefromdoctor_tbwhereId=?”,3)
  66. //Update data
  67. Doc5: = models. Doctortb {Name: “Dr. Zhong”}
  68. //Change the name of the record with update data ID 2 to “Dr. Zhong”
  69. iUpdate,_:=engine.Id(2).Update(&doc5)
  70. FMT. Println (“update result:”, iupdate)
  71. //Specify a table name query. Table()
  72. user:=models.UserTb{Id:2}
  73. b,_=engine.Table(“user_tb”).Get(&user)
  74. fmt.Println(user)
  75. //Business
  76. session:=engine.NewSession()
  77. defersession.Close()
  78. err=session.Begin()
  79. _,err=session.Exec(“deletefromdoctor_tbwhereId=?”,6)
  80. iferr!=nil{
  81. session.Rollback()
  82. return
  83. }
  84. _,err=session.Exec(“deletefromuser_tbwhereId=?”,10)
  85. iferr!=nil{
  86. session.Rollback()
  87. return
  88. }
  89. err=session.Commit()
  90. iferr!=nil{
  91. return
  92. }
  93. Fmt.println (“transaction executed successfully”)
  94. }

summary

The use process is summarized as follows:

1. Download xorm package and CMD Toolkit

2. Copy the template code folder in the CMD toolkit to the generation directory

3. Use reverse to generate data structure code to save hard work

4. Instance engine xorm. Newengine ()

5. Happy call

The demo provides the contents of adding, deleting, changing, checking a single piece of data, checking lists, checking pages, transactions, etc. commonly used in our development business. If the article is useful to you, please like it.

Recommended Today

Vue、Three. JS implementation panorama

1、 First, we need to create a Vue project This paper mainly records the process of building panorama in detail, so building Vue project is not described too much. 2、 Install three js npm install three –save npm install three-trackballcontrols –save npm install three-orbit-controls –save npm i three-obj-mtl-loader –save npm i three-fbx-loader –save npm i […]