表和表之间的关联映射

用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 mapperPUBLIC "-//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 mapperPUBLIC "-//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  bon 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  don 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;}@Overridepublic 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;}@Overridepublic 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>{@Overridepublic 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()));}@Overridepublic 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()));}@Overridepublic 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()));}@Overridepublic 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 configurationPUBLIC "-//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>