Formulating SQL Statements

When you write a prepared statement, you use a question mark character (?) as a placeholder that will later be replaced by a value you specify using a setXXX( ) method. These placeholders can be used only for values that need to be specified in a SQL statement and not in place of SQL keywords; they can’t be used to implement a type of macro language. When building SQL statements, you must abide by certain rules. For an INSERT statement, you can use placeholders only in the VALUES list. For example:

insert into person_identifier_type 
( code, description, inactive_date ) 
values 
( ?, ?, ? )

In this example, the first placeholder, or question mark (?), represents the value for the code column; the second represents the description column, and the third represents the inactive_date column.

For an UPDATE statement, you can use placeholders only in the SET VALUES list and in the WHERE clause. For example:

update person_identifier_type 
set    description = ? 
where  code = ?

In this example, the first placeholder represents the new value for the description column, while the second represents a value for the code column in the WHERE clause.

For a DELETE statement, you can use the placeholder only in the WHERE clause. For example:

delete person_identifier_type
where  code = ?

Finally, for a SELECT statement, you can use the placeholder in the SELECT list, WHERE clause, GROUP BY clause, and ORDER BY clause. For example:

select ?, code, description from person_identifier_type ...

Get Java Programming with Oracle JDBC now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.