SQLJ and JDBC
Tags: , , , , , database, db, db2, development, dynamicsql, java, jdbc, oracle, orm, Software, sql, sqlj, staticsql
As a follow up on my last post comparing Static SQL with Dynamic SQL, I will now post an example of how to run the same code using static and dynamic SQL.
One of my visitors left a comment saying that the scope of static and dynamic SQL in Oracle is different than the one I mentioned. I am not familiar at all with Oracle, but was able to find some information on their documentation where they compare JDBC and SQLJ. Since their concept of static vs dynamic SQL is different from the concept in DB2, so my examples may not make sense for Oracle users. I also found out that although Oracle has had plans to desupport SQLJ in its data server, that support has been reinstated in their 10g release.
The two code samples I will show next are shipped with DB2 (get your free copy of DB2 Express-C) and can be found in the file %DB2FOLDER%/samples/java/sqlj/TbRead.java. I’ll just use one of the several examples in that file, that executes a sub-select statement in the employee table.
Sample code in SQLJ:
#sql cur7 = {SELECT job, edlevel, SUM(comm)
FROM employeeWHERE job IN('DESIGNER', 'FIELDREP')GROUP BY ROLLUP(job, edlevel)};
while (true){
#sql {FETCH :cur7 INTO :job, :edlevel, :commSum};
if (cur7.endFetch()){
break;
}
System.out.print("Job: " + job + " Ed Level: " + edlevel + " Tot Comm: " +commSum);
}
Sample code in JDBC:
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT job, edlevel, SUM(comm) "
+" FROM employee "
+" WHERE job IN('DESIGNER','FIELDREP') "
+" GROUP BY ROLLUP(job, edlevel)");
while (rs.next())
{
if (rs.getString(1) != null)
{
job = rs.getString(1);
edlevel = rs.getString(1);
commSum = rs.getString(1);
System.out.print("Job: " + job + " Ed Level: " + edlevel + " Tot Comm: " +commSum);
}
}
Although both styles present different syntax, from a developer’s perspective, the only main difference is than when using JDBC one needs to explicitly fetch the row values into Java variables one by one. A common comment from Java developers is that SQLJ is not really Java (one needs to use annotations instead of java method calls), so they prefer to stick with JDBC.
Like I explained in my previous post, the biggest difference between these two styles (static SQL using SQLJ and dynamic SQL using JDBC) is that the SQL statements in the SQLJ files need to be compiled and bound to the database ahead of runtime. The following diagram illustrates this process:
After the deployment process, SQLJ execution is simpler than JDBC. While JDBC statements need to be prepared at execution time, SQLJ statements are already compiled and ready to use. The two following diagrams illustrate these differences:
As you can see, static SQL execution process is much simpler, but it requires a complex deployment process. This is an aspect of database development where there is a clash between DBAs and Developers. While ones – the DBAs – prefer the much more refined security and execution control provided by SQLJ and static SQL, others – the Developers – prefer the easier development process of dynamic SQL in the form of JDBC.
Soon, I will talk here about a new Java Data Access platform that supports the usage of both static and dynamic SQL at runtime (through a JVM property), allowing DBAs and Developers to use dynamic SQL on development and test environments and going with static SQL on the production environment. This way, the development community will get the best of both worlds: ease of deployment during development and testing phase and greater performance and control on the production environment.
If you are looking for a data management and application development tool, you should take a look at the new IBM Data Studio. It is an eclipse-based development environment, free to download and to use and with support to all major RDBMS. Download IBM Data Studio.
Popularity: 12% [?]
