mybatis教程---MyBatis一对一、MyBatis一对多

表和表之间的关联映射

用Oracle数据库中自带的emp和dept表

项目目录

BaseDao

package com.iweye.ssm.dao;

import org.apache.ibatis.session.SqlSession;

import com.iweye.ssm.util.DBUtil;

public class BaseDao {
	public SqlSession getSession() {
		return DBUtil.getSession();
	}
}

DeptDao

package com.iweye.ssm.dao;

import java.util.List;

import com.iweye.ssm.pojo.Dept;
/**
 * 简单的关联映射
 * @auto <a href="mailto:1808857902@qq.com">wei_xing</a>
 * @time 2014年8月9日上午9:41:17
 * @version 2014
 */
public class DeptDao extends BaseDao{
	/**
	 * 查询所有
	 * @return
	 */
	public List<Dept> getAll() {
		try {
			List<Dept> list = getSession().selectList("deptMapper.selectAllDepts");
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
		}
	}
	public List<Dept> selectAllDeptsForAssociation() {
		try {
			List<Dept> list = getSession().selectList("deptMapper.selectAllDeptsForAssociation");
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
		}
	}
}

EmpDao

package com.iweye.ssm.dao;

import java.util.List;

import com.iweye.ssm.pojo.Emp;

public class EmpDao extends BaseDao {
	
	public List<Emp> getAll() {
		try {
			List<Emp> list = getSession().selectList("empMapper.getAllEmps");
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
		}
	}
	public List<Emp> getAllEmpsForAssociationMap(){
		try {
			List<Emp> list = getSession().selectList("empMapper.getAllEmpsForAssociationMap");
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
		}
	}
	public List<Emp> getAllEmpsForCollectionMap(){
		try {
			List<Emp> list = getSession().selectList("empMapper.getAllEmpsForCollectionMap");
			return list;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
		}
	}
	
}

Dept.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="deptMapper">
	<!-- 简单的结果映射 -->
	<resultMap id="deptResultMap" type="Dept">
	  <id property="deptno" column="deptno" />
	  <result property="dname" column="dname"/>
	  <result property="loc"  column="loc"/>
	  <collection property="emps" ofType="Emp" column="deptno">
	  	  <id property="empno" column="empno" />
		  <result property="ename" column="ename"/>
		  <result property="job" column="job"/>
		  <result property="mgr" column="mgr"/>
		  <result property="hiredate" column="hiredate"/>
		  <result property="sal" column="sal"/>
		  <result property="comm" column="comm"/> 
		   <result property="deptno" column="deptno"/> 
	  </collection>
	</resultMap>


	<select id="selectAllDepts" resultType="Dept">
		select * from dept
	</select>
	
	<select id="selectAllDeptsForAssociation"  parameterType="int" resultMap="deptResultMap">
		select d.deptno,d.dname,d.loc,e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno 
		from dept d, emp e 
		where d.deptno=e.deptno  
	</select>
	
	<select id="selectDeptByDeptno" parameterType="int" resultType="Dept">
		select * from dept where depteno = #{deptno}
	</select>
</mapper>

Emp.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="empMapper">
	<sql id="selectSql">a.empno,a.job,a.mgr, a.hiredate,a.sal,a.comm,a.deptno,b.deptno,b.dname,b.loc</sql>
	
	<select id="getAllEmps" resultType="Emp">
		select * from emp
	</select>

	<!-- ###############################Association############################################ -->
	<resultMap id="empAssociationResult" type="Emp">
		  <id property="empno" column="empno" />
		  <result property="ename" column="ename"/>
		  <result property="job" column="job"/>
		  <result property="mgr" column="mgr"/>
		  <result property="hiredate" column="hiredate"/>
		  <result property="sal" column="sal"/>
		  <result property="comm" column="comm"/>
		  <result property="deptno" column="deptno"/>
		  <association property="dept" column="deptno" javaType="Dept">
			  <id property="deptno" column="deptno"/>
			  <result property="dname" column="dname"/>
			  <result property="loc" column="loc"/>
		  </association>
	</resultMap>
	<!-- 这里也可以直接写为
		select * from emp a, dept b 
		where  a.deptno = b.deptno
	 -->
	<select id="getAllEmpsForAssociationMap" resultMap="empAssociationResult">
		select <include refid="selectSql"/> from  emp a left outer join dept  b
		on b.deptno=a.deptno  
	</select>
		<!-- ###############################collection ############################################ -->
	<resultMap id="empCollectionResult" type="Emp">
		  <id property="empno" column="empno" />
		  <result property="ename" column="ename"/>
		  <result property="job" column="job"/>
		  <result property="mgr" column="mgr"/>
		  <result property="hiredate" column="hiredate"  typeHandler="com.iweye.ssm.typeHandler.DateTypeHandler"/>
		  <result property="sal" column="sal"/>
		  <result property="comm" column="comm"/>
		  <result property="deptno" column="deptno"/>
		  <collection  property="dept"  column="deptno" javaType="Dept">
			  <id property="deptno" column="deptno"/>
			  <result property="dname" column="dname"/>
			  <result property="loc" column="loc"/>
		  </collection >
	</resultMap>
	
	<select id="getAllEmpsForCollectionMap" resultMap="empCollectionResult">
		select * from emp e left outer join dept  d
		on e.deptno=d.deptno  
	</select>
</mapper>

Dept

package com.iweye.ssm.pojo;

import java.util.List;


/**
 * @auto <a href="mailto:1808857902@qq.com">wei_xing</a>
 * @time 2014年8月9日上午7:57:04
 * @version 2014
 */
public class Dept {
	private Integer deptno;
	private String dname;
	private String loc;
	private  List<Emp> emps;
	
	public Dept( String dname, String loc) {
		super();
		this.dname = dname;
		this.loc = loc;
	}

	public Dept() {
		super();
		// TODO Auto-generated constructor stub
	}

	public Integer getDeptno() {
		return deptno;
	}

	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}

	public String getDname() {
		return dname;
	}

	public void setDname(String dname) {
		this.dname = dname;
	}

	public String getLoc() {
		return loc;
	}

	public void setLoc(String loc) {
		this.loc = loc;
	}

	public List<Emp> getEmps() {
		return emps;
	}

	public void setEmps(List<Emp> emps) {
		this.emps = emps;
	}

	@Override
	public String toString() {
		return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc
				+ ", emps=" + emps + "]";
	}

}

Emp

package com.iweye.ssm.pojo;

import java.util.Date;


/**
 *  
 * @auto  <a href="mailto:1808857902@qq.com">wei_xing</a>
 * @time 2014-5-26 下午03:23:48
 * @version 2014
 */

public class Emp implements java.io.Serializable {
	private static final long serialVersionUID = 1L;
	private Long empno;//
	private String  ename;//
	private String   job    ;//
	private Long  mgr;//
	private Date hiredate;// DATE,
	private Double  sal;
	private Double  comm ;
	private Integer deptno ;
	private Dept dept;
	
	public Emp() {
		super();
	}
	public Emp(Long empno, String ename, String job, Long mgr, Date hiredate,
			Double sal, Double comm, Integer deptno, Dept dept) {
		super();
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
		this.dept = dept;
	}



	public Long getEmpno() {
		return empno;
	}
	public void setEmpno(Long empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public Long getMgr() {
		return mgr;
	}
	public void setMgr(Long mgr) {
		this.mgr = mgr;
	}
 
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}
	public Double getSal() {
		return sal;
	}
	public void setSal(Double sal) {
		this.sal = sal;
	}
	public Double getComm() {
		return comm;
	}
	public void setComm(Double comm) {
		this.comm = comm;
	}
	public Dept getDept() {
		return dept;
	}
	public void setDept(Dept dept) {
		this.dept = dept;
	}
	@Override
	public String toString() {
		return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job
				+ ", mgr=" + mgr + ", hiredate=" + hiredate + ", sal=" + sal
				+ ", comm=" + comm + ", deptno=" + deptno + ", dept=" + dept
				+ "]";
	}
}

DateTypeHandler 这个是对日期类型的转换

package com.iweye.ssm.typeHandler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;

import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;

public class DateTypeHandler implements TypeHandler<String>{

	@Override
	public String getResult(ResultSet rs, String para) throws SQLException {
		String sdf = rs.getString(para);
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.YEAR, Integer.valueOf(sdf.substring(0, 4)));
		cal.set(Calendar.MONTH, Integer.valueOf(sdf.substring(4, 6)));
		cal.set(Calendar.DAY_OF_MONTH, Integer.valueOf(sdf.substring(0, 4)));
		return (new SimpleDateFormat("yyyy-MM-dd").format(cal.getTime()));
	}

	@Override
	public String getResult(ResultSet rs, int para) throws SQLException {
		String sdf = rs.getString(para);
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.YEAR, Integer.valueOf(sdf.substring(0, 4)));
		cal.set(Calendar.MONTH, Integer.valueOf(sdf.substring(4, 6)));
		cal.set(Calendar.DAY_OF_MONTH, Integer.valueOf(sdf.substring(0, 4)));
		return (new SimpleDateFormat("yyyy-MM-dd").format(cal.getTime()));
	}

	@Override
	public String getResult(CallableStatement cs, int para) throws SQLException {
		String sdf = cs.getString(para);
		Calendar cal = Calendar.getInstance();
		cal.set(Calendar.YEAR, Integer.valueOf(sdf.substring(0, 4)));
		cal.set(Calendar.MONTH, Integer.valueOf(sdf.substring(4, 6)));
		cal.set(Calendar.DAY_OF_MONTH, Integer.valueOf(sdf.substring(0, 4)));
		return (new SimpleDateFormat("yyyy-MM-dd").format(cal.getTime()));
	}

	@Override
	public void setParameter(PreparedStatement ps, int i, String date, JdbcType jt) throws SQLException {
		ps.setString(i, date);
	}
}

DBUtil

package com.iweye.ssm.util;

import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class DBUtil {	

	public DBUtil() {
	}
	// 配置文件的所在位置和名称
	private static String resource = "mybatis-config.xml";
	// 存放连接池
	private static final ThreadLocal<SqlSession> threadLocal = new ThreadLocal<SqlSession>();
	// 用来建立连接的，该类就是连接池，使用单例设计模式
	private static SqlSessionFactory sqlSessionFactory;
	// 由于SessionFactory是重量级的，所以只实例化一次
	static {
		try {
			// 加载配置文件到内存中
			InputStream inputStream = Resources.getResourceAsStream(resource);
			// 建立连接池以及里面的连接
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	/**
	 * 单例模式 取得数据库连接对象
	 * @return
	 */
	public static SqlSession getSession() {       
		// 如果没有连接，则取得一个新的连接       
		SqlSession session = threadLocal.get();
		if (session == null) {             
			session = sqlSessionFactory.openSession();          
			// 把取得出的连接记录到ThreadLocal中，以便下次使用。
			threadLocal.set(session);
		}
		return session; 
	}

	
	/**
	 * 关闭连接
	 */
	 public static void closeSession() {
        SqlSession session = (SqlSession) threadLocal.get();
        threadLocal.set(null);
        if (session != null) {
            session.close();
        }
    }
	 
	public static void main(String[] args) {
		System.err.println(getSession());
	}
}

config.properties

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:orcl
username=scott
password=tiger

mybatis-config.xml

<?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>
	<!-- 引用资源文件 -->
	<properties resource="config.properties"  />
	<!-- 类型别名是为 Java 类型命名一个短的名字 -->
	<typeAliases>
		<typeAlias alias="Dept" type="com.iweye.ssm.pojo.Dept"/>
		<typeAlias alias="Emp" type="com.iweye.ssm.pojo.Emp"/>
	</typeAliases>
	
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="${driver}" />
				<property name="url" value="${url}" />
				<property name="username" value="${username}" />
				<property name="password" value="${password}" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="com/iweye/ssm/dao/Dept.xml" />
		<mapper resource="com/iweye/ssm/dao/Emp.xml" />
	</mappers>
</configuration>