Saturday, February 22, 2020

JDBC PreparedStatement

JDBC(Java Database Connectivity)

PreparedStatement

PreparedStatement is an interface derived from the Statement Interface. The Statement object becomes difficult to handle and use sometimes because the variables and objects are embedded into a String object. Especially when we have multiple SQL statements to execute. PreparedStatement is easy to use and convenient. PreparedStatement is given a pre-compiled SQL statement, when it is created, unlike a Statement object which is first compiled by the RDBMS and then executed. The benefit to this is that most of the time, this SQL statement is sent to the DBMS directly, where it can be compiled. It means that the PreparedStatement object contains not only the SQL statement but a precompiled SQL statement also. As a result, when the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to re-compile it.

For example, in the method below

//A method with Statement Object
public void insertData(Employee emp)
      {
            Connection con=DBConnection.getConnection();
            try {
                  Statement stmt=con.createStatement();
                  int id=emp.getEid();
                  String name=emp.getEname();
                  String dept=emp.getEdept();
                  int sal=emp.getEsal();
                  //Embed data into SQL
                  String sql="INSERT INTO EMPLOYEE VALUES("+id+",'"+name+"','"+dept+"',"+sal+")";
                  stmt.execute(sql);
                  System.out.println("Table UPADTED successfully");
                  con.close();
            } catch (SQLException e) {
                  System.out.println("SOME EXCEPTION");
                  e.printStackTrace();
            }
           
      }

The variables are embedded in normal SQL Statement object as,

String sql="INSERT INTO EMPLOYEE VALUES("+id+",'"+name+"','"+dept+"',"+sal+")";

This will become more complex if SQL query lengthy or multiple queries are executed together, but for the PreparedStatement, you do not need to embed the variables and objects. 

For Example,
//A method with PreparedStatement Object
public void insertDataByPreparedStatement(Employee emp)
      {
            Connection con=DBConnection.getConnection();
            try {
                  int id=emp.getEid();
                  String name=emp.getEname();
                  String dept=emp.getEdept();
                  int sal=emp.getEsal();
   String sql="INSERT INTO MYEMPLOYEE1 VALUES(?,?,?,?)";//SQL Query
                  PreparedStatement pstmt=con.prepareStatement(sql);
                  pstmt.setInt(1, emp.getEid());
                  pstmt.setString(2, emp.getEname());
                  pstmt.setString(3, emp.getEdept());
                  pstmt.setInt(4, emp.getEsal());
                  pstmt.execute();
                  System.out.println("Table UPADTED successfully");
                  con.close();
            } catch (SQLException e) {
                  System.out.println("SOME EXCEPTION");
                  e.printStackTrace();
            }
           

      }

The Supplier Methods


The SQL Statement is having a question mark symbol(?) as a placeholder for the variable or object. These placeholders can be later filled by supplier methods. In the example above the SQL statement is carrying four (?) characters, that are later filled by four supplier methods accordingly. 

Video Tutorial