Entity retrieved from database with same case as in query

Question

My database contains the following table:

table:

country {
    code varchar(255) not null
        primary key
};

class:

@Entity
public class Country {
    @Id
    @Column(name = "code")
    private String mCode;

    public String getCode() {
        return mCode;
    }

    public void setCode(String code) {
        mCode = code;
    }
}

sample table rows:

| code |
|------|
| USA  |
| UK   |

When I retrieve a country using the following CrudRepository:

public interface CountryRepository extends CrudRepository<Country, String> {
}

First scenario:

mRepository.findOne("USA")

It will give me the following result in my rest api:

{
  "code": "USA"
}

Second scenario:

mRepository.findOne("UsA")

It will give me the following result in my rest api:

{
  "code": "UsA"
}

Third scenario:

mRepository.findOne("Usa")

It will give me the following result in my rest api:

{
  "code": "Usa"
}

I have also inspected the same behavior using the debugger and found my object in memory actually have the same behavior.

What I Want: I want the returned data to be the same case as in database.

Answer 1

you could wrap the CrudRepository with another layer such as service or controller.

public Country findOne(String code){
    Country country = mRepository.findOne(keyWord)
    if (country !=null){
        country.setCode(code)
        return country;
    }
    return null;
}

The entity returned from mRepository is what stored in database. I think you should have another method to do this special handling.

Answer 2

Try overriding the equals() and hashcode() methods in your entity such that they take the case of mCase into consideration (and don't use any other fields in these two methods).

Answer 3

By calling repository.findOne("Usa") (default implementation is SimpleJpaRepository.findOne) Hibernate will use EntityManager.find which instantiates the entity(if it's found in the Database and not present in first and second level cache) and set the passed argument value as primary key, using SessionImpl.instantiate method, instead of using the Select query result. I've filled a ticket in Hibernate Jira for this issue.

Solution 1:

Do not use natural id as a primary key:

As said, it's not recommended to use a business/natural key as primary key, as it may change in the future as business rules change (Business rules can change without permission!!) + If you're using clustered index, a string may be slower for primary key, and perhaps you will find two rows in the database: Country('USA'), Country('USA '), use a Surrogate Key instead, you can check this two SO questions for more details:

• What's the best practice for primary keys in tables?
• Strings as Primary Keys in SQL Database

If you choose this option don't forget to map a unique constraint for the business key using @UniqueConstraint:

@Entity
@Table(uniqueConstraints = @UniqueConstraint(columnNames = "code"))
public class Country {

    // Your code ...     

    @Column(name = "code", nullable=false)
    private String mCode;

    //...     
}

Solution 2:

Change Country.mCode case:

If you have a possibility to store all Country.code in UpperCase :

@Entity
public class Country {


    private String mCode;

    protected Country(){
    }

    public Country(String code){
       this.mCode = code.toUpperCase()
    }

    @Id
    @Column(name = "code")
    public String getCode() {
        return this.mCode;
    }

   // Hibernate will always use this setter to assign mCode value
   private void setCode(String code) {
       this.mCode = code.toUpperCase();
   }

}

Solution 3:

Customize the CrudRepository:

While adding a custom function to your repository, you should always get rid of the default findOne(String), so you force others to use the "safest" method.

Solution 3.1:

Use custom implementations for mRepository find method (I named it findOneWithRightStringCase):

public interface CountryRepositoryCustom {
    Country findOneWithRightStringCase(String id);    
}

public class CountryRepositoryImpl implements CountryRepositoryCustom{

    @PersistenceContext private EntityManager entityManager;

    @Override
    public Country findOneRespectCase(String id) {
        try {
            return entityManager.createQuery("SELECT c FROM Country c WHERE c.mCode=:code", Country.class).setParameter("code", id).getSingleResult();

        } catch (NoResultException ex) {
            return null;
        }

    }
}

public interface CountryRepository extends CrudRepository<Country, String>, CountryRepositoryCustom {   

    @Override
    public default Country findOne(String id) {
        throw new UnsupportedOperationException("[findOne(String)] may not match the case stored in database for [Country.code] value, use findOneRespectCase(String) instead!");
    }
}

Solution 3.2:

You may add a Query methods for your repository :

public interface CountryRepository extends CrudRepository<Country, String> {
    Country findByMCode(String mCode);

    @Override
    public default Country findOne(String id) {
        throw new UnsupportedOperationException("[findOne(String)] may not match the case stored in database for [Country.code] value, use findByMCode(String) instead!");
    }
}

Or Use @Query(JPQL):

public interface CountryRepository extends CrudRepository<Country, String> {
    @Query("select c from Country c where c.mCode= ?1")
    Country selectCountryByCode(String mCode);

    @Override
    public default Country findOne(String id) {
        throw new UnsupportedOperationException("[findOne(String)] may not match the case stored in database for [Country.code] value, use selectCountryByCode(String) instead!");
    }
}

Hope it helps!

_{Note: I'm using Spring Data 1.11.8.RELEASE and Hibernate 5.2.10.Final.}

Answer 4

As already hinted by @Bedla in the comment, you may be using a case insensitive varchar data type in your database. However, this is not recommended for primary keys in Hibernate (and in general), because Hibernate relies on id property value uniqueness when referring to entities in the persistence context (and second-level cache if enabled).

For example, after loading an entity by "USA" and then by "usa" (or after merging a detached "usa" while "USA" has already been loaded, etc) in the same persistence context you may end up with two different instances in the persistence context, meaning further that they would be flushed separately thus overwriting each other changes, etc.

Rather use a case sensitive data type in the database, but allow searching by ignoring case:

public interface CountryRepository extends CrudRepository<Country, String> {
    @Query("select c from Country c where upper(c.mCode) = upper(?1)")
    Country getCountry(String code);
}

PS Country codes are not good candidates for primary keys in general, because they can change.