Detailed Java JDBC statement and PreparedStatement objects _java

Once we get a connection, we can interact with the database. In JDBC Statement, the CallableStatement and PreparedStatement interfaces define methods and properties that allow you to send SQL or PL/SQL commands and receive data from the database.

They also define methods that help Java and the database use differences in converting data between SQL data types.

The following table provides a summary of the purpose of each interface to determine which interface to use

Statement object:

Creating statement objects
when you can execute an SQL statement using the statement object, you need to create one using the Createstatement () method of the Connection object, as shown in the following example:

Statement stmt = null;
try {
  stmt = conn.createstatement ();
  . . .
}
catch (SQLException e) {
  ...
}
Finally {
  ...
}

Once you create a statement object, you can then use it to execute the SQL statement with one of its three execution methods.

Boolean execute (String SQL): Returns False if the ResultSet object can be retrieved to return a Boolean value of true. Use this method to execute SQL DDL statements, or when you need to use real dynamic SQL.

int executeupdate (String SQL): Returns the number of rows executed by the affected SQL statement. Use this method to execute an SQL statement that wants to get some of the affected rows-for example, a insert,update or DELETE statement.

ResultSet executequery (String SQL): Returns the ResultSet object. When you want to get a result set using this method, it's like using a SELECT statement.

Close the Statement object:
just as you close a Connection object to save the database resource, you should also close the statement object for the same reason.

A simple call to the close () method will do the job. This is true if the connection object is closed first and it closes the statement object. However, you should always explicitly close the statement object to ensure proper cleanup.

Statement stmt = null;
try {
  stmt = conn.createstatement ();
  . . .
}
catch (SQLException e) {
  ...
}
finally {
  stmt.close ();
}

PreparedStatement Objects
the PreparedStatement interface extends the statement interface, allowing a common statement object to add several advanced features.

Statement provides flexibility for dynamic parameters.

To create a PreparedStatement object:

PreparedStatement pstmt = null;
try {
  String SQL = ' Update Employees SET age =? WHERE id =? ";
  pstmt = Conn.preparestatement (SQL);
  . . .
}
catch (SQLException e) {
  ...
}
Finally {
  ...
}

All the parameters in JDBC are represented? Symbol, which is a known parameter marker. Before executing the SQL statement, you must supply each parameter of the value.

The Setxxx () method binds the value to the parameter, where xxx represents the Java data type that you want to bind to the input parameter value. If you forget to provide a value, you will receive a sqlexception.

Each parameter marker is referenced by its ordinal position. The first tag represents position 1, the next position is 2, and so on. This method differs from the Java array index, starting with 0.

All statement object methods are used to interact with the database (a) execute (), (b) executequery (), and (c) executeupdate () also work with PreparedStatement objects. However, the method is modified to use an SQL statement that can take advantage of the input parameters.

Close the PreparedStatement object:
just as close the statement object, for the same reason, the PreparedStatement object should also be closed.

A simple call to the close () method will do the job. If the connection object is closed, it closes the PreparedStatement object first. However, you should always explicitly close the PreparedStatement object to ensure proper cleanup.

PreparedStatement pstmt = null;
try {
  String SQL = ' Update Employees SET age =? WHERE id =? ";
  pstmt = Conn.preparestatement (SQL);
  . . .
}
catch (SQLException e) {
  ...
}
finally {
  pstmt.close ();
}

PreparedStatement instance
The following are examples of this making use of preparedstatement and opening and closing statments:
Copy Jdbcexample.java in the following example, compile and run as follows:

Step 1.

Import required packages import java.sql.*; public class Jdbcexample {//JDBC driver name and database URL static final String jdbc_driver = "Com.mysql.jdbc.Driv 
  ER ";

  Static final String Db_url = "Jdbc:mysql://localhost/emp";
  The Database credentials static final String USER = "username";
  
  Static final String pass = "password";
  public static void Main (string[] args) {Connection conn = null;
  PreparedStatement stmt = null;

   try{//step 2:register JDBC driver Class.forName ("Com.mysql.jdbc.Driver");
   Step 3:open A connection System.out.println ("Connecting to Database ...");

   conn = Drivermanager.getconnection (Db_url,user,pass);
   Step 4:execute a query System.out.println ("creating statement ..."); String sql = "UPDATE Employees set age=?"
   WHERE id=? ";
   
   stmt = conn.preparestatement (sql);
   Bind values into the parameters. Stmt.setint (1, 35); This is would set age Stmt.setint (2, 102); This would set ID//LEt us update age of the record with ID = 102;
   int rows = Stmt.executeupdate ();

   SYSTEM.OUT.PRINTLN ("Rows impacted:" + rows);
   Let us select the records and display them.
   sql = "SELECT ID, Employees";

   ResultSet rs = stmt.executequery (SQL);
     Step 5:extract The data from the result set while (Rs.next ()) {//retrieve by column name int id = rs.getint ("id");
     int age = Rs.getint (' age ');
     String a = rs.getstring ("a");

     String last = rs.getstring ("last");
     Display values System.out.print ("ID:" + ID);
     System.out.print (", Age:" + age);
     System.out.print (", A:" + a);
   System.out.println (", Last:" + last);
   }//step 6:clean-up Environment Rs.close ();
   Stmt.close ();
  Conn.close ();
  }catch (SQLException se) {//handle errors for JDBC se.printstacktrace ();
  }catch (Exception e) {//handle errors for class.forname e.printstacktrace (); }finally{//finally block USEd to close resources try{if (stmt!=null) stmt.close ();
   }catch (SQLException se2) {}//Nothing we can do try{if (conn!=null) conn.close ();
   }catch (SQLException se) {se.printstacktrace ();
}//end finally try}//end try System.out.println ("goodbye!");

 }//end Main}//end Jdbcexample

Now let's compile the example above as follows:

C:>javac Jdbcexample.java

When you run Jdbcexample, it produces the following results:

C:>java Jdbcexample

Connecting to Database ...
Creating Statement
... Rows impacted:1
id:100, age:18, First:zara, Last:ali id:101,
age:25, First:mahnaz, Last:fatma
id:10 2, age:35, First:zaid, Last:khan
id:103, age:30, First:sumit, Last:mittal
goodbye!