Once a connection is obtained, we can interact with the database. Methods and properties defined in the JDBC Statement, CallableStatement and PreparedStatement interfaces enable the sending of SQL or PL/SQL commands and receiving data from the database.
They also define methods that help convert data differences between Java and databases using SQL data types.
The following table provides an overview of how each interface is used to understand which interface to use
Statement object:
Create a Statement object
When you can use the Statement object to execute SQL statements, you need to use the createStatement( ) method of the Connection object to create one, as shown in the following example:
Statement stmt = null;try { stmt = conn.createStatement( ); . . .}catch (SQLException e) { . . .} finally { . . .}Once a Statement object is created, it can then be used to execute SQL statements with one of its three execution methods.
boolean execute(String SQL): Returns the boolean value true if the ResultSet object can be retrieved, otherwise returns false. Use this method to execute SQL DDL statements, or when using real dynamic SQL.
int executeUpdate(String SQL): Returns the number of rows executed by the affected SQL statement. Use this method to execute, and hope to get some affected rows of SQL statements - for example, INSERT, UPDATE, or DELETE statements.
ResultSet executeQuery(String SQL): Returns the ResultSet object. Use this method when you want to get a result set, just like using a SELECT statement.
Close the Statement object:
Just as closing a Connection object to hold a database resource, the Statement object should be closed for the same reason.
A simple call to the close() method will do the job. If the Connection object is closed first, it will close the Statement object as well. However, the Statement object should always be explicitly closed to ensure proper clearance.
Statement stmt = null;try { stmt = conn.createStatement( ); . . .}catch (SQLException e) { . . .} finally { stmt.close();}
PreparedStatement Object
The PreparedStatement interface extends the Statement interface, allowing several advanced features to be added to a common Statement object.
statement provides flexibility for dynamic parameters.
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 parameters in JDBC are represented? Symbol, which is a known parameter marker. Before executing the SQL statement, each parameter of the value must be provided.
The setXXX() method binds the value to a parameter, where XXX represents the Java data type you want to bind to the input parameter value. If you forget to provide the value, you will receive a SQLException.
Each parameter tag is referenced by its sequence number position. The first mark indicates position 1, the next position is 2, and so on. This method is different from Java array indexing, starting with 0.
All Statement objects methods to interact with the database (a) execute(), (b) executeQuery(), and (c) executeUpdate() also work with PreparedStatement objects. However, the method is modified to use SQL statements, which can utilize the input parameters.
Close the PreparedStatement object:
Just as closing a 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 will first close the PreparedStatement object. However, the PreparedStatement object should always be explicitly closed to ensure correct clearance.
PreparedStatement pstmt = null;try { String SQL = "Update Employees SET age = ? WHERE id = ?"; pstmt = conn.prepareStatement(SQL); . . .}catch (SQLException e) { . . .} finally { pstmt.close();} PreparedStatement instance
Here are examples of this making use of PreparedStatement and turning statments on and off:
Copy the following example JDBCExample.java, compile and run as shown below:
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/EMP"; // 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 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 all the records and display them. sql = "SELECT id, first, last, age FROM Employees"; ResultSet rs = stmt.executeQuery(sql); //STEP 5: Extract data from result set while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); //Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); 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 JDBCExampleNow let’s compile the above example as follows:
C:>javac JDBCExample.java
When running JDBCExample, it produces the following results:
C:>java JDBCExample
Connecting to database...Creating statement...Rows impacted : 1ID: 100, Age: 18, First: Zara, Last: AliID: 101, Age: 25, First: Mahnaz, Last: FatmaID: 102, Age: 35, First: Zaid, Last: KhanID: 103, Age: 30, First: Sumit, Last: MittalGoodbye!