Vita Rara: A Life Uncommon

Separating SQL and Java Code


Categories: | |

I have been programming in Java for about eight years now. One of the persistent issues I have had is writing programs that will work with more than one database. In general I use EJB to handle persistence in my business layer. The issue that has remained is keeping SQL out of the code for simple things like screen displays and report generation, either to the screen or to PDF files for printing.

I've done some looking around and have not found a solution yet. I might be missing it, and if you know of one please let me know. In the mean time myself and my fellow programmer John came up with the following. It is by no means complete. This is an idea at this point, but one I think has merit.

QueryBuilder

A Java based library for the generation of database platform independent SQL queries using an XML descriptor. Your code will no longer have to have any SQL code in it to do such things as: generating a list of items using a join where you don't want to roll a custom backing bean; build multi-part queries using an arbitrary number of arguments from multiple tables such as a search page with multiple independent criteria drawn from multiple tables in your data model.

The XML descriptor would look something like this:

<querybuilder>
	<query name="findCustomer">
		<select>
			<clause type="default">customer.*<clause>
		<select>
		<from>
			<clause type="default">
				customer
				join personcustomerjoin on customer.id = personcustomerjoin.customerid
				join person on personcustomerjoin.personid = person.id
			<clause>
		<from>
		<where>
			<clause name="byContactName">
				person.name = ?
			<clause>
			<clause name="byStartDate" type="default">
				customer.startDate = ?
			<clause>	
			<clause name="byStartDate type="mysql">
				TO_DATE(customer.startDate) = TO_DATE(?)
			<clause>
			<clause
		<where>	
		<orderby>
			<clause type="default">
				customer.name
			<clause>	
		<orderby>
	<query>
<querybuilder>	

So, what does that do? The example has defined one query "findCustomer." The query tag has a select, from, where and orderby section. You could also add groupby and any other ones that would be necessary to express the full SQL systax.

Each of the select, from, where, etc. sections contain clause tags, which can take a name or type attribute. The "default" type is a reserved type. Other types can be specified by the user as needed to support as many different database engines as they want. Simply add type="Oracle" for instance to have Oracle SQL statements.

Now, how would you use this. This is easiest demonstrated with pseudo code. I haven't tested this. None of it is written yet. I'm not sure I ever will.

	QueryBuilder qb = QueryBuilder.getInstance ();
	Query q = QueryBuilder.getNamedQuery ("findCustomer");
	
	if (request.contains ("contactName") ) {
		q.setWhereClause ("byContactName", request.get ("contactName") );
	}
	if (request.contains ("startDate") ) {
		q.setWhereClause ("byStartDate", request.get ("startDate") );
	}
	ResultSet rs = q.execute ();

	// Do something with the ResultSet.

As you can see the QueryBuilder is a Singleton. You would need to initialize it somemplace in your system, or it could be implemented to automatically get its information and configure itself from the environment. Not exactly sure how but here's a code example idea.

	QueryBuilder qb = QueryBuilder.getInstance ();
	qb.setQueryDefinitions (queryBuilderXmlFile);
	qb.setDataSource (someDataSource);
	qb.setDataSourceType ("mysql");

This code gets a handle to the system wide QueryBuilder implementation, passes it the xml file for parsing, sets the datasource to use for executing queries, and tells it the type of data source it is.

So, given the initialization of the QueryBuilder per the second example, we would only be returned queries that would work in MySQL. Any place where there is a default and a mysql alternative the QueryBuilder would opt to use the MySQL specific code snippets that have been provided in the XML file. So, the first code example would result in the following query:

	select 
		customer.* 
	from 
		customer
		join personcustomerjoin on customer.id = personcustomerjoin.customerid
		join person on personcustomerjoin.personid = person.id
	where
		person.name = somevalue
		AND TO_DATE(customer.startDate) = TO_DATE('2005-01-01 00:00:00')
	order by
		customer.name

That's it for the moment. More on this later after I have some working code. In the mean time what do you think? Is there something out there that already does this?

Mark

A new component to build SQL queries programmatically

Hello,

May be you'll be interested about our new Java component to build SQL queries programmatically? It doesn't use XML, but has API to do what you want. It is called Active Query Builder, you may try it at www.activequerybuilder.com

We'll be glad to hear any wishes, suggestions and bug reports from you.

Nice.

Nice idea!

I've been struggling for far too long with Hibernate... Its Criteria query is not only unbelievably buggy (just TRY to do subqueries in it!), it's actually architecturally flawed (try to join the same table twice; it's not possible now, and according to the bug report, it isn't planned to be fixed).

Send up a signal when you get a preliminary version!

yep

yeah yeah Hibernate , these

yeah yeah Hibernate , these mapping API's are good only when things are rosy ! Mark I juust love your idea . I work on a project which has got horrendous queries( diffrennt schemas, not normalised tables etc) build dynamically in java code .

cheers !