学完oracle的存储过程之后我就一直在纠结怎么使用mybitis来调用,在网上找了好多资料很多都不靠谱,经过我的各种尝试和修改,终于找到了解决方案.废话不多说直接上代码.
首先用maven搭建mybitis和spring.
pom文件:
<properties>
<spring.version>4.2.4.RELEASE</spring.version>
</properties>
<dependencies>
<!-- Spring -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<!--junit测试-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.9</version>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!--mybatis整合spring-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.0</version>
</dependency>
<!--oracle驱动-->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc14</artifactId>
<version>10.2.0.4.0</version>
</dependency>
<!--连接池-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.9</version>
</dependency>
</dependencies>注意:这里mybitis版本要用3.4.5或以上,不然有个注解用不了,你也可以不用spring整合.接下来是applicationContext.xml配置文件:<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans-4.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<!--包扫描-->
<context:component-scan base-package="cn.itcast"/>
<!-- 数据库连接池 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="url" value="jdbc:oracle:thin:@192.168.66.166:1521:orcl"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
<property name="driverClassName" value="oracle.jdbc.OracleDriver"/>
</bean>
<!-- 让spring管理sqlsessionfactory 使用mybatis和spring整合包中的 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 数据库连接池 -->
<property name="dataSource" ref="dataSource"/>
</bean>
<!--接口扫描 MapperScannerConfigurer-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<!--指定Dao接口的包-->
<property name="basePackage" value="cn.itcast.dao"/>
</bean>
</beans>这里数据库的用户名和密码记得改成你自己的,我的包结构是cn.itcast,这里包扫描也要更改,我使用的表是复制oracle自带的EMP表: 接下来创建存储过程:create or replace procedure pro_emp1 (empnum emp.empno%type ,empsal out emp.sal%type) as begin select sal*12+nvl(comm,0) into empsal from emp where empno = empnum; end;这里是计算指定员工的年薪,通过传入员工编号得到年薪(基本类型).--------------------------------------------------------------------------------------------------------------------create or replace procedure pro_emp2 (deptnum emp.deptno%type ,rs out Sys_Refcursor) as begin open rs for select * from emp where deptno = deptnum; end;这里是通过部门编号查找部门成员,返回的是一个游标,注意游标用Sys_Refcursor封装接下来是重点,在dao包中创建EmpDao:public interface EmpDao {
@Options(statementType = StatementType.CALLABLE) //这里选择CALLABLE表示调用存储过程
@Select("{call pro_emp1 (#{empnum,mode=IN,jdbcType=INTEGER},#{empsal,mode=OUT,jdbcType=DOUBLE})}") //这里mode=OUT表示输出参数
public void callPro_emp1(Map<String ,Object> prama); //返回一个基本类型可以不用返回值,返回的基本类型被封装到传入的Map中
@Options(statementType = StatementType.CALLABLE) //这里返回的数据类型是游标所有jdbcType选CURSOR,封装的java类型选ResultSet,如果只返回一行数据也要用游标封装
@Select("{call pro_emp2 (#{deptnum,mode=IN,jdbcType=INTEGER},#{rs,mode=OUT,jdbcType=CURSOR,javaType=ResultSet,resultMap=rm})}") //告诉mybitis如何封装数据
@Results(id = "rm",value = {
@Result(property="empno",column="EMPNO"),
@Result(property="ename",column="ENAME"),
@Result(property="job",column="JOB"),
@Result(property="mgr",column="MGR"),
@Result(property="hiredate",column="HIREDATE"),
@Result(property="sal",column="SAL"),
@Result(property="comm",column="COMM"),
@Result(property="deptno",column="DEPTNO")
}) //mybitis版本要是过低,会没有id这个属性
public List<Emp> callPro_emp2(Map<String ,Object> prama); //这里一定要有一个List的返回值,不然会报错,如果用配置文件来配置可以不用返回值
}接下来是测试:我们用JUnit整合Spring来测试:@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class ProcedureTest {
@Autowired
private EmpDao empDao;
@Test
public void test01(){
Map<String,Object> prama=new HashMap<String, Object>();
prama.put("empnum", 7902); //把入参封装到Map中
empDao.callPro_emp1(prama);
Double empsal = (Double) prama.get("empsal"); //执行完后会自动将结果封装在Map中,key为你在注解中定义的值:empsal
System.out.println(empsal);
}
@Test
public void test02() {
Map<String,Object> prama=new HashMap<String, Object>();
prama.put("deptnum", 20);
empDao.callPro_emp2(prama);//这个方法的返回值并没有封装数据,数据封装在传入的Map中
List<Emp> rs = (List) prama.get("rs");//执行完后会自动将结果封装在Map中,key为你在注解中定义的值:rs,注意这里强转为List即可
for (Emp emp : rs) {
System.out.println(emp);
}
}
}
|
|