I need to build a simple web interface into a database. It would only allow for querying the database and would generate XML for its output.

Ideally it would be done in such a way that new queries coudl be added with very little difficulty.

Since I want the interface to be RESTful the URLs would look something like this:

• /rest/person/([0-9]+) - returns a single person using a unique numeric identifier; assume that there are many 'types' of people: employee, vendor, etc.
• /rest/people?field1=___&field2=___ - returns a list of people whose values for fields 1 and 2 match the values specified

The URL pattern would map to a select statement (i.e. select * from entity where field1 = ___ and field2 = ___) which, when executed, would generate a result set. That result set then needs to be transformed into an XML document.

I'll describe one possible implementation and hope for some input or recommendations.

Since the result of a query is just a "list of maps" it could easily be represented in XML. You would just need to specify the query and a suitable name for the entities returned:

<mapping pattern='/rest/person/([0-9]+)'>
	<query>select * from person p 
		left join employee e on p.id=e.id 
		left join vendor v p.id=v.id 
		where e.id=$1</query>
	<name>person<name>
</mapping>
<mapping pattern='/rest/people'>
	<query>select * from person p 
		left join employee e on p.id=e.id 
		left join vendor v p.id=v.id 
		where p.field1=$field1 
		and p.field2=$field2</query>
	<name>person</name>
</mapping>

Some Java code to iterate over the ResultSet (and ResultSet.MetaData) would generate something like:

<results>
	<person>
		<id>1234</id>
		<name>jane doe</name>
		<type>employee</type>
		<manager>1236</manager>
	</person>	
	<person>
		<id>1235</id>
		<name>joe shmoe</name>
		<type>vendor</type>
		<companyname>acme</companyname>
	</person>
</results>

This XML would probably not be ideal but applying it to an XSLt transformation afterwards would take care of that. The XSLt could be specified along with the mappings in an XML config file.

Now I've left out a lot of detail and most of this is off the top of my head but I am hoping some of you have some ideas.