一、动态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>
用于不支持自增长主键的数据库, 尽量避免写这个东西
符号：
< < 小于号
> > 大于号
& & 和
' ’ 单引号
" " 双引号
<![CDATA[]]>
例子：
model：
package model;
import java.util.Date;
public class Emp {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Integer sal;
private Integer comm;
private Integer deptno;
private Integer sex;
private Dept dept;
public Emp() {
super();
// TODO Auto-generated constructor stub
}
public Emp(Integer empno, String ename, String job, Integer mgr, Date hiredate, Integer sal, Integer comm, Integer deptno, Integer sex,
Dept dept) {
super();
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.hiredate = hiredate;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
this.sex = sex;
this.dept = dept;
}
public Integer getEmpno() {
return empno;
}
public void setEmpno(Integer empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public Integer getMgr() {
return mgr;
}
public void setMgr(Integer mgr) {
this.mgr = mgr;
}
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public Integer getSal() {
return sal;
}
public void setSal(Integer sal) {
this.sal = sal;
}
public Integer getComm() {
return comm;
}
public void setComm(Integer comm) {
this.comm = comm;
}
public Integer getDeptno() {
return deptno;
}
public void setDeptno(Integer deptno) {
this.deptno = deptno;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
public Dept getDept() {
return dept;
}
public void setDept(Dept dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
+ ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + ", sex=" + sex + ", dept=" + dept + "]";
}
}
package model;
public class PageBean {
private int page;
private int rows;
private int firstRow;
private int maxRow;
public PageBean() {
this.page = 1;
this.rows = 10;
this.firstRow = (page-1)*rows;
this.maxRow = page*rows;
}
public PageBean(int page, int rows) {
this.page = page;
this.rows = rows;
this.firstRow = (page-1)*rows;
this.maxRow = page*rows;
}
public int getPage() {
return page;
}
public void setPage(int page) {
this.page = page;
}
public int getRows() {
return rows;
}
public void setRows(int rows) {
this.rows = rows;
}
public int getFirstRow() {
return firstRow;
}
public void setFirstRow(int firstRow) {
this.firstRow = firstRow;
}
public int getMaxRow() {
return maxRow;
}
public void setMaxRow(int maxRow) {
this.maxRow = maxRow;
}
}
mapper：
package mapper;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.RowBounds;
import model.Emp;
import model.PageBean;
public interface EmpMapper {
List<Emp> selectEmpByMapParam(Map<String,Object> map);
List<Emp> selectEmpByChoose(Map<String ,Object> map);
List<Emp> selectEmpByWhere(Map<String ,Object> map);
List<Emp> selectEmpByTrim(Map<String ,Object> map);
List<Emp> selectEmpByForeachMap(Map<String ,Object> map);
List<Emp> selectEmpByForeachList(List<Integer> idlist);
List<Emp> selectEmpByForeachInteger(Integer[] array);
int updateEmpBySet(Emp e);
int insertEmp(Emp e);
List<Emp> selectEmp(RowBounds rbs);
List<Emp> selectEmpByPage(PageBean pg);
}
<?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="mapper.EmpMapper">
<select id="selectEmp" parameterType="Map" resultType="Emp">
select * from p_emp e
</select>
<select id="selectEmpByMapParam" parameterType="Map" resultType="Emp">
select * from p_emp e
<if test="sex!=null">
where e.sex=#{sex}
</if>
</select>
<select id="selectEmpByChoose" parameterType="Map" resultType="Emp">
select * from p_Emp e where 1=1
<choose>
<when test="ename!=null">
and e.ename like #{ename}
</when>
<when test="job!=null">
and e.job like #{job}
</when>
<otherwise>
and e.ssex = '1'
</otherwise>
</choose>
</select>
<select id="selectEmpByWhere" parameterType="Map" resultType="Emp">
select * from p_emp e
<where>
<if test="ename!=null">
and e.ename like #{ename}
</if>
<if test="job!=null">
and e.job like #{job}
</if>
</where>
</select>
<select id="selectEmpByTrim" parameterType="Map" resultType="Emp">
select * from p_Emp e
<trim prefix="where" prefixOverrides="and|or">
<if test="ename!=null">
and e.ename like #{ename}
</if>
<if test="job!=null">
and e.job like #{job}
</if>
</trim>
</select>
<select id="selectEmpByForeachMap" resultType="Emp">
select * from p_Emp e where e.empno in
<foreach collection="idList" item="aaa" open="(" close=")" separator=",">
#{aaa}
</foreach>
</select>
<select id="selectEmpByForeachList" resultType="Emp">
select * from p_Emp e where e.empno in
<foreach collection="list" item="aaa" open="(" close=")" separator=",">
#{aaa}
</foreach>
</select>
<select id="selectEmpByForeachInteger" resultType="Emp">
select * from p_Emp e where e.empno in
<foreach collection="array" item="aaa" open="(" close=")" separator=",">
#{aaa}
</foreach>
</select>
<update id="updateEmpBySet" parameterType="Emp">
update p_Emp e
<set>
<if test="ename!=null">
e.ename=#{ename},
</if>
<if test="job!=null">
e.job=#{job},
</if>
<if test="mgr!=null">
e.mgr=#{mgr},
</if>
<if test="hiredate!=null">
e.hiredate=#{hiredate},
</if>
<if test="sal!=null">
e.sal=#{sal}
</if>
</set>
where e.empno=#{empno}
</update>
<sql id="pageSqlPre">
SELECT * FROM (
</sql>
<sql id="pageSqlSuf">
WHERE ROWNUM <![CDATA[<=]]> 9) r WHERE r.rnum>6
</sql>
<select id="selectEmpBySql" resultType="Emp">
<include refid="pageSqlPre"></include>
SELECT e.*, ROWNUM rnum FROM p_Emp e
<include refid="pageSqlSuf"></include>
</select>
<!-- 不建议使用
<selectKey keyProperty="empno" order="BEFORE" resultType="int">
select test1.nextval from dual
</selectKey> -->
<sql id="pagePre">
SELECT * FROM (
</sql>
<sql id="pageSuf">
WHERE ROWNUM <![CDATA[<=]]> #{maxRow}) r
WHERE r.rnum > #{firstRow}
</sql>
<select id="selectEmpByPage" resultType="Emp" parameterType="PageBean">
<include refid="pagePre"></include>
select e.*, rownum rnum from p_emp e
<include refid="pageSuf"></include>
</select>
</mapper>
测试：
package test;
import static org.junit.Assert.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import mapper.DeptMapper;
import mapper.EmpMapper;
import model.Dept;
import model.Emp;
import model.PageBean;
import oracle.net.aso.e;
import util.MyBatisUtil;
public class JUTest {
private SqlSession ss;
private EmpMapper em;
private DeptMapper dm;
@Before
public void setUp() throws Exception {
ss = MyBatisUtil.getSession();
em = ss.getMapper(EmpMapper.class);
dm = ss.getMapper(DeptMapper.class);
}
@After
public void tearDown() throws Exception {
MyBatisUtil.destory(ss);
}
@Test
public void test() {
Map map=new HashMap();
//map.put("sex", "1");
// List<Emp> elist = em.selectEmpByMapParam(map);
// for(Emp e:elist){
// System.out.println(e);
// }
//map.put("ename", "T%");
// map.put("job", "推销员");
// List<Emp> elist=em.selectEmpByChoose(map);
// for(Emp e:elist){
// System.out.println(e);
// }
// map.put("ename", "T%");
// map.put("job", "推销员");
// List<Emp> elist =em.selectEmpByWhere(map);
// for(Emp e:elist){
// System.out.println(e);
// }
// map.put("ename", "T%");
// map.put("job", "推销员");
// List<Emp> elist =em.selectEmpByTrim(map);
// for(Emp e:elist){
// System.out.println(e);
// }
// List<Integer> idlist=new ArrayList<Integer>();
// idlist.add(7369);
// idlist.add(7521);
// idlist.add(7782);
// //直接传list集合，xml中collection属性为传入类型
// //List<Emp> elist=em.selectEmpByForeachList(idlist);
// map.put("idList", idlist);
// //将集合加到map中，传map，xml中collection属性为map的键
// //List<Emp> elist=em.selectEmpByForeachMap(map);
// //传集合，xml中collection属性为传入类型
// Integer[] ia={7369,7521};
// List<Emp> elist=em.selectEmpByForeachInteger(ia);
// for(Emp e:elist){
// System.out.println(e);
// }
// Emp e=new Emp();
// e.setEmpno(7010);
// e.setEname("修改");
// e.setSal(999);
// int ea=em.updateEmpBySet(e);
// System.out.println(ea);
//分页1 设置RowBounds，然后传入，自动分页
// int page = 2;
// int rows = 5;
//
// int limit = (page-1)*rows;
// int offset = rows;
//
//
// RowBounds rbs = new RowBounds(limit, offset);
// List<Emp> elist = em.selectEmp(rbs);
// for(Emp e:elist){
// System.out.println(e);
// }
//分页2 利用实体类，sql引入等
PageBean pb = new PageBean(1, 5);
List<Emp> elist = em.selectEmpByPage(pb);
for(Emp f : elist) {
System.out.println(f);
}
}
}
二、二进制存入图片
将图片转换为字节数组一Blob格式存入取出数据库
例子：
model：
package model;
public class Puser {
private String pname;
private String ppassword;
private byte[] pprcture;
public Puser() {
super();
// TODO Auto-generated constructor stub
}
public Puser(String pname, String ppassword, byte[] pprcture) {
super();
this.pname = pname;
this.ppassword = ppassword;
this.pprcture = pprcture;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
public String getPpassword() {
return ppassword;
}
public void setPpassword(String ppassword) {
this.ppassword = ppassword;
}
public byte[] getPprcture() {
return pprcture;
}
public void setPprcture(byte[] pprcture) {
this.pprcture = pprcture;
}
}
mapper：
package mapper;
import java.util.List;
import model.Puser;
public interface PuserMapper {
int insertPuser(Puser p);
Puser getUser(String pname);
}
<?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="mapper.PuserMapper">
<insert id="insertPuser">
insert into puser values(#{pname},#{ppassword},#{pprcture})
</insert>
<select id="getUser" parameterType="String" resultType="puser">
select * from puser a where a.pname=#{pname}
</select>
</mapper>
测试：
package test;
import static org.junit.Assert.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import mapper.PuserMapper;
import model.Puser;
import util.MyBatisUtil;
public class JUtest {
private SqlSession ss;
private PuserMapper pm;
@Before
public void setUp() throws Exception {
ss = MyBatisUtil.getSession();
pm=ss.getMapper(PuserMapper.class);
}
@After
public void tearDown() throws Exception {
MyBatisUtil.destory(ss);
}
@Test
public void test() {
/*File file=new File("E:\\2017-4-1手机 备份\\2017·1·15备份\\kowy.jpg");
InputStream in =null;
if(file.exists()){
try {
in = new FileInputStream(file);
byte[] bs=new byte[in.available()];
in.read(bs);
Puser p=new Puser("鑫月半","666s",bs);
int a=pm.insertPuser(p);
System.out.println(a);
in.close();
} catch (Exception e) {
e.printStackTrace();
}
}else{
System.out.println("文件有问题");
}*/
File outfile = new File("E:\\sanpang.jpg"); // 目标文件的地址
try {
OutputStream out = new FileOutputStream(outfile);
Map m=new HashMap();
//m.put("pname","鑫月半")
Puser user = pm.getUser("鑫月半");
byte[] bs = user.getPprcture();
System.out.println(bs.toString());
//out.write(bs);
//out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}