JAVA調用資料庫預存程序

下面將舉出JAVA對ORACLE資料庫預存程序的調用

        

ConnUtils串連工具類：用來擷取串連、釋放資源

package com.ljq.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 串連工具類
 * 
 * ConnUtils類聲明為final類說明此類不可以被繼承
 * 
 * @author jiqinlin
 * 
*/
public final class ConnUtils {
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String user = "test";
private static String password = "test";

/**
     * 說明要訪問此類只能通過static或單例模式
*/
private ConnUtils() {
    }

// 註冊驅動 (只做一次)
    static {
try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
        }
    }

/**
     * 擷取Connection對象
     * 
     * @return
     * @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
    }

/**
     * 釋放資源
     * 
     * @param rs
     * @param st
     * @param conn
*/
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
                rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
try {
if (st != null)
                    st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
if (conn != null)
try {
                        conn.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
            }
        }
    }
}

            

建立帶出參預存程序代碼：

--帶出參預存程序
CREATE OR REPLACE PROCEDURE stu_proc(v_name OUT VARCHAR2) AS
BEGIN 
SELECT o.sname INTO v_name FROM student o where o.id = 2;
END;

        

使用java調用帶出參的預存程序

package com.ljq.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

public class ProceTest {

public static void main(String[] args) throws Exception {
        Connection conn = null;
        CallableStatement statement = null;
        String sql = "{call stu_proc(?)}";
try {
            conn = ConnUtils.getConnection();
            statement = conn.prepareCall(sql);
            statement.registerOutParameter(1, Types.VARCHAR);
            statement.executeUpdate();
//輸出：lisi
            String sname = statement.getString(1);
            System.out.println(sname);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            ConnUtils.free(null, statement, conn);
        }
    }

}

            

建立帶出入參預存程序代碼

--帶出入參預存程序
CREATE OR REPLACE PROCEDURE stu_proc(v_id IN NUMBER, v_name OUT VARCHAR2) AS
BEGIN 
  SELECT o.sname INTO v_name FROM student o where o.id = v_id;
END;

          

使用JAVA調用帶出入參預存程序

package com.ljq.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

public class ProceTest {

public static void main(String[] args) throws Exception {
        Connection conn = null;
        CallableStatement statement = null;
        String sql = "{call stu_proc(?, ?)}";
try {
            conn = ConnUtils.getConnection();
            statement = conn.prepareCall(sql);
            statement.setInt(1, 1);
            statement.registerOutParameter(2, Types.VARCHAR);
            statement.executeUpdate();
//輸出：zhangsan
            String sname = statement.getString(2);
            System.out.println(sname);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            ConnUtils.free(null, statement, conn);
        }
    }

}