CODEWITHSUNDEEP
spring-dataspring-data-jpa
crisli
crisli

Reputation: 562

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

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 ().

Upvotes: 1

Views: 2367

Answers (1)

manish
manish

Reputation: 20135

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

  1. Create a custom user type called CharType. Perform the necessary padding and trimming within this class.
  2. 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.

Upvotes: 1

Related Questions