Go MySQL
To connect Database is one of important part for Web Application.
Get user data, item data, etc… There are a lot of cases to get and create data base data.
In the past entry, explained how to connect MySQL
Steps
To support MySQL Connect, we have multiple prepartions.
- Prepare MySQL database
- Create Table
- Get go driver
- Prepare connection codes
- Prepare handler function for end point
- Call connection from main.go
MySQL prepartion
Create MySQL Database under this condition
User | root |
Password | abc4po@g |
DB Name | gosample |
Port | 3306 |
And, Insert following table and data
CREATE TABLE `gosample`.`posts` ( `id` INT NOT NULL AUTO_INCREMENT, `content` VARCHAR(255) NULL, `author` VARCHAR(255) NULL, PRIMARY KEY (`id`)); INSERT INTO posts (content, author) VALUES ('ABDCE', 'Mumu-man'); INSERT INTO posts (content, author) VALUES ('teketeke', 'Takeyan');
MySQL Driver
Go prepares several RDBMS driver.
Please check this for SQL drivers
To use MySQL driver, you need to get using
go get -u github.com/go-sql-driver/mysql
Connection codes
Let’s prepare connection codes.
mysqlweb.go
package web import ( "database/sql" "fmt" "log" "time" ) var DbConn *sql.DB type connectionstr struct { User string Password string Host string Port int DBName string } func SetupDatabase() { var err error connection := &connectionstr{"root", "abc4po@g", "localhost", 3306, "gosample"} DbConn, err = sql.Open("mysql", fmt.Sprintf("%s:%s@tcp(%s:%d)/%s", connection.User, connection.Password, connection.Host, connection.Port, connection.DBName)) if err != nil { log.Fatal(err) } DbConn.SetMaxOpenConns(4) // Sets the maximum number of open connections to the database DbConn.SetMaxIdleConns(4) // Sets the maximum number of connections in the idle connection pool DbConn.SetConnMaxLifetime(60 * time.Second) // Sets the maximum amount of time a connection may be used
Now, DbConn is connection to use MySQL data.
Last 4 lines are setting of connection and connection pooling.
main.go
Let’s use above connection from main function.
package main import ( "fmt" "net/http" "github.com/DJ110/gorefs/web" _ "github.com/go-sql-driver/mysql" ) func main() { // MySQL web.SetupDatabase() }
It’s just call above function from main(). And need to import driver.
Next is handler for SQL.
sqlweb.go
package web import ( "context" "encoding/json" "fmt" "log" "net/http" "time" ) type Posts struct { Id int Content string Author string } func SelectSQL(w http.ResponseWriter, _ *http.Request) { results, err := DbConn.Query(`select * from posts`) if err != nil { log.Fatal(err) } defer results.Close() list := []Posts{} for results.Next() { var post Posts results.Scan(&post.Id, &post.Content, &post.Author) fmt.Println(post) list = append(list, post) } data, err := json.Marshal(list) if err != nil { fmt.Println("json marshal error") return } w.Write([]byte(data)) } func UpdateSQL(w http.ResponseWriter, _ *http.Request) { result, err := DbConn.Exec(`UPDATE posts SET author=? WHERE id=?`, "Dokkan", 1) if err != nil { log.Fatal(err) } row, err := result.RowsAffected() if err != nil { log.Fatal(err) } fmt.Printf("number of affected rows %d", row) } func SQLWithContext(w http.ResponseWriter, _ *http.Request) { ctx, cancel := context.WithTimeout(context.Background(), 15*time.Second) defer cancel() row, err := DbConn.QueryContext(ctx, `select * from posts`) if err != nil { log.Fatal(err) } defer row.Close() list := []Posts{} for row.Next() { var post Posts row.Scan(&post.Id, &post.Content, &post.Author) list = append(list, post) } data, err := json.Marshal(list) if err != nil { fmt.Println("json marshal error") return } w.Write([]byte(data)) }
コメント