Mybatis整理
mybatis
基本配置文件
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="logPrefix" value="dao."/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<package name="com.mybatis.use.model.country"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="UNPOOLED">
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis_study?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/mybatis/use/mapper/country/CountryMapper.xml"/>
</mappers>
</configuration>
<!--mapper定义-->
<mappers>
<mapper resource = 'xml的相对路径'/>
<package name = '接口所在的包'/>
</mappers>
实体定义
1.为什么使用包装类。因为在动态sql的是有xxx != null 总是true,基本类型会有默认值
XXXMapper
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespeace 接口全限定名称 -->
<mapper namespace="com.mybatis.use.mapper.country.CountryMapper">
<select id="selectAll" resultType="Country">
select id,country_name,country_code
from country
</select>
</mapper>
resultMap省略的情况
1.实体与数据库字段相同可以省略，使用resultType代替。
2.如果配置了 _
多个实体信息返回的情况（不考虑变更resultMap）
1.字段较少的情况下使用继承
/**new resultType*/
@Data
public SysroleExtend extends Sysrole{
private String userName;
}
2.字段很多的情况使用组合
@Data
public SysRole{
private SysUser user
}
映射变更为
as user.userName
关于deleteById的问题
int deleteById(Long id);
int deleteById2(SysUser sysUser);
多参数
1.使用map
2.Param注解
3.arg0... / param1...
注解形式
动态SQL
- if
condition != null / condition == null --- erevyType
condition != '' / condition == '' --- String
where 1=1 可选条件的时候符合sql语法可以用
update 的最后加上id = _#{id}，_保证sql正确性
<insert id="insertSelective" useGeneratedKeys="true" keyProperty="id">
insert into sys_user(
user_name,user_password,
<if test="userEmail != null and userEmail != ''">
user_email,
</if>
user_info,
head_img,
create_time
)values (
#{userName},#{userPassword},
<if test="userEmail != null and userEmail !=''">
#{user_email},
</if>
#{userInfo},#{headImg,jdbcType=BLOB},
#{createTime,jdbcType=TIMESTAMP}
)
</insert>
- choose
实现if...else的功能
<select id="selectByIdOrUsername" resultType="com.mybatis.use.model.rbac.SysUser">
select
<include refid="column"/>
from sys_user
where 1=1
<choose>
<when test="id != null">
and id = #{id}
</when>
<when test="userName != null and userName != ''">
and user_name = #{userName}
</when>
<otherwise>
and 1=2
</otherwise>
</choose>
</select>
- where
以and/or开头，可自动去除
主要可规避where 1=1 / 1=2 这种不太好的语法
- set
处理sql末尾的逗号
set为null时，id = #{id}仍然需要
- trim
prefix 增加前缀
prefixoverrides去掉前缀
suffixOverrides去掉后缀
suffix 增加后缀
- foreach
1.集合查询
2.批量插入
3.动态update
<!-- foreach collection即为@Param指定的名字 -->
<foreach collection="list" open="(" close=")" separator="," item="id" index="i">
#{id}
</foreach>
其他：数组的collection为array
Map collection为key值
循环传入Map则命名为@Param或者_parameter
insert不连续，如果命令没错数据错误自增主键还是会增加
<update id="updateByMap">
update sys_user
set
<foreach collection="_parameter" item="val" index="key" separator=",">
${key} = #{val}
</foreach>
where id = #{id}
</update>
@Test
public void testUpdateByMap(){
SqlSession sqlSession = getSqlSession();
try {
SysUserMapper userMapper = sqlSession.getMapper(SysUserMapper.class);
Map<String,Object> map = new HashMap<>();
map.put("id",1L);
map.put("user_password","12345678");
userMapper.updateByMap(map);
sqlSession.commit();
SysUser sysUser = userMapper.selectById(1L);
log.info("sysUser:{}",gson.toJson(sysUser));
}finally {
sqlSession.close();
}
}
逆向工程
<?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>
<!-- 不生成example代码,且一个表对应一个实体-->
<context id="MySqlContext" targetRuntime="Mybatis3Simple" defaultModelType="flat">
<property name="beginningDelimiter" value=""/>
<property name="endingDelimiter" value=""/>
<!-- 日期和注释配置-->
<commentGenerator>
<property name="suppressDate" value="true"/>
<property name="addRemarkComments" value="true"/>
</commentGenerator>
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/mybatis_study?serverTimezone=UTC&useUnicode=true&characterEncoding=utf8&useSSL=false"
userId="root"
password="root">
<!-- 不生成mysql信息-->
<property name="nullCatalogMeansCurrent" value="true"/>
</jdbcConnection>
<!-- 生成实体的路径-->
<javaModelGenerator targetPackage="com.mybatis.use.gen.model" targetProject="src\main\java">
<property name="trimStrings" value="true"/>
</javaModelGenerator>
<!-- 生成xml的位置-->
<sqlMapGenerator targetPackage="com.mybatis.use.gen.mapper" targetProject="src\main\resources\com\mybatis\use\gen\mapper"/>
<!-- 接口与xm分离-->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.mybatis.use.gen.mapper" targetProject="src\main\java"/>
<!-- 数据库中的所有表，自增ID-->
<table tableName="%">
<generatedKey column="id" sqlStatement="MySql"/>
</table>
</context>
</generatorConfiguration>
package com.mybatis.use.gen;
import lombok.extern.slf4j.Slf4j;
import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.exception.InvalidConfigurationException;
import org.mybatis.generator.exception.XMLParserException;
import org.mybatis.generator.internal.DefaultShellCallback;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
@Slf4j
public class Generator {
public static void main(String[] args) {
List<String> warnings = new ArrayList<>();
//是否覆盖代码
boolean overwrite = true;
InputStream is = Generator.class.getResourceAsStream("/generator/generatorConfig.xml");
ConfigurationParser parser = new ConfigurationParser(warnings);
Configuration config = null;
try {
config = parser.parseConfiguration(is);
} catch (IOException | XMLParserException e) {
e.printStackTrace();
}finally {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
}
DefaultShellCallback callback = new DefaultShellCallback(overwrite);
MyBatisGenerator generator = null;
try {
generator = new MyBatisGenerator(config,callback,warnings);
generator.generate(null);
} catch (InvalidConfigurationException | InterruptedException | IOException | SQLException e) {
e.printStackTrace();
}
for (String warning : warnings){
log.warn("{}",warning);
}
}
}
多表查询
- 一对一
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.use.gen.mapper.SysUserMapper">
<resultMap id="BaseResultMap" type="com.mybatis.use.gen.model.SysUser">
<!--
WARNING - @mbg.generated
This element is automatically generated by MyBatis Generator, do not modify.
-->
<id column="id" jdbcType="BIGINT" property="id" />
<result column="user_name" jdbcType="VARCHAR" property="userName" />
<result column="user_password" jdbcType="VARCHAR" property="userPassword" />
<result column="user_email" jdbcType="VARCHAR" property="userEmail" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="user_info" jdbcType="LONGVARCHAR" property="userInfo" />
<result column="head_img" jdbcType="LONGVARBINARY" property="headImg" />
<!-- 一对一属性配置，处理重名的列-->
<result column="role_id" jdbcType="BIGINT" property="sysRole.id" />
<result column="role_name" jdbcType="VARCHAR" property="sysRole.roleName" />
<result column="enabled" jdbcType="INTEGER" property="sysRole.enabled" />
<result column="create_by" jdbcType="BIGINT" property="sysRole.createBy" />
<result column="role_create_time" jdbcType="TIMESTAMP" property="sysRole.createTime" />
</resultMap>
<select id="selectUserAndRoleById" resultMap="BaseResultMap">
select
u.id,u.user_name,u.user_password,u.user_email,
u.user_info,u.head_img,u.create_time,
r.id role_id,r.role_name,r.enabled enabled,
r.create_by create_by,r.create_time role_create_time
from sys_user u inner join sys_user_role ur on u.id = ur.user_id
inner join sys_role r on ur.role_id = r.id
where u.id = #{id}
</select>
</mapper>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.use.gen.mapper.SysUserMapper">
<resultMap id="BaseResultMap" type="com.mybatis.use.gen.model.SysUser" extends="com.mybatis.use.mapper.rbac.SysUserMapper.sysUserMap">
<!-- 继承 一对一属性配置，处理重名的列-->
<result column="role_id" jdbcType="BIGINT" property="sysRole.id" />
<result column="role_name" jdbcType="VARCHAR" property="sysRole.roleName" />
<result column="enabled" jdbcType="INTEGER" property="sysRole.enabled" />
<result column="create_by" jdbcType="BIGINT" property="sysRole.createBy" />
<result column="role_create_time" jdbcType="TIMESTAMP" property="sysRole.createTime" />
</resultMap>
<select id="selectUserAndRoleById" resultMap="BaseResultMap">
select
u.id,u.user_name,u.user_password,u.user_email,
u.user_info,u.head_img,u.create_time,
r.id role_id,r.role_name,r.enabled enabled,
r.create_by create_by,r.create_time role_create_time
from sys_user u inner join sys_user_role ur on u.id = ur.user_id
inner join sys_role r on ur.role_id = r.id
where u.id = #{id}
</select>
</mapper>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.use.gen.mapper.SysUserMapper">
<resultMap id="BaseResultMap" type="com.mybatis.use.gen.model.SysUser" extends="com.mybatis.use.mapper.rbac.SysUserMapper.sysUserMap">
<!-- 一对一属性配置，处理重名的列-->
<!-- association关联一对一 -->
<association property="sysRole" columnPrefix="role_" javaType="com.mybatis.use.gen.model.SysRole">
<result column="id" jdbcType="BIGINT" property="id" />
<result column="role_name" jdbcType="VARCHAR" property="roleName" />
<result column="enabled" jdbcType="INTEGER" property="enabled" />
<result column="create_by" jdbcType="BIGINT" property="createBy" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
</association>
</resultMap>
<select id="selectUserAndRoleById" resultMap="BaseResultMap">
select
u.id,u.user_name,u.user_password,u.user_email,
u.user_info,u.head_img,u.create_time,
r.id role_id,r.role_name,r.enabled enabled,
r.create_by create_by,r.create_time role_create_time
from sys_user u inner join sys_user_role ur on u.id = ur.user_id
inner join sys_role r on ur.role_id = r.id
where u.id = #{id}
</select>
</mapper>
<!-- 关联映射-->
<resultMap id="BaseResultMap" type="com.mybatis.use.gen.model.SysUser" extends="com.mybatis.use.mapper.rbac.SysUserMapper.sysUserMap">
<association property="sysRole" columnPrefix="role_" resultMap="com.mybatis.use.gen.mapper.SysRoleMapper.BaseResultMap">
</association>
</resultMap>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mybatis.use.gen.mapper.SysUserMapper">
<resultMap id="BaseResultMap" type="com.mybatis.use.gen.model.SysUser" extends="com.mybatis.use.mapper.rbac.SysUserMapper.sysUserMap">
<association property="sysRole" columnPrefix="role_" resultMap="com.mybatis.use.gen.mapper.SysRoleMapper.BaseResultMap">
</association>
</resultMap>
<!-- 关联查询 -->
<resultMap id="userRoleMapSelect" extends="com.mybatis.use.mapper.rbac.SysUserMapper.sysUserMap" type="com.mybatis.use.gen.model.SysUser">
<association property="sysRole" column="{id=role_id}" select="com.mybatis.use.gen.mapper.SysRoleMapper.selectRoleById"/>
</resultMap>
<select id="selectUserAndRoleById" resultMap="userRoleMapSelect">
select
u.id,u.user_name,u.user_password,u.user_email,
u.user_info,u.head_img,u.create_time,
ur.role_id
from sys_user u inner join sys_user_role ur on u.id = ur.user_id
where u.id = #{id}
</select>
</mapper>
延迟加载
使用的情况下才去走查询。
<resultMap id="userRoleMapSelect" extends="com.mybatis.use.mapper.rbac.SysUserMapper.sysUserMap" type="com.mybatis.use.gen.model.SysUser">
<association property="sysRole" fetchType="lazy" column="{id=role_id}" select="com.mybatis.use.gen.mapper.SysRoleMapper.selectRoleById"/>
</resultMap>
<select id="selectUserAndRoleById" resultMap="userRoleMapSelect">
select
u.id,u.user_name,u.user_password,u.user_email,
u.user_info,u.head_img,u.create_time,
ur.role_id
from sys_user u inner join sys_user_role ur on u.id = ur.user_id
where u.id = #{id}
</select>
LazyLoadTriggerMethods有 hashcode() toString(),equls(),clone()
- 一对多
依靠id进行关联，如果没有配置mybatis或通过其他字段进行比较合并
<resultMap id="userRoleListMap" extends="com.mybatis.use.mapper.rbac.SysUserMapper.sysUserMap" type="com.mybatis.use.gen.model.NewSysUser">
<id property="id" column="id"/>
<result property="userName" column="user_name"/>
<result property="userPassword" column="user_password"/>
<result property="userEmail" column="user_email"/>
<result property="userInfo" column="user_info"/>
<result property="headImg" column="head_img" jdbcType="BLOB"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<collection property="sysRoles" columnPrefix="role_" ofType="com.mybatis.use.model.rbac.SysRole">
<id column="id" jdbcType="BIGINT" property="id" />
<result column="role_name" jdbcType="VARCHAR" property="roleName" />
<result column="enabled" jdbcType="INTEGER" property="enabled" />
<result column="create_by" jdbcType="BIGINT" property="createBy" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
</collection>
</resultMap>
<resultMap id="userRoleListMap" extends="com.mybatis.use.mapper.rbac.SysUserMapper.sysUserMap" type="com.mybatis.use.gen.model.NewSysUser">
<id property="id" column="id"/>
<result property="userName" column="user_name"/>
<result property="userPassword" column="user_password"/>
<result property="userEmail" column="user_email"/>
<result property="userInfo" column="user_info"/>
<result property="headImg" column="head_img" jdbcType="BLOB"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<collection property="sysRoles" columnPrefix="role_" ofType="com.mybatis.use.model.rbac.SysRole" resultMap="com.mybatis.use.gen.mapper.SysRoleMapper.BaseResultMap"/>
</resultMap>
类型映射
<typeHandlers>
<!-- 枚举映射器-->
<typeHandler javaType="com.mybatis.use.model.constant.Enabled" handler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>
<!-- 自定义类型-->
<typeHandler javaType="com.mybatis.use.model.constant.NewEnabled" handler="com.mybatis.use.handler.EnumHandler"/>
</typeHandlers>
package com.mybatis.use.handler;
import com.mybatis.use.model.constant.NewEnabled;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
public class EnumHandler implements TypeHandler<NewEnabled> {
private final Map<Integer,NewEnabled> enabledMap = new HashMap<>();
public EnumHandler() {
for (NewEnabled enabled : NewEnabled.values()){
enabledMap.put(enabled.getValue(),enabled);
}
}
@Override
public void setParameter(PreparedStatement preparedStatement, int i, NewEnabled newEnabled, JdbcType jdbcType) throws SQLException {
preparedStatement.setInt(i,newEnabled.getValue());
}
@Override
public NewEnabled getResult(ResultSet resultSet, String s) throws SQLException {
Integer value = resultSet.getInt(s);
return enabledMap.get(value);
}
@Override
public NewEnabled getResult(ResultSet resultSet, int i) throws SQLException {
Integer value = resultSet.getInt(i);
return enabledMap.get(value);
}
@Override
public NewEnabled getResult(CallableStatement callableStatement, int i) throws SQLException {
Integer value = callableStatement.getInt(i);
return enabledMap.get(value);
}
}
缓存集成
默认开启一级缓存（SqlSession）
<flushCache='true'>屏蔽一级缓存
二级缓存（SqlSessionFactory）
配置参数
<mapper>
<cache
eviction ="FIFO"
flushInterval="6000"
size="1024"
readOnly="true"/>
</mapper>
注：@CacheNamespace
readWrite(readOnly)
其他Redis
//TODO
拦截器与插件
集成
源码分析
//TODO
git地址与说明
基于基于《Mybatis从入门到精通》整理
https://github.com/DaveModl/mybatis-study-source/tree/master
浙公网安备 33010602011771号