go+mysql实现页面的增删改查练习
原文地址:http://www.niu12.com/article/35
初次学go,在了解一些基础之后就开始做一个用户的增删改查来回顾知识,有很多数据验证和安全漏洞并没有考虑,只当作联系
前提:下载mysql驱动
a.go get github.com/go-sql-driver/mysql
b.数据表结构
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('1', '卡牌');
INSERT INTO `users` VALUES ('2', 'card');
INSERT INTO `users` VALUES ('3', '周起');
INSERT INTO `users` VALUES ('4', '有的人');
1.目录结构
| tpl --------模板文件
|-----create.html --------创建页面
|-----list.html --------列表页面
|-----update.html --------更新页面
| handlers.go --------业务逻辑处理(增删改查)
| main.go --------入口文件(路由控制)
| models.go --------模型文件(映射数据表)
2.main.go
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"log"
"net/http"
"os"
)
var (
db *sql.DB
err error
)
/**
检测错误,抛出异常
*/
func checkError(err error) {
if err != nil {
panic(err)
}
}
func main() {
db, err = sql.Open("mysql", "root:123456@tcp(47.97.215.189 )/test?charset=utf8")
checkError(err)
defer db.Close()
// 测试连接
err = db.Ping()
checkError(err)
os.Setenv("PORT", "8888")
port := os.Getenv("PORT")
if port == "" {
log.Fatal("服务器端口号未设置")
}
// 路由
// 用户列表
http.HandleFunc("/", listHandle)
http.HandleFunc("/list", listHandle)
// 添加用户
http.HandleFunc("/create", createHandle)
//// 更新用户
http.HandleFunc("/update", updateHandle)
//// 删除用户
http.HandleFunc("/delete", deleteHandle)
// 监听端口
http.ListenAndServe(":"+port, nil)
}
3.handlers.go
package main
import (
"html/template"
"net/http"
)
// 用户列表
func listHandle(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
http.Error(w, "请求方式错误", http.StatusMethodNotAllowed)
}
rows, err := db.Query("SELECT * FROM users")
if err != nil {
panic(err)
}
var users []User
var user User
for rows.Next() {
err = rows.Scan(&user.Id
, &user.Username)
users = append(users, user)
}
t, err := template.New("list.html").ParseFiles("tpl/list.html")
err = t.Execute(w, users)
if err != nil {
panic(err)
}
}
// 创建用户
func createHandle(w http.ResponseWriter, r *http.Request) {
if r.Method == "GET" {
t, err := template.New("create.html").ParseFiles("tpl/create.html")
if err != nil {
panic(err)
}
t.Execute(w,nil)
}
if r.Method == "POST" {
r.ParseForm()
username := r.Form["username"][0]
stmt, err := db.Prepare("INSERT INTO users(username) VALUES(?)")
if err != nil {
panic(err)
}
_, err = stmt.Exec(username)
if err != nil{
panic(err)
}
http.Redirect(w, r, "/list", 301)
}
}
// 修改用户信息
func updateHandle(w http.ResponseWriter, r *http.Request) {
r.ParseForm()
if r.Method == "GET" {
id := r.Form["id"][0]
var user User
row := db.QueryRow("SELECT * FROM users WHERE id = ?", id)
row.Scan(&user.Id , &user.Username)
t, err := template.New("update.html").ParseFiles("tpl/update.html")
if err != nil {
panic(err)
}
t.Execute(w, user)
}
if r.Method == "POST" {
id := r.Form["id"][0]
username := r.Form["username"][0]
stmt, err := db.Prepare("UPDATE users SET username = ? WHERE id = ?")
if err != nil {
panic(err)
}
_,err = stmt.Exec(username, id)
if err != nil {
panic(err)
}
http.Redirect(w, r, "/list", http.StatusMovedPermanently)
}
}
// 删除用户
func deleteHandle(w http.ResponseWriter, r *http.Request) {
if r.Method != "GET" {
http.Error(w, "请求方式错误", http.StatusMethodNotAllowed)
}
r.ParseForm()
id := r.Form["id"][0]
stmt, err := db.Prepare("DELETE FROM users WHERE id = ?")
if err != nil {
panic(err)
}
_,err = stmt.Exec(id)
if err != nil {
panic(err)
}
http.Redirect(w, r, "/list", http.StatusMovedPermanently)
}
4.models.go
package main
type User struct {
Id int64 `json:"id"`
Username string `json:"username"`
}
5.执行go run *.go运行或者go build来打包
6.在页面输入localhost:8888/ 进行操作