ORM framework

ORM: Object Relational Mapping

Function:
- By manipulating structure objects, to achieve the purpose of manipulating database tables.
- Generate a database table from a structure object.
Advantages:
- SQL can be complex. (Oracle — subquery — nested) ORM operates on the database without using SQL
- Different developers write SQL statements with different execution efficiency.
ORM supported by go:
- GORM: gORM Chinese document
- XORM:

Gorm connects to databases and builds tables

Review the MySQL

Confirm mysql service start: ps xua | grep mysql
Connect to MySQL database:mysql -uroot -p
View database:show databases;
Delete database:drop database t1;
Select database, view table:Use Database name Show tables;
Create database:Create database name charset=utf8;

Create a table

Connecting to a Database

import (
	"github.com/jinzhu/gorm"
	_ "github.com/go-sql-driver/mysql"   // the "_" code does not use packages directly, the underlying links are used!
	"fmt"
)
// mysql: driver name of the database
// Link database -- format: username: password @ protocol (IP:port)/ database name? xxx&yyy&
conn, err := gorm.Open("mysql"."Root: 123456 @ TCP (127.0.0.1:3306)/test")
iferr ! =nil {
    fmt.Println("gorm.Open err:",err)
    return
}
defer conn.Close()
Copy the code

Create database tables. You cannot create a database using GORM. Use SQL statements in advance to create the database you want. AutoMigrate() creates the table. The default created table is complex. Before creating, add conn.singularTable (true) to create tables with non-plural table names.
```
// Do not use plural table names
conn.SingularTable(true)

// Create database tables with GORM.
fmt.Println(conn.AutoMigrate(new(Student)).Error)
Copy the code
```

To view

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

Copy the code

MySQL package init method

_ "github.com/go-sql-driver/mysql"When importing a package, the “_”, which means, drive the go system to automatically call init() before the main() function is called.
There are two special functions in go: — lowercase, visible outside the package
1. Main () – The entry function for the project
2. Init () — when guiding a package, but not used in a program. Automatically called before main() is called.
  - View: cursor placed on MySQL package ‘MySQL’. Use ctrl-left mouse button. See the source code. Include the definition of the init() function at the bottom of driver.go.
  - Init () is used to register MySQL drivers.

Gorm connection pool

By default, the GORM framework creates the MySQL database connection conn, which is a connection pool handle. conn, err := gorm.Open("mysql"."Root: 123456 @ TCP (127.0.0.1:3306)/test")
Copy the code

Initializes a global variable and receives a handle

// Create a global connection pool handle
var GlobalConn *gorm.DB
GlobalConn = conn
Copy the code

Modify the initial connection pool properties

/ / the initial number
GlobalConn.DB().SetMaxIdleConns(10)
/ / maximum number
GlobalConn.DB().SetMaxOpenConns(100)
Copy the code

Use the connection pool handle

Redis connection pool: no need to use Get() method, take a connection.// Do not use plural table names
GlobalConn.SingularTable(true)
// Create database tables with GORM.
fmt.Println(GlobalConn.AutoMigrate(new(Student)).Error)
Copy the code

The MySQL 8 hour time zone is faulty

MySQL default time: EAST 8 region time. — Beijing time — eight hours short.

When connecting to the database, add the following properties:? parseTime=True&loc=Local

conn, err := gorm.Open("mysql”,"root:123456@tcp(127.0. 01.:3306)/test? parseTime=True&loc=Local")
Copy the code

Then execute SQL statement, GORM access MySQL using Beijing time.

Gorm manipulates database data

Gorm inserts data

// insert into student(name, age) values('zhangsan', 100)

func InsertData(a)  {
	// Create data first -- create object
	var stu Student
	stu.Name = "zhangsan"
	stu.Age = 100

	// Insert (create) data
	fmt.Println(GlobalConn.Create(&stu).Error)
}
Copy the code

Precautions for use:

The create() function used when inserting data, and the & object must be passed when passing arguments. An error is reported if the ampersand is omitted
Make sure that when you insert into the database,GlobalConn.SingularTable(true)To take effect. Indicates that the plural table name is not used.

Gorm queries data

Simple query method:
- First(& User): Gets the First data in the user table
```
func SearchData(a)  {
	var stu Student
	GlobalConn.First(&stu)
	fmt.Println(stu)
}
Copy the code
```
  SQL: SELECT * FROM student ORDER BY id LIMIT 1; Select * from name, age; select * from age;
```
GlobalConn.Select("name, age").First(&stu)
Copy the code
```
- SQL: SELECT * FROM users ORDER BY id DESC LIMIT 1 SELECT * FROM users ORDER BY id DESC LIMIT 1
- Find(&user): Gets all data in the user table.
```
var stu []Student		// Change to slice
GlobalConn.Select("name, age").Find(&stu)   // Find() queries multiple entries
Copy the code
```
- SQL: select name, age from student;
Use the WHERE clause

select name, age from student where name = 'lisi';

GlobalConn.Select("name, age").Where("name = ?"."lisi").Find(&stu)
Copy the code

select name, age from student where name = 'lisi' and age = 22;

// Method 1:
GlobalConn.Select("name, age").Where("name = ?"."lisi").
			Where("age = ?".22).Find(&stu)
// Method 2:
GlobalConn.Select("name, age").Where("name = ? and age = ?"."lisi".22).Find(&stu)
Copy the code

Gorm updates data

Save(): Updates by primary key. If no primary key is specified for the data, the data is not updated and the operation becomes Insert.

func UpdateData(a)  {
    var stu Student
    stu.Name = "wangwu"
    stu.Age = 99	// No id specified -- no primary key! - insert
    fmt.Println(GlobalConn.Save(&stu).Error)
}
Copy the code

func UpdateData(a)  {
    var stu Student
    stu.Name = "wangwu"
    stu.Age = 77
    stu.Id = 4		// Specify id -- update operation!
    fmt.Println(GlobalConn.Save(&stu).Error)
}
Copy the code

Update(): Updates a field.

fmt.Println(GlobalConn.Model(new(Student)).Where("name = ?"."zhaoliu").
            Update("name"."lisi").Error)
// Model(new(Student): specifies to update the "Student" table
Copy the code

Updates(): Updates multiple fields.

fmt.Println(GlobalConn.Model(new(Student)).Where("name = ?"."lisi").
            Updates(map[string]interface{} {"name":"liuqi"."age":24}).Error)
Copy the code

Grom deletes data

Delete: Physical deletion. Actually perform Delete.
Soft delete: a logical delete. Not really. Delete is not performed.
- When you create a table, add a Delete field to the table. When you need to delete, update the “Delete field” to true
- The value whose Delete field is null is not queried.

Create a table:

// Create a global structure
type Student struct {
      gorm.Model	// In go, anonymous members inherit! Student inheritance Model
      Name string
      Age int
}
// On Model, Ctrl-b jumps to the Model class definition.
type Model struct {
      ID        uint `gorm:"primary_key"`
      CreatedAt time.Time
      UpdatedAt time.Time
      DeletedAt *time.Time `sql:"index"`
}
// The Model table is automatically maintained by mysql.
Copy the code

Perform soft delete:

// Use the Delete() argument to specify the name of the table where the data to be deleted resides.
fmt.Println(GlobalConn.Where("name = ?"."lisi").Delete(new(Student)).Error)
Copy the code

Validation:
1. Select * from student; You can still see “LISi” data. But. Delete_at fields. Data is filled in.
2. In the GORM framework, execute the query statement:
```
func SearchData(a)  {
    varStu []Student globalconn. Find(&stu) fmt.println (stu)} -- query result is: [] ----"Soft delete"Success!Copy the code
```

To query soft delete data:

GlobalConn.Unscoped().Find(&stu)
Copy the code

Want to implement “physical delete”

-- Delete with Unscoped(). GlobalConn.Unscoped().Where("name = ?"."lisi").Delete(new(Student))
Copy the code

Gorm sets table properties

Modify table field size

// Create a global structure
type Student struct {
	Id    int
        // string -- varchar. The default size is 255. You can specify the size when creating the table.
	Name  string `gorm:"size:100; default:'xiaoming'"`
	Age   int
	Class int    `gorm:"not null"`
}
Copy the code

Conclusion: Modifying table attributes only works when the table is first created. Or add a new field to the table, valid! Other scenarios, modify table attributes, in gorM operation, invalid!

Set a time

Default MySQL database has 3 types of times:
- Date:
- Datetime:
- TimeStamp: indicates the timeStamp. — In GORm, only timeStamp
If you must use the “data type” unique to the MySQL database, use the “type” keyword to set it.

// Create a global structure
type Student struct {
	Id    int
	Name  string    `gorm:"size:100; default:'xiaoming'"`
	Age   int
	Class int       `gorm:"not null"`
	Join  time.Time `gorm:"type:timestamp"`// Create table Student with timestamp type.
}
Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

GORM MySQL operation

ORM framework

Gorm connects to databases and builds tables

Review the MySQL

Create a table

MySQL package init method

Gorm connection pool

The MySQL 8 hour time zone is faulty

Gorm manipulates database data

Gorm inserts data

Gorm queries data

Gorm updates data

Grom deletes data

Gorm sets table properties

Modify table field size

Set a time

GORM MySQL operation

ORM framework

Gorm connects to databases and builds tables

Review the MySQL

Create a table

MySQL package init method

Gorm connection pool

The MySQL 8 hour time zone is faulty

Gorm manipulates database data

Gorm inserts data

Gorm queries data

Gorm updates data

Grom deletes data

Gorm sets table properties

Modify table field size

Set a time

Related Posts

SpringMVC – Receives request parameters and data echo

Spring Boot 2 x tutorial series | AOP aspects unified print request logs

Spring Boot Tutorial – Elasticsearch