Table of Contents
The purpose of this framework is to let developers use different sources of data in a consistent manner that leverages the similarities to create common interfaces to access them. It also aims to augment the functions of existing data access mechanisms, again leveraging the similarities between providers to create common extensions.
When starting a new data driven application developers typically need some mechanism to query data with the following capabilities:
-
Pagination of large datasets
-
Dynamic Ordering (for clickable columns)
-
Lazy Loading of data for large datasets
-
Optional restrictions in the query for when parameter values are missing
-
The ability to apply a common front end to the back end data access mechanism so you can reuse both the front and back end to implement forms in minutes.
As a long time Seam user, Seam provides most of this with the
EntityQuery
that can be used to run queries using JPA but it isn't perfect.
I extended the Seam
EntityQuery
and
blogged about it
and it still remains a popular article today when people are
looking for info on JSF and pagination. However this solution
only works in Seam projects but the same functionality is needed
in Wicket, Spring, JSP and even Swing and console apps. Other
frameworks such as Wicket provide a front end and back end
interface for handling ordered paginated datasets, but the
actual implementation is up to the user as this is beyond the
scope of Wicket.
The other source of inspiration is from Borland Delphi which worked on the idea that a common data access interface meant that data driven components could access data without considering where it came from. A standard data interface meant that third party Delphi component developers could create data access components that would work with any kind of data source, from Oracle, MySQL and MS Access to XML or file based datasets as long as there was a data provider implementation for it. When developing in Java, you typically have to code against a common but more specific back end such as a JDBC query and write new interfaces for different view technologies. If you write the front end code for say JPA or Hibernate and you need to access an alternative data source then typically you cannot re-use your existing code for the new data source.
It seems that all these frameworks recognized the need for controlled data access but it was beyond the scope of the individual frameworks to provide a cross framework solution.
So, I scratched my own itch and created this project which defines a common API for querying diverse data sources with pagination, ordering, restrictions and parameter management. This lets us write common view code that can interface with queries that are interchangeable. The goal of this project is to bridge the gaps between these different view and data access frameworks.
Even if you still plan on using a single data access mechanism (i.e. Hibernate or JPA) there are still advantages to using Spigot. It's like a screw driver, 99% of the time you use it for one thing only, but when you need it to pry something open or some other function, it works equally well.
Most projects plan on using only one data access mechanism but by coding to the Spigot API, you can switch implementations later. You might move from JPA to Hibernate or even to plain JDBC queries and most of the code that uses these queries, including parameterized queries and ordering, will remain the same. There may also be times where you need to access different types of data stores such as a file based datasource which needs accessing, paginating and displaying.
Regardless of where the different types of datastores come from, with Spigot you will be able to access them and control the results using your existing view code to paginate and order the data. Even if you don't see yourself switching data access mechanisms or having to access diverse data stores, you may want to just switch a query here and there from a pure ORM type query to a JDBC SQL query but still want to interface with the data and use the features available in your other queries.
Spigot makes it easier to query for the data you need. It features flexible parameter definition by setting the value manually, using EL expressions, extracting the values using reflection or your own custom parameter resolver. Parameters that don't have a value assigned can optionally be left out of the query which can be useful when you are writing search forms where the user may leave out search criteria and you don't want it included in the query. Doing this manually can lead to messy code that is hard to read, test and maintain as you check parameter values, and include or exclude parts of the sql where clause depending on whether there is a value, after which you then have to stitch the query together.
Example 1. Coding search queries the hard way.
String sql = ""; boolean hasClause = false; if (searchCriteria.id != null && searchCriteria.id.length() != 0) { if (sql.length()!= 0) { sql = sql + " AND "; } sql= sql+ "p.id = :id"; paramsMap("id",searchCriteria.id); } if (searchCriteria.firstName != null && searchCriteria.firstName.length() != 0) { if (sql.length()!= 0) { sql = sql + " AND "; } sql= sql+ "p.firstName = :firstName"; paramsMap("firstName",searchCriteria.firstName); } //check we have at least one restriction if (sql.length() != 0) { sql = " WHERE " + sql; } //build the select statement sql = [select statement] + sql; //build the query qry = createQuery(sql); //add the parameters into the query for (String key : paramsMap.keySet) { qry.setParameter(key,paramsMap.get(key)) } //get the results List<Person> results = qry.resultList();
With Spigot, you can define the query, specify how the parameters are resolved and the data provider will take care of generating the right request so that the only restrictions included in the query are ones with parameter values assigned.
Example 2. Coding search queries the easy way.
//define the parameterized restrictions qry.addRestriction("p.id = :id"); qry.addRestriction("p.firstName = :firstName"); qry.addRestriction("p.lastName = :lastName"); //set the parameter resolver ParameterResolver resolver = new ReflectionParameterResolver(searchCriteria)); qry.addParameterResolver(resolver); //get results List<Person> results = qry.resultList();
Spigot lets you order your data based on order key values as
opposed to explicit table column values which could be prone
to SQL injection attacks. By using an
orderKey
value which is translated server side into a sorting
representation, you can control what values are used for
setting the order of the dataset ensuring no harmful SQL is
inserted into your query.
At the heart of Spigot is a simple API which allows you to turn anything into a source of data that can be accessed and paginated from existing view code. This can range from custom datasources or third party applications to text, csv, remote data points, binary files or even just a static in-memory list of objects.