gin框架web操作数据库

1. 新增数据

效果：

写入数据

执行完毕后,前台输出

后台输出

html

 1 //cat src/page/login.html
 2 
 3 <!DOCTYPE html>
 4 <html lang="en">
 5 <head>
 6     <meta charset="UTF-8">
 7     <title>添加学生信息</title>
 8 
 9 </head>
10 <body>
11 <form action="http://127.0.0.1:8080/form" method="POST" enctype="application/x-www-form-urlencoded">
12 
13 
14     <div>
15 
16         <label>学号:
17             <input type="text" name="no">
18         </label>
19     </div>
20 
21     <div>
22 
23         <label> 姓名:
24             <input type="text" name="name">
25         </label>
26     </div>
27 
28     <div>
29 
30         <label> 成绩:
31             <input type="number" name="score">
32         </label>
33     </div>
34 
35 
36 
37 
38     <div>
39 
40         <input type="submit" value="提交">
41     </div>
42 
43 
44 
45 </form>
46 </body>
47 </html>

  1 //cat src/main/main.go
  2 
  3 package main
  4 
  5 import (
  6     "database/sql"
  7     "fmt"
  8     "github.com/gin-gonic/gin"
  9     _ "github.com/go-sql-driver/mysql"
 10     "log"
 11     "strings"
 12 )
 13 
 14 //定义一个和表对应的结构体
 15 type Student struct {
 16     id    int
 17     no    string //学号
 18     name  string //姓名
 19     score uint   //成绩
 20 }
 21 
 22 func main() {
 23 
 24     //1. 创建路由
 25     r := gin.Default()
 26 
 27     //2.
 28     r.POST("/form", func(c *gin.Context) {
 29 
 30         //表单参数设置默认值
 31         type1 := c.DefaultPostForm("type", "alert")
 32 
 33         //接收username,password
 34         no := c.PostForm("no")
 35         name := c.PostForm("name")
 36         score := c.PostForm("score")
 37 
 38         //DATABASE BASH
 39         db, err := InitDatabase()
 40         defer db.Close()
 41         if err != nil {
 42             log.Println(err)
 43             return
 44         }
 45         ////增
 46         insertSql := "insert into student(no, name, score) values(?, ?, ?)"
 47         err = Execute(db, insertSql, no, name, score)
 48         if err != nil {
 49             log.Printf("insert data error : %v
", err)
 50             return
 51         }
 52 
 53         //查
 54         querySql := "select id, no, name, score from student where name = ?"
 55         rows, err := QueryData(db, querySql, "Jack")
 56         defer rows.Close()
 57         if err != nil {
 58             log.Printf("query data error:%v
", err)
 59             return
 60         }
 61         s := new(Student)
 62 
 63         for rows.Next() {
 64             rows.Scan(&s.id, &s.no, &s.name, &s.score)
 65             log.Println(*s)
 66         }
 67 
 68         ////改
 69         //updateSql := "update student set name = ? where no = ?"
 70         //Execute(db, updateSql, "Rose", "123456")
 71         //
 72         ////删
 73         //deleteSql := "delete from student where no = ? "
 74         //Execute(db, deleteSql,"123456")
 75 
 76         c.String(200,
 77             fmt.Sprintf(type1, no, name, score))
 78 
 79     })
 80     //3. 监听
 81     r.Run()
 82 
 83 }
 84 
 85 //初始化数据库连接
 86 func InitDatabase() (*sql.DB, error) {
 87     //将数据转换成数据库url作为返回值
 88     url := strings.Join([]string{"root", ":", "root", "@tcp(", "127.0.0.1", ":", "3306", ")/", "xu"}, "")
 89     db, err := sql.Open("mysql", url)
 90     if err != nil {
 91         log.Printf("open database error:%v", err)
 92         return nil, err
 93     }
 94     return db, nil
 95 }
 96 
 97 //执行增、改、删任务
 98 func Execute(db *sql.DB, sql string, params ...interface{}) error {
 99     stmt, _ := db.Prepare(sql) //预编译
100     defer stmt.Close()
101     _, err := stmt.Exec(params...)
102     if err != nil {
103         log.Printf("execute sql error:%v
", err)
104         return err
105     }
106     log.Println("execute sql success")
107     return nil
108 }
109 
110 //查询数据库数据
111 func QueryData(db *sql.DB, sql string, params ...interface{}) (*sql.Rows, error) {
112     stmt, _ := db.Prepare(sql)
113     defer stmt.Close()
114     rows, err := stmt.Query(params...)
115     if err != nil {
116         log.Printf("query data error:%v", err)
117         return nil, err
118     }
119     log.Println("query data success")
120     return rows, nil
121 }

2 删除

结果

html

 1 <!DOCTYPE html>
 2 <html lang="en">
 3 <head>
 4     <meta charset="UTF-8">
 5     <title>删除学生</title>
 6 
 7 </head>
 8 <body>
 9 <form action="http://127.0.0.1:8080/delete" method="POST" enctype="application/x-www-form-urlencoded">
10 
11 
12     <div>
13 
14         <label>学号:
15             <input type="text" name="no">
16         </label>
17     </div>
18 
19 
20     <div>
21 
22         <input type="submit" value="删除">
23     </div>
24 
25 
26 
27 </form>
28 </body>
29 </html>

 1 package main
 2 
 3 import (
 4     "database/sql"
 5     "fmt"
 6     "github.com/gin-gonic/gin"
 7     _ "github.com/go-sql-driver/mysql"
 8     "log"
 9     "strings"
10 )
11 
12 //定义一个和表对应的结构体
13 type Student struct {
14     id    int
15     no    string //学号
16     name  string //姓名
17     score uint   //成绩
18 }
19 
20 func main() {
21 
22     //创建路由
23     r := gin.Default()
24 
25 
26     //删除
27     r.POST("/delete", func(c *gin.Context) {
28 
29         //接收
30         no := c.PostForm("no")
31 
32         db, err := InitDatabase()
33         defer db.Close()
34         if err != nil {
35             log.Println(err)
36             return
37         }
38 
39         deleteSql := "delete from student where no = ? "
40         Execute(db, deleteSql, no)
41 
42         c.String(200, fmt.Sprintf("delete ok!"))
43 
44     })
45 
46     //3. 监听
47     r.Run()
48 
49 }
50 
51 //初始化数据库连接
52 func InitDatabase() (*sql.DB, error) {
53     //将数据转换成数据库url作为返回值
54     url := strings.Join([]string{"root", ":", "root", "@tcp(", "127.0.0.1", ":", "3306", ")/", "xu"}, "")
55     db, err := sql.Open("mysql", url)
56     if err != nil {
57         log.Printf("open database error:%v", err)
58         return nil, err
59     }
60     return db, nil
61 }
62 
63 //执行增、改、删任务
64 func Execute(db *sql.DB, sql string, params ...interface{}) error {
65     stmt, _ := db.Prepare(sql) //预编译
66     defer stmt.Close()
67     _, err := stmt.Exec(params...)
68     if err != nil {
69         log.Printf("execute sql error:%v
", err)
70         return err
71     }
72     log.Println("execute sql success")
73     return nil
74 }
75 
76 //查询数据库数据
77 func QueryData(db *sql.DB, sql string, params ...interface{}) (*sql.Rows, error) {
78     stmt, _ := db.Prepare(sql)
79     defer stmt.Close()
80     rows, err := stmt.Query(params...)
81     if err != nil {
82         log.Printf("query data error:%v", err)
83         return nil, err
84     }
85     log.Println("query data success")
86     return rows, nil
87 }

3. 查询和新增

效果

html

 1 // cat files/gin_test01/src/templates/login.html
 2 
 3 <!DOCTYPE html>
 4 <html lang="en">
 5 <head>
 6     <meta charset="UTF-8">
 7     <title></title>
 8 
 9 </head>
10 <body>
11 <div><a href="/list">学生名单</a></div>
12   <table border="1">
13       <thead>
14       <tr>
15           <th>id</th>
16             <th>no</th>
17             <th>name</th>
18              <th>score</th>
19       </tr>
20       </thead>
21       <tbody>
22                     <tr>
23                         <td>{{.ID}}</td>
24                         <td>{{.No}} </td>
25                         <td>{{.Name}} </td>
26                         <td>{{.Score}} </td>
27                         <td><a href="delete>no={{.No}} "> delete</a></td>
28 
29                     </tr>
30       </tbody>
31 
32   </table>
33 
34 
35 </body>
36 </html>

  1 // cat src/files/gin_test01/src/main/main.go
  2 
  3 package main
  4 
  5 import (
  6     "database/sql"
  7     "fmt"
  8     "github.com/gin-gonic/gin"
  9     _ "github.com/go-sql-driver/mysql"
 10     "log"
 11     "net/http"
 12     "strings"
 13 )
 14 
 15 //定义一个和表对应的结构体
 16 type Student struct {
 17     id    int
 18     no    string //学号
 19     name  string //姓名
 20     score uint   //成绩
 21 }
 22 
 23 func main() {
 24 
 25     //创建路由
 26     r := gin.Default()
 27 
 28     //新增数据
 29     r.POST("/form", func(c *gin.Context) {
 30 
 31         //表单参数设置默认值
 32         c.DefaultPostForm("type", "alert")
 33 
 34         //接收
 35         no := c.PostForm("no")
 36         name := c.PostForm("name")
 37         score := c.PostForm("score")
 38 
 39         db, err := InitDatabase()
 40         defer db.Close()
 41         if err != nil {
 42             log.Println(err)
 43             return
 44         }
 45         ////增
 46         insertSql := "insert into student(no, name, score) values(?, ?, ?)"
 47         err = Execute(db, insertSql, no, name, score)
 48         if err != nil {
 49             log.Printf("insert data error : %v
", err)
 50             return
 51         }
 52 
 53         c.String(200,
 54             fmt.Sprintf("save ok!"))
 55 
 56     })
 57 
 58     //查询数据
 59     r.GET("/list", func(c *gin.Context) {
 60 
 61         db, err := InitDatabase()
 62         defer db.Close()
 63         if err != nil {
 64             log.Println(err)
 65             return
 66         }
 67 
 68         //查
 69         //querySql := "select id, no, name, score from student "
 70         rows, err := QueryData(db, "select id, no, name, score from student")
 71         defer rows.Close()
 72         if err != nil {
 73             log.Printf("query data error:%v
", err)
 74             return
 75         }
 76         s := new(Student)
 77 
 78         //html渲染
 79         r.LoadHTMLFiles("files/gin_test01/src/templates/list.html")
 80 
 81         for rows.Next() {
 82             rows.Scan(&s.id, &s.no, &s.name, &s.score)
 83             log.Println(*s)
 84             //c.HTML(http.StatusOK, "list.html", gin.H{"ID": &s.id, "No": &s.no, "Name": &s.name, "Score": &s.score})
 85             data := map[string]interface{}{
 86                 "ID":    &s.id,
 87                 "No":    &s.no,
 88                 "Name":  &s.name,
 89                 "Score": &s.score,
 90             }
 91             c.HTML(http.StatusOK, "list.html", data)
 92 
 93         }
 94 
 95     })
 96 
 97     //3. 监听
 98     r.Run()
 99 
100 }
101 
102 //初始化数据库连接
103 func InitDatabase() (*sql.DB, error) {
104     //将数据转换成数据库url作为返回值
105     url := strings.Join([]string{"root", ":", "root", "@tcp(", "127.0.0.1", ":", "3306", ")/", "xu"}, "")
106     db, err := sql.Open("mysql", url)
107     if err != nil {
108         log.Printf("open database error:%v", err)
109         return nil, err
110     }
111     return db, nil
112 }
113 
114 //执行增、改、删任务
115 func Execute(db *sql.DB, sql string, params ...interface{}) error {
116     stmt, _ := db.Prepare(sql) //预编译
117     defer stmt.Close()
118     _, err := stmt.Exec(params...)
119     if err != nil {
120         log.Printf("execute sql error:%v
", err)
121         return err
122     }
123     log.Println("execute sql success")
124     return nil
125 }
126 
127 //查询数据库数据
128 func QueryData(db *sql.DB, sql string, params ...interface{}) (*sql.Rows, error) {
129     stmt, _ := db.Prepare(sql)
130     defer stmt.Close()
131     rows, err := stmt.Query(params...)
132     if err != nil {
133         log.Printf("query data error:%v", err)
134         return nil, err
135     }
136     log.Println("query data success")
137     return rows, nil
138 }