Data Access Object Design Questions

[Oliver Chua]

I have a design question regarding Data Access Objects.
DAO classes are Java classes with SQL code in them (SQL code is interspersed in the Java code).
What is the best way to provide maintainability with regards to the SQL code in the DAO?
Maintainability comes into play during optimization, and also during maintenance projects.

issue 1: Should SQL code be stored in a class/interface as static final Strings so that they're separated from Java code? I find the best way to format SQL code is by separating SELECT clause, FROM clause, WHERE clause, ORDER clause, etc. For nested selects, by using parenthesis and aligning using tabs. Ideally, code can be copied from the Java IDE then pasted directly to the SQL editor properly formatted. However, there's no way in Java to declare a single String spread across multiple lines without resorting to using + (concat operator) after every line. I'm thinking of a String declaration with a comment where the comment is a properly formatted version of the declaration but this may not be popular with developers as this will result to double work.

issue 2: If I should store SQL code with Java code, should I use String or StringBuffer to contain SQL codes? I've read somewhere using Strings is ok since the compiler can optimize compile time String objects rather than runtime objects which is the case when using StringBuffer. I didn't bookmark the article so I can't give a reference, sorry.

issue 3: Is it better to declare the SQL code as a long line or is it better to chop so that the code is visible on the IDE screen? Long lines are better for copying and pasting to the SQL Editor, Chopped lines are better for visual examination of the code. I wouldn't say for readability too since I find it hard to read chopped SQL in DAOs.

issue 4: I use PreparedStatements and I really like the idea of stubbing out a dynamic part. In the case of Strings however, a ? will be replaced with a 'variable' with the quotes included. This cannot be used to factor out dynamic parts in the SQL code.
For example:
get records with null column - SELECT col2 FROM table WHERE col IS NULL
get records with specified column - SELECT col2 FROM table WHERE col =?

I cannot use SELECT col2 FROM table WHERE col ? for IS NULL because the resulting String would become col 'IS NULL'

It brings us to the question: How should we support dynamic parts in SQL code?
a. construct sql dynamically using something like this:
"SELECT "+"selectClause" +" FROM " fromClause + "WHERE "+whereClause
b. provide static sql for each case.

Construct SQL Pros and Cons and arguments:
a. SQL should follow code reuse tenet. Factor out common code for reuse. This will make it easy to support additional queries that are similar to existing ones.
b.

Static SQL Pros and Cons and arguments:
a. Factoring out introduces indirection which causes complexity. For example, in nested selects that have n nullable columns in the WHERE clause, there would be 2(n+1) branches of logic (check if WHERE or AND keyword should be added, check if column is null or not.)
b. Complex code is hard to comprehend, maintain and test.

I guess it all boils down to this question: Does the same tenet of code reuse (Factoring similar code into reusable parts) also apply to SQL code in DAOs or should SQL code be considered only as constants?

[Stan James]

1. Where to store ... Ask yourself if it's ok to compile, test and deploy for every SQL change. If so, you can put it in the code. If not, you might want it outside the code in a file or database. I've done the database route once. Right now we have a DAO base class that expects you to override a method called getSQL() and return a string. In that method you're free to build the SQL any way you want.

Format ... It needs to wind up as one long string. You could write Java like this:

String sql = "select * "
           + "from table "
           + "where column = value "
           + "and column = value";

so it's sort of formatted. Or for serious geek fun, write a parser / formatter that can take a string and pretty print it to the log.

2. String or StringBuffer ... Use StringBuffer if you're going to concatenate a bunch of parts a bunch of times. The String building I did above would be fine once at startup, but not a thousand times a second in a heavily loaded system. Then I'd switch to StringBuffer & concat().

3. Long line or chopped - do you mean like the code above? Your choice, I guess. I'm a bit of an alignment freak but the code formatter in Eclipse wipes the formatting out so I'm getting over that.

4. Prepared statements are good for two things: 1) It's easier to use special characters because the set parameter methods escape the special stuff for you. It's no fun to have a production database crash because somebody uses a semi-colon in a comment field. (Been there) And 2) you can get real performance gains if you run the same statement over and over. But you can't do real dynamic things like different combinations of where clauses and such. Back in my COBOL days (yup, I'm old) dynamic was not allowed so we had a prepared statement for every possible combination of where fields. Lots of em!

Reuse - Not sure where you're going, but I wouldn't put much effort into reusing little clauses of SQL.

Any of that helpful?

[Maulin Vasavada]

Hi Oliver,

Good points. It would be nice to hear from experienced folks here.

Regards,
Maulin

[Oliver Chua]

Hi Stan and Maulin...

Thanks for the reply.

I decided to rewrite my questions into 2 design patterns.
It may be clearer that way.

Tell me if you agree with the points or not.

[Oliver Chua]

Pattern:
DAO SQL Code Declaration and Formatting

Context:
a. When writing DAO classes, SQL code is interspersed with Java code. SQL code varies from being a very simple one liner that can fit to a screen to a 30-line SQL code with complex joins (multiple joins, nested selects, unions, etc.).
b. Developers would usually need to:
i. be able to visually examine the SQL code during development and debugging.
ii. be able to copy and paste the SQL code to their SQL editors with formatting.
iii. avoid performance penalties of concatenating String literals.

Problem:
a. There is no way in Java to declare a single String spread across multiple lines without resorting to using + (concat operator) after every line.
For example:
<CODE>
String str="Hello
World
"; //illegal
</CODE>
The only alternative is to chop SQL code into multiple lines
<CODE>
String str="Hello ";
str+="\n World";
</CODE>
b. Code containing SQL code that is chopped to fit screen width is hard to read because of the many interspersed quotes and + signs.
c. Adding Strings or even using StringBuffer to add String literals will introduce performance penalty.


Solution:
a. Declare SQL code as a public static final String variable. The declaration is a one liner, no matter how long. This will be the one used by the application. Declaring SQL code as a one liner will solve the performance issue related to concatenating many Strings.
b. Immediately after the declaration, a multi-line comment will be added to provide a formatted
version of the SQL code declaration. This will be used by the developers to copy and paste to their SQL editors.

Example:
<CODE>
public static final String selectEmployees="SELECT emp_name FROM employee x, employee_info y WHERE x.emp_code=y.emp_code ORDER BY emp_name";
/*
SELECT emp_name
FROM employee x, employee_info y
WHERE x.emp_code=y.emp_code
ORDER BY emp_name
*/
}
</CODE>

Consequences/implications:
a. Developers have to maintain and keep track of 2n statements, where n is the number of SQL statements in the application.
b. If developer fails to keep the two SQL statements in sync, the actual SQL code used in the application will produce different result than when developers copy and paste the SQL code in comments, and may cause confusion.

[Oliver Chua]

Pattern:
DAO Complex Dynamic PreparedStatement SQL code

Context:
a. When coding SQL code in DAO classes, there would be some SQL code that will be similar, but will vary in the select clause, where clause, etc. This usually occur in select statements.
b. This pattern does not refer to parameters whose values can be stubbed out using PreparedStatements.
For example:
i. get records with null column - <CODE>SELECT col2 FROM table WHERE col IS NULL</CODE>
ii. get records with specified column - <CODE>SELECT col2 FROM table WHERE col =?</CODE>
I cannot use <CODE>SELECT col2 FROM table WHERE col ?</CODE> and call
<CODE>pstmt.setString(1,"IS NULL");</CODE> to look for a null column because the resulting String would become <CODE>SELECT col2 FROM table WHERE col 'IS NULL'</CODE>
What I would have to do is this:
<CODE>
String whereClause="IS NULL";
if (findNotNull) whereClause="=?";
String sql="SELECT col2 FROM table WHERE col"+whereClause;
</CODE>
c. Sometimes, all fields will be similar, except for the table name.
d. Developers would usually need to:
a. be able to visually examine the SQL code during development and debugging.
b. be able to copy and paste the SQL code to their SQL editors with formatting.
c. avoid performance penalties of concatenating String literals.

Problem:
a. Cyclomatic Complexity is a metric that relates to the number of independent conditional logic in a program. Below is a code snippet from a DAO class. The query returns a list of employees, search criteria is two, where each search criteria can be All, None or a record from the database.
The code below is an example of a complex dynamic SQL code. It suffers from high cyclomatic complexity and will make the code hard to understand, hard to maintain and hard to test.
<CODE>
public void selectEmployees(boolean isCount, SearchEmployeeBean seb)
String selectClause = " DISTINCT a.employee_code, first_name, last_name ";
String tailClause = "";
if (isCount) {
selectClause = " COUNT(*) FROM (SELECT DISTINCT a.employee_code";
tailClause = ") z";
}

String whereClause = "";
if (!GroupingVO.getAllGroupingCode().equals(seb.getGroupingCode())) {
whereClause = "WHERE c.group_code=? ";
}
if (!UnitVO.getAllUnitCode().equals(seb.getUnitCode())) {
if (whereClause.equals("")) {
whereClause = " WHERE ";
} else {
whereClause += " AND ";
}
whereClause += " c.unit_code";
if (!UnitVO.getNoneUnitCode().equals(seb.getUnitCode())) {
whereClause += "=? ";
} else if (UnitVO.getNoneUnitCode().equals(seb.getUnitCode())
&& GroupingVO.getNoneGroupingCode().equals(seb.getGroupingCode())) {
whereClause += "=? ";
} else {
whereClause += " IS NULL ";
}
}
sb.append("SELECT " + selectClause + " FROM employee a LEFT JOIN employee_unit b ON " + "a.employee_code=b.employee_code LEFT JOIN unit c ON b.unit_code=c.unit_code " +
whereClause + " ORDER by last_name, first_name " + tailClause);
</CODE>
b. In most cases, the cyclomatic complexity is actually inevitable because it is part of the requirements. The problem lies in the visual examination of the SQL code interspersed with the Java code.
c. The resulting SQL code is not available to the developer when examining the DAO class. He has to run the code and print it out to be able to get it. Sometimes, this is not possible or too troublesome.

Solution:
a. For a single level of logic, substitution is acceptable. For example, only table name will change,
a simple private replacement method will do.
b. For more than one level of logic, as in the code discussed in the Problem section, make a skeleton of all the possible combination of conditional logic. For each condition, declare a single line of SQL code.
Doing this will make clear all the possible combinations. This will also enable the developer to access a single line of SQL without having to run the program.

Example:
<CODE>
if (isCount){
if (GroupingVO.getAllGroupingCode().equals(seb.getGroupingCode())) {
if (UnitVO.getAllUnitCode().equals(seb.getUnitCode())) {
sql="SELECT COUNT(*) FROM (SELECT DISTINCT a.employee_code...";
}
else{
sql="SELECT COUNT(*) FROM (SELECT DISTINCT a.employee_code...";
}
}
if (GroupingVO.getNoneGroupingCode().equals(seb.getGroupingCode())) {
if (UnitVO.getAllUnitCode().equals(seb.getUnitCode())) {
sql="SELECT COUNT(*) FROM (SELECT DISTINCT a.employee_code...";
}
else{
sql="SELECT COUNT(*) FROM (SELECT DISTINCT a.employee_code...";
}
}
}
else{
if (GroupingVO.getAllGroupingCode().equals(seb.getGroupingCode())) {
if (UnitVO.getAllUnitCode().equals(seb.getUnitCode())) {
sql="SELECT DISTINCT a.employee_code, first_name, last_name...";
}
else{
sql="SELECT DISTINCT a.employee_code, first_name, last_name...";
}
}
if (GroupingVO.getNoneGroupingCode().equals(seb.getGroupingCode())) {
if (UnitVO.getAllUnitCode().equals(seb.getUnitCode())) {
sql="SELECT DISTINCT a.employee_code, first_name, last_name...";
}
else{
sql="SELECT DISTINCT a.employee_code, first_name, last_name...";
}
}
}
</CODE>

Consequences/implications:
1. If there are a lot of resulting combination, there might be a lot of if else loops, but the conditional logic will be clearer since each condition will only have one statement.
2. SQL code in DAO class should not follow the code reuse tenet: factor out common code. SQL code in DAO class should be considered only as String literals.

[Stan James]

I'll assume you're doing this on a server, hope that's right. I would not try to keep code & comments in sync. You'll get burned one day for sure.

If you want to prioritize readability you might just put your simpler SQL statements in text files and cache them up at server startup. The overhead is minimal in the big picture of starting up a complex system. I have a little utility that reads any number of queries from one file and puts them in a map by name:

-- Name Description <- required comment line
any kind of sql
formatted to your taste
terminated with
a semicolon;
 
-- QueryTwo Retrieve all widgets in current billing cycle
etc.

I say simpler cause this will only stand up to relatively simple substitutions or parameterizations.

For building dynamic clauses, you might try to generalize the proces. The base DAO might get kinda deep but the stuff programmers write from day to day could be much simpler. Just one idea ... think about a search criteria object with public variables that match the columns in your database.

WidgetSearchCriteria c = new WidgetSearchCriteria();
c.PartNumber = "123ABC";
c.PartNumberOperator = "EQ";
c.Price = 100;
c.PriceOperator = "LT";
String whereClause = buildSql(criteria);

Imagine a framework using reflection to query public variables and build SQL. Maybe all the values on one criteria object are connected by "AND", multiple criteria objects are connected by "OR".

Such a framework would be a big job and there would always be things you could hand code that the framework couldn't do. You might look to buy or use open source rather than build one.

Different direction ... one thing we do a lot is try to put the complexity into a view and simplify the SQL the developer sees in the code. We don't use stored procs but that would be another option.

[Oliver Chua]

Stan,

Yup, this DAO is used in web apps.
Guess you're right about the dangers of keeping code and comment in sync.
Keeping SQL code in a separate file didn't occur to me, thanks for the idea.

Been down the road of in house frameworks and I can tell you it will be trouble for the long run. I'll be looking at Jakarta DBUtils.

Thanks!

[Stan James]

I agree about going off the deep end on framework building. I'm using a vendor product that generates code for basic CRUD operations from Rational Rose -> XML -> XSL -> Java. It's pretty effective for the basics and has a clean way to hand code SQL if you have to. But building and supporting such a framework is a formidible job. We've reported a few defects and field-built a few enhancements. I kinda wish the vendor had been able to buy instead of build so we'd have wider support. Good luck with that direction!

[Ilja Preuss]

Originally posted by Stan James:

String sql = "select * "
           + "from table "
           + "where column = value "
           + "and column = value";

String or StringBuffer ... Use StringBuffer if you're going to concatenate a bunch of parts a bunch of times. The String building I did above would be fine once at startup, but not a thousand times a second in a heavily loaded system. Then I'd switch to StringBuffer & concat().

The String above actually would get *slower* with a StringBuffer, because the result is a compile time constant - the byte code will return the already concatenated string if you use the code above.

And even if you make the String dynamic, for example

String sql = "select * "
           + "from table "
           + "where column =  " + value
           + "and column = " + value;

using a StringBuffer wouldn't buy you anything, as the compiler will translate that to StringBuffer operations, anyway. Only if you split the String building over several expressions

String sql = "select * ";
sql += "from table ";
sql += "where column =  " + value;
sql += "and column = " + value;

you start getting benefits from using a StringBuffer. (You might also consider using a StringBuilder when working with recent JDKs, which isn't synchronized).

[Stan James]

Yes, I was assuming the dynamic part - variables mixed in with constants. I wasn't aware that String optimized so nicely. Is that recent? We're stuck on 1.3 JDK for now.

Thanks!!

[Ilja Preuss]

Originally posted by Stan James:
Yes, I was assuming the dynamic part - variables mixed in with constants. I wasn't aware that String optimized so nicely. Is that recent? We're stuck on 1.3 JDK for now.

Well, it's *allowed* by the JLS for years now, as far as I remember. And I'm quite sure that Sun's compiler is implemented that way in JDK 1.3, probably even before.

See http://java.sun.com/docs/books/jls/second_edition/html/expressions.doc.html#40226