Mybatis学习记录(3)
1.输出映射和输入映射
Mapper.xml映射文件定义了操作数据库的sql,每个sql就是一个statement,映射文件是mybatis的核心。
(1)parameterType(输入类型)
1.传递简单类型
使用占位符#{},或者${}进行sql拼接。
2.传递pojo对象
使用ognl表达式解析对象字段的值,#{}或者${}括号中的值 为pojo属性名称。
3.传递pojo包装对象
开发中可以通过使用pojo传递查询条件,查询的条件可能是综合的查询条件,这时可以使用包装对象传递输入参数。
包装对象：Pojo类中的一个属性是另一个pojo
即新建一个queryInfo类将所需要的查询条件全部声明到此类中,并生成get,set方法。
编写QueryInfo类 UserQueryInfo.java
package com.javaweb.mybatis.model.queryInfo; import java.io.Serializable; import com.javaweb.mybatis.model.User; public class UserQueryInfo implements Serializable{ /** * */ private static final long serialVersionUID = 1L; private User user; public User getUser() { return user; } public void setUser(User user) { this.user = user; } }
Mapper.xml配置文件
<!-- 根据用户名模糊查询 --> <select id="findUserByQueryInfo" parameterType="com.javaweb.mybatis.model.queryInfo.UserQueryInfo" resultType="com.javaweb.mybatis.model.User"> select * from mybatis_user where username like "%${user.userName}%" </select>
Mapper接口
public interface UserMapper { /** * 遵循四个原则 * 1.接口方法名 ==User.xml中的id名 * 2.返回值类型与Mapper.xml的返回值类型一致 * 3.方法的入参类型与Mapper.xml中的入参类型一致 * 4.命名空间绑定此接口,即Mapper.xml的namespace是此接口的路径 */ public List<User> findUserByQueryInfo(UserQueryInfo queryInfo);
Jubit单元测试类:
@Test public void testMapperQueryInfo() throws Exception{ //加载核心配置文件 String resource="sqlMapConfig.xml"; InputStream in=Resources.getResourceAsStream(resource); //创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); //创建SqlSession SqlSession sqlSession=sqlSessionFactory.openSession(); //SqlSession帮我生成一个实现类 UserMapper userMapper=sqlSession.getMapper(UserMapper.class); UserQueryInfo queryInfo =new UserQueryInfo(); User user=new User(); user.setUserName("王"); queryInfo.setUser(user); List<User> userList=userMapper.findUserByQueryInfo(queryInfo); for(User u:userList){ System.out.println(u); } }
2.resultType(输出类型)
(1)输出简单类型
需求查询用户表的数据总条数
Mapper接口:
public interface UserMapper { /** * 遵循四个原则 * 1.接口方法名 ==User.xml中的id名 * 2.返回值类型与Mapper.xml的返回值类型一致 * 3.方法的入参类型与Mapper.xml中的入参类型一致 * 4.命名空间绑定此接口,即Mapper.xml的namespace是此接口的路径 */ //查询数据条数 public Integer countUser();
Mapper.xml配置文件
<!-- 查询数据条数 --> <select id="countUser" resultType="Integer"> select count(1) from mybatis_user where 1=1 and id=2 </select>
Junit单元测试类:
@Test public void testMapperCountUserQueryInfo() throws Exception{ //加载核心配置文件 String resource="sqlMapConfig.xml"; InputStream in=Resources.getResourceAsStream(resource); //创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); //创建SqlSession SqlSession sqlSession=sqlSessionFactory.openSession(); //SqlSession帮我生成一个实现类 UserMapper userMapper=sqlSession.getMapper(UserMapper.class); Integer countUser = userMapper.countUser(); System.out.println(countUser); }
(2)输出pojo对象
见上一章
(3)输出pojo列表
见上一章
3.resultMap(输出类型)
resultType可以指定将查询结果映射为pojo,但需要pojo的属性名和sql查询的列名一致才能映射成功。
但是如果是sql查询字段名和pojo的属性名不一致,就可以通过resultMap将字段名和属性名手动做一个对应关系,resultMap实质上还需要将查询结果映射到pojo对象中。
resultMap可以实现将映射为复杂类型的pojo,比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询。
示例:查询订单表order中的所有数据
数据库表构造如下:
Order对象order.java
public class Order implements Serializable{ /** * */ private static final long serialVersionUID = 1L; private String id; private String userId; private Integer number; private String note; private Date createTime; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getUserId() { return userId; } public void setUserId(String userId) { this.userId = userId; } public Integer getNumber() { return number; } public void setNumber(Integer number) { this.number = number; } public String getNote() { return note; } public void setNote(String note) { this.note = note; } public Date getCreateTime() { return createTime; } public void setCreateTime(Date createTime) { this.createTime = createTime; } @Override public String toString() { return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", note=" + note + ", createTime=" + createTime + "]"; }
OrderMapper.java接口
/** * 订单接口 * @author fanyukai * */ public interface OrderMapper { public List<Order> orderList(); }
Mapper.xml配置文件
<mapper namespace="com.javaweb.mybatis.mapper.OrderMapper"> <!-- id：设置resultMap的id --> <resultMap type="com.javaweb.mybatis.model.Order" id="orders"> <!-- 定义主键 --> <!-- property:主键在pojo的属性名 --> <!-- colunm:主键在数据库中的列名 --> <id property="id" column="id" /> <!-- 定义普通属性 --> <result column="user_id" property="userId"/> </resultMap> <!-- 查询所有订单 --> <select id="orderList" resultMap="orders"> select id,user_id,number,note,createtime from mybatis_orders </select> </mapper>
Junit单元测试类：
@Test public void testMapperOrderList() throws Exception{ //加载核心配置文件 String resource="sqlMapConfig.xml"; InputStream in=Resources.getResourceAsStream(resource); //创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); //创建SqlSession SqlSession sqlSession=sqlSessionFactory.openSession(); //SqlSession帮我生成一个实现类 OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); List<Order> orderList=orderMapper.orderList(); for(Order orders :orderList){ System.out.println(orders); } }
3.动态sql
(1)通过mybatis提供的各种标签方法实现动态拼接sql
<sql>和<include>标签
(2)where,if 标签
4.关联查询
一对一关联:一份订单对应一个用户
使用resultMap,定义专门的resultMap用于映射一对一查询结果
sql语句:
select o.id, o.user_id, o.number, o.note, o.createtime, u.id, u.username FROM mybatis_orders o LEFT JOIN mybatis_user u ON o.user_id=u.id
改造pojo类
public class Order implements Serializable{ /** * */ private static final long serialVersionUID = 1L; private String id; private String userId; private Integer number; private String note; private Date createTime; //附加对象 用户对象 private User user; public User getUser() { return user; } public void setUser(User user) { this.user = user; /* 后面get,set方法省略*/
Mapper.xml配置文件
<resultMap type="com.javaweb.mybatis.model.Order" id="order"> <!-- 定义普通属性 --> <id column="id" property="id"/> <result column="user_id" property="userId"/> <result column="number" property="number"/> <result column="createtime" property="createTime"/> <!-- 一对一 --> <association property="user" javaType="com.javaweb.mybatis.model.User" > <id column="id" property="user_id"/> <result column="userName" property="userName"/> </association> </resultMap> <!-- 一对一关联查询 以订单为中心关联用户 --> <select id="selectOrder" resultMap="order"> select o.id, o.user_id, o.number, o.note, o.createtime, u.id, u.username FROM mybatis_orders o LEFT JOIN mybatis_user u ON o.user_id=u.id </select>
Mapper接口:
public interface OrderMapper { //一对一关联查询 以订单为中心关联用户 public List<Order> selectOrder();
Junit单元测试类:
@Test public void testMapperOrderOneToOne() throws Exception{ //加载核心配置文件 String resource="sqlMapConfig.xml"; InputStream in=Resources.getResourceAsStream(resource); //创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); //创建SqlSession SqlSession sqlSession=sqlSessionFactory.openSession(); //SqlSession帮我生成一个实现类 OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); List<Order> orderList=orderMapper.selectOrder(); for(Order orders :orderList){ System.out.println(orders); } }
一对多关联:一个用户对应多个订单
sql语句
select o.id, o.user_id, o.number, o.note, o.createtime, u.username FROM mybatis_user u LEFT JOIN mybatis_orders o ON o.user_id=u.id
改造pojo类 User.java:
public class User implements Serializable{ /** * user表的pojo对象 */ private static final long serialVersionUID = 1L; private String id; private String userName; private String userAge; private String userSex; private String userAddress; //附加对象list 一对多 private List<Order> orderList; public List<Order> getOrderList() { return orderList; } public void setOrderList(List<Order> orderList) { this.orderList = orderList; }
/*后面get,set方法省略*/
Mapper.xml配置文件:
<!-- 一对多 以用户为中心--> <resultMap type="com.javaweb.mybatis.model.User" id="user"> <id column="id" property="id"/> <result column="username" property="userName"/> <!-- 一对多 --> <collection property="orderList" ofType="com.javaweb.mybatis.model.Order">
<!-- 配置主键,是关联Order的唯一标识 -->
<id column="id" property="id"/> <result column="number" property="userName"/> <result column="user_id" property="userId"/> <result column="note" property="note"/> <result column="createtime" property="createTime"/> </collection> </resultMap> <select id="selectUserList" resultMap="order"> select o.id, o.user_id, o.number, o.note, o.createtime, u.username FROM mybatis_user u LEFT JOIN mybatis_orders o ON o.user_id=u.id </select>
Mapper接口:
public interface OrderMapper { //一对多关联 public List<User> selectUserList(); }
Junit单元测试类:
@Test public void testMapperOrderOneToMany() throws Exception{ //加载核心配置文件 String resource="sqlMapConfig.xml"; InputStream in=Resources.getResourceAsStream(resource); //创建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); //创建SqlSession SqlSession sqlSession=sqlSessionFactory.openSession(); //SqlSession帮我生成一个实现类 OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class); List<User> selectUserList = orderMapper.selectUserList(); for(User user :selectUserList){ System.out.println(user); } }
