javaEE jdbc获取数据2（存储过程调用和结果集获取）

最新推荐文章于 2021-03-13 06:34:28 发布

原创最新推荐文章于 2021-03-13 06:34:28 发布 · 662 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #javaEE #hibernate #plsql

oracle 同时被 2 个专栏收录

89 篇文章

订阅专栏

javaEE

3 篇文章

订阅专栏

本文介绍了一个使用JDBC调用Oracle存储过程的例子，并展示了如何处理返回的结果集。该示例包括连接数据库、准备并执行存储过程，以及解析结果集的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、jdbc获取数据2（存储过程调用和结果集获取）

package aTest;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;

import com.ImpStr;
/**
 * 通用sql使用
 */
public class ImpSqlCall {
    private static String clssName = "oracle.jdbc.driver.OracleDriver";
    private static String url = "jdbc:oracle:thin:@*********:1521:orcl";
    private static String user = "*******";
    private static String password = "12345678";
    
    
	private static ArrayList<DataStr> getDataStr(ResultSet rs, int numberOfColumns) {
    	ArrayList<DataStr> reslutList = new ArrayList<DataStr>();
    	try{
    		int rownum = 1;
    		while (rs.next()) {
            	DataStr datas = new DataStr();
            	ArrayList strsList = new ArrayList();
            	for (int i = 1; i <= numberOfColumns; i++) {
                    Object colVObject = rs.getObject(i);
                    String colValue = ImpStr.getStrFromObject(colVObject);
                    strsList.add(colValue);
                }
                datas.setRownum(rownum);
                datas.setStrsList(strsList);
                reslutList.add(datas);
                rownum++;
            }
    	} catch (Exception e) {
    		e.printStackTrace();
    	}    	
		return reslutList;
	}
    
	/**
	 * @param callSql  类型是PACK_TEST.TESTB2(?,?) 参数是string类型的
	 * @param parList
	 * @return
	 */
    public static synchronized ArrayList<DataStr> getStrMapList(final String callSql, final ArrayList parList)
    {
        ArrayList<DataStr> reslutList = new ArrayList<DataStr>();
        Connection Conn = null;
        Statement Stmt = null;
        ResultSet rs = null;
        String mCallSql = "{ call "+callSql + " }";
//        System.out.println("\n/*****开始执行时间------------>" + CTools.getCurrentTimeString() + "************/");
        try {
			Class.forName(clssName);
			Conn = DriverManager.getConnection(url,user,password);
			CallableStatement proc = null;
			
//			proc = Conn.prepareCall("{ call PACK_TEST.TESTB2(?,?) }");
			proc = Conn.prepareCall(mCallSql);
			for(int i=0; i<parList.size(); i++){
				proc.setString(i+2, ImpStr.getStrFromObject(parList.get(i)));
			}
			proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
			proc.execute();
			rs = (ResultSet)proc.getObject(1);
			ResultSetMetaData RsMeta = rs.getMetaData();              
            int numberOfColumns = RsMeta.getColumnCount();//字段个数
            
            reslutList = getDataStr(rs, numberOfColumns);

			
		} catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (Stmt != null) { try {Stmt.close();} catch (Exception e) {};}
            if (Conn != null) { try {Conn.close();} catch (Exception e) {};}
            
//            System.out.println("/*****结束时间------------>"    + CTools.getCurrentTimeString() + "************/");
        }
        return reslutList;
    }



	public static void main(String[] args)
    {
    	String sql = "PACK_TEST.TESTB2(?,?)";    	
    	ArrayList list = new ArrayList();
    	list.add("fsdfasf");
    	
    	ArrayList<DataStr> reslutLis = getStrMapList(sql, list);
    	for (int i = 0; i < reslutLis.size(); i++){
    		DataStr data = reslutLis.get(i);
        	ArrayList strs = data.getStrsList();
        	System.out.println("data.getRownum()=="+data.getRownum() + "  strs[i]=="+strs.get(0).toString());
    	}
    }
}

  type REFCURSORTYPE is REF CURSOR;

  PROCEDURE TESTB2( PARA2 OUT REFCURSORTYPE, PARA1 IN VARCHAR2) AS  
    v_sql varchar2(200);
    mycur REFCURSORTYPE;
  BEGIN
    
    v_sql := 'select ywlsh from TJ_CZW_YW t where rownum < 10';
    
    OPEN PARA2 FOR v_sql;
  END TESTB2;