gorm+postgersql一些使用整理

1：实例化连接：

对于的配置信息为：

datasource_postgersql:
    driverName: postgres
    host: localhost
    port: 5432
    database: xmly_code_go
    username: postgres
    password: 123456
    dbSslmode: disable

连接的示例

//打印链接的额数据库的参数信息
    driverName := viper.GetString("datasource_postgersql.driverName")
    host := viper.GetString("datasource_postgersql.host")
    port := viper.GetString("datasource_postgersql.port")
    database := viper.GetString("datasource_postgersql.database")
    username := viper.GetString("datasource_postgersql.username")
    password := viper.GetString("datasource_postgersql.password")
    //格式化链接PG数据库--端口号的地方注意必须是字符串的形式
    global.GVA_DB, err = gorm.Open(driverName, fmt.Sprintf("host=%s port=%s user=%s dbname=%s password=%s sslmode =disable",
        host,
        port,
        username,
        database,
        password,
    ))
    if err != nil {
        panic("数据库连接失败，请检查参数")
    }

2：分页查询数据列表

//表模型的
type User struct {
    gorm.Model
    Username string `gorm:"type:varchar(20);not null " json:"username" validate:"required,min=4,max=12" label:"用户名"`
    Password string `gorm:"type:varchar(20);not null" json:"password" validate:"required,min=6,max=20" label:"密码"`
    Role     int    `gorm:"type:int;DEFAULT:2" json:"role" validate:"required,gte=2" label:"角色码"`
}
//表的逻辑处理-----返回用戶列表信息，分页的返回，返回的切片的类型的数据
// 查询用户列表
func GetUsers(pageSize int, pageNum int) ([]User, int) {
    var users []User
    var total int
    if pageNum == 0 {
        pageNum = 1
    }
    index := (pageNum - 1) * pageSize
    //执行分页和返回总数
    if err = global.GVA_DB.Limit(pageSize).Offset(index).Find(&users).Count(&total).Error; err != nil && err != gorm.ErrRecordNotFound {
        // 错误处理...
        return nil, 0
    }
    return users, total
}

3：单表的核查

//表模型的
type User struct {
    gorm.Model
    Username string `gorm:"type:varchar(20);not null " json:"username" validate:"required,min=4,max=12" label:"用户名"`
    Password string `gorm:"type:varchar(20);not null" json:"password" validate:"required,min=6,max=20" label:"密码"`
    Role     int    `gorm:"type:int;DEFAULT:2" json:"role" validate:"required,gte=2" label:"角色码"`
}

//db.First(&model).ForUpdate

//表的逻辑处理-----检测用户信息是否已存在
func CheckUser(name string) (code int) {
    var users User
    //db.Where("name = ?", "jinzhu").First(&user)
    global.GVA_DB.Select("id").Where("username = ?", name).First(&users)
    if users.ID > 0 {
        return errmsg.ERROR_USERNAME_USED //1001
    }
    return errmsg.SUCCSE
}

4：使用代码的形式添加外键信息的时候异常（字符串转义问题异常）

异常语句SQL：

 ALTER TABLE "articles" ADD CONSTRAINT articles_user_id_user_user_id_foreign FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT

对应GORM示例

    //创建外键
        if err2 := db.Model(&Articles{}).
            AddForeignKey("user_id", "user(user_id)", "RESTRICT", "RESTRICT").Error; err2 != nil {
            panic(err2)
        }

提示异常问题：

image.png

解决处理：
分析正常的可以执行插入的SQL

ALTER TABLE "articles" ADD CONSTRAINT articles_user_id_user_user_id_foreign FOREIGN KEY ( user_id ) REFERENCES "user"  ( user_id ) ON DELETE RESTRICT ON UPDATE RESTRICT
===============
 ALTER TABLE "articles" ADD CONSTRAINT articles_user_id_user_user_id_foreign FOREIGN KEY (user_id) REFERENCES user(user_id) ON DELETE RESTRICT ON UPDATE RESTRICT

区别在于REFERENCES "user"  和  REFERENCES uesr

解决问题：

if err2 := db.Model(&Articles{}).
            AddForeignKey("user_id", "\"user\""+"(user_id)", "RESTRICT", "RESTRICT").Error; err2 != nil {
            panic(err2)
}
或
 if err2 := db.Model(&Articles{}).
            AddForeignKey("user_id", `"user"`+"(user_id)", "RESTRICT", "RESTRICT").Error; err2 != nil {
            panic(err2)
        }

连接查询一样的问题：

func GetArticlesList(page, pageSize uint) (data *PaginationQ, err error) {
    q := PaginationQ{
        PageSize: pageSize,
        Page:     page,
        Data:     &[]ArticlesInfo{},
    }

    return q.SearchAll(db.Model(&Articles{}).
        Where(&Articles{Status: PublishedArticlesStatus}).
        Select("articles.*, user.*").
        Joins("left join \"user\" on \"user\".user_id = \"articles\".user_id"))

}