mybatis扩展-存储过程

一、存储过程调用关键

1、SQL映射文件中,使用select标签定义调用存储过程
(1)statementType=”CALLABLE”:表示要调用存储过程
(2){call procedure_name(params)}
2、编辑存储过程对象,用于保存存储过程的输入参数和输出参数

二、存储过程调用-mysql

1、搭建数据库

id      last_name    gender      email
1	  mike	       0         [email protected]	
2	  book	       0  	[email protected]	
3	  tom	       1        [email protected]	
4	  jerry	       1	[email protected]	
5	  hhee	       1	[email protected]	
6	  jerry4       1	[email protected]	
7	  smith0x1     1	[email protected]	
8	  mas	       1	[email protected]	
9	  smith0x1     1	[email protected]	
10	  allen0x1     0	[email protected]

2、创建存储过程

USE `mybatis`;
DROP PROCEDURE IF EXISTS proc_employee;
DELIMITER //
CREATE PROCEDURE proc_employee(IN p_start INT, IN p_end INT,OUT p_count INT)
BEGIN
  SELECT COUNT(*) INTO p_count FROM `tbl_employee`;
  SELECT * FROM ( SELECT a.*  FROM `tbl_employee` a  WHERE a.`id` < p_end)  b
  WHERE b.`id` >p_start;
END//
DELIMITER ;

特别注意点:

开头部分:DELIMITER //【这两斜杠前面有空格,需要注意】
结尾部分:DELIMITER ;【分号前面也有个空格,需要注意】

在定义过程时,使用DELIMITER // 命令将语句的结束符号从分号 ; 
临时改为//,使得过程体中使用的分号被直接传递到服务器,而不会被客户端(如mysql)解释。

3、mysql客户端测试端调用存储过程

set @p_count=1;
CALL proc_employee(1,8,@p_count); 
select @p_count;

4、编辑全局配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
 PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>


	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url" value="jdbc:mysql://localhost:3306/mybatis" />
				<property name="username" value="root" />
				<property name="password" value="Cool123!" />
			</dataSource>
		</environment>
	</environments>
	
		<!-- 5、databaseIdProvider:支持多数据库厂商的;
		 type="DB_VENDOR":VendorDatabaseIdProvider
		 	作用就是得到数据库厂商的标识(驱动getDatabaseProductName()),mybatis就能根据数据库厂商标识来执行不同的sql;
		 	MySQL,Oracle,SQL Server,xxxx
	  -->
	<databaseIdProvider type="DB_VENDOR">
		<!-- 为不同的数据库厂商起别名 -->
		<property name="MySQL" value="mysql"/>
		<property name="Oracle" value="oracle"/>
		<property name="SQL Server" value="sqlserver"/>
	</databaseIdProvider>
	
	<!-- 将我们写好的sql映射文件(EmployeeMapper.xml)一定要注册到全局配置文件(mybatis-config.xml)中 
	如果 数据库全局文件 和 子配置文件 不在同一个目录 ,就需要 /目录/目录/.../EmployeeMapper_old.xml
	-->
	<mappers>
	    <!-- 新方法操作mybatis 需要 的配置文件 -->
		<mapper resource="EmployeeMapper.xml" />
	</mappers>
</configuration>

5、编辑javabean

Employee.java

package com.mybatis.bean;

public class Employee {
	private Integer id;
	private String lastName;
	private String email;
	private String gender;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getGender() {
		return gender;
	}

	public void setGender(String gender) {
		this.gender = gender;
	}
	
	public Employee(){
		super();
	}
	
	public Employee(String lastName, String email, String gender) {
		super();
		this.lastName = lastName;
		this.email = email;
		this.gender = gender;
	}

	@Override
	public String toString() {
		return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
	}

}

存储过程的bean文件  ProcPage.java

package com.mybatis.bean;

import java.util.List;

/**
 * 封装分页查询数据
 * @author lfy
 *
 */
public class ProcPage {
	
	private int start;
	private int end;
	private int count;
	private List<Employee> emps;
	
	public int getStart() {
		return start;
	}
	public void setStart(int start) {
		this.start = start;
	}
	public int getEnd() {
		return end;
	}
	public void setEnd(int end) {
		this.end = end;
	}
	public int getCount() {
		return count;
	}
	public void setCount(int count) {
		this.count = count;
	}
	public List<Employee> getEmps() {
		return emps;
	}
	public void setEmps(List<Employee> emps) {
		this.emps = emps;
	}
	
	

}

6、配置mapper接口

EmployeeMapper.java

package com.mybatis.mapper;

import java.util.List;

import com.mybatis.bean.Employee;
import com.mybatis.bean.ProcPage;

public interface EmployeeMapper {
	
	public List<Employee> getPageByProcedure(ProcPage page);
	
}

7、配置SQL映射文件

EmployeeMapper.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.mybatis.mapper.EmployeeMapper">
	

	 <!-- public void getPageByProcedure(); 
	1、使用select标签定义调用存储过程
	2、statementType="CALLABLE":表示要调用存储过程
	3、{call procedure_name(params)}
	-->
	<select id="getPageByProcedure" statementType="CALLABLE" databaseId="mysql" resultMap="PageEmp">
		{
		call proc_employee(
			#{start,mode=IN,jdbcType=INTEGER},
			#{end,mode=IN,jdbcType=INTEGER},
			#{count,mode=OUT,jdbcType=INTEGER}
		)
		}
	</select>
	<resultMap type="com.mybatis.bean.Employee" id="PageEmp">
		<id column="ID" property="id"/>
		<result column="LAST_NAME" property="lastName"/>
		<result column="EMAIL" property="email"/>
	</resultMap>
	 
	
</mapper>

8、Junit单元测试

package com.mybatis.test;


import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import com.mybatis.bean.ProcPage;
import com.mybatis.mapper.EmployeeMapper;


/**
 * 1、接口式编程
 * 	原生:		Dao		====>  DaoImpl
 * 	mybatis:	Mapper	====>  xxMapper.xml
 * 
 * 2、SqlSession代表和数据库的一次会话;用完必须关闭;
 * 3、SqlSession和connection一样她都是非线程安全。每次使用都应该去获取新的对象。(就是不要放在共享成员变量里面,A线程用完释放,B线程再用就为空了)
 * 4、mapper接口没有实现类,但是mybatis会为这个接口生成一个代理对象。
 * 		(将接口和xml进行绑定)
 * 		EmployeeMapper empMapper =	sqlSession.getMapper(EmployeeMapper.class);
 * 5、两个重要的配置文件:
 * 		mybatis的全局配置文件:包含数据库连接池信息,事务管理器信息等...系统运行环境信息
 * 		sql映射文件:保存了每一个sql语句的映射信息:
 * 					将sql抽取出来。	
 *
 */

public class MybatisTest {
	
	public SqlSessionFactory getSqlSessionFactory() throws IOException {
		String resource = "mybatis-config.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		return new SqlSessionFactoryBuilder().build(inputStream);
	}
	
	/**
	 * mysql分页:
	 * 存储过程包装分页逻辑
	 * @throws IOException 
	 */
	@Test
	public void testProcedure() throws IOException{
		SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
		SqlSession openSession = sqlSessionFactory.openSession();
		try{
			EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
			ProcPage page = new ProcPage();
			page.setStart(5);
			page.setEnd(8);
			page.setEmps(mapper.getPageByProcedure(page));
			
			System.out.println("总记录数:"+page.getCount());
			System.out.println("查出的数据:"+page.getEmps().size());
			System.out.println("查出的数据:"+page.getEmps());
		}finally{
			openSession.close();
		}
		
	}
}

三、存储过程调用流程-oracle

基本和mysql相同,部分不相同。【因为存储过程不一样,存储过程对应有四个参数,导致后面的操作稍微不同】

1、数据库存储过程:

create or replace procedure
proc_employee(p_start in int,
              p_end in int,
              p_count out int,
              ref_cur out sys_refcursor) AS
BEGIN 
   select count(*) into p_count from tbl_employee;
   open ref_cur for
        select * from (select e.*,rownum as rn) from tbl_employee e where rownum <p_end)
        where rn >p_start;
END proc_employee;

2、mapper接口文件

package com.mybatis.mapper;

import java.util.List;

import com.mybatis.bean.Employee;
import com.mybatis.bean.ProcPage;

public interface EmployeeMapper {
	
	public void getPageByProcedure(ProcPage page);
	
}

3、SQL映射文件

<mapper namespace="com.mybatis.mapper.EmployeeMapper">

	<!-- public void getPageByProcedure(); 
	1、使用select标签定义调用存储过程
	2、statementType="CALLABLE":表示要调用存储过程
	3、{call procedure_name(params)}
	-->
	
	<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>
	
</mapper>

4、Junit单元测试

/**
 * oracle分页:
 * 		借助rownum:行号;子查询;
 * 存储过程包装分页逻辑
 * @throws IOException 
 */
@Test
public void testProcedure() throws IOException{
	SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
	SqlSession openSession = sqlSessionFactory.openSession();
	try{
		EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
		OraclePage page = new OraclePage();
		page.setStart(1);
		page.setEnd(5);
		mapper.getPageByProcedure(page);
			
		System.out.println("总记录数:"+page.getCount());
		System.out.println("查出的数据:"+page.getEmps().size());
		System.out.println("查出的数据:"+page.getEmps());
	}finally{
		openSession.close();
	}
		
}

 

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments