MyBatis增删改查操作、MyBatis存储过程、MyBatis分页、MyBatis一对一、MyBatis一对多

 一、用到的实体类如下：

Student.java

package com.company.entity;

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

public class Student implements Serializable{
	
	private static final long serialVersionUID = 1L;
	private int id;
	private String name;
	private Date birth;
	private Group group;
	
	public Group getGroup() {
		return group;
	}
	public void setGroup(Group group) {
		this.group = group;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Date getBirth() {
		return birth;
	}
	public void setBirth(Date birth) {
		this.birth = birth;
	}
	@Override
	public String toString() {
		return "Student [birth=" + birth + ", group=" + group + ", id=" + id
				+ ", name=" + name + "]";
	}
	
	
}

Group.java

package com.company.entity;

import java.util.List;

public class Group {
	private int id;
	private String name;
	private String position;
	private List<Student> students;
	
	public List<Student> getStudents() {
		return students;
	}
	public void setStudents(List<Student> students) {
		this.students = students;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPosition() {
		return position;
	}
	public void setPosition(String position) {
		this.position = position;
	}
	@Override
	public String toString() {
		return "Group [id=" + id + ", name=" + name + ", position=" + position
				+ "]";
	}
	
}

二、实体对应的表结构

student表:

create table student(

id  int primary key,

name varchar2(20),

birth date,

group_id int references g_group(g_id));

 

g_group表:

create  table g_group(

g_id int primary key,

g_name varchar2(20),

g_position varchar2(30));

 

sequence:

create sequence student_id_sequence;

create sequence group_id_sequence;

 

三、Student和Group的映射文件如下，你可以在映射文件中找到，关于MyBatis的增删改查操作，MyBatis调用存储过程，MyBatis分页以及MyBatis对一对一、多对多的处理

xml文件中都标有注释，看的时候配合下面的具体实现看，虽然有点乱

 

student.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="com.company.dao.IStudentDAO">
	
	<!-- mybatis缓存 -->
	<cache eviction="LRU" flushInterval="600000" size="1024" readOnly="false" />
	
	<!-- sql标签用来定义一些可以被重用的sql语句或字段或片段等 -->
	<sql id="studentColumns">select id,name,birth from student</sql>
	
	<!-- 此处获得多对一的关系 ,但就单条记录而言却是一对一的关系，所以一对一的写法跟此相同-->
	<resultMap type="Student" id="getStudentAndGroup" >
		<id column="id" property="id"/>
		<result column="name" property="name"/>
		<result column="birth" property="birth"/>
		<association property="group" column="group_id" javaType="Group">
			<id column="g_id" property="id"/>
			<result column="g_name" property="name"/>
			<result column="g_position" property="position"/>
		</association>
	</resultMap>
	<select id="many2one" resultMap="getStudentAndGroup" parameterType="int" >
		select s.id,s.name,s.birth,s.group_id,g.g_id,g.g_name,g.g_position 
		from student s 
		left join g_group g on s.group_id = g.g_id
		where s.id = #{id}
	</select>
	
	
	<!-- 意图是获得一个学生，并且获得该学生所属的组，跟上面的意思差不多 ,用association的select属性-->
	<!-- 于上面的相比个人感觉上面的效率要高些，因为上面只有一条sql语句 -->
	<resultMap type="Student" id="getStudentAndGroupUseSelectMap">
		<id column="id" property="id"/>
		<result column="name" property="name"/>
		<result column="birth" property="birth"/>
		<association property="group" column="group_id" javaType="Group" select="selectGroup" />
	</resultMap>
	<select id="getStudentAndGroupUseSelect" resultMap="getStudentAndGroupUseSelectMap" parameterType="int">
		select * 
		from student 
		where id = #{id}
	</select>
	<select id="selectGroup" resultType="Group" parameterType="int" flushCache="false" useCache="true"><!-- 此处实用缓存 -->
		select g_id as id, g_name as name, g_position as position 
		from g_group 
		where g_id = #{id}
	</select>

	<!-- 动态sql语句 的测试dynamic sql-->	
	<select id="getStudentBySomeCondition" parameterType="Student" resultType="Student">
		select *
		from student
		<where>
			<if test="id != null">
				id>2
			</if>
			<if test="name != null">
				and name like '%g%'
			</if>
		</where>
	</select>
	
	<!-- MyBatis调用存储过程 -->
	<resultMap type="Student" id="studentMap">
		<id column="id" property="id"/>
		<result column="name" property="name"/>
		<result column="birth" property="birth"/>
	</resultMap>
	<select id="getAllUser" statementType="CALLABLE" >
		{call get_all_student(#{students ,mode=OUT, jdbcType=CURSOR, javaType=ResultSet, resultMap=studentMap} )}
	</select>
	
	
	<!-- MyBatis向student表中插入一条数据 -->
	<insert id="add" parameterType="Student" keyColumn="id">
		<selectKey keyProperty="id" order="BEFORE" resultType="int"> 
			select stu_id_sequence.nextval from dual
		</selectKey>
		insert into student(id,name,birth) values(#{id},#{name},#{birth})
	</insert>
	
	<!-- 根据id获得学生的信息 -->
	<select id="getById" parameterType="int" resultType="Student">
		<include refid="studentColumns"/> where id=#{id}
	</select>
	
	<!-- 此处的实现方法是一个分页的原型,请查看IStudentDAOImpl.java中的调用方法 -->
	<select id="getAllStudent" resultMap="studentMap">
		<include refid="studentColumns"/> order by id<!--此处是引用了上面预定义好的sql语句-->
	</select>
	
	
</mapper>

group.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="com.company.dao.IGroupDAO">
	
	
	
	<resultMap type="Group" id="groupResultMap" >
		<id column="g_id" property="id"/>
		<result column="g_name" property="name"/>
		<result column="g_position" property="position"/>
	</resultMap>
	<sql id="getALl">select * from</sql>
	
	<!-- 意图想通过获得组和组中的所有student,此处相当于one2many -->
	<resultMap type="Group" id="getGroupAndStudents">
		<id column="g_id" property="id"/>
		<result column="g_name" property="name"/>
		<result column="g_position" property="position"/>
		<collection property="students" ofType="Student" column="group_id"><!-- 注意此处的group_id是student表的外键 -->
			<id column="id" property="id"/>
			<result column="name" property="name"/>
			<result column="birth" property="birth"/>
		</collection>
	</resultMap>
	
	
	
	
	
	
	<select id="getById" parameterType="int" resultMap="getGroupAndStudents">
		select g.g_id,g.g_name,g.g_position,s.id,s.name,s.birth ,s.group_id
		from g_group g
		left join student s on g.g_id = s.group_id
		where g.g_id = #{id}
	</select>
	
	<!-- 
	<select id="getById" parameterType="int" resultType="Group">
		select g_id as id, g_name as name, g_position as position from g_group where g_id=#{id}
	</select>
	 -->
	<select id="getByIdResultMap" parameterType="_int" resultMap="groupResultMap">
		select g_id ,g_name, g_position  from g_group where g_id=#{id}
	</select>
	<delete id="deleteById" parameterType="_int" timeout="1000">
		delete from g_group where g_id=#{id}
	</delete>
	<insert id="add" parameterType="Group">
		insert into g_group(g_id, g_name, g_position) 
		values(#{id}, #{name}, #{position})
	</insert>
	
</mapper>

四、接口IStudentDAO.java和IGroupDAO.java中定义了IStudentDAOImpl.java和IGroupDAOImpl.java中需要实现的方法

IStudentDAO.java

package com.company.dao;

import java.util.List;

import com.company.entity.Student;

public interface IStudentDAO {
	/**
	 * 增加一个学生
	 * @param student
	 */
	public void add(Student student);
	
	/**
	 * 根据学生的Id删除学生
	 * @param id
	 */
	public void deleteById(int id);
	
	/**
	 * 通过学生的id获得学生的信息
	 * @param id
	 * @return
	 */
	public Student getById(int id);
	
	/**
	 * 更新学生信息
	 * @param student
	 */
	public void update(Student student);
	
	/**
	 * 此处是MyBatis的分页查询
	 * @return
	 */
	public List<Student> getAllStudent();
	
	/**
	 * 多对一
	 * @param id
	 * @return
	 */
	public Student many2one(int id);
	
	/**
	 * 获得学生的信息，并且获得该学生所属的组的信息
	 * @param id
	 * @return
	 */
	public Student getStudentAndGroupUseSelect(int id);
	
	/**
	 * 动态sql
	 * @param student
	 * @return
	 */
	public List<Student> getStudentBySomeCondition(Student student);
	
	/**
	 * 获得所有的学生信息，此处是调用在数据库中存储过程
	 * @return
	 */
	public List<Student> getAllUser();
}

对应的实现类IStudentDAOImpl.java如下：

package com.company.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import com.company.dao.IStudentDAO;
import com.company.entity.Student;
import com.company.util.DBUtil;

public class IStudentDAOImpl implements IStudentDAO {
	
	
	
	public void add(Student student) {
		SqlSessionFactory sqlSessionFactory = DBUtil.getSqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		try{
			IStudentDAO dao = session.getMapper(IStudentDAO.class);
			dao.add(student);
			session.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
	}

	public void deleteById(int id) {

	}

	public Student getById(int id) {
		SqlSessionFactory sqlSessionFactory = DBUtil.getSqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		Student student = null;
		try{
			IStudentDAO dao = session.getMapper(IStudentDAO.class);
			student = dao.getById(id);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return student;
	}

	public void update(Student student) {

	}
	
	
	public List<Student> getStudentsByGroupId(int groupId) {
		return null;
	}
	
	/**
	 * 测试selectList，分页的原型
	 * @author king
	 * @return students
	 * @serialData 2011-7-29
	 */
	public List<Student> getAllStudent() {
		SqlSessionFactory sqlSessionFactory = DBUtil.getSqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		List<Student> students = new ArrayList<Student>();
		try{
			RowBounds rb = new RowBounds(1,6);//RowBounds的下标是从0开始，表示第一条记录，此表示从第二条记录开始，取6条记录
			students = session.selectList("com.company.dao.IStudentDAO.getAllStudent", null, rb);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return students;
	}

	public Student many2one(int id) {
		SqlSessionFactory sqlSessionFactory = DBUtil.getSqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		Student student = null;
		try{
			IStudentDAO dao = session.getMapper(IStudentDAO.class);
			student = dao.many2one(id);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return student;
		
	}
	
	public Student getStudentAndGroupUseSelect(int id){
		SqlSessionFactory sqlSessionFactory = DBUtil.getSqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		Student student = null;
		try{
			IStudentDAO dao = session.getMapper(IStudentDAO.class);
			student = dao.getStudentAndGroupUseSelect(id);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return student;
	}
	
	public List<Student> getStudentBySomeCondition(Student student){
		SqlSessionFactory sqlSessionFactory = DBUtil.getSqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		List<Student> students = new ArrayList<Student>();
		try{
			IStudentDAO dao = session.getMapper(IStudentDAO.class);
			students = dao.getStudentBySomeCondition(student);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return students;
	}
	
	public List<Student> getAllUser(){
		SqlSessionFactory sqlSessionFactory = DBUtil.getSqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		List<Student> students = new ArrayList<Student>();
		try{
			Map<String,List<Student>> map = new HashMap<String,List<Student>>();
			session.selectOne("com.company.dao.IStudentDAO.getAllUser", map);
			students =  (List<Student>) map.get("students");
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return students;
	}
	
	public static void main(String[] args) {
		Student student = new Student();
		IStudentDAOImpl impl = new IStudentDAOImpl();
		List<Student> students = impl.getAllStudent();
		for(Student s : students) {
			System.out.println(s);
		}
	}
}

IGroupDAO.java代码如下：

package com.company.dao;

import com.company.entity.Group;

public interface IGroupDAO {
	/**
	 * 增加一个组
	 * @param group
	 */
	public void add(Group group);
	/**
	 * 根据id删除组
	 * @param id
	 */
	public void deleteById(int id);
	
	/**
	 * 此方法是通过id获得一个组的信息，并且获得该组下面的所有的学生信息
	 * @param id
	 * @return
	 */
	public Group getById(int id);
	
	
	/**
	 * 此方法是测试如何设定ResultMap的方式来从数据库中获得Group
	 * @param id
	 * @return
	 */
	public Group getByIdResultMap(int id);
	
	
	public void update(Group group);
	
	
	
}

IGroupDAO.java对应的实现类IGroupDAOImpl.java如下 ：

package com.company.dao.impl;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import com.company.dao.IGroupDAO;
import com.company.entity.Group;
import com.company.entity.Student;
import com.company.util.DBUtil;

public class IGroupDAOImpl implements IGroupDAO{

	public void add(Group group) {
		SqlSession session = DBUtil.getSqlSessionFactory().openSession();
		try{
			IGroupDAO dao = session.getMapper(IGroupDAO.class);
			dao.add(group);
			session.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
	}
	
	public void deleteById(int id) {
		SqlSession session = DBUtil.getSqlSessionFactory().openSession();
		try{
			IGroupDAO dao = session.getMapper(IGroupDAO.class);
			dao.deleteById(id);
			session.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		
	}

	public Group getById(int id) {
		SqlSessionFactory sqlSessionFactory = DBUtil.getSqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		
		Group group = null;
		try{
			IGroupDAO dao = session.getMapper(IGroupDAO.class);
			group = dao.getById(id);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return group;
	}

	public Group getByIdResultMap(int id) {
		SqlSessionFactory sqlSessionFactory = DBUtil.getSqlSessionFactory();
		SqlSession session = sqlSessionFactory.openSession();
		Group group = null;
		try{
			IGroupDAO dao = session.getMapper(IGroupDAO.class);
			group = dao.getByIdResultMap(id);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			session.close();
		}
		return group;
	}
	
	public void update(Group group) {
		// TODO Auto-generated method stub
		
	}

	
	public static void main(String[] args) {
		//System.out.println(new IGroupDAOImpl().getByIdResultMap(1));
		
		/*Group group = new Group();
		group.setId(3);
		group.setName("南京农业2");
		group.setPosition("南京信新街口2");
		new IGroupDAOImpl().add(group);*/
		
		Group group = new IGroupDAOImpl().getById(1);
		
		for(Student s:group.getStudents()){
			System.out.println(s.getId()+" , " + s.getName());
		}
		
	}
}

（转自：http://blog.csdn.net/zhangweiwtmdbf/article/details/6641328）