Mybatis （二）

根据用户名模糊查询（输入参数为包装类）

pojo类

public class QueryVo implements Serializable{
	private User user;
}

UserMapper 接口

public List<User> findUserByQueryVo(QueryVo vo);

UserMapper.xml

<select id="findUserByQueryVo" parameterType="QueryVo" resultType="com.itheima.mybatis.pojo.User">
		select * from user where username like '%${user.username}%'
</select>

测试类

public class MybatisMapperTest {
	String resource = "sqlMapConfig.xml";
	InputStream in = Resources.getResourceAsStream(resource);
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	SqlSession sqlSession = sqlSessionFactory.openSession();
	
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	
	QueryVo vo = new QueryVo();
	User user = new User();
	user.setUsername("tom");
	vo.setUser(user);
		
	List<User> us = userMapper.findUserByQueryVo(vo);
	for (User u : us) {
		System.out.println(u);
	}
}

查询数据条数

UserMapper接口

public Integer selectNum();

UserMapper.xml

<select id="selectNum" resultType="Integer">
		select count(1) from user
</select>

测试类

public class MybatisMapperTest {
	String resource = "sqlMapConfig.xml";
	InputStream in = Resources.getResourceAsStream(resource);
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	SqlSession sqlSession = sqlSessionFactory.openSession();
	
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	
	Integer selectNum = userMapper.selectNum();
	System.out.println(selectNum);
}

---

ResultMap

查询所有订单

当数据库的字段名和pojo类的属性名不一致时使用

数据库字段

Order 类

public class Orders  implements Serializable{
  
	private static final long serialVersionUID = 1L;
	
	private Integer id;
    private Integer userId;
    private String number;
    private Date createtime;
    private String note;
    
get\set\toString....

OrderMapper 接口

public List<Orders> selectAllOrders();

OrderMapper.xml

<mapper namespace="com.itheima.mybatis.mapper.OrderMapper">
	<resultMap type="Orders" id="aaa">
		<id column="id" property="id"/>
		<result column="user_id" property="userId" />
	</resultMap>
	
	<select id="selectAllOrders" resultMap="aaa">
		select * from orders
	</select>
</mapper>

测试类

public void testMapper() throws Exception {
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		OrderMapper orderMapper  = sqlSession.getMapper(OrderMapper.class);
		
		List<Orders> selectAllOrders = orderMapper.selectAllOrders();
		for (Orders orders : selectAllOrders) {
			System.out.println(orders);
		}
}

---

动态sql

public class QueryVo implements Serializable{
	private User user;
	private List<Integer> idsList;
	private Integer[] ids;

set\get....

根据性别和名字查询用户

UserMapper 接口

public List<User> findUserBySexAndName(User user);

UserMapper.xml

where 1=1 可以用 一对where标签 代替
where 标签可以去掉第一个前and

<select id="findUserBySexAndName" resultType="User" parameterType="User">
		select * from user 
		//where 1=1
		<where>
		 	<if test="sex != null and sex != ''">
		 		and sex = #{sex}
			 </if>
			 <if test="username !=null and username !=''">
		 		and username=#{username}
		 	</if>
		</where>
</select>

测试类

public void testMapper() throws Exception {
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		User user = new User();
		user.setSex("1");
		user.setUsername("陈小明");
		
		List<User> findUserBySexAndName = userMapper.findUserBySexAndName(user);
		for (User user2 : findUserBySexAndName) {
			System.out.println(user2);
		}
}

根据多个id查询用户信息

• 输入参数为包装类

UserMapper 接口

public List<User> findUserByIds(QueryVo vo);

UserMapper.xml

foreach标签，进行遍历 
collection：遍历的集合，这里是QueryVo的ids属性
item：遍历的项目，可以随便写，，但是和后面的#{}里面要一致
open：在前面添加的sql片段
close：在结尾处添加的sql片段 
separator：指定遍历的元素之间使用的分隔符 

<select id="findUserByIds" resultType="User" parameterType="QueryVo">
		select * from user 
		where id in 
		<foreach collection="idsList" item="id" separator="," open="(" close=")">
			#{id}
		</foreach>
</select>

测试类

public void testMapper() throws Exception {
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		QueryVo vo = new QueryVo();
		
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(16);
		ids.add(22);
		ids.add(24);
		
		vo.setIdsList(ids);
		userMapper.findUserByIds(vo);
}

• 输入参数为Integer[] 类

UserMapper 接口

public List<User> findUserByIds(Integer[] ids);

UserMapper.xml

<select id="findUserByIds" parameterType="Integer" resultType="User">
		select * from user
		where 
		<foreach collection="array" item="id" separator="," open="id in(" close=")">
			#{id}
		</foreach>
</select>

测试类

public void testMapper() throws Exception {
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		Integer[] ids = new Integer[3];
		ids[0] = 16;
		ids[1] = 22
		ids[2] = 24;
		
		List<User> findUserByIds = userMapper.findUserByIds(ids);
		for (User user : findUserByIds) {
			System.out.println(user);
		}
}

• 输入参数为List类型

UserMapper 接口

public List<User> findUserByIds(List<Integer> ids);

UserMapper.xml

<select id="findUserByIds" parameterType="List" resultType="User">
		select * from user 
		<where>
			<foreach collection="list" item="id" separator="," open="(" close=")">
				#{id}
			</foreach>
		</where> 
</select>

测试类

public void testMapper() throws Exception {
		String resource = "sqlMapConfig.xml";
		InputStream in = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
		SqlSession sqlSession = sqlSessionFactory.openSession();
		
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		
		List<Integer> list = new ArrayList<>();
		list.add(16);
		list.add(22);
		
		List<User> findUserByIds = userMapper.findUserByIds(list);
		for (User user : findUserByIds) {
			System.out.println(user);
		}
}

---

一对一映射

以订单为中心，订单和用户之间的关系是一对一，一个订单对应一个用户

订单表：

Oders类属性值：

private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;

用户表：

User类属性值：

private Integer id;
private String username;
private String sex;
private Date birthday;
private String address;

步骤：

1.在订单类中添加用户对象，并重写set\get方法

private User user;
 
public User getUser() {
	return user;
}

public void setUser(User user) {
	this.user = user;
}

2.在OrderMapper 接口中定义一个方法

	public List<Orders> selectOrders();

3.在OrderMapper.xml中添加映射并编写sql语句

column：数据库表字段名
property:pojo类属性

<mapper namespace="com.itheima.mybatis.mapper.OrderMapper">
	<resultMap type="Orders" id="order">
		<id column="id" property="id"/>
		<result column="user_id" property="userId"/>
		<result column="number" property="number"/>
		
		<association property="user" javaType="User">
			<id column="user_id" property="id"/>
			<result column="username" property="username"/>
		</association>
	</resultMap>
	
	<select id="selectOrders" resultMap="order">
		select o.id,o.user_id,o.number,o.createtime,u.username
		from orders o
		left join user u
		on o.user_id = u.id
	</select>
</mapper>

4.测试

public void testOderList() throws Exception {
	String resource = "sqlMapConfig.xml";
	InputStream in = Resources.getResourceAsStream(resource);
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	SqlSession sqlSession = sqlSessionFactory.openSession();
		
	OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
	
	List<Orders> selectOrders = orderMapper.selectOrders();
	for (Orders orders : selectOrders) {
		System.out.println(orders);
	}
}

---

一对多映射

步骤

1.在User类中添加Orders属性，并设置set\get方法

private List<Orders> orders;

public List<Orders> getOrders() {
		return orders;
}
public void setOrders(List<Orders> orders) {
		this.orders = orders;
}

2.在UserMapper 接口中定义一个方法

public List<User> selectUser();

3.在UserMapper.xml 文件中添加映射并编写sql语句

<resultMap type="User" id="user">
	<id column="uid" property="id"/>
	<result column="username" property="username"/>
	<collection property="orders" ofType="Orders">
		<id column="id" property="id"/>
		<result column="number" property="number"/>
	</collection>
</resultMap> 
	
<select id="selectUser" resultMap="user">
	select u.id as uid,u.username,o.id,o.number,o.createtime
	from user u
	left join orders o
	on u.id=o.user_id
</select>

4.测试

public void testOderList() throws Exception {
	String resource = "sqlMapConfig.xml";
	InputStream in = Resources.getResourceAsStream(resource);
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
	SqlSession sqlSession = sqlSessionFactory.openSession();
	
	UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
	
	List<User> selectUser = userMapper.selectUser();
		
	for (User user : selectUser) {
		System.out.println(user);
	}
}