Mybatis使用技巧总结

1、Mybatis plus 实现分页

1-1、引入分页需要的jar包gav

<dependency>
	<groupId>com.github.pagehelper</groupId>
	 <artifactId>pagehelper-spring-boot-starter</artifactId>
	 <version>1.4.2</version>
</dependency>

1-2、资源配置文件设置

pagehelper:
  helperDialect: mysql #检测适配当前的数据库类型
  reasonable: false # 设置为true时，当前页数<=0时，会自动从1开
  supportMethodsArguments: true #支持通过函数参数进行分页
  params: count=countSql #

1-3、后台服务如何使用

  PageMethod.startPage(page, pageSize);
或者
PageHelper.startPage(page, pageSize);

去掉总记录统计
PageHelper.startPage(page, pageSize).setCount(false);
或者
PageHelper.startPage(page, pageSize,false);

更多详细使用方式参考官方文档

2、mybatis对象操作多条件查询

2-1、多条件组合

例句：where a=1 and (b=2 or c=3)

 QueryWrapper<UserFileEntity> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("IS_DELETE","1");
        Consumer<QueryWrapper<UserFileEntity>> consumer = new Consumer<QueryWrapper<JianmoUserFileEntity>>() {
            @Override
            public void accept(QueryWrapper<UserFileEntity> userFileEntityQueryWrapper) {
                userFileEntityQueryWrapper.eq("USER_ID",userId);
                userFileEntityQueryWrapper.or();
                userFileEntityQueryWrapper.eq("IS_PUBLIC",1);
            }
        };
        queryWrapper.and(consumer);
        queryWrapper.orderByDesc("SORT");

3、orm 框架数据批量操作的方法

3-1、批量插入

     <insert id="saveBatchAccountRole" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO account_role( ROLE_ID,   ACCOUNT_ID) VALUES
        <foreach collection="list" item="accountRole" separator="," >
            (#{accountRole.roleId},#{accountRole.accountId})
        </foreach>
    </insert>

3-2、批量删除

<delete id="deleteAccountRoleByAccountIds" parameterType="java.util.List">
        DELETE FROM account_role  WHERE ACCOUNT_ID in
        <foreach collection="userIdList" item="userid" index="no" open="("
                 separator="," close=")">
            #{userid}
        </foreach>
    </delete>

3-3、批量查询

    <select id="selectSceneLookatListByIds" parameterType="java.util.ArrayList" resultType="com.jmyd.xxx.vo.SceneLookatVO">
        SELECT
        <include refid="Base_Column_List"/>
        FROM  scene_lookat WHERE ID IN
        <foreach collection="list" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>

    </select>

3-4、批量删除

 <update id="updateBatchSceneHotContent" parameterType="java.util.ArrayList">
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            UPDATE scene_hot_content
            <set>
                <if test="name!= null">
                    NAME=#{item.name},
                </if>
                <if test="hotContentId!= null">
                    HOT_CONTENT_ID=#{item.hotContentId},
                </if>
                <if test="ath!= null">
                    ATH=#{item.ath},
                </if>
                <if test="pic!= null">
                    PIC=#{item.pic},
                </if>
                <if test="atv!= null">
                    ATV=#{item.atv},
                </if>
                <if test="lookatId!= null">
                    LOOKAT_ID=#{item.lookatId}
                </if>
            </set>
            WHERE ID=#{item.id}
        </foreach>
    </update>

其他更多操作参见mybatis官方详细文档

4、快速生成表对应的mvc多层结构代码

4-1、代码示例

public class DataMapperUtil {
    public static void main(String[] args) {
        new DataMapperUtil().makeTableCode();
    }
   public void makeTableCode(){
       AutoGenerator gen = new AutoGenerator();

//mybatis-plus 全局配置信息
       GlobalConfig globalConfig = new GlobalConfig();
       globalConfig.setAuthor("author");//作者名
       String projectPath = System.getProperty("user.dir");
//生成文件的输出目录
       globalConfig.setOutputDir(projectPath + "/src/main/newcode");
//生成后是否打开文件夹
       globalConfig.setOpen(false);
//时间类型对应策略：只使用java.util.date代替
       globalConfig.setDateType(DateType.ONLY_DATE);
       globalConfig.setFileOverride(true);//是否覆盖已有文件
       globalConfig.setBaseResultMap(true);//开启BaseResultMap
       globalConfig.setBaseColumnList(true);//开启baseColumnList
//自定义文件命名，注意:%s会自动填充表实体属性
       globalConfig.setMapperName("%sMapper");
       globalConfig.setXmlName("%sMapper");
       globalConfig.setServiceName("%sService");
       globalConfig.setServiceImplName("%sServiceImpl");
       globalConfig.setControllerName("%sController");
       globalConfig.setEntityName("%sEntity");//实体名字
       gen.setGlobalConfig(globalConfig);

//数据源配置
       DataSourceConfig dataSourceConfig = new DataSourceConfig();

//mysql 数据库
        dataSourceConfig.setDbType(DbType.MYSQL);
        dataSourceConfig.setUrl("jdbc:mysql://127.0.0.1:3306/jmydyx?characterEncoding=utf8");
        dataSourceConfig.setDriverName("com.mysql.cj.jdbc.Driver");
        dataSourceConfig.setUsername("");
        dataSourceConfig.setPassword("");
       gen.setDataSource(dataSourceConfig);

//包配置
       PackageConfig packageConfig = new PackageConfig();
//packageConfig.setModuleName("anti_generator");
       packageConfig.setParent("com.jmyd.jmydyx");
       gen.setPackageInfo(packageConfig);

//策略配置
       StrategyConfig strategyConfig = new StrategyConfig();
       strategyConfig.setNaming(NamingStrategy.underline_to_camel); //表名生成策略
       strategyConfig.setColumnNaming(NamingStrategy.underline_to_camel); //列名生成策略
//lomok 模板
//strategyConfig.setEntityLombokModel(true);
//strategyConfig.setRestControllerStyle(true);
//是否生成注解@TableField
       strategyConfig.setEntityTableFieldAnnotationEnable(true);
//需要生成的表
       strategyConfig.setInclude("yuexiu_scene_lookat");
       gen.setStrategy(strategyConfig);
//自定义属性注入，这个必须有
       InjectionConfig cfg=new InjectionConfig() {
           @Override
           public void initMap() { }
       };
       gen.setCfg(cfg);
//执行生成
       gen.execute();
   }

}