【goland如果使用xorm执行mysql的存储过程】

goland如果使用xorm执行mysql的存储过程

需求背景

存在用户表和用户邀请关系表，当用户A邀请用户B，则B用户为A用户直接邀请的人；B用户邀请了C用户，则C用户就是A用户的间接邀请人。

如何查询当前用户间接邀请的注册用户有那些？

需求分析

• 需要使用递归查询间接邀请的用户有那些，将其id放到一个数组中。
• 将这个递归查询的SQL转换成一个存储过程，使用xorm执行mysql的存储过程；这样操作避免SQL注入。

xorm执行mysql的存储过程的demo示例

存储过程如下

• 创建一个存储过程，传入一个int类型的参数，返回一个int类型的参数

CREATE PROCEDURE add_ten(IN in_param INT, OUT out_param INT)
	  BEGIN
	      SET out_param = in_param + 10;
	  END

go代码实现

package main

import (
	"PsycheEpic/src/config"
	"PsycheEpic/src/datasource"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

// 当然这里也可以定义一个结构体，用于接收存储过程的输出的参数
/*
type Result struct {
	OutParam int `xorm:"out_param"`
}
*/

func main() {
	config.InitConfig()
	datasource.InitMysql()
	// 创建一个存储过程，接收一个输入参数in_param，返回一个输出参数out_param
	// 存储过程的逻辑是将输入参数加10后赋值给输出参数
	sql := `CREATE PROCEDURE add_ten(IN in_param INT, OUT out_param INT)
	  BEGIN
	      SET out_param = in_param + 10;
	  END`

	_, err := datasource.Engine.Exec(sql)
	if err != nil {
		fmt.Println(err)
		return
	}

	// 调用存储过程，传入一个输入参数10，使用一个Result结构体变量接收输出参数
	var out_param int
	_, err = datasource.Engine.Exec("CALL add_ten(?,  @out_param)", 10)
	if err != nil {
		fmt.Println(err)
		return
	}
	// 从MySQL中获取存储过程的输出参数
	_, err = datasource.Engine.SQL("SELECT @out_param").Get(&out_param)
	if err != nil {
		fmt.Println(err)
		return
	}
	// 打印输出参数的值，应该是20
	fmt.Println(out_param)

}

• 【注意】如果存储过程中没有查询输出参数结果的SELECT语句，需要在代码中执行查询操作

  • // 从MySQL中获取存储过程的输出参数
    	_, err = datasource.Engine.SQL("SELECT @out_param").Get(&out_param)
    	if err != nil {
    		fmt.Println(err)
    		return
    	}
    

需求实现

存储过程的SQL如下:

DELIMITER //
CREATE PROCEDURE GetIndirectInvites(IN InputInviterID INT)
BEGIN
    WITH RECURSIVE indirect_invites AS (
        SELECT inviter_id, invitee_id
        FROM user_invitation_relationship
        WHERE inviter_id = InputInviterID
        UNION ALL
        SELECT ir.inviter_id, ir.invitee_id
        FROM user_invitation_relationship ir
        INNER JOIN indirect_invites ii ON ii.invitee_id = ir.inviter_id
    )
    SELECT DISTINCT invitee_id
    FROM indirect_invites
    WHERE invitee_id NOT IN (
        SELECT invitee_id
        FROM user_invitation_relationship
        WHERE inviter_id = InputInviterID
    );
END //
DELIMITER ;

• 调用执行存储过程

call GetIndirectInvites(1)

代码实现

// GetIndirectInvitees 通过存储过程获取间接邀请人

// Invitee 是一个用于存储存储过程返回结果的结构体
type Invitee struct {
	InviteeID int64 `xorm:"invitee_id"`
}

// GetIndirectInvitees 通过存储过程获取间接邀请人
func GetIndirectInvitees(inviterID int64) ([]int64, error) {
	// 创建用于存储结果的切片
	var results []Invitee

	// 使用Session来执行存储过程
	session := datasource.Engine.NewSession()
	defer session.Close()

	// 调用存储过程并将结果保存到 results
	err := session.SQL("CALL GetIndirectInvites(?)", inviterID).Find(&results)
	if err != nil {
		// 注意这里不再是 log.Fatalf，因为我们需要返回错误而不是立即退出程序
		return nil, errors.New("调用查询间接邀请人的存储过程报错: " + err.Error())
	}

	// 如果结果数组为空，则返回一个空数组而不是错误
	if len(results) == 0 {
		return []int64{}, nil
	}

	// 将结果转换为 int64 数组
	var inviteeIds []int64
	for _, result := range results {
		inviteeIds = append(inviteeIds, result.InviteeID)
	}

	return inviteeIds, nil
}

func GetIndirectInvitees(inviterID int64) ([]int64, error) {
	var inviteeIds []int64

	session := datasource.Engine.NewSession()
	defer session.Close()

	err := session.SQL("CALL GetIndirectInvites(?)", inviterID).Find(&inviteeIds)
	if err != nil {
		return nil, errors.New("调用查询间接邀请人的存储过程出错: " + err.Error())
	}

	return inviteeIds, nil
}

示例二

SQL存储过程如下

CREATE PROCEDURE query_contact(IN i_name VARCHAR(10))
BEGIN
    SELECT contact.user_id, 
           contact.real_name, 
           contact.age, 
           contact.phone_number, 
           contact.home_address AS address, 
           contact.create_time 
    FROM contact
    WHERE contact.real_name = i_name;
END;
-- 调用执行存储过程
call  query_contact1('Jerry')

go代码执行存储过程

package main

import (
	"PsycheEpic/src/config"
	"PsycheEpic/src/datasource"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
)

type Contact struct {
	UserId      int    `json:"user_id" xorm:"user_id"`
	Name        string `json:"name" jorm:"real_name" xorm:"real_name"`
	Age         int    `json:"age" xorm:"age"`
	PhoneNumber string `json:"phone_number" xorm:"phone_number"`
	HomeAddress string `json:"home_address" xorm:"home_address"`
	CreateTime  string `json:"create_time" xorm:"create_time"`
}

type ContactResult struct {
	UserID      int    `xorm:"user_id"`
	RealName    string `xorm:"real_name"`
	Age         int    `xorm:"age"`
	PhoneNumber string `xorm:"phone_number"`
	Address     string `xorm:"address"`
	CreateTime  string `xorm:"create_time"`
}

func QueryContact(name string) (*ContactResult, error) {
	var result ContactResult

	session := datasource.Engine.NewSession()

	// 由于 xorm 不支持 OUT 参数，我们直接获取返回的结果集
	_, err := session.SQL("CALL query_contact(?)", name).Get(&result)
	if err != nil {
		return nil, err
	}

	return &result, nil
}

func main() {
	//读取参数配置
	config.InitConfig()
	//执行数据库初始化
	datasource.InitMysql()

	// 调用函数
	name := "Jerry"
	contact, err := QueryContact(name)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("Result: %+v\n", contact)
}

xorm的mysql初始化函数

package datasource

import (
	"PsycheEpic/src/config"
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/go-xorm/xorm"
	"log"
)

var (
	db     *sql.DB
	Engine *xorm.Engine
)

func InitMysql() {
	fmt.Println("InitMysql....")

	// xorm 连接数据库
	var err error
	Engine, err = xorm.NewEngine("mysql", config.Conf.Mysql_UserName+":"+config.Conf.Mysql_PWD+"@tcp(127.0.0.1:"+config.Conf.MysqlPort+")/"+config.Conf.DBname+"?charset=utf8")

	fmt.Println(" ============== 【xorm 连接数据库】 =====================   ")
	println("xorm 连接数据库,Engine: ", Engine)
	fmt.Println(" ============== 【xorm 连接数据库】 =====================   ")

	if err != nil {
		log.Println("数据库连接失败:", err)
		fmt.Println("数据库连接失败:", err)
		return
	}

}

读取json配置

package config

import (
	"encoding/json"
	"github.com/patrickmn/go-cache"
	"os"
	"time"
)

var (
	Conf      *AppConfig
	CacheCode = cache.New(3*time.Hour, 15*time.Second)
)

type AppConfig struct {
	AppName string `json:"app_name"` //项目名称 no-name

	DBname   string `json:"db_name"`   //数据库名称 test_schema
	DBserver string `json:"db_server"` //mysql域名

	Mode           string `json:"mode"`
	Mysql_UserName string `json:"mysql_username"` //mysql用户名 root
	Mysql_PWD      string `json:"mysql_pwd"`      //mysql密码 root
	MysqlPort      string `json:"mysql_port"`     //mysql启动端口

	Port string `json:"port"` //项目启动端口


	Md5Salt string `json:"md5_salt"`
}

func InitConfig() *AppConfig {

	file, err := os.Open("./config.json")
	/*
		 var file *os.File
			var err error
			if runtime.GOOS == "linux" {
				file, err = os.Open("./config.json")
			} else {
				file, err = os.Open("src/config.json")
			}
	*/

	if err != nil {
		println("error is :", err)
	}

	decoder := json.NewDecoder(file)

	conf := AppConfig{}

	err = decoder.Decode(&conf)

	if err != nil {

		println("error is :", err)

	}
	Conf = &conf
	return &conf

}

json

{

  "app_name": "no-name",
  "db_name" : "nexthuman",
  "db_server" : "localhost",
  "mode": "dev",
  "mysql_port": "3306",
  "mysql_username" : "root",
  "mysql_pwd" : "822198gxq!",

}