[JAVA] 以JDBC執行 SQL 及StoreProcedure

這邊來寫一下JAVA連線SQL和執行StoreProcedure的做法.
先附上 oracle官方說明文件
https://docs.oracle.com/javase/tutorial/jdbc/basics/storedprocedures.html

範例如下
 

public static void getSupplierOfCoffee(String coffeeName, String[] supplierName) {

    Connection con = DriverManager.getConnection("jdbc:default:connection");
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    String query =
        "select SUPPLIERS.SUP_NAME " +
        "from SUPPLIERS, COFFEES " +
        "where " +
        "SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +
        "and ? = COFFEES.COF_NAME";

    pstmt = con.prepareStatement(query);
    pstmt.setString(1, coffeeName);
    rs = pstmt.executeQuery();

    if (rs.next()) {
        supplierName[0] = rs.getString(1);
    } else {
        supplierName[0] = null;
    }
}

跟C#的作法可說沒什麼差別
建立Connection後以 PreparedStatement 執行.
最後的執行結果用ResultSet接回來.

叫用StoreProcedure
 

public static void myFunction(String inputParameter) {

    Connection con = DriverManager.getConnection("jdbc:default:connection");

	CallableStatement cs;  
	try {  
		// 設定 CallableStatement  
		cs = con.prepareCall("{call mySP(?)}");  
		  
		// 定義 IN/OUT 參數的 Index 與型態  
		cs.registerOutParameter(1, Types.VARCHAR);  
		  
		// 設定 IN/OUT 參數的 Index 及值  
		cs.setString(1, inputParameter);  
		  
		// 執行並取回 IN/OUT 參數值  
		cs.execute();  
		String outParam = cs.getString(1);           // IN/OUT 參數值  
	} catch (SQLException e) {  
	}  
}

簡單列個範例,
有需要請在自行調整吧.