JDBC & PreparedStatement
The PreparedStatement interface is extended Statement, with extra feature to send a pre-compiled SQL statementwith parameters. For create, insert, update or delete statement, uses executeUpdate(sql);
for select query, you uses executeQuery(sql)
1: Example of PreparedStatement for update a record into a table
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class PreparedStatementUpdateDemo {
private static final String DB_DRIVER_TEST = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION_TEST = "jdbc:oracle:thin:@localhost:1521:mytest";
private static final String DB_USER_TEST = "user";
private static final String DB_PASSWORD_TEST = "password";
public static void main(String[] args) {
try {
updateRecordToTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void updateRecordToTable() throws SQLException {
Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String updateTableSQL = "UPDATE testuser SET USERNAME = ? "+ " WHERE USER_ID = ?";
try {
dbConnection = getDBConnection();
preparedStatement = dbConnection.prepareStatement(updateTableSQL);
preparedStatement.setString(1, "ahmad_new_value");
preparedStatement.setInt(2, 1004);
// execute update SQL stetement here
preparedStatement.executeUpdate();
System.out.println("Record is updated in testuser table");
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER_TEST);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION_TEST, DB_USER_TEST,DB_PASSWORD_TEST);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}
After running the code you can view in testuser table one record is updated.
The Result is :
The username of user_id = 1004 is updated to a new value 'ahmad_new_value'.
You can insert/delete records in testuser table similarly.
2:Example of PreparedStatement to select a list of records from a table
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PreparedStatementSelectDemo{
private static final String DB_DRIVER_TEST = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION_TEST = "jdbc:oracle:thin:@localhost:1521:mytest";
private static final String DB_USER_TEST = "user";
private static final String DB_PASSWORD_TEST = "password";
public static void main(String[] args) {
try {
recordsFromTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void recordsFromTable() throws SQLException {
Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String selectSQL = "SELECT USER_ID, USERNAME FROM testuser WHERE USER_ID = ?";
try {
dbConnection = getDBConnection();
preparedStatement = dbConnection.prepareStatement(selectSQL);
preparedStatement.setInt(1, 1004);
// execute select SQL stetement here
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
String userid = rs.getString("USER_ID");
String username = rs.getString("USERNAME");
System.out.println("userid : " + userid);
System.out.println("username : " + username);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER_TEST);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION_TEST, DB_USER_TEST,DB_PASSWORD_TEST);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}
Result after running the code
List of the records with user_id = 1004 are retrieved from table testuser and displayed.
3 : Example of Insert records in batch process, via JDBC PreparedStatement
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class BatchUpdateDemo {
private static final String DB_DRIVER_TEST = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION_TEST = "jdbc:oracle:thin:@localhost:1521:mytest";
private static final String DB_USER_TEST = "user";
private static final String DB_PASSWORD_TEST = "password";
public static void main(String[] args) {
try {
batchInsertRecordsIntoTable();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void batchInsertRecordsIntoTable() throws SQLException {
Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String insertTableSQL = "INSERT INTO testuser"+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"+ "(?,?,?,?)";
try {
dbConnection = getDBConnection();
preparedStatement = dbConnection.prepareStatement(insertTableSQL);
dbConnection.setAutoCommit(false);
preparedStatement.setInt(1, 101);
preparedStatement.setString(2, "wakil101");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();
preparedStatement.setInt(1, 102);
preparedStatement.setString(2, "wakil102");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();
preparedStatement.setInt(1, 103);
preparedStatement.setString(2, "wakil103");
preparedStatement.setString(3, "system");
preparedStatement.setTimestamp(4, getCurrentTimeStamp());
preparedStatement.addBatch();
preparedStatement.executeBatch();
dbConnection.commit();
System.out.println("Record is inserted into testuser table");
} catch (SQLException e) {
System.out.println(e.getMessage());
dbConnection.rollback();
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER_TEST);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION_TEST, DB_USER_TEST,DB_PASSWORD_TEST);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
private static java.sql.Timestamp getCurrentTimeStamp() {
java.util.Date today = new java.util.Date();
return new java.sql.Timestamp(today.getTime());
}
}
After running code the Result are :
3 records are inserted into database via batch update process.
JDBC and Stored Procedure
1 : Example of Stored procedure IN parameter via JDBC CallableStatement.
The code will show you how to pass IN parameter in Stored procedure and call it via jdbc CallableStatement
First create a stored procedure in oracle database as below :
CREATE OR REPLACE PROCEDURE insertTestuser(
p_userid IN testuser.USER_ID%TYPE,
p_username IN testuser.USERNAME%TYPE,
p_createdby IN testuser.CREATED_BY%TYPE,
p_date IN testuser.CREATED_DATE%TYPE)
IS
BEGIN
INSERT INTO testuser ("USER_ID", "USERNAME", "CREATED_BY", "CREATED_DATE")
VALUES (p_userid, p_username,p_createdby, p_date);
COMMIT;
END;
/
Second Calls Stored Procedure via CallableStatement
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class CallableStatementINParameterDemo {
private static final String DB_DRIVER_TEST = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION_TEST = "jdbc:oracle:thin:@localhost:1521:mytest";
private static final String DB_USER_TEST = "user";
private static final String DB_PASSWORD_TEST = "password";
public static void main(String[] args) {
try {
callStoredProcINParameter();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void callStoredProcINParameter() throws SQLException {
Connection dbConnection = null;
CallableStatement callableStatement = null;
String insertStoreProc = "{call insertTestuser(?,?,?,?)}";
try {
dbConnection = getDBConnection();
callableStatement = dbConnection.prepareCall(insertStoreProc);
callableStatement.setInt(1, 1000);
callableStatement.setString(2, "wakil");
callableStatement.setString(3, "system");
callableStatement.setDate(4, getCurrentDate());
// execute insertDBUSER store procedure here
callableStatement.executeUpdate();
System.out.println("Record is inserted into testuser table
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (callableStatement != null) {
callableStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER_TEST);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION_TEST, DB_USER_TEST,DB_PASSWORD_TEST);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
private static java.sql.Date getCurrentDate() {
java.util.Date today = new java.util.Date();
return new java.sql.Date(today.getTime());
}
}
Result is : When above example is executed, a new record will be inserted into database via stored procedure.
2 : Example of Stored procedure OUT parameter via JDBC CallableStatement.
The code will show you how to pass OUT parameter in Stored procedure and call it via jdbc CallableStatement
First create a Stored Procedure with IN and OUT parameters. Later, calls it via JDBC.
CREATE OR REPLACE PROCEDURE getTestuserByid(
p_userid IN DBUSER.USER_ID%TYPE,
o_username OUT DBUSER.USERNAME%TYPE,
o_createdby OUT DBUSER.CREATED_BY%TYPE,
o_date OUT DBUSER.CREATED_DATE%TYPE)
IS
BEGIN
SELECT USERNAME , CREATED_BY, CREATED_DATE
INTO o_username, o_createdby, o_date
FROM testuser WHERE USER_ID = p_userid;
END;
/
Second Call it via JDBC CallableStatement
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class CallableStatementOUTParameterDemo{
private static final String DB_DRIVER_TEST = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION_TEST = "jdbc:oracle:thin:@localhost:1521:mytest";
private static final String DB_USER_TEST = "user";
private static final String DB_PASSWORD_TEST = "password";
public static void main(String[] args) {
try {
callStoredProcOUTParameter();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void callStoredProcOUTParameter() throws SQLException {
Connection dbConnection = null;
CallableStatement callableStatement = null;
String getTestuserByid = "{call getTestuserByid(?,?,?,?)}";
try {
dbConnection = getDBConnection();
callableStatement = dbConnection.prepareCall(getTestuserByid);
callableStatement.setInt(1, 10);
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4, java.sql.Types.DATE);
// execute getTestuserByid store procedure here
callableStatement.executeUpdate();
String userName = callableStatement.getString(2);
String createdBy = callableStatement.getString(3);
Date createdDate = callableStatement.getDate(4);
System.out.println("UserName : " + userName);
System.out.println("CreatedBy : " + createdBy);
System.out.println("CreatedDate : " + createdDate);
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (callableStatement != null) {
callableStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER_TEST);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION_TEST, DB_USER_TEST,DB_PASSWORD_TEST);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}
JDBC CallableStatement CURSOR
3 : Example of Stored procedure INOUT parameter via JDBC CallableStatement
JDBC example to call above stored procedure, cast the returned CURSOR to ResultSet and
loop through the records sequentially.
First Create a StoredProcedure
CREATE OR REPLACE PROCEDURE getTestuserCursor(
p_username IN DBUSER.USERNAME%TYPE,
c_dbuser OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_dbuser FOR
SELECT * FROM testuser WHERE USERNAME LIKE p_username || '%';
END;
/
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
public class CallableStatementCURSORDemo {
private static final String DB_DRIVER_TEST = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION_TEST = "jdbc:oracle:thin:@localhost:1521:mytest";
private static final String DB_USER_TEST = "user";
private static final String DB_PASSWORD_TEST = "password";
public static void main(String[] args) {
try {
callStoredProcCURSORParameter();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}
private static void callStoredProcCURSORParameter() throws SQLException {
Connection dbConnection = null;
CallableStatement callableStatement = null;
ResultSet rs = null;
String getTestuserCursorSql = "{call getTestuserCursor(?,?)}";
try {
dbConnection = getDBConnection();
callableStatement = dbConnection.prepareCall(getTestuserCursorSql);
callableStatement.setString(1, "wakil"); callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
// execute getTestuserCursor store procedure here
callableStatement.executeUpdate();
// get cursor and cast it to ResultSet
rs = (ResultSet) callableStatement.getObject(2);
while (rs.next()) {
String userid = rs.getString("USER_ID");
String userName = rs.getString("USERNAME");
String createdBy = rs.getString("CREATED_BY");
String createdDate = rs.getString("CREATED_DATE");
System.out.println("UserName : " + userid);
System.out.println("UserName : " + userName);
System.out.println("CreatedBy : " + createdBy);
System.out.println("CreatedDate : " + createdDate);
}
} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
if (rs != null) {
rs.close();
}
if (callableStatement != null) {
callableStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER_TEST);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
} try {
dbConnection = DriverManager.getConnection(DB_CONNECTION_TEST, DB_USER_TEST,DB_PASSWORD_TEST);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}
4 : JDBC Transaction CURSOR
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TransactionDemo {
private static final String DB_DRIVER_TEST = "oracle.jdbc.driver.OracleDriver";
private static final String DB_CONNECTION_TEST = "jdbc:oracle:thin:@localhost:1521:mytest";
private static final String DB_USER_TEST = "user";
private static final String DB_PASSWORD_TEST = "password";
public static void main(String[] args) throws SQLException {
Connection dbConnection = null;
PreparedStatement preparedStatementInsert = null;
PreparedStatement preparedStatementUpdate = null;
String insertTableSQL = "INSERT INTO testuser"
+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
+ "(?,?,?,?)";
String updateTableSQL = "UPDATE testuser SET USERNAME =? "+ "WHERE USER_ID = ?";
try {
dbConnection = getDBConnection();
dbConnection.setAutoCommit(false);
preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
preparedStatementInsert.setInt(1, 999);
preparedStatementInsert.setString(2, "wakil101");
preparedStatementInsert.setString(3, "system");
preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
preparedStatementInsert.executeUpdate();
preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
preparedStatementUpdate.setString(1, "new string");
preparedStatementUpdate.setInt(2, 999);
preparedStatementUpdate.executeUpdate();
dbConnection.commit();
} catch (SQLException e) {
System.out.println(e.getMessage());
dbConnection.rollback();
} finally {
if (preparedStatementInsert != null) {
preparedStatementInsert.close();
}
if (preparedStatementUpdate != null) {
preparedStatementUpdate.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER_TEST);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION_TEST, DB_USER_TEST,DB_PASSWORD_TEST);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
} private static java.sql.Timestamp getCurrentTimeStamp() {
java.util.Date today = new java.util.Date();
return new java.sql.Timestamp(today.getTime());
}
}
No comments:
Post a Comment