首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MyBatis框架(三)动态SQL,分页,二进制存入数据库图片

MyBatis框架(三)动态SQL,分页,二进制存入数据库图片

作者头像
二十三年蝉
发布2018-02-28 11:10:37
1.9K0
发布2018-02-28 11:10:37
举报
文章被收录于专栏:闻道于事闻道于事

一、动态sql语句,分页

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>   用于不支持自增长主键的数据库, 尽量避免写这个东西

符号:

&lt; < 小于号 &gt; > 大于号 &amp; & 和 &apos; ’ 单引号 &quot; " 双引号

<![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         }
117         
118     }
119 }

二、二进制存入图片

将图片转换为字节数组一Blob格式存入取出数据库

例子:

model:

 1 package model;
 2 
 3 public class Puser {
 4     private String pname;
 5     private String ppassword;
 6     private byte[] pprcture;
 7     
 8     
 9     public Puser() {
10         super();
11         // TODO Auto-generated constructor stub
12     }
13     public Puser(String pname, String ppassword, byte[] pprcture) {
14         super();
15         this.pname = pname;
16         this.ppassword = ppassword;
17         this.pprcture = pprcture;
18     }
19     public String getPname() {
20         return pname;
21     }
22     public void setPname(String pname) {
23         this.pname = pname;
24     }
25     public String getPpassword() {
26         return ppassword;
27     }
28     public void setPpassword(String ppassword) {
29         this.ppassword = ppassword;
30     }
31     public byte[] getPprcture() {
32         return pprcture;
33     }
34     public void setPprcture(byte[] pprcture) {
35         this.pprcture = pprcture;
36     }
37     
38     
39 }

mapper:

 1 package mapper;
 2 
 3 import java.util.List;
 4 
 5 import model.Puser;
 6 
 7 
 8 public interface PuserMapper {
 9     int insertPuser(Puser p);
10     
11     Puser getUser(String pname);
12 }
 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.PuserMapper">
 6 
 7     <insert id="insertPuser">
 8         insert into puser values(#{pname},#{ppassword},#{pprcture})
 9     </insert>
10     
11     <select id="getUser"  parameterType="String" resultType="puser">
12         select * from puser a where a.pname=#{pname}
13     </select>
14     
15 </mapper>

测试:

 1 package test;
 2 
 3 import static org.junit.Assert.*;
 4 
 5 import java.io.File;
 6 import java.io.FileInputStream;
 7 import java.io.FileNotFoundException;
 8 import java.io.FileOutputStream;
 9 import java.io.InputStream;
10 import java.io.OutputStream;
11 import java.util.HashMap;
12 import java.util.Map;
13 
14 import org.apache.ibatis.session.SqlSession;
15 import org.junit.After;
16 import org.junit.Before;
17 import org.junit.Test;
18 
19 import mapper.PuserMapper;
20 import model.Puser;
21 import util.MyBatisUtil;
22 
23 public class JUtest {
24     private SqlSession ss;
25     private PuserMapper pm;
26 
27     @Before
28     public void setUp() throws Exception {
29         ss = MyBatisUtil.getSession();
30         pm=ss.getMapper(PuserMapper.class);
31     }
32 
33     @After
34     public void tearDown() throws Exception {
35         MyBatisUtil.destory(ss);
36     }
37 
38     @Test
39     public void test() {
40         /*File file=new File("E:\\2017-4-1手机 备份\\2017·1·15备份\\kowy.jpg");
41         InputStream in =null;
42         if(file.exists()){
43             try {
44                 in = new FileInputStream(file);
45                 byte[] bs=new byte[in.available()];
46                 in.read(bs);
47                 Puser p=new Puser("鑫月半","666s",bs);
48                 int a=pm.insertPuser(p);
49                 System.out.println(a);
50                 in.close();
51                 
52                 
53             } catch (Exception e) {
54                 e.printStackTrace();
55             }
56         }else{
57             System.out.println("文件有问题");
58         }*/
59         
60         File outfile = new File("E:\\sanpang.jpg"); // 目标文件的地址
61         
62         try {
63             OutputStream out = new FileOutputStream(outfile);
64             Map m=new HashMap();
65             //m.put("pname","鑫月半")
66             Puser user = pm.getUser("鑫月半"); 
67             byte[] bs = user.getPprcture();
68             System.out.println(bs.toString());
69             //out.write(bs);
70             //out.close();
71         } catch (Exception e) {
72             e.printStackTrace();
73         }
74     }
75 
76 }
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2017-09-24 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一、动态sql语句,分页
  • 二、二进制存入图片
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档