一、用到的实体类如下:
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 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);
*/
}
}