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 }
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 }