Top

JAVA Spring MyBatis DAY07

  1. MyBatis使用案例
  2. 返回Map类型数据
  3. 使用Mapper映射器
  4. 使用ResultMap映射结果

1 MyBatis使用案例

1.1 问题

使用MyBatis对员工表进行增删改查。

1.2 方案

MyBatis使用步骤:

  1. 导包
  2. 创建MyBatis主配置文件
  3. 创建实体类
  4. 创建映射文件
  5. 创建数据访问组件

1.3 步骤

步骤一:创建项目,导入jar包

创建项目Spring07,导入jar包,如下图:

图-1

步骤二: 创建MyBatis主配置文件

在src下创建MyBatis主配置文件SqlMapConfig.xml,代码如下:

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN" 
	"/ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
	<environments default="environment">
		<environment id="environment">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="oracle.jdbc.OracleDriver" />
				<property name="url"
					value="jdbc:oracle:thin:@localhost:1521:xe" />
				<property name="username" value="lhh" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/tarena/dao/EmpMapper.xml" />
	</mappers>
</configuration>

步骤三:创建实体类

创建员工实体类Emp,代码如下:

package com.tarena.entity;

import java.io.Serializable;
import java.sql.Date;

public class Emp implements Serializable{

	private Integer empno;
	private String ename;
	private String job;
	private Integer mgr;
	private Date hiredate;
	private Double sal;
	private Double comm;
	private Integer deptno;

	public Integer getEmpno() {
		return empno;
	}

	public void setEmpno(Integer 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 Integer getMgr() {
		return mgr;
	}

	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}

	public Date getHiredate() {
		return hiredate;
	}

	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}

	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 Integer getDeptno() {
		return deptno;
	}

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

}

步骤四:创建映射文件

创建映射文件EmpMapper.xml,代码如下:

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
 "/ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="com.tarena.dao.EmpMapper">

	<select id="findAll" 
		resultType="com.tarena.entity.Emp">
		select * from t_emp
	</select>
	
	<select id="findById"
		parameterType="integer"
		resultType="com.tarena.entity.Emp">
		select * from t_emp where empno=#{id}
	</select>
	
	<insert id="save" 
		parameterType="com.tarena.entity.Emp">
		insert into t_emp values(
			emp_seq.nextval,
			#{ename},
			#{job},
			#{mgr},
			#{hiredate},
			#{sal},
			#{comm},
			#{deptno}
		)
	</insert>
	
	<update id="update"
		parameterType="com.tarena.entity.Emp">
		update t_emp set
			ename=#{ename},
			job=#{job},
			mgr=#{mgr},
			hiredate=#{hiredate},
			sal=#{sal},
			comm=#{comm},
			deptno=#{deptno}
		where empno=#{empno}
	</update>
	
	<delete id="delete"
		parameterType="integer">
		delete from t_emp where empno=#{id}
	</delete>
	
</mapper>

步骤五:创建数据访问组件

创建获取连接工具类MyBatisUtil,代码如下:

package com.tarena.dao;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.tarena.test.TestCase;

public class MyBatisUtil {
	
	private static SqlSessionFactory sf;
	
	static {
		//加载配置文件
		SqlSessionFactoryBuilder builder =
			new SqlSessionFactoryBuilder();
		sf = builder.build(TestCase.class.getClassLoader()
				.getResourceAsStream("SqlMapConfig.xml"));
	}
	
	/**
	 *	创建连接
	 */
	public static SqlSession getSession() {
		return sf.openSession();
	}

	public static void main(String[] args) {
		SqlSession session = MyBatisUtil.getSession();
		System.out.println(session);
		session.close();
	}
	
}

创建数据访问组件MyBatisEmpDao,代码如下:

package com.tarena.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.tarena.entity.Emp;

public class MyBatisEmpDao {
	
	public List<Emp> findAll() {
		SqlSession session = MyBatisUtil.getSession();
		List<Emp> list = session.selectList("com.tarena.dao.EmpMapper.findAll");
		session.close();
		return list;
	}
	
	public Emp findById(int id) {
		SqlSession session = MyBatisUtil.getSession();
		Emp emp = session.selectOne("com.tarena.dao.EmpMapper.findById", id);
		session.close();
		return emp;
	}
	
	public void save(Emp e) {
		SqlSession session = MyBatisUtil.getSession();
		try {
			session.insert("com.tarena.dao.EmpMapper.save", e);
			session.commit();
		} catch (Exception e1) {
			e1.printStackTrace();
			session.rollback();
		} finally {
			session.close();
		}
	}
	
	public void update(Emp e) {
		SqlSession session = MyBatisUtil.getSession();
		try {
			session.update("com.tarena.dao.EmpMapper.update", e);
			session.commit();
		} catch (Exception e1) {
			e1.printStackTrace();
			session.rollback();
		} finally {
			session.close();
		}
	}
	
	public void delete(int id) {
		SqlSession session = MyBatisUtil.getSession();
		try {
			session.delete("com.tarena.dao.EmpMapper.delete", id);
			session.commit();
		} catch (Exception e1) {
			e1.printStackTrace();
			session.rollback();
		} finally {
			session.close();
		}
	}
	
}

步骤六:测试

创建测试类TestCase,代码如下:
package com.tarena.test;

import java.sql.Date;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.tarena.dao.DeptMapper;
import com.tarena.dao.EmpMapper;
import com.tarena.dao.MyBatisEmpDao;
import com.tarena.dao.MyBatisUtil;
import com.tarena.entity.Dept;
import com.tarena.entity.Emp;

public class TestCase {
	
	/**
	 * 创建连接
	 */
	@Test
	public void test1() {
		SqlSessionFactoryBuilder builder =
			new SqlSessionFactoryBuilder();
		SqlSessionFactory sf = 
			builder.build(TestCase.class.getClassLoader()
					.getResourceAsStream("SqlMapConfig.xml"));
		SqlSession session = sf.openSession();
		System.out.println(session);
		session.close();
	}
	
	/**
	 * MyBatisEmpDao.findAll()
	 */
	@Test
	public void test2() {
		EmpMapper mapper = new MyBatisEmpDao();
		List<Emp> list = mapper.findAll();
		for(Emp e : list) {
			System.out.println(
				e.getEmpno() + " " + e.getEname());
		}
	}
	
	/**
	 * MyBatisEmpDao.findById()
	 */
	@Test
	public void test3() {
		EmpMapper mapper = new MyBatisEmpDao();
		Emp e = mapper.findById(1);
		System.out.println(
			e.getEmpno() + " " + e.getEname());
	}
	
	/**
	 * MyBatisEmpDao.save()
	 */
	@Test
	public void test4() {
		Emp e = new Emp();
		e.setEname("唐僧");
		e.setJob("领导");
		e.setMgr(0);
		e.setHiredate(new Date(
			System.currentTimeMillis()));
		e.setSal(9000.0);
		e.setDeptno(1);
		
		EmpMapper mapper = new MyBatisEmpDao();
		mapper.save(e);
	}
	
	/**
	 * MyBatisEmpDao.update()
	 */
	@Test
	public void test5() {
		EmpMapper mapper = new MyBatisEmpDao();
		Emp e = mapper.findById(21);
		e.setEname("孙悟空");
		e.setSal(3000.0);
		mapper.update(e);
	}
	
	/**
	 * MyBatisEmpDao.delete()
	 */
	@Test
	public void test6() {
		EmpMapper mapper = new MyBatisEmpDao();
		mapper.delete(21);
	}
	
}

依次执行这些测试方法,效果如下图:

图-2

1.4 完整代码

SqlMapConfig.xml完整代码如下:

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN" 
	"/ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
	<environments default="environment">
		<environment id="environment">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="oracle.jdbc.OracleDriver" />
				<property name="url"
					value="jdbc:oracle:thin:@localhost:1521:xe" />
				<property name="username" value="lhh" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/tarena/dao/EmpMapper.xml" />
		<mapper resource="com/tarena/dao/DeptMapper.xml" />
	</mappers>
</configuration>

Emp完整代码如下:

package com.tarena.entity;

import java.io.Serializable;
import java.sql.Date;

public class Emp implements Serializable{

	private Integer empno;
	private String ename;
	private String job;
	private Integer mgr;
	private Date hiredate;
	private Double sal;
	private Double comm;
	private Integer deptno;

	public Integer getEmpno() {
		return empno;
	}

	public void setEmpno(Integer 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 Integer getMgr() {
		return mgr;
	}

	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}

	public Date getHiredate() {
		return hiredate;
	}

	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}

	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 Integer getDeptno() {
		return deptno;
	}

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

}

MyBatisUtil完整代码如下:

package com.tarena.dao;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.tarena.test.TestCase;

public class MyBatisUtil {
	
	private static SqlSessionFactory sf;
	
	static {
		//加载配置文件
		SqlSessionFactoryBuilder builder =
			new SqlSessionFactoryBuilder();
		sf = builder.build(TestCase.class.getClassLoader()
				.getResourceAsStream("SqlMapConfig.xml"));
	}
	
	/**
	 *	创建连接
	 */
	public static SqlSession getSession() {
		return sf.openSession();
	}

	public static void main(String[] args) {
		SqlSession session = MyBatisUtil.getSession();
		System.out.println(session);
		session.close();
	}
	
}

MyBatisEmpDao完整代码如下:

package com.tarena.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.tarena.entity.Emp;

public class MyBatisEmpDao {
	
	public List<Emp> findAll() {
		SqlSession session = MyBatisUtil.getSession();
		List<Emp> list = session.selectList("com.tarena.dao.EmpMapper.findAll");
		session.close();
		return list;
	}
	
	public Emp findById(int id) {
		SqlSession session = MyBatisUtil.getSession();
		Emp emp = session.selectOne("com.tarena.dao.EmpMapper.findById", id);
		session.close();
		return emp;
	}
	
	public void save(Emp e) {
		SqlSession session = MyBatisUtil.getSession();
		try {
			session.insert("com.tarena.dao.EmpMapper.save", e);
			session.commit();
		} catch (Exception e1) {
			e1.printStackTrace();
			session.rollback();
		} finally {
			session.close();
		}
	}
	
	public void update(Emp e) {
		SqlSession session = MyBatisUtil.getSession();
		try {
			session.update("com.tarena.dao.EmpMapper.update", e);
			session.commit();
		} catch (Exception e1) {
			e1.printStackTrace();
			session.rollback();
		} finally {
			session.close();
		}
	}
	
	public void delete(int id) {
		SqlSession session = MyBatisUtil.getSession();
		try {
			session.delete("com.tarena.dao.EmpMapper.delete", id);
			session.commit();
		} catch (Exception e1) {
			e1.printStackTrace();
			session.rollback();
		} finally {
			session.close();
		}
	}
	
}

EmpMapper.xml完整代码如下:

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
 "/ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="com.tarena.dao.EmpMapper">

	<select id="findAll" 
		resultType="com.tarena.entity.Emp">
		select * from t_emp
	</select>
	
	<select id="findById"
		parameterType="integer"
		resultType="com.tarena.entity.Emp">
		select * from t_emp where empno=#{id}
	</select>
	
	<insert id="save" 
		parameterType="com.tarena.entity.Emp">
		insert into t_emp values(
			emp_seq.nextval,
			#{ename},
			#{job},
			#{mgr},
			#{hiredate},
			#{sal},
			#{comm},
			#{deptno}
		)
	</insert>
	
	<update id="update"
		parameterType="com.tarena.entity.Emp">
		update t_emp set
			ename=#{ename},
			job=#{job},
			mgr=#{mgr},
			hiredate=#{hiredate},
			sal=#{sal},
			comm=#{comm},
			deptno=#{deptno}
		where empno=#{empno}
	</update>
	
	<delete id="delete"
		parameterType="integer">
		delete from t_emp where empno=#{id}
	</delete>
	
</mapper>

TestCase完整代码如下:

package com.tarena.test;

import java.sql.Date;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.tarena.dao.DeptMapper;
import com.tarena.dao.EmpMapper;
import com.tarena.dao.MyBatisEmpDao;
import com.tarena.dao.MyBatisUtil;
import com.tarena.entity.Dept;
import com.tarena.entity.Emp;

public class TestCase {
	
	/**
	 * 创建连接
	 */
	@Test
	public void test1() {
		SqlSessionFactoryBuilder builder =
			new SqlSessionFactoryBuilder();
		SqlSessionFactory sf = 
			builder.build(TestCase.class.getClassLoader()
					.getResourceAsStream("SqlMapConfig.xml"));
		SqlSession session = sf.openSession();
		System.out.println(session);
		session.close();
	}
	
	/**
	 * MyBatisEmpDao.findAll()
	 */
	@Test
	public void test2() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		List<Emp> list = dao.findAll();
		for(Emp e : list) {
			System.out.println(
				e.getEmpno() + " " + e.getEname());
		}
	}
	
	/**
	 * MyBatisEmpDao.findById()
	 */
	@Test
	public void test3() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		Emp e = dao.findById(1);
		System.out.println(
			e.getEmpno() + " " + e.getEname());
	}
	
	/**
	 * MyBatisEmpDao.save()
	 */
	@Test
	public void test4() {
		Emp e = new Emp();
		e.setEname("唐僧");
		e.setJob("领导");
		e.setMgr(0);
		e.setHiredate(new Date(
			System.currentTimeMillis()));
		e.setSal(9000.0);
		e.setComm(300.0);
		e.setDeptno(1);
		
		MyBatisEmpDao dao = new MyBatisEmpDao();
		dao.save(e);
	}
	
	/**
	 * MyBatisEmpDao.update()
	 */
	@Test
	public void test5() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		Emp e = dao.findById(61);
		e.setEname("孙悟空");
		e.setSal(3000.0);
		dao.update(e);
	}
	
	/**
	 * MyBatisEmpDao.delete()
	 */
	@Test
	public void test6() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		dao.delete(61);
	}
	
}

2 返回Map类型数据

2.1 问题

使用Map封装查询结果。

2.2 方案

只要指定返回结果为Map,则MyBatis可以自动将查询结果封装成Map。

2.3 步骤

步骤一:在映射文件中增加查询SQL

在EmpMapper.xml中增加一个SQL,根据部门ID查询员工,追加代码如下:

	<select id="findByDeptNo"
		parameterType="int"
		resultType="map">
		select * from t_emp where deptno=#{deptno}
	</select>

步骤二: 在数据访问组件中增加查询方法

在MyBatisEmpDao中增加查询方法,根据部门ID查询员工,追加代码如下:

	public List<Map<String,Object>> findByDeptNo(int deptno) {
		SqlSession session = MyBatisUtil.getSession();
		List<Map<String,Object>> list = 
			session.selectList("com.tarena.dao.EmpMapper.findByDeptNo", deptno);
		session.close();
		return list;
	}

步骤三:测试

在TestCase中增加测试方法,追加代码如下:

	/**
	 * MyBatisEmpDao.findByDeptNo()
	 */
	@Test
	public void test7() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		List<Map<String, Object>> list = dao.findByDeptNo(1);
		for(Map<String, Object> e : list) {
			System.out.println(
				e.get("EMPNO") + " " +
				e.get("ENAME"));
		}
	}

执行该方法,结果如下图:

图-3

2.4 完整代码

EmpMapper.xml完整代码如下:

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
 "/ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="com.tarena.dao.EmpMapper">

	<select id="findAll" 
		resultType="com.tarena.entity.Emp">
		select * from t_emp
	</select>
	
	<select id="findById"
		parameterType="integer"
		resultType="com.tarena.entity.Emp">
		select * from t_emp where empno=#{id}
	</select>
	
	<insert id="save" 
		parameterType="com.tarena.entity.Emp">
		insert into t_emp values(
			emp_seq.nextval,
			#{ename},
			#{job},
			#{mgr},
			#{hiredate},
			#{sal},
			#{comm},
			#{deptno}
		)
	</insert>
	
	<update id="update"
		parameterType="com.tarena.entity.Emp">
		update t_emp set
			ename=#{ename},
			job=#{job},
			mgr=#{mgr},
			hiredate=#{hiredate},
			sal=#{sal},
			comm=#{comm},
			deptno=#{deptno}
		where empno=#{empno}
	</update>
	
	<delete id="delete"
		parameterType="integer">
		delete from t_emp where empno=#{id}
	</delete>
	
	<select id="findByDeptNo"
		parameterType="int"
		resultType="map">
		select * from t_emp where deptno=#{deptno}
	</select>
	
</mapper>

MyBatisEmpDao完整代码如下:

package com.tarena.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.tarena.entity.Emp;

public class MyBatisEmpDao {
	
	public List<Emp> findAll() {
		SqlSession session = MyBatisUtil.getSession();
		List<Emp> list = session.selectList("com.tarena.dao.EmpMapper.findAll");
		session.close();
		return list;
	}
	
	public Emp findById(int id) {
		SqlSession session = MyBatisUtil.getSession();
		Emp emp = session.selectOne("com.tarena.dao.EmpMapper.findById", id);
		session.close();
		return emp;
	}
	
	public void save(Emp e) {
		SqlSession session = MyBatisUtil.getSession();
		try {
			session.insert("com.tarena.dao.EmpMapper.save", e);
			session.commit();
		} catch (Exception e1) {
			e1.printStackTrace();
			session.rollback();
		} finally {
			session.close();
		}
	}
	
	public void update(Emp e) {
		SqlSession session = MyBatisUtil.getSession();
		try {
			session.update("com.tarena.dao.EmpMapper.update", e);
			session.commit();
		} catch (Exception e1) {
			e1.printStackTrace();
			session.rollback();
		} finally {
			session.close();
		}
	}
	
	public void delete(int id) {
		SqlSession session = MyBatisUtil.getSession();
		try {
			session.delete("com.tarena.dao.EmpMapper.delete", id);
			session.commit();
		} catch (Exception e1) {
			e1.printStackTrace();
			session.rollback();
		} finally {
			session.close();
		}
	}
	
	public List<Map<String,Object>> findByDeptNo(int deptno) {
		SqlSession session = MyBatisUtil.getSession();
		List<Map<String,Object>> list = 
			session.selectList("com.tarena.dao.EmpMapper.findByDeptNo", deptno);
		session.close();
		return list;
	}
	
}

TestCase完整代码如下:

package com.tarena.test;

import java.sql.Date;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.tarena.dao.DeptMapper;
import com.tarena.dao.EmpMapper;
import com.tarena.dao.MyBatisEmpDao;
import com.tarena.dao.MyBatisUtil;
import com.tarena.entity.Dept;
import com.tarena.entity.Emp;

public class TestCase {
	
	/**
	 * 创建连接
	 */
	@Test
	public void test1() {
		SqlSessionFactoryBuilder builder =
			new SqlSessionFactoryBuilder();
		SqlSessionFactory sf = 
			builder.build(TestCase.class.getClassLoader()
					.getResourceAsStream("SqlMapConfig.xml"));
		SqlSession session = sf.openSession();
		System.out.println(session);
		session.close();
	}
	
	/**
	 * MyBatisEmpDao.findAll()
	 */
	@Test
	public void test2() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		List<Emp> list = dao.findAll();
		for(Emp e : list) {
			System.out.println(
				e.getEmpno() + " " + e.getEname());
		}
	}
	
	/**
	 * MyBatisEmpDao.findById()
	 */
	@Test
	public void test3() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		Emp e = dao.findById(1);
		System.out.println(
			e.getEmpno() + " " + e.getEname());
	}
	
	/**
	 * MyBatisEmpDao.save()
	 */
	@Test
	public void test4() {
		Emp e = new Emp();
		e.setEname("唐僧");
		e.setJob("领导");
		e.setMgr(0);
		e.setHiredate(new Date(
			System.currentTimeMillis()));
		e.setSal(9000.0);
		e.setComm(300.0);
		e.setDeptno(1);
		
		MyBatisEmpDao dao = new MyBatisEmpDao();
		dao.save(e);
	}
	
	/**
	 * MyBatisEmpDao.update()
	 */
	@Test
	public void test5() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		Emp e = dao.findById(61);
		e.setEname("孙悟空");
		e.setSal(3000.0);
		dao.update(e);
	}
	
	/**
	 * MyBatisEmpDao.delete()
	 */
	@Test
	public void test6() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		dao.delete(61);
	}
	
	/**
	 * MyBatisEmpDao.findByDeptNo()
	 */
	@Test
	public void test7() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		List<Map<String, Object>> list = dao.findByDeptNo(1);
		for(Map<String, Object> e : list) {
			System.out.println(
				e.get("EMPNO") + " " +
				e.get("ENAME"));
		}
	}
	
}

3 使用Mapper映射器

3.1 问题

使用Mapper映射器,实现对员工表的增删改查。

3.2 方案

Mapper映射器使用注意事项:

  1. Mapper接口名和对应的映射文件中的namespace必须一致
  2. Mapper接口中的方法必须和映射文件中的对应的SQL元素ID一致

3.3 步骤

步骤一:创建Mapper映射器

创建员工查询的Mapper映射器EmpMapper,代码如下:

package com.tarena.dao;

import java.util.List;
import java.util.Map;

import com.tarena.entity.Emp;

public interface EmpMapper {
	
	List<Emp> findAll();
	
	Emp findById(int id);
	
	void save(Emp e);
	
	void update(Emp e);
	
	void delete(int id);
	
	List<Map<String, Object>> findByDeptNo(int deptno);
	
}

步骤二: 测试

在TestCase中增加测试方法,追加代码如下:

	/**
	 * 使用Mapper映射器
	 */
	@Test
	public void test8() {
		SqlSession session = MyBatisUtil.getSession();
		EmpMapper mapper = session.getMapper(EmpMapper.class);
		List<Emp> list = mapper.findAll();
		for(Emp e : list) {
			System.out.println(
				e.getEmpno() + " " + e.getEname());
		}
	}

执行该方法,效果如下图:

图-4

3.4 完整代码

EmpMapper完整代码如下:

package com.tarena.dao;

import java.util.List;
import java.util.Map;

import com.tarena.entity.Emp;

public interface EmpMapper {
	
	List<Emp> findAll();
	
	Emp findById(int id);
	
	void save(Emp e);
	
	void update(Emp e);
	
	void delete(int id);
	
	List<Map<String, Object>> findByDeptNo(int deptno);
	
}

TestCase完整代码如下:

package com.tarena.test;

import java.sql.Date;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.tarena.dao.DeptMapper;
import com.tarena.dao.EmpMapper;
import com.tarena.dao.MyBatisEmpDao;
import com.tarena.dao.MyBatisUtil;
import com.tarena.entity.Dept;
import com.tarena.entity.Emp;

public class TestCase {
	
	/**
	 * 创建连接
	 */
	@Test
	public void test1() {
		SqlSessionFactoryBuilder builder =
			new SqlSessionFactoryBuilder();
		SqlSessionFactory sf = 
			builder.build(TestCase.class.getClassLoader()
					.getResourceAsStream("SqlMapConfig.xml"));
		SqlSession session = sf.openSession();
		System.out.println(session);
		session.close();
	}
	
	/**
	 * MyBatisEmpDao.findAll()
	 */
	@Test
	public void test2() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		List<Emp> list = dao.findAll();
		for(Emp e : list) {
			System.out.println(
				e.getEmpno() + " " + e.getEname());
		}
	}
	
	/**
	 * MyBatisEmpDao.findById()
	 */
	@Test
	public void test3() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		Emp e = dao.findById(1);
		System.out.println(
			e.getEmpno() + " " + e.getEname());
	}
	
	/**
	 * MyBatisEmpDao.save()
	 */
	@Test
	public void test4() {
		Emp e = new Emp();
		e.setEname("唐僧");
		e.setJob("领导");
		e.setMgr(0);
		e.setHiredate(new Date(
			System.currentTimeMillis()));
		e.setSal(9000.0);
		e.setComm(300.0);
		e.setDeptno(1);
		
		MyBatisEmpDao dao = new MyBatisEmpDao();
		dao.save(e);
	}
	
	/**
	 * MyBatisEmpDao.update()
	 */
	@Test
	public void test5() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		Emp e = dao.findById(61);
		e.setEname("孙悟空");
		e.setSal(3000.0);
		dao.update(e);
	}
	
	/**
	 * MyBatisEmpDao.delete()
	 */
	@Test
	public void test6() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		dao.delete(61);
	}
	
	/**
	 * MyBatisEmpDao.findByDeptNo()
	 */
	@Test
	public void test7() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		List<Map<String, Object>> list = dao.findByDeptNo(1);
		for(Map<String, Object> e : list) {
			System.out.println(
				e.get("EMPNO") + " " +
				e.get("ENAME"));
		}
	}
	
	/**
	 * 使用Mapper映射器
	 */
	@Test
	public void test8() {
		SqlSession session = MyBatisUtil.getSession();
		EmpMapper mapper = session.getMapper(EmpMapper.class);
		List<Emp> list = mapper.findAll();
		for(Emp e : list) {
			System.out.println(
				e.getEmpno() + " " + e.getEname());
		}
	}
	
}

4 使用ResultMap映射结果

4.1 问题

使用ResultMap映射结果,解决表中字段和实体类中属性不同名的问题。

4.2 方案

通过在映射文件中配置<resultMap>标记,将表中字段和实体对象中属性映射。

4.3 步骤

步骤一:创建部门表

创建部门表t_dept,代码如下:

--部门表
create table t_dept(
  deptno NUMBER(4) CONSTRAINT DEPT_ID_PK PRIMARY KEY,
  dname varchar(20),
  loc varchar(50)
);

create sequence dept_seq start with 100;

--预置的部门表数据
insert into t_dept values (1,'销售部','北京');
insert into t_dept values (2,'采购部','上海');
insert into t_dept values (3,'研发部','广州');
commit;

步骤二: 创建部门实体类

创建部门实体类,代码如下:

package com.tarena.entity;

import java.io.Serializable;

public class Dept implements Serializable {

	private Integer id;
	private String name;
	private String loc;

	public Integer getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getLoc() {
		return loc;
	}

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

}

步骤三:创建部门Mapper映射器

创建部门Mapper映射器DeptMapper,代码如下:

package com.tarena.dao;

import java.util.List;

import com.tarena.entity.Dept;

public interface DeptMapper {
	
	List<Dept> findAll();

}

步骤四:创建部门映射文件

创建部门映射文件DeptMapper.xml,代码如下:

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
 "/ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="com.tarena.dao.DeptMapper">

	<select id="findAll" 
		resultMap="deptMap">
		select * from t_dept
	</select>
	
	<resultMap type="com.tarena.entity.Dept" id="deptMap">
		<result property="id" column="deptno"/>
		<result property="name" column="dname"/>
		<result property="loc" column="loc"/>
	</resultMap>
	
</mapper>

步骤五:声明部门映射文件

在SqlMapConfig.xml中声明部门映射文件,代码如下:

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN" 
	"/ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
	<environments default="environment">
		<environment id="environment">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="oracle.jdbc.OracleDriver" />
				<property name="url"
					value="jdbc:oracle:thin:@localhost:1521:xe" />
				<property name="username" value="lhh" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/tarena/dao/EmpMapper.xml" />
#cold_bold		<mapper resource="com/tarena/dao/DeptMapper.xml" />
	</mappers>
</configuration>

步骤六:测试

在TestCase中增加测试方法,追加代码如下:

/**
	 * 使用ResultMap映射
	 */
	@Test
	public void test9() {
		SqlSession session = MyBatisUtil.getSession();
		DeptMapper mapper = session.getMapper(DeptMapper.class);
		List<Dept> list = mapper.findAll();
		for(Dept d : list) {
			System.out.println(
				d.getId() + " " + d.getName());
		}
	}

执行该测试方法,结果如下图:

图-5

4.4 完整代码

建表脚本完整代码如下:

--部门表
create table t_dept(
  deptno NUMBER(4) CONSTRAINT DEPT_ID_PK PRIMARY KEY,
  dname varchar(20),
  loc varchar(50)
);

create sequence dept_seq start with 100;

--预置的部门表数据
insert into t_dept values (1,'销售部','北京');
insert into t_dept values (2,'采购部','上海');
insert into t_dept values (3,'研发部','广州');
commit;

Dept完整代码如下:

package com.tarena.entity;

import java.io.Serializable;

public class Dept implements Serializable {

	private Integer id;
	private String name;
	private String loc;

	public Integer getId() {
		return id;
	}

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

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getLoc() {
		return loc;
	}

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

}

DeptMapper完整代码如下:

package com.tarena.dao;

import java.util.List;

import com.tarena.entity.Dept;

public interface DeptMapper {
	
	List<Dept> findAll();

}

DeptMapper.xml完整代码如下:

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"      
 "/ibatis.apache.org/dtd/ibatis-3-mapper.dtd">

<mapper namespace="com.tarena.dao.DeptMapper">

	<select id="findAll" 
		resultMap="deptMap">
		select * from t_dept
	</select>
	
	<resultMap type="com.tarena.entity.Dept" id="deptMap">
		<result property="id" column="deptno"/>
		<result property="name" column="dname"/>
		<result property="loc" column="loc"/>
	</resultMap>
	
</mapper>

SqlMapConfig.xml完整代码如下:

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE configuration PUBLIC "-//ibatis.apache.org//DTD Config 3.0//EN" 
	"/ibatis.apache.org/dtd/ibatis-3-config.dtd">
<configuration>
	<environments default="environment">
		<environment id="environment">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="oracle.jdbc.OracleDriver" />
				<property name="url"
					value="jdbc:oracle:thin:@localhost:1521:xe" />
				<property name="username" value="lhh" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/tarena/dao/EmpMapper.xml" />
		<mapper resource="com/tarena/dao/DeptMapper.xml" />
	</mappers>
</configuration>

TestCase完整代码如下:

package com.tarena.test;

import java.sql.Date;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.tarena.dao.DeptMapper;
import com.tarena.dao.EmpMapper;
import com.tarena.dao.MyBatisEmpDao;
import com.tarena.dao.MyBatisUtil;
import com.tarena.entity.Dept;
import com.tarena.entity.Emp;

public class TestCase {
	
	/**
	 * 创建连接
	 */
	@Test
	public void test1() {
		SqlSessionFactoryBuilder builder =
			new SqlSessionFactoryBuilder();
		SqlSessionFactory sf = 
			builder.build(TestCase.class.getClassLoader()
					.getResourceAsStream("SqlMapConfig.xml"));
		SqlSession session = sf.openSession();
		System.out.println(session);
		session.close();
	}
	
	/**
	 * MyBatisEmpDao.findAll()
	 */
	@Test
	public void test2() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		List<Emp> list = dao.findAll();
		for(Emp e : list) {
			System.out.println(
				e.getEmpno() + " " + e.getEname());
		}
	}
	
	/**
	 * MyBatisEmpDao.findById()
	 */
	@Test
	public void test3() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		Emp e = dao.findById(1);
		System.out.println(
			e.getEmpno() + " " + e.getEname());
	}
	
	/**
	 * MyBatisEmpDao.save()
	 */
	@Test
	public void test4() {
		Emp e = new Emp();
		e.setEname("唐僧");
		e.setJob("领导");
		e.setMgr(0);
		e.setHiredate(new Date(
			System.currentTimeMillis()));
		e.setSal(9000.0);
		e.setComm(300.0);
		e.setDeptno(1);
		
		MyBatisEmpDao dao = new MyBatisEmpDao();
		dao.save(e);
	}
	
	/**
	 * MyBatisEmpDao.update()
	 */
	@Test
	public void test5() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		Emp e = dao.findById(61);
		e.setEname("孙悟空");
		e.setSal(3000.0);
		dao.update(e);
	}
	
	/**
	 * MyBatisEmpDao.delete()
	 */
	@Test
	public void test6() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		dao.delete(61);
	}
	
	/**
	 * MyBatisEmpDao.findByDeptNo()
	 */
	@Test
	public void test7() {
		MyBatisEmpDao dao = new MyBatisEmpDao();
		List<Map<String, Object>> list = dao.findByDeptNo(1);
		for(Map<String, Object> e : list) {
			System.out.println(
				e.get("EMPNO") + " " +
				e.get("ENAME"));
		}
	}
	
	/**
	 * 使用Mapper映射器
	 */
	@Test
	public void test8() {
		SqlSession session = MyBatisUtil.getSession();
		EmpMapper mapper = session.getMapper(EmpMapper.class);
		List<Emp> list = mapper.findAll();
		for(Emp e : list) {
			System.out.println(
				e.getEmpno() + " " + e.getEname());
		}
	}
	
	/**
	 * 使用ResultMap映射
	 */
	@Test
	public void test9() {
		SqlSession session = MyBatisUtil.getSession();
		DeptMapper mapper = session.getMapper(DeptMapper.class);
		List<Dept> list = mapper.findAll();
		for(Dept d : list) {
			System.out.println(
				d.getId() + " " + d.getName());
		}
	}
	
}