mybatis多表查询操作

1.配置文件

<?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>
		<typeAlias alias="Emp" type="com.yh.entity.Employee"/>
		<typeAlias alias="Dept" type="com.yh.entity.Dept"/>
	</typeAliases>

	<environments default="development">
		<environment id="development">
		<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
			<property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
			<property name="url" value="jdbc:oracle:thin:@172.16.17.160:1521:orcl"/>
			<property name="username" value="scott"/>
			<property name="password" value="123"/>
			</dataSource>
		</environment>
	</environments>
	
	
	<mappers>
		<mapper resource="com/yh/entity/EmployeeMapper.xml"/>
		<mapper resource="com/yh/entity/DeptMapper.xml"/>
	</mappers>
</configuration>

2.实体类

public class Dept implements java.io.Serializable {

	// Fields

	private Integer deptno;
	private String dname;
	private String loc;
	private Set<Employee> employees = new HashSet<Employee>(0);

public class Employee implements java.io.Serializable {

	// Fields

	private Integer empno;
	private Dept dept;
	private String ename;
	private String job;
	private Short mgr;
	private Date hiredate;
	private Double sal;
	private Double comm;

3.映射文件

<?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.yh.entity">
	<resultMap id="BaseResultMap" type="Emp">
		<id property="empno" column="empno" />
		<result property="ename" column="ename" />
		<result property="job" column="job" />
	</resultMap>

	<!-- 多对一 -->
	<resultMap type="Emp" id="EmpDeptRM" extends="BaseResultMap">
		<association property="dept" javaType="Dept">
			<id column="deptno" property="deptno" jdbcType="INTEGER" />
			<result column="dname" property="dname" jdbcType="VARCHAR" />
			<result column="loc" property="loc" jdbcType="INTEGER" />
		</association>
	</resultMap>
	<select id="getDeptOfEmp1" parameterType="int" resultMap="EmpDeptRM">
		SELECT
		d.*,e.* FROM Dept d, Emp e
		WHERE d.deptno=e.deptno AND
		e.empno=#{empno}
  </select>
</mapper>

<?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.yh.entity">
   <resultMap type="Dept" id="DeptEmpRM">
	    <id column="deptno" property="deptno" jdbcType="INTEGER" />
	    <result column="dname" property="dname" jdbcType="VARCHAR" />
	    <result column="loc" property="loc" jdbcType="INTEGER" />
	      
	  	<collection property="employees"  ofType="Emp">
	  		<id property="empno" column="empno"/>
	  		<result property="ename" column="ename"/>
	  		<result property="job" column="job"/>
	  		<result property="hiredate" column="hiredate"/>
	  	</collection>
  </resultMap>
	
	
  <select id="getDeptOfEmp" parameterType="int" resultMap="DeptEmpRM">
     SELECT d.*,e.* FROM  Dept d, Emp e
 WHERE d.deptno=e.deptno AND e.empno=#{empno}
  </select>
   <select id="getDeptOfEmp2" parameterType="int" resultMap="DeptEmpRM">
     SELECT d.*,e.* FROM  Dept d, Emp e
 WHERE d.deptno=e.deptno AND d.deptno=20
  </select>
   
</mapper>

4.单元测试

public class MybatisTest {
	@Test
	public void getDeptOfEmp() {
		String cfgname = "mybatis-config.xml";
		Reader reader = null;
		SqlSessionFactory sf = null;
		SqlSession session = null;
		int count = 0;

		try {
			reader = Resources.getResourceAsReader(cfgname);
			SqlSessionFactoryBuilder sfb = new SqlSessionFactoryBuilder();// ����������
			sf = sfb.build(reader);// ��������
			session = sf.openSession();
//			Dept d=(Dept)session.selectOne("com.yh.entity.getDeptOfEmp", 7369);
//			System.out.println(d.getDname()+"  ");
//			Set<Employee> set=d.getEmployees();
//			for (Employee employee : set) {
//				System.out.println(set.size());
//				System.out.println(employee.getEmpno());
//				System.out.println(employee.getEname());
//				System.out.println(employee.getJob());
//				System.out.println(employee.getDept());
//				System.out.println("------------------------------");
//			}
			
			List<Dept> d2=session.selectList("com.yh.entity.getDeptOfEmp2", 20);
			System.out.println(d2.size());
			for (Dept d : d2) {
				System.out.println("---------------"+d.getDeptno());
				System.out.println(d.getDname());
				System.out.println("---------------"+d.getEmployees().size());
				Iterator<Employee> it=d.getEmployees().iterator();
				while(it.hasNext()){
					Employee e=it.next();
					System.out.println("++++++++++++++++"+e.getEmpno());
					System.out.println("++++++++++++++++"+e.getHiredate());
					System.out.println("++++++++++++++++"+e.getSal());
				}
			}
			
//			Employee e=(Employee)session.selectOne("com.yh.entity.getDeptOfEmp1", 7369);
//			System.out.println(e.getEname()+" "+e.getDept().getDname());
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
	}
}

5.订单表中包含商品信息，订单信息，着重两个一对一的映射文件以及相关提取参数方法，两次内联的sql语句是实现

<?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.yunhe.dao.ItemMapper">
<resultMap type="Item" id="ItemGoodOrder">
	<id property="id" column="ID" javaType="INTEGER"/>
	<result property="amount" column="AMOUNT" javaType="INTEGER"/>
	<association property="good" javaType="com.yunhe.entity.OrderGood">
		<id column="ID" property="id"  jdbcType="INTEGER" />
		<result column="PRICE" property="price"  jdbcType="FLOAT" />
		<result column="NAME" property="name"  jdbcType="INTEGER" />
		<result column="SPEOFICATION" property="sprofication"  jdbcType="INTEGER" />
		<result column="MANUFACTURER" property="manufacturer"  jdbcType="INTEGER" />
	</association>
	<association property="order" resultMap="ItemGoodOrder1"/>
</resultMap>

<resultMap type="com.yunhe.entity.OrderOrder" id="ItemGoodOrder1">
		<result column="CREATETIME" property="createtime"  jdbcType="DATE" />
		<result column="ADDRESS" property="address"  jdbcType="INTEGER" />
		<result column="NAME" property="name"  jdbcType="INTEGER" />
</resultMap>

<insert id="additem" parameterType="Item">
		insert into orderitem  values(sq_id.nextval,#{gid},#{oid},#{amount})
	</insert>
	<select id="selectItem" parameterType="int" resultMap="ItemGoodOrder">
		select t.* ,g.*,o.* from ORDERITEM t join ordergood g on t.gid=g.id 
			join orderorder o on o.id=t.oid where t.oid=#{orderid}
	</select>
</mapper>

public class IItemDao implements ItemDao {

	public int additem(OrderItem item) {
		// TODO Auto-generated method stub
		SqlSession session=MyBatisUtil.getSession();
		int i=session.insert("com.yunhe.dao.ItemMapper.additem", item);
		session.commit();
		MyBatisUtil.closeSession();
		return i;
	}

	public List<OrderItem> selectItem(int orderid) {
		// TODO Auto-generated method stub
		SqlSession session=MyBatisUtil.getSession();
		List<OrderItem> i=session.selectList("com.yunhe.dao.ItemMapper.selectItem", orderid);
		MyBatisUtil.closeSession();
		return i;
	}

}

6.插入注解，以及系统当前时间的sql语句，多条件查询的语句#{id} 用于数值 '${address}' 用于非数值

<mapper namespace="com.yunhe.dao.OrderMapper">
	<insert id="saveorder" parameterType="Order">
		insert into orderorder  values(sq_id.nextval,'${name}','${address}',sysdate)
	</insert>
	<select id="selectorder" parameterType="Order" resultType="Order">
		select  * from ORDERORDER o 
		<where>
			<if test="id>0"> o.id=#{id}</if>
			<if test="name!=null">and  o.name='${name}'</if>
			<if test="address!=null">and o.address='${address}'</if>
		</where>
	order by createtime desc
	</select>
</mapper>

6MybatisUtil讲解

package com.yunhe.util;

import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
/**
 * mybatis连接数据库的工具类
 * @author Administrator
 *
 */
public class MyBatisUtil {
	private MyBatisUtil(){
	}
	private static final String RESOURCE = "mybatis-config.xml";
	private static SqlSessionFactory sqlSessionFactory = null;
	private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
	static {
		Reader reader = null;
		try {
			reader = Resources.getResourceAsReader(RESOURCE);
			SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
			sqlSessionFactory = builder.build(reader);
		} catch (Exception e1) {
			e1.printStackTrace();
			throw new ExceptionInInitializerError("初始化MyBatis错误，请�?��配置文件或数据库");
			
		}
	}
	public static SqlSessionFactory getSqlSessionFactory(){
		return sqlSessionFactory;
	}
	public static SqlSession getSession(){
		//sessionTL的get()方法根据当前线程返回其对应的线程内部变量�?
		//也就是我们需要的Session，多线程情况下共享数据库链接是不安全的�?
		//ThreadLocal保证了每个线程都有自己的Session�?
		SqlSession session = threadLocal.get();
		// 如果session为null，则打开�?��新的session
		if (session == null){
			session = (sqlSessionFactory !=null) ?sqlSessionFactory.openSession():null;
			threadLocal.set(session); // 5
		}
		return session;
	}
	public static void closeSession(){
		SqlSession session = (SqlSession) threadLocal.get(); // 2
		threadLocal.set(null);
		if (session !=null){
			session.close();
		}
	}
}