定义一个存储过程:
- create or replace procedure prg_add(p1 in number,p2 in number,p3 out number)
- as
- begin
- p3:=p1+p2;
- end;
定义一个方法:
- create or replace function fun_add(p1 number, p2 number)
- return number
- as
- begin
- return p1+p2;
- end;
java中的sql调用语句:
String sql="{call 存储过程名(?,?,?)}"
Register(3,Number);
String sql="{?=call 函数名(?,?)}"
jdbc.properties:
- url=jdbc\:oracle\:thin\:@localhost\:1521\:orcl
- driverClass=oracle.jdbc.OracleDriver
- account=scott
- password=tiger
mybatis.xml:
- <?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>
- <properties resource="cn/et/mybatis/lesson01/jdbc.properties">
- </properties>
- <!--
- 配置连接数据库的环境 development开发环境
- -->
- <environments default="development">
- <environment id="development">
- <!-- 事务交给JDBC来管理 也就是通过 Connection的 commit和rollback管事事务 -->
- <transactionManager type="JDBC"/>
- <dataSource type="POOLED">
- <property name="driver" value="${driverClass}"/>
- <property name="url" value="${url}"/>
- <property name="username" value="${account}"/>
- <property name="password" value="${password}"/>
- </dataSource>
- </environment>
- </environments>
- <mappers>
- <mapper resource="cn/et/mybaits/lesson02/func/call_mapper.xml" />
- </mappers>
- </configuration>
call_mapper.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">
- <!--
- 接口映射
- namespace必需跟接口的全名一致
- -->
- <mapper namespace="cn.et.mybaits.lesson02.func.CallMapper">
- <!-- statementType: STATEMENT就是搞sql语句 CALLABLE就是搞存储过程和函数-->
- <select id="callProcedure" parameterType="map" statementType="CALLABLE">
- {
- call prg_add(
- #{p1,mode=IN,jdbcType=NUMERIC},
- #{p2,mode=IN,jdbcType=NUMERIC},
- #{p3,mode=OUT,jdbcType=NUMERIC}
- )
- }
- </select>
- <select id="callFunction" parameterType="map" statementType="CALLABLE">
- {
- #{p3,mode=OUT,jdbcType=NUMERIC}=call fun_add(
- #{p1,mode=IN,jdbcType=NUMERIC},
- #{p2,mode=IN,jdbcType=NUMERIC}
- )
- }
- </select>
- </mapper>
CallMapper接口:
- package cn.et.mybaits.lesson02.func;
- import java.util.Map;
- public interface CallMapper {
- public void callProcedure(Map map);
- public void callFunction(Map map);
- }
TestMabatis.Java:
- package cn.et.mybaits.lesson02.func;
- import java.io.InputStream;
- import java.util.HashMap;
- import java.util.List;
- import java.util.Map;
- import org.apache.ibatis.session.SqlSession;
- import org.apache.ibatis.session.SqlSessionFactory;
- import org.apache.ibatis.session.SqlSessionFactoryBuilder;
- import org.junit.Test;
- public class TestMybatis {
- public static SqlSession getSession(){
- String resource = "/cn/et/mybaits/lesson02/mybatis.xml";
- InputStream inputStream = TestMybatis.class.getResourceAsStream(resource);
- SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
- //打开会话
- SqlSession session = sqlSessionFactory.openSession();
- return session;
- }
- /* public static void main(String[] args) {
- SqlSession session = getSession();
- CallMapper cm = session.getMapper(CallMapper.class);
- Map map = new HashMap();
- map.put("p1", 145);
- map.put("p2", 100);
- cm.callProcedure(map);
- System.out.println(map.get("p3"));
- }
- */
- public static void main(String[] args) {
- SqlSession session = getSession();
- CallMapper cm = session.getMapper(CallMapper.class);
- Map map = new HashMap();
- map.put("p1", 1243);
- map.put("p2", 1000);
- cm.callFunction(map);
- System.out.println(map.get("p3"));
- }
- }