Modularized persistence


There are many libraries out there that contain reusable code, but only a few of them persist data in relational database. Even if they do so, they behave normally as standalone project. It is hard to use their tables in custom queries of the project that embeds them.

Our goal is to create a technology stack that allows to embed logically related table sets into separate modules. There are many use-cases in which reusable persistent data structure can be defined. With a well defined API, complicated queries can be eased.

A reusable module with relational database tables must meet the following requirements:

  • The module must provide an API that makes it possible to easily create or modify the records of the tables
  • It must be possible to reference the tables with foreign keys from other modules
  • The module should provide API to easily extend SQL queries

During a project many parts can be separated that can be implemented in a reusable way. This post shows some practical use-cases and the way how they can be implemented.


Imagine that you have a User entity with the following fields:

  • userId
  • name
  • birth_date
  • country_code

Task: Show a filtered and paginated list of users on a website. The list should be ordered by the localized country name

Low-performance solution

  • Select all the users that meet the filter criteria. If we have 1 million results, than download all of them into memory.
  • Translate the country name based on a ResourceBundle for each user
  • Order the list of users
  • Show the current page (Twenty records from the million)

High performance solution with the re-usable Localization module:

Create a table called localized_data with the following fields:

  • key
  • locale
  • default_locale (boolean)
  • value

Fill this table with the country codes and with their translations. Example:

key locale default_locale value
country.DE de false Deutschland
country.DE en true Germany
country.DE en_US false Germany
country.HU en true Hungary
country.HU hu_HU false Magyarország

We have the original SQL:

    FROM user u

We would like to change the SQL in the way that:

  • Country name is selected from the localized_data table
  • The query gives back 20 rows beginning with the row index 10.000 of the result set.
  • If the country name is not available in the locale of the user (e.g.: en_US), select the country name with language of the user (e.g.: en)
  • If the country name is not available in the language of the user, select the country name with a default language
  • If the country name not available at all in the localized_data table, get the country code

The modified SQL would look like this:

        (SELECT ld.value
          FROM localized_data ld
          WHERE ld.key = u.country_code
            AND ld.locale = :userLocale),

        (SELECT ld.value
          FROM localized_data ld
          WHERE ld.key = u.country_code
            AND ld.locale = :userFallbackLocale),
        (SELECT ld.value
          FROM localized_data ld
          WHERE ld.key = u.country_code
            AND ld.default_locale),
        u.country_code) AS country_name,
  FROM user u
  ORDER BY country_name
  LIMIT 20 OFFSET 10000;

The SQL above looks difficult.  Re-implementing this query at each place is almost impossible. It is time to find a technology that supports Object-Oriented SQL queries to be able to separate complex  query parts to reusable functions. Some solutions that allow this:

In former times we used JPA Criteria API. Recently we changed to QueryDSL due to its benefits. The original query would look like this with QueryDSL:

QUser user = new QUser("u");
SQLQuery query = new SQLQuery(...);
resultList = query.from(user).list(..., user.countryCode, ...);

It would be hard to write down the COALESCE logic every place where we want to have localized data as part of the result set. Let’s make a function called createLocalizedCoalesce(DSLExpression<String> key, Locale locale). With that function, it will be easy to write the robust query:

QUser user = new QUser("u");

Coalesce countryNameExpression = createLocalizedCoalesce(user.countryCode, Locale locale);
DslExpression<String> countryNameAs =;

SQLQuery query = new SQLQuery(...);
resultList = query.from(user)
                  .list(..., countryNameExpression, ...);

Building a query with the complex localized_data logic is not that difficult anymore.

Initialize database schema

Defining tables, views, versions

We chose QueryDSL to write queries. However, we need a tool that can populate the database schema.

We investigated lots of the technologies that supports creating and modifying tables and Liquibase won. In our solution, each module contains a liquibase changelog file that defines the database schema. In the localization module it looks like the following:

<changeSet id="1.0.0" author="everit">
  <createTable tableName="localized_data" schemaName="org.everit.osgi.localization.schema">
    <column name="localized_data_id" type="bigint" autoIncrement="true">
    <constraints primaryKey="true" nullable="false"  />
    <column name="default_locale" type="boolean" >
      <constraints nullable="false" />
    <column name="key_" type="varchar(255)" />
    <column name="locale_" type="varchar(10)" />
    <column name="value_" type="varchar(2000)" />
  <addUniqueConstraint columnNames="key_, locale_" constraintName="unique_key_locale"
       schemaName="org.everit.osgi.localization.schema" tableName="localized_data" />

Initializiation of the database

Our stack is based on OSGi and Declarative Services. Using the right modules and components we are able initialize the database before the business logic is activated. This goal can be achieved by the configuration of the following components:

Database initialization

Database initialization

  • First of all, we need a JDBC driver that registers a DataSourceFactory as an OSGi service
  • Everit DSF component picks up the DataSourceFactory service and registers an XADataSource OSGi service
  • Everit – Commons DBCP component picks up the XADataSource service and by instantiating a BasicManagedDataSource it registers a DataSource OSGi service
  • Everit Liquibase DataSource component
    • picks up the DataSource service that was registered by Everit – Commons DBCP component
    • tries to initialize/validate the database with the specified Liquibase changelog file
    • if the initialization/validation is successful, the DataSource object will be registered again as an OSGi service but with different service properties
  • Based on service properties, business components can pick up the DataSource that was registered by Liquibase DataSource component

With a configuration like this, we can be sure that the database is up-to-date when the business components are activated.

How does the Liquibase DataSource component know, where it should find the changelog file?

Each bundle that contains one or more Liquibase changelog files. They must also provide one or moreliquibase.schema capability. The capability can have the following attributes:

  • name: The “name” of the schema that identifies the changelog file. By “schema” I mean the identifier of  a logicaly  related set of tables, views and sequences. The meaning of “schema” capability attribute is not the same as a database schema.
  • resource: The path of the changelog file within the bundle
  • custom attributes: Any custom attribute may be specified. They can be useful if we want to allow special filters on the consumer side (e.g.: version, dbtype, …)

In case of localization, the capability MANIFEST header looks like the following:

Provide-Capability: liquibase.schema;name=org.everit.osgi.localization.schema;resource=/META-INF/osgi.localization.liquibase.xml

Everit – Liquibase DataSource component should be configured to pull this capability. To do that, simply specify the following at the liquibase.schema configuration property: org.everit.osgi.localization.schema.

It is possible to add directives in the configuration of the Liquibase DataSource component. E.g.: org.everit.osgi.localization.schema;filter:=(version>=1.0.0)

Referencing tables and views from other modules

By using Liquibase it is possible to include a changelog file from other changelogs. In case we use the Liquibase jar that is bundled by Everit, it is also possible to include changelog files from other bundles. To do that:

  • The consumer bundle must have a Require-Capability MANIFEST header that refers to the capability offered by the producer bundle. By doing that, a wire will be created between the bundles. Our solution finds inclusions based on these wires.
  • the include tag must define the path with the “eosgi:” prefix and add the name of the liquibase.schema capability as value instead of the path

If we want to use the Localization module in our project, we should include the liquibase changelog in the following way in the changelog of the application:

<include file="eosgi:org.everit.osgi.localization.schema"/>

And specify the following requirement in the MANIFEST of the application bundle:

Require-Capability: liquibase.schema;filter:=(name=org.everit.osgi.localization.schema)

The wiring and inclusion above works transitively. E.g.: If you include authorization tables in your application, you include its dependencies transitively as well. As resource is included in authorization, the resource table will be created/validated during the application initialization.

Generating the QueryDSL metadata classes

Writing  QueryDSL metadata classes manually would be a hard task. There is a tool called LQMG that generates the Metadata classes from the Liquibase capabilities for us. This tool has the following steps:

Steps of code generation

Steps of code generation

  • Starts an embedded OSGi container (Equinox)
  • Deploys the specified bundles. Those bundles should be specified that contain liquibase capabilities.
  • Starts an embedded H2 server
  • Based on the specified liquibase capabilities, initializes the embedded H2 database. This is done in the same way as Liquibae DataSource works
  • Generates the QueryDSL metadata classes from the H2 databsae

It is also possible to specify renaming rules in LQMG to generate class names and properties that are easier to read in the source code.

If we use lqmg-maven-plugin, the dependencies of the projects will be handled as the OSGi bundles.

The Localization module will be available on GitHub soon.


The Resource project is already available at GitHub. Resource has no own functionality. It is simply a table that has a generated id. Resource is often used in other modules to wire their functionality to each other.


Databsae Sequence could have been used as well, but we wanted to let the developers find out if any other module uses the same resource id. In case a foreign key references the resource record, it cannot be deleted until all module allows it.


The authorization module has already been implemented by Everit based on JPA-OSGi technology stack. As we are changing our technology stack, it will be available soon based on Liquibase-QueryDSL at GitHub.

To have a reusable authorization module, we can use the following relational schema:

Relational schema of Authorization

Relational schema of Authorization

permission table

Defines a permission between two resources.

  • authorized_resource: The one, who has a permission on something. E.g.: user, role, user group, external system, etc.
  • target_resource: Resources which other resources can run action on. It might be a document that a user can open and edit or a user who can be deleted or modified
  • action: The action of the permission. E.g.: If the authorized resource was a user and the target resource was a document, actions could be ‘open’, ‘edit’, ‘delete’, …

permission_inheritance table

It is possible to define inheritance between authorized resources. The child resource inherits all of the permissions of the parent. A parent could be a user group, role or organization, while a child could be a user or user group. The table is abstract enough to let the designer of the application decide, how to describe roles, groups, users and the relation between them.

Functionality of authorization

First of all, the authorization module contains functions that make it possible to manipulate permissions between resources and the permission inheritance DAG. It also contains a permissionChecker that can

  • calculate the inheritance and provide it from cache next time
  • provide permission information for an action between two resources
    • from the database
    • from cache

And last but not least, the authorization component provides functionality to extend QueryDSL based queries with permission restrictions. Imagine the following query:

  FROM document d
  JOIN document.attachment a;

We can define permissions on every entity that contains a foreign key to the resource table. Every entities can be found in a SQL statement behind FROM and JOIN parts may be authorized or target resource. In this case we can define permissions on documents and on their attachments. We define the following function:

Predicate createPermissionPredicate(
       Expression<Long> authorizedResourceIdExpr,
       Expression<Long> targetResourceIdExpr,
       String[] action);

The function generates the following predicate (in QueryDSL of course):

EXISTS(SELECT 1 FROM permission p
         WHERE p.authorized_resource_id IN (?, ...)
           AND p.target_resource_id = d.resource_id
           AND action IN (?, ...))

By using QueryDSL and the function above, it is easy to extend every queries with authorization restrictions.

Full-text search

This is more a concept for us at the moment. We realized that full-text searching together with permission checks cannot be implemented effectively if the index is outside of the database. We saw huge document management systems that did the following:

  • Doing a full-text search with a technology like Lucene
  • Checking permissions on each record one-by-one
  • Showing twenty records on the user interface from the middle of the result set

And saw them die on requests that had larger result sets :).

Every modern DBMS has built in full-text search support:

  • H2-Lucene
  • MySQL – Sphinx
  • PostgreSQL – TSearch2
  • MSSQL – Some Microsoft stuff 🙂
  • Oracle – Oracle Text

All of these allow to have FTS as part of a complex SQL statement. We believe that by using the FTS and the authorization modules together the two logic can be migrated into one SQL statement easily. By doing that, only the number of final results have to be transported from the database.

Other use-cases

After the developer starts to design projects with this concept, he/she will find many reusable parts. Some ideas: historical tables with data mining functions, audit records, currencies with exchange rates, geolocations, gtfs, …


Richconsole 1.2.1 is released

Issues resolved:

  • Activate all bundle that are in the dependency closure of the installed bundle
  • Richconsole hangs during hiding the deployer window
  • Support dropping projects from Eclipse package explorer to the deploy window
  • NullpointerException if MANIFEST.MF does not contain Bundle-SymbolicName

transaction-helper 1.0.1 and 1.1.0 are released

I am happy to announce the version 1.0.1 and 1.1.0 of transaction-helper.

Version 1.0.1 is a bugfix version. The logic of OSGi and transaction propagation is also separated into two different classes, therefore it is possible to use the solution outside of OSGi. Version 1.0.1 is compiled to be compatible with Java 5.

Version 1.1.0 is compatible with Java 7. When a suppressed exception occurs, the Throwable.addSuppressed() function is called instead of logging the exception.

Commons DBCP Component 2.0.1 is released

The 2.0 release of commons dbcp brought a big performance improvement. However, it had an annoying bug that made it impossible to use an XA enabled datasource. Now 2.0.1 is released and the blocker bug is solved.

A new version of Everit Commons DBCP Component has just been released, too. The only change in this version is that it points to the 2.0.1 version of commons-dbcp as a dependency.

Serving static web resources in an OSGi environment

After having the persistence layer, I was thinking how to serve static resources in an OSGi based web environment. I definitely do not want to create WABs with web.xml files. This is I have come up so far:

In case we would like to offer static resources from a bundle we add the following capability to it one or more times:
Provide-Capability: static.resources
Possible attributes of the capability:
  • path: Path in the bundle where from we would like to export resources
  • filePattern: Pattern of the files that can be served. E.g.: *.gif
  • recurse: Export files from sub-directories or not. Default is true.
  • scope: Only export files from the bundle or from the imported packages as well. Possible values: local / wiring. Default is local
  • mimeConfig: Location of an optional properties file inside the bundle where file extension – metatype values are paired. E.g.: gif=image/gif
  • someAttributeKey: Optional attributes that will be listed in the OSGi service properties (see below)
A full example:
Provide-Capability: static.resources;path=/pathinbundle/;filePattern=*;recurse=true;scope=wiring;someAttributeKey=someValue

An extender picks up every bundle that contains the “static.resources” capability and registers a StaticResourceProvider OSGi service for each capability. The service properties come from the capability attributes. The functions of the StaticResourceProvider interface:

  • listFiles(String folder): Listing files and sub-directories inside the specified folder
  • getResourceAsStream(String path)
  • getMimeType(String path)
The StaticResourceProvider OSGi services can be used by the component called StaticResourceServlet. The configuration options of the component:
  • OSGi filter that addresses one or more static resource provider service
  • acceptRanges: Whether range queries are accepted or not (HTTP 1.1)
  • dirAllowed: If directory listing is allowed or not
  • styleSheet: Location of a CSS static resource that could decorate directory listing
  • welcomeFileNames: Comma separeated list of welcome file names. If directory url is provided, it will be redirected to the welcome file if that exists.
  • gzFileRegex: If a file name with path matches with this optional regular expression, it will be sent unpacked, via a compressed stream if available (HTTP 1.1)
  • etags: Whether etags are allowed or not (HTTP 1.1)
  • maxCacheSize: The maximum total size of the cache or 0 for no cache.
  • maxCachedFileSize: The maximum size of a file to cache
  • maxCachedFiles: The maximum number of files to cache
  • cacheControl: If set, all static content will have this value set as the cache-controlheader
  • otherServiceProps: String array of key-value pairs that should be put into the properties of the registered OSGi service. E.g.: Putting whiteboard pattern specific properties.

The configuration properties of StaticResourceServlet component can be familiar from DefaultServlet of Jetty.



I implemented a similar solution and it is now available at github.

The specification was changed a bit. It is more simple. As soon as I used it in a project, I will upload it to maven central.

Downloading WSDL files for offline use

Another blog post from the past.

We had the problem that one of the developer worked on the service layer of a project and the other wanted to use the functions of it. The service layer was exposed to the internet as web services. As the function signatures of the service layer had not been finalized yet there was the question how to re-generate the client side code on the other side each times the api had been changed.

It was pretty clear that as using maven for compiling the source the ws-import maven plugin should be used. However an url of the wsdl file must be provided for that plugin and this is where the problems came into the picture. The wsdl contained several xsd:import xml tags which made it pretty hard to download always the wsdl files with the xsd files it imports by hand. Also the value of schemalocation attribute had to be always changed to the location of the downloaded xsd files.

Luckily it was not hard to write a code snippet that downloads a wsdl (which is an xsd as well) from an url and if it imports other xsd files it downloads them as well. Also the code snippet changes the schemalocation in the downloaded wsdl files.

The code snippet is the following:

 package hu.everit.utils.xsd.downloader;

import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import javax.xml.namespace.NamespaceContext;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class XsdDownloader {

	public static class XsdNameSpaceContext implements NamespaceContext {

		private final Map<String, String> nameSpaceUrisByPrefixes;

		public XsdNameSpaceContext() {
			nameSpaceUrisByPrefixes = new HashMap<String, String>();

		public String getNamespaceURI(final String prefix) {
			return nameSpaceUrisByPrefixes.get(prefix);

		public String getPrefix(final String namespaceURI) {
			// TODO Auto-generated method stub
			return null;

		public Iterator getPrefixes(final String namespaceURI) {
			// TODO Auto-generated method stub
			return null;


	 * @param args
	public static void main(final String[] args) {
		if (args.length != 2) {
			System.out.println("Only two parameters: 1-url 2-downloadPrefix");
		String xsdUrl = args[0];
		String filePrefix = args[1];
		XsdDownloader xsdDownloader = new XsdDownloader();
		try {
		} catch (TransformerConfigurationException e) {
			// TODO Auto-generated catch block
		} catch (IOException e) {
			// TODO Auto-generated catch block
		} catch (ParserConfigurationException e) {
			// TODO Auto-generated catch block
		} catch (SAXException e) {
			// TODO Auto-generated catch block
		} catch (TransformerException e) {
			// TODO Auto-generated catch block

	Map<String, String> fileNamesByprocessedUrls =
			new HashMap<String, String>();

	private String downloadPrefix;

	private void downloadXsdRecurse(final String xsdUrl) throws IOException,
			ParserConfigurationException, SAXException, TransformerException {

		String outputFileName = downloadPrefix;
		if (fileNamesByprocessedUrls.size() > 0) {
			outputFileName =
					outputFileName + "." + fileNamesByprocessedUrls.size();
		outputFileName = outputFileName + ".xsd";
		fileNamesByprocessedUrls.put(xsdUrl, outputFileName);

		DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
		DocumentBuilder db = dbf.newDocumentBuilder();
		Document doc = db.parse(xsdUrl);


		File outputFile = new File(outputFileName);
		TransformerFactory trf = TransformerFactory.newInstance();
		Transformer tr = trf.newTransformer();
		Source source = new DOMSource(doc);
		Result result = new StreamResult(outputFile);
		tr.transform(source, result);

	private void processElementRecurse(final Element node) throws IOException,
			ParserConfigurationException, SAXException, TransformerException {
		NodeList nl = node.getChildNodes();
		for (int i = 0, n = nl.getLength(); i < n; i++) {
			Node childNode = nl.item(i);
			if (childNode instanceof Element) {
				Element childElement = (Element) childNode;
				if ("".equals(childElement
						&& childElement.getLocalName().equals("import")) {
					String schLoc = childElement.getAttribute("schemaLocation");
					if (!fileNamesByprocessedUrls.containsKey(schLoc)) {
						String newLoc = fileNamesByprocessedUrls.get(schLoc);
						if (newLoc != null) {
							childElement.setAttribute("schemaLocation", newLoc);
					} else {
						String newLoc = fileNamesByprocessedUrls.get(schLoc);
						childElement.setAttribute("schemaLocation", newLoc);
				} else if (""
						&& childElement.getLocalName().equals("import")) {
					String schLoc = childElement.getAttribute("location");
					if (!fileNamesByprocessedUrls.containsKey(schLoc)) {
						String newLoc = fileNamesByprocessedUrls.get(schLoc);
						if (newLoc != null) {
							childElement.setAttribute("location", newLoc);
					} else {
						String newLoc = fileNamesByprocessedUrls.get(schLoc);
						childElement.setAttribute("location", newLoc);
				} else {

	public void setDownloadPrefix(final String downloadPrefix) {
		this.downloadPrefix = downloadPrefix;

The main function of the class above accepts two parameters. The first one is the url of the wsdl file and the second is the prefix of the file that will be the beginning of the downloaded files. The downloaded files will be named as PREFIX.xsd, PREFIX.1.xsd, PREFIX.2.xsd, … The first one is the main one that the specified url contained, the others are the imported. The code snippet also takes care of the cyclic imports so the files that were downloaded ones already will not be downloaded again.
The mavenized project with the compiled executable jar is available here. To run it enter the following command: java -jar everit-xsd-downloader-1.0.jar URL FILEPREFIX 

Everit Liquibase modules on maven-central

Recently, I decided to leave JPA and try something new concerning persistence. I evaluated many technologies and found Liquibase and QueryDSL to be the best for our needs. Both of them have their unique advantages.

Liquibase is perfect for maintaining database schemas. It is also possible to follow version changes when a deployment is performed and also to generate upgrade SQL scripts.

QueryDSL could work as a replacement of JPA Criteria API for us. JPA helped us to start a completely new way of programming. We could place our entities into separate modules and write utility functions that generate only a snippet from the complete SQL statement. However, we reached the limitations of JPA Criteria API soon. After I started to use QueryDSL, I felt that I superseded those limitations.

To use these technologies, we needed new tools and modules. We decided to use OSGi as the base of our framework, therefore, all our technologies must be modularized. By modularization, I do not only mean to extend the MANIFEST.MF file of the JAR files, but also to extend the functionality of our technologies to make it possible to use them within the concept of modularization. The following tools were implemented recently:

  • liquibase-bundle: I extended the functionality of Liquibase to be able to import database schema snippets from different bundles. The snippets can be imported transitively. I also enhanced the standard OSGi support of Liquibase a little bit. With the change, extensions can be deployed as fragment bundles. I would like to contribute to the Liquibase project with the code so that I do not have to maintain the changes.
  • liquibase-component: A simple OSGi Declarative Services component that uses the functionality of Liquibase-bundle to process changelog files. The component can be configured. One option is to upgrade the database, the other one is to dump the changes to SQL scripts so the db admin can perform the upgrade later. I am not sure if this component will have many future versions. It might happen that the code of the component will be integrated into the liquibase-datasource.
  • liquibase-datasource: A configurable DataSource component. It needs a DataSource to access the database and also a database schema expression that points to the liquibase changelog file. After the database migration is finished, the component registers a new DataSource OSGi service.
  • osgi-lqmg: Liquibase-QueryDSL-Metadata-Generator can be used to generate QueryDSL metadata classes directly from Liquibase changelog files. This tool uses the OSGi concept too, so instead of specifying the exact location of the changelog file, a Bundle-Capability must be defined.
  • lqmg-maven-plugin: A maven plugin that uses osgi-lqmg to generate Liquibase Metadata classes. The plugin deploys the dependencies into an embedded OSGi container and uses them to find the Liquibase changelogs.

With the tools above, it is possible to use Liquibase and QueryDSL together easily within the OSGi world. I hope that this will become a common solution in the future.