Spring Boot JPA - EntityManager converting ID field to uppercase

Question

Im building a Spring Boot application which is auto configuring my JPA datasource based on the below properties.

spring.datasource.url=jdbc:jtds:sqlserver://localhost;DatabaseName=testDB;useCursors=true
spring.datasource.username=testDBUser
spring.datasource.password=test123
spring.datasource.driver-class-name=net.sourceforge.jtds.jdbc.Driver
spring.datasource.maxActive=100
spring.datasource.minIdle=5
spring.datasource.removeAbandoned=true
spring.datasource.removeAbandonedTimeout=300
spring.datasource.defaultTransactionIsolation=REPEATABLE_READ
spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.EJB3NamingStrategy
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect
spring.jpa.properties.hibernate.show_sql=false
spring.jpa.properties.hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory
spring.jpa.properties.hibernate.cache.use_second_level_cache=true
spring.jpa.properties.hibernate.cache.use_query_cache=true

Now, I have a table as below

TableA{
String id;
String prop1;
String prop2;
String prop3;
}

The test case that fails is below. As you can see below I'm just trying to compare the result from EntityManager vs Spring Data Repository.

The test fails because the "id" value returned from EntityManager is "D97" (upper case) and the value returned from Spring Data repository is "d97". The actual value is "d97".

@Test
public void testIfSpringRepositoryWorks() {
    EntityManager entityManager = entityManagerFactory.createEntityManager();
    entityManager.getTransaction().begin();

    List<TableAEntity> result = entityManager.createQuery("FROM TableAEntity WHERE id='d97'" , TableAEntity.class).getResultList();

    if(result==null) {
        Assert.fail("Empty Result from EntityManager for id: d97 ");
    } else {
        TableAEntity fromEntityManager = result.get(0);
        TableAEntity fromSpringJPA = tableARepository.findOne("d97");

        if(!fromEntityManager.equals(fromSpringJPA)) {
            Assert.fail("TableAEntity is not same");
        }
    }


    entityManager.getTransaction().commit();
    entityManager.close();
}

I'm not sure on why EntityManager is converting my id field to upper case. Please help in finding out the root cause.

Thanks!

Can you post the equals method of TableAEntity? Where in your test are you asserting that the Ids are different? — MarkOfHall, Jan 27 '15 at 15:20
Hi Steve.. the equals method is a basic implementation through lombok project... using the @EqualsAndHashCode annotation. — rakpan, Jan 27 '15 at 15:24
The default SQLServer configuration is case INsensitive. Thus, if your data contains a row with id 'd97' and a row with 'D97' then both rows will be returned. That is one reason many choose to use numeric types for Id columns. see http://stackoverflow.com/questions/1411161/sql-server-check-case-sensitivity — MarkOfHall, Jan 27 '15 at 15:25
I agree! But why is EntityManager behaving differently? Our existing app was built on Hibernate's SessionFactory implementation and that too works as expected (returns the actual value of 'd97').. Is there any configuration that i'm supposed to have? — rakpan, Jan 27 '15 at 15:30
Just for giggles, lets assert that the result of the entity manager query only has one row. — MarkOfHall, Jan 27 '15 at 15:41
Please post the actual class and the generated `equals` method. I would also doubt the usefulness of this test (unless you don't trust the frameworks you are using?). You are using the same `EntityManagerFactory` (I assume) for a plain JPA as for the Spring Data based repositories the latter uses the `find` method of the `EntityManager` and not a query. You might want to check the number of results returned from the query. — M. Deinum, Jan 27 '15 at 19:22

score 1 · Answer 1 · answered Jan 28 '15 at 16:14

I'm not sure if this is a problem or a situation for me to create a work around. But here's what is happening.

In the above test, my query to Spring JPA is

tableARepository.findOne("d97");

which is equivalent to

entityManager.find(TableA.class,"d97");

When either of both is executed, the below portion of the code forms the entity key in the Hibernate Row Reader logic (org.hibernate.loader.plan.exec.process.internal.RowSetProcessorHelper).

public static EntityKey getOptionalObjectKey(QueryParameters queryParameters, SessionImplementor session) {
    final Object optionalObject = queryParameters.getOptionalObject();
    final Serializable optionalId = queryParameters.getOptionalId();
    final String optionalEntityName = queryParameters.getOptionalEntityName();

    return INSTANCE.interpretEntityKey( session, optionalEntityName, optionalId, optionalObject );
}

Needless to say, whatever parameter you pass as the key will ideally be the primary key value of the returned object (Ignoring the value returned by the database).

Now, when I execute the query

 List<TableAEntity> result = entityManager.createQuery("FROM TableAEntity WHERE id='d97'" , TableAEntity.class).getResultList()

Since I'm not passing any primary key directly to hibernate its probably just retaining the value returned by hibernate.

Now as SQLServer default collation is case insensitive, it is capitalizing my primary key when it returns the data.

I'm not sure of the rationale behind why Hibernate overrides the value of primary key returned by the database.

But, it could be helpful if hibernate has a feature to turn off such a row processing logic.

Hope this helps!

Spring Boot JPA - EntityManager converting ID field to uppercase

1 Answers1