How do we call a stored procedure from JDBC?

• The foremost step is to create a CallableStatement object. 

• With the Statement and PreparedStatement object ,it is done with an open
Connection object. 

• A CallableStatement object contains a call to a stored procedure.

Code:

CallableStatement cs = con.prepareCall("{call SHOW_Sales}");
ResultSet rs = cs.executeQuery();

How do we call a stored procedure from JDBC?

• The foremost step is to create a CallableStatement object. 

• With the Statement and PreparedStatement object ,it is done with an open
Connection object. 

• A CallableStatement object contains a call to a stored procedure.

Code:

CallableStatement cs = con.prepareCall("{call SHOW_Sales}");
ResultSet rs = cs.executeQuery();

or

• PL/SQL stored procedures are called from the JDBC programs by creating the prepareCall() in the Connection object. 

• A call to the method prepareCall() carries out variable bind parameters as input parameters, output variables & makes an object instance of the CallableStatement class.

• The following line of code implies this:

Callable Statement stproc_ stmt = conn.prepareCall("{call procname(?,?,?)}");

where conn is the instance of the Connection class.

What is the function of setAutoCommit?

• When a connection is created, it is in auto-commit mode.

• This means that each individual SQL statement is to be treated as a single transaction .

• The setAutoCommit will be automatically committed just after getting executed. 

• The way by which two or more statements are clubbed into a transaction to disable the auto-commit mode is :

con.setAutoCommit (false);

• Once auto-commit mode is disabled, no SQL statements will be committed until we call the method ‘commit’ explicitly.

Code :

con.setAutoCommit(false);
PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEE SALES = ? WHERE COF_NAME LIKE ?");
updateSales.setInt(1, 50); updateSales.setString(2, "Colombian");
updateSales.executeUpdate();
PreparedStatement updateTotal =
con.prepareStatement("UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?");
updateTotal.setInt(1, 50);
updateTotal.setString(2, "Colombian");
updateTotal.executeUpdate();
con.commit();
con.setAutoCommit(true);

Differentiate between a Statement and a PreparedStatement.

• A standard Statement is used for creating a Java representation for a literal SQL statement and for executing it on the database. 

• A PreparedStatement is a precompiled Statement. 

• A Statement has to verify its metadata in the database every time. 

• But ,the prepared statement has to verify its metadata in the database only once.

• If we execute the SQL statement, it will go to the STATEMENT. 

• But, if we want to execute a single SQL statement for the multiple number of times, it’ll go to the PreparedStatement.

Define PreparedStatement.

• A Preparedstatement is an SQL statement which is precompiled by the database.

• By precompilation, the prepared statements improve the performance of the SQL commands that are executed multiple times (given that the database supports prepared statements). 

• After compilation, prepared statements may be customized before every execution by the alteration of predefined SQL parameters.

Code:

PreparedStatement pstmt = conn.prepareStatement("UPDATE data= ? WHERE vl = ?");
pstmt.setBigDecimal(1, 1200.00);
pstmt.setInt(2, 192);

What is a Statement ?

• The Statement acts just like a vehicle via which SQL commands are sent.

• By the connection objects, we create the Statement kind of objects.

Statement stmt = conn.createStatement();

• This method returns the object, which implements the Statement interface.

What is represented by the connection object?

• The connection object represents the communication context.

• All the communication with the database is executed via the connection objects only.

• Connection objects are used as the main linking elements.