mybatis调用存储过程

输入一个园的半径返回园的周长和面积

首先创建存储过程

create or replace procedure pro_text(s out varchar2,r in out varchar2)
is
v_pi number(10):=3.14;
begin
  s:=v_pi*r*r;
  r:=2*v_pi*r;
end;

创建测试接口

package com.aaa.mybatis.dao;

import java.util.Map;

public interface TextDao {
    public void returnSorC(Map map);
}

创建mapper文件并调用存储过程

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.aaa.mybatis.dao.TextDao">
    <select id="returnSorC" statementType="CALLABLE">
        <!--调用存储过程有专门的标签statementType="CALLABLE" 用{}括起来-->
        {
          call pro_text(
<!--#{第一个是参数名，第二个mode代表出参还是入参或者出入参必须大写,
          jdbcType参数类型jdvcType里没NUMBER,有VARCHAR   ctrl+n输入jdbcType可以看jdbcType都有哪些类型}-->
           #{s,mode=OUT,jdbcType=INTEGER},
            #{r,mode=INOUT,jdbcType=INTEGER}
          )
        }
</select>
</mapper>

创建工具类

package com.aaa.mybatis.util;

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 java.io.IOException;
import java.io.Reader;

public class SqlSessionFactoryUtil {
    private static SqlSessionFactory sessionFactory;
    static {
        Reader reader =null;
        try {
            reader= Resources.getResourceAsReader("mybatis.xml");
            sessionFactory =new SqlSessionFactoryBuilder().build(reader,"myOracle");
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                reader.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    public  static SqlSession getSession(){
        return  sessionFactory.openSession();
    }
}

创建mybatis主配置文件

<?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>

    <settings>
　　　　 <!--打印日志可以看执行的sql语句-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
    <environments default="myOracle">
        <environment id="myOracle">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"></property>
                <property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"></property>
                <property name="username" value="scott"></property>
                <property name="password" value="tiger"></property>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/aaa/mybatis/dao/TextDaoMapper.xml"></mapper><!--指向mapper文件的路径-->
    </mappers>
    
</configuration>

创建测试类

package com.aaa.mybatis.text;

import com.aaa.mybatis.dao.TextDao;
import com.aaa.mybatis.util.SqlSessionFactoryUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Text {
    public static void main(String[] args) {
        //输入一个园的半径返回这个圆的周长和面积 这里我们通过传Map的方式，穿过去两个key,一个值，因为存储过程
　　　　 //有一个出参和一个出入参。
        SqlSession session= SqlSessionFactoryUtil.getSession();
        TextDao mapper = session.getMapper(TextDao.class);
        Map map=new HashMap();
        map.put("r",5);
        map.put("s",null);
        mapper.returnSorC(map);
        System.out.println("这个圆的周长是："+map.get("r")+"面积是："+map.get("s"));
        session.close();
    }
}

测试结果

Logging initialized using \'class org.apache.ibatis.logging.stdout.StdOutImpl\' adapter.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
PooledDataSource forcefully closed/removed all connections.
Opening JDBC Connection
Created connection 701141022.
Setting autocommit to false on JDBC Connection [oracle.jdbc.driver.T4CConnection@29ca901e]
==>  Preparing: { call pro_text( ?, ? ) } ---执行的sql
==> Parameters: 5(Integer)
这个圆的周长是：30面积是：75  ---执行的结果
Resetting autocommit to true on JDBC Connection [oracle.jdbc.driver.T4CConnection@29ca901e]
Closing JDBC Connection [oracle.jdbc.driver.T4CConnection@29ca901e]
Returned connection 701141022 to pool.

Process finished with exit code 0

发表于

2019-05-19 16:35

一天两天、

阅读(10998)

编辑