数据库
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'my1');
INSERT INTO `user` VALUES ('2', 'my2');
INSERT INTO `user` VALUES ('3', 'tom3');
INSERT INTO `user` VALUES ('4', 'tom4');
INSERT INTO `user` VALUES ('5', 'tom5');
models.go文件代码
package models
import (
"github.com/astaxie/beego/orm"
)
type User struct {
Id int `json:"id" `
Name string `json:"name,omitempty" orm:"size(50)"`
}
func init() {
// 需要在 init 中注册定义的 model
orm.RegisterModel(new(User))
}
controllers下 index.go的代码
package controllers
import (
"fmt"
"quickstart/models"
"github.com/astaxie/beego"
"github.com/astaxie/beego/orm"
_ "github.com/go-sql-driver/mysql"
"strconv"
"math/rand"
)
type IndexController struct {
beego.Controller
}
func (c *IndexController) Dbtest() {
//测试原生SQL执行的方法
var type1 = c.GetString("type")
orm1 := orm.NewOrm()
if type1 == "exec" { //修改操作
fmt.Println(type1)
a := rand.Int()
//res, err := orm.Raw("update user set name=? where id = ?", "alter1" + strconv.Itoa(a),"1").Exec()
//上面的语句等同于以下两行
r := orm1.Raw("update user set name=? where id = ?")
res,err := r.SetArgs("alter1" + strconv.Itoa(a),"1").Exec()
if err != nil {
c.Ctx.WriteString("查询失败1!")
return
}
n, err := res.RowsAffected()//受影响的行数
if err != nil {
c.Ctx.WriteString("查询失败2!")
return
}
c.Ctx.WriteString("mysql row affected nums = " + strconv.Itoa(int(n)))
}else if type1 == "struct" {
数据映射到结构体
fmt.Println(type1)
var user = models.User{}
err := orm1.Raw("select * from user where id = ?","2").QueryRow(&user)
fmt.Println(user)
if err == nil {
c.Ctx.WriteString(strconv.Itoa(user.Id) + " " + user.Name + "\n")
return
}
}else if type1 == "structs" {
多行数据映射到结构体贪杯
fmt.Println(type1)
var users = []models.User{}
n,err := orm1.Raw("select * from user where id > ?","2").QueryRows(&users)
fmt.Println(users)
if err == nil && n > 0 {
for _,v := range users {
c.Ctx.WriteString(strconv.Itoa(v.Id) + " " + v.Name + "\n")
}
return
}
}else if type1 == "tomap" {
两个字段之间的映射
fmt.Println(type1)
var res = orm.Params{}
n,err := orm1.Raw("select * from user where id > ?","2").RowsToMap(&res, "name", "id")
fmt.Println(res)
if err == nil && n > 0 {
for key, value := range res {
c.Ctx.WriteString(key + "ID是:" + value.(string) + "\n")
}
return
}
}else if type1 == "prepare" {
sql语句在发送到服务器时会做语法检查和编译 多次语句相同,参数不同的数据更新,可先将SQK预编译,后面只发送参数即可
fmt.Println(type1)
p,err := orm1.Raw("update user set name = ? where id = ?").Prepare()
if err != nil {
c.Ctx.WriteString("查询出错!")
return
}
res, err := p.Exec("my1",1)
res.RowsAffected()
if err != nil {
c.Ctx.WriteString("错误!")
return
}
res, err = p.Exec("my2",2)
res.RowsAffected()
if err != nil {
c.Ctx.WriteString("错误!")
return
}
}else if type1 == "vlist" {
ValuesList 返回结果集 slice 。 Raw SQL 查询获得的结果集 Value 为 string 类型,NULL 字段的值为空 。
//
//下面我们查询表中所有的name 并保存到 list中 :
fmt.Println(type1)
var lists []orm.ParamsList
num, err := orm1.Raw("SELECT id,name FROM user").ValuesList(&lists)
if err == nil && num > 0 {
for _,v:=range lists{
for _,v1:=range v{
c.Ctx.WriteString(v1.(string)+" ")
}
c.Ctx.WriteString("\n")
}
}else {
c.Ctx.WriteString("查询出错!")
}
}else {
fmt.Println("default")
}
c.Data["Website"] = "beego.me3344"
c.Data["Email"] = "astaxie@gmail.com1122"
c.TplName = "myindex.tpl"
}
func init() {
// 注册驱动
orm.RegisterDriver("mysql", orm.DRMySQL)
// 注册默认数据库
// 我的mysql的root用户密码为tom,打算把数据表建立在名为test数据库里
// 备注:此处第一个参数必须设置为“default”(因为我现在只有一个数据库),否则编译报错说:必须有一个注册DB的别名为 default
orm.RegisterDataBase("default", "mysql", "root:root@tcp(127.0.0.1:3306)/godb?charset=utf8")
}