Named Queries

Named query support was introduced in the underlying PDQ engine early 2007. However, it was not advertised to Project Zero team and others. The mechanism to make this possible is to pass a Map into PDQ's factory method. Subsequent calls to the query interfaces result in a lookup SQL string as a key in the map first. A hit will make use of the value as the SQL and a miss with utilize the application's passed in string. In other words, its either a key or it is SQL.

There is a forum thread discussing this topic. Information and designs in the thread will be brought over here.

Summary

The end goal is to enable the query APIs to allow for short names that point to longer SQL strings that are external to application code. For instance:

result = queryFirst('my_named_query', [arg1, arg2, arg3])

Advantages

The advantages are that it is much more concise. You can create a name that describes what is going on rather than another developer trying to infer from SQL (complex queries can get confusing). Also, by consolidating the location of SQL, optimizations down the road are easier such as leveraging DB2's Static SQL. The big advantage that I see is that this can be a means to provide different SQL strings for different database vendors. For instance, if you look at the employee.demo application, zero.config includes derby.config.

Disadvantages

The disadvantages include having to flip back and forth between code and where the SQL actually lives. Another is that it is impossible to take advantage of GStrings. Externalized queries would be just that, external, thus no inline variable option.

Implementation

I implemented externalized SQL in the past but held back because zero.config does not support multi-line strings. So complex queries are VERY annoying debugging on one line. Some options to consider:

Option #1: Special stanza shared by all db's

[/app/db/statements]
select_all_people=SELECT * FROM person
select_by_id=SELECT * FROM person WHERE id=?
insert_person=INSERT INTO person (name, phone) VALUES (:name, :phone)

Option #2: Data structure in db config

[/app/db/theDB/config]
class=org.apache.derby.jdbc.ClientDataSource
serverName=localhost
portNumber=1527
databaseName=db/myDB
connectionAttributes=create=true
statements=[
  select_all_people=SELECT * FROM person,
  select_by_id=SELECT * FROM person WHERE id=?,
  insert_person=INSERT INTO person (name, phone) VALUES (:name, :phone),
]