JDBC - Statements, PreparedStatement and CallableStatement

The JDBC Statement, CallableStatement, and PreparedStatement interfaces define the methods and properties that enable you to send SQL or PL/SQL commands and receive data from your database.

I. Statement - The Statement interface provides methods to execute queries with the database. The statement interface is a factory of ResultSet i.e. it provides factory method to get the object of ResultSet.

Commonly used methods of Statement interface:

1) public ResultSet executeQuery(String sql): is used to execute SELECT query. It returns the object of ResultSet.

2) public int executeUpdate(String sql): is used to execute specified query, it may be create, drop, insert, update, delete etc.

3) public boolean execute(String sql): is used to execute queries that may return multiple results.

4) public int[] executeBatch(): is used to execute batch of commands.

 

II. PreparedStatement - The PreparedStatement interface is a subinterface of Statement. It is used to execute parameterized query. The PreparedStatement interface accepts input parameters at runtime.

The performance of the application will be faster if you use PreparedStatement interface because query is compiled only once.

PreparedStatement stmt=con.prepareStatement("insert into Emp values(?,?)");  

III. CallableStatement - CallableStatement interface is used to call the stored procedures and functions. Just as a Connection object creates the Statement and PreparedStatement objects, it also creates the CallableStatement object, which would be used to execute a call to a database stored procedure.

CallableStatement cstmt = null;

String SQL = "{call getEmpName (?, ?)}";

             cstmt = conn.prepareCall (SQL);

 

Ex: JDBC Example

import java.sql.*;

public class FirstExample {

   static final String DB_URL = "jdbc:mysql://localhost/STUDENT";

   static final String USER = "guest";

   static final String PASS = "guest123";

   static final String QUERY = "SELECT id, first, last, age FROM Employees";

   public static void main(String[] args) {

      // Open a connection

      try{

        Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);

         Statement stmt = conn.createStatement();

         ResultSet rs = stmt.executeQuery(QUERY);) {

         // Extract data from result set

         while (rs.next()) {

            // Retrieve by column name

            System.out.print("ID: " + rs.getInt("id"));

            System.out.print(", Age: " + rs.getInt("age"));

            System.out.print(", First: " + rs.getString("first"));

            System.out.println(", Last: " + rs.getString("last"));

         }

      } catch (SQLException e) {

         e.printStackTrace();

      }

   }

}