目录
0. 文章目录
Mybatis映射文件
动态SQL
关联查询
1. Mybatis映射文件
映射文件中select的两个参数重要参数:parameter和resultType
parameter: 输入映射,定义输入到sql中的映射类型,${value}表示使用参数将${value}替换,做字符串拼接。如果是取简单数量类型的参数,括号中的值必须为value。
resultType: 输出映射,定义结果映射类型。
1.1 输入映射
输入映射支持的数据类型:
- 基本数据类型:基础类型以及包装类、String
- POJO
- Map
- 包装的POJO:一个pojo中有pojo属性
输入映射参数为map类型举例
/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.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"> <mapper namespace="com.itzhouq.mybatis.mapper.UserMapper"> <!-- statementId --> <select id="findById" parameterType="int" resultType="com.itzhouq.mybatis.pojo.User" > select * from user where id = #{id} </select> <insert id="insertUser" parameterType="com.itzhouq.mybatis.pojo.User"> <selectKey resultType="int" keyProperty="id" order="AFTER"> <!-- 返回当前事务最后产生的id值 --> SELECT LAST_INSERT_ID(); </selectKey> insert into user(username, birthday, sex, address) values(#{username}, #{birthday}, #{sex}, #{address}); </insert> <select id="findUserByMap" parameterType="map" resultType="com.itzhouq.mybatis.pojo.User"> select * from user where username like '%${username}%' and sex = #{sex} and address=#{address} </select> </mapper>
/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.java
package com.itzhouq.mybatis.mapper; import java.util.List; import java.util.Map; import com.itzhouq.mybatis.pojo.User; public interface UserMapper { public User findById(int id); public void insertUser(User user); //映射参数为map类型 public List<User> findUserByMap(Map<String, Object> map); }
/mybatis-day02/src/com/itzhouq/mybatis/test/UserMapperTest.java
package com.itzhouq.mybatis.test; import java.io.IOException; import java.util.HashMap; import java.util.List; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Before; import org.junit.Test; import com.itzhouq.mybatis.mapper.UserMapper; import com.itzhouq.mybatis.pojo.User; public class UserMapperTest { SqlSessionFactory sqlSessionFactory = null; @Before public void init() throws IOException { SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); sqlSessionFactory = builder.build(Resources.getResourceAsStream("sqlMapConfig.xml")); } @Test public void testFindById() { SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findById(28); System.out.println(user); sqlSession.close(); } @Test public void testFindByMap() { SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); HashMap<String, Object> map = new HashMap<String, Object>(); // select * from user where username like '%张%' and sex = '1' and address='河南郑州' // select * from user where username like '%${username}%' and sex = #{sex} and address=#{address} map.put("username", "张"); map.put("sex", "1"); map.put("address", "河南郑州"); List<User> users= mapper.findUserByMap(map); System.out.println(users); sqlSession.close(); } }
- 测试testFindByMap得到两个user对象
- 输入映射参数为包装的POJO类型举例
在pojo中新建一个类QueryVo
/mybatis-day02/src/com/itzhouq/mybatis/pojo/QueryVo.java
package com.itzhouq.mybatis.pojo; public class QueryVo { private User user; public User getUser() { return user; } public void setUser(User user) { this.user = user; } }
接口中/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.java
package com.itzhouq.mybatis.mapper; import java.util.List; import java.util.Map; import com.itzhouq.mybatis.pojo.QueryVo; import com.itzhouq.mybatis.pojo.User; public interface UserMapper { public User findById(int id); public void insertUser(User user); //映射参数为map类型 public List<User> findUserByMap(Map<String, Object> map); //映射参数为包装的POJO类型 public List<User> findUserByQueryVo(QueryVo queryVo); }
测试文件中/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.xml添加
<select id="findUserByQueryVo" parameterType="queryVo" resultType="user"> <!-- queryVo user username --> select * from user where username like '%${user.username}%' </select>
测试类/mybatis-day02/src/com/itzhouq/mybatis/test/UserMapperTest.java
@Test public void testFindByQueryVo() { SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); QueryVo queryVo = new QueryVo(); User user = new User(); user.setUsername("张"); queryVo.setUser(user); List<User> users= mapper.findUserByQueryVo(queryVo); System.out.println(users); sqlSession.close(); }
1.2 输出映射
- 输出映射支持的数据类型:
- 基本数据类型:基础类型以及包装类、String
- POJO
- Map
- List
输出映射参数为map类型举例
接口com.itzhouq.mybatis.mapper.UserMapper
package com.itzhouq.mybatis.mapper; import java.util.List; import java.util.Map; import com.itzhouq.mybatis.pojo.QueryVo; import com.itzhouq.mybatis.pojo.User; public interface UserMapper { public User findById(int id); public void insertUser(User user); //映射参数为map类型 public List<User> findUserByMap(Map<String, Object> map); //映射参数为包装的POJO类型 public List<User> findUserByQueryVo(QueryVo queryVo); //输出映射类型为int型 public int findUserCount(); //输出映射参数为map类型 public Map findById2(int id); }
映射配置文件/mybatis-day02/src/com/itzhouq/mybatis/mapper/UserMapper.xml中添加
<select id="findUserCount" resultType="int"> select count(*) from user; </select>
测试类
@Test public void testFindById2() { SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); Map map = mapper.findById2(28); System.out.println(map); //{birthday=2019-04-09, address=芭蕉洞, sex=0, id=28, username=玉面狐狸} sqlSession.close(); }
1.3 resultMap
resultMap作用:当实体类的属性名和表的字段名不一致时,需要手动映射。
复制数据库中的user表为user_copy1,更改其中的username和sex字段,这时候这两个字段和实体类User中的属性名就不一致了。
更改后的表和类需要正常映射的话,需要手动配置resultMap
<resultMap id="findById3ResultMap" type="user">
<id column="id" property="id"/>
<result column="user_name" property="username"/>
<result column="gender" property="sex"/>
<!-- <result column="birthday" property="birthday"/>
<result column="address" property="address"/> -->
</resultMap>
<select id="findById3" parameterType="int" resultMap="findById3ResultMap" >
select * from user_copy1 where id = #{id}
</select>
- 字段相同的列可以省略不用手动配置
2. 动态SQL
2.1 if和where
需求分析:实际开发过程中,会用到组合条件查询,条件的个数是不固定的,这就要求sql语句中的条件根据传入的参数动态改变。为此,mybatis引入了where和if标签产生动态sql。
示例:
UserMapper.java文件中添加一个方法
//动态Sql public List<User> findListByUser(User user);
xml配置文件
<select id="findListByUser" parameterType="user" resultType="user"> select * from user <where> <if test="username!=null and username!=''"> username like '%${username}%' </if> <if test="sex!=null and sex!=''"> and sex = #{sex} </if> <if test="address!=null and address!=''"> and address=#{address} </if> </where> </select>
- if作用:判断
- where作用:取出第一个and或者or
测试
@Test public void testFindListByUser() { SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); // user.setUsername("张"); user.setSex("1"); user.setAddress("河南郑州"); List<User> list= mapper.findListByUser(user); System.out.println(list); sqlSession.close(); }
mybatis可以根据user中条件个数动态生成sql语句
比如上面代码中两个条件下,生成的sql语句为:
DEBUG [main] - ==> Preparing: select * from user WHERE sex = ? and address=?
2.2 foreach循环
需求分析:要查询用户id为1 16 22 的用户信息,相对应的sql语句为 SELECT * FROM
user
where id in (1, 16, 22)。查询的参数是数组,把这个数组放在包装类中传递。接口文件中添加
//动态sql,foreach循环 public List<User> findListByArray(QueryVo queryVo);
包装类中添加数组ids
package com.itzhouq.mybatis.pojo; public class QueryVo { private User user; int[] ids; public int[] getIds() { return ids; } public void setIds(int[] ids) { this.ids = ids; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } }
映射配置文件
<select id="findListByArray" parameterType="queryVo" resultType="user"> <!-- queryVo --> <!-- ids = [ 1, 16, 22] --> <!-- SELECT * FROM `user` where id in (1, 16, 22) --> <!-- select * from user where id in --> SELECT * FROM `user` where id in <foreach collection="ids" open="(" close=")" separator="," item="id"> #{id} </foreach> </select>
测试类
@Test public void testFindListByArray() { SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); QueryVo queryVo = new QueryVo(); int[] ids = {1, 16, 22}; queryVo.setIds(ids); List<User> list= mapper.findListByArray(queryVo); System.out.println(list); sqlSession.close(); }
生成的sql语句
DEBUG [main] - ==> Preparing: SELECT * FROM user where id in ( ? , ? , ? )
2.3 sql片段 sql+include
sql片段主要是用于xml文件中的代码复用
<select id="findListByUser" parameterType="user" resultType="user"> select * from user <include refid="findUserWhere"></include> </select> <sql id="findUserWhere"> <where> <if test="username!=null and username!=''"> username like '%${username}%' </if> <if test="sex!=null and sex!=''"> and sex = #{sex} </if> <if test="address!=null and address!=''"> and address=#{address} </if> </where> </sql>
2.4 set
需求分析:在更新数据库操作时,有可能只是更改某几个字段,传递的数据类型为user时,如果user的某些属性没有赋值,相当于把这些数据库中的这些字段设为null或者默认值,这样不符合我们的需求。这就需要判断user中的属性是否为空了。但是结合sql语句,修改操作时,每个字段后面又一个逗号。这里需要需要使用set去掉逗号,保证生成sql语句的正确性。
接口代码
//动态sql,set public void updateUser(User user);
映射文件中添加
<update id="updateUser" parameterType="user"> <!-- update user set username=#{username},sex=#{sex}, birthday=#{birthday},address=#{address} where id=#{id} --> update user <set> <if test="username != null and username != ''"> username=#{username}, </if> <if test="sex != null and sex != ''"> sex=#{sex}, </if> <if test="birthday != null"> birthday=#{birthday}, </if> <if test="address != null and address != ''"> address=#{address}, </if> </set> where id=#{id} </update>
测试类
@Test public void testUpdateUser() { SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setId(28); user.setUsername("铁扇公主"); mapper.updateUser(user); sqlSession.commit(); sqlSession.close(); }
生成的sql语句
DEBUG [main] - ==> Preparing: update user SET username=? where id=?
4. 关联查询
4.1 商品订单数据模型
4.2 一对一查询
- 案例:查询所有订单信息,关联查询下单用户信息
- 因为一个订单信息只会是一个人下的订单,所以从查询订单信息出发关联用户信息为一对一查询。如果从用户信息出发下的订单则会为一对多查询,因为一个用户可以下多个订单。
4.2.1 方法一:
使用resultType,定义订单信息pojo类,此pojo类中包括了订单信息和用户信息。
sql语句
select o.*,u.username,u.address,u.birthday,u.sex from orders o left join user u on o.user_id = u.id
新建pojo类,OrdersUser,这个类中应该包括sql查询出来的所有字段
public class OrdersUser { private int id; private int user_id; private String number; private Date createtime; private String note; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 public int getId() { return id; } public void setId(int id) { this.id = id;
- OrdersMapper.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"> <mapper namespace="com.itzhouq.mybatis.mapper.OrdersMapper"> <!-- statementId --> <select id="findOrdersUserList" resultType="ordersUser" > select o.*,u.username,u.address,u.birthday,u.sex from orders o left join user u on o.user_id = u.id </select> </mapper>
- 测试类OrdersMapperTest.java
package com.itzhouq.mybatis.test; import java.io.IOException; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Before; import org.junit.Test; import com.itzhouq.mybatis.mapper.OrdersMapper; import com.itzhouq.mybatis.mapper.UserMapper; import com.itzhouq.mybatis.pojo.OrdersUser; import com.itzhouq.mybatis.pojo.QueryVo; import com.itzhouq.mybatis.pojo.User; public class OrdersMapperTest { SqlSessionFactory sqlSessionFactory = null; @Before public void init() throws IOException { SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); sqlSessionFactory = builder.build(Resources.getResourceAsStream("sqlMapConfig.xml")); } @Test public void testFindOrdersUserList() { SqlSession sqlSession = sqlSessionFactory.openSession(); OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class); List<OrdersUser> ordersUserList = mapper.findOrdersUserList(); System.out.println(ordersUserList); sqlSession.close(); } }
查询结果与数据库中相同
- 定义专门的pojo类作为输出类型,其中定了sql查询结果集所有的字段,此方法较为简单。
4.2.2 方法2:
使用resultMap,定义resultMap用于映射一对一的查询结果。
select o.*,u.username,u.address,u.birthday,u.sex from orders o left join user u on o.user_id = u.id
定义pojo类:早Orders类中加入Users属性,user属性中用于存储关联查询的用户信息,因为订单关联查询用户是一对一的关系,所以这使用单个User对象存储关联查询的用户信息。
public class Orders { private int id; private int user_id; private String number; private Date createtime; private String note; private User user; public int getId() { return id; }
映射文件OrdersMapper.xml
<resultMap id="ordersResultMap" type="orders" > <id column="id" property="id"/> <result column="user_id" property="user_id"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/> <!-- 构建一对一级联关系 --> <association property="user" javaType="com.itzhouq.mybatis.pojo.User"> <id column="user_id" property="id"/> <result column="username" property="username"/> <result column="address" property="address"/> <result column="birthday" property="birthday"/> <result column="sex" property="sex"/> </association> </resultMap> <select id="findOrdersList" resultMap="ordersResultMap" > select o.*,u.username,u.address,u.birthday,u.sex from orders o left join user u on o.user_id = u.id </select>
这里resultMap指定ordersResultMap。
:表示进行关联查询的单条记录
property:表示关联查询到结果存储在com.itzhouq.mybatis.pojo.User中
javaType:表示关联查询的结果类型
:查询结果的user_id列对象关联对象的id属性,这里是表示user_id是关联对象的唯一标识。
- OrdersMapper接口
public interface OrdersMapper { public List<OrdersUser> findOrdersUserList(); public List<Orders> findOrdersList(); }
测试
@Test public void testFindOrdersList() { SqlSession sqlSession = sqlSessionFactory.openSession(); OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class); List<Orders> ordersList = mapper.findOrdersList(); System.out.println(ordersList); sqlSession.close(); }
总结:使用association完成关联查询,将关联查询信息映射到pojo对象中。
4.3 一对多查询
- 案例:查询所有用户信息及用户相关联的订单信息
- 用户信息和订单信息为一对多的关系
sql语句
定义pojo类:在User中添加
public class User implements Serializable { private int id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 private List<Orders> ordersList;
映射文件OrdersMapper.xml
<resultMap type="user" id="UserResultMap"> <id column="id" property="id"/> <result column="username" property="username"/> <result column="address" property="address"/> <result column="birthday" property="birthday"/> <result column="sex" property="sex"/> <!-- public List<User> findUserList(); --> <!-- collection:一对多关联,ofType集合中包含的属性 --> <collection property="ordersList" ofType="com.itzhouq.mybatis.pojo.Orders"> <id column="oid" property="id"/> <result column="id" property="user_id"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/> </collection> </resultMap> <select id="findUserList" resultMap="UserResultMap"> select u.*,o.id oid,o.number,o.createtime,o.note from user u inner join orders o on o.user_id = u.id </select>
collection部分定义了用户关联的订单信息。表示关联查询结果集 property="orders":关联查询的结果集存储在User对象的上哪个属性。 ofType="orders":指定关联查询的结果集中的对象类型即List中的对象类型。此处可以使用别名,也可以使用全限定名。 <id />及<result/>的意义同一对一查询。
接口
public interface OrdersMapper { public List<OrdersUser> findOrdersUserList(); public List<Orders> findOrdersList(); //查询User,User中关联多个订单 public List<User> findUserList(); }
测试
@Test public void testFindUserList() { SqlSession sqlSession = sqlSessionFactory.openSession(); OrdersMapper mapper = sqlSession.getMapper(OrdersMapper.class); List<User> userList = mapper.findUserList(); System.out.println(userList); sqlSession.close(); }
- 结果与数据库中查询的结果一致。