mybatis入门三_mybatis 直接查询vo-优快云博客

本文链接：https://blog.youkuaiyun.com/java_12_liuxinfei/article/details/79713656

上一篇学习了mybatis使用ognl表达式解析对象字段值，#{}、${}括号中为pojo属性值；其实在开发中都是通过传入pojo对象去操作更多的业务的，今天使用pojo包装对象。

先有一个包装类QueryVo，包装类有User对象

输入映射和输出映射

parameterType输入类型

package cn.itcast.mybatis.pojo;
//包装对象
public class QueryVo {	
	private User user;
	public QueryVo() {
		super();
	}
	public QueryVo(User user) {
		super();
		this.user = user;
	}
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
}

定义UserMapper接口

package cn.itcast.mybatis.mapper;

import java.util.List;

import cn.itcast.mybatis.pojo.QueryVo;
import cn.itcast.mybatis.pojo.User;

public interface UserMapper {
	
	/**
	 * 根据id查询用户
	 * 
	 * @param id
	 * @return
	 */
	public User queryUserById(int id);

	/**
	 * 根据用户名模糊查询数据
	 * 
	 * @param username
	 * @return
	 */
	public List<User> queryUserByUsername(String username);

	/**
	 * 新增用户
	 * 
	 * @param user
	 */
	public void saveUser(User user);

	/**
	 * 根据包装对象查询用户数据
	 * 
	 * @param queryVo
	 * @return
	 */
	public List<User> queryUserByQueryVo(QueryVo queryVo);

	/**
	 * 查询用户数据总记录数
	 * 
	 * @return
	 */
	public int queryUserCount();

	/**
	 * 根据性别和用户名查询用户
	 * 
	 * @param user
	 * @return
	 */
	public List<User> queryUserBySexAndUsername(User user);

	/**
	 * 根据多个id查询用户
	 * 
	 * @param queryVo
	 * @return
	 */
	public List<User> queryUserByIds(QueryVo queryVo);

	/**
	 * 查询用户数据，包含订单数据
	 * 
	 * @return
	 */
	public List<User> queryUserOrder();
}

同包名下的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">
<!-- 
	namespace：mapper.xml和接口命名一样
 -->
<mapper namespace="cn.itcast.mybatis.mapper.UserMapper">
	<!-- 
		parameterType：参数类型为QueryVo  接口所传的参数 
		resultType：返回类型
	 -->
	<select id="queryUserByQueryVo" 
		parameterType="QueryVo" resultType="user" >
			SELECT * FROM USER WHERE username LIKE '%${user.username}%'
	</select>
</mapper>

SqlMapConfig.xml配置

事物、数据库、mapper扫描配置

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<!-- 设置别名 -->
	<typeAliases>
		<package name="cn.itcast.mybatis.pojo"/>
	</typeAliases>
	<environments default="development">
		<environment id="development">
			<!-- 使用JDBC事物管理 -->
			<transactionManager type="JDBC"></transactionManager>
			<!-- 数据连接池 -->
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
				<property name="username" value="root"/>
				<property name="password" value="root"/>
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<!-- 使用包扫描方式 -->
		<package name="cn.itcast.mybatis.mapper"/>
	</mappers>
</configuration>

测试Test

public class UserMapperTest {
	
	private SqlSessionFactory sqlSessionFactory;
	
	@Before
	public void init() throws Exception{
		SqlSessionFactoryBuilder sessionFactoryBuilder = new SqlSessionFactoryBuilder();
		InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
		this.sqlSessionFactory = sessionFactoryBuilder.build(inputStream);
	}
	
	@Test
	public void queryUserByQueryVoTest(){
		SqlSession openSession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = openSession.getMapper(UserMapper.class);
		QueryVo queryVo = new QueryVo();
		User user = new User();
		user.setUsername("测");
		queryVo.setUser(user);
		List<User> list = userMapper.queryUserByQueryVo(queryVo);
		for (User user2 : list) {
			System.out.println(user2);
		}
	}
}

测试结果：

User [id=28, username=测试用户01, sex=男, birthday=null, address=bj]
User [id=29, username=测试02, sex=女, birthday=null, address=bj]
User [id=30, username=测试02, sex=女, birthday=null, address=bj]

resultType输出类型

输出简单类型，查询用户条数

UserMapper.xml

<!-- 查询用户数据总记录数
		  输出类型为int
	 -->
	<select id="queryUserCount" parameterType="User" resultType="int">
		SELECT COUNT(*) FROM USER
	</select>

测试：

@Test
	public void queryUserCountTest(){
		SqlSession openSession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = openSession.getMapper(UserMapper.class);
		int count = userMapper.queryUserCount();
		System.out.println(count);  openSession.colse();//关闭链接,跟spring整合后可以交给spring
	}

结果：

注意：输出简单类型必须查询出来的结果集有一条记录，最终将第一个字段的值转换为输出类型

resultMap

resultType可以指定将查询结果映射为pojo，但需要pojo的属性名和sql查询的列名一致方可映射成功(可以参考第一天)

如果sql查询字段名和pojo的属性名不一致，可以通过resultMap将字段名和属性名作一个对应关系，resultMap实质上还需要将查询结果映射到pojo对象中。

resultMap可以实现将查询结果映射为复杂类型的pojo，比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询

使用resultMap

创建OrderMapper接口与OrderMapper配置wen

package cn.itcast.mybatis.pojo;

import java.util.Date;

public class Order {
	// 订单id
	private int id;
	// 用户id
	private Integer userId;
	// 订单号
	private String number;
	// 订单创建时间
	private Date createtime;
	// 备注
	private String note;

	//get/set省略
}

package cn.itcast.mybatis.mapper;
import java.util.List;
import cn.itcast.mybatis.pojo.Order;
public interface OrderMapper {	
	/**
	 * 查询所有订单数据
	 * 
	 * @return
	 */
	public List<Order> queryOrderAll();


	/**
	 * 查询订单数据，包含用户信息
	 * 
	 * @return
	 */
	public List<Order> queryOrderUserResultMap();
}

OrderMapper.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="cn.itcast.mybatis.mapper.OrderMapper">
	<!-- resultMap最终要映射到pojo上，type是指定到那个pojo上的 -->
	<!-- 因为sqlMapConfig配置mapper扫描pojo  配置typeAliases别名 不区分大小写 -->
	<resultMap type="order" id="orderResultMap">
		<!-- column：主键在数据库列名 property为pojo属性名 -->
		<id column="id" property="id"/>
		<result column="user_id" property="userId"/>
		<result column="number" property="number"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
	</resultMap>
	
	<select id="queryOrderAll" resultMap="orderResultMap">
		SELECT id, user_id,
		number,
		createtime, note FROM `order`
	</select>
</mapper>

测试

	@Test
	public void queryOrderAllTest(){
		SqlSession openSession = this.sqlSessionFactory.openSession();
		OrderMapper orderMapper = openSession.getMapper(OrderMapper.class);
		List<Order> list = orderMapper.queryOrderAll();
		for (Order order : list) {
			System.out.println(order);
		}
	}

log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Order [id=1, userId=28, number=10000001, createtime=Tue Mar 27 00:00:00 CST 2018, note=null]
Order [id=2, userId=28, number=10000002, createtime=Tue Mar 27 00:00:00 CST 2018, note=null]
Order [id=3, userId=29, number=10000003, createtime=Tue Mar 27 00:00:00 CST 2018, note=null]

动态SQL

通过mybatis提供的各种标签方法实现动态拼接sql

需求：根据性别和名字查询用户

        /**
	 * 根据条件查找用户信息
	 * @return
	 */
	public List<User> queryUserByWhere(User user);

<select id="queryUserByWhere" parameterType="user" resultType="user">
			SELECT id, username, birthday, sex, address FROM `user`
				WHERE sex = #{sex} AND username LIKE '%${username}%'
	</select>

测试

@Test
	public void queryUserByWhereTest(){
		SqlSession openSession = this.sqlSessionFactory.openSession();
		OrderMapper orderMapper = openSession.getMapper(OrderMapper.class);
		User user = new User();
		user.setUsername("01");
		user.setSex("男");		
		List<User> userByWhere = orderMapper.queryUserByWhere(user);
		for (User orderUser : userByWhere) {
			System.out.println(orderUser);
		}
	}

log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
User [id=28, username=测试用户01, sex=男, birthday=null, address=bj]

如果注释掉 user.setSex("男")，是肯定查不出来的，如果按之前学的，需要编写多个sql，显然不靠谱了

解决方案，使用动态sql的if标签

	<select id="queryUserByWhere" parameterType="user" resultType="user">
			SELECT id, username, birthday, sex, address FROM `user`
			WHERE 1=1
			<if test="sex != null and sex != ''">
				AND sex = #{sex}
			</if>
			<if test="username != null and username != ''">
				AND username like '%${username}%'
			</if>
	</select>

结果

log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
User [id=28, username=测试用户01, sex=男, birthday=null, address=bj]

注意字符串类型的数据需要要做不等于空字符串校验

还可以改造where

<select id="queryUserByWhere" parameterType="user" resultType="user">
			    SELECT id, username, birthday, sex, address FROM `user`			
			<where>
				<if test="sex != null and sex != ''">
					AND sex = #{sex}
				</if>
				<if test="username != null and username != ''">
					AND username like '%${username}%'
				</if>
			</where>
</select>

SQL片段

sql中重复的sql提取出来，使用include引用

<select id="queryUserByWhere" parameterType="user" resultType="user">
			<!-- 使用include加载sql片段  -->
			SELECT <include refid="userFields"></include> FROM `user`
			<where>
				<if test="sex != null and sex != ''">
					AND sex = #{sex}
				</if>
				<if test="username != null and username != ''">
					AND username like '%${username}%'
				</if>
			</where>
	</select>
	
	<sql id="userFields">
		id, username, birthday, sex, address
	</sql>

forEach标签

根据多个id查询用户信息

改造QueryVo包装类

package cn.itcast.mybatis.pojo;

import java.util.List;

//包装对象
public class QueryVo {
	private User user;
	
	//根据多个id查询
	private List<Integer> ids;

	//get/set方式省略
}

UserMapper.xml

<select id="queryUserByIds" parameterType="queryVo" resultType="user">
		<!-- 
			forech标签，进行遍历
			collection：是遍历集合，这里是queryVo里面的ids属性
			item：遍历变量，可以随便写
			open：在前面添加的sql片段
			close：在结尾处添加的sql片段
			separator：指定遍历的元素之间使用的分隔符
		 -->
		SELECT
		<include refid="userFields" />
		FROM user
		<where>
			<!-- collection：拼接sql所遍历的集合 -->
			<!-- item：遍历集合的遍历，可以写任意的名字 -->
			<!-- open：拼接的sql是什么开头 -->
			<!-- close：拼接的sql是什么结尾 -->
			<!-- separator：sql语句中条件的分隔符 -->
			<foreach collection="ids" item="item" open="id IN (" close=")"
				separator=",">
				#{item}
			</foreach>
		</where>
	</select>
	
	<!-- 声明sql片段 -->
	<!-- id:就是sql片段的唯一标识符 -->
	<sql id="userFields">
		id, username, birthday, sex, address
	</sql>

测试

@Test
	public void queryUserByIdsTest(){
		SqlSession openSession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = openSession.getMapper(UserMapper.class);		
		QueryVo queryVo = new QueryVo();
		ArrayList<Integer> arrayList = new ArrayList<>();
		arrayList.add(28);
		arrayList.add(30);
		queryVo.setIds(arrayList);
		List<User> list = userMapper.queryUserByIds(queryVo);
		for (User user : list) {
			System.out.println(user);
		}
	}

结果：

log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
User [id=28, username=测试用户01, sex=男, birthday=null, address=bj]
User [id=30, username=测试02, sex=女, birthday=null, address=bj]

关联查询

商品订单数据模型

一对一查询

查询所有订单信息，关联查询下单用户信息

注意：因为一个订单信息只会是一个人下的订单，所以从查询订单信息出发关联查询用户信息为一对一查询。如果从用户信息出发查询用户下的订单信息则为一对多查询，因为一个用户可以下多个订单。

这里有两种方式，一种是resultType，一种是resultMap

就使用第二种吧resultMap，返回pojo类，那就需要改造一下Order类，添加User对象

package cn.itcast.mybatis.pojo;

import java.util.Date;

public class Order {
	// 订单id
	private int id;
	// 用户id
	private Integer userId;
	// 订单号
	private String number;
	// 订单创建时间
	private Date createtime;
	// 备注
	private String note;

	private User user;

	//get/set方法和toString方式省略

}

UserMapper.xml

<!-- resultMap最终要映射到pojo上，type是指定到那个pojo上的 -->
	<!-- 因为sqlMapConfig配置mapper扫描pojo  配置typeAliases别名 不区分大小写 -->
	<resultMap type="order" id="orderResultMap">
		<!-- column：主键在数据库列名 property为pojo属性名 -->
		<id column="id" property="id"/>
		<result column="user_id" property="userId"/>
		<result column="number" property="number"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
		<!-- 配置一对一属性
			property：是order里user属性
			javaType：属性类型
		 -->
		<association property="user" javaType="user">
			<!-- id：声明主键    user_id:表示关联查询对象的唯一标识	 -->
			<id column="user_id" property="id"/>
			<result property="username" column="username"/>
			<result property="address" column="address"/>
		</association>
	</resultMap>	
	
	<select id="queryOrderUserResultMap" resultMap="orderResultMap">
		SELECT
		o.id,
		o.user_id,
		o.number,
		o.createtime,
		o.note,
		u.username,
		u.address
		FROM
		`order` o
		LEFT JOIN `user` u ON o.user_id = u.id
	</select>

测试

@Test
	public void queryOrderUserResultMapTest(){
		SqlSession openSession = this.sqlSessionFactory.openSession();
		OrderMapper orderMapper = openSession.getMapper(OrderMapper.class);	
		List<Order> list = orderMapper.queryOrderUserResultMap();
		for (Order order : list) {
			System.out.println(order);
		}
	}

结果：

log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Order [id=1, userId=28, number=10000001, createtime=Tue Mar 27 00:00:00 CST 2018, note=null]
Order [id=2, userId=28, number=10000002, createtime=Tue Mar 27 00:00:00 CST 2018, note=null]
Order [id=3, userId=29, number=10000003, createtime=Tue Mar 27 00:00:00 CST 2018, note=null]

一对多查询

查询所有用户信息及用户关联的订单信息，用户信息和订单信息为一对多关系。

改造User类

package cn.itcast.mybatis.pojo;

import java.util.Date;
import java.util.List;

public class User {
	private int id;
	private String username;
	private String sex;
	private Date birthday;
	private String address;
	//一个用户可以有多个订单
	private List<Order> orders;
}

<!-- 配置用户订单的ResultMap -->
	<resultMap type="user" id="userOrderResultMap">
		<!-- 配置id主键 -->
		<id column="id" property="id" />

		<!-- 配置result -->
		<result column="username" property="username" />
		<result column="birthday" property="birthday" />
		<result column="sex" property="sex" />
		<result column="address" property="address" />

		<!-- collection:配置一对多的映射关系 -->
		<!-- property:映射的属性名 -->
		<!-- javaType:属性类型 -->
		<!-- ofType：集合里面元素的类型 -->
		<collection property="orders" javaType="List" ofType="Order">
			<!-- 配置id主键 -->
			<id column="oid" property="id" />

			<!-- 配置其他属性 -->
			<result column="number" property="number" />
			<result column="createtime" property="createtime" />
			<result column="note" property="note" />
		</collection>
	</resultMap>

	<!-- 查询用户数据，包含订单数据 -->
	<select id="queryUserOrder" resultMap="userOrderResultMap">
		SELECT
		u.id,
		u.username,
		u.birthday,
		u.sex,
		u.address,
		o.id oid,
		o.number,
		o.createtime,
		o.note
		FROM
		`user` u
		LEFT JOIN `order` o ON u.id = o.user_id
	</select>

测试：

@Test
	public void queryUserOrderTest(){
		SqlSession openSession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = openSession.getMapper(UserMapper.class);	
		List<User> userOrder = userMapper.queryUserOrder();
		for (User user : userOrder) {
			System.out.println(user);
		}
	}

测试结果：