Mybatis---输入输出映射和动态sql

前期准备

传递包装对象

采用包装了可以对被包装的内所有属性进行传递

resultMap

resultType可以把查询结果映射为类的集合（同时以内中的多个属性为查询条件），但要注意：类的属性名要和sql查询的列名一致才能查询成功。如果查询字段不一致，就要通过resultMap将字段名和属性名做一个对应关系。resultMap可以实现复杂类型的映射查询，在查询结果中包括类和list实现一对一和一对多查询。

动态sql

If标签，对sql条件进行过滤，特别是空值和空字符串

Where标签，where标签自动添加where语句，同时处理sql语句中第一个前and关键字

Sql片段， Sql中可将重复的sql提取出来，使用时用include引用即可，最终达到sql重用的目的

Foreach标签，根据多个数据查询（如 in（1，2，3）），向sql传递array或list

注意： 传递数组时collection的值一定要是array，传递集合时collection的值一定要是list，这是由底层代码决定的，在底层代码编写中接收的array、list、属性值都会转变为Map，然后再以array、list、属性名传递给sql

程序思路

1. 完成对动态sql语句的简单查询（if标签、where标签、resultMap标签、foreach标签、sql片段）
2. 在SqlMapConfig.xml文件中写好别名
3. 在Mapper.xml写好相应标签的查询语句
4. 创建好实体类、包装类、动态代理接口和方法
5. 书写测试类进行测试

代码实现

SqlMapConfig.xml中的别名设置，采用的是接口路径方法，要求Mapper.xml要和Mapper.java名称一致，并放在接口路径的包下

	<mappers>
		<mapper class="www.tl.dao.UserMapper"/>
		<mapper class="www.tl.dao.OrderMapper"/>
	</mappers>

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：命名空间，用于隔离,使用动态代理开发,与UserMapper接口的类路径一致 -->
<mapper namespace="www.tl.dao.UserMapper">
	<!-- sql片段 -->
	<sql id="userFiles">
		id,username,sex
	</sql>

	<!-- if标签 -->
	<!-- 字符串拼接，根据条件查询用户，对查询条件进行空值和空字符串判断 -->
	<!-- #{}:占位符  == ?  '五'
		 ${}:字符串拼接 == 五 -->
	<select id="queryUserByIf" resultType="User" parameterType="User">
		select * 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>

	<!-- where标签自动添加where语句，同时处理sql语句中第一个前and关键字 -->
	<select id="queryUserByWhere" resultType="User" parameterType="User">
		select * 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片段 -->
	<select id="queryUserBySql" resultType="User">
		select <include refid="userFiles"/>from user
	</select>
	
	<!-- foreach标签, 根据多个id查询数据 -->
	<select id="queryUserByids" resultType="user" parameterType="QueryVo">
		<!-- foreach标签：
			collection,遍历的集合
			item,遍历的项目
			open,在前面的sql片段
			close,在结尾处的sql片段
			separator,分隔符 -->
		select * from user
		<where>
			<foreach collection="ids" item="id" open="id in (" close=")" separator=",">
				#{id}
			</foreach>
		</where>
	</select>
</mapper>

UserMapper.java

package www.tl.dao;
import java.util.List;
import www.tl.entity.QueryVo;
import www.tl.entity.User;

public interface UserMapper {

	public List<User> queryUserByIf(User user);
	public List<User> queryUserByWhere(User user);
	public List<User> queryUserBySql();
	public List<User> queryUserByids(QueryVo queryVo);
}

包装类

package www.tl.entity;
import java.util.List;
public class QueryVo {

	private List<Integer> ids;
	public List<Integer> getIds() {
		return ids;
	}

	public void setIds(List<Integer> ids) {
		this.ids = ids;
	}
	
}

User.java

package www.tl.entity;

import java.io.Serializable;
import java.util.Date;

public class User implements Serializable {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private Integer id;
	private String username;
	private String sex;
	private Date birthday;
	private String address;


	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public Date getBirthday() {
		return birthday;
	}
	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", sex=" + sex
				+ ", birthday=" + birthday + ", address=" + address + "]";
	}	
}

测试类和测试方法

package www.tl.daotest;

import java.io.InputStream;
import java.util.ArrayList;
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 www.tl.dao.UserMapper;
import www.tl.entity.QueryVo;
import www.tl.entity.User;

public class MapperTest {

	private SqlSessionFactory sqlSessionFactory;
	@Before
	public void init()throws Exception {
		/*加载资源*/
		String resource = "SqlMapConfig.xml";
		InputStream ins = Resources.getResourceAsStream(resource);
		/*创建SqlSessionFactoryBulider*/
		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
		/*创建SqlSessionFactory*/
		this.sqlSessionFactory = sqlSessionFactoryBuilder.build(ins);
	}
@Test
	public void queryUserByIf(){
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = new User();
		user.setSex("1");
		user.setUsername("小");
		List<User> list = userMapper.queryUserByIf(user);
		for (User user2 : list) {
			System.out.println(user2);
		}
	}
	@Test
	public void queryUserByWhere(){
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = new User();
		user.setSex("1");
		user.setUsername("小");
		List<User> list = userMapper.queryUserByIf(user);
		for (User user2 : list) {
			System.out.println(user2);
		}
	}
	@Test
	public void queryUserBySql(){
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		List<User> list = userMapper.queryUserBySql();
		for (User user2 : list) {
			System.out.println(user2);
		}
	}
	@Test
	public void queryUserByids() {
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		List<Integer> list = new ArrayList<Integer>();
		list.add(10);
		list.add(16);
		list.add(22);
		QueryVo queryVo = new QueryVo();
		queryVo.setIds(list);
		List<User> users = userMapper.queryUserByids(queryVo);
		for (User user : users) {
			System.out.println(user);
		}
		
	}
}

resultMap

这里单独拿出来，因为这个涉及到不同的数据表
oderMapper.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：命名空间，用于隔离,使用动态代理开发,与UserMapper接口的类路径一致 -->
<mapper namespace="www.tl.dao.OrderMapper">
	<!-- 用resultMap解决类属性和表字段不匹配问题 -->
	<resultMap type="Order" id="orderMap">
		<!-- 定义主键，如果是多个字段则定义多个id
			 column:数据库中的字段
			 property:指定类中的属性 -->
		<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="orderMap">
		select id,user_id,number,createtime,note from orders
	</select>
	
</mapper>

Oder.java

package www.tl.entity;

import java.io.Serializable;
import java.util.Date;

public class Order implements Serializable{
    /**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	private Integer id;

    private Integer userId;

    private String number;

    private Date createtime;

    private String note;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number == null ? null : number.trim();
    }

    public Date getCreatetime() {
        return createtime;
    }

    public void setCreatetime(Date createtime) {
        this.createtime = createtime;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note == null ? null : note.trim();
    }

	@Override
	public String toString() {
		return "Order [id=" + id + ", userId=" + userId + ", number=" + number + ", createtime=" + createtime
				+ ", note=" + note + "]";
	} 
}

OderMapper.java

package www.tl.dao;

import java.util.List;

import www.tl.entity.Order;
import www.tl.entity.User;

public interface OrderMapper {

	public List<Order> queryOrderAll();
	
}

OrderTeset.java

package www.tl.daotest;
import java.io.InputStream;
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 www.tl.dao.OrderMapper;
import www.tl.dao.UserMapper;
import www.tl.entity.Order;

public class OderTest {
	private SqlSessionFactory sqlSessionFactory;
	@Before
	public void  init() throws Exception {
		/*加载资源文件*/
		String resource = "SqlMapConfig.xml";
		InputStream resourceAsStream = Resources.getResourceAsStream(resource);
		/*创建SqlSessionFactoryBuilder*/
		SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
		/*创建SqlSessionFactory*/
		this.sqlSessionFactory = sqlSessionFactoryBuilder.build(resourceAsStream);
		
		
	}
	@Test
	public void queryOrderAll() {
		/*创建会话（sqlSession）*/
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		/*创建OrderMapper*/
		OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
		/*调用sql并输出结果*/
		List<Order> list = orderMapper.queryOrderAll();
		for (Order order : list) {
			System.out.println(order);
		}
	}
	
}

总结

在进行程序编写时，程序的执行顺序为：

1. 测试类
2. 加载SqlMapConfig.xml文件
3. 调用动态代理接口
4. 操作实体类
5. 操作Mapper.java
6. 操作Mapper.xml
7. 操作数据库