Mybatis 映射器

新建一个JAVA项目，加入对应的架包，配置Mybatis的核心配置文件、日志文件种数据库属性文件（在之前写的博客中有）。

新建实体类Student和Teacher，如果你提供有参构造方法，必须把无参构造方法写出来

package com.bean; import java.util.List; /** * 老师实体类，负责与数据库中的Teacher表做映射 * @author 1 * */ public class Teacher { private Integer tid; private String tName; private String subject; private List<student> students; //一个老师对应多个学生 //get set方法自己生成 } </student>

package com.bean; /** * 学生实体类，负责与数据库中的Student表做映射 * @author 1 * */ public class Student{ private Integer id; private String name; private String address; private Teacher teacher; //老师 一对学生对应一个老师 //get set方法自己生成 }

使用单例设计模式取得SqlSessionFactory

package com.until; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; /** * 使用单例设计模式取得SqlSession对象 * @author 1 * */ public class SqlSessionFactoryUtil { private static SqlSessionFactory sqlSessionFactory = null; //SqlSessionFactory对象 private static final Class CLASS_LOCK = SqlSessionFactoryUtil.class; //类线程锁 //构造方法私有化 private SqlSessionFactoryUtil() {} /** * 让调用方法取得SqlSessionFactory对象 * @return */ public static SqlSessionFactory initSqlSessionFactory(){ //防止多线程产生多个不同的SqlSessionFactory对象 synchronized (CLASS_LOCK) { if (sqlSessionFactory == null) { Reader reader = null ; try { reader = Resources.getResourceAsReader("mybatis_config.xml"); } catch (Exception e) { } sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } } return sqlSessionFactory; } /** * 让调用方法取得SqlSession对象 * @return */ public static SqlSession openSqlSession(){ if (sqlSessionFactory == null) { initSqlSessionFactory(); } return sqlSessionFactory.openSession(); } }

对数据库的最基本操作（增 删 改 查）

创建IstudetDao接口

package com.idao; import org.apache.ibatis.annotations.Param; import com.bean.Student; public interface IStudentDao { /** * 通过Id查询学生 * @param id * @return */ public Student getStudentById(int id); /** * 添加一个学生 * @param student * @return 返回成功添加的记录数 */ public int addStudent(Student student); /** * 通过学生的的姓与老师的id来删除学生数据 *@param id 要删除的Id * @return 返回成功删除的记录数 */ public int deleteStudentById(int id); /** * 通过Id来更新学生的姓名（真正程序不会这样使用，这里是为了做了一个多参数的例子） * @param id 要更新学生的ID * @param name 更新后的名字 * @return 更新成功的记录数 */ public int updateStudentNameById(@Param("id")int id , @Param("name")String name); }

配置Student.xml

<?xml version="1.0" encoding="UTF-8" ?> <mapper namespace="com.idao.IStudentDao"> <!-- select 这个标签标记用来查询数据 id 接口中对应方法的名称 parameterType 方法的参数类型 resultType 把查询到的结果封装到那个JavaBean，默认是表中字段与类中字段命名一致 可以使用别名（别名为类中的属性字段,因为我的JavaBean中字段与表字段 一致，为了说明这种使用，别名我也命名一样），不能与resultMap一起使用 --> <select id="getStudentById" parametertype="int" resulttype="com.bean.Student"> select id id, name, address from student where id = #{id} </select> <!-- insert 这个标签标记用来添加数据 默认反回的成功添加多少条记录 id 接口中对应方法的名称 parameterType 这里可以不指定，使用JavaBean映射传入参数 --> <insert id="addStudent" parametertype="com.bean.Student"> insert into student (id , name , address) values (#{id},#{name},#{address}) </insert> <!-- insert 这个标签标记用来删除数据 默认反回的成功删除多少条记录 id 接口中对应方法的名称 parameterType 因为我们是根据ID删除的，而Id的类型是Int --> <delete id="deleteStudentById" parametertype="int"> delete from student where id = #{id} </delete> <!-- insert 这个标签标记用来删除数据 默认反回的成功删除多少条记录 id 接口中对应方法的名称 parameterType 多个参数，不指定参数类型 --> <update id="updateStudentNameById"> update student set name = #{name} where id = #{id} </update> </mapper>

为IstudetDao接口创建单元测试

package com.test; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import com.bean.Student; import com.idao.IStudentDao; import com.until.SqlSessionFactoryUtil; public class IStudentDaoTest { @Test public void testGetStudentById() { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); Student student = studentDao.getStudentById(8); System.out.println(student); } finally { if (session != null) { session.close(); } } } @Test public void testAddStudent() { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); Student student = new Student(); student.setAddress("testAddStudent"); student.setId(1); student.setName("test"); int num = studentDao.addStudent(student); session.commit(); System.out.println(num); } finally { if (session != null) { session.close(); } } } @Test public void testDeleteStudentById() { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); int num = studentDao.deleteStudentById(1); System.out.println(num); session.commit(); } finally { if (session != null) { session.close(); } } } @Test public void testUpdateStudentNameById() { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); int num = studentDao.updateStudentNameById(2, "张菲"); session.commit(); System.out.println(num); } finally { if (session != null) { session.close(); } } } }

级联操作

向IstudetDao接口中添加以下方法

/** * 通过老师tid来查询学生 * @param tId 老师的ID * @return */ public List<student> findByTid(int tId); /** * 实现多表查询 * @return */ public List<student> selectAll(); /** * 实现多表查询 * 使用 了association * @return */ public List<student> selectAll2(); </student></student></student>

向Student.xml中添加对应的配置

<!-- 通过老师ID来查询学生 --> <select id="findByTid" parametertype="int" resulttype="com.bean.Student"> select id, name, address from student where tId = #{tId} </select> <!-- 级联查询--> <!-- 进行resultMap的封装 不使用association id 为标记符号，给resultMap引用 type 对应的JAVA实体类 --> <resultmap id="student_teacher" type="com.bean.Student"> <!-- id 表中主键那列，只能有一个 property 为对应JAVA实体类的属性名 column 表中列 result 除了主键外的其它列 --> <id column="id" property="id"> <result column="name" property="name"> <result column="address" property="address"> <!-- teacher.tId teacher是com.bean.Student中Teacher类私有对象 tId是Teacher私有属性 --> <result column="tId" property="teacher.tId"> <result column="tName" property="teacher.tName"> <result column="subject" property="teacher.subject"> </result></result></result></result></result></id></resultmap> <!-- resultMap的内容是对应resultMap的id --> <select id="selectAll" resultmap="student_teacher"> select s.id id ,s.name name ,s.address address ,t.tId tId,t.tName tName,t.subject subject from student s , teacher t where s.tId = t.tId </select> <!-- 多表查询 使用了association 同时还可以进行分步查询--> <!-- 进行resultMap的封装 --> <resultmap id="student_teacher2" type="com.bean.Student"> <id column="id" property="id"> <result column="name" property="name"> <result column="address" property="address"> <!-- 把这个查询过程交com.dao.ITheacherDao命名空间中getTeacherById 即com.dao.ITheacherDao类中getTeacherById方法 --> <association property="teacher" column="tId" select="com.idao.ITheacherDao.getTeacherById"> </association> </result></result></id></resultmap> <select id="selectAll2" resultmap="student_teacher2"> select s.id id ,s.name name ,s.address address ,t.tId tId,t.tName tName,t.subject subject from student s , teacher t where s.tId = t.tId </select>

向IStudentDaoTest测试类中添加相应的测试方法

@Test public void testSelectAll() { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); List<student> list = studentDao.selectAll(); System.out.println(list.get(0)); } finally { if (session != null) { session.close(); } } } @Test public void testSelectAll2() { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); List<student> list = studentDao.selectAll2(); System.out.println(list.get(0)); } finally { if (session != null) { session.close(); } } } </student></student>

添加Teacher.xml（要注册到Mybatis的配置文件中）

<?xml version="1.0" encoding="UTF-8"?> <mapper namespace="com.idao.ITheacherDao"> <select id="getTeacherById" parametertype="int" resulttype="com.bean.Teacher"> select * from teacher where tId = #{tId} </select> <!-- 一对多级联 使用collection --> <resultmap type="com.bean.Teacher" id="teacher_student"> <id property="tid" column="tid"> <result property="tName" column="tName"> <result property="subject" column="subject"> <collection property="students" column="tid" select="com.idao.IStudentDao.findByTid"> </collection></result></result></id></resultmap> <select id="getTeacherAndStudentsById" parametertype="int" resultmap="teacher_student"> select tid , tName ,subject from teacher where tId = #{tid} </select> </mapper>

添加ITeacherDao接口

package com.idao; import com.bean.Teacher; public interface ITheacherDao { /** * 通过Id查询教师，不级联查询他的学生 * @param id 要查询的Id * @return */ public Teacher getTeacherById(Integer tid); /** * 通过Id查询教师，级联查询他的学生 * @param id 要查询的Id * @return */ public Teacher getTeacherAndStudentsById(Integer tId); }

为ITeacherDao接口创建单元测试

package com.test; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import com.bean.Student; import com.idao.ITheacherDao; import com.until.SqlSessionFactoryUtil; public class ITheacherDaoTest { @Test public void testGetTeacherById() { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); ITheacherDao theacherDao = session.getMapper(ITheacherDao.class); System.out.println(theacherDao.getTeacherById(1).getSubject()); } finally { if (session != null) { session.close(); } } } @Test public void testGetTeacherAndStudentsById() { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); ITheacherDao theacherDao = session.getMapper(ITheacherDao.class); List<student> list = theacherDao.getTeacherAndStudentsById(2).getStudents(); System.out.println(list.size()); } finally { if (session != null) { session.close(); } } } } </student>

动态SQL

向IstudetDao接口中添加以下方法

/** * 动态SQL语句 * SQL .... if * @param student * @return */ public List<student> queryByif(Student student); /** * 动态SQL语句 * SQL .... Choose * @param student * @return */ public List<student> queryByChoose(Student student); /** * 动态SQL * SQL .... Trim .. if * @param student * @return */ public List<student> queryByTrim(Student student); /** * 动态SQL * SQL .... Where .. if * @param student * @return */ public List<student> queryByWhere(Student student) ; /** * 动态SQL * SQL... set.... if * @param student * @return */ public int updateStudet(Student student); /** * 批量删除 * @param list * @return */ public Integer deleteByList(List<integer> list); public Integer deleteByArray(Integer [] arr); </integer></student></student></student></student>

向Student.xml中添加对应的配置

<!-- 动态SQL --> <!-- if --> <!-- 如果传入的对象中name属性不为空，则通过name进行查询 --> <select id="queryByif" resulttype="com.bean.Student"> select * from student where 1 = 1 <if test="name != null and name !=''"> and name like '${name}%' </if> </select> <!-- choose --> <!-- 想当 switch...case...default 只有一个匹配就不会往下运行 --> <select id="queryByChoose" resulttype="com.bean.Student"> select * from student where 1 = 1 <choose> <when test="name != null and name !=''"> and name like '${name}%' </when> <when test="address !=null and address !=''"> and address like '${address}%' </when> <otherwise> order by name </otherwise> </choose> </select> <!-- 解决 where 1 = 1 --> <select id="queryByTrim" resulttype="com.bean.Student"> select * from student <trim prefix="where" prefixoverrides="AND | OR"> <if test="name != null"> and name like '${name}%' </if> <if test="address !=null"> and address like '${address}%' </if> </trim> </select> <!-- 解决 where 1 = 1 --> <select id="queryByWhere" resulttype="com.bean.Student"> select * from student <where> <if test="name != null"> and name like '${name}%' </if> <if test="address !=null"> and address like '${address}%' </if> </where> </select> <update id="updateStudet"> update student <set> <if test="name != null"> name = #{name} </if> <if test="address !=null"> address = #{address} </if> </set> where id = #{id} </update> <!-- 对集合进行循环 --> <delete id="deleteByList"> delete from student where id in <foreach collection="list" open="(" separator="," close=")" item="id"> #{id} </foreach> </delete> <!-- 对集合进行循环 --> <delete id="deleteByArray"> delete from student where id in <foreach collection="array" open="(" separator="," close=")" item="id"> #{id} </foreach> </delete>

向IStudentDaoTest测试类中添加相应的测试方法

@Test public void testQueryByif() throws IOException { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); Student student = new Student(); student.setName("李"); List<student> list = studentDao.queryByif(student); System.out.println(list.size()); } finally { if (session != null) { session.close(); } } } @Test public void testQueryByChoose() throws IOException { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); Student student = new Student(); // student.setName("李"); // student.setAddress("22"); List<student> list = studentDao.queryByChoose(student); System.out.println(list.size()); } finally { if (session != null) { session.close(); } } } @Test public void testQueryByTrim() throws IOException { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); Student student = new Student(); student.setName("李"); student.setAddress("22"); List<student> list = studentDao.queryByTrim(student); System.out.println(list.size()); } finally { if (session != null) { session.close(); } } } @Test public void testQueryByWhere() throws IOException { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); Student student = new Student(); student.setName("李"); student.setAddress("22"); List<student> list = studentDao.queryByWhere(student); System.out.println(list.size()); } finally { if (session != null) { session.close(); } } } @Test public void testUpdateStudet() throws IOException { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); Student student = new Student(); student.setId(40); student.setName("李"); System.out.println(studentDao.updateStudet(student)); session.commit(); } finally { if (session != null) { session.close(); } } } @Test public void testDeleteByList() throws IOException { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); List<integer> list = new ArrayList<>(); for (int i = 33; i < 40; i++) { list.add(i); } System.out.println(list.size()); int co = studentDao.deleteByList(list); session.commit(); System.out.println(co); } finally { if (session != null) { session.close(); } } } @Test public void testDeleteByArray() throws IOException { SqlSession session = null; try { session = SqlSessionFactoryUtil.openSqlSession(); IStudentDao studentDao = session.getMapper(IStudentDao.class); Integer[] ids = { 31, 32, 33 }; int co = studentDao.deleteByArray(ids); session.commit(); System.out.println(co); } finally { if (session != null) { session.close(); } } } </integer></student></student></student></student>