/**
* ==========
* REF CURSOR
* ==========
*
* Another option of executing dynamic SQL from JDBC is provided in this
* example. Keep in mind that this example will only work with Oracle8i and
* higher. In this case, the procedure uses a PL/SQL procedure which returns
* a REF CURSOR.
*
* A REF CURSOR is similar a pointer in the C programming language. It points
* to rows retrieved from the database using a PL/SQL cursor. The example I
* provide in this class uses a REF CURSOR to point to the result set
* returned by a SELECT statement that retrieves rows from the DEPT table
* using a PL/SQL cursor.
*
* In this example, I call a PL/SQL procedure named "get_dept_ref_cursor" which
* returns a variable of type "t_ref_cursor".
*
* Stored procedures can return user-defined types, or cursor variables, of the
* REF CURSOR category. This output is equivalent to a database cursor or a
* JDBC result set. A REF CURSOR essentially encapsulates the results of a
* query.
*
* Advantages of using a REF CURSOR are:
*
* 1.) Code Reusability
*
* The same package procedure could be used for other Java and non-Java
* applications.
*
* 2.) Load Balancing.
*
*
*/public class RefCursorExample { final static String driverClass = "oracle.jdbc.driver.OracleDriver";
final static String connectionURL = "jdbc:oracle:thin:@localhost:1521:CUSTDB";
final static String userID = "scott";
final static String userPassword = "tiger";
Connection con = null;
/**
* Construct a RefCursorExample object. This constructor will create an Oracle
* database connection.
*/
public RefCursorExample() { try { System.out.print(" Loading JDBC Driver -> " + driverClass + "\n");
Class.forName(driverClass).newInstance(); System.out.print(" Connecting to -> " + connectionURL + "\n");
this.con = DriverManager.getConnection(connectionURL, userID, userPassword);
System.out.print(" Connected as -> " + userID + "\n\n"); } catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} }
/**
* This method is used to return a REF CURSOR that will be used to retrieve
* data from a result set. This REF CUSROR is retrieved by the JDBC program
* into a ResultSet.
*
* This method Uses the OracleCallableStatement and OracleResultSet classes.
*/
public void performRefCursor() { OracleCallableStatement oraCallStmt = null;
OracleResultSet deptResultSet = null; System.out.println("Using OracleCallableStatement / OracleResultSet");
System.out.println("-----------------------------------------------"); try { oraCallStmt = (OracleCallableStatement) con.prepareCall(
"{? = call ref_cursor_package.get_dept_ref_cursor(?)}"
);
oraCallStmt.registerOutParameter(1, OracleTypes.CURSOR);
oraCallStmt.setInt(2, 104);
oraCallStmt.execute(); deptResultSet = (OracleResultSet) oraCallStmt.getCursor(1); while (deptResultSet.next()) {
System.out.println(
" - " +
deptResultSet.getString(2) + " (" + deptResultSet.getInt(1) + "), " +
deptResultSet.getString(3)
);
}
System.out.println();
oraCallStmt.close();
} catch (SQLException e) { e.printStackTrace(); } }
/**
* This method is used to return a REF CURSOR that will be used to retrieve
* data from a result set. This REF CUSROR is retrieved by the JDBC program
* into a ResultSet.
*
* This method Uses the the regular CallableStatement and ResultSet classes.
*/
public void performRefCursor2() { CallableStatement cstmt = null;
ResultSet rset = null; System.out.println("Using CallableStatement / ResultSet");
System.out.println("-----------------------------------"); try { cstmt = con.prepareCall(
"{? = call ref_cursor_package.get_dept_ref_cursor(?)}"
);
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setInt(2, 104);
cstmt.execute(); rset = (ResultSet) cstmt.getObject(1);
while (rset.next()) {
System.out.println(
" - " +
rset.getString(2) + " (" + rset.getInt(1) + "), " +
rset.getString(3)
);
}
System.out.println();
cstmt.close();
} catch (SQLException e) { e.printStackTrace(); } }
/**
* Close down Oracle connection.
*/
public void closeConnection() { try {
System.out.print(" Closing Connection...\n");
con.close();
} catch (SQLException e) {
e.printStackTrace();
} }
/**
* Sole entry point to the class and application.
* @param args Array of String arguments.
* @exception java.lang.InterruptedException
* Thrown from the Thread class.
*/
public static void main(String[] args)
throws java.lang.InterruptedException { RefCursorExample mainPrg = new RefCursorExample();
mainPrg.performRefCursor();
mainPrg.performRefCursor2();
mainPrg.closeConnection(); }}
* ==========
* REF CURSOR
* ==========
*
* Another option of executing dynamic SQL from JDBC is provided in this
* example. Keep in mind that this example will only work with Oracle8i and
* higher. In this case, the procedure uses a PL/SQL procedure which returns
* a REF CURSOR.
*
* A REF CURSOR is similar a pointer in the C programming language. It points
* to rows retrieved from the database using a PL/SQL cursor. The example I
* provide in this class uses a REF CURSOR to point to the result set
* returned by a SELECT statement that retrieves rows from the DEPT table
* using a PL/SQL cursor.
*
* In this example, I call a PL/SQL procedure named "get_dept_ref_cursor" which
* returns a variable of type "t_ref_cursor".
*
* Stored procedures can return user-defined types, or cursor variables, of the
* REF CURSOR category. This output is equivalent to a database cursor or a
* JDBC result set. A REF CURSOR essentially encapsulates the results of a
* query.
*
* Advantages of using a REF CURSOR are:
*
* 1.) Code Reusability
*
* The same package procedure could be used for other Java and non-Java
* applications.
*
* 2.) Load Balancing.
*
*
*/public class RefCursorExample { final static String driverClass = "oracle.jdbc.driver.OracleDriver";
final static String connectionURL = "jdbc:oracle:thin:@localhost:1521:CUSTDB";
final static String userID = "scott";
final static String userPassword = "tiger";
Connection con = null;
/**
* Construct a RefCursorExample object. This constructor will create an Oracle
* database connection.
*/
public RefCursorExample() { try { System.out.print(" Loading JDBC Driver -> " + driverClass + "\n");
Class.forName(driverClass).newInstance(); System.out.print(" Connecting to -> " + connectionURL + "\n");
this.con = DriverManager.getConnection(connectionURL, userID, userPassword);
System.out.print(" Connected as -> " + userID + "\n\n"); } catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} }
/**
* This method is used to return a REF CURSOR that will be used to retrieve
* data from a result set. This REF CUSROR is retrieved by the JDBC program
* into a ResultSet.
*
* This method Uses the OracleCallableStatement and OracleResultSet classes.
*/
public void performRefCursor() { OracleCallableStatement oraCallStmt = null;
OracleResultSet deptResultSet = null; System.out.println("Using OracleCallableStatement / OracleResultSet");
System.out.println("-----------------------------------------------"); try { oraCallStmt = (OracleCallableStatement) con.prepareCall(
"{? = call ref_cursor_package.get_dept_ref_cursor(?)}"
);
oraCallStmt.registerOutParameter(1, OracleTypes.CURSOR);
oraCallStmt.setInt(2, 104);
oraCallStmt.execute(); deptResultSet = (OracleResultSet) oraCallStmt.getCursor(1); while (deptResultSet.next()) {
System.out.println(
" - " +
deptResultSet.getString(2) + " (" + deptResultSet.getInt(1) + "), " +
deptResultSet.getString(3)
);
}
System.out.println();
oraCallStmt.close();
} catch (SQLException e) { e.printStackTrace(); } }
/**
* This method is used to return a REF CURSOR that will be used to retrieve
* data from a result set. This REF CUSROR is retrieved by the JDBC program
* into a ResultSet.
*
* This method Uses the the regular CallableStatement and ResultSet classes.
*/
public void performRefCursor2() { CallableStatement cstmt = null;
ResultSet rset = null; System.out.println("Using CallableStatement / ResultSet");
System.out.println("-----------------------------------"); try { cstmt = con.prepareCall(
"{? = call ref_cursor_package.get_dept_ref_cursor(?)}"
);
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setInt(2, 104);
cstmt.execute(); rset = (ResultSet) cstmt.getObject(1);
while (rset.next()) {
System.out.println(
" - " +
rset.getString(2) + " (" + rset.getInt(1) + "), " +
rset.getString(3)
);
}
System.out.println();
cstmt.close();
} catch (SQLException e) { e.printStackTrace(); } }
/**
* Close down Oracle connection.
*/
public void closeConnection() { try {
System.out.print(" Closing Connection...\n");
con.close();
} catch (SQLException e) {
e.printStackTrace();
} }
/**
* Sole entry point to the class and application.
* @param args Array of String arguments.
* @exception java.lang.InterruptedException
* Thrown from the Thread class.
*/
public static void main(String[] args)
throws java.lang.InterruptedException { RefCursorExample mainPrg = new RefCursorExample();
mainPrg.performRefCursor();
mainPrg.performRefCursor2();
mainPrg.closeConnection(); }}
解决方案 »
- 请教个线程的问题,请高手解答,在线求解
- Struts2+spring2+Hibernate3.3整合出错
- java SQL查询数组顺序乱,求解决!!!
- javax.servlet.ServletException: Cannot get value for expression
- webservice 异常 求助
- 急求一个 HQL语句
- webwork2——最优雅的框架技术!
- 问一个关于HIBERNATE,不知道深不深奥的问题
- sql语句如何写?
- js中提交表单 action进不去
- 问个比较傻的问题(关于servlet里url的定制)
- mysql问题 只有我不会 大家帮我看看谢谢了 救生这么多分了
是个不错的东西.