The project architecture

SpringBoot + Mybatis (annotated development) quickly generates entity classes (POJOs) and mapper.java, also known as Dao layer: Mybatis -generator plug-in

Use of mybatis- Generator plug-in

<? The XML version = "1.0" encoding = "utf-8"? > <! DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"> <generatorConfiguration> <context id="DB2Tables" targetRuntime="MyBatis3"> <commentGenerator> <property name="suppressDate" value="true"/> <property name="suppressAllComments" value="true"/> </commentGenerator> <! <jdbcConnection driverClass=" com.mysql.cj.jdbc.driver" ConnectionURL = "JDBC: mysql: / / 127.0.0.1:3306 / mac_dev" userId "root", "= =" root "> < / jdbcConnection > < javaTypeResolver > <property name="forceBigDecimals" value="false"/> </javaTypeResolver> <javaModelGenerator targetPackage="com.beijin.limengya.mobile.pojo" targetProject="src/main/java"> <property name="enableSubPackages" value="false" /> <property name="trimStrings" value="true" /> </javaModelGenerator> <sqlMapGenerator targetPackage="mapper" targetProject="src/main/resources"> <property name="enableSubPackages" value="false" /> </sqlMapGenerator> <javaClientGenerator targetPackage="com.beijin.limengya.mobile.dao" targetProject="src/main/java" type="ANNOTATEDMAPPER"> <! -- type="XMLMAPPER" --> <property name="enableSubPackages" value="false" /> </javaClientGenerator> <! <table tableName="COLLECTION_USER_INFO" domainObjectName="Repairjl" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false"> </table> </context> </generatorConfiguration>Copy the code

Where tableName is the name of the database table and domainObjectName is the name of the entity class

Take a look at the generated content

public class Repairjl {
    private Integer id;

    private String deviceId;

    private String repairName;

    private String anceName;

    private String finishExplain;

    private String failureCause;

    private Integer preventId;

    private Integer testId;

    private Integer partId;

    private Integer repairUserid;

    private Integer wxUserid;

    private String repairTime;

    private String createTime;

    private String endTime;

    private Integer finishId;

    private Integer downId;

    public Integer getId(a) {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getDeviceId(a) {
        return deviceId;
    }

    public void setDeviceId(String deviceId) {
        this.deviceId = deviceId == null ? null : deviceId.trim();
    }

    public String getRepairName(a) {
        return repairName;
    }

    public void setRepairName(String repairName) {
        this.repairName = repairName == null ? null : repairName.trim();
    }

    public String getAnceName(a) {
        return anceName;
    }

    public void setAnceName(String anceName) {
        this.anceName = anceName == null ? null : anceName.trim();
    }

    public String getFinishExplain(a) {
        return finishExplain;
    }

    public void setFinishExplain(String finishExplain) {
        this.finishExplain = finishExplain == null ? null : finishExplain.trim();
    }

    public String getFailureCause(a) {
        return failureCause;
    }

    public void setFailureCause(String failureCause) {
        this.failureCause = failureCause == null ? null : failureCause.trim();
    }

    public Integer getPreventId(a) {
        return preventId;
    }

    public void setPreventId(Integer preventId) {
        this.preventId = preventId;
    }

    public Integer getTestId(a) {
        return testId;
    }

    public void setTestId(Integer testId) {
        this.testId = testId;
    }

    public Integer getPartId(a) {
        return partId;
    }

    public void setPartId(Integer partId) {
        this.partId = partId;
    }

    public Integer getRepairUserid(a) {
        return repairUserid;
    }

    public void setRepairUserid(Integer repairUserid) {
        this.repairUserid = repairUserid;
    }

    public Integer getWxUserid(a) {
        return wxUserid;
    }

    public void setWxUserid(Integer wxUserid) {
        this.wxUserid = wxUserid;
    }

    public String getRepairTime(a) {
        return repairTime;
    }

    public void setRepairTime(String repairTime) {
        this.repairTime = repairTime == null ? null : repairTime.trim();
    }

    public String getCreateTime(a) {
        return createTime;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime == null ? null : createTime.trim();
    }

    public String getEndTime(a) {
        return endTime;
    }

    public void setEndTime(String endTime) {
        this.endTime = endTime == null ? null : endTime.trim();
    }

    public Integer getFinishId(a) {
        return finishId;
    }

    public void setFinishId(Integer finishId) {
        this.finishId = finishId;
    }

    public Integer getDownId(a) {
        return downId;
    }

    public void setDownId(Integer downId) {
        this.downId = downId;
    }

Copy the code

The plug-in has completed entity generation and automatically generated the getSet method

The DAO layer generates two files REPAIRJLSQLProvider.java and RepairJLMapper.java

Note that repairJLMapper. Java does not automatically generate the @Mapper annotation. We add it manually

package com.beijin.limengya.mobile.dao;

import com.beijin.limengya.mobile.pojo.*;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;
import java.util.Map;

@Mapper
public interface RepairjlMapper {
    @Delete({
            "delete from COLLECTION_HISTORICAL_MAINTENANCE"."where ID = #{id,jdbcType=INTEGER}"
    })
    int deleteByPrimaryKey(Integer id);

    @Insert({
            "insert into COLLECTION_HISTORICAL_MAINTENANCE (ID, DEVICE_ID, "."REPAIR_NAME, ANCE_NAME, "."FINISH_EXPLAIN, FAILURE_CAUSE, "."PREVENT_ID, TEST_ID, "."PART_ID, REPAIR_USERID, "."WX_USERID, REPAIR_TIME, "."CREATE_TIME, END_TIME, "."FINISH_ID)"."values (#{id,jdbcType=INTEGER}, #{deviceId,jdbcType=VARCHAR}, "."#{repairName,jdbcType=VARCHAR}, #{anceName,jdbcType=VARCHAR}, "."#{finishExplain,jdbcType=VARCHAR}, #{failureCause,jdbcType=VARCHAR}, "."#{preventId,jdbcType=INTEGER}, #{testId,jdbcType=INTEGER}, "."#{partId,jdbcType=INTEGER}, #{repairUserid,jdbcType=INTEGER}, "."#{wxUserid,jdbcType=INTEGER}, #{repairTime,jdbcType=VARCHAR}, "."#{createTime,jdbcType=VARCHAR}, #{endTime,jdbcType=VARCHAR}, "."#{finishId,jdbcType=INTEGER})"
    })
    int insert(Repairjl record);

    @InsertProvider(type=RepairjlSqlProvider.class, method="insertSelective")
    int insertSelective(Repairjl record);

    @Select({
            "select"."ID, DEVICE_ID, REPAIR_NAME, DOWN_ID，ANCE_NAME, FINISH_EXPLAIN, FAILURE_CAUSE, PREVENT_ID, "."TEST_ID, PART_ID, REPAIR_USERID, WX_USERID, REPAIR_TIME, CREATE_TIME, END_TIME, "."FINISH_ID"."from COLLECTION_HISTORICAL_MAINTENANCE"."where ID = #{id,jdbcType=INTEGER}"
    })
    @Results({
            @Result(column="ID", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="DEVICE_ID", property="deviceId", jdbcType=JdbcType.VARCHAR),
            @Result(column="REPAIR_NAME", property="repairName", jdbcType=JdbcType.VARCHAR),
            @Result(column="DOWN_ID", property="downId", jdbcType=JdbcType.INTEGER),
            @Result(column="ANCE_NAME", property="anceName", jdbcType=JdbcType.VARCHAR),
            @Result(column="FINISH_EXPLAIN", property="finishExplain", jdbcType=JdbcType.VARCHAR),
            @Result(column="FAILURE_CAUSE", property="failureCause", jdbcType=JdbcType.VARCHAR),
            @Result(column="PREVENT_ID", property="preventId", jdbcType=JdbcType.INTEGER),
            @Result(column="TEST_ID", property="testId", jdbcType=JdbcType.INTEGER),
            @Result(column="PART_ID", property="partId", jdbcType=JdbcType.INTEGER),
            @Result(column="REPAIR_USERID", property="repairUserid", jdbcType=JdbcType.INTEGER),
            @Result(column="WX_USERID", property="wxUserid", jdbcType=JdbcType.INTEGER),
            @Result(column="REPAIR_TIME", property="repairTime", jdbcType=JdbcType.VARCHAR),
            @Result(column="CREATE_TIME", property="createTime", jdbcType=JdbcType.VARCHAR),
            @Result(column="END_TIME", property="endTime", jdbcType=JdbcType.VARCHAR),
            @Result(column="FINISH_ID", property="finishId", jdbcType=JdbcType.INTEGER)
    })
    Repairjl selectByPrimaryKey(Integer id);

    @UpdateProvider(type=RepairjlSqlProvider.class, method="updateByPrimaryKeySelective")
    int updateByPrimaryKeySelective(Repairjl record);

    @Update({
            "update COLLECTION_HISTORICAL_MAINTENANCE"."set DEVICE_ID = #{deviceId,jdbcType=VARCHAR},"."REPAIR_NAME = #{repairName,jdbcType=VARCHAR},"."DOWN_ID = #{downId,jdbcType=INTEGER},"."ANCE_NAME = #{anceName,jdbcType=VARCHAR},"."FINISH_EXPLAIN = #{finishExplain,jdbcType=VARCHAR},"."FAILURE_CAUSE = #{failureCause,jdbcType=VARCHAR},"."PREVENT_ID = #{preventId,jdbcType=INTEGER},"."TEST_ID = #{testId,jdbcType=INTEGER},"."PART_ID = #{partId,jdbcType=INTEGER},"."REPAIR_USERID = #{repairUserid,jdbcType=INTEGER},"."WX_USERID = #{wxUserid,jdbcType=INTEGER},"."REPAIR_TIME = #{repairTime,jdbcType=VARCHAR},"."CREATE_TIME = #{createTime,jdbcType=VARCHAR},"."END_TIME = #{endTime,jdbcType=VARCHAR},"."FINISH_ID = #{finishId,jdbcType=INTEGER}"."where ID = #{id,jdbcType=INTEGER}"
    })
    int updateByPrimaryKey(Repairjl record);

    @Select("SELECT ID,ANCE_NAME FROM COLLECTION_HISTORICAL_MAINTENANCE " +
            "WHERE DEVICE_ID = #{numbers,jdbcType=VARCHAR}" +
            "AND FINISH_ID = 1 ")
    List<Map<String,Object>> findHistory(String numbers);


    @InsertProvider(type = RepairjlSqlProvider.class , method = "insertSelective")
    int  writeRepair(Repairjl repairjl);

    @Select("SELECT D.ID, D.EQUIPMENT_NUMBER, D.EQUIPMENT_NAME, D.ENG_NAME,F.FACTORY_NAME,T.TYPE_NAME, L.LINE_NAME,P.WORK_NAME,H.REPAIR_NAME " +
                   "FROM COLLECTION_DEVICE_INFORMATION D JOIN COLLECTION_FACTORY F ON D.FACTURER_ID = F.ID " +
                   "JOIN COLLECTION_EQUIPMENT_TYPE T ON D.EQUIPMENT_TYPE = T.ID " +
                   "JOIN COLLECTION_PRODUCTION_LINE L ON D.PRODUCTION_LINE = L.ID " +
                   "JOIN COLLECTION_PRODUCTION_LINE_POSITION P ON D.PRODUCTION_LINE_POSITION = P.ID " +
                   "JOIN COLLECTION_HISTORICAL_MAINTENANCE H ON D.EQUIPMENT_NUMBER = H.DEVICE_ID " +
                   "WHERE H.DEVICE_ID = #{numbers,jdbcType=VARCHAR} AND H.FINISH_ID = 2"
    )
    List<Map<String, Object>> findRepair(String numbers);

    @Select("SELECT ID,ANCE_NAME FROM COLLECTION_HISTORICAL_MAINTENANCE WHERE DEVICE_ID = #{numbers,jdbcType=VARCHAR} AND FINISH_ID = 1")
    List<Map<String, Object>> findProblem(String numbers);

    @Select("SELECT ID,LOCATION_NAME FROM COLLECTION_FAULT_LOCATION WHERE EQUIPMENT_NUMBER = #{numbers,jdbcType=VARCHAR}")
    List<Map<String, Object>> findLocal(String numbers);

    @Select("SELECT ID,INFORMATION_NAME From COLLECTION_FAULT_INFORMATION WHERE EQUIPMENT_NUMBER = #{numbers,jdbcType=VARCHAR} ")
    List<Map<String, Object>> findMessage(String numbers);

    @Select("SELECT ID,CAUSE_NAME FROM COLLECTION_CAUSE_SHUTDOWN ")
    List<Shutdown> findDownList(a);

    @Select("SELECT ID,NUMBER,EQUIPMENT_NAME,FACTORY_NUMBER FROM COLLECTION_SPARE_PARTT_LEDGER")
    List<Map<String, Object>> findPart();

    @Select("SELECT ID,METHOD_NAME FROM COLLECTION_VERIFICATION_METHOD")
    List<YanZhengfs> findFsList(a);

    @Select("SELECT ID,RESULTS_NAME FROM COLLECTION_VERIFICATION_RESULTS")
    List<YanZhengjg> findJgList(a);

    @UpdateProvider(type = RepairjlSqlProvider.class,method = "updateByPrimaryKeySelective")
    int startRepair(Repairjl repair);

    @Select("SELECT ID,FAILURE_CAUSE FROM COLLECTION_HISTORICAL_MAINTENANCE WHERE ID = #{maxId,jdbcType=INTEGER}")
    Map<String, Object> findReason(int selectMaxId);

    @Select("SELECT MAX(ID)FROM COLLECTION_HISTORICAL_MAINTENANCE WHERE DEVICE_ID = #{numbers,jdbcType=VARCHAR} AND FINISH_ID = 1")
    int findMaxId(String numbers);
}
Copy the code

Repairjlsqlprovider. Java is

package com.beijin.limengya.mobile.dao;

import com.beijin.limengya.mobile.pojo.Repairjl;
import org.apache.ibatis.jdbc.SQL;

public class RepairjlSqlProvider {

    public String insertSelective(Repairjl record) {
        SQL sql = new SQL();
        sql.INSERT_INTO("COLLECTION_HISTORICAL_MAINTENANCE");
        
        if(record.getId() ! =null) {
            sql.VALUES("ID"."#{id,jdbcType=INTEGER}");
        }
        
        if(record.getDeviceId() ! =null) {
            sql.VALUES("DEVICE_ID"."#{deviceId,jdbcType=VARCHAR}");
        }
        
        if(record.getRepairName() ! =null) {
            sql.VALUES("REPAIR_NAME"."#{repairName,jdbcType=VARCHAR}");
        }

        if(record.getDownId() ! =null) {
            sql.VALUES("DOWN_ID"."#{downId,jdbcType=INTEGER}");
        }

        if(record.getAnceName() ! =null) {
            sql.VALUES("ANCE_NAME"."#{anceName,jdbcType=VARCHAR}");
        }

        if(record.getFinishExplain() ! =null) {
            sql.VALUES("FINISH_EXPLAIN"."#{finishExplain,jdbcType=VARCHAR}");
        }

        if(record.getFailureCause() ! =null) {
            sql.VALUES("FAILURE_CAUSE"."#{failureCause,jdbcType=VARCHAR}");
        }

        if(record.getPreventId() ! =null) {
            sql.VALUES("PREVENT_ID"."#{preventId,jdbcType=INTEGER}");
        }

        if(record.getTestId() ! =null) {
            sql.VALUES("TEST_ID"."#{testId,jdbcType=INTEGER}");
        }

        if(record.getPartId() ! =null) {
            sql.VALUES("PART_ID"."#{partId,jdbcType=INTEGER}");
        }

        if(record.getRepairUserid() ! =null) {
            sql.VALUES("REPAIR_USERID"."#{repairUserid,jdbcType=INTEGER}");
        }

        if(record.getWxUserid() ! =null) {
            sql.VALUES("WX_USERID"."#{wxUserid,jdbcType=INTEGER}");
        }

        if(record.getRepairTime() ! =null) {
            sql.VALUES("REPAIR_TIME"."#{repairTime,jdbcType=VARCHAR}");
        }

        if(record.getCreateTime() ! =null) {
            sql.VALUES("CREATE_TIME"."#{createTime,jdbcType=VARCHAR}");
        }

        if(record.getEndTime() ! =null) {
            sql.VALUES("END_TIME"."#{endTime,jdbcType=VARCHAR}");
        }

        if(record.getFinishId() ! =null) {
            sql.VALUES("FINISH_ID"."#{finishId,jdbcType=INTEGER}");
        }

        return sql.toString();
    }

    public String updateByPrimaryKeySelective(Repairjl record) {
        SQL sql = new SQL();
        sql.UPDATE("COLLECTION_HISTORICAL_MAINTENANCE");

        if(record.getDeviceId() ! =null) {
            sql.SET("DEVICE_ID = #{deviceId,jdbcType=VARCHAR}");
        }

        if(record.getRepairName() ! =null) {
            sql.SET("REPAIR_NAME = #{repairName,jdbcType=VARCHAR}");
        }

        if(record.getDownId() ! =null) {
            sql.SET("DOWN_ID = #{downId,jdbcType=INTEGER}");
        }

        if(record.getAnceName() ! =null) {
            sql.SET("ANCE_NAME = #{anceName,jdbcType=VARCHAR}");
        }
        
        if(record.getFinishExplain() ! =null) {
            sql.SET("FINISH_EXPLAIN = #{finishExplain,jdbcType=VARCHAR}");
        }
        
        if(record.getFailureCause() ! =null) {
            sql.SET("FAILURE_CAUSE = #{failureCause,jdbcType=VARCHAR}");
        }
        
        if(record.getPreventId() ! =null) {
            sql.SET("PREVENT_ID = #{preventId,jdbcType=INTEGER}");
        }
        
        if(record.getTestId() ! =null) {
            sql.SET("TEST_ID = #{testId,jdbcType=INTEGER}");
        }
        
        if(record.getPartId() ! =null) {
            sql.SET("PART_ID = #{partId,jdbcType=INTEGER}");
        }
        
        if(record.getRepairUserid() ! =null) {
            sql.SET("REPAIR_USERID = #{repairUserid,jdbcType=INTEGER}");
        }
        
        if(record.getWxUserid() ! =null) {
            sql.SET("WX_USERID = #{wxUserid,jdbcType=INTEGER}");
        }
        
        if(record.getRepairTime() ! =null) {
            sql.SET("REPAIR_TIME = #{repairTime,jdbcType=VARCHAR}");
        }
        
        if(record.getCreateTime() ! =null) {
            sql.SET("CREATE_TIME = #{createTime,jdbcType=VARCHAR}");
        }
        
        if(record.getEndTime() ! =null) {
            sql.SET("END_TIME = #{endTime,jdbcType=VARCHAR}");
        }
        
        if(record.getFinishId() ! =null) {
            sql.SET("FINISH_ID = #{finishId,jdbcType=INTEGER}");
        }
        
        sql.WHERE("ID = #{id,jdbcType=INTEGER}");
        
        returnsql.toString(); }}Copy the code

As you can see, two method add and delete methods are generated to concatenate the SQL statement by determining whether the entity class attribute value is null and return the SQL required to form the project.

How do we accomplish our requirements using dynamic SQL

The method calls from Controller to DAO are omitted here, and SQL calls are made directly in mapper. Java

@InsertProvider(type=RepairjlSqlProvider.class, method="insertSelective")
    int insertSelective(Repairjl record);
Copy the code

Write the operations that need to be performed on the method. In this example, add. Type is the generated path of the RepairjlSqlProvider, and Method is the method that needs to be executed in the class.

The above is based on Mybatis annotated development dynamic SQL use, personal understanding is limited, shortcomings of a lot of advice