> ### 第六例 原生sql操作 ~~~ package demo3 import ( "database/sql" "fmt" _ "github.com/go-sql-driver/mysql" "html/template" "net/http" "time" ) //TODO : Datatase func getConn() (*sql.DB, error) { host := "localhost" dbname := "messagebox" username := "root" password := "123456" db, err := sql.Open("mysql", username+":"+password+"@tcp("+host+")/"+dbname+"?charset=utf8") return db, err } //TODO : 留言板列表頁 func Index(w http.ResponseWriter, r *http.Request) { r.ParseForm() sort := r.Form["sort"] if len(sort) == 0 { sort = append(sort, "0") } db, err := getConn() if err != nil { fmt.Println(err.Error()) } sortfiled := "" if sort[0] == "0" { sortfiled = "time" } else { sortfiled = "viewnum" } rows, err := db.Query(fmt.Sprintf("select id, title, viewnum, time as time2 from msg_article where status = 'A' order by %s desc limit 10", sortfiled)) if err != nil { fmt.Println(err.Error()) } data := make([]map[string]string, 0, 0) for rows.Next() { var ( id string title string viewnum string time2 int64 ) err = rows.Scan(&id, &title, &viewnum, &time2) if err != nil { fmt.Println(err) } m := make(map[string]string) m["id"] = id m["title"] = title m["viewnum"] = viewnum tm := time.Unix(time2, 0) m["time"] = tm.Format("2006-01-02 15:04:05") data = append(data, m) } tpl, err := template.ParseFiles("demo3/view/index.html") if err != nil { fmt.Println(err.Error()) return } tpl.Execute(w, data) } //TODO : 創建留言板 func Create(w http.ResponseWriter, r *http.Request) { tpl, err := template.ParseFiles("demo3/view/create.html") if err != nil { fmt.Println(err.Error()) return } tpl.Execute(w, nil) } //TODO : 處理留言板表單 func CreateForm(w http.ResponseWriter, r *http.Request) { r.ParseForm() if r.Method == "POST" { name := r.Form["title"] content := r.Form["content"] times := time.Now().Unix() db, err := getConn() if err != nil { fmt.Println("getConn", err.Error()) } _, err = db.Exec("insert into msg_article(title, content, time) values (?, ?, ?)", name[0], content[0], times) if err != nil { fmt.Println("Insert:", err.Error()) } else { http.Redirect(w, r, "/index", 302) } } } //TODO : 刪除留言 func Delete(w http.ResponseWriter, r *http.Request) { r.ParseForm() id := r.Form["id"] db, err := getConn() if err != nil { fmt.Println("getConn", err.Error()) } _, err = db.Exec(fmt.Sprintf("update msg_article set status='D' where id=%s", id[0])) if err != nil { fmt.Println("Delete", err.Error()) } else { http.Redirect(w, r, "/index", 302) } } func Demo3() { //設置靜態目錄 http.Handle("/static/", http.StripPrefix("/static/", http.FileServer(http.Dir("demo3/static")))) http.HandleFunc("/", Index) http.HandleFunc("/index", Index) http.HandleFunc("/create", Create) http.HandleFunc("/createform", CreateForm) http.HandleFunc("/delete", Delete) http.HandleFunc("/favicon.ico", func(writer http.ResponseWriter, request *http.Request) {}) http.ListenAndServe(":9001", nil) } ~~~