定义一个存储过程:
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"));
}
}