JDBC Statement Interface

java.sql.Statement is used for executing a static SQL statement and returning the results it produces. Statement interface has two subinterfaces-

PreparedStatement- Stores a precompiled SQL statement which can then be used to execute this statement multiple times by passing different values for the parameters. Read more about PreparedStatement in this post- JDBC PreparedStatement Interface
CallableStatement- Used to execute SQL stored procedures. Read more about CallableStatement in this post- JDBC CallableStatement Interface

Methods in Statement interface

Some of the frequently used methods of the Statement interface are detailed here-

execute(String sql)- Executes the given SQL statement, which may return multiple results. This method returns a boolean; true if the first result is a ResultSet object; false if it is an update count or there are no results.
executeQuery(String sql)- Executes the given SQL statement returning a ResultSet object. Suited for running a SELECT query.
executeUpdate(String sql)- Executes the specified SQL which may be an INSERT, UPDATE or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement (Create, Drop).
addBatch(String sql)- If you are running a bunch of SQL statements as a batch, this method adds the given SQL command to the current list of commands for this Statement object.
executeBatch()- Submits a batch of commands to the database for execution.
close()- Releases this Statement object's database and JDBC resources immediately.

JDBC Statement example

In the example we’ll use all the three types of execute methods; execute(), executeQuery() and executeUpdate() to have a better understanding of how to use these methods.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementDemo {
  public static void main(String[] args) {
    Connection connection = null;
    try {
      // Load driver
      Class.forName("com.mysql.cj.jdbc.Driver");
      // connection object
      connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/knpcode", 
                   "root", "admin");
      // create Statement object
      Statement statement = connection.createStatement(); 
      /** execute method **/
      boolean flag = statement.execute("UPDATE Employee SET DEPARTMENT = 'HR' where id = 15");
      // flag false means not returning resultset
      if(flag == false){
        System.out.println("Updated rows " + statement.getUpdateCount() );
      }
	      
      /** executeUpdate method **/
      // Insert statement
      int count = statement.executeUpdate("INSERT INTO Employee(FIRST_NAME, LAST_NAME, DEPARTMENT) "
          + "values('John', 'Trudaue', 'IT')");
      System.out.println("Number of records Inserted " + count);
            
      // update statement
      count = statement.executeUpdate("UPDATE Employee SET DEPARTMENT = 'Finance' where id = 15");
      System.out.println("Number of records Updated " + count);
	            
      //delete statement
      count = statement.executeUpdate("Delete from Employee where id = 11");
      System.out.println("Number of records Deleted " + count);
            
      /** executeQuery method **/
      // Executing Query
      ResultSet rs = statement.executeQuery("Select * from Employee");

      while(rs.next()){
        System.out.println("id: " + rs.getInt("id") + 
           " First Name: " + rs.getString("FIRST_NAME") + 
           " Last Name: " + rs.getString("LAST_NAME")+ 
           " Dept: " + rs.getString("DEPARTMENT")); 
      }   
	           
    }catch (ClassNotFoundException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        //close connection 
        try {
          connection.close();
        } catch (SQLException e) {   
          e.printStackTrace();
        }
      }
    }
  }
}

That's all for the topic JDBC Statement Interface. If something is missing or you have something to share about the topic please write a comment.

You may also like

KnpCode

August 17, 2022