- 优化sql操作,将连接数据库定义在main函数中,在关闭的时候总体关闭数据库,这样就不用每次都去连接
- 新增Deletesignindata函数 delete:删除登录信息; 登出删除登录列表元素
- 实现登出与查询操作结果的接口
1优化sql操作
//对数据库操作的所有函数
import (
"database/sql"
"fmt"
"time"
)
// 添加操作信息,向表OperationRecord(操作列表)添加
func Addoperation(db *sql.DB, id int, operation string) error {
_, err := db.Exec("insert into OperationRecord (id,behavior,time) VALUES (?, ?, ?)", id, operation, time.Now().Unix())
if err != nil {
return err
}
return nil
}
// 获取:表OperationRecord(操作列表)的数据
func Getoperations(db *sql.DB, id int) ([]OperationRecord, error) {
rows, err := db.Query("SELECT behavior, time FROM OperationRecord WHERE id = ?", id)
if err != nil {
return nil, err
}
defer rows.Close()
result := []OperationRecord{}
for rows.Next() {
p := &OperationRecord{ID: id}
err = rows.Scan(&p.Behavior, &p.Time)
if err != nil {
return result, err
}
result = append(result, *p)
}
if err := rows.Err(); err != nil {
return result, err
}
return result, nil
}
// user表------------------------------------------------------------------
// add:向user(注册列表)添加元素
func AddLogindata(db *sql.DB, logindata *Logindata) error {
_, err := db.Exec("insert into user (name,password,Email) VALUES (?, ?, ?)", logindata.Name, logindata.Password, logindata.Email)
if err != nil {
return err
}
return nil
}
// ((id是操作用的,不是给用户登录用的))
// get:获取:向user(注册列表)获取id,登陆成功,获取id
func GetuserId(db *sql.DB, email string) (int, error) {
id := -1
err := db.QueryRow("SELECT id FROM user WHERE Email = ?", email).Scan(&id)
if err != nil {
return -1, err
}
return id, nil
}
// get:获取:向user(注册列表)获取email(改密时查Email,不能由用户传入Email,)
func GetuserEmail(db *sql.DB, id int) (string, error) {
Email := ""
err := db.QueryRow("SELECT Email FROM user WHERE id = ?", id).Scan(&Email)
if err != nil {
return "", err
}
return Email, nil
}
// 查:验证user(注册列表)密码
func Verifypassword(db *sql.DB, id int, password string) error {
count := 0
err := db.QueryRow("SELECT COUNT(*) FROM user WHERE id = ? AND password = ? ", id, password).Scan(&count)
if err != nil {
return err
}
return nil
}
// 改:修改:user(注册列表)密码
func Changepassword(db *sql.DB, id int, newpassword string) error {
_, err := db.Exec("UPDATE users SET password = ? WHERE id = ?", newpassword, id)
if err != nil {
return err
}
return nil
}
// -SignInlist--------------------------------------------------------------------
// 查:SignIn查询身份验证功能:传入SignInData
func SignInVerification(db *sql.DB, signInData *SignInData) error {
// 查询身份认证码是否正确
var expirationTime int64 //储存过期时间
err := db.QueryRow("SELECT ExpirationTime FROM SignInList WHERE id = ? AND IDcode = ?", signInData.ID, signInData.IDCode).Scan(&expirationTime)
if err != nil {
return fmt.Errorf("非法访问")
}
// 验证身份认证码是否过期
if time.Now().Unix() > expirationTime {
return fmt.Errorf("身份验证过期,请重新登陆")
}
// 验证成功.更新身份认证码的过期时间
_, err = db.Exec("UPDATE SignInList SET ExpirationTime = ? WHERE id = ? AND IDcode = ?", time.Now().Unix()+30*60, signInData.ID, signInData.IDCode)
if err != nil {
return err
}
return nil
}
// 增:向登录列表插入信息
func InsertSignInData(db *sql.DB, data *SignInData) error {
// 准备SQL语句
stmt, err := db.Prepare("REPLACE INTO SignInList (id, IDcode, ExpirationTime) VALUES (?, ?, ?)")
if err != nil {
return err
}
defer stmt.Close()
// 执行插入操作
result, err := stmt.Exec(data.ID, data.IDCode, time.Now().Unix()+30*60)
if err != nil {
return err
}
// 输出插入结果
_, err = result.RowsAffected()
if err != nil {
return err
}
return nil
}
// delete:删除登录信息; 登出删除登录列表元素
func Deletesignindata(db *sql.DB, id int) error {
_, err := db.Exec("DELETE FROM SignInList WHERE id = ?", id)
if err != nil {
return err
}
return nil
}
// VerificationCode(验证码储存库)----------------------------------------------------------
// 查: 验证验证码
func Verify(db *sql.DB, email string, verification int) error {
// 查询验证码是否正确
var expirationTime int64 //储存过期时间
//代表已经验证过
db.Exec("UPDATE VerificationCode SET password = ? WHERE Email = ?", 1, email)
err := db.QueryRow("SELECT ExpirationTime , time FROM VerificationCode WHERE Email = ? AND Verification = ?", email, verification).Scan(&expirationTime)
if err != nil {
return fmt.Errorf("验证码错误")
}
// 验证身份认证码是否过期
if time.Now().Unix() > expirationTime {
return fmt.Errorf("过期")
}
return nil
}
// 增:验证码了列表插入信息
func InsertVerification(db *sql.DB, data EmailVerificationCode) error {
// 准备SQL语句
stmt, err := db.Prepare("REPLACE INTO VerificationCode (Email, Verification, ExpirationTime, time) VALUES (?, ?, ?, ?)")
if err != nil {
return err
}
defer stmt.Close()
// 执行插入操作
_, err = stmt.Exec(data.Email, data.Verification, time.Now().Unix()+5*60, 0)
if err != nil {
return err
}
return nil
}
// get:获取:验证次数,重发验证码0无,1有
func GetuVerificationtime(email string) (int, error) {
db, err := Opensql()
if err != nil {
return -1, err
}
defer db.Close()
t := -1
err = db.QueryRow("SELECT time FROM user WHERE Email = ?", email).Scan(&t)
if err != nil {
return -1, err
}
return t, nil
}
3实现登出与查询操作结果的接口
func main() {
r := gee7.New()
db, err := funcmod.Opensql()
if err != nil {
fmt.Printf("数据库出错=%#v\n", err)
return
}
defer db.Close()
//模拟登录插入登录列表信息
r.POST("/signup", signup.Signin(db))
// 登出
r.POST("/signout", func(ctx *gee7.Context) {
data := funcmod.SignInData{}
//读取用户传入的json数据
ctx.Getjson(&data)
//身份验证
err = funcmod.SignInVerification(db, &data)
if err != nil {
ctx.FailJson(401, err.Error())
return
}
//删除登录列表信息
err = funcmod.Deletesignindata(db, data.ID)
if err != nil {
ctx.JSON(401, gee7.H{
"msg": err.Error(),
})
}
//添加操作信息
err = funcmod.Addoperation(db, data.ID, "登出成功")
if err != nil {
ctx.JSON(401, gee7.H{
"msg": err.Error(),
})
}
})
r.POST("/getoperations", func(ctx *gee7.Context) {
data := funcmod.SignInData{}
//读取用户传入的json数据
ctx.Getjson(&data)
//身份验证
err = funcmod.SignInVerification(db, &data)
if err != nil {
ctx.FailJson(401, err.Error())
return
}
//读取所有的该用户的操作信息
r, err := funcmod.Getoperations(db, data.ID)
if err != nil {
ctx.FailJson(401, err.Error())
return
}
ctx.JSON(200, gee7.H{
"operations": r,
"msg": "ok",
})
})
r.Run(":8888")
}
本文由mdnice多平台发布
