美文网首页Golang
xorm使用

xorm使用

作者: 我就是小政政 | 来源:发表于2019-08-14 19:07 被阅读0次

    定义关系模型

    关系说明：（逻辑外键在子表上）
    • 教师表（子表）1：教师详细表（主表）1
    • 课程表（子表）N：教师表（主表）1
    • 课程表（主表）N：学生表（主表）N，需要成绩表（作为关系表）
    结构体类型说明

    时间使用time.Time
    主键使用int64
    分数使用decimal.Decimal

    结构体tag说明

    xorm:"index"：增加索引
    xorm:"created" 、xorm:"updated"、xorm:"deleted" ：自动生成时间
    Id int64：此形式自动表示xorm:"pk autoincr"
    xorm:"extends"：应用于一个匿名成员结构体或者非匿名成员结构体之上，表示此结构体的所有成员也映射到数据库中，extends可加载无限级
    -：这个Field将不进行字段映射
    version：这个Field将会在insert时默认为1，每次更新自动加1
    参考：http://gobook.io/read/github.com/go-xorm/manual-zh-CN/chapter-02/4.columns.html

    //教师详细信息
type Detail struct {
    Id        int64
    Email     string
    Addr      string
    Tel       string
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

//学生
type Student struct {
    Id        int64
    Name      string
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

//教师
type Teacher struct {
    Id        int64
    Name      string
    DetailId  int64     `xorm:"index notnull"`
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

//课程
type Course struct {
    Id        int64
    Name      string
    TeacherId int64     `xorm:"index notnull"`
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

//成绩表
type Performance struct {
    Id        int64
    CourseId  int64 `xorm:"index notnull"`
    StudentId int64 `xorm:"index notnull"`
    Score     decimal.Decimal
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

    查询

    1对1查询、1对多查询
    • 教师表（子表）1：教师详细表（主表）1
    //教师表
type Teacher struct {
    Id        int64
    Name      string
    DetailId  int64     `xorm:"index notnull"` //这里添加逻辑外键，不添加真实约束
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

    //教师详细表
type Detail struct {
    Id        int64
    Email     string
    Addr      string
    Tel       string
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

    添加关联结构体

    type TeacherDetail struct {
    Teacher `xorm:"extends"` //使用extends继承所有字段
    Detail  `xorm:"extends"` 
}

func (TeacherDetail) TableName() string {
    //指定使用该结构体对象 进行数据库查询时，使用的表名，这里返回子表名称
    return "teacher"
}

    查询调用

        //1对1查询（1教师：1教师详情）,子表：教师表，主表：详情表
    tcherDetails := make([]TeacherDetail, 0)
    engine.Join("LEFT", "detail", "teacher.detail_id=detail.id").Find(&tcherDetails)
    logrus.Infof("查询1对1（1教师：1教师详情）：%v", tcherDetails)
    /* 执行结果
    [xorm] [info]  2019/08/14 16:02:58.692951 [SQL] SELECT `teacher`.*, `detail`.* FROM `teacher` LEFT JOIN detail ON teacher.detail_id=detail.id
    INFO[0000] 查询1对1（1教师：1教师详情）：[{{1 卡卡西 1 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {1 kakaka@sina.com 卡卡卡 卡卡卡卡卡 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}} {{2 凯 2 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {2 kai@sina.com 木叶村 11111111 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}}]
    */
    • 课程表（子表）N：教师表（主表）1
    //课程
type Course struct {
    Id        int64
    Name      string
    TeacherId int64     `xorm:"index notnull"` //添加逻辑外键
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

    添加关联结构体

    type CourseTeacher struct {
    Course  `xorm:"extends"`
    Teacher `xorm:"extends"`
}

func (CourseTeacher) TableName() string {
    return "course" //返回子表名称
}

    查询调用

        //1对多查询（1个老师：N个课程；1个课程：1个老师），子表：课程表，主表：教师表
    courseTchers := make([]CourseTeacher, 0) //声明数组
    engine.Join("LEFT", "teacher", "course.teacher_id=teacher.id").Find(&courseTchers)
    logrus.Infof("查询1对多（N课程：1老师）：%v", courseTchers)
    /* 执行结果
    [xorm] [info]  2019/08/14 16:02:58.695181 [SQL] SELECT * FROM `course` LEFT JOIN teacher ON course.teacher_id=teacher.id
    INFO[0000] 查询1对多（N课程：1老师）：[{{1 疾风手里剑 1 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {1 卡卡西 1 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}} {{2 基本体术 2 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {2 凯 2 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}}]
    */
    多对多查询
    • 课程表（主表）N：学生表（主表）N，需要成绩表（作为关系表）
    //成绩表
type Performance struct {
    Id        int64
    CourseId  int64 `xorm:"index notnull"` //添加逻辑外键
    StudentId int64 `xorm:"index notnull"` //添加逻辑外键
    Score     decimal.Decimal
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

    查询调用

        //多对多查询（N课程：N学生），子表、关系表：成绩表，主表：课程表、学生表
    performs := make([]Performance, 0)
    engine.Join("LEFT", "course", "performance.course_id=course.id").
        Join("left", "student", "performance.student_id=student.id").
        Find(&performs)
    logrus.Infof("查询多对多(N课程：N学生)：%v", performs)
    /* 执行结果
    [xorm] [info]  2019/08/14 16:02:58.697606 [SQL] SELECT * FROM `performance` LEFT JOIN course ON performance.course_id=course.id left JOIN student ON performance.student_id=student.id
    INFO[0000] 查询多对多(N课程：N学生)：[{1 1 1 100 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {2 1 2 60 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {3 1 3 80 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}]
    */

    完整示例如下：

    package main

import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/go-xorm/xorm"
    "github.com/shopspring/decimal"
    "github.com/sirupsen/logrus"
    "time"
    "xorm.io/core"
)

//教师详细信息
type Detail struct {
    Id        int64
    Email     string
    Addr      string
    Tel       string
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

//学生
type Student struct {
    Id        int64
    Name      string
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

//教师
type Teacher struct {
    Id        int64
    Name      string
    DetailId  int64     `xorm:"index notnull"`
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

//课程
type Course struct {
    Id        int64
    Name      string
    TeacherId int64     `xorm:"index not null"`
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

//成绩表
type Performance struct {
    Id        int64
    CourseId  int64 `xorm:"index notnull"`
    StudentId int64 `xorm:"index notnull"`
    Score     decimal.Decimal
    CreatedAt time.Time `xorm:"created"`
    UpdatedAt time.Time `xorm:"updated"`
    DeletedAt time.Time `xorm:"deleted"`
}

type TeacherDetail struct {
    Teacher `xorm:"extends"`
    Detail  `xorm:"extends"`
}

//func (TeacherDetail) TableName() string {
//  //指定使用该结构体对象 进行数据库查询时，使用的表名
//  return "teacher"
//}

type CourseTeacher struct {
    Course  `xorm:"extends"`
    Teacher `xorm:"extends"`
}

func (CourseTeacher) TableName() string {
    return "course"
}

func main() {
    engine, err := xorm.NewEngine("mysql", "root:root@tcp(192.168.99.100:3308)/demo?charset=utf8&parseTime=true&loc=Local")
    if err != nil {
        logrus.Panicf("数据库连接错误，%v", err)
    }

    //设置日志显示
    engine.ShowSQL(true)
    engine.SetLogLevel(core.LOG_DEBUG)

    //设置连接池
    engine.SetMaxOpenConns(3)
    engine.SetMaxIdleConns(1)
    engine.SetConnMaxLifetime(12 * time.Hour)

    // 设置缓存
    // cacher := xorm.NewLRUCacher(xorm.NewMemoryStore(), 1000)
    // engine.SetDefaultCacher(cacher)

    //测试连接
    engine.Ping()

    //将结构体同步到数据库
    //推荐生产环境按照传统的flyway的处理方式，
    //将每个版本的sql幂等处理、编号、归档，升级时各局点存储升级记录、做sql文件md5校验，避免混乱。
    //而通过sync2方式编写sql升级管理流程，每次升级需要编写升级代码，显然通过sql的配置文件的形式更方便。
    err = engine.Sync2(new(Detail), new(Student), new(Teacher), new(Course), new(Performance))
    if err != nil {
        logrus.Panicf("同步到数据库失败，%v", err)
    }

    //插入基础数据
    detail1 := &Detail{Id: 1, Tel: "卡卡卡卡卡", Addr: "卡卡卡", Email: "kakaka@sina.com"}
    detail2 := &Detail{Id: 2, Tel: "11111111", Addr: "木叶村", Email: "kai@sina.com"}
    stu1 := &Student{Id: 1, Name: "佐助"}
    stu2 := &Student{Id: 2, Name: "鸣人"}
    stu3 := &Student{Id: 3, Name: "小樱"}
    stu4 := &Student{Id: 4, Name: "雏田"}
    tcher1 := &Teacher{Id: 1, Name: "卡卡西", DetailId: 1}
    tcher2 := &Teacher{Id: 2, Name: "凯", DetailId: 2}
    course1 := &Course{Name: "疾风手里剑", TeacherId: 1}
    course2 := &Course{Name: "基本体术", TeacherId: 2}
    perf1 := &Performance{CourseId: 1, StudentId: 1, Score: decimal.NewFromFloat(100)}
    perf2 := &Performance{CourseId: 1, StudentId: 2, Score: decimal.NewFromFloat(60)}
    perf3 := &Performance{CourseId: 1, StudentId: 3, Score: decimal.NewFromFloat(80)}

    engine.Insert(detail1, detail2, stu1, stu2, stu3, stu4, tcher1, tcher2, course1, course2, perf1, perf2, perf3)

    //单表,单条查询, 值存入传入的对象中
    theOne := &Student{Id: 1}
    theOther := &Student{}
    _, _ = engine.Get(theOne)
    _, _ = engine.Id(1).Get(theOther)
    _, _ = engine.Where("id=?", 1).Get(theOther)

    //软删除
    _, _ = engine.Delete(&Student{Name: "雏田"})

    //单表，多条查询，值存入传入的对象中
    //返回数组
    stuArr1 := make([]Student, 0) //make声明slice
    _ = []Student{}               //字面量声明slice
    engine.Find(&stuArr1)
    logrus.Infof("查询学生结构体数组：%v", stuArr1)
    /* 执行结果
    [xorm] [info]  2019/08/14 16:02:58.689451 [SQL] SELECT `id`, `name`, `created_at`, `updated_at` FROM `student`
    INFO[0000] 查询学生结构体数组：[{1 佐助 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {2 鸣人 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {3 小樱 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}]
    */

    //返回map
    stuMap1 := make(map[int64]Student) //make声明Map
    _ = map[int64]Student{}            //字面量声明Map
    engine.Find(&stuMap1)
    logrus.Infof("查询学生map：%v", stuMap1)
    /* 执行结果
    [xorm] [info]  2019/08/14 16:02:58.691631 [SQL] SELECT `id`, `name`, `created_at`, `updated_at` FROM `student`
    INFO[0000] 查询学生map：map[1:{1 佐助 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} 2:{2 鸣人 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} 3:{3 小樱 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}]
    */

    //1对1查询（1教师：1教师详情）,子表：教师表，主表：详情表
    tcherDetails := make([]TeacherDetail, 0)
    engine.
                Table("teacher").   //使用Table指定时，可不用编写结构体的TableName方法
        //Cols("teacher.*", "detail.*").
        //Select("teacher.*,detail.*").
        Join("LEFT", "detail", "teacher.detail_id=detail.id").Find(&tcherDetails)
    logrus.Infof("查询1对1（1教师：1教师详情）：%v", tcherDetails)
    /* 执行结果
    [xorm] [info]  2019/08/14 16:02:58.692951 [SQL] SELECT `teacher`.*, `detail`.* FROM `teacher` LEFT JOIN detail ON teacher.detail_id=detail.id
    INFO[0000] 查询1对1（1教师：1教师详情）：[{{1 卡卡西 1 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {1 kakaka@sina.com 卡卡卡 卡卡卡卡卡 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}} {{2 凯 2 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {2 kai@sina.com 木叶村 11111111 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}}]
    */

    //1对多查询（1个老师：N个课程；1个课程：1个老师），子表：课程表，主表：教师表
    courseTchers := make([]CourseTeacher, 0) //声明数组
    engine.Join("LEFT", "teacher", "course.teacher_id=teacher.id").Find(&courseTchers)
    logrus.Infof("查询1对多（N课程：1老师）：%v", courseTchers)
    /* 执行结果
    [xorm] [info]  2019/08/14 16:02:58.695181 [SQL] SELECT * FROM `course` LEFT JOIN teacher ON course.teacher_id=teacher.id
    INFO[0000] 查询1对多（N课程：1老师）：[{{1 疾风手里剑 1 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {1 卡卡西 1 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}} {{2 基本体术 2 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {2 凯 2 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}}]
    */

    //多对多查询（N课程：N学生），子表、关系表：成绩表，主表：课程表、学生表
    performs := make([]Performance, 0)
    engine.Join("LEFT", "course", "performance.course_id=course.id").
        Join("left", "student", "performance.student_id=student.id").
        Find(&performs)
    logrus.Infof("查询多对多(N课程：N学生)：%v", performs)
    /* 执行结果
    [xorm] [info]  2019/08/14 16:02:58.697606 [SQL] SELECT * FROM `performance` LEFT JOIN course ON performance.course_id=course.id left JOIN student ON performance.student_id=student.id
    INFO[0000] 查询多对多(N课程：N学生)：[{1 1 1 100 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {2 1 2 60 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST} {3 1 3 80 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}]
    */

    //不定义自己的集合，使用rows的封装类型返回
    newStu := new(Student)
    rows, err := engine.Rows(newStu)
    if err != nil {
    }
    defer rows.Close()
    for rows.Next() {
        _ = rows.Scan(newStu)
        logrus.Infof("newStu:%v", newStu)
    }
    /* 执行结果
    [xorm] [info]  2019/08/14 16:02:58.700549 [SQL] SELECT `id`, `name`, `created_at`, `updated_at` FROM `student`

    INFO[0000] newStu:&{1 佐助 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}
    INFO[0000] newStu:&{2 鸣人 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}
    INFO[0000] newStu:&{3 小樱 2019-08-14 14:24:56 +0800 CST 2019-08-14 14:24:56 +0800 CST}
    */

    //执行sql
    sql1 := "select * from student where id = ?"
    queryRet, err := engine.Query(sql1, 1)
    logrus.Infof("使用sql查询结果：%v", queryRet)
    /* 执行结果
    [xorm] [info]  2019/08/14 17:52:00.199751 [SQL] select * from student where id = ? []interface {}{1}
    INFO[0000] 使用sql查询结果：[map[created_at:[50 48 49 57 45 48 56 45 49 52 84 49 54 58 50 51 58 52 51 43 48 56 58 48 48] deleted_at:[] id:[49] name:[228 189 144 229 138 169] updated_at:[50 48 49 57 45 48 56 45 49 52 84 49 54 58 50 51 58 52 51 43 48 56 58 48 48]]]
    */

    sql2 := "insert into student (name,created_at,updated_at) values (?,now(),now())"
    ret, err := engine.Exec(sql2, "日向宁次")
    lastInsertId, _ := ret.LastInsertId()
    effectedRows, _ := ret.RowsAffected()
    logrus.Infof("执行sql命令结果,插入id：%v，影响行数：%v",
        lastInsertId, effectedRows)
    /* 执行结果
    [xorm] [info]  2019/08/14 17:52:00.200798 [SQL] insert into student (name) values (?) []interface {}{"日向宁次"}
    INFO[0000] 执行sql命令结果,插入id：8，影响行数：1
    */

    //事务
    txExample(engine)

    //事件
    before := func(bean interface{}) {
        fmt.Println("before", bean)
    }
    after := func(bean interface{}) {
        fmt.Println("after", bean)
    }
    engine.Before(before).After(after).Get(new(Student))
    /* 执行结果
    before &{0  0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC}
    after &{1 佐助 2019-08-14 16:23:43 +0800 CST 2019-08-14 16:23:43 +0800 CST 0001-01-01 00:00:00 +0000 UTC}
     */

}

func txExample(engine *xorm.Engine) {
    session := engine.NewSession()
    defer session.Close()

    //插入 教师详情
    detail := &Detail{
        Email: "zilaiye@sina.com",
        Addr:  "木叶村",
        Tel:   "999",
    }

    _, err := session.Insert(detail) //insert后会更新 detail变量中的相关字段，id、createdAt、updatedAt等
    if err != nil {
        session.Rollback() //插入失败回滚
        return
    }

    //插入 教师
    tcher := &Teacher{
        Name:     "自来也",
        DetailId: detail.Id,
    }
    _, err = session.Insert(tcher)
    if err != nil {
        session.Rollback()
        return
    }
    err = session.Commit()
    if err != nil {
        return
    }
}

    reverse工具

    根据已有的mysql表 生成models代码
    文档https://github.com/go-xorm/cmd/
    https://blog.csdn.net/weixin_34417635/article/details/93643108
    go build 出xorm.exe后，在template、xorm.exe目录下执行命令
    ./xorm reverse [-s] mysql "root:yourpassword@tcp(xxx.xxx.xxx.xxx:3306)/yourdatabase?charset=utf8" templates/goxorm 生成的路径
    PS C:\Users\zmz\go\src\github.com\go-xorm\cmd\xorm> ./xorm help reverse
    该命令具体用法：

    -s                Generated one go file for every table
driverName        Database driver name, now supported four: mysql mymysql sqlite3 postgres
datasourceName    Database connection uri, for detail infomation please visit driver's project page
tmplPath          Template dir for generated. the default templates dir has provide 1 template
generatedPath     This parameter is optional, if blank, the default value is models, then will
                  generated all codes in models dir
tableFilterReg    Table name filter regexp

    相关文章

      网友评论

        本文标题：xorm使用

        本文链接：https://www.haomeiwen.com/subject/jfmnjctx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        Golang

        关于我们|服务条款|联系我们|xorm使用|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！