Mybatis杂项

本篇文章包括：

1. 处理CLOB、BLOB类型数据

CLOB：大文本类型，比如小说 每个数据库的类型不同   clob MySQL中没有，使用longtext,longtext就是clob
BLOB：二进制文件，比如图片、电影、音乐 MySQL中blob类型小，存的东西不大，一般使用longblob

2. 输入多个输入参数：此种方式比较挫，一般不使用，通常使用hashmap传入参数

3. Mybatis分页

逻辑分页：是指把所有内容查出之后放在缓存中，再从缓存中和获取相应条数的数据，数据量大时性能不好
物理分页：MySQL还没有实现物理分页，通过拼接SQL实现物理分页, 比较常用

4. Mybatis缓存

在并发量比较大时，都是查询操作，这种情况用缓存效果比较好
Mybatis 默认情况下，MyBatis 启用一级缓存，即同一个 SqlSession 接口对象调用了相同的 select 语句，则直接会从缓存中返回结果，而不是再查询一次数据库;开发者可以自己配置二级缓存，二级缓存是全局的;默认情况下，select 使用缓存的，insert update delete 是不使用缓存的;

代码如下：项目代码下载地址https://download.csdn.net/download/chpllp/10605900

junit测试代码，StudentTest.java

package com.java1234.service;

import static org.junit.Assert.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
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.apache.log4j.Logger;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.java1234.mappers.StudentMapper;
import com.java1234.model.Student;
import com.java1234.util.SqlSessionFactoryUtil;

public class StudentTest {
	private static Logger logger=Logger.getLogger(StudentTest.class);
	private SqlSession sqlSession=null;
	private StudentMapper studentMapper=null;
	//测试前调用
	@Before
	public void setUp() throws Exception {
		sqlSession=SqlSessionFactoryUtil.openSession();
		studentMapper=sqlSession.getMapper(StudentMapper.class);
	}
	//测试后调用
	@After
	public void tearDown() throws Exception {
		sqlSession.close();
	}
	//插入clob、blob类型数据
	@Test
	public void testInsertStudent() {
		logger.info("添加学生带图片带备注");
		Student student=new Student();
		student.setName("张三4");
		student.setAge(14);
		student.setRemark("很长的文本。。。。。");
		byte []pic=null;
		try {
			File file=new File("D://eclipse-workspace/MyBatisPro07/pic.jpg");
			InputStream inputStream=new FileInputStream(file);
			pic = new byte[inputStream.available()];
			inputStream.read(pic);//将流读到字节数组中去
			inputStream.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
		student.setPic(pic);
		studentMapper.insertStudent(student);
		sqlSession.commit();
	}
	//读取clob、blob类型数据
	@Test
	public void testGetStudent() {
		logger.info("通过ID查找学生");
		Student student=studentMapper.getStudentById(5);
		System.out.println(student);
		byte []pic=student.getPic();
		try {
			File file=new File("d://pic2.jpg");
			OutputStream outputStream=new FileOutputStream(file);
			outputStream.write(pic);//把字节写到输出流中
			outputStream.close();
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	//传入多个参数进行查询，一般不使用这种方式，使用hashmap传入参数
	@Test
	public void testSearchStudent() {
		logger.info("查询学生");
		List<Student> students=studentMapper.searchStudent("%张%", 14);
		for(Student s:students) {
			System.out.println(s);
		}
	}
	//Mybatis分页
	//逻辑分页，是指把所有内容查出之后放在缓存中，再从缓存中和获取相应条数的数据，数据量大时性能不好
	@Test
	public void testFindStudents() {
		logger.info("逻辑分页查询学生");
		int offset=0,limit=3;
		RowBounds rowBounds=new RowBounds(offset, limit);
		List<Student> students=studentMapper.findStudents(rowBounds);
		for(Student s:students) {
			System.out.println(s);
		}
		
	}
	//MySQL还没有实现物理分页，通过拼接SQL实现物理分页, 比较常用
	@Test
	public void testFindStudents2() {
		logger.info("物理分页查询学生");
		Map<String, Object> map=new HashMap<String, Object>();
		map.put("start", 0);
		map.put("size", 3);
		List<Student> students=studentMapper.findStudents2(map);
		for(Student s:students) {
			System.out.println(s);
		}
	}
}

StudentMapper.java

package com.java1234.mappers;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.RowBounds;

import com.java1234.model.Student;

public interface StudentMapper {
	public int insertStudent(Student student);//插入clob、blob类型数据
	public Student getStudentById(Integer id);//读取clob、blob类型数据
	public List<Student> searchStudent(String name, int age);//传入多个参数进行查询，一般使用hashmap进行传参
	public List<Student> findStudents(RowBounds rowBounds);//逻辑分页取数据
	public List<Student> findStudents2(Map map);//物理分页取数据
}

StudentMapper.xml

<?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.java1234.mappers.StudentMapper">
	<!-- 缓存的配置，放在namespace下，针对每个namespace -->
	<cache size="1024" flushInterval="60000" eviction="LRU" readOnly="false"/>
    <!--1，size:表示缓存cache中能容纳的最大元素数。默认是1024；
    	2，flushInterval:定义缓存刷新周期，以毫秒计；
     	3，eviction:定义缓存的移除机制；默认是LRU(least recently userd，最近最少使用),还有FIFO(first in first out，先进先出)
     	4，readOnly:默认值是false,缓存可读可写，假如是true的话，缓存只能读。
     -->
	<resultMap type="Student" id="StudentResult">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="age" column="age"/>
	</resultMap>
	<!--插入clob、blob类型数据-->
	<insert id="insertStudent" parameterType="Student">
		insert into t_student values(null, #{name}, #{age}, #{pic}, #{remark});
	</insert>
	<!-- 读取clob、blob类型数据 -->
	<select id="getStudentById" parameterType="Integer" resultType="Student">
		select * from t_student where id=#{id}
	</select>
	<!-- 传入多个参数进行查询 -->
	<select id="searchStudent" resultMap="StudentResult">
		select * from t_student where name like #{param1} and age=#{param2}
	</select>
	<!-- select语句 useCache默认为true，即使用缓存，flushCache默认为false，即不清空缓存 -->
	<!-- 逻辑分页取数据，全部取出放在内存中-->
	<select id="findStudents" resultMap="StudentResult" flushCache="false" useCache="true">
		select * from t_student
	</select>
	<!-- 通过拼接SQL实现物理分页取数据 -->
	<select id="findStudents2" parameterType="Map" resultMap="StudentResult">
		select * from t_student
		<if test="start!=null and size!=null">
			limit #{start},#{size}
		</if>
	</select>	
</mapper> 

Student.java

package com.java1234.model;

public class Student {

	private Integer id;
	private String name;
	private Integer age;
	private byte[] pic;   //blob映射成字节数组
	private String remark; //clob大文本映射成字符串
	
	public Student() {
		super();
		// TODO Auto-generated constructor stub
	}
	public Student(String name, Integer age) {
		super();
		this.name = name;
		this.age = age;
	}
	public Student(Integer id, String name, Integer age) {
		super();
		this.id = id;
		this.name = name;
		this.age = age;
	}
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Integer getAge() {
		return age;
	}
	public void setAge(Integer age) {
		this.age = age;
	}
	public byte[] getPic() {
		return pic;
	}
	public void setPic(byte[] pic) {
		this.pic = pic;
	}
	public String getRemark() {
		return remark;
	}
	public void setRemark(String remark) {
		this.remark = remark;
	}
	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + ", age=" + age + ", remark=" + remark + "]";
	}	
}