Solutions to IT problems

Solutions I found when learning new IT stuff

Creating a Framework for Chemical Structure Search – Part 6

leave a comment »


Series Overview

This is Part 6 – Data Access Layer of the “Creating a Framework for Chemical Structure Search“-Series.

Previous posts:

Follow-ups:

Introduction

In the previous article I introduced the entity model of MoleculeDatabaseFramework. This article will explain the Data Access Layer which uses Spring-Data-JPA with Hibernate and how the Chemical Structure Search methods of the Bingo PostgreSQL Cartridge are exposed to Hibernate and QueryDSL.

How Spring-Data JPA works

Basic functionality

I quote from Spring-Data website:

Spring Data JPA aims to significantly improve the implementation of data access layers by reducing the effort to the amount that’s actually needed. As a developer you write your repository interfaces, including custom finder methods, and Spring will provide the implementation automatically.

You create a new interface that extends from generic interfaces provided by Spring-Data and represents the repository for an entity. There are different kinds of repository interfaces but the repositories in MoleculeDatabaseFramework all extend JpaRepository. JpaRepository provides CRUD-methods and some retrieval methods for your entity.

Repositories in MoleculeDatabaseFramework also extend QueryDslPredicateExecutor. This adds findOne(predicate) and findAll(predicate) methods. Predicates are basically type-safe WHERE-Clauses.

Custom query methods

Besides the provided methods you can add your custom search methods by following the findBy-method conventions of Spring Data JPA or by annotating a method with @Query were the value of the annotation is either a JPQL Query or native SQL.

Custom Queries providing your own method implementation

In case you have a very complex query that can’t be automatically created by Spring-Data, you can create them yourself.

1. Create Custom Query Interface

To achieve this you need to first create an interface containing the desired query method(s) and annotate it with @NoRepositoryBean:

@NoRepositoryBean
public interface ChemicalStructureSearchRepository<T> {

    Page<T> findByChemicalStructure(String structureData,
            StructureSearchType searchType,
            Pageable pageable, Predicate predicate,
            String searchOptions,
            PathBuilder<T> pathBuilder);


    Page<T> findBySimilarStructure(String structureData,
            SimilarityType similarityType,
            Double lowerBound, Double upperBound,
            Pageable pageable, Predicate predicate,
            PathBuilder<T> pathBuilder);
}

This is the Source Code of ChemicalStructureSearchRepository minus JavaDoc comments.

2. Create a repository extending Custom Query interface

As an example below the Source Code for ChemicalCompoundRepository which extends ChemicalStructureSearchRepository:

@Repository
@Transactional(propagation = Propagation.MANDATORY)
public interface ChemicalCompoundRepository<T extends ChemicalCompound>
        extends ChemicalStructureSearchRepository<T>, JpaRepository<T, Long>,
        QueryDslPredicateExecutor<T> {
    
    List<T> findByCompositionsPkChemicalStructureId(Long structureId);
    
    T findByCas(String cas);

    @Query("select c from Containable c where c.chemicalCompound = ?1")
    List<Containable> getContainablesByCompound(ChemicalCompound compound);
}

3. Create an implementation of your repository

The convention is that the implementation is named after the repository with “Impl” appended, in this case ChemicalCompoundRepositoryImpl. This implementation must only implement your custom methods in this case defined in ChemicalStructureSearchRepository.

public class ChemicalCompoundRepositoryImpl<T extends ChemicalCompound>
        implements ChemicalStructureSearchRepository<T> {

	//...fields and constructors snipped...

    @Cacheable(STRUCTURE_QUERY_CACHE)
    @Override
    public Page<T> findByChemicalStructure(String structureData,
            StructureSearchType searchType, Pageable pageable,
            Predicate predicate, String searchOptions,
            PathBuilder<T> compoundPathBuilder) {
			
			//...implementation snipped...
    }


    @Cacheable(STRUCTURE_QUERY_CACHE)
    @Override
    public Page<T> findBySimilarStructure(String structureData,
            SimilarityType similarityType, Double lowerBound, Double upperBound,
            Pageable pageable, Predicate predicate,
            PathBuilder<T> compoundPathBuilder) {
			
			//...implementation snipped...
    }
}

Below an UML Class Diagram that shows the relationships of ChemicalCompoundRepository:

ChemicalCompoundRepository UML

Spring-Data automatically detects the repository implementation and combines all provided and all your custom search methods into one object which you use by calling them from ChemicalCompoundRepository.


Page<T> page = getRepository().findByChemicalStructure(structureData, searchType,
                pageable, predicate, searchOptions, pathBuilder);

Using the Repositories

MoleculeDatabaseFramework provides generic repositories for all entities in the entity model.

Source Code for all Repositories

To make use of a chemical structure search enabled repository you need to extend it using your specific entity implementation and optionally add your custom find methods:

@Repository
public interface RegistrationCompoundRepository extends ChemicalCompoundRepository<RegistrationCompound> {

    List<RegistrationCompound> findByRegNumberStartingWith(String regNumber);

}

That’s it!

You can find further information on how to implement entities and repositories in the MoleculeDatabaseFramework Tutorial as this article is meant to show the inner workings of the framework and not how to use it.

Exposing Bingo PostgreSQL Cartridge Methods

This is done by using a custom dialect extending Hibernates PostgreSQL82Dialect:

public class BingoPostgreSQLDialect extends PostgreSQL82Dialect {

    public BingoPostgreSQLDialect() {
         registerFunction("issubstructure", new SQLFunctionTemplate(
                 StandardBasicTypes.BOOLEAN, "?1  @ (?2, ?3)::bingo.sub"));
         registerFunction("isexactstructure", new SQLFunctionTemplate(
                 StandardBasicTypes.BOOLEAN, "?1  @ (?2, ?3)::bingo.exact"));
         registerFunction("matchessmarts", new SQLFunctionTemplate(
                 StandardBasicTypes.BOOLEAN, "?1  @ (?2, ?3)::bingo.smarts"));
         registerFunction("matchesformula", new SQLFunctionTemplate(
                 StandardBasicTypes.BOOLEAN, "?1  @ (?2, ?3)::bingo.gross"));
         registerFunction("issimilarstructure", new SQLFunctionTemplate(
                 StandardBasicTypes.BOOLEAN, "?1  @ (?2, ?3, ?4, ?5)::bingo.sim"));
         registerFunction("hasmassbetween", new SQLFunctionTemplate(
                 StandardBasicTypes.BOOLEAN, "?1 > ?2::bingo.mass AND ?1 < ?3::bingo.mass"));         
    }
}

And as a usage example a source code snippet from ChemicalCompoundRepositoryImpl:

public Page<T> findByChemicalStructure(String structureData,
            StructureSearchType searchType, Pageable pageable,
            Predicate predicate, String searchOptions,
            PathBuilder<T> compoundPathBuilder) {
			
	//...snipped...
			
	BooleanExpression matchesStructureQuery; // this is a Predicate!

	switch (searchType) {
		case EXACT:
			matchesStructureQuery = BooleanTemplate.create(
					"isExactStructure({0},{1},{2}) = true",
					structure.structureData,
					ConstantImpl.create(structureData),
					ConstantImpl.create(searchOptions));
			break;
		case SUBSTRUCTURE:
			matchesStructureQuery = BooleanTemplate.create(
					"isSubstructure({0},{1},{2}) = true",
					structure.structureData,
					ConstantImpl.create(structureData),
					ConstantImpl.create(searchOptions));
			break;
		//...snipped other cases
	}

	baseQuery = baseQuery.from(compoundPathBuilder)
			.innerJoin(compound.compositions, composition)
			.innerJoin(composition.pk.chemicalStructure, structure)
			.where(matchesStructureQuery.and(predicate));
	//...snipped...
}

Full Source Code for ChemicalCompoundRepositoryImpl

The next Part will focus on the Service Layer. The Service Layer controls transactions and security.

Advertisements

Written by kienerj

May 2, 2013 at 07:51

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: