Hibernate, Oracle, Spring Data JPA and NULLS FIRST

Hibernate, Oracle, Spring and NULLS FIRST

so an unusual little problem popped from a colleague the other day, they want to sort in ascending order a result set from an Oracle DB, but with null values at the top of the list.  we're using Spring Data JPA and Oracle has a specific sql statement to help

 select * from some_entity order by end_state asc nulls first

nice - but how do we do this in JPA?

so, Hibernate offers the interceptor pattern to modify the generated sql just before it goes off for execution.  we're going to use that to mark it up with our suffix of NULLS FIRST

here's the code

package de.incompleteco.spring.data.support.hibernate;

import org.hibernate.EmptyInterceptor;

public class OracleNullsFirstInteceptor extends EmptyInterceptor {

 private static final long serialVersionUID = 1L;

 @Override
 public String onPrepareStatement(String sql) {
  //check if it's our statement
  if (sql.contains("asc")) {
   sql = sql + " nulls first";//append the oracle specific code
  }//end if
  //return
  return sql;
 }
 
}

but, there's a catch with this design, we don't want NULLS FIRST appended to every statement, just when we use ASC.  so, in Spring Data JPA, we need to specify a Sort to help flag as ASC

        ...
        repository.findAll(Sort.Direction.ASC);

finally, here's the Spring config to support adding the interceptor

...
  
   
  

one last thing to note, if you want to test this in both an in-memory and against Oracle without changing the code, have a look at the H2 database, it plays nice with the NULLS FIRST appendage.

Spring Integration JPA and REST

Spring Integration JPA and REST

here's a little sample project i put together to illustrate how to expose JPA objects via REST using Spring Integration.  it's a bit rough but it does the following;
- PUT a new entity
- GET an entity by id
- GET all entities

here's the entity;

package de.incompleteco.spring.jpa.domain;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="simple_entity")
public class SimpleEntity implements Serializable {

 private static final long serialVersionUID = 1l;

 @Id
 @GeneratedValue(strategy=GenerationType.AUTO)
 private Long id;
 
 @Column
 private String stuff;

 public Long getId() {
  return id;
 }

 public void setId(Long id) {
  this.id = id;
 }

 public String getStuff() {
  return stuff;
 }

 public void setStuff(String stuff) {
  this.stuff = stuff;
 }

 @Override
 public String toString() {
  return "SimpleEntity [id=" + id + ", stuff=" + stuff + "]";
 }
 
}

here's the entity manager factory;

here's the Spring Integration for the JPA service

here's the Spring Integration for the HTTP endpoint

and here's a test showing it all

package de.incompleteco.spring.jpa;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertTrue;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.get;
import static org.springframework.test.web.servlet.request.MockMvcRequestBuilders.put;
import static org.springframework.test.web.servlet.result.MockMvcResultMatchers.status;
import static org.springframework.test.web.servlet.setup.MockMvcBuilders.webAppContextSetup;

import javax.annotation.Resource;

import org.codehaus.jackson.map.ObjectMapper;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.http.MediaType;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.web.WebAppConfiguration;
import org.springframework.test.web.servlet.MockMvc;
import org.springframework.test.web.servlet.MvcResult;
import org.springframework.web.context.WebApplicationContext;

import de.incompleteco.spring.jpa.domain.SimpleEntity;

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration({"classpath:/META-INF/spring/*-context.xml"})
@ActiveProfiles("junit")
public class IntegrationTest {

 @Resource
 private WebApplicationContext context;
 
 private ObjectMapper mapper;
 
 private MockMvc mvc;
 
 @Before
 public void before() throws Exception {
  //setup
  mapper = new ObjectMapper();//json
  mvc = webAppContextSetup(context).build();  
 }

 @Test
 public void test() throws Exception {
  //create an object
  SimpleEntity entity = new SimpleEntity();
  entity.setStuff("hello world");
  //convert
  String json = mapper.writeValueAsString(entity);
  //execute
  MvcResult result = mvc.perform(put("/simpleEntity").contentType(MediaType.APPLICATION_JSON).content(json))
   .andExpect(status().isOk()).andReturn();
  //now convert back
  SimpleEntity resultEntity = mapper.readValue(result.getResponse().getContentAsByteArray(), SimpleEntity.class);
  //now check
  assertTrue(resultEntity.getId() > 0);
  assertEquals(entity.getStuff(),resultEntity.getStuff());
  //retrieve by the id
  result = mvc.perform(get("/simpleEntity").param("id", resultEntity.getId().toString()))
   .andExpect(status().isOk()).andReturn();
  //now convert
  SimpleEntity retrievedEntity = mapper.readValue(result.getResponse().getContentAsByteArray(), SimpleEntity.class);
  //now check
  assertEquals(entity.getStuff(),retrievedEntity.getStuff());
  //retrieve all
  result = mvc.perform(get("/simpleEntity"))
    .andExpect(status().isOk()).andReturn();
  //now it *should* be an array of SimpleEntity
  SimpleEntity[] entities = mapper.readValue(result.getResponse().getContentAsByteArray(),SimpleEntity[].class);
  //test
  assertNotNull(entities);
  assertTrue(entities.length > 0);
  
 }
}

if you want the code, it's here; https://github.com/incomplete-code/spring-integration-jpa

Spring LocalContainerEntityManagerFactoryBean, packagesToScan and JTA

Spring LocalContainerEntityManagerFactoryBean, packagesToScan and JTA

here's an interesting one, Hibernate 4, Spring JPA (using LocalContainerEntityManagerFactoryBean) and JTA.  the scenario is, testing in JUnit and need to specify (and emulate) a JTA transaction. unfortunately, it's not all straight out-of-the-box, but here it is;

first, LocalContainerEntityManagerFactoryBean treats everything as a non-JTA transaction, so there's a little extra configuration to be done.  To switch it out, Spring does provide a postProcessor for the PersistenceUnit and a mutable persistent unit to it, but we have to write our own switch.  (it's pretty straight forward, but maybe we can ask the nice chaps to include it out of the box in 3.2.x?)

here's my cut - you can roll/cut & paste your own

package org.incompletecode.spring.batch.jpa;
 
import org.springframework.orm.jpa.persistenceunit.MutablePersistenceUnitInfo;
import org.springframework.orm.jpa.persistenceunit.PersistenceUnitPostProcessor;
 
public class JTAPersistenceUnitPostProcessor implements
  PersistenceUnitPostProcessor {
 
 @Override
 public void postProcessPersistenceUnitInfo(MutablePersistenceUnitInfo pui) {
  //switch the nonJTA to JTA
  pui.setJtaDataSource(pui.getNonJtaDataSource());
  pui.setNonJtaDataSource(null);
 }
 
}

and now here's a quick example of using Bitronix as your JTA provider

 
 
 
 
     
 
  
 
 
 
     
 
 
 
     
     
 
 
 
 
 
      
      
      
      
      
      
          
              jdbc:h2:mem:a;DB_CLOSE_ON_EXIT=FALSE;DB_CLOSE_DELAY=-1
          
        
 
 

Spring Data JPA & Hibernate & EhCache

Spring Data JPA & Hibernate & EhCache

here's a simple setup to run an EhCache configuration on Spring Data JPA

first the maven

  4.0.0
  org.incomplete.spring
  spring-jpa-cache
  0.0.1-SNAPSHOT
  war
  spring-jpa-cache
  
  	
	  
	    org.apache.maven.plugins
	    maven-compiler-plugin
	    2.3.2
	    
	        1.6
	        1.6
	    
	    	
  	
  
  
  	
  		org.springframework.data
  		spring-data-jpa
  		1.1.0.RELEASE
  	
  	
  		org.hibernate
  		hibernate-entitymanager
  		4.1.4.Final
  	
  	
  		org.hibernate
  		hibernate-validator
  		4.3.0.Final
  	
  	
  		org.hsqldb
  		hsqldb
  		2.2.8
  		test
  	
  	
  		org.springframework
  		spring-test
  		3.1.0.RELEASE
  		test
  	
  	
  		junit
  		junit
  		4.8.2
  		test
  	
  	
  		log4j
  		log4j
  		1.2.14
  	
  	
  		org.slf4j
  		slf4j-log4j12
  		1.6.0
  	
  	
  		net.sf.ehcache
  		ehcache-core
  		2.6.0
  	
  	
  		org.hibernate
  		hibernate-ehcache
  		4.1.4.Final
  	
  

then our basic entity

package org.incompletecode.spring.domain;
 
import java.io.Serializable;
 
import javax.persistence.Cacheable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
 
@Cacheable
@Entity
@Table(name="simple_entity")
public class SimpleEntity implements Serializable {
 
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
 
	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private Long id;
	
	@Column
	private String name;
	
	@Column
	private String description;
 
	public Long getId() {
		return id;
	}
 
	public void setId(Long id) {
		this.id = id;
	}
 
	public String getName() {
		return name;
	}
 
	public void setName(String name) {
		this.name = name;
	}
 
	public String getDescription() {
		return description;
	}
 
	public void setDescription(String description) {
		this.description = description;
	}
 
	@Override
	@Transient
	public String toString() {
		return "SimpleEntity [id=" + id + ", name=" + name + ", description="
				+ description + "]";
	}
	
	
	
}

our DAO

package org.incompletecode.spring.dao;
 
import org.incompletecode.spring.domain.SimpleEntity;
import org.springframework.data.jpa.repository.JpaRepository;
 
public interface SimpleEntityDAO extends JpaRepository< SimpleEntity, Long > {
 
}

now our Spring Config for the key parts (Spring Data, JPA, Transaction Manager)

 
	
	
	
 
	
		
		
		
			
				
					
		
		
			
				org.hibernate.cache.SingletonEhCacheProvider
				/META-INF/cache/ehcache.xml
				true
				true
				org.hibernate.cache.ehcache.EhCacheRegionFactory
			
		
	
 
	
		
		
	
	

and of course our database stuff

the cache config

and now for a unit test

package org.incompletecode.spring.dao;
 
import static org.junit.Assert.assertNotNull;
 
import java.util.ArrayList;
import java.util.List;
 
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hsqldb.jdbc.JDBCDataSource;
import org.incompletecode.spring.domain.SimpleEntity;
import org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.mock.jndi.SimpleNamingContextBuilder;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.util.StopWatch;
 
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"classpath:/META-INF/spring/data-context.xml",
	"classpath:/META-INF/spring/datasource-context.xml"})
public class SimpleEntityDAOTest {
 
	private static final Log logger = LogFactory.getLog(SimpleEntityDAOTest.class);
	
	public static final int count = 10000;
	
	@Autowired
	private SimpleEntityDAO simpleEntityDAO;
	
	private StopWatch stopWatch = new StopWatch();
	
	@BeforeClass
	public static void beforeClass() throws Exception {
		SimpleNamingContextBuilder builder = SimpleNamingContextBuilder.emptyActivatedContextBuilder();
		//build
		JDBCDataSource dataSource = new JDBCDataSource();
		dataSource.setUrl("jdbc:hsqldb:mem:a");
		//bind
		builder.bind("jdbc/datasource", dataSource);
	}
	
	@Before
	public void before() throws Exception {
		List< SimpleEntity > simpleEntities = new ArrayList< SimpleEntity >(count);
		//create a list
		stopWatch.start();
		for (int i=0; i< count; i++) {
			SimpleEntity simpleEntity = new SimpleEntity();
			simpleEntity.setName("code" + i);
			simpleEntities.add(simpleEntity);
		}//end for
		stopWatch.stop();
		logger.info("time to create: " + stopWatch.getLastTaskTimeMillis() + "ms");
		//save
		stopWatch.start();
		simpleEntityDAO.save(simpleEntities);
		stopWatch.stop();
		logger.info("time to save: " + stopWatch.getLastTaskTimeMillis() + "ms");
	}
	
	@After
	public void after() throws Exception {
		stopWatch.start();
		simpleEntityDAO.deleteAllInBatch();
		stopWatch.stop();
		logger.info("time to delete in batch: " + stopWatch.getLastTaskTimeMillis() + "ms");
	}
	
	@Test
	public void test() throws Exception {
		//create an entity
		SimpleEntity simpleEntity = new SimpleEntity();
		simpleEntity.setName("hi");
		//save
		simpleEntityDAO.save(simpleEntity);
		//check
		assertNotNull(simpleEntity.getId());
	}
	
	@Test
	public void testRetrieveAll() throws Exception {
		stopWatch.start();
		simpleEntityDAO.findAll();
		stopWatch.stop();
		logger.info("time to retrieve all: " + stopWatch.getLastTaskTimeMillis() + "ms");
	}
	
	@Test
	public void testMultipleRetrieveAll() throws Exception {
		for (int i=0;i<10;i++) {
			testRetrieveAll();
		}
	}
	
}

here's some stats on the unit test above; running it on my local machine inside STS, i can squeeze a 20% performance improvement with caching enabled.  not too shabby