JDBC - PreparedStatement

PreparedStatement

PreparedStatement is more powerful version of Java Statements and always quicker and easier than statement objects in order to handle SQL queries.We can use preparedStatement for parametric query. PreparedStatement gets pre compiled in database and access plan is also cached in database, which allows database to execute parametric query written using prepared statement much faster than normal query.

For Table data structure ref:-http://www.javatechtipssharedbygaurav.com/2013/04/jdbc-statement.html


Demo example of JDBC PreparedStatement( Providing configuration for both oracle and MySQL DB)


package com.gaurav.jdbcexamples;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCPreparedStatementExample {

      /** Configuraing a connection for Oracle 10G */
      /*
        private static String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver"; //Oracle Driver filename
        private static String USERNAME = "system";
        private static String PASSWORD = "system";
        private static String DATABASE_URL = "jdbc:oracle:thin:@localhost:1521:XE"; //Database connecting URL for Oracle
       
       */

      /** Configuraing a connection for MySQL databse */

      private static String JDBC_DRIVER = "com.mysql.jdbc.Driver"; // MySQL Driver filename
                                                                                                       
      private static String USERNAME = "root";
      private static String PASSWORD = "root";
      private static String DATABASE_URL = "jdbc:mysql://localhost:3306/test"; // Database connecting URL for MySQL
                                                                                                                 

      public static void main(String args[]) {
            Connection con = null;
            PreparedStatement pstmt = null;
            ResultSet rs = null;
            try {
                  Class.forName(JDBC_DRIVER);
                  con = DriverManager.getConnection(DATABASE_URL, USERNAME, PASSWORD);

                  String query = "Insert into student(STUDENT_ID,FIRSTNAME,LASTNAME,BIRTHDATE,TOTAL_MARKS,DIVISION) values(?,?,?,?,?,?)";
                  pstmt = con.prepareStatement(query);
                  pstmt.setInt(1, 24);
                  pstmt.setString(2, "John");
                  pstmt.setString(3, "Neilson");

                  Date date = Date.valueOf("1981-09-27");

                  pstmt.setDate(4, date);
                  pstmt.setInt(5, 690);
                  pstmt.setString(6, "SECOND");
                  int i = pstmt.executeUpdate();
                  System.out.println("Number of Records inserted is-" + i);

            } catch (SQLException sqe) {
                  sqe.printStackTrace();
            } catch (Exception e) {
                  e.printStackTrace();
            } finally {
                  try {
                        if (rs != null)
                              rs.close();
                        if (pstmt != null)
                              pstmt.close();
                        if (con != null)
                              con.close();
                  } catch (Exception e) {
                        e.getMessage();
                  }
            }
      }
}


Result

Java Side - It will display the following line

               Number of Records inserted is-1
DB Side:- By Executing the query SELECT * FROM STUDENT, we will get below output.
Fourth row is inserted as the successful execution of above program