项目架构

SpringBoot + Mybatis （注解式开发） 快速生成 实体类（ pojo）以及Mapper.java,也就是Dao层：mybatis-generator插件

mybatis-generator插件的使用

<?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" password="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>

将数据库链接改为自己的数据库链接 其中tableName 为数据库 表名 domainObjectName 为对应实体类名

看一下生成的内容

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() {
        return id;
    }

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

    public String getDeviceId() {
        return deviceId;
    }

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

    public String getRepairName() {
        return repairName;
    }

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

    public String getAnceName() {
        return anceName;
    }

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

    public String getFinishExplain() {
        return finishExplain;
    }

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

    public String getFailureCause() {
        return failureCause;
    }

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

    public Integer getPreventId() {
        return preventId;
    }

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

    public Integer getTestId() {
        return testId;
    }

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

    public Integer getPartId() {
        return partId;
    }

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

    public Integer getRepairUserid() {
        return repairUserid;
    }

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

    public Integer getWxUserid() {
        return wxUserid;
    }

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

    public String getRepairTime() {
        return repairTime;
    }

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

    public String getCreateTime() {
        return createTime;
    }

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

    public String getEndTime() {
        return endTime;
    }

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

    public Integer getFinishId() {
        return finishId;
    }

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

    public Integer getDownId() {
        return downId;
    }

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

插件已经将实体生成完毕，并且自动生成了getset方法

dao层生成了两个文件 RepairjlSqlProvider.java和RepairjlMapper.java

这时候需要注意的是RepairjlMapper.java并没有自动生成@Mapper注解，我们手动加上

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();

    @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();

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

    @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);
}

RepairjlSqlProvider.java的内容为

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}");
        
        return sql.toString();
    }
}

可以看到 生成了两个方法 添加和删除方法，通过判断实体类属性值是否为空 对sql语句进行拼接 ，最后组成项目所需要的sql进行返回。

如何使用动态sql完成我们的需求

此处省略从controller到dao的方法调用 ，直接以mapper.java的方法进行SQL的调用 在Mapper.java中找到需要动态sql的方法

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

在方法上写上需要进行的操作，这里以添加为例， type为生成的RepairjlSqlProvider的路径，method为这个类中的需要执行的方法 这里根据需要选择添加方法，启动项目，进行测试 。

以上就是基于Mybatis注解式开发动态sql的使用 ，个人理解有限 ，不足之处多多的指教