mybatis的补充
mybatis的补充
1、 动态SQL语句(SQL拼接)
1.1、 if条件判断+where
<select id="selectAccountByNameAndisdeleted" resultType="com.qy129.entity.Account">
select * from account
<where>
<if test="name !=null and name !=''">
and name like concat('%',#{name},'%')
</if>
<if test="isdeleted != null">
and isdeleted=#{isdeleted}
</if>
</where>
</select>
1.2、choose+when+otherwise
<select id="selectAccountByNameAndisdeletedAsChoose" resultType="com.qy129.entity.Account">
select * from account
<where>
<choose>
<when test="name != null and name != ''">
and name like concat('%',#{name},'%')
</when>
<when test="isdeleted != null">
and isdeleted =#{isdeleted}
</when>
<otherwise>
<![CDATA[and money < 1500]]]>
</otherwise>
</choose>
</where>
</select>
1.3、trim
<select id="selectAccountByNameAndisdeletedAsChoose" resultType="com.qy129.entity.Account">
select * from account
/*prefix:前缀 prefixOverrides:替换前缀
suffix:后缀 suffixOverrides:替换后缀*/x`
<trim prefix="where" prefixOverrides="or|and">
<choose>
<when test="name != null and name != ''">
and name like concat('%',#{name},'%')
</when>
<when test="isdeleted != null">
and isdeleted =#{isdeleted}
</when>
<otherwise>
<![CDATA[and money < 1500]]]>
</otherwise>
</choose>
</trim>
</select>
1.4、foreach
<select id="selectAccountAsForeach" resultType="com.qy129.entity.Account">
select * from account
<where>
<if test="ids !=null and ids.length>0">
id in
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</if>
</where>
</select>
2、mybatis逆向工程
官网链接:http://mybatis.org/generator/configreference/classPathEntry.html
2.1、引入依赖
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.7</version>
</dependency>
2.2、配置内容(放在工程目录下)
<?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>
<!--mysql驱动jar所在的绝对路径 必须低于8.0以下 -->
<classPathEntry location="C:\\repMaven\\mysql\\mysql-connector-java\\5.1.38\\mysql-connector-java-5.1.38.jar"/>
<context id="DB2Tables" targetRuntime="MyBatis3">
<!--去掉注释-->
<commentGenerator>
<property name="suppressAllComments" value="true"/>
</commentGenerator>
<!--连接数据库-->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/fuxi3"
userId="root"
password="">
</jdbcConnection>
<javaTypeResolver>
<property name="forceBigDecimals" value="false"/>
</javaTypeResolver>
<!--实体类生成路径-->
<javaModelGenerator targetPackage="com.qy129.entity" targetProject="./src/main/java">
<property name="enableSubPackages" value="true"/>
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!--映射文件生成路径-->
<sqlMapGenerator targetPackage="mapper" targetProject="./src/main/resources">
<property name="enableSubPackages" value="true"/>
</sqlMapGenerator>
<!--dao层生成路径-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.qy129.dao" targetProject="./src/main/java">
<property name="enableSubPackages" value="true"/>
</javaClientGenerator>
<!--数据库表的信息-->
<table schema="fuxi3" tableName="t_book" domainObjectName="Book"
enableCountByExample="false" enableDeleteByExample="false" enableSelectByExample="false"
enableUpdateByExample="false"
>
<property name="useActualColumnNames" value="true"/>
<generatedKey column="ID" sqlStatement="DB2" identity="true"/>
<columnOverride column="DATE_FIELD" property="startDate"/>
<ignoreColumn column="FRED"/>
<columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR"/>
</table>
<table schema="fuxi3" tableName="t_type" domainObjectName="BookType"
enableCountByExample="false" enableDeleteByExample="false" enableSelectByExample="false"
enableUpdateByExample="false"
>
<property name="useActualColumnNames" value="true"/>
<generatedKey column="ID" sqlStatement="DB2" identity="true"/>
<columnOverride column="DATE_FIELD" property="startDate"/>
<ignoreColumn column="FRED"/>
<columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR"/>
</table>
</context>
</generatorConfiguration>
2.3、进行逆向工程的测试
package com.qy129.test;
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;
import java.io.File;
import java.util.ArrayList;
import java.util.List;
public class TestGenerator {
public static void main(String[] args) throws Exception {
List<String> warnings = new ArrayList<String>();
boolean overwrite = true;
File configFile = new File("generatorConfig.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
myBatisGenerator.generate(null);
}
}
3、缓存
缓存原理图:
在mybatis中,一级缓存默认开启-------》基于session的
二级缓存需要配置--------》基于namespace的
3.1 开启二级缓存,在配置文件中开启
①、在配置文件中开启二级缓存
<settings>
<setting name = "cacheEnabled" value = "true" />
</settings>
②、在对应的映射文件Mapper中使用二级缓存
4、分页插件(PageHelper)
4.1、导依赖
<!--pageHelper的依赖-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.11</version>
</dependency>
4.2、在mybatis配置文件中加入代码
<plugins>
<!-- com.github.pagehelper为PageHelper类所在包名 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 使用下面的方式配置参数,后面会有所有的参数介绍 -->
</plugin>
</plugins>
4.3、通过代码完成分页
AccountDao accountDao = session.getMapper(AccountDao.class);
PageHelper.startPage(2,2); //设置分页的条件
List<Account> all = accountDao.findAll();
PageInfo pageInfo=new PageInfo(all);
System.out.println("总页码:"+pageInfo.getPages());
System.out.println("总条数:"+pageInfo.getTotal());
System.out.println("当前显示的页码:"+pageInfo.getPageNum());
System.out.println("当前页码的数据:"+pageInfo.getList());
w PageInfo(all);
System.out.println(“总页码:”+pageInfo.getPages());
System.out.println(“总条数:”+pageInfo.getTotal());
System.out.println(“当前显示的页码:”+pageInfo.getPageNum());
System.out.println(“当前页码的数据:”+pageInfo.getList());