Java calls the Oracle Stored Procedure

Java calls the Oracle Stored Procedure
Step: 1. Write the Oracle Stored Procedure 2. Write the database to obtain the Connection Tool 3. Write a simple application to call the Stored Procedure Implementation: 1. Oracle stored procedure:

/* Test table */create table test (id varchar2 (32), name varchar2 (32);/* insert data IN Stored Procedures */create or replace procedure insert_procedure (PARA1 IN VARCHAR2, PARA2 IN VARCHAR2) asbegin insert into test (id, name) VALUES (PARA1, PARA2); END insert_procedure; /* returned result set of the stored PROCEDURE */create or replace procedure select_procedure (para_id IN VARCHAR2, name OUT sys_refcursor/* The sys_refcursor type is in sys. */) asbegin open name for select * FROM test WHERE id = para_id; END;

2. JDBC Tool

Import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. SQLException; import java. SQL. statement; public class DBUtil {public static final String DRIVER = oracle. jdbc. driver. oracleDriver; public static final String URL = jdbc: oracle: thin: @ localhost: 1521/orcl; public static final String USERNAME = pfm; public static final String PASSWORD = pfm; /*** use static code blocks Register the database driver */static {try {Class. forName (DRIVER);} catch (ClassNotFoundException e) {e. printStackTrace () ;}}/*** get Connection ** @ return */public static Connection getConnection () {Connection conn = null; try {conn = DriverManager. getConnection (URL, USERNAME, PASSWORD);} catch (SQLException e) {e. printStackTrace ();} return conn;}/*** get Statement ** @ return */public static Statement GetStatement () {Statement st = null; try {st = getConnection (). createStatement ();} catch (SQLException e) {e. printStackTrace ();} return st;}/*** disable ResultSet ** @ param rs */public static void closeResultSet (ResultSet rs) {if (rs! = Null) {try {rs. close ();} catch (SQLException e) {e. printStackTrace () ;}}/ *** close Statement ** @ param st */public static void closeStatement (Statement st) {if (st! = Null) {try {st. close ();} catch (SQLException e) {e. printStackTrace () ;}}/ *** close Connection ** @ param conn */public static void closeConnection (Connection conn) {if (conn! = Null) {try {conn. close ();} catch (SQLException e) {e. printStackTrace () ;}}/ *** close all ** @ param rs * @ param sta * @ param conn */public static void closeAll (ResultSet rs, Statement sta, connection conn) {closeResultSet (rs); closeStatement (sta); closeConnection (conn );}}

3. Call the stored procedure:

Import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import oracle. jdbc. driver. oracleTypes;/*** test the stored procedure for calling **/public class StoredTest {public static void main (String [] args) {insert_call (); // select_call ();} /*** execute the stored procedure to insert data */public static void insert_call () {Connection conn = DBUtil. getConnectio N (); PreparedStatement pst = null; CallableStatement proc = null; // create the object to execute the Stored Procedure try {proc = conn. prepareCall ({call insert_procedure (?,?) }); Proc. setString (1, 1); // set the first input parameter proc. setString (2, hello call); // set the first input parameter proc.exe cute (); // run} catch (SQLException e) {e. printStackTrace ();} finally {try {// close the IO stream proc. close (); DBUtil. closeAll (null, pst, conn);} catch (Exception e) {e. printStackTrace () ;}}/ *** run the Stored Procedure query data */public static void select_call () {Connection conn = DBUtil. getConnection (); CallableStatement stmt; Try {stmt = conn. prepareCall ({call select_procedure (?, ?) }); // Using this call method cannot implement multi-line syntax stmt. setString (1, 1); stmt. registerOutParameter (2, OracleTypes. CURSOR); stmt.exe cute (); ResultSet rs = (ResultSet) stmt. getObject (2); while (rs. next () {System. out. println (rs. getString (name) ;}} catch (SQLException e) {e. printStackTrace ();} finally {DBUtil. closeConnection (conn );}}}