mybatis-扩展
分页插件
使用pageHelper参考官方https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
批处理
- mybatis批处理
修改构建sqlsession对象的方式即可
SqlSession openSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
long start = System.currentTimeMillis();
try{
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
for (int i = 0; i < 10000; i++) {
mapper.addEmp(new Employee(UUID.randomUUID().toString().substring(0, 5), "b", "1"));
}
openSession.commit();
long end = System.currentTimeMillis();
//批量:(预编译sql一次==>设置参数===>10000次===>执行(1次))
//Parameters: 616c1(String), b(String), 1(String)==>4598
//非批量:(预编译sql=设置参数=执行)==》10000 10200
System.out.println("执行时长:"+(end-start));
}finally{
openSession.close();
}
- 整合spring
需要批处理看这里
拿到session之后,按照mybatis批处理的使用方式编码即可
调用存储过程
create or replace p_temp_procedure(
p_table_name in varchar,p_start in int,p_end in int,p_count out int,p_records out sys_refcursor
) as
begin
select count(*) into p_count from p_table_name;
open p_records for
select * from (select rownum rn,t.* from p_table_name t where rownum<=p_end) where rn >=p_start;
end p_temp_procedure;
- statementType="CALLABLE":表示要调用存储过程
- 语法{call procedure_name(params)};参数里的jdbcType参考JdbcType枚举里的属性
<select id="getPageByProcedure" statementType="CALLABLE" databaseId="oracle"> {call hello_test( #{start,mode=IN,jdbcType=INTEGER}, #{end,mode=IN,jdbcType=INTEGER}, #{count,mode=OUT,jdbcType=INTEGER}, #{emps,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=PageEmp} )} </select>
<resultMap type="com.mybatis.bean.Employee" id="PageEmp"> <id column="EMPLOYEE_ID" property="id"/> <result column="LAST_NAME" property="email"/> <result column="EMAIL" property="email"/> </resultMap>
自定义类型处理器
mybatis默认枚举类型保存的方式是枚举名称,如果我们要用枚举的其他属性作为存储内容的话,需要自定义typeHandler
实现TypeHandler接口。或者继承BaseTypeHandler
public class MyEnumEmpStatusTypeHandler implements TypeHandler<EmpStatus> { /** * 定义当前数据如何保存到数据库中 */ @Override public void setParameter(PreparedStatement ps, int i, EmpStatus parameter, JdbcType jdbcType) throws SQLException { // TODO Auto-generated method stub System.out.println("要保存的状态码:"+parameter.getCode()); ps.setString(i, parameter.getCode().toString()); } @Override public EmpStatus getResult(ResultSet rs, String columnName) throws SQLException { // TODO Auto-generated method stub //需要根据从数据库中拿到的枚举的状态码返回一个枚举对象 int code = rs.getInt(columnName); System.out.println("从数据库中获取的状态码:"+code); EmpStatus status = EmpStatus.getEmpStatusByCode(code); return status; } @Override public EmpStatus getResult(ResultSet rs, int columnIndex) throws SQLException { // TODO Auto-generated method stub int code = rs.getInt(columnIndex); System.out.println("从数据库中获取的状态码:"+code); EmpStatus status = EmpStatus.getEmpStatusByCode(code); return status; } @Override public EmpStatus getResult(CallableStatement cs, int columnIndex) throws SQLException { // TODO Auto-generated method stub int code = cs.getInt(columnIndex); System.out.println("从数据库中获取的状态码:"+code); EmpStatus status = EmpStatus.getEmpStatusByCode(code); return status; }
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public enum EmpStatus { LOGIN(100,"用户登录"),LOGOUT(200,"用户登出"),REMOVE(300,"用户不存在"); private Integer code; private String msg; private EmpStatus(Integer code,String msg){ this.code = code; this.msg = msg; } public Integer getCode() { return code; } public void setCode(Integer code) { this.code = code; } public String getMsg() { return msg; } public void setMsg(String msg) { this.msg = msg; } //按照状态码返回枚举对象 public static EmpStatus getEmpStatusByCode(Integer code){ switch (code) { case 100: return LOGIN; case 200: return LOGOUT; case 300: return REMOVE; default: return LOGOUT; } }
在全局配置文件定义typeHandlers
<typeHandlers> <!--1、配置我们自定义的TypeHandler --> <typeHandler handler="com.mybatis.typehandler.MyEnumEmpStatusTypeHandler" javaType="com.mybatis.bean.EmpStatus"/> <!--2、也可以在处理某个字段的时候告诉MyBatis用什么类型处理器 保存:#{empStatus,typeHandler=xxxx} 查询: <resultMap type="com.mybatis.bean.Employee" id="MyEmp"> <id column="id" property="id"/> <result column="empStatus" property="empStatus" typeHandler=""/> </resultMap> 注意:如果在参数位置修改TypeHandler,应该保证保存数据和查询数据用的TypeHandler是一样的。 --> </typeHandlers>