MyBatis框架（三）动态SQL，分页，二进制存入数据库图片
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MyBatis框架（三）动态SQL，分页，二进制存入数据库图片，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含13672字，纯文字阅读大概需要20分钟。
内容图文
1, <if>条件
<if test="key!=null">
拼接sql语句
</if>
2, <choose><when><otherwise>
注意：只能执行一个分支
<choose>
<when test="key==‘value‘">
拼接sql语句
</when>
<when test="key==‘value‘">
拼接sql语句
</when>
<otherwise>
前两者都不符合时执行
</otherwise>
</choose>
3, <where>
自动添加where关键字
如果where子句第一句中有 or 或者 and 则删除第一个
4, <trim>
功能与<where>类似, 并且提供了前缀, 后缀的添加, 更加灵活
5, <foreach>
用来遍历传入的集合参数
item(定义集合中每个对象的名字),
collection(集合的对象的名字),
open(定义开始的字符),
close(定义结束的字符),
separator(定义分割的字符)
index(定义元素的索引)
6, <set>
主要用于update
自动加上set关键字
自动剔除最后一个 ","
7, <sql>
经常用于一些常用或者固定的语句, 在外面定义一个语句, 在各种标签中引入
使用include, 相当于直接写在上面
8, <selectKey>
用于不支持自增长主键的数据库, 尽量避免写这个东西
符号：
< < 小于号
> > 大于号
& & 和
' ’ 单引号
" " 双引号
<![CDATA[]]>
例子：
model：
1 package model; 2 3 import java.util.Date; 4 5 public class Emp { 6 private Integer empno; 7 private String ename; 8 private String job; 9 private Integer mgr; 10 private Date hiredate; 11 private Integer sal; 12 private Integer comm; 13 private Integer deptno; 14 private Integer sex; 15 private Dept dept; 16 public Emp() { 17 super(); 18 // TODO Auto-generated constructor stub 19 } 20 public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Integer sal, Integer comm, Integer deptno, Integer sex, 21 Dept dept) { 22 super(); 23 this.empno = empno; 24 this.ename = ename; 25 this.job = job; 26 this.mgr = mgr; 27 this.hiredate = hiredate; 28 this.sal = sal; 29 this.comm = comm; 30 this.deptno = deptno; 31 this.sex = sex; 32 this.dept = dept; 33 } 34 public Integer getEmpno() { 35 return empno; 36 } 37 public void setEmpno(Integer empno) { 38 this.empno = empno; 39 } 40 public String getEname() { 41 return ename; 42 } 43 public void setEname(String ename) { 44 this.ename = ename; 45 } 46 public String getJob() { 47 return job; 48 } 49 public void setJob(String job) { 50 this.job = job; 51 } 52 public Integer getMgr() { 53 return mgr; 54 } 55 public void setMgr(Integer mgr) { 56 this.mgr = mgr; 57 } 58 public Date getHiredate() { 59 return hiredate; 60 } 61 public void setHiredate(Date hiredate) { 62 this.hiredate = hiredate; 63 } 64 public Integer getSal() { 65 return sal; 66 } 67 public void setSal(Integer sal) { 68 this.sal = sal; 69 } 70 public Integer getComm() { 71 return comm; 72 } 73 public void setComm(Integer comm) { 74 this.comm = comm; 75 } 76 public Integer getDeptno() { 77 return deptno; 78 } 79 public void setDeptno(Integer deptno) { 80 this.deptno = deptno; 81 } 82 public Integer getSex() { 83 return sex; 84 } 85 public void setSex(Integer sex) { 86 this.sex = sex; 87 } 88 public Dept getDept() { 89 return dept; 90 } 91 public void setDept(Dept dept) { 92 this.dept = dept; 93 } 94 @Override 95 public String toString() { 96 return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate 97 + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + ", sex=" + sex + ", dept=" + dept + "]"; 98 } 99 100 101 102 }
1 package model; 2 3 public class PageBean { 4 private int page; 5 private int rows; 6 private int firstRow; 7 private int maxRow; 8 9 public PageBean() { 10 this.page = 1; 11 this.rows = 10; 12 this.firstRow = (page-1)*rows; 13 this.maxRow = page*rows; 14 } 15 16 public PageBean(int page, int rows) { 17 this.page = page; 18 this.rows = rows; 19 this.firstRow = (page-1)*rows; 20 this.maxRow = page*rows; 21 } 22 23 public int getPage() { 24 return page; 25 } 26 27 public void setPage(int page) { 28 this.page = page; 29 } 30 31 public int getRows() { 32 return rows; 33 } 34 35 public void setRows(int rows) { 36 this.rows = rows; 37 } 38 39 public int getFirstRow() { 40 return firstRow; 41 } 42 43 public void setFirstRow(int firstRow) { 44 this.firstRow = firstRow; 45 } 46 47 public int getMaxRow() { 48 return maxRow; 49 } 50 51 public void setMaxRow(int maxRow) { 52 this.maxRow = maxRow; 53 } 54 55 }
mapper：
1 package mapper; 2 3 import java.util.List; 4 import java.util.Map; 5 6 import org.apache.ibatis.session.RowBounds; 7 8 import model.Emp; 9 import model.PageBean; 10 11 public interface EmpMapper { 12 List<Emp> selectEmpByMapParam(Map<String,Object> map); 13 List<Emp> selectEmpByChoose(Map<String ,Object> map); 14 List<Emp> selectEmpByWhere(Map<String ,Object> map); 15 List<Emp> selectEmpByTrim(Map<String ,Object> map); 16 List<Emp> selectEmpByForeachMap(Map<String ,Object> map); 17 List<Emp> selectEmpByForeachList(List<Integer> idlist); 18 List<Emp> selectEmpByForeachInteger(Integer[] array); 19 int updateEmpBySet(Emp e); 20 int insertEmp(Emp e); 21 List<Emp> selectEmp(RowBounds rbs); 22 List<Emp> selectEmpByPage(PageBean pg); 23 }
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5 <mapper namespace="mapper.EmpMapper"> 6 7 <select id="selectEmp" parameterType="Map" resultType="Emp"> 8 select * from p_emp e 9 </select> 10 11 <select id="selectEmpByMapParam" parameterType="Map" resultType="Emp"> 12 select * from p_emp e 13 <if test="sex!=null"> 14 where e.sex=#{sex} 15 </if> 16 </select> 17 18 <select id="selectEmpByChoose" parameterType="Map" resultType="Emp"> 19 select * from p_Emp e where 1=1 20 <choose> 21 <when test="ename!=null"> 22 and e.ename like #{ename} 23 </when> 24 <when test="job!=null"> 25 and e.job like #{job} 26 </when> 27 <otherwise> 28 and e.ssex = ‘1‘ 29 </otherwise> 30 </choose> 31 </select> 32 33 <select id="selectEmpByWhere" parameterType="Map" resultType="Emp"> 34 select * from p_emp e 35 <where> 36 <if test="ename!=null"> 37 and e.ename like #{ename} 38 </if> 39 <if test="job!=null"> 40 and e.job like #{job} 41 </if> 42 </where> 43 </select> 44 45 <select id="selectEmpByTrim" parameterType="Map" resultType="Emp"> 46 select * from p_Emp e 47 <trim prefix="where" prefixOverrides="and|or"> 48 <if test="ename!=null"> 49 and e.ename like #{ename} 50 </if> 51 <if test="job!=null"> 52 and e.job like #{job} 53 </if> 54 </trim> 55 </select> 56 57 <select id="selectEmpByForeachMap" resultType="Emp"> 58 select * from p_Emp e where e.empno in 59 <foreach collection="idList" item="aaa" open="(" close=")" separator=","> 60 #{aaa} 61 </foreach> 62 </select> 63 <select id="selectEmpByForeachList" resultType="Emp"> 64 select * from p_Emp e where e.empno in 65 <foreach collection="list" item="aaa" open="(" close=")" separator=","> 66 #{aaa} 67 </foreach> 68 </select> 69 <select id="selectEmpByForeachInteger" resultType="Emp"> 70 select * from p_Emp e where e.empno in 71 <foreach collection="array" item="aaa" open="(" close=")" separator=","> 72 #{aaa} 73 </foreach> 74 </select> 75 76 <update id="updateEmpBySet" parameterType="Emp"> 77 update p_Emp e 78 <set> 79 <if test="ename!=null"> 80 e.ename=#{ename}, 81 </if> 82 <if test="job!=null"> 83 e.job=#{job}, 84 </if> 85 <if test="mgr!=null"> 86 e.mgr=#{mgr}, 87 </if> 88 <if test="hiredate!=null"> 89 e.hiredate=#{hiredate}, 90 </if> 91 <if test="sal!=null"> 92 e.sal=#{sal} 93 </if> 94 </set> 95 where e.empno=#{empno} 96 </update> 97 98 <sql id="pageSqlPre"> 99 SELECT * FROM ( 100 </sql> 101 <sql id="pageSqlSuf"> 102 WHERE ROWNUM <![CDATA[<=]]> 9) r WHERE r.rnum>6 103 </sql> 104 105 <select id="selectEmpBySql" resultType="Emp"> 106 <include refid="pageSqlPre"></include> 107 SELECT e.*, ROWNUM rnum FROM p_Emp e 108 <include refid="pageSqlSuf"></include> 109 </select> 110 111 <!-- 不建议使用 112 <selectKey keyProperty="empno" order="BEFORE" resultType="int"> 113 select test1.nextval from dual 114 </selectKey> --> 115 116 <sql id="pagePre"> 117 SELECT * FROM ( 118 </sql> 119 <sql id="pageSuf"> 120 WHERE ROWNUM <![CDATA[<=]]> #{maxRow}) r 121 WHERE r.rnum > #{firstRow} 122 </sql> 123 124 <select id="selectEmpByPage" resultType="Emp" parameterType="PageBean"> 125 <include refid="pagePre"></include> 126 select e.*, rownum rnum from p_emp e 127 <include refid="pageSuf"></include> 128 </select> 129 130 </mapper>
测试：
1 package test; 2 3 import static org.junit.Assert.*; 4 5 import java.util.ArrayList; 6 import java.util.Date; 7 import java.util.HashMap; 8 import java.util.List; 9 import java.util.Map; 10 11 import org.apache.ibatis.session.RowBounds; 12 import org.apache.ibatis.session.SqlSession; 13 import org.junit.After; 14 import org.junit.Before; 15 import org.junit.Test; 16 17 import mapper.DeptMapper; 18 import mapper.EmpMapper; 19 import model.Dept; 20 import model.Emp; 21 import model.PageBean; 22 import oracle.net.aso.e; 23 import util.MyBatisUtil; 24 25 public class JUTest { 26 private SqlSession ss; 27 private EmpMapper em; 28 private DeptMapper dm; 29 30 @Before 31 public void setUp() throws Exception { 32 ss = MyBatisUtil.getSession(); 33 em = ss.getMapper(EmpMapper.class); 34 dm = ss.getMapper(DeptMapper.class); 35 } 36 37 @After 38 public void tearDown() throws Exception { 39 MyBatisUtil.destory(ss); 40 } 41 42 @Test 43 public void test() { 44 Map map=new HashMap(); 45 //map.put("sex", "1"); 46 // List<Emp> elist = em.selectEmpByMapParam(map); 47 // for(Emp e:elist){ 48 // System.out.println(e); 49 // } 50 51 //map.put("ename", "T%"); 52 // map.put("job", "推销员"); 53 // List<Emp> elist=em.selectEmpByChoose(map); 54 // for(Emp e:elist){ 55 // System.out.println(e); 56 // } 57 58 // map.put("ename", "T%"); 59 // map.put("job", "推销员"); 60 // List<Emp> elist =em.selectEmpByWhere(map); 61 // for(Emp e:elist){ 62 // System.out.println(e); 63 // } 64 65 // map.put("ename", "T%"); 66 // map.put("job", "推销员"); 67 // List<Emp> elist =em.selectEmpByTrim(map); 68 // for(Emp e:elist){ 69 // System.out.println(e); 70 // } 71 72 // List<Integer> idlist=new ArrayList<Integer>(); 73 // idlist.add(7369); 74 // idlist.add(7521); 75 // idlist.add(7782); 76 // //直接传list集合，xml中collection属性为传入类型 77 // //List<Emp> elist=em.selectEmpByForeachList(idlist); 78 // map.put("idList", idlist); 79 // //将集合加到map中，传map，xml中collection属性为map的键 80 // //List<Emp> elist=em.selectEmpByForeachMap(map); 81 // //传集合，xml中collection属性为传入类型 82 // Integer[] ia={7369,7521}; 83 // List<Emp> elist=em.selectEmpByForeachInteger(ia); 84 // for(Emp e:elist){ 85 // System.out.println(e); 86 // } 87 88 // Emp e=new Emp(); 89 // e.setEmpno(7010); 90 // e.setEname("修改"); 91 // e.setSal(999); 92 // int ea=em.updateEmpBySet(e); 93 // System.out.println(ea); 94 95 //分页1 设置RowBounds，然后传入，自动分页 96 // int page = 2; 97 // int rows = 5; 98 // 99 // int limit = (page-1)*rows; 100 // int offset = rows; 101 // 102 // 103 // RowBounds rbs = new RowBounds(limit, offset); 104 // List<Emp> elist = em.selectEmp(rbs); 105 // for(Emp e:elist){ 106 // System.out.println(e); 107 // } 108 109 //分页2 利用实体类，sql引入等 110 PageBean pb = new PageBean(1, 5); 111 112 List<Emp> elist = em.selectEmpByPage(pb); 113 114 for(Emp f : elist) { 115 System.out.println(f); 116 } <span style="color: 本文系统来源：http://www.cnblogs.com/jiangwz/p/7575498.html
内容总结
以上是互联网集市为您收集整理的MyBatis框架（三）动态SQL，分页，二进制存入数据库图片全部内容，希望文章能够帮你解决MyBatis框架（三）动态SQL，分页，二进制存入数据库图片所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】