The iBATIS philosophy - iBATIS in Action - page 9

Dynamic SQL has the advantage of flexibility. The SQL can be manipulated at
runtime based on different parameters or dynamic application functions. For exam-
ple, a query-by-example web form might allow the user to select the fields to search
upon and what data to search for. This would require a dynamic change to the WHERE
clause of the SQL statement, which can be easily done with Dynamic SQL .
Dynamic SQL is currently the most popular means of accessing relational data-
bases from modern languages. Most such languages include a standard API for
database access. Java developers and . NET developers will be familiar with the
standard API s in those languages: JDBC and ADO.NET , respectively. These stan-
dard SQL API s are generally very robust and offer a great deal of flexibility to the
developer. The following is a simple example of Dynamic SQL in Java:

String name;
Date hiredate;
String sql = "SELECT emp_name, hire_date"
+ " FROM employee WHERE emp_num = ? ";
Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement (sql);
ps.setInt (1, 28959);
ResultSet rs = ps.executeQuery();
while (rs.next) {
name = rs.getString("emp_name");
hiredate = rs.getDate("hire_date");
}
rs.close();
conn.close();
Without a doubt, Dynamic SQL is not as elegant as inline SQL , or even stored pro-
cedures (and we even left out the exception handling). The API s are often com-
plex and very verbose, just like the previous example. Using these frameworks
generally results in a lot of code, which is often very repetitive. In addition, the
SQL itself is often too long to be on a single line. This means that the string has to
be broken up into multiple strings that are concatenated. Concatenation results
in unreadable SQL code that is difficult to maintain and work with.
So if the SQL isn't best placed in the database as a stored procedure, or in the
language as inline SQL , or in the application as a data structure, what do we do
with it? We avoid it. In modern object-oriented applications, one of the most com-
pelling solutions to interacting with a relational database is through the use of an
object/relational mapping tool.

Should be in try-catch
block