Solutions to IT problems

Solutions I found when learning new IT stuff

Posts Tagged ‘Hibernate

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

Creating a Framework for Chemical Structure Search – Part 4

leave a comment »

Series Overview

This is Part 4 – Component Selection of the “Creating a Framework for Chemical Structure Search“-Series.

Previous posts:

Follow-ups:

Introduction

Finally I will start with the actual creation of the framework. In this part I will introduce the main components (existing 3rd party frameworks and libraries) I use and briefly explain my choices. At this point I think it is fair to mention that my work was basically integrating different existing software components into my desired end-product while taking into account real-world problems and offering a solution for them. There are no new magic algorithms in chemical structure searching, modeling or drug discovery to be found here!

My first try

In my previous effort at creating a framework for chemical structure search, I thought being platform independent, especially regarding the used relational database management system (RDBMS), is an important aspect. Therefore I relied on doing the chemical structure search in the application and not the database. However it is exactly that part that lead to huge performance and efficiency problems. I had to do some stuff that just felt wrong and “hacky” to get usable performance.

Encountered issues with Application-based Substructure Search

Object Creation Performance

The first issue was, that for every structure search, all the structures (molfiles) passing the fingerprint screen had to be loaded from the database and converted to an IAtomContainer Object from the Chemistry Development Kit. It was the creation of these objects that was very CPU intensive. This was due to the fact that you had to detect aromaticity and similar things for every AtomContainer object. I found the solution for this in OrChem, a free cartridge for Oracle based on the CDK. The creators seemed to have the exact same issue and came up with their custom format. That format stored everything required like aromaticity and so forth in a CDK-specific way so the creation of IAtomContainers was not an issue anymore.

Substructure Search Performance

The second issue was the mediocre performance of the substructure search itself. The solution was a complex approach using multi-threading and queues. The first thread screened all structures using the pre-generated fingerprints. Fingerprints were stored in the database but loaded into memory on application start. If a structure passed the screen it’s database id was put into a queue. A second thread reads form that queue, loaded the molfile from database and generated the IAtomContainer and put them into a second queue. Then there were multiple threads (configurable amount) that took the AtomContainers from the queue and did the actual test for subgraph isomorphism. Again, if a structure passed this phase too, it’s database id was put into the output queue and the AtomContainer discarded. This last step was required because AtomContainers are memory hogs and you had to control somehow how many there were in memory at any time.

CPU load now easily reached 100% for seconds during substructure searches. I then realized that the database alone could easily use 20% or more of that probably due to loading all the structures form it. So I added the option to hold the custom format from OrChem in memory ( not big of an issue actually in terms of memory consumption) to reduce load on database and hence use those CPU cycles for substructure search. I guess you have long figured out how convoluted this all was. But it actually worked amazingly well! Because the hits were put into a queue it was easily possible to display the first say 5 hits on a web page while the search continued in the background. So you could give the impression of a very fast search!

Why start from scratch again?

So why change it? Tons of reasons. All of this was done with plain JDBC and various kinds of data transfer objects. Tight-Coupling and maintainability was a serious issue. On the application side of things it was impossible to sort the results because hits are returned somewhat randomly and hence real paging was not possible either. The second thing was how could you search for a substructure and a numeric property at the same time? Well the solution for that was, that one of the substructure search methods had a Set-argument. The Set should contain the database ids of the structures the search should be performed over. Hence do an SQL query for the numeric property first and feed the ids into the substructure search. That worked but again, not very straight forward. Adding and using such custom properties to the database was rather messy too, it lacked proper transaction support and so forth. All in all it was nothing to be proud of and certainly not usable in a real production environment. I did however learn a lot about the Java 5 concurrency package.

Component for Substructure Search

I decided that being dependent on a specific RDBMS is a minor issue compared to above outlined problems. I already knew about the open-source Bingo Cartridge and to my luck the company behind it was developing a version for PostgreSQL. So my choice of this component was easy. Use PostgreSQL with Bingo, both are free and open-source.

Application-side Chemistry toolkit

Especially for Input-output the framework required a Chemistry Toolkit and I again chose the Chemistry Development Kit CDK.

ORM

While it would be preferable to be independent of the ORM, I wasn’t able to achieve that but I admit I did not but much effort in it. MoleculeDatabaseFramework uses JPA 2.0 and hibernate as it’s JPA provider. The part that is hibernate specific is the custom SQL dialect I created for accessing the Structure Search functions of Bingo in JPQL and hence also QueryDSL. There is no specific reason I chose hibernate except I already knew it and it was able to do what I required. So I did not investigate any other JPA providers.

Application Framework – Dependency-Injection

Well I guess this is obvious. I chose Spring. I’ve heard and read a lot about Spring. I’ve always wanted to learn it and this was my chance. I also did not want the framework to depend an a full-blown Java EE Application server.

Data Access Layer – CRUD and Querying

I initial started the project with plain Spring and JPA (Hibernate). But shortly after I in my “research” I read about Spring Data JPA and it’s integration with QueryDSL. 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.

To illustrate this here an example snippet showing an example implementation of my framework:

@Repository
public interface RegistrationCompoundRepository extends ChemicalCompoundRepository {

    List findByRegNumberStartingWith(String regNumber);

}

RegistrationCompound has a property called regNumber. Above interface method is automatically implemented by Spring Data and will return a result List of the RegistrationCompounds that match the passed in argument. That’s all you need to write. No SQL and not even a method implementation. Just create the interface and then follow the findBy method conventions of Spring Data.

A Spring Data repository can also make use of QueryDSL.

Querydsl is a framework which enables the construction of type-safe SQL-like queries for multiple backends including JPA, JDO and SQL in Java.

Example:

List result = query.from(customer)
    .where(customer.lastName.like("A%"), customer.active.eq(true))
    .orderBy(customer.lastName.asc(), customer.firstName.desc())
    .list(customer);

If you use QueryDSL in your Spring Data Repository using QueryDslPredicateExecutor

@Repository
@Transactional(propagation = Propagation.MANDATORY)
public interface ChemicalCompoundRepository
        extends ChemicalStructureSearchRepository, JpaRepository<T, Long>,
        QueryDslPredicateExecutor {
    //...
}

the repository will have additional methods that take a QueryDSL Predicate as an input. A Predicate is basically the WHERE-Clause of the query, like from above example customer.lastName.like("A%"). Some methods take additional parameter like a Pageable. This can be used for paging, the Pageable includes the paging (limit, offset) and sorting information.

This all means it is trivial to extend the repository my framework provides and add your own custom search methods to it. With using predicates you can create complex queries which at the same time search by chemical substructure, return the result sorted and paged and all this with a 1-line method declaration.

public Page findByChemicalStructure(String structureData,
            StructureSearchType searchType,
            Pageable pageable, Predicate predicate);

So I hope this got you interested!

Spring 3 with JPA 2.0 (Hibernate) for beginners

leave a comment »

Introduction

This is a follow-up post to Spring 3 and Hibernate 4 for beginners.

After some considerations I decided to change my application to use JPA instead of “native Hibernate”. The configuration here assumes a standalone application, meaning outside of application container like Tomcat. However changing to JNDI and JTA is “only” a matter of configuration.

Changes

Entity Classes

If you used JPA annotations for your entity classes you don’t have to change anything. If you want to be fully independent of the JPA Implementation you will need to remove all Hibernate specific annotations which can be problematic in certain cases.

Abstract DAO

This remains almost the same. You need to exchange the sessionFactory with entityManager and thats it.

@Repository
public abstract class AbstractJpaDAO< T extends Serializable> {

    private Class< T> clazz;
    @PersistenceContext
    private EntityManager entityManager;

    public AbstractJpaDAO(final Class< T> clazzToSet) {
        this.clazz = clazzToSet;
    } 

    public T getById(final Long id) {
        Preconditions.checkArgument(id != null);
        return getEntityManager().find(clazz, id);
    }

    public List< T> getAll() {
        return getEntityManager().createQuery("from " + clazz.getName())
                .getResultList();
    }

    public void create(final T entity) {
        Preconditions.checkNotNull(entity);
        getEntityManager().persist(entity);
    }

    public T update(final T entity) {
        Preconditions.checkNotNull(entity);
        return (T) getEntityManager().merge(entity);
    }

    public void delete(final T entity) {
        Preconditions.checkNotNull(entity);
        getEntityManager().remove(entity);
    }

    public void deleteById(final Long entityId) {
        final T entity = getById(entityId);
        Preconditions.checkState(entity != null);
        delete(entity);
    }

    /**
     * @return the entityManager
     */
    public EntityManager getEntityManager() {
        return entityManager;
    }

    /**
     * @param entityManager the entityManager to set
     */
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
}

Configuration

First you will not need to create a persistence unit! You can but since spring 3.1 this is not required anymore. If you are using Netbeans and see the warning “The Project does not contain a persistence unit” you can safely ignore it.

Below the new Spring Application-Context file. The important changes are replacing session factory with entity manager factory and changing transaction manager to jpa transaction manager. The datasource and connection pool remains exactly the same.

I’ve also added Service classes to the configuration which I created after writing the previous post.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"

       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
          http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
          http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
          http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">      

    <context:component-scan base-package="org.bitbucket.kienerj.moleculedatabaseframework" />
    <context:annotation-config />    
    
    <bean id="entityManagerFactory" autowire="autodetect"
          class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <!-- This scan the packages for entity classes an hence no need for persistence unit -->
        <property name="packagesToScan" value="org.bitbucket.kienerj.moleculedatabaseframework.entity" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="showSql" value="false" />
                <!-- if this is true it can override hibernate.hbm2ddl.auto settings -->
                <property name="generateDdl" value="false" />
                <property name="databasePlatform" value="org.hibernate.dialect.PostgreSQLDialect" />
            </bean>
        </property>
        <!-- put any ORM specific stuff here -->
        <property name="jpaProperties">
            <props>
                <!-- for test config only --> 
                <prop key="hibernate.hbm2ddl.auto">create-drop</prop> <
            </props>
        </property>
    </bean>
       
    
    <!-- Spring bean configuration. Tell Spring to bounce off BoneCP -->
    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <property name="targetDataSource">
            <ref local="mainDataSource" />
        </property>
    </bean>
            
    <!-- BoneCP configuration -->
    <bean id="mainDataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
        <property name="driverClass" value="org.postgresql.Driver" />
        <property name="jdbcUrl" value="jdbc:postgresql:MolDB" />
        <property name="username" value="postgres"/>
        <property name="password" value="123456"/>
        <property name="idleConnectionTestPeriod" value="60"/>
        <property name="idleMaxAge" value="240"/>      
        <property name="maxConnectionsPerPartition" value="60"/>
        <property name="minConnectionsPerPartition" value="20"/>
        <property name="partitionCount" value="3"/>
        <property name="acquireIncrement" value="10"/>                              
        <property name="statementsCacheSize" value="50"/>
        <property name="releaseHelperThreads" value="3"/>
    </bean>
    
    <bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>
    <tx:annotation-driven transaction-manager="txManager" />   
    
    
    <bean id="chemicalCompoundService" 
          class="org.bitbucket.kienerj.moleculedatabaseframework.service.ChemicalCompoundServiceImpl"/>
    
    <bean id="chemicalStructureService" autowire="autodetect"
          class="org.bitbucket.kienerj.moleculedatabaseframework.service.ChemicalStructureServiceImpl"/>
    
    <bean id="abstractJpaDAO" abstract="true"
          class="org.bitbucket.kienerj.moleculedatabaseframework.dao.AbstractJpaDAO"/>
    
    <bean id="chemicalStructureDAO" parent="abstractJpaDAO" autowire="autodetect"
          class="org.bitbucket.kienerj.moleculedatabaseframework.dao.ChemicalStructureDAO"/>
    <bean id="chemicalCompoundDAO" parent="abstractJpaDAO" autowire="autodetect"
          class="org.bitbucket.kienerj.moleculedatabaseframework.dao.ChemicalCompoundDAO"/>
</beans>

Quering

Since last post I have created some methods in the DAOs and created a Service Layer. Anyway I had to change my Queries too. The most notable change is that in queries that return 1 result only you need to change uniqueResult() to getSingleResult() but much more important is, that getSingleResult() will throw a NoResultException in case no result was found. In my case this is expected to happen and a common scenario. In my case I simulate behaviour of uniqueResult() by catching the exception and returning null.

public ChemicalStructure getByStructureKey(String structureKey) {

	try {
		ChemicalStructure structure = getEntityManager()
				.createQuery(
					"FROM ChemicalStructure structure "
				  + "WHERE structure.structureKey = :structureKey", ChemicalStructure.class)
				.setParameter("structureKey", structureKey)
				.getSingleResult();
		return structure;
	} catch (NoResultException nre) {
		return null;
	}        
}

Keep in mind you will need to change the application and test config! I wasted a lot of time because I changed application config and then ran tests which failed…Beginners mistake. 🙂

Hope this was helpful.

Written by kienerj

October 16, 2012 at 13:53

Posted in Database, Java, Programming

Tagged with , , ,

Spring 3 and Hibernate 4 for beginners

with 6 comments

Introduction Comments

One would assume there are tons of tutorials and blog post out there about this topic. This is true. There are actually so many it is hard to find one that is up to date and fits your level of knowledge. Most of them assume you are already rather good in using Spring, Hibernate or both. So some very fundamental stuff is never mentioned and that stuff will make your application throw exceptions all over the place. I mainly had trouble with the configuration file and were to put what annotation.

Scope of this Article

This article covers my first steps in using Spring and Hibernate together to create a very basic Data Access Layer. I have never used Spring or Hibernate before in a real application. During my studies in a project I once made a simple app using JPA with TopLink. Some very basic knowledge is required like you should probably have seen an @Entity annotation before (else google it) but in general this is an article from a beginner for beginners. You will need to now basic relational database terms and design principles. The project uses maven and hence you should now some basics of it. Also some knowledge in chemistry will make it easier to understand the example but that is not required. Feel free to comment if you have improvements or suggestions to the design and code shown in this article.

The Problem

The DAL I created should be able to store “chemical compounds”. What is a “chemical compound”? It’s a mixture of substances and each substance occurs in a compound in a percentage. Just like whiskey is a mixture of 40 % ethanol and 60 % water (of course this is a simplified view but you get what I mean). Note that a “substance” has a specific “chemical structure”, eg. its known of which atoms it consists and how they are linked together. To summarize we can say a chemical compound consist multiple “chemical structures” (substances) and a “chemical structure” (substance) can occur in multiple compounds. This is a classic many-to-many relationship.

Hibernate and many-to-many with percentage

It turned out that there is no simple way to create a many-to-many relationship in hibernate where the “link table” has an additional column (the percentage of the substance in the compound). I followed this tutorial and therefore created the java classes as shown in the next section. As a summary you will need to create two one-to-many relationships and a linker class. But in practice it is even more complicated.

The Entity classes

ChemicalStructure

Nothing special here, eg. documentation and comments are sparse ;)..funny comments aside the important part is the @OneToMany annotation towards the bottom of the class and its “mappedBy = “pk.chemicalStructure” attribute.

<br />package org.bitbucket.kienerj.moleculedatabaseframework.entityclasses;<br /><br />import java.io.Serializable;<br />import java.util.Set;<br />import javax.persistence.CascadeType;<br />import javax.persistence.Column;<br />import javax.persistence.Entity;<br />import javax.persistence.FetchType;<br />import javax.persistence.GeneratedValue;<br />import javax.persistence.GenerationType;<br />import javax.persistence.Id;<br />import javax.persistence.OneToMany;<br />import javax.persistence.SequenceGenerator;<br />import javax.persistence.Table;<br /><br />@Entity<br />@Table(name = "chemical_structure")<br />public class ChemicalStructure implements Serializable {<br /><br /><%%KEEPWHITESPACE%%>    private Long id;<br /><%%KEEPWHITESPACE%%>    private String structureKey;<br /><%%KEEPWHITESPACE%%>    private String structureData;<br /><%%KEEPWHITESPACE%%>    private Set occurence;<br /><br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @return the id<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    @Id<br /><%%KEEPWHITESPACE%%>    @GeneratedValue(strategy = GenerationType.AUTO, generator = "chem_structure_seq_gen")<br /><%%KEEPWHITESPACE%%>    @SequenceGenerator(name = "chem_structure_seq_gen", sequenceName = "seq_chemical_structure")<br /><%%KEEPWHITESPACE%%>    @Column(name = "structure_id")<br /><%%KEEPWHITESPACE%%>    public Long getId() {<br /><%%KEEPWHITESPACE%%>        return id;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @param id the id to set<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    public void setId(Long id) {<br /><%%KEEPWHITESPACE%%>        this.id = id;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @return the structureKey<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    @Column(name = "structure_key", unique = true)<br /><%%KEEPWHITESPACE%%>    public String getStructureKey() {<br /><%%KEEPWHITESPACE%%>        return structureKey;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @param structureKey the structureKey to set<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    public void setStructureKey(String structureKey) {<br /><%%KEEPWHITESPACE%%>        this.structureKey = structureKey;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @return the structureData<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    @Column(name = "chemical_structure")<br /><%%KEEPWHITESPACE%%>    public String getStructureData() {<br /><%%KEEPWHITESPACE%%>        return structureData;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @param structureData the structureData to set<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    public void setStructureData(String structureData) {<br /><%%KEEPWHITESPACE%%>        this.structureData = structureData;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    @OneToMany(fetch = FetchType.LAZY, mappedBy = "pk.chemicalStructure",<br /><%%KEEPWHITESPACE%%>            cascade = CascadeType.ALL)<br /><%%KEEPWHITESPACE%%>    public Set getOccurence() {<br /><%%KEEPWHITESPACE%%>        return occurence;<br /><%%KEEPWHITESPACE%%>    }<br /><%%KEEPWHITESPACE%%>    public void setOccurence(Set occurence) {<br /><%%KEEPWHITESPACE%%>        this.occurence = occurence;<br /><%%KEEPWHITESPACE%%>    }<br />}<br />

ChemicalCompound

Very similar to ChemicalStructure. Also note the @OneToMany annotation. Here there is some additonal stuff going on. This entity class is configured to be sub-classed. All subclasses will be stored in the same table were the column “compound_type” stores to what entity class a certain row belongs to.

<br />package org.bitbucket.kienerj.moleculedatabaseframework.entityclasses;<br /><br />import java.io.Serializable;<br />import java.util.HashSet;<br />import java.util.Set;<br />import javax.persistence.CascadeType;<br />import javax.persistence.Column;<br />import javax.persistence.DiscriminatorColumn;<br />import javax.persistence.DiscriminatorType;<br />import javax.persistence.DiscriminatorValue;<br />import javax.persistence.Entity;<br />import javax.persistence.FetchType;<br />import javax.persistence.GeneratedValue;<br />import javax.persistence.GenerationType;<br />import javax.persistence.Id;<br />import javax.persistence.Inheritance;<br />import javax.persistence.InheritanceType;<br />import javax.persistence.OneToMany;<br />import javax.persistence.SequenceGenerator;<br />import javax.persistence.Table;<br />import org.bitbucket.kienerj.moleculedatabaseframework.HashCodeUtil;<br /><br />@Entity<br />@Inheritance(strategy = InheritanceType.SINGLE_TABLE)<br />@Table(name = "chemical_compound")<br />@DiscriminatorColumn(name = "compound_type", discriminatorType=DiscriminatorType.STRING)<br />@DiscriminatorValue("ChemicalCompound")<br />public class ChemicalCompound implements Serializable {<br /><br /><%%KEEPWHITESPACE%%>    private Long id;<br /><%%KEEPWHITESPACE%%>    private String compoundName;<br /><%%KEEPWHITESPACE%%>    private Set composition = new HashSet();<br /><br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @return the id<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    @Id<br /><%%KEEPWHITESPACE%%>    @GeneratedValue(strategy = GenerationType.AUTO, generator = "chem_compound_seq_gen")<br /><%%KEEPWHITESPACE%%>    @SequenceGenerator(name = "chem_compound_seq_gen", sequenceName = "seq_chem_compound")<br /><%%KEEPWHITESPACE%%>    @Column(name = "chem_compound_id")<br /><%%KEEPWHITESPACE%%>    public Long getId() {<br /><%%KEEPWHITESPACE%%>        return id;<br /><%%KEEPWHITESPACE%%>    }<br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @param id the id to set<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    public void setId(Long id) {<br /><%%KEEPWHITESPACE%%>        this.id = id;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @return the compoundName<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    @Column(name = "compound_name")<br /><%%KEEPWHITESPACE%%>    public String getCompoundName() {<br /><%%KEEPWHITESPACE%%>        return compoundName;<br /><%%KEEPWHITESPACE%%>    }<br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @param compoundName the compoundName to set<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    public void setCompoundName(String compoundName) {<br /><%%KEEPWHITESPACE%%>        this.compoundName = compoundName;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    @OneToMany(fetch = FetchType.LAZY, mappedBy = "pk.compound", cascade = CascadeType.ALL)<br /><%%KEEPWHITESPACE%%>    public Set getComposition() {<br /><%%KEEPWHITESPACE%%>        return composition;<br /><%%KEEPWHITESPACE%%>    }<br /><%%KEEPWHITESPACE%%>    public void setComposition(Set composition) {<br /><%%KEEPWHITESPACE%%>        this.composition = composition;<br /><%%KEEPWHITESPACE%%>    }<br />}<br />

Now the special or should I say non-intuitive part for which you need to either read the whole hibernate manual or find a tutorial to be able to figure it out. Maybe there is a simpler way but I was lazy and went with the tutorial.

You need the “linker class” defining the “linker table”. That class contains an “ID Class”, ChemicalCompoundCompositionID, which is annotated with @Embeddable. This “ID Class” is used as the primary key (pk) in the “linker class”. But I suggest you look at the code. It’s rather easy to understand but to figure it out yourself…not so much.

ChemicalCompoundComposition

<br />package org.bitbucket.kienerj.moleculedatabaseframework.entityclasses;<br /><br />import java.io.Serializable;<br />import javax.persistence.AssociationOverride;<br />import javax.persistence.AssociationOverrides;<br />import javax.persistence.Column;<br />import javax.persistence.EmbeddedId;<br />import javax.persistence.Entity;<br />import javax.persistence.JoinColumn;<br />import javax.persistence.Table;<br />import javax.persistence.Transient;<br /><br />@Entity<br />@Table(name = "compound_composition")<br />@AssociationOverrides({<br /><%%KEEPWHITESPACE%%>    @AssociationOverride(name = "pk.chemicalStructure",<br /><%%KEEPWHITESPACE%%>    joinColumns =<br /><%%KEEPWHITESPACE%%>    @JoinColumn(name = "chemical_structure_id")),<br /><%%KEEPWHITESPACE%%>    @AssociationOverride(name = "pk.compound",<br /><%%KEEPWHITESPACE%%>    joinColumns =<br /><%%KEEPWHITESPACE%%>    @JoinColumn(name = "chem_compound_id"))})<br />public class ChemicalCompoundComposition implements Serializable {<br /><br /><%%KEEPWHITESPACE%%>    private ChemicalCompoundCompositionID pk = new ChemicalCompoundCompositionID();<br /><%%KEEPWHITESPACE%%>    private Double percentage;<br /><br /><%%KEEPWHITESPACE%%>    @EmbeddedId<br /><%%KEEPWHITESPACE%%>    public ChemicalCompoundCompositionID getPk() {<br /><%%KEEPWHITESPACE%%>        return pk;<br /><%%KEEPWHITESPACE%%>    }<br /><%%KEEPWHITESPACE%%>    public void setPk(ChemicalCompoundCompositionID pk){<br /><%%KEEPWHITESPACE%%>        this.pk = pk;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    @Transient<br /><%%KEEPWHITESPACE%%>    public ChemicalCompound getCompound(){<br /><%%KEEPWHITESPACE%%>        return pk.getCompound();<br /><%%KEEPWHITESPACE%%>    }<br /><%%KEEPWHITESPACE%%>    public void setCompound(ChemicalCompound compound){<br /><%%KEEPWHITESPACE%%>       pk.setCompound(compound);<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    @Transient<br /><%%KEEPWHITESPACE%%>    public ChemicalStructure getChemicalStructure(){<br /><%%KEEPWHITESPACE%%>        return pk.getChemicalStructure();<br /><%%KEEPWHITESPACE%%>    }<br /><%%KEEPWHITESPACE%%>    public void setChemicalStructure(ChemicalStructure structure){<br /><%%KEEPWHITESPACE%%>       pk.setChemicalStructure(structure);<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @return the percentage<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    @Column(name = "percentage", nullable = false)<br /><%%KEEPWHITESPACE%%>    public Double getPercentage() {<br /><%%KEEPWHITESPACE%%>        return percentage;<br /><%%KEEPWHITESPACE%%>    }<br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * @param percentage the percentage to set<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    public void setPercentage(Double percentage) {<br /><%%KEEPWHITESPACE%%>        this.percentage = percentage;<br /><%%KEEPWHITESPACE%%>    }<br />}<br /><br />

ChemicalCompoundCompositionID

<br />package org.bitbucket.kienerj.moleculedatabaseframework.entityclasses;<br /><br />import java.io.Serializable;<br />import javax.persistence.Embeddable;<br />import javax.persistence.ManyToOne;<br /><br />@Embeddable<br />public class ChemicalCompoundCompositionID implements Serializable {<br /><br /><%%KEEPWHITESPACE%%>    private ChemicalCompound compound;<br /><%%KEEPWHITESPACE%%>    private ChemicalStructure chemicalStructure;<br /><br /><%%KEEPWHITESPACE%%>    @ManyToOne(fetch = FetchType.LAZY)<br /><%%KEEPWHITESPACE%%>    public ChemicalCompound getCompound() {<br /><%%KEEPWHITESPACE%%>        return compound;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    public void setCompound(ChemicalCompound compound) {<br /><%%KEEPWHITESPACE%%>        this.compound= compound;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    @ManyToOne(fetch = FetchType.LAZY)<br /><%%KEEPWHITESPACE%%>    public ChemicalStructure getChemicalStructure() {<br /><%%KEEPWHITESPACE%%>        return chemicalStructure;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    public void setChemicalStructure(ChemicalStructure structure) {<br /><%%KEEPWHITESPACE%%>        this.chemicalStructure = structure;<br /><%%KEEPWHITESPACE%%>    }<br />}<br />

As you can see this is far more complex than you would have imaged. We also need to create an entity (ChemicalCompoundComposition) that has no real meaning in the real world and for each compound you will create you will have to create as many ChemicalCompoundComposition instances as the compound contains substances. Note that fetch = FetchType.LAZY is absolutely required in the @Embeddable ID because else session.merge(chemicalCompound) will lead to a StackOverflowError (endless references).

The Data Access Objects

Now we need to create the layer that actually persists the entities to the database. To create that layer I followed the design outlined in The Persistence Layer with Spring 3.1 and Hibernate. We create a generic abstract class AbstractHibernateDAO that contains the basic database operations. The concrete classes are very simple as the only point of them is to specify which type of Entity they save. Of course you can add methods or override some if required.

AbstractHibernateDAO

The important part are the annotations @Repository, @Transactional and @Autowired

<br />package org.bitbucket.kienerj.moleculedatabaseframework.dao;<br /><br />import com.google.common.base.Preconditions;<br />import java.io.Serializable;<br />import java.util.List;<br />import org.hibernate.Session;<br />import org.hibernate.SessionFactory;<br />import org.springframework.beans.factory.annotation.Autowired;<br />import org.springframework.stereotype.Repository;<br />import org.springframework.transaction.annotation.Transactional;<br /><br />@Repository<br />@Transactional<br />public abstract class AbstractHibernateDAO&lt; T extends Serializable&gt; {<br /><br /><%%KEEPWHITESPACE%%>    private final Class&lt; T&gt; clazz;<br /><%%KEEPWHITESPACE%%>    @Autowired<br /><%%KEEPWHITESPACE%%>    SessionFactory sessionFactory;<br /><br /><%%KEEPWHITESPACE%%>    public AbstractHibernateDAO(final Class&lt; T&gt; clazzToSet) {<br /><%%KEEPWHITESPACE%%>        this.clazz = clazzToSet;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    public T getById(final Long id) {<br /><%%KEEPWHITESPACE%%>        Preconditions.checkArgument(id != null);<br /><%%KEEPWHITESPACE%%>        return (T) this.getCurrentSession().get(this.clazz, id);<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    public List&lt; T&gt; getAll() {<br /><%%KEEPWHITESPACE%%>        return this.getCurrentSession()<br /><%%KEEPWHITESPACE%%>                .createQuery("from " + this.clazz.getName()).list();<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    public void create(final T entity) {<br /><%%KEEPWHITESPACE%%>        Preconditions.checkNotNull(entity);<br /><%%KEEPWHITESPACE%%>        this.getCurrentSession().persist(entity);<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    public void update(final T entity) {<br /><%%KEEPWHITESPACE%%>        Preconditions.checkNotNull(entity);<br /><%%KEEPWHITESPACE%%>        this.getCurrentSession().merge(entity);<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    public void delete(final T entity) {<br /><%%KEEPWHITESPACE%%>        Preconditions.checkNotNull(entity);<br /><%%KEEPWHITESPACE%%>        this.getCurrentSession().delete(entity);<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    public void deleteById(final Long entityId) {<br /><%%KEEPWHITESPACE%%>        final T entity = this.getById(entityId);<br /><%%KEEPWHITESPACE%%>        Preconditions.checkState(entity != null);<br /><%%KEEPWHITESPACE%%>        this.delete(entity);<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    public void setSessionFactory(SessionFactory sessionFactory) {<br /><%%KEEPWHITESPACE%%>        this.sessionFactory = sessionFactory;<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    protected final Session getCurrentSession() {<br /><%%KEEPWHITESPACE%%>        return this.sessionFactory.getCurrentSession();<br /><%%KEEPWHITESPACE%%>    }<br />}<br />

ChemicalStructureDAO

<br />package org.bitbucket.kienerj.moleculedatabaseframework.dao;<br /><br />import org.bitbucket.kienerj.moleculedatabaseframework.entityclasses.ChemicalStructure;<br />import org.springframework.stereotype.Repository;<br /><br />@Repository<br />public class ChemicalStructureDAO extends AbstractHibernateDAO {<br /><br /><%%KEEPWHITESPACE%%>    public ChemicalStructureDAO() {<br /><%%KEEPWHITESPACE%%>        super(ChemicalStructure.class);<br /><%%KEEPWHITESPACE%%>    }<br />}<br />

ChemicalCompoundDAO

<br />package org.bitbucket.kienerj.moleculedatabaseframework.dao;<br /><br />import org.bitbucket.kienerj.moleculedatabaseframework.entityclasses.ChemicalCompound;<br />import org.springframework.stereotype.Repository;<br /><br />@Repository<br />public class ChemicalCompoundDAO extends AbstractHibernateDAO{<br /><br /><%%KEEPWHITESPACE%%>    public ChemicalCompoundDAO() {<br /><%%KEEPWHITESPACE%%>        super(ChemicalCompound.class);<br /><%%KEEPWHITESPACE%%>    }<br />}<br />

The Spring configuration file

I had the most trouble with this. Keep in mind that this highly depends on what dependencies you plan on using, eg. witch database, connection pool or not and so forth. The configuration I use is for PostgreSQL using BoneCP connection pool. The configuration for BoneCP was taken from the BoneCP web page. The spring configuration file is under src/main/resources/ApplicationContext.xml. See the comments within the file.

<br />&lt;?xml version="1.0" encoding="UTF-8"?&gt;<br />&lt;beans xmlns="http://www.springframework.org/schema/beans"<br /><%%KEEPWHITESPACE%%>       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"<br /><%%KEEPWHITESPACE%%>       xmlns:aop="http://www.springframework.org/schema/aop"<br /><%%KEEPWHITESPACE%%>       xmlns:context="http://www.springframework.org/schema/context"<br /><%%KEEPWHITESPACE%%>       xmlns:tx="http://www.springframework.org/schema/tx"<br /><br /><%%KEEPWHITESPACE%%>       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd<br /><%%KEEPWHITESPACE%%>          http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd<br /><%%KEEPWHITESPACE%%>          http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd<br /><%%KEEPWHITESPACE%%>          http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd<br />"&gt;<br /><br /><%%KEEPWHITESPACE%%>    &lt;bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" autowire="autodetect"&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="dataSource" ref="dataSource" /&gt;<br /><%%KEEPWHITESPACE%%>        &lt;!-- Don't forget to list all your entity classes here --&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="annotatedClasses"&gt;<br /><%%KEEPWHITESPACE%%>            &lt;list&gt;<br /><%%KEEPWHITESPACE%%>                &lt;value&gt;org.bitbucket.kienerj.moleculedatabaseframework.entityclasses.ChemicalStructure&lt;/value&gt;<br /><%%KEEPWHITESPACE%%>                &lt;value&gt;org.bitbucket.kienerj.moleculedatabaseframework.entityclasses.ChemicalCompound&lt;/value&gt;<br /><%%KEEPWHITESPACE%%>                &lt;value&gt;org.bitbucket.kienerj.moleculedatabaseframework.entityclasses.ChemicalCompoundComposition&lt;/value&gt;<br /><%%KEEPWHITESPACE%%>            &lt;/list&gt;<br /><%%KEEPWHITESPACE%%>        &lt;/property&gt;<br /><br /><%%KEEPWHITESPACE%%>        &lt;property name="hibernateProperties"&gt;<br /><%%KEEPWHITESPACE%%>            &lt;props&gt;<br /><%%KEEPWHITESPACE%%>                &lt;prop key="hibernate.dialect"&gt;org.hibernate.dialect.PostgreSQLDialect&lt;/prop&gt;<br /><%%KEEPWHITESPACE%%>                &lt;prop key="hibernate.show_sql"&gt;true&lt;/prop&gt;<br /><%%KEEPWHITESPACE%%>                &lt;prop key="hibernate.hbm2ddl.auto"&gt;create&lt;/prop&gt;<br /><%%KEEPWHITESPACE%%>            &lt;/props&gt;<br /><%%KEEPWHITESPACE%%>        &lt;/property&gt;<br /><%%KEEPWHITESPACE%%>    &lt;/bean&gt;<br /><br /><%%KEEPWHITESPACE%%>    &lt;!-- Spring bean configuration. Tell Spring to bounce off BoneCP --&gt;<br /><%%KEEPWHITESPACE%%>    &lt;bean id="dataSource"<br /><%%KEEPWHITESPACE%%>          class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy"&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="targetDataSource"&gt;<br /><%%KEEPWHITESPACE%%>            &lt;ref local="mainDataSource" /&gt;<br /><%%KEEPWHITESPACE%%>        &lt;/property&gt;<br /><%%KEEPWHITESPACE%%>    &lt;/bean&gt;<br /><br /><%%KEEPWHITESPACE%%>    &lt;!-- BoneCP configuration --&gt;<br /><%%KEEPWHITESPACE%%>    &lt;bean id="mainDataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close"&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="driverClass" value="org.postgresql.Driver" /&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="jdbcUrl" value="jdbc:postgresql:MolDB" /&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="username" value="postgres"/&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="password" value="123456"/&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="idleConnectionTestPeriod" value="60"/&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="idleMaxAge" value="240"/&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="maxConnectionsPerPartition" value="60"/&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="minConnectionsPerPartition" value="20"/&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="partitionCount" value="3"/&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="acquireIncrement" value="10"/&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="statementsCacheSize" value="50"/&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="releaseHelperThreads" value="3"/&gt;<br /><%%KEEPWHITESPACE%%>    &lt;/bean&gt;<br /><br /><%%KEEPWHITESPACE%%>    &lt;bean id="txManager"<br /><%%KEEPWHITESPACE%%>          class="org.springframework.orm.hibernate4.HibernateTransactionManager"&gt;<br /><%%KEEPWHITESPACE%%>        &lt;property name="sessionFactory" ref="sessionFactory" /&gt;<br /><%%KEEPWHITESPACE%%>    &lt;/bean&gt;<br /><%%KEEPWHITESPACE%%>    &lt;tx:annotation-driven transaction-manager="txManager" /&gt;<br /><br /><%%KEEPWHITESPACE%%>    &lt;context:annotation-config /&gt;<br /><br /><%%KEEPWHITESPACE%%>    &lt;!-- Note the usage of abstract="true" and parent="AbstractHibernateDAO" in the implementations--&gt;<br /><%%KEEPWHITESPACE%%>    &lt;bean id="AbstractHibernateDAO" abstract="true"<br /><%%KEEPWHITESPACE%%>          class="org.bitbucket.kienerj.moleculedatabaseframework.dao.AbstractHibernateDAO"/&gt;<br /><br /><%%KEEPWHITESPACE%%>    &lt;bean id="ChemicalStructureDAO" parent="AbstractHibernateDAO"<br /><%%KEEPWHITESPACE%%>          class="org.bitbucket.kienerj.moleculedatabaseframework.dao.ChemicalStructureDAO"/&gt;<br /><%%KEEPWHITESPACE%%>    &lt;bean id="ChemicalCompoundDAO" parent="AbstractHibernateDAO"<br /><%%KEEPWHITESPACE%%>          class="org.bitbucket.kienerj.moleculedatabaseframework.dao.ChemicalCompoundDAO"/&gt;<br />&lt;/beans&gt;<br />

More Configuration: Maven pom.xml

<br />&lt;project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"<br /><%%KEEPWHITESPACE%%>  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"&gt;<br /><%%KEEPWHITESPACE%%>    &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;<br /><br /><%%KEEPWHITESPACE%%>    &lt;groupId&gt;org.bitbucket.kienerj&lt;/groupId&gt;<br /><%%KEEPWHITESPACE%%>    &lt;artifactId&gt;MoleculeDatabaseFramework&lt;/artifactId&gt;<br /><%%KEEPWHITESPACE%%>    &lt;version&gt;0.1-SNAPSHOT&lt;/version&gt;<br /><%%KEEPWHITESPACE%%>    &lt;packaging&gt;jar&lt;/packaging&gt;<br /><br /><%%KEEPWHITESPACE%%>    &lt;name&gt;MoleculeDatabaseFramework&lt;/name&gt;<br /><%%KEEPWHITESPACE%%>    &lt;url&gt;http://maven.apache.org&lt;/url&gt;<br /><%%KEEPWHITESPACE%%>    &lt;build&gt;<br /><%%KEEPWHITESPACE%%>        &lt;plugins&gt;<br /><%%KEEPWHITESPACE%%>            &lt;plugin&gt;<br /><%%KEEPWHITESPACE%%>                &lt;groupId&gt;org.apache.maven.plugins&lt;/groupId&gt;<br /><%%KEEPWHITESPACE%%>                &lt;artifactId&gt;maven-compiler-plugin&lt;/artifactId&gt;<br /><%%KEEPWHITESPACE%%>                &lt;version&gt;2.3.2&lt;/version&gt;<br /><%%KEEPWHITESPACE%%>                &lt;configuration&gt;<br /><%%KEEPWHITESPACE%%>                    &lt;source&gt;1.7&lt;/source&gt;<br /><%%KEEPWHITESPACE%%>                    &lt;target&gt;1.7&lt;/target&gt;<br /><%%KEEPWHITESPACE%%>                    &lt;showDeprecation&gt;true&lt;/showDeprecation&gt;<br /><%%KEEPWHITESPACE%%>                &lt;/configuration&gt;<br /><%%KEEPWHITESPACE%%>            &lt;/plugin&gt;<br /><%%KEEPWHITESPACE%%>        &lt;/plugins&gt;<br /><%%KEEPWHITESPACE%%>    &lt;/build&gt;<br /><%%KEEPWHITESPACE%%>    &lt;properties&gt;<br /><%%KEEPWHITESPACE%%>        &lt;project.build.sourceEncoding&gt;UTF-8&lt;/project.build.sourceEncoding&gt;<br /><%%KEEPWHITESPACE%%>    &lt;/properties&gt;<br /><br /><%%KEEPWHITESPACE%%>    &lt;dependencies&gt;<br /><%%KEEPWHITESPACE%%>        &lt;dependency&gt;<br /><%%KEEPWHITESPACE%%>            &lt;groupId&gt;junit&lt;/groupId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;artifactId&gt;junit&lt;/artifactId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;version&gt;3.8.1&lt;/version&gt;<br /><%%KEEPWHITESPACE%%>            &lt;scope&gt;test&lt;/scope&gt;<br /><%%KEEPWHITESPACE%%>        &lt;/dependency&gt;<br /><%%KEEPWHITESPACE%%>        &lt;dependency&gt;<br /><%%KEEPWHITESPACE%%>            &lt;groupId&gt;org.springframework&lt;/groupId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;artifactId&gt;spring-orm&lt;/artifactId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;version&gt;3.1.2.RELEASE&lt;/version&gt;<br /><%%KEEPWHITESPACE%%>        &lt;/dependency&gt;<br /><%%KEEPWHITESPACE%%>        &lt;dependency&gt;<br /><%%KEEPWHITESPACE%%>            &lt;groupId&gt;org.hibernate&lt;/groupId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;artifactId&gt;hibernate-entitymanager&lt;/artifactId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;version&gt;4.1.7.Final&lt;/version&gt;<br /><%%KEEPWHITESPACE%%>        &lt;/dependency&gt;<br /><%%KEEPWHITESPACE%%>        &lt;dependency&gt;<br /><%%KEEPWHITESPACE%%>            &lt;groupId&gt;com.jolbox&lt;/groupId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;artifactId&gt;bonecp-provider&lt;/artifactId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;version&gt;0.8.0-alpha1&lt;/version&gt;<br /><%%KEEPWHITESPACE%%>        &lt;/dependency&gt;<br /><%%KEEPWHITESPACE%%>        &lt;dependency&gt;<br /><%%KEEPWHITESPACE%%>            &lt;groupId&gt;postgresql&lt;/groupId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;artifactId&gt;postgresql&lt;/artifactId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;version&gt;9.1-901.jdbc4&lt;/version&gt;<br /><%%KEEPWHITESPACE%%>        &lt;/dependency&gt;<br /><%%KEEPWHITESPACE%%>        &lt;dependency&gt;<br /><%%KEEPWHITESPACE%%>            &lt;groupId&gt;cglib&lt;/groupId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;artifactId&gt;cglib&lt;/artifactId&gt;<br /><%%KEEPWHITESPACE%%>            &lt;version&gt;2.2.2&lt;/version&gt;<br /><%%KEEPWHITESPACE%%>        &lt;/dependency&gt;<br /><%%KEEPWHITESPACE%%>    &lt;/dependencies&gt;<br />&lt;/project&gt;<br />

You need BoneCP-0.8.0-alpha1 for Hibernate 4! The release version does not work. This was also one of the many issues i had to figure out (thanks google). Maybe other connection pools have similar issues.

How to use

<br />package org.bitbucket.kienerj.moleculedatabaseframework;<br /><br />import org.bitbucket.kienerj.moleculedatabaseframework.dao.ChemicalCompoundDAO;<br />import org.bitbucket.kienerj.moleculedatabaseframework.dao.ChemicalStructureDAO;<br />import org.bitbucket.kienerj.moleculedatabaseframework.entityclasses.ChemicalCompound;<br />import org.bitbucket.kienerj.moleculedatabaseframework.entityclasses.ChemicalCompoundComposition;<br />import org.bitbucket.kienerj.moleculedatabaseframework.entityclasses.ChemicalStructure;<br />import org.springframework.context.ApplicationContext;<br />import org.springframework.context.support.ClassPathXmlApplicationContext;<br /><br />public class App<br />{<br /><%%KEEPWHITESPACE%%>    public static void main( String[] args )<br /><%%KEEPWHITESPACE%%>    {<br /><br /><%%KEEPWHITESPACE%%>        // Yes,  I should now and then actually read manuals and documentations (=boring) but then<br /><%%KEEPWHITESPACE%%>        // I only have so much time and examples help me so much more<br /><%%KEEPWHITESPACE%%>        // THIS IS THE IMPORTANT LINE I DID NOT SEE IN ANY TUTORIAL<br /><%%KEEPWHITESPACE%%>        ApplicationContext context = new ClassPathXmlApplicationContext("ApplicationContext.xml");<br /><br /><%%KEEPWHITESPACE%%>        // You need to create the DAOs from Spring Context else the "Spring magic" will not be available<br /><%%KEEPWHITESPACE%%>        ChemicalStructureDAO structureDAO = (ChemicalStructureDAO) context.getBean("ChemicalStructureDAO");<br /><%%KEEPWHITESPACE%%>        ChemicalCompoundDAO compoundDAO = (ChemicalCompoundDAO) context.getBean("ChemicalCompoundDAO");<br /><br /><%%KEEPWHITESPACE%%>        // From here on it is pretty straight forward.<br /><%%KEEPWHITESPACE%%>        // For me there are a bit too many lines to make it look nice but it works...<br /><%%KEEPWHITESPACE%%>        ChemicalStructure structure1 = new ChemicalStructure();<br /><%%KEEPWHITESPACE%%>        structure1.setStructureKey("c1ccccc1");<br /><%%KEEPWHITESPACE%%>        structure1.setStructureData("c1ccccc1");<br /><br /><%%KEEPWHITESPACE%%>        ChemicalStructure structure2 = new ChemicalStructure();<br /><%%KEEPWHITESPACE%%>        structure2.setStructureKey("CC=O");<br /><%%KEEPWHITESPACE%%>        structure2.setStructureData("CC=O");<br /><br /><%%KEEPWHITESPACE%%>        structureDAO.create(structure1);<br /><%%KEEPWHITESPACE%%>        structureDAO.create(structure2);<br /><br /><%%KEEPWHITESPACE%%>        ChemicalCompound compound = new ChemicalCompound();<br /><%%KEEPWHITESPACE%%>        compound.setCompoundName("Test Compound");<br /><br /><%%KEEPWHITESPACE%%>        ChemicalCompoundComposition composition1 = new ChemicalCompoundComposition();<br /><%%KEEPWHITESPACE%%>        composition1.setCompound(compound);<br /><%%KEEPWHITESPACE%%>        composition1.setChemicalStructure(structure1);<br /><%%KEEPWHITESPACE%%>        composition1.setPercentage(70.0);<br /><br /><%%KEEPWHITESPACE%%>        ChemicalCompoundComposition composition2 = new ChemicalCompoundComposition();<br /><%%KEEPWHITESPACE%%>        composition2.setCompound(compound);<br /><%%KEEPWHITESPACE%%>        composition2.setChemicalStructure(structure2);<br /><%%KEEPWHITESPACE%%>        composition2.setPercentage(30.0);<br /><br /><%%KEEPWHITESPACE%%>        compound.getComposition().add(composition1);<br /><%%KEEPWHITESPACE%%>        compound.getComposition().add(composition2);<br /><br /><%%KEEPWHITESPACE%%>        compoundDAO.create(compound);<br /><%%KEEPWHITESPACE%%>    }<br />}<br />

After running above snippet the database looks like this:

Conclusion

I hope this article can help some people to do their first steps with Spring and Hibernate. After all it is not hard if you know how it works..if…

UPDATE 1

You will need to watch out for LazyInitializationException. With above code of you call compound.getComposition().get(0).getChemicalStructure() (eg. get the first ChemicalStructure linked to this compound) outside of the DAO, meaning Hibernate Session is closed, you will get a LazyInitializationException. This happens because the data for the ChemicalStructure was not loaded (FetchType.LAZY) when loading the compound. To avoid this you will need to explicitly load the data within the DAO (Hibernate Session). Example

<br /><%%KEEPWHITESPACE%%>    public ChemicalCompound getById(Long id) {<br /><br /><%%KEEPWHITESPACE%%>        ChemicalCompound compound = compoundDAO.getById(id);<br /><%%KEEPWHITESPACE%%>        for (ChemicalCompoundComposition composition : compound.getComposition()) {<br /><%%KEEPWHITESPACE%%>            composition.getChemicalStructure().getId();<br /><%%KEEPWHITESPACE%%>        }<br /><%%KEEPWHITESPACE%%>        return compound;<br /><%%KEEPWHITESPACE%%>    }<br />

UPDATE Sep 2013

Above example of a many-to-many relationship with an additional column, namely ChemicalCompoundComposition can be achieved much easier and this easier solution is also more flexible especially in terms up updating. The solution is actually pretty obvious. No idea why I did not see it back then. Rookie mistake. Below the new code, with JPA 2.0 annotations and using project lombok annotations for auto generation of getters, setters and equals() and hashCode() methods.

<br />@Entity<br />@Table(name = "compound_composition", uniqueConstraints=<br /><%%KEEPWHITESPACE%%>        @UniqueConstraint(columnNames = {"chem_compound_id", "chemical_structure_id"}))<br />@JsonIdentityInfo(generator=ObjectIdGenerators.IntSequenceGenerator.class, property="@id")<br />@Data<br />@EqualsAndHashCode(callSuper = false, of = {"compound", "chemicalStructure"})<br />public class ChemicalCompoundComposition extends BaseEntity {<br /><br /><%%KEEPWHITESPACE%%>    @Id<br /><%%KEEPWHITESPACE%%>    @GeneratedValue(strategy = GenerationType.AUTO,<br /><%%KEEPWHITESPACE%%>            generator = "composition_seq_gen")<br /><%%KEEPWHITESPACE%%>    @SequenceGenerator(name = "composition_seq_gen",<br /><%%KEEPWHITESPACE%%>            sequenceName = "seq_compound_composition", allocationSize = 1000)<br /><%%KEEPWHITESPACE%%>    @Column(name = "composition_id")<br /><%%KEEPWHITESPACE%%>    private Long id;<br /><br /><%%KEEPWHITESPACE%%>    @Column(name = "percentage")<br /><%%KEEPWHITESPACE%%>    private Double percentage;<br /><br /><%%KEEPWHITESPACE%%>    @JoinColumn(name = "chem_compound_id", nullable = false)<br /><%%KEEPWHITESPACE%%>    @ManyToOne(fetch = FetchType.LAZY, cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH})<br /><%%KEEPWHITESPACE%%>    private ChemicalCompound compound;<br /><%%KEEPWHITESPACE%%>    @JoinColumn(name = "chemical_structure_id", nullable = false)<br /><%%KEEPWHITESPACE%%>    @ManyToOne(fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REFRESH})<br /><%%KEEPWHITESPACE%%>    private ChemicalStructure chemicalStructure;<br /><br /><%%KEEPWHITESPACE%%>    /**<br /><%%KEEPWHITESPACE%%>     * For JPA Provider<br /><%%KEEPWHITESPACE%%>     */<br /><%%KEEPWHITESPACE%%>    public ChemicalCompoundComposition() {<br /><%%KEEPWHITESPACE%%>    }<br /><br /><%%KEEPWHITESPACE%%>    public ChemicalCompoundComposition(ChemicalCompound compound,<br /><%%KEEPWHITESPACE%%>            ChemicalStructure structure,<br /><%%KEEPWHITESPACE%%>            Double percentage) {<br /><br /><%%KEEPWHITESPACE%%>        this.percentage = percentage;<br /><%%KEEPWHITESPACE%%>        this.chemicalStructure = structure;<br /><%%KEEPWHITESPACE%%>        this.compound = compound;<br /><%%KEEPWHITESPACE%%>    }<br />}<br />

Written by kienerj

October 5, 2012 at 16:38

Posted in Database, Java, Programming

Tagged with , ,