XORM 的复杂操作

https://blog.csdn.net/aminic/article/details/42029653

package main

import (
"fmt"
"log"

_ "github.com/go-sql-driver/mysql"
"github.com/go-xorm/xorm"
)

type ModelBase struct {
Id   int64
Name string
}

type Person struct {
Id   int64
Name string
}

type Department struct {
Id   int64
Name string
}

type DepartmentTotalPrice struct {
Department
}

type DepartmentPerson struct {
Id         int64
Department Department `xorm:"department_id bigint"`
Person     Person     `xorm:"person_id bigint"`
}

type Order struct {
Id         int64
Person     Person `xorm:"person_id bigint"`
Title      string
TotalPrice float64
}

type OrderDetail struct {
Id     int64
Order  Order `xorm:"order_id bigint"`
Name   string
Number int64
Price  float64
}

var x *xorm.Engine
func init() {
var err error
x, err = xorm.NewEngine("mysql", "root:12345678@/test?charset=utf8")
x.ShowSQL = true
if err != nil {
log.Fatalf("Fail to create XORM engine : %v", err)
}
if err = x.Sync(
new(Person),
new(Department),
new(DepartmentPerson),
new(Order), new(OrderDetail)); err != nil {
log.Fatalf("Fail to sync struct to  table schema : %v", err)
} else {
fmt.Println("Succ sync struct to table schema")
}
}

func createPerson() {
fmt.Println("++++++++++++++++++++ Create Person")
persons := []Person{
Person{Name: "lucy"},
Person{Name: "maven"},
Person{Name: "tom"},
Person{Name: "kim"},
}
var (
num int64
err error
)
if num, err = x.Insert(persons); err != nil {
log.Fatalf("Fail to Insert Persons : %v", err)
}
fmt.Printf("Succ to insert person number : %d\n", num)
}

func createDepartment() {
fmt.Println("++++++++++++++++++++ Create Department")
departs := []Department{
Department{Name: "Basic Processing"},
Department{Name: "Person Management"},
}
var (
num int64
err error
)
if num, err = x.Insert(departs); err != nil {
log.Fatalf("Fail to Insert Department : %v", err)
}
fmt.Printf("Succ to insert Department number : %d\n", num)
}

func getDeparts() (departs []Department, err error) {
////////get department from DB
departs = make([]Department, 0)

if err = x.Find(&departs); err != nil {
return nil, err
}

fmt.Printf("Succ to get Department number : %d\n", len(departs))
for i, d := range departs {
fmt.Printf("DataIndex : %d        DataContent : %#v\n", i, d)
}
return departs, nil
}

func getPersons() (persons []Person, err error) {
////////get person from DB
persons = make([]Person, 0)

if err = x.Find(&persons); err != nil {
return nil, err
}

fmt.Printf("Succ to get Person number : %d\n", len(persons))
for i, d := range persons {
fmt.Printf("DataIndex : %d        DataContent : %#v\n", i, d)
}
return persons, nil
}

func createDepartmentPersonRelation() {
fmt.Println("++++++++++++++++++++ Create Department Person Relation")

var err error
var departs []Department
if departs, err = getDeparts(); err != nil {
log.Fatalf("Fail to Get Department : %v \n", err)
}
var persons []Person
if persons, err = getPersons(); err != nil {
log.Fatalf("Fail to Get Person : %v \n", err)
}

////////create relation to DB
relations := []DepartmentPerson{
DepartmentPerson{Department: departs[0], Person: persons[0]},
DepartmentPerson{Department: departs[0], Person: persons[1]},
DepartmentPerson{Department: departs[1], Person: persons[2]},
DepartmentPerson{Department: departs[1], Person: persons[3]},
}
var num int64
if num, err = x.Insert(relations); err != nil {
log.Fatalf("Fail to insert Department Person relation : %#v \n", err)
}
fmt.Printf("Succ to insert Department Person relation number : %d\n", num)
}

func createOrder() {
fmt.Println("++++++++++++++++++++ Create Person Order")
//get persons
var (
err     error
num     int64
persons []Person
)

if persons, err = getPersons(); err != nil {
log.Fatalf("Fail to Get Person : %v \n", err)
}

//create order and details
for _, p := range persons {

//create order to DB
for i := int64(1); i < 3; i++ {
//using transction
s := x.NewSession()
err = s.Begin()
o := Order{
Person: p,
Title:  fmt.Sprintf("order %d for person %s", i, p.Name),
}

if num, err = s.Insert(&o); err != nil {
s.Rollback()
log.Fatalf("Fail to  %s  save ", o.Title)
}
fmt.Printf("Succ to  %s  save . order id is : %d \n", o.Title, o.Id)
////////////
details := []OrderDetail{
OrderDetail{
Order:  o,
Name:   fmt.Sprintf("detail %d _ %s", i, o.Title),
Number: i,
Price:  11.0,
},
OrderDetail{
Order:  o,
Name:   fmt.Sprintf("detail %d _ %s", i+1, o.Title),
Number: i + 1,
Price:  12.0,
},
}

if num, err = s.InsertMulti(&details); err != nil {
s.Rollback()
log.Fatalf("Fail to  detail for  %s  save ", o.Title)
}

s.Commit()
fmt.Printf("Succ to  detail for  %s  save number : %d\n", o.Title, num)
}
}
}
func getOneDepartmentPersonRelationByID() {
var (
err error
has bool
)
//good run
relation1 := DepartmentPerson{Id: 5}
if has, err = x.Get(&relation1); err != nil {
log.Fatalf("Find Department Person Relation Error : %v", err)
}
fmt.Printf("find one : %d    data content : %#v\n", has, relation1)
//////////////////////////////////////
//good run
var relation2 DepartmentPerson
if has, err = x.Id(5).Get(&relation2); err != nil {
log.Fatalf("Find Department Person Relation Error : %v", err)
}
fmt.Printf("find one : %d    data content : %#v\n", has, relation2)

}
func getBasicProcessingDepartmentPersonMavenOrderDetails() {
var (
err     error
details []OrderDetail
)
fmt.Println("++++++++++++++++ get details by order title")
//get details by order title
if err = x.Join("INNER", "order", "order.id=order_detail.order_id").
Where("order.title=?", "order 1 for person maven").
Find(&details); err != nil {
log.Fatalf("Find order detail by order title Error : %v", err)
}
fmt.Printf("find order detail number : %d\n", len(details))
for i, d := range details {
fmt.Printf("index : %d        data content : %#v\n", i, d)
}
//////////////////////////
fmt.Println("++++++++++++++++ get details for person and department condition")
//get details for person and department condition
if err = x.Join("INNER", "order", "order.id=order_detail.order_id").
Join("INNER", "person", "person.id=order.person_id").
Join("LEFT", "department_person", "department_person.person_id=order.person_id").
Join("LEFT", "department", "department.id=department_person.department_id").
Where("department.name=?", "Basic Processing").
And("person.name=?", "maven").
Find(&details); err != nil {
log.Fatalf("Find order detail by order title Error : %v", err)
}
fmt.Printf("find order detail number : %d\n", len(details))
for i, d := range details {
fmt.Printf("index : %d        data content : %#v\n", i, d)
}
}

func getOrderTotalPrice() {
fmt.Println("++++++++++++++++++++ get order total price using Iterate method")
var (
err                 error
orders_UsingIterate []*Order
)

if err = x.Iterate(new(Order), func(i int, bean interface{}) error {
o := bean.(*Order)
var details []OrderDetail

if err := x.Where("order_id=?", o.Id).Find(&details); err == nil {
var totalPrice float64 = 0
for _, d := range details {
totalPrice += (float64(d.Number) * d.Price)
}
o.TotalPrice = totalPrice
orders_UsingIterate = append(orders_UsingIterate, o)
}
return nil
}); err != nil {
log.Fatalf("Find order total price Error using iterate method : %v", err)
}

fmt.Printf("order count : %d\n", len(orders_UsingIterate))
for i, o := range orders_UsingIterate {
fmt.Printf("index : %d        data content : %#v\n", i, o)
}
/////////////////////////////////////////////////////////
fmt.Println("++++++++++++++++++++ get order total price using rows method")
var rows *xorm.Rows
if rows, err = x.Rows(new(Order)); err != nil {
log.Fatalf("Find order total price Error using rows method : %v", err)
}
defer rows.Close()

var o Order
var orders_UsingRows []Order
for rows.Next() {
if err = rows.Scan(&o); err != nil {
fmt.Printf("rows scan in order error : %v\n", err)
}
var details []OrderDetail
if err := x.Where("order_id=?", o.Id).Find(&details); err == nil {
var totalPrice float64 = 0
for _, d := range details {
totalPrice += (float64(d.Number) * d.Price)
}
o.TotalPrice = totalPrice
orders_UsingRows = append(orders_UsingRows, o)
}
}
fmt.Printf("order count : %d\n", len(orders_UsingRows))
for i, o := range orders_UsingRows {
fmt.Printf("index : %d        data content : %#v\n", i, o)
}

}

func main() {
createPerson()
createDepartment()
createDepartmentPersonRelation()
createOrder()
getOneDepartmentPersonRelationByID()
getBasicProcessingDepartmentPersonMavenOrderDetails()
getOrderTotalPrice()
}