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-

  1. 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
  2. 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.

Statement example in Java

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();
        }
      }
    }
  }
}

Related Posts

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.