Mybatis中简单语法总结

一、分页插件（PageHelper）

需要注意的点：
（1）只有紧跟在PageHelper.startPage(int page,int pageSize)方法后的第一个Mybatis的查询方法会被分页；
（2）不要在系统中使用多个分页插件；
（3）分页插件不支持带有for、update语句的分页，对于带有 for、update 的sql会抛出运行时异常；
（4）分页查询不支持嵌套结果映射（由于嵌套结果方式会导致结果集被折叠，因此分页查询的结果在折叠后总数会减少，因此无法保证分页结果数量正确），所有一对多关系都是懒加载的

（1）该插件目前支持Oracle/Mysql/MariaDB/SQLite/Hsqldb/PostgreSQL六种数据库分页
（2）使用：

• 在pom.xml配置文件中添加相关依赖

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

• 在代码中使用

//设置分页信息
PageHelper.startPage(1,10); //获取第一页，10条内容
List<Country> list=countryMapper.selectId(1); //紧跟的第一个select方法会被分页

Page page=new Page();
//获取数据总条数
Long total=page.getTotal();

二、if 标签中的判断

（1）判断String是否为空

<if test="stringParam!=null and stringParam!=''"></if>

（2）判断Integer是否大于0

<if test="intParam!=null and intParam gt 0"></if>

（3） 判断List是否不为空

<if test="listParam!=null and listParam.size>0"></if>

（4） 判断String是否以某特定字符开头

<if test="stringParam!=null and stringParam.indexOf('user')!=-1"></if>

（5） 判断字符串是否等于特定字符串

<if test=‘stringParam!=null and stringParam==“user”’></if>

判断是否等于特定字符串不能使用下面的方法：

<if test="stringParam!=null and stringParam!='user'"></if>

即最外边使用双引号，里面使用单引号，此写法会报java.lang.NumberFormatException异常
如果要用以上这种写法，应做以下修改：

<if test="stringParam!=null and stringParam!='user'.toString()"></if>

三、resultMap中collection内嵌查询

<resultMap id="RegisterDeviceCheckDtoMap" 
type="com.ideal.saas.common.dto.ib.terminal.RegisterDeviceCheckDto">
    <result column="areaAddress" jdbcType="VARCHAR" property="areaAddress"/>
    <result column="areaTenantId" jdbcType="INTEGER" property="areaTenantId"/>
    <result column="areaType" jdbcType="INTEGER" property="areaType"/>
    <result column="deviceCount" jdbcType="INTEGER" property="deviceCount"/>
    <collection property="deviceTypeIds" 
                ofType="java.lang.Integer" 
                column="{tenantId = areaTenantId}"
                select="queryTenantDeviceTypeIds">
    </collection>
    <collection property="productIds" 
                ofType="java.lang.Integer" 
                column="deviceTypeId"
                select="queryProductIdsByDeviceTypeId">
    </collection>
</resultMap>

<select id="queryTenantDeviceTypeIds" resultType="java.lang.Integer">
    SELECT DISTINCT
    b.device_type_id
    FROM
    (
    	SELECT app_id
    	FROM ib_tenant_model WHERE tenant_id = #{tenantId}
    ) a
    LEFT JOIN ib_app_device_type_relation b ON a.app_id =b.app_id
</select>

<select id="queryProductIdsByDeviceTypeId" resultType="java.lang.Integer">
    SELECT b.product_id
    FROM (
    	SELECT product_type_id 
    	FROM ib_dt_pt_relation WHERE device_type_id = #{deviceTypeId}
    ) a
    INNER JOIN pac_product b ON b.product_type_id = a.product_type_id
</select>

四、Mybatis动态SQL中trim标签的使用

trim是一个格式化的标记，可以完成set或者where标记的功能

• 情景1：

-- prefix：前缀
-- prefixoverride：去掉第一个and或者or

select * from user
<trim prefix="where" prefixoverride="AND|OR">
	<if test="name!=null and name.length()>0">
        and name=#{name}
    </if>
    <if test="gender!=null and gender.length()>0">
        and gender=#{gender}
    </if>
</trim>

假如name和gender的值都不为null的话打印的sql为：

select * from user where name='XX' and gender='XX'

• 情景2：

-- suffixoverride：去掉最后一个逗号（也可以是其他标记）
-- suffix：后缀

update user
<trim prefix="set" suffixoverride="," suffix="where id=#{id}">
    <if test="name!=null and name.length()>0">
        name=#{name},
    </if>
    <if test="gender!=null and gender.length()>0">
        gender=#{gender},
    </if>
</trim>

假如name和gender都不为null的话，打印的sql为：

update user set name=#{name},gender=#{gender} where id=#{id}

五、Mybatis插入数据后返回这个数据在数据库中的主键

• 方式1：

<insert id="insertUser" parameterType="common.User">
    <selectKey keyProperty="id" resultType="int" order="AFTER">
        select LAST_INSERT_ID() 
    </selectKey>
    insert into 
    user
    (name,gender)
    values
    (#{name},#{gender})
</insert>

• 方式2：

<insert id="insertUser" parameterType="common.User" useGeneratedKeys="true" keyProperty="id">
    insert into 
    user
    (name,gender)
    values
    (#{name},#{gender})
</insert>

java代码中使用：

User user=new User("xiaoa",1);
userDao.insertUser(user);
Integer id=user.getId();

六、MySQL中一些简单函数

• CONCAT(,,)：字符串拼接
• DATE_FORMAT()：时间格式化函数

格式	描述
%d	月的天（00-31）
%e	月的天（0-31）
%H	小时（00-23）
%h	小时（01-12）
%i	分钟（00-59）
%m	月（00-12）
%s	秒（00-59）

DATE_FORMAT(event_time,'%Y-%m-%d')

• DATE_ADD(event_time,INTERVAL 1 HOUR)：event_time增加一小时
• 查询字段为空时返回0，否则返回1的两种写法：

-- 方法1：
SELECT
	t.device_type_id,
	t.device_type_name,c.company_id,
	CASE 
		WHEN c.company_id IS NULL THEN 0
		else  1
		END
FROM
	ib_device_type t
LEFT JOIN ib_company_device_type c ON t.device_type_id = c.device_type_id;

-- 方法2：
SELECT
	t.device_type_id,
	t.device_type_name,c.company_id,
	IF(ISNULL(c.company_id),0,1)
FROM
	ib_device_type t
LEFT JOIN ib_company_device_type c ON t.device_type_id = c.device_type_id;

• mysql中not in语句对NULL值的处理：

1）在使用in时以下两个sql语句的执行结果一致：

select count(1) as count from ib_user where user_id in (1,2,null);

select count(1) as count from ib_user where user_id in (1,2);

2）在使用not in时：

-- 返回0
SELECT COUNT(1) as count FROM ib_user WHERE user_id not in (10164,3,NULL);  
-- 返回66
SELECT COUNT(1) as count FROM ib_user WHERE user_id not in (10164,3);

解决办法：

select 
	distinct area_id
from ib_area
where area_route like '12-1%'
	and tenant_id=12
	and area_id not in (
	  	select area_id from ib_energy_threshold where tenant_id =12 and area_id is not NULL
	  );

• mysql中字符串正则匹配：

SELECT area_id FROM 
	ib_area
WHERE area_route REGEXP(
	SELECT REPLACE(con,',','|') FROM(
		SELECT GROUP_CONCAT(con) con FROM(
			SELECT CONCAT('^',area_route,'-','+') AS con FROM(
				SELECT area_route FROM ib_user_area WHERE user_id=10164 AND tenant_id=12
	)temp)temp)temp
)

七、数据库中做连接查询时条件筛选

查询1：

SELECT 
* 
FROM ib_device_role 
WHERE role_id in (1,100);

查询2：

SELECT 
*
FROM ib_user_device_role_relation
WHERE user_id=10164;

查询3：
现在要求查用户10164在租户12下的所有设备角色，如果用左连接直接在on后面加上筛选租户的条件则有：

SELECT
	a.device_role_id,
	b.tenant_id
FROM
	ib_user_device_role_relation a
LEFT JOIN ib_device_role b ON a.device_role_id = b.role_id AND	b.tenant_id = 12
WHERE
	a.user_id=10164;

现在主键为100的设备角色也会被查出来，但是租户id显示为null，这显然是不正确的，有以下两种解决方案：

-- 方案1：
SELECT
	a.device_role_id,
	b.tenant_id
FROM
	ib_user_device_role_relation a
LEFT JOIN ib_device_role b ON a.device_role_id = b.role_id 
WHERE
	a.user_id=10164
AND	b.tenant_id = 12;


-- 方案2：
SELECT
	a.device_role_id,
	b.tenant_id
FROM
	ib_user_device_role_relation a
INNER JOIN ib_device_role b ON a.device_role_id = b.role_id AND	b.tenant_id = 12
WHERE
	a.user_id=10164;

注意：
（1）inner join 中on和where没有区别，左连接和右连接就不一样了；
（2）匹配数据时无论左连接还是右连接，都是拿符合ON后的过滤条件去做数据匹配，不符合的会保留主表数据，用NULL填充副表数据