springboot2使用Jdbctemplate进行oracle存储过程调用

第一步.修改pom.xml，因为jdbctemplate在spring-boot-starter-jdbc里面，所以要引入spring-boot-starter-web依赖

<dependency>
	<groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

第二步：使用注解定义数据源和jdbctemplate

package com.config;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import com.zaxxer.hikari.HikariDataSource;
@Configuration
public class DataSourceConfig {
 /**
     * 主数据源配置 ds1数据源
     */
    @Primary
    @Bean(name = "ds1Properties")
    @ConfigurationProperties(prefix = "spring.datasource.db1")
    public DataSourceProperties ds1DataSourceProperties() {
        return new DataSourceProperties();
    }
    /**
     * 主数据源 ds1数据源
     */
    @Primary
    @Bean(name = "dataSource1")
    public DataSource ds1DataSource(@Qualifier("ds1Properties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }
    @Bean(name = "ds1JdbcTemplate")
    public JdbcTemplate getJdbcTemplate(@Qualifier(value = "dataSource1")DataSource ds) {
     return new JdbcTemplate(ds);
    }
}

第三步：将jdbcTemplate注入到dao类，使用SimpleJdbcCall进行存储过程调用

package com.dao;
import java.util.HashMap;
import java.util.Map;
import javax.annotation.Resource;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional(rollbackFor = Exception.class)
public class CommDAO {
 @Autowired
 @Qualifier(value = "ds1JdbcTemplate")
 JdbcTemplate jdbcTemplate;
 public void callProcedure(String ProStr,Map<String,Object> inMap) {
//  调用存储过程
  SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(this.jdbcTemplate.getDataSource());
  simpleJdbcCall.withCatalogName(ProStr.substring(0, ProStr.indexOf('.'))).withProcedureName(ProStr.substring(ProStr.indexOf('.')+1, ProStr.length()));
  SqlParameterSource parameterSource = new MapSqlParameterSource().addValues(inMap);
  Map<String, Object> out = simpleJdbcCall.execute(parameterSource);
 }
}

第四步：在service中定义一个新方法，在service中调用dao类中刚刚定义的调用存储过程的方法，同时在controller里面定义一个方法调用service这个方法

package com.service;
import java.util.Map;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.dao.CommDAO;
@Service
public class UserService {
 @Autowired
 CommDAO dao;
 public void call(String ProStr,Map<String,Object> inMap) {
  dao.callProcedure(ProStr, inMap);
 }
}

package com.controller;
import java.io.File;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.UUID;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import com.service.UserService;
@Controller
public class HelloWorldController {
    @RequestMapping("/callProdure")
    @ResponseBody
    public String call() {
     Map<String,Object> inMap = new HashMap<String,Object>();
     inMap.put("pi_param", "hello");
     userService.call("pkg_test_temp.get_testxx", inMap);
  return "call success";
    }
}

第五步：定义存储过程

CREATE OR REPLACE PACKAGE  pkg_test_temp AS 
   PROCEDURE get_testxx(
   pi_param    IN   VARCHAR2,
      po_fhz      OUT  VARCHAR2,
      po_msg      OUT  VARCHAR2
   );
END pkg_test_temp;
/
show err

CREATE OR REPLACE PACKAGE BODY pkg_test_temp AS
PROCEDURE get_testxx(pi_param IN VARCHAR2,po_fhz OUT VARCHAR2,po_msg OUT VARCHAR2)
IS
 v_max INT;
 v_content varchar2(200);
BEGIN
 BEGIN
  SELECT MAX(pid)
    INTO v_max
    FROM temp_test_20200821;
 EXCEPTION
  WHEN OTHERS THEN
   po_fhz := 'ECOMM000002';
   po_msg := '获取最大值有误';
   RETURN;
 END;
 v_content := to_char(sysdate,'yyyymmddhh24miss')||'+'||pi_param;
 INSERT INTO temp_test_20200821(PID,CONTENT) VALUES(v_max+1,v_content);
 po_fhz := '1';
 po_msg := '调用成功'||pi_param;
EXCEPTION
 WHEN OTHERS THEN
  po_fhz := 'ECOMM000001';
  po_msg := '调用存储过程:'||'pkg_test_temp'||'发生错误';
  RETURN;
 END get_testxx;
END pkg_test_temp;
/
show err

测试截图