JDBC statements-Prepared,Callable

Hi All,

In this post we will discuss about different connection statements used in JDBC.

There are primarily three types of connection statements:-

1. Statement
2. PreparedStatement
3. CallableStatement

Lets discuss each one of them in detail

Statement interface :- is used to query database from Java,it provides different methods like executeQuery(), executeUpdate() etc.

• Resultset executeQuery(String) :- is used to execute SQL SELECT queries,returns ResultSet.
•  Int executeUpdate(String) :- is used to execute SQL(INSERT,UPDATE,DELETE) queries,returns int value indicating number of rows affected
• boolean execute(String) :- returns true if a resultSet is retrieved successfully, generally used to execute DML statements
• Statement query gets compiled and executed every time when it is called
• It is used in case where there is no repetition of a query

Syntax :- statement.executeQuery("select mail from COMPANY where name="+"'"+uName+"'");

PreparedStatement interface :-

• Prepared statement queries are precompiled and the fetch plan will be stored in cache,so for subsequent requests only execution will happen
• They are faster than Statement queries because Statement queries will get compiled every time
• Used in case there is repetition of a query
• Prepared statements can be parameterized,parameterization of query values is done by using '?' - place holder in setXXX() method
• Prepared Statement prevents SQL injection
  For detailed explanation on SQL injection with example refer SQL injection

Syntax :-There are two ways of using prepared statement

1.PreparedStatement preparedStatement = connection.prepareStatement("select * from COMPANY where sid="+"'"+emailId+"'");

2. PreparedStatement preparedStatement = connection.prepareStatement("select * from COMPANY where sid=? AND name=?");

preparedStatement.setInt(1, 101);

preparedStatement.setString(2, "Google");

setXXX() -- takes two parameters first one tells position of the value to be placed,

this starts from 1 .

Second parameter is the respective value to be passed for ?-place holder

The first type of declaration will not prevent SQL injections because we are hard coding the where clause with a variable.

Second type of declaration will prevent SQL injection because all the parameters passed will be escaped by JDBC

CallableStatement :-

• is an interface used to execute stored procedures
• There are three types of parameters for a stored procedure
  • IN :- used to provide input to stored procedure,this is set by using setXXX()
  • OUT :- is used to hold values from a procedure,this is retrieved using getXXX()
  • INOUT :- acts as both input and output parameters

Code snippets :-

Procedure

create or replace procedure "P_COMPANY" (pname IN VARCHAR2, pemailid OUT VARCHAR2)

is begin

select mail into pemailid from COMPANY where name=pname;

end;

Procedure P_COMPANY takes two parameters and select query put  the  mail Id into pemailid(OUT parameter)

//CallableStatement usage

1 CallableStatement cs = connection.prepareCall("{call P_COMPANY(?,?)}");

2 cs.setString(1, name);

3 cs.registerOutParameter(2, java.sql.Types.VARCHAR);

4 cs.execute();

5 String mailId = cs.getString(2);

• Line 1 we are calling the the procedure P_COMPANY
• setting the IN parameter using setString(1, name);
• registering OUT parameter ,we have to mention sql data type also
• execute() or executeQuery() or executeUpdate() can be used to execute the procedure
• Line 5 getting the output from pemailid OUT variable

Complete code

JDBCConnection.java

public class JDBCConnection {

public void connectDB(String name) throws ClassNotFoundException,

SQLException {

Connection connection = null;

;

CallableStatement cs = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

// Creating connection object

connection = DriverManager.getConnection(

"jdbc:oracle:thin:@localhost:1521:xe", "system", "admin");

cs = connection.prepareCall("{call P_COMPANY(?,?)}");

cs.setString(1, name);

cs.registerOutParameter(2, java.sql.Types.VARCHAR);

cs.execute();

String mailId = cs.getString(2);

System.out.println("Required Mail id" + mailId);

} finally {

try {

connection.close();

cs.close();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

public static void main(String[] args) {

JDBCConnection jdbc = new JDBCConnection();

try {

jdbc.connectDB("Yahoo");

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

Output :- Required Mail id yahoo@yahoo.com

Key Points :-

• Statement is used for non-repeating queries
• Prepared Statement will be pre-compiled,on subsequent requests the query will get executes
• Action/Execution plan is stored in cache which helps in faster execution
• Prepared Statements will prevent SQL injections
• Prepared Statements allows parameterization of values by using place holder [?]
• Procedure can be executed using callable interface
• IN parameter is set using setXXX()
• OUT parameter is retrived by first registering the parameter and using getXXX()

                          Happy Learning

Please provide your valuable comments on this article and share it across your network.

Contact me @ sudheer@javarecent.com or admin@java-recent.com