Types of Statements

Statements

The Statementinterface lets you execute a simple SQL statement with no parameters. The SQL instructions are inserted into the Statementobject when the Statement.executeXXXmethod is called.

Query Statement: This code segment creates a Statementobject and calls the Statement.executeQuerymethod to select text from the dbadatabase. The results of the query are returned in a ResultSetobject. How to retrieve results from a ResultSetobject is explained in Result Sets below.

 Statement stmt = con.createStatement();
 ResultSet results = stmt.executeQuery(
                       "SELECT TEXT FROM dba ");

Update Statement: This code segment creates a Statementobject and calls the Statement.executeUpdatemethod to add an email address to a table in the dbadatabase.

  String updateString = 
           "INSERT INTO dba VALUES (some text)";
  int count = stmt.executeUpdate(updateString);

Prepared Statements

The PreparedStatementinterface descends from the Statementinterface and uses a template to create a SQL request. Use a PreparedStatementto send precompiled SQL statements with one or more parameters.

Query PreparedStatement: You create a PreparedStatementobject by specifying the template definition and parameter placeholders. The parameter data is inserted into the PreparedStatementobject by calling its setXXXmethods and specifying the parameter and its data. The SQL instructions and parameters are sent to the database when the executeXXXmethod is called.

This code segment creates a PreparedStatementobject to select user data based on the user's email address. The question mark ("?") indicates this statement has one parameter.

  PreparedStatement pstmt = con.prepareStatement(
                              select theuser from 
                              registration where 
                              emailaddress like ?");
//Initialize first parameter with email address
  pstmt.setString(1, emailAddress);
  ResultSet results = ps.executeQuery();

Once the PreparedStatementtemplate is initialized, only the changed values are inserted for each call.

  pstmt.setString(1, anotherEmailAddress);

Note: Not all database drivers compile prepared statements.

Update PreparedStatement: This code segment creates a PreparedStatementobject to update a seller's registration record. The template has five parameters, which are set with five calls to the apprpriate PreparedStatement.setXXXmethods.

PreparedStatement ps = con.prepareStatement(
	"insert into registration(theuser, password, 
           emailaddress, creditcard, 
           balance) values (
           ?, ?, ?, ?, ?)");
  ps.setString(1, theuser);
  ps.setString(2, password);
  ps.setString(3, emailaddress);
  ps.setString(4, creditcard);
  ps.setDouble(5, balance);
  ps.executeUpdate();

--Oracle