Go connect to the database (simple query learning)

go Connect to database ( Simple query learning )

One 、go Bring their own

1. Download dependency

go get -u github.com/go-sql-driver/mysql

2. insert data

package main

import (
	"database/sql"
	"fmt"
	// Don't forget to import the driver 
	_ "github.com/go-sql-driver/mysql"
)

func main() {
    

	//1. Open the connection 
	//driverName： Drive connection ,go No longer in com.mysql.jbdc.... It is "mysql"
	// data source ： Fixed format , user name : password @tcp(ip:port)/ database 
	db, err := sql.Open("mysql", "root:[email protected](localhost:3306)/ssmbuild")
	// Don't forget this sentence , This is the real way to open the database 
	db.Ping()
	// Pay attention to closing resources , And use defer keyword , Finally close ,defer Equivalent to a stack 
	defer func() {
    
		if db != nil {
    
			db.Close()
		}
	}()
	if err != nil {
    
		fmt.Println(" Database connection failed ！")
		return
	}
	//2. Preprocessing sql
	//"?" As a placeholder , amount to JDBC Medium preparedStatement
	stm, err := db.Prepare("insert into books values(default ,?,?,?)")
	// Pay attention to closing resources , And use defer keyword , Finally close ,defer Equivalent to a stack 
	defer func() {
    
		if stm != nil {
    
			stm.Close()
		}
	}()
	if err != nil {
    
		fmt.Println(" Preprocessing failed ")
		return
	}
	//Exec()： Indefinite length parameter , One to one correspondence of parameters and placeholders 
	res, err := stm.Exec("go", 1, "go Language from entry to grave ")
	if err != nil {
    
		fmt.Println("sql Execution failure ")
		return
	}
	//3. To get the results 
	// Number of rows affected , The influence line is 1, influence n Line is n
	count, err := res.RowsAffected()
	if err != nil {
    
		fmt.Println(" The result acquisition failed ")
		return
	}
	if count > 0 {
    
		fmt.Println(" Added successfully ")
	} else {
    
		fmt.Println(" Add failed ")
	}
    
    // Sometimes the business may need to obtain the primary key of the inserted data id
	id, err := res.LastInsertId()
	fmt.Println(id)
}

2.1 Get the primary key of the inserted data id

// Sometimes the business may need to obtain the primary key of the inserted data id
id, err := res.LastInsertId()
fmt.Println(id)

3. Update data

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
    
	//1. obtain db Connect 
	db, err := sql.Open("mysql", "root:[email protected](localhost:3306)/ssmbuild")
	// True connection 
	db.Ping()
	// Close the database 
	defer func() {
    
		if db != nil {
    
			db.Close()
		}
	}()
	if err != nil {
    
		fmt.Println(" The connection fails ")
		return
	}

	//2. obtain stm
	stm, err := db.Prepare("update books set bookCounts = ?")
	// close stm
	defer func() {
    
		if stm != nil {
    
			stm.Close()
		}
	}()
	// Judge stm Is it empty 
	if err != nil {
    
		fmt.Println(" Preprocessing failed ")
		return
	}
	// Fill in placeholders 
	res, err := stm.Exec(10)
	if err != nil {
    
		fmt.Println(" Failed to get result ")
		return
	}
	count, err := res.RowsAffected()
	if count < 0 {
    
		fmt.Println(" Update failed ")
	} else {
    
		fmt.Println(" The update is successful ")
	}
}

4. Delete data

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
    
	//1. establish db Connect 
	db, err := sql.Open("mysql", "root:[email protected](localhost:3306)/ssmbuild")
	// Judge err Is it empty 
	if err != nil {
    
		fmt.Println(" The connection fails ")
		return
	}
	// close resource 
	defer func() {
    
		db.Close()
	}()
	//2. Preprocessing 
	stm, err := db.Prepare("delete from books where bookName = ?")
	// Determine whether it is null 
	if err != nil {
    
		fmt.Println(" Preprocessing failed ")
		return
	}
	// close resource 
	defer func() {
    
		stm.Close()
	}()
	// Fill in placeholders 
	res, err := stm.Exec("go")
	// Sentenced to empty 
	if err != nil {
    
		fmt.Println(" Result error ")
		return
	}
	// Get the number of rows affected 
	count, err := res.RowsAffected()
	if count < 0 {
    
		fmt.Println(" Delete failed ")
	} else {
    
		fmt.Println(" Delete successful ")
	}
}

5. Query data

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
    
	//1. establish db Connect 
	db, err := sql.Open("mysql", "root:[email protected](localhost:3306)/ssmbuild")
	// Sentenced to empty 
	if err != nil {
    
		fmt.Println(" The connection fails ")
		return
	}
	// close resource 
	defer func() {
    
		db.Close()
	}()
	// The real opening 
	db.Ping()

	//2. Preprocessing （ Inquire about ）
	stm, err := db.Prepare("select * from books")
	// Sentenced to empty 
	if err != nil {
    
		fmt.Println(" Preprocessing failed ")
		return
	}
	// close resource 
	defer func() {
    
		stm.Close()
	}()

	//3. Get query results 
	rows, err := stm.Query()
	// Sentenced to empty 
	if err != nil {
    
		fmt.Println(" The query fails ")
		return
	}
	// close resource 
	defer func() {
    
		if rows != nil {
    
			rows.Close()
		}
	}()
	// Loop through the result 
	for rows.Next() {
    
		var bookId, bookCount int
		var bookName, detail string
		// Assign values respectively 
		rows.Scan(&bookId, &bookName, &bookCount, &detail)
		fmt.Println(bookId, bookName, bookCount, detail)
	}
}

Two 、GORM

1. Download dependency

go get -u github.com/jinzhu/gorm

2. Connect to database (mysql)

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)
func main() {
    
	// Define connection information , user name 、 password 、 Communication protocol and other information 
	url := "root:[email protected](localhost:3306)/ssmbuild?charset=utf8&parseTime=True&loc=Local"
	// Connect to database , When connecting to the database , Some advanced configurations can be added , Namely gorm.Config Parameters in 
	_, err := gorm.Open(mysql.Open(url), &gorm.Config{
    })
	if err != nil {
    
		fmt.Println(" The connection fails ")
		return
	} else {
    
		fmt.Println(" Successful connection ")
	}
}

3. according to go Create table

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// establish user Entity , Used to create... From entities sql surface 
type User struct {
    
	UserId   int `gorm:"primary_key"`
	UserName string
	UserSex  string
}

func main() {
    
	//1. Connect to database 
	dsn := "root:[email protected](localhost:3306)/ssmbuild?charset=utf8&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
    })
	// Sentenced to empty 
	if err != nil {
    
		fmt.Println(" Database connection failed ")
		return
	}

	//2. Create table , Automatic migration ( Correspond the structure to the data table )
	//db.AutoMigrate(&books{})
	db.AutoMigrate(&User{
    })
}

4. insert data

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// establish user Entity , Used to create... From entities sql surface 
type User struct {
    
	UserId   int `gorm:"primary_key"`
	UserName string
	UserSex  string
}

func main() {
    
	//1. Connect to database 
	dsn := "root:[email protected](localhost:3306)/ssmbuild?charset=utf8&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
    })
	// Sentenced to empty 
	if err != nil {
    
		fmt.Println(" Failed to connect to database ")
		return
	}

	//2. insert data 
	user := User{
    1, " Zhang San ", " male "}
	db.Create(user)
}

5. Query data

5.1 First&Take&Last

#First()： Sort in ascending order through the primary key , Get the first data 
select * from table order by id limit 1
#Task()： Do not sort by column , Get the first data directly 
select * from table limit 1
#Last()： Sort in descending order through the primary key , Get the first data 
select * from table order by id desc limit 1

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// establish books Entity 
type Books struct {
    
	// Be careful ： When the entity and database names are inconsistent , Alias is needed to query 
	BookID     int    `gorm:"column:bookID",gorm:"primary_key"`
	BookName   string `gorm:"column:bookName"`
	BookCounts int    `gorm:"column:bookCounts"`
	Detail     string `gorm:"column:detail"`
}

func main() {
    
	//1. Define connection information , user name 、 password 、 Communication protocol and other information 
	url := "root:[email protected](localhost:3306)/ssmbuild?charset=utf8&parseTime=True&loc=Local"
	// Connect to database , When connecting to the database , Some advanced configurations can be added , Namely gorm.Config Parameters in 
	db, err := gorm.Open(mysql.Open(url), &gorm.Config{
    })
	if err != nil {
    
		fmt.Println(" The connection fails ")
		return
	}

	//2. Inquire about 
	var book Books
	//2.1 db.First()  Sort in ascending order of primary key , Take the first data 
	//select * from books order by bookId limit 1
	// Create a books object , Assign the found data to this object 
	db.First(&book)

	//2.2 db.Take()  Get a piece of data , No sort field specified 
	//select * from books limit 1
	//db.Take(&book)
	//fmt.Println(book)

	//2.3 db.Last()  Sort by primary key in descending order , Take the first data 
	//select * from books order by bookId desc limit 1
	db.Last(&book)
	fmt.Println(book)
}

5.2 Multi record query

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// establish books Entity 
type Books struct {
    
	// Be careful ： When the entity and database names are inconsistent , Alias is needed to query 
	BookID     int    `gorm:"column:bookID",gorm:"primary_key"`
	BookName   string `gorm:"column:bookName"`
	BookCounts int    `gorm:"column:bookCounts"`
	Detail     string `gorm:"column:detail"`
}

func main() {
    
	//1. Define connection information , user name 、 password 、 Communication protocol and other information 
	url := "root:[email protected](localhost:3306)/ssmbuild?charset=utf8&parseTime=True&loc=Local"
	// Connect to database , When connecting to the database , Some advanced configurations can be added , Namely gorm.Config Parameters in 
	db, err := gorm.Open(mysql.Open(url), &gorm.Config{
    })
	if err != nil {
    
		fmt.Println(" The connection fails ")
		return
	}

	// Multi record query , Need to use Books Array 
	books := []Books{
    }
	db.Find(&books)
	fmt.Println(books)
}

5.3 Conditions of the query

string Conditions

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// establish books Entity 
type Books struct {
    
	// Be careful ： When the entity and database names are inconsistent , Alias is needed to query 
	BookID     int    `gorm:"column:bookID",gorm:"primary_key"`
	BookName   string `gorm:"column:bookName"`
	BookCounts int    `gorm:"column:bookCounts"`
	Detail     string `gorm:"column:detail"`
}

func main() {
    
	//1. Define connection information , user name 、 password 、 Communication protocol and other information 
	url := "root:[email protected](localhost:3306)/ssmbuild?charset=utf8&parseTime=True&loc=Local"
	// Connect to database , When connecting to the database , Some advanced configurations can be added , Namely gorm.Config Parameters in 
	db, err := gorm.Open(mysql.Open(url), &gorm.Config{
    })
	if err != nil {
    
		fmt.Println(" The connection fails ")
		return
	}

	//4. Use criteria to index 
	//4.1 string Conditions 
	//4.1.1  Get the first record that meets the conditions and is arranged in ascending order of primary key 
	//var book Books
	//sql:select * from books where bookName = 'Linux' order by id limit 1
	//db.Where("bookName=?", "Linux").First(&book)
	//fmt.Println(book)
    
	//4.1.2  Get all records that meet the conditions 
	//books := []Books{}
	//sql:select * from books where bookCounts=10
	//db.Where("bookCounts", 10).Find(&books)
	//fmt.Println(books)
    
	//4.1.3  Except for records that meet the conditions , Find out all other records 
	//books := []Books{}
	select * from books where bookName <> 'Linux'
	//db.Where("bookName <> ?", "Linux").Find(&books)
	//fmt.Println(books)
    
	//4.1.4  Fuzzy query 
	// Because fuzzy query may be a piece of data , It can also be multiple pieces of data , So use an array to receive 
	//books := []Books{}
	sql:select * from books bookName like %Li%
	//db.Where("bookName like ?", "%Li%").Find(&books)
	//fmt.Println(books)
    
	//4.1.5  Multiconditional query 
	//var book Books
	//db.Where("bookName = ? and bookCounts = ?", "mysql", 10).Find(&book)
	//fmt.Println(book)
    
	//4.1.6  Use between
	books := []Books{
    }
	db.Where("bookCounts between ? and ?", 1, 20).Find(&books)
	fmt.Println(books)
}

Struct&Map Conditions

// Actually use Struct and Map Just like mybatis Use objects and map It's the same 

//5. Use Struct and Map Query conditions 
//5.1  Use Struct Inquire about 
//var book Books
//db.Where(Books{BookName: "Linux"}).Find(&book)
//fmt.Println(book)
//5.2  Use Map Inquire about 
var book Books
maps := make(map[string]string)
maps["bookName"] = "Linux"
// Direct will map Just put it in 
db.Where(maps).Find(&book)
fmt.Println(book)

Select a specific field to query

//6. Select a specific field to query 
books := []Books{
    }
db.Select("bookID", "bookName").Where("bookCounts", 10).Find(&books)
fmt.Println(books)

limit & offset

// Be careful ：
// gorm There is no limit 1,2, So you can only use offset To offset 
// limit 1,2 --》 limit 2 offset 1 


//7.limit & offset
//7.1 limit
//books := []Books{}
//sql:select * from books limit 3  Take the first 1 Data -- The first 3 Data 
//db.Limit(3).Find(&books)
// Be careful ： stay gorm Cannot be used in Limit 1,2  In this way , So we can only cooperate with offset To use 
//fmt.Println(books)
//7.2 offset & limit
books := []Books{
    }
//sql:select * from books limit 2 offset 1  Be careful ： Excluding the 1 Data 
db.Limit(2).Offset(1).Find(&books)
fmt.Println(books)

6. Delete data

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// establish books Entity 
type Books struct {
    
	// Be careful ： When the entity and database names are inconsistent , Alias is needed to query 
	BookID     int    `gorm:"column:bookID",gorm:"primary_key"`
	BookName   string `gorm:"column:bookName"`
	BookCounts int    `gorm:"column:bookCounts"`
	Detail     string `gorm:"column:detail"`
}

func main() {
    
	//1. Create connection 
	url := "root:[email protected](localhost:3306)/ssmbuild?charset=utf8&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(url), &gorm.Config{
    })
	// Sentenced to empty 
	if err != nil {
    
		fmt.Println(" The connection fails ")
		return
	}
	// Delete record 
	//1. Delete... According to the primary key 
	//sql:delete from books where id =4
	//db.Delete(Books{}, 4)
	//2. Delete... According to conditions 
	db.Where("bookName = ?", "Linux").Delete(Books{
    })
}

7. Update data

package main

import (
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// establish books Entity 
type Books struct {
    
	// Be careful ： When the entity and database names are inconsistent , Alias is needed to query 
	BookID     int    `gorm:"column:bookID",gorm:"primary_key"`
	BookName   string `gorm:"column:bookName"`
	BookCounts int    `gorm:"column:bookCounts"`
	Detail     string `gorm:"column:detail"`
}

func main() {
    
	//1. Create connection 
	url := "root:[email protected](localhost:3306)/ssmbuild?charset=utf8&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(url), &gorm.Config{
    })
	// Sentenced to empty 
	if err != nil {
    
		fmt.Println(" The connection fails ")
		return
	}
	// Update data 
	//1. Update a column 
	//db.Model(Books{}).Where("bookName", "Java").Update("bookCounts", 15)
	//2. Update multiple columns 
	//2.1  Use struct to update 
	//db.Model(Books{}).Where("bookName", "Java").Updates(Books{BookCounts: 20, Detail: "Java Dogs don't learn "})
	//2.2  Use map to update 
	db.Model(Books{
    }).Where("bookName", "MySQL").Updates(map[string]interface{
    }{
    "BookCounts": "33", "Detail": "MySQL It's too hard "})
}