jdbcTemplate调用Oracle存储过程返回List集合
作者:xyzc(cr10210206@163)
1.编写存储过程
jdbctemplate查询一条数据-- 模糊查询  返回多条数据
CREATE OR REPLACE PROCEDURE P_EMP_SELECT
(RESULTLIST OUT SYS_REFCURSOR,V_ID IN NUMBER,V_NAME IN VARCHAR2)
IS
SQL_STR VARCHAR2(500);
BEGIN
    SQL_STR:='SELECT * FROM EMP WHERE 1=1 ';
    DBMS_OUTPUT.put_line('V_ID='||V_ID || '  V_NAME='||V_NAME);
    IF(V_ID<>0) THEN
    BEGIN
      SQL_STR:=SQL_STR || ' AND EMPNO>= ' || V_ID;
    END;
    END IF;
    IF(V_NAME IS NOT NULL) THEN -- 判断字符串是否为空
    BEGIN
      SQL_STR:=SQL_STR || ' AND ENAME LIKE '''|| V_NAME ||'%'' '; -- 字符串是四个单引号 ''A%''
    END;
    END IF;
    DBMS_OUTPUT.put_line(' SQL_STR:'||SQL_STR);-- 输出SQL语句
    OPEN RESULTLIST FOR SQL_STR;
END;
-- 测试
DECLARE
MYCRS SYS_REFCURSOR;-- 注意这里用分号；
V_EMP EMP%ROWTYPE;
BEGIN
-- 调用过程，返回的是已经打开的CURSOR
P_EMP_SELECT(MYCRS,7900,'');
LOOP
    FETCH MYCRS INTO V_EMP;
    EXIT WHEN MYCRS%NOTFOUND;
    DBMS_OUTPUT.put_line(V_EMP.EMPNO||'  '||V_EMP.ENAME);
END LOOP;
CLOSE MYCRS;
END;
2.编写Java代码
package st;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import oracle.jdbc.driver.OracleTypes;
import org.springframework.dao.DataAccessException;
import org.CallableStatementCallback;
import org.JdbcTemplate;
/**
* 测试Oracle存储过程
* */
public class TestOraclePro {
   
    private static JdbcTemplate jdbcTemplate = TestDao.getJdbcTemplateOracle();
   
    /**
    * 测试
    * */
    public static void main(String[] args) {
        TestOraclePro test = new TestOraclePro();
        List<HashMap<String, Object>> result = stPro();
        System.out.println("\nresult:\n" + result);
        /**
        * 返回的结果：
        *
        * [{DEPTNO=20, COMM=null, HIREDATE=1980-12-17 00:00:00.0, MGR=7902, SAL=800, JOB=CLERK, ENAME=SMITH, EMPNO=7369},
        *  {DEPTNO=30, COMM=300, HIREDATE=1981-02-20 00:00:00.0, MGR=7698, SAL=1600, JOB=SALESMAN, ENAME=ALLEN, EMPNO=7499},
        *  {DEPTNO=30, COMM=500, HIREDATE=1981-02-22 00:00:00.0, MGR=7698, SAL=1250, JOB=SALESMAN, ENAME=WARD, EMPNO=7521},
        *  {DEPTNO=20, COMM=null, HIREDATE=1981-04-02 00:00:00.0, MGR=7839, SAL=2975, JOB=MANAGER, ENAME=JONES, EMPNO=7566},
        *  {DEPTNO=30, COMM=1400, HIREDATE=1981-09-28 00:00:00.0, MGR=7698, SAL=1250, JOB=SALESMAN, ENAME=MARTIN, EMPNO=7654},
        *  {DEPTNO=30, COMM=null, HIREDATE=1981-05-01 00:00:00.0, MGR=7839, SAL=2850, JOB=MANAGER, ENAME=BLAKE, EMPNO=7698},
        *  {DEPTNO=10, COMM=null, HIREDATE=1981-06-09 00:00:00.0, MGR=7839, SAL=2450, JOB=MANAGER, ENAME=CLARK, EMPNO=7782},
        *  {DEPTNO=20, COMM=null, HIREDATE=1987-04-19 00:00:00.0, MGR=7566, SAL=3000, JOB=ANALYST, ENAME=SCOTT, EMPNO=7788},
        *  {DEPTNO=10, COMM=null, HIREDATE=1981-11-17 00:00:00.0, MGR=null, SAL=5000, JOB=PRESIDENT, ENAME=KING, EMPNO=7839},
        *  {DEPTNO=30, COMM=0, HIREDATE=1981-09-08 00:00:00.0, MGR=7698, SAL=1500, JOB=SALESMAN, ENAME=TURNER, EMPNO=7844},
        *  {DEPTNO=20, COMM=null, HIREDATE=1987-05-23 00:00:00.0, MGR=7788, SAL=1100, JOB=CLERK, ENAME=ADAMS, EMPNO=7876},
        *  {DEPTNO=30, COMM=null, HIREDATE=1981-12-03 00:00:00.0, MGR=7698, SAL=950, JOB=CLERK, ENAME=JAMES, EMPNO=7900},
        *  {DEPTNO=20, COMM=null, HIREDATE=1981-12-03 00:00:00.0, MGR=7566, SAL=3000, JOB=ANALYST, ENAME=FORD, EMPNO=7902},
        *  {DEPTNO=10, COMM=null, HIREDATE=1982-01-23 00:00:00.0, MGR=7782, SAL=1300, JOB=CLERK, ENAME=MILLER, EMPNO=7934}]
        * */
    }
   
    /**
    * 调用存储过程返回 List<HashMap<String, Object>>
    * */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    public List<HashMap<String, Object>> testPro(){
        final String sql = "{call P_EMP_SELECT(?,?,?)}";
        List<HashMap<String, Object>> result = (List<HashMap<String, Object>>) jdbcTemplate.execute(sql,new CallableStatementCallback(){
            public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
                List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>(); 
     
                    cs.registerOutParameter(1, OracleTypes.CURSOR); //输出参数：游标
                    cs.setInt(2, 2000);//输入参数
                    cs.setString(3, "");//输入参数
                   
                    cs.execute();//执行
                   
                    ResultSet rs = null;
                    try {
                       
                        rs = (ResultSet) cs.getObject(1);//获取结果集
                        while (rs.next()) { 
                            HashMap<String, Object> dataMap = new HashMap<String, Object>(); 
                            ResultSetMetaData rsMataData = rs.getMetaData(); 
                            for (int i = 1; i <= ColumnCount(); i++) { 
                                dataMap.ColumnName(i), rs.ColumnName(i))); 
                            } 
                            list.add(dataMap); 
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    finally{
                        if(rs != null){
                            rs.close();
                        }
                    }
                    return list; 
              } 
        });
        return result;
    }
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。