mybatis复杂映射
1. 类型名对应
当实体类与表中字段完全一致时，mapper文件里返回类型用resultType，否则要用resultMap,并且建立resultMap映射
package com.rf.domain; import java.util.Date; public class User { // `id` INT(11) NOT NULL AUTO_INCREMENT, private int id; //`username` VARCHAR(32) NOT NULL COMMENT '用户名称', private String usernameabc; //`birthday` DATETIME DEFAULT NULL COMMENT '生日', private Date birthdayabc; //`sex` CHAR(1) DEFAULT NULL COMMENT '性别', private String sexabc; //`address` VARCHAR(256) DEFAULT NULL COMMENT '地址',PRIMARY KEY (`id`) private String addressbc; //getter and setter 省略 }
public interface UserMapper { public List<User> findAllResultMap(); }
<resultMap id="userResultMap" type="user">
<id column="uid" property="id"></id>
<result column="NAME" property="username"></result>
<result column="PASSWORD" property="username"></result>
</resultMap>
<select id="findAllResultMap" resultMap="userResultMap">
SELECT id AS uid,username AS NAME,password AS PASSWORD FROM USER
</select>
2. 多条件查询
- 使用 #{arg0}-#{argn} 或者 #{param1}-#{paramn} 获取参数
- 使用注解，引入 @Param() 注解获取参数
- 使用pojo对象传递参数
<!-- 多条件查询方式1 利用arg或param传递-->
<select id="findByNameAndId" resultMap="UserMapperResult" parameterType="user">
<!--select * from user where id = #{arg0} and username = #{arg1}-->
<!-- 多条件查询方式2 利用@param传递-->
<!--select * from user where id = #{id} and username = #{username}-->
select * from user where id = #{id} and username = #{usernameabc}
</select>
//相同的包名，接口名，方法名，返回类型及参数类型 public interface UserMapper { public List<User> findAll(); //方式1 // public User findByNameAndId(int id,String username); //方式2 <!-- 多条件查询方式2 利用@param传递--> // public User findByNameAndId(@Param("id") int id, @Param("username") String username); //方式3 <!-- 多条件查询方式2 利用对象传递--> public User findByNameAndId(User user); }
@Test public void testUserMapperResult() throws IOException { //1.加载核心配置文件import org.apache.ibatis.io.Resources; InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.获取SqlSessionFactory 工厂对象 SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); //3.获取会话对象 SqlSession sqlSession = sessionFactory.openSession(); //映射查询 UserMapper mapper = sqlSession.getMapper(UserMapper.class); // User user = mapper.findByNameAndId(2,"应颠"); User user = new User(); user.setId(2); user.setUsernameabc("应颠"); User user1 = mapper.findByNameAndId(user); System.out.println(user1); }
3. 模糊查询
<mapper namespace="com.r'f.mapper.UserMapper"> <select id="findByUsername1" parameterType="string" resultType="user"> select * from user where username like #{username} </select> </mapper>
<mapper namespace="com.rf.mapper.UserMapper"> <!--不推荐使用，因为会出现sql注入问题--> <select id="findByUsername2" parameterType="string" resultType="user"> select * from user where username like '${value}' </select> </mapper>
- #{} :表示一个占位符号，可以实现preparedStatement向占位符中设置值，自动进行java类型和jdbc类型转换，#{}可以有效防止sql注入。#{} 可以接收简单类型值或pojo属性值(实体类)。如果parameterType传输单个简单类型值， #{} 括号中名称随便写。
- ${} :表示拼接sql串通过 ${} 可以将parameterType 传入的内容拼接在sql中且不进行jdbc类型转换，会出现sql注入问题。${} 可以接收简单类型值或pojo属性值。如果parameterType传输单个简单类型值， ${} 括号中只能是value。
4.返回主键
- 添加属性useGeneratedKeys：useGeneratedKeys="true" 声明返回主键 keyProperty="id" 把返回主键的值，封装到实体的id属性中，只适用于主键自增的数据库，mysql和sqlserver支持，oracle不支持
- 借助selectKey 标签
<selectKey keyColumn="id" keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID();
</selectKey>
- selectKey 适用范围广，支持所有类型数据库keyColumn="id" 指定主键列名，keyProperty="id" 指定主键封装到实体的id属性中，resultType="int" 指定主键类型，order="AFTER" 设置在sql语句执行前（后），执行此语句，Oracle用before
<!-- 返回主键-->
<insert id="save" parameterType="user" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
INSERT INTO user(username,birthday,sex,address) values(#{usernameabc},#{birthdayabc},#{sexabc},#{addressabc})
</insert>
<!-- 返回主键2-->
<insert id="save2" parameterType="user">
<selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
select LAST_INSERT_ID()
</selectKey>
INSERT INTO user(username,birthday,sex,address) values(#{usernameabc},#{birthdayabc},#{sexabc},#{addressabc})
</insert>
//根据username模糊查询user表
@Test
public void testsave2() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = factory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsernameabc("子慕");
user.setAddressabc("北京");
user.setBirthdayabc(new Date());
user.setSexabc("男");
mapper.save2(user);
sqlSession.commit();
System.out.println("返回主键:" + user.getId());
}
5.动态sql
- if标签 完成多标签查询
<!-- where标签相当于 where 1=1，但是如果没有条件，就不会拼接where关键字 --> <select id="findByIdAndUsernameIf" parameterType="user" resultType="user"> SELECT * FROM `user` <where> <if test="id != 0"> AND id = #{id} </if> <if test="username != null"> AND username = #{username} </if> </where> </select>
- set标签:动态更新表数据，如果该属性有值就更新，没有值不做处理
<!-- set标签在更新的时候，自动加上set关键字，然后去掉最后一个条件的逗号 --> <update id="updateIf" parameterType="user"> UPDATE `user` <set> <if test="username != null"> username = #{username}, </if> <if test="birthday != null"> birthday = #{birthday}, </if> <if test="sex !=null"> sex = #{sex}, </if> <if test="address !=null"> address = #{address}, </if> </set> WHERE id = #{id} </update>
- foreach标签:实现多值查询，遍历
<foreach>标签用于遍历集合，它的属性：
• collection：代表要遍历的集合元素,collection
• open：代表语句的开始部分
• close：代表结束部分
• item：代表遍历集合的每个元素，生成的变量名
• sperator：代表分隔符
<!-- 如果查询条件为普通类型 List集合，collection属性值为：collection 或者 list --> <select id="findByList" parameterType="list" resultType="user" > SELECT * FROM `user` <where> <foreach collection="collection" open="id in(" close=")" item="id" separator=","> #{id} </foreach> </where> </select>
<!--
如果查询条件为普通类型 Array数组，collection属性值为：array
-->
<select id="findByArray" parameterType="int" resultType="user">
SELECT * FROM `user`
<where>
<foreach collection="array" open="id in(" close=")" item="id"
separator=",">
#{id}
</foreach>
</where>
</select>
- SQL语句片段抽取:映射文件中可将重复的 sql 提取出来，使用时用 include 引用即可，最终达到 sql 重用的目的
<!--抽取的sql片段--> <sql id="selectUser"> SELECT * FROM `user` </sql> <select id="findByList" parameterType="list" resultType="user" > <!--引入sql片段--> <include refid="selectUser"></include> <where> <foreach collection="collection" open="id in(" close=")" item="id" separator=","> #{id} </foreach> </where> </select>
6.核心配置文件标签
- plugins标签
- MyBatis可以使用第三方的插件来对功能进行扩展，分页助手PageHelper是将分页的复杂操作进行封装，使用简单的方式即可获得分页的相关数据
- 导入通用PageHelper的坐标 --在mybatis核心配置文件中配置PageHelper插件 --测试分页数据获取
@Test public void testPageHelper() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml"); SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = factory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); //设置分页参数 PageHelper.startPage(3,2); List<User> users = mapper.findAll(); for (User user : users) { System.out.println(user); } System.out.println(users.size()); //其他分页的数据 PageInfo<User> pageInfo = new PageInfo<User>(users); System.out.println("总条数："+pageInfo.getTotal()); System.out.println("总页数："+pageInfo.getPages()); System.out.println("当前页："+pageInfo.getPageNum()); System.out.println("每页显示长度："+pageInfo.getPageSize()); System.out.println("是否第一页："+pageInfo.isIsFirstPage()); System.out.println("是否最后一页："+pageInfo.isIsLastPage()); }
7. 多表查询
- 一对一，用association标签关联，property="user" 封装实体的属性名，javaType="user" 封装实体的属性类型
<mapper namespace="com.mapper.OrdersMapper">
<resultMap id="ordermap" type="com.domain.Orders">
<id property="id" column="id"></id>
<result property="uid" column="uid"></result>
<result property="total" column="total"></result>
<result property="ordertime" column="ordertime"></result>
<association property="user" javaType="com.domain.User">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="birthday" property="birthday"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
<!-- 查询所有订单，与此同时查询出每个订单所属的用户-->
<select id="findAllWithUser" parameterType="com.domain.Orders" resultMap="ordermap">
select * from orders o left join user u on o.uid= u.id;
</select>
</mapper>
- 一对多，用collection标签管理，property="orderList" 封装到集合的属性名，ofType="order”封装集合的泛型类型
<resultMap id="userMap" type="com.domain.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<result property="birthday" column="birthday"></result>
<result property="sex" column="sex"></result>
<result property="address" column="address"></result>
<!-- property对应实体类中的属性，ofType 表明封装的类型 -->
<collection property="ordersList" ofType="com.domain.Orders">
<id property="id" column="oid"></id>
<result property="uid" column="uid"></result>
<result property="total" column="total"></result>
<result property="ordertime" column="ordertime"></result>
</collection>
</resultMap>
<!-- //查询所有用户，与此同时查询出该用户具有的订单-->
<select id="findAllWithOrder" resultMap="userMap">
SELECT *,o.id oid FROM USER u LEFT JOIN orders o ON u.id=o.uid
</select>
- 多对多，用collection标签管理，property="orderList" 封装到集合的属性名，ofType="order”封装集合的泛型类型
<resultMap id="userMap" type="com.domain.User"> <id property="id" column="id"></id> <result property="username" column="username"></result> <result property="birthday" column="birthday"></result> <result property="sex" column="sex"></result> <result property="address" column="address"></result> <!-- property对应实体类中的属性，ofType 表明封装的类型 --> <collection property="ordersList" ofType="com.domain.Orders"> <id property="id" column="oid"></id> <result property="uid" column="uid"></result> <result property="total" column="total"></result> <result property="ordertime" column="ordertime"></result> </collection> <collection property="roleList" ofType="com.domain.Sys_role"> <!-- column对应的是 sys_user_role的属性roleid--> <id property="id" column="roleid"></id> <result property="roleName" column="roleName"></result> <result property="roleDesc" column="roleDesc"></result> </collection> </resultMap> <select id="findAllRoles" resultMap="userMap"> SELECT * ,sr.roleDesc,sr.rolename FROM USER u LEFT JOIN sys_user_role sur ON u.id=sur.userid LEFT JOIN sys_role sr ON sur.roleid =sr.id </select>
8. 嵌套查询
- 一对一:使用<resultMap>+<association>做配置，通过column条件，执行select查询
RoleMapper.xml
<!--一对一嵌套查询--> <resultMap id="orderMap" type="order"> <id column="id" property="id"></id> <result column="ordertime" property="ordertime"></result> <result column="money" property="money"></result> <!--根据订单中uid外键，查询用户表--> <association property="user" javaType="user" column="uid" select="com.lagou.mapper.UserMapper.findById"></association> </resultMap> <select id="findAllWithUser" resultMap="orderMap" > SELECT * FROM orders </select>
UserMapper.xml
<select id="findById" parameterType="int" resultType="user"> SELECT * FROM `user` where id = #{uid} </select>
UserMapper.java
public interface UserMapper {
public User findById(Integer id);
}
- 一对多:使用<resultMap>+<collection>做配置，通过column条件，执行select查询
OdrersMapper.xml <select id="findByUid" resultType="com.domain.Orders" parameterType="int"> select * from orders where uid=#{id} </select>
UserMapper.xml <collection property="ordersList" ofType="com.domain.Orders" column="id" select="com.mapper.OrdersMapper.findByUid"></collection> <select id="findAllWithOrders" resultMap="userMap"> select * from user </select>
- 多对多:使用<resultMap>+<collection>做配置，通过column条件，执行select查询
UserMapper.xml <collection property="roleLise" ofType="com.domain.Sys_role" column="userid" select="com.mapper.Sys_roleMapper.findByUid"></collection> <select id="findAllWithRole" resultMap="userMap"> select * from user </select> Sys_roleMapper.xml <select id="findByUid" parameterType="int" resultType="com.domain.Sys_role"> SELECT sr.id,sr.roleDesc,sr.rolename FROM sys_role sr LEFT JOIN sys_user_role sur ON sr.id=sur.roleid WHERE sur.userid=#{id} </select>
- 优点：简化多表查询操作
- 缺点：执行多次sql语句，浪费数据库性能
浙公网安备 33010602011771号