GO web development combat 2, database related

Last time, xdm shared the simple method in the http package in GO web development, and there is a template at the end, I will add it here

Go directly to the case

template

  • We write a server-side demo in main.go, and use templates to write winter data into html files
  • Correspondingly, you need to write an html file and set the data location that needs template injection

main.go

package main

import (
    "fmt"
    "html/template"
    "net/http"
)

var myTemp *template.Template

type Person struct {
    Name  string
    Title string
    Age   int
}

//Start processing data and responding
func userInfo(w http.ResponseWriter, req *http.Request) {
    defer req.Body.Close()

    var pp []Person
    // simple simulated data
    p1 := Person{
        Name:  "xmt 1 No",
        Title: "1 No. Visualizer",
        Age:   18,
    }
    p2 := Person{
        Name:  "xmt 2 No",
        Title: "2 No. Visualizer",
        Age:   15,
    }
    p3 := Person{
        Name:  "xmt 3 No",
        Title: "3 No. Visualizer",
        Age:   29,
    }

    pp = append(pp, p1, p2, p3)

    // write data to the template
    err := myTemp.Execute(w, pp)
    if err != nil {
        fmt.Println("Execute err ;%v", err)
        return
    }

}

func main() {

    //Initialize the template
    var err error
    myTemp, err = template.ParseFiles("./index.html")
    if err != nil {
        fmt.Println("ParseFiles err ;%v", err)
        return
    }

    //Register a function that processes templates and enable listening
    http.HandleFunc("/", userInfo)
    err = http.ListenAndServe("0.0.0.0:9999", nil)
    if err != nil {
        fmt.Println("ListenAndServe err ;%v", err)
        return
    }

}

index.html

<html>
    <head>

    </head>
    <body>
        <p>hello world</p>
        <table border="1" align="center" width="600px">
            <tr>
                <td>{{.Name}}</td> <td>{{.Age}}</td><td>{{.Title}}</td>
            </tr>
        </table>
    </body>
</html>

The above code is also relatively simple. You can start the server by running main.go directly. We only need to access it in the browser.

http://localhost:8888/

You can see the effect of our html display, the data is dynamic

In addition, let's take a look at the mysql database used in go web

Mysql

Connect to the database

To operate the database, the basic steps are as follows

  • First open, then ping, you must be able to connect to the MySQL database after the ping passes
  • To write mysql code, you must import this package _ "github.com/go-sql-driver/mysql", you need to execute the init function in mysql first
  • Remember to fill in your mysql password here. If you think the password is hard to remember, you can set the password to 123456 for learning and practice

    package main
    
    import (
        "database/sql"
        "fmt"
    
        _ "github.com/go-sql-driver/mysql" // Exception after commenting out _ call initialization function
    
    )
    
    func main() {
        //Open the mysql database to connect, you must ping to connect to the mysql database
        db, err := sql.Open("mysql", "root:xxxxxx@tcp(127.0.0.1:3306)/go_test?charset=utf8mb4")
        if err != nil {
            fmt.Println("Open err : ", err)
            return
        }
    
        err = db.Ping()
        if err != nil {
            fmt.Println("Ping err : ", err)
            return
        }
        db.Close()
    }

Here we can see charset=utf8mb4 , here is to set the character encoding format to utf8mb4 , mainly for

What needs to be said here is that basically our current mysql encoding settings are all set to utf8mb4 character set because it supports 4-byte Unicode characters

When Unicode was not perfect in the early days, UTF8 was used, which only needed a maximum of 3 bytes to represent Unicode characters

Increase

Do data addition operations and insert data into the database

  • Here we use placeholders to insert data
  • Fill in the standard sql statement in the sqlInfo variable
func insertPiceInfo(db *sql.DB) {
    // ?  as a placeholder
    sqlInfo := "insert into user(name,age)values(?,?)"
    ret, err := db.Exec(sqlInfo, "xxx", 19)
    if err != nil {
        fmt.Println("Exec err : ", err)
        return
    }

    //The id of the inserted data
    id, err := ret.LastInsertId()
    if err != nil {
        fmt.Println("LastInsertId err : ", err)
        return
    }

    fmt.Println("LastInsertId == ", id)

    //The number of rows affected by this operation
    rows, err := ret.RowsAffected()
    if err != nil {
        fmt.Println("RowsAffected err : ", err)
        return
    }
    fmt.Println("rows == ", rows)

}

After executing the insert statement, you can get the id of the successfully inserted data and the number of rows affected by the inserted data through the inserted result

delete

Deleting data is relatively simple. Similarly, we can get the result of deleting data to obtain the number of rows affected by it, etc.

func deletePiceInfo(db *sql.DB) {
    // ?  as a placeholder
    sqlInfo := "delete from user where id=  xx "
    ret, err := db.Exec(sqlInfo)
    if err != nil {
        fmt.Println("Exec err : ", err)
        return
    }

    //The number of rows affected by this operation
    rows, err := ret.RowsAffected()
    if err != nil {
        fmt.Println("RowsAffected err : ", err)
        return
    }
    fmt.Println("rows == ", rows)

}

Revise

func updatePiceInfo(db *sql.DB) {
    // ?  as a placeholder
    sqlInfo := "update user set name='xxx' where id=xx"
    ret, err := db.Exec(sqlInfo)
    if err != nil {
        fmt.Println("Exec err : ", err)
        return
    }

    //The number of rows affected by this operation
    rows, err := ret.RowsAffected()
    if err != nil {
        fmt.Println("RowsAffected err : ", err)
        return
    }
    fmt.Println("rows == ", rows)

}

Look, the modification operation is similar to other addition and deletion operations, and the writing method is basically the same, and it is relatively simple

Inquire

For query operations, there should be relatively more operations used in database operations. If go operates mysql queries, there are simply two points to note:

  • The rows obtained after Query need to remember to close
  • After calling the query data, you need to remember to call the Scan method immediately, otherwise the held database connection will not be released
type myInfo struct{
    id int
    name string
    age int
}

func selectInfo(db *sql.DB) {
    sqlInfo := "select * from user"
    rows, err := db.Query(sqlInfo)
    if err != nil {
        fmt.Println("Exec err : ", err)
        return
    }
    // VERY IMPORTANT: Closing rows releases held database connections
    defer rows.Close()
    //Output the number of rows queried

    for rows.Next(){
        var u myInfo
        rows.Scan(&u.id,&u.name,&u.age)
        fmt.Printf("id = %d, name = %s, age = %d\n",u.id,u.name,u.age)
    }

}

Welcome to like, follow and collect

Friends, your support and encouragement is my motivation to keep sharing and improve quality

Ok, that's it for this time

Technology is open, and our mentality should be even more open. Embrace change, live in the sun, and strive to move forward.

I am A Bing Yunyuan, welcome to like, follow and collect, see you next time~

Tags: Web Development Go http

Posted by searchman on Sun, 29 Jan 2023 01:38:00 +1030