public String print() {
System.out.println(Calls2DatabaseHelper.exeucteSql("procedure", "Pro_Get_Employee_Name(?,?)", new String[] { "100" },
1));
System.out.println(Calls2DatabaseHelper.exeucteSql("function", "fn_Get_Employee_Name(?)", new String[] { "100" },
1));
String sql ="SELECT FIRST_NAME || ' ' || LAST_NAME Emp_Name FROM EMPLOYEES WHERE EMPLOYEE_ID = 100";
System.out.println(Calls2DatabaseHelper.exeucteSql("select", sql, new String []{},
0));
sql ="SELECT FIRST_NAME || ' ' || LAST_NAME Emp_Name FROM EMPLOYEES WHERE EMPLOYEE_ID = ?";
System.out.println(Calls2DatabaseHelper.exeucteSql("select", sql, new String []{"100"},
0));
sql ="update EMPLOYEES set LAST_NAME = ? WHERE EMPLOYEE_ID = ?";
System.out.println(Calls2DatabaseHelper.exeucteSql("update", sql, new String []{"MAhmoud","100"},
0));
return "";
}
public static DBTransaction getConnection() throws SQLException {
return ADFUtils.getAppImpl().getDBTransaction();
}
public static String exeucteSql(String operaition, String signature, String[] inValues, int numOutValues) {
String returnValue = ""
String begin = " begin ";
String end = " end; ";
String sql = "";
System.out.println(sql);
if (operaition == "procedure") {
signature+=";";
sql = begin + signature + end;
try (CallableStatement cs = getConnection().createCallableStatement(sql, 0)) {
for (int i = 1; i <= inValues.length; i++) {
cs.setString(i, inValues[i - 1]);
}
for (int i = inValues.length + 1; i <= numOutValues + 1; i++) {
cs.registerOutParameter(i, OracleTypes.VARCHAR);
}
cs.execute();
returnValue = cs.getString(2);
} catch (SQLException e) {
e.printStackTrace();
}
}
else if (operaition == "function") {
signature+=";";
signature="?:= "+signature;
sql = begin + signature + end;
System.out.println(sql);
try (CallableStatement cs = getConnection().createCallableStatement(sql, 0)) {
cs.registerOutParameter(1, OracleTypes.VARCHAR);
for (int i = 2; i <= inValues.length+1; i++) {
cs.setString(i, inValues[i - 2]);
}
cs.execute();
returnValue = cs.getString(1);
} catch (SQLException e) {
e.printStackTrace();
}
}
else if(operaition== "select"){
if(inValues.length>0){
try( PreparedStatement cs = getConnection().createPreparedStatement(signature, 0);) {
for (int i = 1; i <= inValues.length; i++) {
cs.setString(i, inValues[i - 1]);
}
ResultSet rs = cs.executeQuery();
if (rs.next()) {
returnValue = rs.getString("Emp_Name");
}
} catch (SQLException e) {
e.printStackTrace();
}
} else{
Statement stmt;
try {
stmt = getConnection().createStatement(0);
ResultSet rs = stmt.executeQuery(signature);
if (rs.next()) {
returnValue = rs.getString("Emp_Name");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
else {
try( PreparedStatement cs = getConnection().createPreparedStatement(signature, 0);) {
if(inValues.length>0){
for (int i = 1; i <= inValues.length; i++) {
cs.setString(i, inValues[i - 1]);
}
}
int numChangedRows = cs.executeUpdate();
getConnection().commit();
System.out.println("numChangedRows="+numChangedRows);
} catch (SQLException e) {
e.printStackTrace();
}
}
return returnValue;
}
Download Link
http://sameh-nassar.blogspot.com/2010/12/calling-sql-statment-inside-java-code.html
Enumeration iterates Vector and Hashtable .
