Types of Statements

  • In java the statements specify the sequence of actions to be performed when a method or constructor is invoked. They can alter the value of variables, generate output, process input, or respond to user mouse or keyboard actions.

  • Different types of statements are described in the following sections.

    • Statement: Execute simple sql queries without parameters. Statement createStatement() Creates an SQL Statement object.

    • PreparedStatement: Execute precompiled sql queries with or without parameters. PreparedStatement prepareStatement(String sql) returns a new PreparedStatement object. PreparedStatement objects are precompiled SQL statements.

    • Callable Statement: Execute a call to a database stored procedure. CallableStatement prepareCall(String sql) returns a new CallableStatement object. CallableStatement objects are SQL stored procedure call statements.

Using Statement

Various Database Operations:
Various database operations that you can perform using a Java application are:
  • Querying a table
  • Inserting rows in a table
  • Updating rows in a table
  • Deleting rows from a table
Select
Querying a Table
  • The SELECTstatement is executed using the executeQuery() method and returns the output in the form of a ResultSet object.
  • The code snippet to retrieve data from the authors table is:
String str = "SELECT * FROM authors"; 
Statement stmt = con.createStatement(); 
ResultSet rs = stmt.executeQuery(str);
Insert
Inserting Rows in a Table
  • The executeUpdate() method enables you to add rows in a table.
  • The code snippet to insert a row in the authors table is:
String str = "INSERT INTO authors(au_id, au_lname, au_fname, address, city, state, contract) 
VALUES ('998-72-3568','Ringer','Albert','801 826-0752 67 Seventh Av.','Salt Lake City','UT','1')"; 
Statement stmt = con.createStatement();
int count = stmt.executeUpdate(str);
Update
Updating Rows in a Table
  • The code snippet to modify a row in the authors table is:
  String str = "UPDATE authors SET address='10932 Second Av.a
  WHERE au_id='998-72-3568'";
  Statement stmt = con.createStatement();
  int count = stmt.executeUpdate(str);
Delete
Deleting Rows from a Table
  • The code snippet to delete a row from the authors table is:
   String str = "DELETE FROM authors WHERE au_id='998-72-3568'";
   Statement stmt = con.createStatement();
   int count = stmt.executeUpdate(str);

Using Prepared Statement

  • Java JDBC Prepared statements are pre-compiled SQL statements. Precompiled SQL is useful if the same SQL is to be executed repeatedly, for example, in a loop. Prepared statements in java only save you time if you expect to execute the same SQL over again.
  • Prepared statement work same as the statement,but there are some differences. when you submitting the query
    1st time following things happened
    1. compiling the query
    2. executing the query
    3. sending results back to the program
    2nd time onwards
    1. executing the query directly
    2. sending results back to the program

Quering Records
  • The prepareStatement() method of the Connection object is used to submit parameterized query to a database.
  • The SQL statement can contain <?> symbol as placeholders that can be replaced by input parameters at runtime. For example,
   stat=con.prepareStatement("SELECT * FROM authors WHERE au_id = ?");
  • The value of each parameter is set by calling an appropriate setXXX() method, where XXX is the data type of the parameter. For example,
    •    stat.setString(1,"1001");
   ResultSet result=stat.executeQuery();
    Inserting Rows
    • The code snippet to create a PreparedStatement object that inserts a row into authors table by passing authors data at runtime is:
    	
   String str = "INSERT INTO authors (au_id, au_fname, au_lname) VALUES (?, ?, ?)";
   PreparedStatement ps = con.prepareStatement(str);
   ps.setString(1, "1001");
   ps.setString(2, "Abraham");
   ps.setString(3, "White");
   int rt=ps.executeUpdate();
    Updating Rows
    • The code snippet to modify the state to CA where city is Oakland in the authors table using the PreparedStatement object is:
    	String str = "UPDATE authors SET state= ? WHERE city= ? ";
    PreparedStatement ps = con.prepareStatement(str);
    ps.setString(1, "CA");
    ps.setString(2, "Oakland");
    int rt=ps.executeUpdate();
    Deleting Rows
    • The code snippet to delete a row from the authors table where author’s first name is Abraham using the PreparedStatement object is:
        String str = "DELETE FROM authors WHERE au_fname= ? ";
    PreparedStatement ps = con.prepareStatement(str);
    ps.setString(1, "Abraham");
    int rt=ps.executeUpdate();

    Using Callable Statement

    • The CallableStatement extends the PreparedStatement interface.
    • The CallableStatement provides an interface for calling database stored procedures.
    • The simplest form of this syntax would be:{call procedure-name}
    • which represents a call to a stored procedure with no parameters.
    • A call to a stored procedure accepting two input parameters:
      • {call procedure-name (?, ?)}
CallableStatement callproc = connection.prepareCall("{call updateLast (?, ?)}");
callproc.setInt (1, 5); 
// 1 specifies the first parameter
callproc.setString (2, "J"); 
// 2 specifies the second parameter
    • To now execute the stored procedure, we use the following statement:callproc.executeUpdate();