This time, we will learn how to use Golang to connect to MySQL database, and use Golang to implement DATABASE CRUD operations.

Set up the environment

First we create a Golang project and configure the project GOPATH. For this step, you can refer to my blog Golang environment Installation &IDEA to develop Golang.

Since we are using a MySQL database, we need to get Golang’s MySQL database driver. We run the go get command in the GOPATH directory to obtain the MySQL driver. After the command is successfully executed, we will directly download the MySQL driver package from the Internet to the SRC directory in your GOPATH directory.

go get -u github.com/go-sql-driver/mysql
Copy the code

Once the Golang project environment is set up, we need to create a database table.

CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(11) DEFAULT NULL,
  `password` varchar(11) DEFAULT NULL.PRIMARY KEY (`id`),
  KEY `nameindex` (`name`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

The project structure

Write structure

To make it easy to encapsulate the data retrieved from the database, we create a Golang structure to encapsulate our data. We create a bean package and then create a user.go file under that package.

user.go

package bean

type User struct {
	id int
	name string
	password string
}

func (user *User) GetId(a) int {
	return user.id
}

func (user *User) SetId(id int) {
	user.id = id
}

func (user *User) GetName(a) string {
	return user.name
}

func (user *User) SetName(name string) {
	user.name = name
}

func (user *User) GetPassword(a) string {
	return user.password
}

func (user *User) SetPassword(password string) {
	user.password = password
}
Copy the code

Write database connection tools

Because we need to get a database connection every time we connect to the database, we simply encapsulate the database connection operation as a method so that we don’t have to get a connection every time. We create an util package and create an initdb.go file under the package.

initdb.go

package util

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

// Let's start by defining database configuration information as constants
const (
	userName = "root"
	password = "admin"
	ip       = "127.0.0.1"
	port     = "3306"
	dbName   = "db_database08"
)

// Initialize the database connection and return a pointer reference to the database connection
func InitDB(a) *sql.DB {
	//Golang data connection: "username: password @tcp(IP: port number)/ database name? charset=utf8"
	path := strings.Join([]string{userName, ":", password, "@tcp(", ip, ":", port, "/", dbName, "? charset=utf8"}, "")
	// Open the database, the former is the driver name, so import: _ "github.com/go-sql-driver/mysql"
	db, err := sql.Open("mysql", path)
	iferr ! =nil {
		// If the database fails to open, panic occurs
		panic(err)
	}
	// Set the maximum number of database connections
	db.SetConnMaxLifetime(10)
	// Set the maximum number of idle connections to the database
	db.SetMaxIdleConns(5)
	// Verify the connection
	iferr := db.Ping(); err ! =nil {
		panic(err)
	}
	// Return the pointer reference to the database connection
	return db
}
Copy the code

CRUD operations

Insert operations

We create an insert package, under which we create an insert. Go file

package main

import (
	"fmt"
	"mysql/util"
)

func main(a) {
	// Use the tool to get the database connection
	db := util.InitDB()
	// Start the transaction
	tx, err := db.Begin()
	iferr ! =nil {
		// The transaction fails to start and panic occurs
		panic(err)
	}
	// Prepare the SQL statement
	sql := "insert into tb_user (`name`, `password`) values (? ,?) "
	// Preprocess SQL statements
	stmt, err := db.Prepare(sql)
	iferr ! =nil {
		panic(err)
	}
	result, err := stmt.Exec("Abudori"."123")
	iferr ! =nil {
		// Failed to execute SQL
		panic(err)
	}
	// Commit the transaction
	tx.Commit()
	// Returns the id of the inserted record
	fmt.Println(result.LastInsertId())
}
Copy the code

Select the operating

We create a select package, under which we create a select.go file

package main

import (
	"fmt"
	"mysql/bean"
	"mysql/util"
)

func main(a) {
	// Use the tool to get the database connection
	db := util.InitDB()
	// Prepare the SQL statement
	sql := "select * from tb_user"
	// Preprocess SQL statements
	stmt, err := db.Prepare(sql)
	iferr ! =nil {
		panic(err)
	}
	rows, err := stmt.Query()
	iferr ! =nil {
		// Failed to execute SQL
		panic(err)
	}
	var users []bean.User
	for rows.Next() {
		var id int
		var name, password string
		err := rows.Scan(&id, &name, &password)
		iferr ! =nil {
			// Failed to read the result set
			panic(err)
		}
		var user bean.User
		user.SetId(id)
		user.SetName(name)
		user.SetPassword(password)
		users = append(users, user)
	}
	fmt.Println(users)
}
Copy the code

The Update operation

We create an update package, under which we create an update.go file

package main

import (
	"mysql/util"
)

func main(a) {
	// Use the tool to get the database connection
	db := util.InitDB()
	// Start the transaction
	tx, err := db.Begin()
	iferr ! =nil {
		// The transaction fails to start and panic occurs
		panic(err)
	}
	// Prepare the SQL statement
	sql := "update tb_user set `password` = ? where `id` = ?"
	// Preprocess SQL statements
	stmt, err := db.Prepare(sql)
	iferr ! =nil {
		panic(err)
	}
	_, err = stmt.Exec("789".1)
	iferr ! =nil {
		// Failed to execute SQL
		panic(err)
	}
	// Commit the transaction
	tx.Commit()
}
Copy the code

The Delete operation

We create a delete package, under which we create a delete.go file

package main

import (
	"mysql/util"
)

func main(a) {
	// Use the tool to get the database connection
	db := util.InitDB()
	// Start the transaction
	tx, err := db.Begin()
	iferr ! =nil {
		// The transaction fails to start and panic occurs
		panic(err)
	}
	// Prepare the SQL statement
	sql := "delete from tb_user where `id` = ?"
	// Preprocess SQL statements
	stmt, err := db.Prepare(sql)
	iferr ! =nil {
		panic(err)
	}
	_, err = stmt.Exec(1)
	iferr ! =nil {
		// Failed to execute SQL
		panic(err)
	}
	// Commit the transaction
	tx.Commit()
}
Copy the code