1、resultMap
适用于复杂查询的输出映射,使用方法:
(1)定义resultMap
(2)使用resultMap作为statement的输出映射类型
2、sql片段
将sql的部分语句单独定义出来,需要使用的时候再引入,提高代码的重用性。
3、实例
(1)User.java
public class User {
private Integer id;
private String name;
private String password;
//get和set方法
}
(2)UserCustomer.java
public class UserCustomer extends User {
//可以扩展用户的信息
}
(3)UserQueryVo.java
public class UserQueryVo {
private List<Integer> ids;
private UserCustomer userCustomer;
public UserCustomer getUserCustomer() {
return userCustomer;
}
public void setUserCustomer(UserCustomer userCustomer) {
this.userCustomer = userCustomer;
}
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
//还可以包装其他的的查询条件,例如订单信息,商品信息等等
}
(4)UserMapper.java
public interface UserMapper {
public List<User> findUserListById(UserQueryVo userQueryVo) throws Exception;
public Integer findUserCountByIds(UserQueryVo userQueryVo) throws Exception;
}
(5)UserMapper.xml
<mapper namespace="com.mybatis.mapper.UserMapper">
<!--
定义resultMap
将select id id_,name name_,password password_ from user where id=#{user.id}和User类中的属性做一个映射关系
type:resultMap最终映射的java对象类型,可以使用别名
id:resultMap的唯一标示
-->
<resultMap type="user" id="userResultMap">
<!--
id:查询结果集的唯一标识
column:查询出来的列名
property: type指定的pojo类型中属性名称
-->
<id column="id_" property="id"/>
<result column="name_" property="name"/>
<result column="password_" property="password"/>
</resultMap>
<!--
定义sql片段
id:sql片段的唯一标识
where不要写在sql片段中
-->
<sql id="query_user_where">
<if test="ids!=null">
<!--
使用 foreach遍历
collection:指定输入对象的集合属性
item:每次遍历生成的对象
open:开始遍历时拼接的串
close:结束遍历时拼接的串
separator:遍历的两个对象中需要使用的拼接串
-->
<foreach collection="ids" item="item_id" open="AND (" close=")" separator="or">
id=#{item_id}
</foreach>
</if>
</sql>
<!--
resultMap:指定定义的resultMap的id,如果这个resultMap在其他的mapper文件中,前面需要加namespace
-->
<select id="findUserListById" parameterType="com.mybatis.bean.UserQueryVo"
resultMap="userResultMap">
select
id id_,
name name_,
password password_
from user
<!--
动态sql:通过表达式进行判断,对sql进行灵活拼接
where:可以自动去掉条件中的第一个and
-->
<where>
<if test="userCustomer!=null">
<if test="userCustomer.id!=null and userCustomer.id!=''">
and user.id=#{userCustomer.id}
</if>
</if>
</where>
</select>
<select id="findUserCountByIds" parameterType="com.mybatis.bean.UserQueryVo"
resultType="Integer">
select count(*) from user
<!--
动态sql:通过表达式进行判断,对sql进行灵活拼接
where:可以自动去掉条件中的第一个and
-->
<where>
<!--
引用sql片段,如果refid指定的id在其他的mapper文件中,前面需要加namespace
-->
<include refid="query_user_where"></include>
</where>
</select>
</mapper>
(6)db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/hwd
jdbc.username=root
jdbc.password=123456
(7)mybatis-config.xml
<configuration>
<properties resource="config/db.properties"></properties>
<typeAliases>
<package name="com.mybatis.bean"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.mybatis.mapper"/>
</mappers>
</configuration>
(8)UserTest.java
public class UserTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws Exception {
String resource = "config/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void findUserListById() throws Exception{
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserCustomer userCustomer = new UserCustomer();
//由于这里使用动态sql,如果不设置指定值,条件不会拼接在sql中
//userCustomer.setId(1);
UserQueryVo userQueryVo = new UserQueryVo();
userQueryVo.setUserCustomer(userCustomer);
List<User> users = userMapper.findUserListById(userQueryVo);
System.out.println("size:" + users.size());
for (User user : users) {
System.out.println(user.toString());
}
sqlSession.close();
}
@Test
public void findUserCountByIds() throws Exception{
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserQueryVo userQueryVo = new UserQueryVo();
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(3);
userQueryVo.setIds(ids);
Integer count = userMapper.findUserCountByIds(userQueryVo);
System.out.println("count:" + count);
sqlSession.close();
}
}