MyBatis增删改查、分页、一对一、一对多

一、用到的实体类如下：

Student.java

[html]  view plain copy

1. package com.company.entity;  
2.   
3. import java.io.Serializable;  
4. import java.util.Date;  
5.   
6. public class Student implements Serializable{  
7.       
8.     private static final long serialVersionUID = 1L;  
9.     private int id;  
10.     private String name;  
11.     private Date birth;  
12.     private Group group;  
13.       
14.       
15.     public Group getGroup() {  
16.         return group;  
17.     }  
18.     public void setGroup(Group group) {  
19.         this.group = group;  
20.     }  
21.     public int getId() {  
22.         return id;  
23.     }  
24.     public void setId(int id) {  
25.         this.id = id;  
26.     }  
27.     public String getName() {  
28.         return name;  
29.     }  
30.     public void setName(String name) {  
31.         this.name = name;  
32.     }  
33.     public Date getBirth() {  
34.         return birth;  
35.     }  
36.     public void setBirth(Date birth) {  
37.         this.birth = birth;  
38.     }  
39.     @Override  
40.     public String toString() {  
41.         return "Student [birth=" + birth + ", group=" + group + ", id=" + id  
42.                 + ", name=" + name + "]";  
43.     }  
44.       
45.       
46. }  

Group.java

[java]  view plain copy

1. package com.company.entity;  
2.   
3. import java.util.List;  
4.   
5. public class Group {  
6.     private int id;  
7.     private String name;  
8.     private String position;  
9.     private List<Student> students;  
10.       
11.       
12.     public List<Student> getStudents() {  
13.         return students;  
14.     }  
15.     public void setStudents(List<Student> students) {  
16.         this.students = students;  
17.     }  
18.     public int getId() {  
19.         return id;  
20.     }  
21.     public void setId(int id) {  
22.         this.id = id;  
23.     }  
24.     public String getName() {  
25.         return name;  
26.     }  
27.     public void setName(String name) {  
28.         this.name = name;  
29.     }  
30.     public String getPosition() {  
31.         return position;  
32.     }  
33.     public void setPosition(String position) {  
34.         this.position = position;  
35.     }  
36.     @Override  
37.     public String toString() {  
38.         return "Group [id=" + id + ", name=" + name + ", position=" + position  
39.                 + "]";  
40.     }  
41.       
42. }  

 

二、实体对应的表结构

student表:

create table t_student(

id  int primary key,

name varchar2(20),

birth date,

group_id int references g_group(g_id));

 

g_group表:

create  table t_group(

id int primary key,

name varchar2(20),

position varchar2(30));

 

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

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

 

studentMapper.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="cn.test.dao.StudentDao">
	<!-- MyBatis缓存 -->
	<cache eviction="LRU" flushInterval="600000" size="1024"
		readOnly="false" />

	<!-- sql标签用来定义一些可以被重用的sql语句或字段或片段等 -->
	<sql id="studentColumns">select id,name,birth from t_student</sql>

	<!-- MyBatis向student表中插入一条数据 -->  
    <insert id="add" parameterType="Student">  
        insert into t_student(name,birth,group_id) values(#{name},#{birth},#{group.id})  
    </insert>  
    
    <!-- 根据Id删除学生信息 -->
    <delete id="deleteById" parameterType="int">
    	delete from t_student where id=#{id}
    </delete>
    
    <!-- 根据id获得学生的信息 -->  
    <select id="getById" parameterType="int" resultType="Student">  
        <include refid="studentColumns"/> where id=#{id}  
    </select>
	
	<!-- 此处的实现方法是一个分页的原型,请查看StudentDAOImpl.java中的调用方法 -->  
    <select id="getAllStudent" resultMap="studentMap">  
   		<!--此处是引用了上面预定义好的sql语句-->  
        <include refid="studentColumns"/> order by id
    </select> 
	
	<!-- 此处获得多对一的关系 ,但就单条记录而言却是一对一的关系，所以一对一的写法跟此相同 -->
	<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="position" property="position" />
		</association>
	</resultMap>
	<select id="many2one" resultMap="getStudentAndGroup" parameterType="int">
		select s.id,s.name,s.birth,s.group_id,g.id as g_id,g.name as g_name,g.position
		from t_student s
		left join t_group g on s.group_id = 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 t_student   
        where id = #{id}  
    </select>  
    <!-- 此处实用缓存 -->
    <select id="selectGroup" resultType="Group" parameterType="int" flushCache="false" useCache="true">  
        select *   
        from t_group   
        where id = #{id}  
    </select>  
	
	<!-- 动态sql语句 的测试dynamic sql-->      
    <select id="getStudentBySomeCondition" parameterType="Student" resultType="Student">  
        select *  
        from t_student s  
        <where>  
            <if test="id != 0">  
                and s.id=#{id}  
            </if>  
            <if test="name != null">  
                and s.name like '%${name}%'  
            </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>  
	
	
</mapper>

groupMapper.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="cn.test.dao.GroupDao">

	<!-- 列名与属性名不一致时 -->
	<resultMap type="Group" id="groupResultMap">
		<result column="id" property="id"/>
		<result column="name" property="name"/>
		<result column="position" property="position"/>
	</resultMap>

	<!-- 获得group组以及组中所有Student成员，相当于one2many -->
	<resultMap type="Group" id="GorupAndStudentsMap">
		<result column="id" property="id"/>
		<result column="name" property="name"/>
		<result column="position" property="position"/>
		<!-- 注意此处的group_id是student表的外键 -->
		<collection property="students" column="group_id" ofType="Student">
			<result column="id" property="id"/>
			<result column="name" property="name"/>
			<result column="birth" property="birth"/>
		</collection>
		
		
	</resultMap>
	
	<insert id="addGroup" parameterType="Group">
		insert into t_group(name,position) values(#{name},#{position})
	</insert>
	
	<delete id="delGroupById" parameterType="int">
		delete from t_group where id=#{id}
	</delete>
	
	<!-- 只能得到Group不能得到Student
	<select id="getGroupById" parameterType="int" resultType="Group">  
        select id,name,position from t_group where id=#{id}  
    </select>
	-->
	
	<select id="getGroupById" parameterType="int" resultMap="GorupAndStudentsMap">
		select g.id,g.name,g.position,s.id,s.name,s.birth,s.group_id 
		from t_group g 
		left join t_student s on g.id=s.group_id 
		where g.id=#{id}
	</select>
	
	<select id="getByIdResultMap" parameterType="int" resultMap="groupResultMap">
		select id,name,position from t_group where id=#{id}
	</select>
	
	<update id="updateGroup" parameterType="Group">
		update t_group set name=#{name},position=#{position} where id=#{id}
	</update>
</mapper>

 

四、接口StudentDAO.java和GroupDAO.java中定义了StudentDAOImpl.java和GroupDAOImpl.java中需要实现的方法

StudentDAO.java

package cn.test.dao;

import java.util.List;

import cn.test.entity.Student;

public interface StudentDao {
	 /** 
     * 增加一个学生 
     * @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);  
	
}

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

package cn.test.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

import cn.test.dao.GroupDao;
import cn.test.dao.StudentDao;
import cn.test.entity.Group;
import cn.test.entity.Student;
import cn.test.utils.MyBatisUtils;

public class StudentDaoImpl implements StudentDao {

	@Override
	public void add(Student student) {
		SqlSession session = MyBatisUtils.getSession();
		try {
			StudentDao sd = session.getMapper(StudentDao.class);
			sd.add(student);
			session.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			MyBatisUtils.closeSession();
		}
	}

	@Override
	public void deleteById(int id) {
		SqlSession session = MyBatisUtils.getSession();
		try {
			StudentDao sd = session.getMapper(StudentDao.class);
			sd.deleteById(id);
			session.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			MyBatisUtils.closeSession();
		}
	}

	@Override
	public Student getById(int id) {
		SqlSession session = MyBatisUtils.getSession();
		Student stu = new Student();
		try {
			StudentDao sd = session.getMapper(StudentDao.class);
			stu = sd.getById(id);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			MyBatisUtils.closeSession();
		}
		return stu;
	}

	@Override
	public void update(Student student) {
		// TODO Auto-generated method stub

	}

	@Override
	public List<Student> getAllStudent() {
		SqlSession session = MyBatisUtils.getSession();
		List<Student> stus = new ArrayList<Student>();
		try {
			//RowBounds的下标是从0开始，表示第一条记录，此表示从第2条记录开始，取4条记录
			RowBounds rb = new RowBounds(1, 4);
			stus = session.selectList("cn.test.dao.StudentDao.getAllStudent", null, rb);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			MyBatisUtils.closeSession();
		}
		return stus;
	}

	@Override
	public Student many2one(int id) {
		SqlSession session = MyBatisUtils.getSession();
		Student stu = new Student();
		try {
			StudentDao sd= session.getMapper(StudentDao.class);
			stu = sd.many2one(2);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			MyBatisUtils.closeSession();
		}
		return stu;
	}

	@Override
	public Student getStudentAndGroupUseSelect(int id) {
		// 测试association 的 select 属性，发送两条SQL效率较低
		return null;
	}

	@Override
	public List<Student> getStudentBySomeCondition(Student student) {
		SqlSession session = MyBatisUtils.getSession();
		List<Student> stus = new ArrayList<Student>();
		try {
			StudentDao sd = session.getMapper(StudentDao.class);
			stus = sd.getStudentBySomeCondition(student);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			MyBatisUtils.closeSession();
		}
		return stus;
	}
	
	public static void main(String[] args) {
		StudentDao sd = new StudentDaoImpl();
		GroupDao gd = new GroupDaoImpl();
		
		/*测试添加学生
		Student stu = new Student();
		Group group = gd.getByIdResultMap(1);
		stu.setName("OK");
		stu.setBirth(new Date());
		stu.setGroup(group);
		sd.add(stu);
		*/
		
		/*根据id删除学生信息
		sd.deleteById(1);
		*/
		
		/*根据id查询学生信息
		Student stu = sd.getById(2);
		System.out.println(stu.getName()+"--"+stu.getBirth().toLocaleString());
		*/
		
		/*分页查询所有学生信息
		List<Student> stus = sd.getAllStudent();
		for (Student student : stus) {
			System.out.println(student.getId()+"-->"+student.getName()+"-->"+student.getBirth().toLocaleString());
		}
		*/
		
		/*根据id查询学生信息与分组信息
		Student stu = sd.many2one(2);
		System.out.println(stu.getName()+"--"+stu.getBirth().toLocaleString()+"-->"+stu.getGroup().getName());
		*/
		
		/*动态SQL*/
		Student s = new Student();
		s.setId(5);
		s.setName("了");
		List<Student> stus = sd.getStudentBySomeCondition(s);
		for (Student student : stus) {
			System.out.println(student.getId()+"-->"+student.getName()+"-->"+student.getBirth().toLocaleString());
		}
		
	}
}

 

GroupDAO.java代码如下：

package cn.test.dao;

import cn.test.entity.Group;

public interface GroupDao {
	/** 
     * 增加一个组 
     * @param group 
     */  
    public void addGroup(Group group);  
    /** 
     * 根据id删除组 
     * @param id 
     */  
    public void delGroupById(int id);  
      
    /** 
     * 通过id获得一个组的信息，并且获得该组下面的所有的学生信息 
     * @param id 
     * @return 
     */  
    public Group getGroupById(int id);  
      
      
    /** 
     * 测试如何设定ResultMap的方式来从数据库中获得Group 
     * @param id 
     * @return 
     */  
    public Group getByIdResultMap(int id);  
      
    /**
     * 更改分组信息
     * @param group
     */
    public void updateGroup(Group group);  
}

GroupDAO.java对应的实现类GroupDAOImpl.java如下 ：

package cn.test.dao.impl;

import org.apache.ibatis.session.SqlSession;

import cn.test.dao.GroupDao;
import cn.test.entity.Group;
import cn.test.utils.MyBatisUtils;

public class GroupDaoImpl implements GroupDao {

	@Override
	public void addGroup(Group group) {
		SqlSession session = MyBatisUtils.getSession();
		try {
			GroupDao gd = session.getMapper(GroupDao.class);
			gd.addGroup(group);
			session.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyBatisUtils.closeSession();
		}
	}

	@Override
	public void delGroupById(int id) {
		SqlSession session = MyBatisUtils.getSession();
		try {
			GroupDao gd = session.getMapper(GroupDao.class);
			gd.delGroupById(id);
			session.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyBatisUtils.closeSession();
		}
	}

	@Override
	public Group getGroupById(int id) {
		SqlSession session = MyBatisUtils.getSession();
		Group g = new Group();
		try {
			GroupDao gd = session.getMapper(GroupDao.class);
			g = gd.getGroupById(id);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyBatisUtils.closeSession();
		}
		return g;
	}

	@Override
	public Group getByIdResultMap(int id) {
		SqlSession session = MyBatisUtils.getSession();
		Group g = null;
		try {
			GroupDao gd = session.getMapper(GroupDao.class);
			g = gd.getByIdResultMap(id);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyBatisUtils.closeSession();
		}
		return g;
	}

	@Override
	public void updateGroup(Group group) {
		SqlSession session = MyBatisUtils.getSession();
		Group g = null;
		try {
			GroupDao gd = session.getMapper(GroupDao.class);
			gd.updateGroup(group);
			session.commit();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyBatisUtils.closeSession();
		}
	}

	public static void main(String[] args) {
		GroupDao gd = new GroupDaoImpl();

		/*
		 * 测试添加方法 Group g = new Group(); 
		 * g.setName("第一组"); 
		 * g.setPosition("没有");
		 * gd.addGroup(g);
		 */

		/*
		 测试删除方法
		 gd.delGroupById(3);
		 */

		/* 测试根据id查找group与student 
		Group g = gd.getGroupById(1);
		System.out.println(g.getName() + "-->" + g.getPosition() + "-->"
				+ g.getStudents().get(0).getId());
		*/
		
		
		/*resultMap查找group
		Group g = gd.getByIdResultMap(1);
		System.out.println(g.getName() + "-->" + g.getPosition());
		*/
		
		/*updateGroup更新
		Group group = new Group();
		group.setId(2);
		group.setName("第二组");
		group.setPosition("二");
		gd.updateGroup(group);
		*/
	}
}