How to add some custom behavior to Spring Data Jpa generated query?

Question

I am using Spring Data Jpa with a legacy Oracle database. Some of our tables used char instead of varchar2 type. That causes some issue when I am using Spring Data Jpa generated query.

For example, I have an entity which named User

@Entity
@Table(name = "USERS")
public class User implements Serializable {

    private static final long serialVersionUID = -4054737587007197812L;

    @Id
    private Long userId;
    @Column(columnDefinition = "char", length = 12)
    private String username;
}

My UserRepository is like:

@Repository
public interface UserRepository extends JpaRepository<User, Long> {

    public User findByUsername(String name);
}

As usual, the Spring Data will generate a query like select * from users where username = ?. But since my Oracle database is using char type for column username, I will not get any result if I passed in the username parameter without padding enough space.

userRepository.findByUsername("Chris") -> not result
userRepository.findByUsername("Chris[with 7 spaces]") -> 1 result

Is there anyway to add some custom code to Spring Data Jpa and let Spring Data Jpa pads the spaces automatically? I don't want to write all query method again and pad the space manually since I have added the length in the Entity class ().

I can't think of a way to do this in JPA because you would probably have columns of different sizes. If you are using Hibernate, this could be achieved using user types. — manish
– manish, Commented Feb 25, 2015 at 15:58

manish · Accepted Answer · 2015-02-27 19:20:24Z

1

If you are using Hibernate as the JPA provider, this can be achieved by creating a Hibernate UserType.

Create a custom user type called CharType. Perform the necessary padding and trimming within this class.
Annotate fields with CHAR(n) as the backing datatype to use CharType.

A complete solution, along with the CharType class is available on Github. See the Person entity and test cases in PersonRepositoryTest for guidance on how to integrate something like this in your own code.

answered Feb 27, 2015 at 19:20

manish

20.2k5 gold badges70 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

crisli Over a year ago

Thanks a lot. It works. But only for ojdbc7. ojdbc6 doesn't support ParameterMetaData.getPrecision(index) method.

crisli Over a year ago

Besides, I have another issue. If I update grade to ojdbc7. I have ORA-00942: table or view does not exist error. I am using Spring Boot now and I have set the spring.jpa.properties.hibernate.default_schema=my_oracle_username

manish Over a year ago

For some reason OJDBC6 does not implement most of the ParameterMetaData methods. This is not a deficiency in the Hibernate UserType solution but utter disregard for end-user requirements and JDBC spec on part of Oracle so there isn't much we can do. My experience with the Oracle drivers has been to set the schema name in the JDBC URL as jdbc:oracle:thin:<schema>@//<server:<port>/<tnslistener> instead of setting the schema name for the JPA provider. Make sure you are not setting the schema name in both places as the driver will then prefix table names with the schema name twice.

Collectives™ on Stack Overflow

How to add some custom behavior to Spring Data Jpa generated query?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related