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）
声明：该文观点仅代表作者本人，牛骨文系教育信息发布平台，牛骨文仅提供信息存储空间服务。
- 上一篇： Mybatis关联查询一对多使用插件分页返回数据不对
- 下一篇： 前端项目添加自定义icon图标步骤
