使用Go语言创建简单的CRUD增删改查

17-01-09 banq
    

用Go构建一个简单的CRUD应用

Go是一个伟大的编程语言,这里使用sqlite数据库创建一个简单的CRUD应用。

首先,我们需要知道使用数据库的方式。 Go提供了一个名为database / sql的内置包,提供了一个围绕SQL或类SQL数据库的轻量级接口,它必须与数据库驱动程序结合使用,在本文中,我们将使用sqlite数据库,和选择https://github.com/mattn/go-sqlite3

接下来,我们将创建使用的模型,我创建了一个struct如下:

type Cost struct {
	Id int64 `json:"id"`
	ElectricAmount int64 `json:"electric_amount"`
	ElectricPrice float64 `json:"electric_price"`
	WaterAmount int64 `json:"water_amount"`
	WaterPrice float64 `json:"water_price"`
	CheckedDate string `json:"checked_date"`
}
<p>

在上面的代码中我使用struct标签,这是一个有用的功能,帮助你序列化到json到响应。

接下来,我们将在main函数中连接到数据库,如下所示:

db, err = sql.Open("sqlite3", "db.sqlite3")
	if err != nil {
		panic(err)
	}
	defer db.Close()
	// test connection
	err = db.Ping()
	if err != nil {
		panic(err)
	}
<p>

CRUD的四个任务是创建,读取,更新和删除。 我们将后面将创建每个对应的处理程序。

func listHandler(w http.ResponseWriter, r *http.Request) {
	if r.Method != "GET" {
		http.Error(w, "Method not allowed", http.StatusBadRequest)
	}
	rows, err := db.Query("SELECT * FROM cost")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
	}
	var funcMap = template.FuncMap{
		"multiplication": func(n float64, f float64) float64 {
			return n * f
		},
		"addOne": func(n int) int {
			return n + 1
		},
	}
	var costs []Cost
	var cost Cost
	for rows.Next() {
		err = rows.Scan(&cost.Id, &cost.ElectricAmount,
			&cost.ElectricPrice, &cost.WaterAmount, &cost.WaterPrice, &cost.CheckedDate)
		if err != nil {
			http.Error(w, err.Error(), http.StatusInternalServerError)
		}
		costs = append(costs, cost)
	}
	//t, err := template.ParseFiles("tmpl/list.html")
	t, err := template.New("list.html").Funcs(funcMap).ParseFiles("tmpl/list.html")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
	}
	err = t.Execute(w, costs)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
	}

}

func createHandler(w http.ResponseWriter, r *http.Request) {
	if r.Method != "POST" {
		http.ServeFile(w, r, "tmpl/create.html")
		return
	}
	var cost Cost
	cost.ElectricAmount, _ = strconv.ParseInt(r.FormValue("ElectricAmount"), 10, 64)
	cost.ElectricPrice, _ = strconv.ParseFloat(r.FormValue("ElectricPrice"), 64)
	cost.WaterAmount, _ = strconv.ParseInt(r.FormValue("WaterAmount"), 10, 64)
	cost.WaterPrice, _ = strconv.ParseFloat(r.FormValue("WaterPrice"), 64)
	cost.CheckedDate = r.FormValue("CheckedDate")
	fmt.Println(cost)

	// Save to database
	stmt, err := db.Prepare(`
		INSERT INTO cost(electric_amount, electric_price, water_amount, water_price, checked_date)
		VALUES(?, ?, ?, ?, ?)
	`)
	if err != nil {
		fmt.Println("Prepare query error")
		panic(err)
	}
	_, err = stmt.Exec(cost.ElectricAmount, cost.ElectricPrice,
				cost.WaterAmount, cost.WaterPrice, cost.CheckedDate)
	if err != nil {
		fmt.Println("Execute query error")
		panic(err)
	}
	http.Redirect(w, r, "/list", 301)
}

func updateHandler(w http.ResponseWriter, r *http.Request) {
	if r.Method != "POST" {
		http.Error(w, "Method is not allowed", http.StatusBadRequest)
	}
	var cost Cost
	cost.Id, _ = strconv.ParseInt(r.FormValue("Id"), 10, 64)
	cost.ElectricAmount, _ = strconv.ParseInt(r.FormValue("ElectricAmount"), 10, 64)
	cost.ElectricPrice, _ = strconv.ParseFloat(r.FormValue("ElectricPrice"), 64)
	cost.WaterAmount, _ = strconv.ParseInt(r.FormValue("WaterAmount"), 10, 64)
	cost.WaterPrice, _ = strconv.ParseFloat(r.FormValue("WaterPrice"), 64)
	cost.CheckedDate = r.FormValue("CheckedDate")
	fmt.Println(cost)
	stmt, err := db.Prepare(`
		UPDATE cost SET electric_amount=?, electric_price=?, water_amount=?, water_price=?, checked_date=?
		WHERE id=?
	`)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
	}
	res, err := stmt.Exec(cost.ElectricAmount, cost.ElectricPrice,
		cost.WaterAmount, cost.WaterPrice, cost.CheckedDate, cost.Id)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
	}
	_, err = res.RowsAffected()
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
	}
	http.Redirect(w, r, "/list", 301)
}

func deleteHandler(w http.ResponseWriter, r *http.Request) {
	if r.Method != "POST" {
		http.Error(w, "Method not allowed", http.StatusBadRequest)
	}
	var costId, _ = strconv.ParseInt(r.FormValue("Id"), 10, 64)
	stmt, err := db.Prepare("DELETE FROM cost WHERE id=?")
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
	}
	res, err := stmt.Exec(costId)
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
	}
	_, err = res.RowsAffected()
	if err != nil {
		http.Error(w, err.Error(), http.StatusInternalServerError)
	}
	http.Redirect(w, r, "/list", 301)

}
<p>

最后让我们在main函数上为每个上述处理程序创建一个路由

// route
	http.HandleFunc("/list", listHandler)
	http.HandleFunc("/create", createHandler)
	http.HandleFunc("/update", updateHandler)
	http.HandleFunc("/delete", deleteHandler)
	http.ListenAndServe(":3333", nil)
<p>

完整的代码和模板请参见https://github.com/thanhngvpt/famcost

Build a simple CRUD with Go – Medium