本文深入解析MyBatis中的结果集映射机制，包括自动映射和自定义映射，并详细介绍了resultType、resultMap、association与collection的使用。同时，文章还覆盖了MyBatis动态SQL的各个元素，如if、choose、trim、foreach等，展示了如何灵活地构建SQL语句。
文章目录
四、Mybatis对于结果集的映射关系
当我们从数据库中拿到结果集的时候，mybatis是如何把结果集封装成一个java对象映射的呢
1.resultType自动映射
通过resultType具体指定一个Java类型，Mybatis就可以把对应的一条数据封装成java类型了，这就是自动映射，但是这里是有一个设置的：autoMappingBehavior默认是PARTIAL，开启自动映射的功能。唯一的要求是列名和javaBean属性名一致。
他这个唯一的要求我们之前在定义book_Name和bookName的时候遇到过，可以使用开启自动驼峰命名规则映射功能，mapUnderscoreToCamelCase=true的方法对他进行自动转换，变得可以识别。
2.resultMap自定义映射
<1> 自定义resultMap，实现高级结果集映射
<2>id ：用于完成主键值的映射
<3>result ：用于完成普通列的映射
<!--自定义映射
type:最终结果集封装的类型
<id>:完成主键列的映射
column:指定结果集的列名
property:指定对象的属性名
<result>:完成普通列的映射
-->
<!--因为我们要通过 isbn 去查，所以id就是作 isbn 的映射，然后再做其余结果集的映射
-->
<resultMap id="MyBook" type="beans.Book">
<id column="isbn" property="isbn"></id>
<result column="book_Name" property="book_Name"></result>
<result column="price" property="price"></result>
</resultMap>
<!--public void getBookResultMap(Integer price);-->
<select id="getBookResultMap" resultMap="MyBook">
select isbn,book_Name,price from book1 where price = #{price}
</select>
下面贴上测试代码
@Test
public void testResultMap() throws IOException {
String resource = "mybatis-resultMap.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream,"development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
BookDao dao = sqlSession.getMapper(BookDao.class);
dao.getBookResultMap(3);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
这时可以成功通过resultMap进行查找
后面又测试了一下如果有两个相同的查找值，会怎么样
@Test
public void testResultMap() throws IOException {
String resource = "mybatis-resultMap.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream,"development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
BookDao dao = sqlSession.getMapper(BookDao.class);
dao.getBookNameResultMap("book01");
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
这样虽然能运行成功但是会报错，因为，有两个相同的可查找值，虽然结果能成功返回两个值，但是会报一下错误
G:\JAVA\jdk1.8.0_131\bin\java.exe -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:H:\IntelliJ IDEA 2018.2.4\lib\idea_rt.jar=3500:H:\IntelliJ IDEA 2018.2.4\bin" -Dfile.encoding=GBK -classpath "H:\IntelliJ IDEA 2018.2.4\lib\idea_rt.jar;H:\IntelliJ IDEA 2018.2.4\plugins\junit\lib\junit-rt.jar;H:\IntelliJ IDEA 2018.2.4\plugins\junit\lib\junit5-rt.jar;G:\JAVA\jdk1.8.0_131\jre\lib\charsets.jar;G:\JAVA\jdk1.8.0_131\jre\lib\deploy.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\access-bridge-64.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\cldrdata.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\dnsns.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\jaccess.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\jfxrt.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\localedata.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\nashorn.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\sunec.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\sunjce_provider.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\sunmscapi.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\sunpkcs11.jar;G:\JAVA\jdk1.8.0_131\jre\lib\ext\zipfs.jar;G:\JAVA\jdk1.8.0_131\jre\lib\javaws.jar;G:\JAVA\jdk1.8.0_131\jre\lib\jce.jar;G:\JAVA\jdk1.8.0_131\jre\lib\jfr.jar;G:\JAVA\jdk1.8.0_131\jre\lib\jfxswt.jar;G:\JAVA\jdk1.8.0_131\jre\lib\jsse.jar;G:\JAVA\jdk1.8.0_131\jre\lib\management-agent.jar;G:\JAVA\jdk1.8.0_131\jre\lib\plugin.jar;G:\JAVA\jdk1.8.0_131\jre\lib\resources.jar;G:\JAVA\jdk1.8.0_131\jre\lib\rt.jar;G:\Users\Administrator\eclipse-workspace\mybatis\out\production\mybatis;G:\Users\Administrator\eclipse-workspace\mybatis\lib\log4j.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\ojdbc6.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\asm-3.3.1.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\junit-4.12.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\cglib-2.2.2.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\c3p0-0.9.1.2.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\log4j-1.2.17.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\mybatis-3.4.1.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\slf4j-api-1.6.1.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\hamcrest-core-1.3.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\ehcache-core-2.6.8.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\slf4j-log4j12-1.6.2.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\mybatis-spring-1.3.0.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\mybatis-ehcache-1.0.3.jar;G:\Users\Administrator\eclipse-workspace\mybatis\lib\mysql-connector-java-5.1.37-bin.jar" com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit4 test.TestResultMapper,testResultMap
log4j:WARN Continuable parsing error 6 and column 77
log4j:WARN 必须为元素类型 "log4j:configuration" 声明属性 "xmlns:xsi"。
log4j:WARN Continuable parsing error 6 and column 77
log4j:WARN 必须为元素类型 "log4j:configuration" 声明属性 "xsi:schemaLocation"。
DEBUG 01-28 12:19:44,771 ==> Preparing: select isbn,book_Name,price from book1 where book_Name = ? (BaseJdbcLogger.java:145)
DEBUG 01-28 12:19:44,913 ==> Parameters: book01(String) (BaseJdbcLogger.java:145)
DEBUG 01-28 12:19:44,967 <== Total: 2 (BaseJdbcLogger.java:145)
org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:81)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
at com.sun.proxy.$Proxy5.getBookNameResultMap(Unknown Source)
at test.TestResultMapper.testResultMap(TestResultMapper.java:22)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Process finished with exit code 0
下面介绍一下自定义映射的级联模式
<!--查询书的价格信息，和书的剩余量的信息-->
<!--public void getSelectTables(String book_Name);-->
<select id="getSelectTables" resultMap="tables">
select bc.book_Name bcn,b.book_Name bn,price,isbn,bc.count
from book_count bc,book1 b
where bc.book_Name='01' and price = #{price}
</select>
<resultMap id="tables" type="beans.Book">
<id column="bn" property="book_Name"></id>
<id column="price" property="price"></id>
<id column="isbn" property="isbn"></id>
<!--级联属性-->
<id column="bcn" property="bookCount.book_Name"></id>
<id column="count" property="bookCount.count"></id>
</resultMap>
<!--
bcn bn price isbn count
01 book01 1 ISBN-001 100-->
并且还在在book表中创建另一张表的实例
private BookCount bookCount;且另一张表设计模式和此表相同。
@Test
public void testSelectTables() throws IOException {
String resource = "mybatis-resultMap.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream,"development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
BookDao dao = sqlSession.getMapper(BookDao.class);
dao.getSelectTables(1);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
<4>association ：一个复杂的类型关联;许多结果将包成这种类型
<select id="getSelectTables" resultMap="tables">
select bc.book_Name bcn,b.book_Name bn,price,isbn,bc.count
from book_count bc,book1 b
where bc.book_Name='01' and price = #{price}
</select>
<resultMap id="tables" type="beans.Book">
<id column="bn" property="book_Name"></id>
<id column="price" property="price"></id>
<id column="isbn" property="isbn"></id>
<!--association:完成关联和联合的映射
property:指定联合属性
javaType:指定联合属性的类型
-->
<association property="bookCount" javaType="beans.BookCount">
<id column="bcn" property="book_Name"></id>
<id column="count" property="count"></id>
</association>
</resultMap>
<5>collection ： 复杂类型的集
这次反过来，先查存库量，再查价格
@Test
public void testCollection() throws IOException {
String resource = "mybatis-resultMap.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream,"development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
BookDao dao = sqlSession.getMapper(BookDao.class);
dao.getCountByName(200);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
<!--public BookCount getCountByName(Integer count);-->
<select id="getCountByName" resultMap="myCount">
select bc.book_Name,bc.count,b.isbn,b.book_Name ,b.price
from book_Count bc left join book1 b on bc.book_Name = '02' where count = #{count}
</select>
<resultMap id="myCount" type="beans.BookCount">
<id column="book_Name" property="book_Name"></id>
<result column="count" property="count"></result>
<!--collection完成集合类型的联合属性的映射
property指定联合属性
ofType指定集合中元素的类型
-->
<collection property="list" ofType="beans.Book">
<id column="isbn" property="isbn"></id>
<id column="book_Name" property="book_Name"></id>
<id column="price" property="price"></id>
</collection>
</resultMap>
五、Mybatis动态SQL
1.简介
- 动态 SQL是MyBatis强大特性之一。极大的简化我们拼装SQL的操作
- 动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似
- MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作
if
choose (when, otherwise)
trim (where, set)
foreach - OGNL（ Object Graph Navigation Language ）对象图导航语言，这是一种强大的
表达式语言，通过它可以非常方便的来操作对象属性。 类似于我们的EL，SpEL等
访问对象属性： person.name
调用方法： person.getName()
调用静态属性/方法： @java.lang.Math@PI
@java.util.UUID@randomUUID()
调用构造方法： new com.atguigu.bean.Person(‘admin’).name
运算符： +,-*,/,%
逻辑运算符： in,not in,>,>=,<,<=,==,!=
注意：xml中特殊符号如”,>,<等这些都需要使用转义字符
2.if 的简单使用
<!--public List<Book> getPriceByIfWhere(Book book);-->
<select id="getPriceByIfWhere" resultType="beans.Book">
select isbn,book_Name,price from book1
where
<if test="isbn!=null && isbn.trim()!=""">
<!--&表示且，istrim表示判空，"是‘-->
isbn = #{isbn}
</if>
<if test="book_Name!=null && book_Name != ''">
and book_Name = #{book_Name}
</if>
<if test="price!=null && price !=''">
and price = #{price}
</if>
</select>
@Test
public void testGetBookByIf() throws IOException {
String resource = "mybatis-DynamicSql.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, "development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
DynamicSqlDao dynamicSqlDao = sqlSession.getMapper(DynamicSqlDao.class);
Book book = new Book();
book.setIsbn("ISBN-001");
book.setBook_Name("book01");
book.setPrice(1);
dynamicSqlDao.getPriceByIfWhere(book);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
if用来拼接sql的时候有一个问题就是，当前面一个值是空，后面值不为空或者判断正确的时候还是会报错，因为第一个的值会被忽略的时候，关键词 and会连接上，导致SQL语法出错。
这时我们就可以用上where了。
3.where 的简单使用
<!--public List<Book> getPriceByIfWhere(Book book);-->
<select id="getPriceByIfWhere" resultType="beans.Book">
select isbn,book_Name,price from book1
<where><!--可以解决第一个出现的and或者or的问题-->
<if test="isbn!=null && isbn.trim()!="""><!--&表示且，istrim表示判空，"是‘-->
and isbn = #{isbn}
</if>
<if test="book_Name!=null && book_Name != ''">
and book_Name = #{book_Name}
</if>
<if test="price!=null && price !=''">
and price = #{price}
</if>
</where>
</select>
4.Trim 的简单使用
<!--public List<Book> getPriceByIfTrim(Book book);-->
<select id="getPriceByIfTrim" resultType="beans.Book">
select isbn,book_Name,price from book1
<!--
prefix:每句话增加一个前缀
prefixOverrides:每句话减少一个前缀
suffix:每句话增加一个后缀
suffixOverrides:每句话减少一个后缀
如果要是去除两个，用'/'连接
-->
<trim prefix="where" suffixOverrides="and">
<if test="isbn!=null && isbn.trim()!="""><!--&表示且，istrim表示判空，"是‘-->
isbn = #{isbn} and
</if>
<if test="book_Name!=null && book_Name != ''">
book_Name = #{book_Name} and
</if>
<if test="price!=null && price !=''">
price = #{price}
</if>
</trim>
</select>
5.Set 的简单使用
<!--public void getPriceBySet(Book book);-->
<update id="getPriceBySet">
update book1
<set><!--清除set语句多余的逗号-->
<if test="book_Name!=null && book_Name != ''">
book_Name = #{book_Name},
</if>
<if test="price!=null">
price = #{price}
</if>
</set>
where isbn = #{isbn}
</update>
6.Choose 的简单使用
mybatis的choose，when和otherwise就相当于java里的switch case 和default一样。
<!--public List<Book> getPriceByChoose(Book book);-->
<select id="getPriceByChoose" resultType="beans.Book">
select isbn,book_Name,price from book1
where
<choose>
<when test="isbn!=null">isbn = #{isbn}</when>
<when test="price!=null">price = #{price}</when>
<when test="book_Name!=null">book_Name = #{book_Name}</when>
<otherwise>price = 0</otherwise>
</choose>
</select>
7.Foreach 的简单使用
<!--public List<Book> getPriceByIsbns(@Param("isbn") List<String> isbns);-->
<select id="getPriceByIsbns" resultType="beans.Book">
select isbn, book_Name,price from book1
where isbn in
<!--
foreach:
collection:指定要迭代的集合
item:局部变量
open:遍历之后在前面再加上一串字符
close:遍历之后在后面再加上一串字符
separator:元素的分隔符
-->
<foreach collection="isbn" item="currID" open="(" close=")" separator=",">
#{currID}
</foreach>
</select>
@Test
public void testGetBookByCollection() throws IOException {
String resource = "mybatis-DynamicSql.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, "development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
DynamicSqlDao dynamicSqlDao = sqlSession.getMapper(DynamicSqlDao.class);
List<String> list = new ArrayList<>();
list.add("ISBN-002");
list.add("ISBN-001");
list.add("ISBN-003");
list.add("ISBN-004");
List<Book> books = dynamicSqlDao.getPriceByIsbns(list);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
扫一扫
933
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
