优化查询

qirong

2023-11-28 fe0a4c0495cf9f1a65343158c2a8c2d4a163e045

优化查询

 src/main/java/com/mindskip/xzs/base/SystemCode.java

@@ -18,7 +18,7 @@
    /**
     * UNAUTHORIZED
     */
    UNAUTHORIZED(401, "用户未登录"),
    UNAUTHORIZED(401, "用户未登录1"),
    /**
     * UNAUTHORIZED
     */

 src/main/java/com/mindskip/xzs/controller/student/DashboardController.java

@@ -108,23 +108,21 @@

        ExamTemplatesVO vo = new ExamTemplatesVO();
        vo.setStatus("0");
        vo.setUserId(user.getId());
        List<ExamTemplates> gets = templatesMapper.gets(vo);
        ExamTemplatesUserCountVO templatesUserCountVO = new ExamTemplatesUserCountVO();
        templatesUserCountVO.setUserId(user.getId());

        for (ExamTemplates get : gets) {
            templatesUserCountVO.setId(get.getId());
            List<ExamTemplatesUser> byUserId = examTemplatesUserMapper.getByUserId(user.getId(), get.getId());
            if(byUserId.size()>0){
                List<ExamTemplatesUserCount> byUserIdAndTemplatesId = templatesUserCountMapper.getByUserIdAndTemplatesId(templatesUserCountVO);
                if(byUserIdAndTemplatesId.size()==0){
                Integer number = templatesUserCountMapper.getCountByUserIdAndTemplatesId(templatesUserCountVO);
                if(number==0){
                    PaperInfo info = new PaperInfo();
                    info.setId(get.getId());
                    info.setName(get.getName());
                    info.setStatus("1");
                    paperInfos.add(info);
                }
            }
        }
        indexVM.setFixedPaper(paperInfos);


 src/main/java/com/mindskip/xzs/domain/vo/ExamTemplatesVO.java

@@ -17,6 +17,7 @@

    private Integer[] subjectId;
    private String status;
    private Integer userId;

    public Integer getId() {
        return id;
@@ -58,6 +59,14 @@
        this.status = status;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    @Override
    public String toString() {
        return "ExamTemplatesVO{" +
@@ -66,6 +75,7 @@
                ", ctime=" + ctime +
                ", subjectId=" + Arrays.toString(subjectId) +
                ", status=" + status +
                ", userId=" + userId +
                '}';
    }
}

 src/main/java/com/mindskip/xzs/repository/ExamTemplatesUserCountMapper.java

@@ -26,4 +26,6 @@
    List<ExamTemplatesUserCount> getByTemplates(@Param("id") Integer id);

    List<UserCountExcelVO> getByExamTemplates(@Param("id") Integer id);

    Integer getCountByUserIdAndTemplatesId(ExamTemplatesUserCountVO examTemplatesUserCountVO);
}

 src/main/java/com/mindskip/xzs/service/impl/ExamPaperServiceImpl.java

@@ -492,7 +492,7 @@
        Integer judgment = examPaperEditRequestVM.getQuestionTypeVMS().stream().mapToInt(QuestionTypeVM::getTrueFalse).sum();


        if ((singleChoice * 2 + multipleChoice * 2 + judgment * 2) != new Integer(100)) {
        if ((singleChoice * 2 + multipleChoice * 2 + judgment * 2) != new Integer(10)) {
            throw new QuestionException();
        }


 src/main/resources/application.yml

@@ -1,4 +1,7 @@
server:
  servlet:
    session:
      timeout: 3600
  port: 8000
  undertow:
    io-threads: 16

 src/main/resources/mapper/ExamTemplatesMapper.xml

@@ -32,17 +32,20 @@

    <select id="gets" resultMap="BaseResultMap" parameterType="com.mindskip.xzs.domain.vo.ExamTemplatesVO">
        select
        <include refid="Base_Column_List"/>
        from t_exam_templates
        where
            <choose>
                <when test="status != null">
                    status = 0
                </when>
                <otherwise>
                    status is null
                </otherwise>
            </choose>
        e.*
        from t_exam_templates e
        inner join t_exam_templates_user u on e.id = u.templates_id
        <where>
            <if test="status != null">
                and e.status = 0
            </if>
            <if test="status == null">
                and e.status is null
            </if>
            <if test="userId != null">
                and u.user_id = #{userId}
            </if>
        </where>
    </select>

    <select id="getById" resultMap="BaseResultMap">

 src/main/resources/mapper/ExamTemplatesUserCountMapper.xml

@@ -61,6 +61,11 @@
        where user_id = #{userId}  and exam_templates_id = #{id}
    </select>

    <select id="getCountByUserIdAndTemplatesId" resultType="java.lang.Integer" parameterType="com.mindskip.xzs.domain.vo.ExamTemplatesUserCountVO">
        select count(*) from t_exam_templates_user_count
        where user_id = #{userId}  and exam_templates_id = #{id}
    </select>

    <select id="getByTemplates" resultMap="BaseResultMap">
        select * from t_exam_templates_user_count
        where exam_templates_id = #{id}