改进代码var req []*Total res := db.Scopes(models.Paginate(page, pageSize)).Raw("SELECT * FROM flaws WHERE facility_id = ? "+ "UNION SELECT * FROM issues WHERE facility_id = ? "+ "UNION SELECT * FROM records WHERE facility_id = ? "+ "UNION SELECT * FROM qualities WHERE facility_id = ? "+ "UNION SELECT * FROM inspections WHERE facility_id = ?", code, code, code, code, code).Find(&req) if res.RowsAffected == 0 { resp.Code = 400 resp.Msg = "记录不存在" ctx.JSON(400, resp) return } resp.Data = req ctx.JSON(200, resp)

可以将这段代码进行重构，使其更加简洁和易读。可以使用以下方式重构：

var total []*Total 
db.Where("facility_id = ?", code).Scopes(
  models.Paginate(page, pageSize),
).Find(&total)

if len(total) == 0 {
  ctx.JSON(400, Response{
    Code:  400,
    Msg:   "记录不存在",
    Data:  nil,
  })
  return
}

ctx.JSON(200, Response{
  Code:  200,
  Msg:   "成功获取记录列表",
  Data:  total,
})

这样可以将查询语句进行简化，同时也可以将错误响应和成功响应的代码进行分离，使得代码更加易于维护。

相关问题

改进代码var req []*Total res := db.Scopes(models.Paginate(page, pageSize)).Raw("SELECT * FROM flaws WHERE facility_id = ? "+ "UNION SELECT * FROM issues WHERE facility_id = ? "+ "UNION SELECT * FROM records WHERE facility_id = ? "+ "UNION SELECT * FROM qualities WHERE facility_id = ? "+ "UNION SELECT * FROM inspections WHERE facility_id = ?", code, code, code, code, code).Find(&req)

可以尝试使用预编译语句来优化 SQL 查询性能，同时使用占位符来避免 SQL 注入攻击。改进后的代码如下所示：

stmt, err := db.DB().Prepare("SELECT * FROM flaws WHERE facility_id = ? " +
    "UNION SELECT * FROM issues WHERE facility_id = ? " +
    "UNION SELECT * FROM records WHERE facility_id = ? " +
    "UNION SELECT * FROM qualities WHERE facility_id = ? " +
    "UNION SELECT * FROM inspections WHERE facility_id = ? " +
    "LIMIT ? OFFSET ?")
if err != nil {
    // 处理错误
}
defer stmt.Close()

req := make([]*Total, 0)
offset := (page - 1) * pageSize
limit := pageSize
rows, err := stmt.Query(code, code, code, code, code, limit, offset)
if err != nil {
    // 处理错误
}
defer rows.Close()

for rows.Next() {
    var item Total
    err := rows.Scan(&item)
    if err != nil {
        // 处理错误
    }
    req = append(req, &item)
}

if err := rows.Err(); err != nil {
    // 处理错误
}

res := db.Model(&Total{}).Where("facility_id = ?", code).Count(&Total{})

// 处理结果

这里我们使用了 `db.DB().Prepare()` 方法来预编译 SQL 查询语句，并使用占位符 `?` 来代替变量，从而避免了 SQL 注入攻击。

同时，我们也将分页查询的 `LIMIT` 和 `OFFSET` 参数移到了 SQL 查询语句中，以避免使用 `Scopes()` 方法产生额外的查询开销。在查询结果时，我们使用 `stmt.Query()` 方法来执行查询，并使用 `rows.Scan()` 方法将查询结果映射到结构体中。

最后，我们使用 `db.Model().Where().Count()` 方法来查询总记录数，并将结果返回给调用者。

改进代码func TracingList(ctx *gin.Context) { resp := models.Response{ Code: 0, Msg: "success", } t, _ := strconv.Atoi(ctx.Query("t")) label, _ := strconv.Atoi(ctx.Query("label")) page, _ := strconv.Atoi(ctx.DefaultQuery("page", "1")) pageSize, _ := strconv.Atoi(ctx.DefaultQuery("pageSize", "10")) code := ctx.Query("code") //设施类型 if len(code) == 0 { resp.Code = 400 resp.Msg = "请输入code值" ctx.JSON(400, resp) return } type Total struct { gorm.Model Type int16 Source int16 Explanation string Label int16 FacilityID string } db := common.DB.Session(&gorm.Session{}) if t < 0 || t > 5 { var req interface{} switch t { case 0: req = []*Total{} case 1: req = []*models.Flaw{} case 2: req = []*models.Issue{} case 3: req = []*models.Record{} case 4: req = []*models.Quality{} case 5: req = []*models.Inspection{} } //查询并分页 if t < 1 || t > 5 { res := db.Scopes(models.Paginate(page, pageSize)).Where("facility_id=?", code).Find(&req) if res.RowsAffected == 0 { resp.Code = 400 resp.Msg = "记录不存在" ctx.JSON(400, resp) return } } else { res := db.Scopes(models.Paginate(page, pageSize)).Raw("SELECT * FROM flaws WHERE facility_id = ? "+ "UNION SELECT * FROM issues WHERE facility_id = ? "+ "UNION SELECT * FROM records WHERE facility_id = ? "+ "UNION SELECT * FROM qualities WHERE facility_id = ? "+ "UNION SELECT * FROM inspections WHERE facility_id = ?", "code", "code", "code", "code", "code").Find(&req) if res.RowsAffected == 0 { resp.Code = 400 resp.Msg = "记录不存在" ctx.JSON(400, resp) return } } resp.Data = req ctx.JSON(200, resp) } else if label < 1 || label > 4 { var req []*Total res := db.Scopes(models.Paginate(page, pageSize)).Raw("SELECT * FROM flaws WHERE label = ? "+ "UNION SELECT * FROM issues WHERE label = ? "+ "UNION SELECT * FROM records WHERE label = ? "+ "UNION SELECT * FROM qualities WHERE label = ? "+ "UNION SELECT * FROM inspections WHERE label = ?", "label", "label", "label", "label", "label").Find(&req) if res.RowsAffected == 0 { resp.Code = 400 resp.Msg = "记录不存在" ctx.JSON(400, resp) return } resp.Data = req ctx.JSON(200, resp) } else { ctx.JSON(400, models.Response{ Code: 400, Msg: "参数有误", }) return } }

以下是改进的代码：

func TracingList(ctx *gin.Context) {
    resp := models.Response{
        Code: 0,
        Msg:  "success",
    }

    code := ctx.Query("code")
    if len(code) == 0 {
        resp.Code = 400
        resp.Msg = "请输入code值"
        ctx.JSON(400, resp)
        return
    }

    t, err := strconv.Atoi(ctx.Query("t"))
    if err != nil || t < 0 || t > 5 {
        resp.Code = 400
        resp.Msg = "无效的参数t"
        ctx.JSON(400, resp)
        return
    }

    label, err := strconv.Atoi(ctx.Query("label"))
    if err != nil || label < 1 || label > 4 {
        resp.Code = 400
        resp.Msg = "无效的参数label"
        ctx.JSON(400, resp)
        return
    }

    page, err := strconv.Atoi(ctx.DefaultQuery("page", "1"))
    if err != nil || page < 1 {
        resp.Code = 400
        resp.Msg = "无效的参数page"
        ctx.JSON(400, resp)
        return
    }

    pageSize, err := strconv.Atoi(ctx.DefaultQuery("pageSize", "10"))
    if err != nil || pageSize < 1 {
        resp.Code = 400
        resp.Msg = "无效的参数pageSize"
        ctx.JSON(400, resp)
        return
    }

    var req interface{}
    switch t {
    case 0:
        req = []*Total{}
    case 1:
        req = []*models.Flaw{}
    case 2:
        req = []*models.Issue{}
    case 3:
        req = []*models.Record{}
    case 4:
        req = []*models.Quality{}
    case 5:
        req = []*models.Inspection{}
    }

    db := common.DB.Session(&gorm.Session{})
    res := db.Scopes(models.Paginate(page, pageSize)).Where("facility_id=?", code)
    if t >= 1 && t <= 5 {
        res = res.Table("flaws").Where("facility_id=?", code).
            Or("facility_id=?", code).
            Or("facility_id=?", code).
            Or("facility_id=?", code).
            Or("facility_id=?", code)
    }
    res.Find(req)
    if res.RowsAffected == 0 {
        resp.Code = 400
        resp.Msg = "记录不存在"
        ctx.JSON(400, resp)
        return
    }

    resp.Data = req
    ctx.JSON(200, resp)
}

主要改进包括：

1. 对输入的参数进行了有效性检查，如 t、label、page 和 pageSize 都需要为正整数，code 不能为空。
2. 使用了 Go 语言内置的错误处理机制，当参数无效时，返回错误信息。
3. 将 SQL 查询语句拆分为两段，根据 t 的值判断使用哪一段，避免了使用 Raw() 方法的安全隐患。
4. 代码结构更加清晰，易于阅读和维护。