MyBatis的XML标签
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验,你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格,还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
代码示例
这里就不做过多讲解啦,需要说的都在代码中进行了注释,希望大家仔细阅读。
TeacherMapper.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
| package com.qinjiangbo.dao;
import com.qinjiangbo.pojo.Teacher;
import java.util.List; import java.util.Map;
public interface TeacherMapper {
public void countTeacherNumber(Map map);
public List<Teacher> findTeachersByIds(List<Integer> ids);
public List<Teacher> findTeachersByIds2(int[] ids);
public List<Teacher> findTeacherByNamePrefix(String prefix);
public int updateTeacherInfo(Teacher teacher); }
|
TeacherMapper.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
| <?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.qinjiangbo.dao.TeacherMapper"> <select id="countTeacherNumber" parameterMap="countTeacherNumberMap" statementType="CALLABLE"> CALL mybatis.count_teacher_procedure(?, ?) </select>
<parameterMap type="java.util.Map" id="countTeacherNumberMap"> <parameter property="t_sex" jdbcType="INTEGER" mode="IN"/> <parameter property="user_count" jdbcType="INTEGER" mode="OUT"/> </parameterMap>
<select id="findTeachersByIds" resultMap="teacherInfoMap"> SELECT * FROM teacher WHERE t_id in <foreach collection="list" item="ids" index="index" open="(" close=")" separator=","> #{ids} </foreach> </select>
<select id="findTeachersByIds2" resultMap="teacherInfoMap"> SELECT * FROM teacher WHERE t_id in <foreach collection="array" item="ids" index="index" open="(" close=")" separator=","> #{ids} </foreach> </select>
<select id="findTeacherByNamePrefix" parameterType="java.lang.String" resultMap="teacherInfoMap"> SELECT * FROM teacher <if test="_parameter != null and _parameter != ''"> WHERE t_name LIKE CONCAT(#{prefix}, '%') </if> </select>
<update id="updateTeacherInfo" parameterType="com.qinjiangbo.pojo.Teacher"> UPDATE teacher <set> <if test="name != null and name != ''"> t_name = #{name} </if> <if test="office != null and office != ''"> t_office = #{office} </if> <if test="gender != null and gender != ''"> t_gender = #{gender} </if> </set> WHERE t_id = #{id} </update>
<resultMap id="teacherInfoMap" type="com.qinjiangbo.pojo.Teacher"> <id column="t_id" property="id"/> <result column="t_name" property="name"/> <result column="t_office" property="office"/> <result column="t_gender" property="gender"/> </resultMap> </mapper>
|
测试用例
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
| package com.qinjiangbo.test;
import com.qinjiangbo.dao.TeacherMapper; import com.qinjiangbo.pojo.Teacher; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.After; import org.junit.Before; import org.junit.Test;
import java.io.InputStream; import java.util.ArrayList; import java.util.Iterator; import java.util.List;
public class MyBatisTest6 {
private SqlSession sqlSession = null;
@Before public void init() { String config = "com/qinjiangbo/conf/configure.xml"; InputStream inputStream = MyBatisTest2.class.getClassLoader().getResourceAsStream(config); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); }
@Test public void testFindTeacherByIds() { TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class); List<Integer> ids = new ArrayList<Integer>(); ids.add(1002); ids.add(1003); ids.add(1006); List<Teacher> teachers = teacherMapper.findTeachersByIds(ids); Iterator<Teacher> iterator = teachers.iterator(); while (iterator.hasNext()) { System.out.println(iterator.next()); } }
@Test public void testFindTeacherByIds2() { TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class); int[] ids = new int[]{1002, 1004, 1005}; List<Teacher> teachers = teacherMapper.findTeachersByIds2(ids); Iterator<Teacher> iterator = teachers.iterator(); while (iterator.hasNext()) { System.out.println(iterator.next()); } }
@Test public void testFindTeacherByNamePrefix() { TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class); List<Teacher> teachers = teacherMapper.findTeacherByNamePrefix("s"); Iterator<Teacher> iterator = teachers.iterator(); while (iterator.hasNext()) { System.out.println(iterator.next()); } }
@Test public void testUpdateTeacherInfo() { TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = new Teacher(); teacher.setId(1003); teacher.setName("QinJiangbo"); int result = teacherMapper.updateTeacherInfo(teacher); System.out.println(result); }
@After public void commit() { sqlSession.commit(); } }
|
以上代码均通过严格的测试,大家可以根据自己的代码再跑一遍。