写在前面
接这篇文章。
14:LanguageDriver
该接口用于获取和封装相关的sql信息以及sql所需要的参数信息:
public interface LanguageDriver {
// 创建用于传递给JDBC statement的实际参数的ParameterHandler对象
ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql);
// 创建封装xml文件中statement信息的SqlSource对象,在启动时加载xml的过程中执行
SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType);
// 同上一个方法,不过数据来源是注解
SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType);
}
接下来我们来自定义一个LanguageDriver,看下如何使用。
14.1:建表
CREATE TABLE `test_language_driver` (
`id` INT(32) NOT NULL AUTO_INCREMENT,
`myname` VARCHAR(63) DEFAULT NULL,
`myage` INT(32) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
14.2:对应实体
public class TestLanguageDriver {
private Integer id;
private String myname;
private Integer myage;
...getter setter tostring...
}
14.3:对应dto
public class TestLanguageDriverDto {
private Integer id;
private String myname;
private Integer myage;
...getter setter tostring...
}
14.4:对应接口
public interface TestLanguageDriverMapper {
TestLanguageDriver fetchById(int id);
void insertOne(TestLanguageDriverDto testLanguageDriverDto);
}
14.5:自定义LanguangeDriver
只是做了sout,证明被调用了!
public class MyLanguageDriver extends XMLLanguageDriver {
@Override
public SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType) {
System.out.println("MyLanguageDriver.createSqlSource");
return super.createSqlSource(configuration, script, parameterType);
}
}
14.6:定义xml
<?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">
<!-- 注意:这里namespace必须使用mapper接口的类全限定名称,不然无法自动生成代理 -->
<mapper namespace="yudaosourcecode.mybatis.mapper.TestLanguageDriverMapper">
<!-- 返回对象定义 -->
<resultMap id="myResultMap" type="yudaosourcecode.mybatis.model.TestLanguageDriver">
<id property="id" column="id" javaType="integer" jdbcType="INTEGER"/>
<result property="myname" column="myname" javaType="string" jdbcType="VARCHAR"/>
<result property="myage" column="myage" javaType="integer" jdbcType="INTEGER"/>
</resultMap>
<!-- 参数对象定义 -->
<parameterMap id="myParameterMap" type="yudaosourcecode.mybatis.dto.TestLanguageDriverDto">
<parameter property="id" javaType="integer" jdbcType="INTEGER"/>
<parameter property="myname" javaType="string" jdbcType="VARCHAR"/>
<parameter property="myage" javaType="integer" jdbcType="INTEGER"/>
</parameterMap>
<insert id="insertOne" parameterMap="myParameterMap">
INSERT INTO `test_language_driver`
(`myname`,
`myage`)
VALUES (#{myname},
#{myage})
</insert>
<select id="fetchById" resultMap="myResultMap" lang="MyLanguageDriver">
SELECT * FROM test_language_driver t WHERE t.`id`=#{id}
</select>
</mapper>
14.7:定义测试代码
public class TestLanguageDriverMapperTest {
private SqlSessionFactory sqlSessionFactory;
private SqlSession sqlSession;
private TestLanguageDriverMapper testLanguageDriverMapper;
///467d8d45399f44fd951793509add17f7
@Before
public void setUp() throws Exception {
String resource = "mybatis/mybatis-config-1.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
this.sqlSessionFactory = sqlSessionFactory;
this.sqlSession = this.sqlSessionFactory.openSession();
this.testLanguageDriverMapper =
this.sqlSession.getMapper(TestLanguageDriverMapper.class);
}
@Test
public void fetchById() {
System.out.println(testLanguageDriverMapper.fetchById(1));
}
@Test
public void insertOne() {
TestLanguageDriverDto testLanguageDriverDto = new TestLanguageDriverDto();
testLanguageDriverDto.setMyage(new Random().nextInt(100));
testLanguageDriverDto.setMyname(UUID.randomUUID().toString());
testLanguageDriverMapper.insertOne(testLanguageDriverDto);
sqlSession.commit();
sqlSession.close();
}
}
执行任意测试代码,可以看到自定义LanguageDriver被调用了,如下:
MyLanguageDriver.createSqlSource
{"id":1,"myage":82,"myname":"930aef2e-f6a8-49dd-a38b-2ab7f8ef77cf"}
Process finished with exit code 0
15:statementinsert
中selectKey
用在<insert/>
标签内部,用来生成主键,下面看个例子。
15.1:表
create table test_selectkey(
id varchar(128) primary key,
myname varchar(64),
myage int(32)
);
15.2:实体
public class TestSelectKey {
private String id;
private String myname;
private int myage;
...getter setter tostring...
}
15.3:dto
public class TestSelectKeyDto {
private String id;
private String myname;
private int myage;
...getter setter tostring...
}
15.4:接口
public interface TestSelectKeyMapper {
void insertByQueryParamter(TestSelectKeyDto testSelectKeyDto);
TestSelectKey getById(String id);
}
15.5:mapper xml
<?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">
<!-- 注意:这里namespace必须使用mapper接口的类全限定名称,不然无法自动生成代理 -->
<mapper namespace="yudaosourcecode.mybatis.mapper.TestSelectKeyMapper">
<resultMap id="myResultMap" type="yudaosourcecode.mybatis.model.TestSelectKey">
<id property="id" column="id" javaType="string" jdbcType="VARCHAR"/>
<result property="myname" column="myname" javaType="string" jdbcType="VARCHAR"/>
<result property="myage" column="myage" javaType="integer" jdbcType="INTEGER"/>
</resultMap>
<parameterMap id="myParameterMap" type="yudaosourcecode.mybatis.dto.TestSelectKeyDto">
<parameter property="id" javaType="string" jdbcType="VARCHAR"/>
<parameter property="myname" javaType="string" jdbcType="VARCHAR"/>
<parameter property="myage" javaType="integer" jdbcType="INTEGER"/>
</parameterMap>
<insert id="insertByQueryParamter" parameterMap="myParameterMap">
<!--
keyProperty:selectKey的结果应该被设置的目标属性
resultType:selectKey结果的类型,不设置也可以,通过mybatis会智能的获取
-->
<selectKey keyProperty="id" keyColumn="id"
resultType="java.lang.String" order="BEFORE">
select uuid()
</selectKey>
INSERT INTO `test_selectkey`
(`id`,
`myname`,
`myage`)
VALUES (#{id},
#{myname},
#{myage})
</insert>
</mapper>
15.6:测试代码
public class TestSelectKeyMapperTest {
private SqlSessionFactory sqlSessionFactory;
private SqlSession sqlSession;
private TestSelectKeyMapper testSelectKeyMapper;
@Before
public void setUp() throws Exception {
String resource = "mybatis/mybatis-config-1.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
this.sqlSessionFactory = sqlSessionFactory;
SqlSession sqlSession = sqlSessionFactory.openSession();
this.sqlSession = sqlSession;
this.testSelectKeyMapper = sqlSession.getMapper(TestSelectKeyMapper.class);
}
@Test
public void insertByQueryParamter() {
TestSelectKeyDto testSelectKeyDto = new TestSelectKeyDto();
testSelectKeyDto.setMyage(new Random().nextInt(100));
testSelectKeyDto.setMyname(UUID.randomUUID().toString());
testSelectKeyMapper.insertByQueryParamter(testSelectKeyDto);
sqlSession.commit();
sqlSession.close();
}
@Test
public void getById() {
}
}
运行insertByQueryParamter
即可插入数据。
16:注解方式配置mybatis
16.1:表
CREATE TABLE test_mybatis_annotation(
id INT(32) AUTO_INCREMENT PRIMARY KEY,
myname VARCHAR(64),
myage INT(32)
);
16.2:实体
public class TestMybatisAnnotation implements Serializable {
private Integer id;
private String myname;
private Integer myage;
...snip getter setter tostring...
}
16.3:dto
public class TestMybatisAnnotationDto {
private Integer id;
private String myname;
private Integer myage;
...snip getter setter tostring...
}
16.4:mapper接口
// 在xml中增删改查的四个标签分别是<insert/>,<delete/>,<update/>,<select/>
// 对应的注解版本是@Insert,@Delete,@Update,@Select
@CacheNamespace(implementation = PerpetualCache.class, eviction = LruCache.class)
public interface TestMybatisAnnotationMapper {
// 增加的方法
@Insert(value = "INSERT INTO `test_mybatis_annotation` (`myname`, `myage`) VALUES (#{myname}, #{myage})")
void insertOne(TestMybatisAnnotationDto testMybatisAnnotationDto);
// 删除的方法
// 修改的方法
// 查询的方法
@Select(value = "SELECT * FROM test_mybatis_annotation t WHERE t.`id`=#{id} ")
TestMybatisAnnotation findOne(int id);
}
16.5:mapper xml
不需要,信息配置在mapper接口中配置。
16.6:全局config配置文件
需要配置mapper接口所在包mappers的扫描,如下是我本地的:
<mappers>
<package name="yudaosourcecode.mybatis.annotation"/>
</mappers>
16.7:测试代码
public class TestMybatisAnnotationMapperTest {
private SqlSessionFactory sqlSessionFactory;
private SqlSession sqlSession;
private TestMybatisAnnotationMapper testMybatisAnnotationMapper;
@Before
public void setUp() throws Exception {
String resource = "mybatis/mybatis-config-annotation.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
this.sqlSessionFactory = sqlSessionFactory;
this.sqlSession = this.sqlSessionFactory.openSession();
this.testMybatisAnnotationMapper =
this.sqlSession.getMapper(TestMybatisAnnotationMapper.class);
}
@Test
public void insertOne() {
TestMybatisAnnotationDto testMybatisAnnotationDto = new TestMybatisAnnotationDto();
testMybatisAnnotationDto.setMyage(new Random().nextInt(100));
testMybatisAnnotationDto.setMyname(UUID.randomUUID().toString());
testMybatisAnnotationMapper.insertOne(testMybatisAnnotationDto);
sqlSession.commit();
sqlSession.close();
}
@Test
public void findOne() {
/*System.err.println("同session第一次查询:");
System.out.println(testMybatisAnnotationMapper.findOne(1));
System.err.println("同session第二次查询:");
System.out.println(testMybatisAnnotationMapper.findOne(1));
*/
System.err.println("不同session第一次查询:");
SqlSession sqlSession1 = sqlSessionFactory.openSession();
System.out.println(sqlSession1.getMapper(TestMybatisAnnotationMapper.class).findOne(1));
sqlSession1.close();
System.err.println("不同session第二次查询:");
SqlSession sqlSession2 = sqlSessionFactory.openSession();
System.out.println(sqlSession2.getMapper(TestMybatisAnnotationMapper.class).findOne(1));
}
}
运行,略。
17:动态sql
使用mapper 接口注解和mapper xml混合方式,分别演示使用注解和在xml中使用动态sql语句。
17.1:建表
CREATE TABLE test_dynamic_sql(
id INT(32) AUTO_INCREMENT PRIMARY KEY,
myname VARCHAR(64),
myage INT(32)
);
17.2:实体
public class TestDynamicSql {
private Integer id;
private String myname;
private Integer myage;
...snip getter setter tostrin...
}
17.3:dto
public class TestDynamicSqlDto {
private Integer id;
private String myname;
private Integer myage;
...snip getter setter tostring...
}
17.4:mapper 接口
public interface TestDynamicSqlMapper {
@Insert(value = { "INSERT INTO `test_dynamic_sql`",
"(`myname`, `myage`)",
"VALUES",
"(#{myname}, #{myage})" })
void insertOneWithSqlAnnotation(TestDynamicSqlDto testDynamicSqlDto);
@Select("<script>" +
"select * from test_dynamic_sql " +
"<if test=\"myname!=null\"> where myname like #{myname}</if>" +
"</script>")
List<TestDynamicSql> queryListWithAnnotationDynamicSql(TestDynamicSqlDto testDynamicSqlDto);
List<TestDynamicSql> queryListWithXmlDynamicSql(TestDynamicSqlDto testDynamicSqlDto);
}
17.5:mapper xml
<?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">
<!-- 注意:这里namespace必须使用mapper接口的类全限定名称,不然无法自动生成代理 -->
<mapper namespace="yudaosourcecode.mybatis.annotation.mapper.TestDynamicSqlMapper">
<resultMap id="myResultMap"
type="yudaosourcecode.mybatis.annotation.model.TestDynamicSql">
<id property="id" column="id" javaType="integer" jdbcType="INTEGER"/>
<result property="myname" column="myname" javaType="string" jdbcType="VARCHAR"/>
<result property="myage" column="myage" javaType="integer" jdbcType="INTEGER"/>
</resultMap>
<select id="queryListWithXmlDynamicSql"
resultMap="myResultMap"
statementType="PREPARED">
SELECT * FROM test_dynamic_sql
<if test="myage!=null">
where myage > #{myage}
</if>
</select>
</mapper>
17.6:测试代码
public class TestDynamicSqlMapperTest {
private SqlSessionFactory sqlSessionFactory;
private SqlSession sqlSession;
private TestDynamicSqlMapper testDynamicSqlMapper;
@Before
public void setUp() throws Exception {
String resource = "mybatis/mybatis-config-annotation.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
this.sqlSessionFactory = sqlSessionFactory;
this.sqlSession = this.sqlSessionFactory.openSession();
this.testDynamicSqlMapper =
this.sqlSession.getMapper(TestDynamicSqlMapper.class);
}
@Test
public void insertOneWithSqlAnnotation() {
TestDynamicSqlDto testDynamicSqlDto = new TestDynamicSqlDto();
testDynamicSqlDto.setMyname(UUID.randomUUID().toString());
testDynamicSqlDto.setMyage(new Random().nextInt(100));
this.testDynamicSqlMapper.insertOneWithSqlAnnotation(testDynamicSqlDto);
this.sqlSession.commit();
this.sqlSession.close();
}
@Test
public void queryListWithAnnotationDynamicSql() {
TestDynamicSqlDto testDynamicSqlDto = new TestDynamicSqlDto();
testDynamicSqlDto.setMyname("%8%");
System.out.println(this.testDynamicSqlMapper
.queryListWithAnnotationDynamicSql(testDynamicSqlDto));
}
@Test
public void queryListWithXmlDynamicSql() {
TestDynamicSqlDto testDynamicSqlDto = new TestDynamicSqlDto();
testDynamicSqlDto.setMyage(8);
System.out.println(this.testDynamicSqlMapper
.queryListWithXmlDynamicSql(testDynamicSqlDto));
}
}
可以运行各个方法进行相关测试。
18:bind标签
本部分在
17:动态sql
基础上分析。
<bind/>
标签用于处理传入参数值,处理后绑定到另一个参数上,执行一个转换的操作,比如模糊查询场景,%
就可以通过这种方式添加,避免程序每个地方都做重复的工作。
在mapper 接口中增加如下方法:
List<TestDynamicSql> queryListWithXmlDynamicSqlBind(TestDynamicSqlDto testDynamicSqlDto);
在mapper xml中提供实现,并使用<bind/>
标签转换myname参数添加%
,如下:
<select id="queryListWithXmlDynamicSqlBind"
resultMap="myResultMap"
statementType="PREPARED">
<!-- 添加%模糊查询符号,这样程序里就需要添加了,不然程序调用几次就要添加几次,而这里只需要一次 -->
<bind name="myname_add_percent" value="'%' + myname + '%'"/>
SELECT * FROM test_dynamic_sql t WHERE t.`myname` LIKE #{myname_add_percent}
</select>
测试代码:
@Test
public void queryListWithXmlDynamicSqlBind() {
TestDynamicSqlDto testDynamicSqlDto = new TestDynamicSqlDto();
testDynamicSqlDto.setMyname("4ad4");
System.out.println(this.testDynamicSqlMapper
.queryListWithXmlDynamicSqlBind(testDynamicSqlDto));
}
运行,如下是我本地的输出日志:
从日志中可以看出来,已经在字符串的前后成功添加了%
了。
19:trim,set,where标签
本部分在
17:动态sql
基础上分析。
这三个标签是在动态SQL语句中,用于处理因为某些if条件不满足而导致的sql语法错误问题。
19.1:where
where标签用于过滤结果,有时候我们在mybatis中会这样写:
SELECT * FROM test_dynamic_sql t where
<if test="myname!=null">
t.`myname` LIKE #{myname}
</if>
<if test="myage!=null">
and myage > #{myage}
</if>
但是此时如果是第一个条件不满足sql语句就变为SELECT * FROM test_dynamic_sql t where and myage > #{myage}
,很明显不满足sql语法,或者是2个条件都不满足则sql语句变为SELECT * FROM test_dynamic_sql t where
,同样是不满足sql语法,<where/>
标签就是用来解决这个问题的,会动态决定是否添加where关键字,以及是否需要删除where关键字后的and关键字,如下接口配置和mapper xml配置:
List<TestDynamicSql> queryListWithXmlDynamicSqlWhere(TestDynamicSqlDto testDynamicSqlDto);
<select id="queryListWithXmlDynamicSqlWhere"
resultMap="myResultMap"
statementType="PREPARED">
SELECT * FROM test_dynamic_sql t
<where>
<if test="myname!=null">
t.`myname` LIKE #{myname}
</if>
<if test="myage!=null">
and myage > #{myage}
</if>
</where>
</select>
就能解决上述描述的问题了,主要做了如下的几件事:
1:动态添加where前缀,如果是需要的话
2:动态删除where关键字后的and,如果是需要的话
对于上述的过程,mybatis还提供了<trim/>
标签达到同样的效果,该标签信息如下:
<!ATTLIST trim
prefix CDATA #IMPLIED
prefixOverrides CDATA #IMPLIED
suffix CDATA #IMPLIED
suffixOverrides CDATA #IMPLIED
>
可以看到有2对属性分别是prefix ,prefixOverrides
,suffix,suffixOverrides
,作用是添加前缀/后缀,以及替换前后缀指定字符
,比如设置prefix="where" prefixOverrides="and"
意思就是添加where前缀,并动态删除where后的and关键字,上例修改mapper xml 如下:
<select id="queryListWithXmlDynamicSqlWhere"
resultMap="myResultMap"
statementType="PREPARED">
SELECT * FROM test_dynamic_sql t
<!--<where>
<if test="myname!=null">
t.`myname` LIKE #{myname}
</if>
<if test="myage!=null">
or myage > #{myage}
</if>
</where>-->
<!-- prefix="where":动态添加where前缀
prefixOverrides="or|and":动态删除or或者是and关键字
-->
<trim prefix="where" prefixOverrides="or|and">
<if test="myname!=null">
t.`myname` LIKE #{myname}
</if>
<if test="myage!=null">
and myage > #{myage}
</if>
</trim>
</select>
19.2:set
当我们进行更新数据的时候可能会写如下的sql语句:
<update id="queryListWithXmlDynamicSqlSet" parameterType="yudaosourcecode.mybatis.annotation.dto.TestDynamicSqlDto">
UPDATE test_dynamic_sql SET
<if test="myname!=null">myname=#{myname},</if>
<if test="myage!=null">myage=#{myage}</if>
WHERE id=2
</update>
此时,当第二个条件为空时生成的sql语句为UPDATE test_dynamic_sql SET myname=#{myname},
,当二者都为空时生成的sql语句是UPDATE test_dynamic_sql SET
,这两种情况都会有sql的语法错误问题,为了解决这类问题,mybatis引入了<set/>
标签,可以修改如下:
<update id="queryListWithXmlDynamicSqlSet" parameterType="yudaosourcecode.mybatis.annotation.dto.TestDynamicSqlDto">
<!-- UPDATE test_dynamic_sql SET -->
<!-- <if test="myname!=null">myname=#{myname},</if>-->
<!-- <if test="myage!=null">myage=#{myage}</if>-->
<!-- WHERE id=2-->
UPDATE test_dynamic_sql
<set>
<if test="myname!=null">myname=#{myname},</if>
<if test="myage!=null">myage=#{myage}</if>
</set>
WHERE id=2
</update>
此时mybatis会替我们去除可能多余的,
或者是set
关键字,类似于where中使用trim解决这个问题,此处也是可以的,因为trim可以动态的设置要添加的前后缀以及需要删除的前后缀,即,trim对于此类问题提供了通用的解决方案,修改如下:
<update id="queryListWithXmlDynamicSqlSet" parameterType="yudaosourcecode.mybatis.annotation.dto.TestDynamicSqlDto">
<!-- UPDATE test_dynamic_sql SET -->
<!-- <if test="myname!=null">myname=#{myname},</if>-->
<!-- <if test="myage!=null">myage=#{myage}</if>-->
<!-- WHERE id=2-->
<!--UPDATE test_dynamic_sql
<set>
<if test="myname!=null">myname=#{myname},</if>
<if test="myage!=null">myage=#{myage}</if>
</set>
WHERE id=2-->
UPDATE test_dynamic_sql
<trim prefix="set" suffixOverrides=",">
<if test="myname!=null">myname=#{myname},</if>
<if test="myage!=null">myage=#{myage}</if>
</trim>
WHERE id=2
</update>
20:foreach
foreach的作用就是通过给定的数据源按照设置的格式生成字符串,最终用于拼接生成sql语句,我们只需要根据自己想要的目标字符串来设置相关的信息即可。
本部分在17:动态sql
基础上分析。
我们来看个动态生成in语句中的('ele1', 'ele2')
的例子,创建mapper 接口和mapper xml,如下:
List<TestDynamicSql> queryListWithXmlDynamicSqlForeach(List<Integer> idsList);
<select id="queryListWithXmlDynamicSqlForeach" resultMap="myResultMap" parameterType="java.util.List">
SELECT * FROM test_dynamic_sql t
WHERE t.`id` IN
<foreach collection="list" index="" open="(" close=")" item="itemId" separator=",">
#{itemId}
</foreach>
</select>
测试代码:
@Test
public void queryListWithXmlDynamicSqlForeach() {
List<Integer> idsList = new ArrayList() {
{
add(1);
add(2);
}
};
System.out.println(this.testDynamicSqlMapper.queryListWithXmlDynamicSqlForeach(idsList));
}
运行如下是我本地日志:
2021-08-18 16--14---16,630 [dongshidaddy--^^--yudaosourcecode.mybatis.annotation.mapper.TestDynamicSqlMapper.queryListWithXmlDynamicSqlForeach]-[DEBUG] ==> Preparing: SELECT * FROM test_dynamic_sql t WHERE t.`id` IN ( ? , ? )
2021-08-18 16--14---16,650 [dongshidaddy--^^--yudaosourcecode.mybatis.annotation.mapper.TestDynamicSqlMapper.queryListWithXmlDynamicSqlForeach]-[DEBUG] ==> Parameters: 1(Integer), 2(Integer)
2021-08-18 16--14---16,681 [dongshidaddy--^^--yudaosourcecode.mybatis.annotation.mapper.TestDynamicSqlMapper.queryListWithXmlDynamicSqlForeach]-[DEBUG] <== Total: 2
[{"id":1,"myage":65,"myname":"8fb20cb1-2752-4181-87a5-3adb30ee72df"}, {"id":2,"myage":8,"myname":"%4ad4%"}]