JPA query using a database function to use a specific index

Question

I have a table named vehicle which has a column named VIN. Users need the ability to search based on the last 6 characters of the VIN. So I've added an index in Postgres like so:

  CREATE INDEX "ix_vin_last_6" ON vehicle(RIGHT(vin,6));

I added a few test records as follows:

LAST6VIN_A_123456
LAST6VIN_B_123456
LAST6VIN_C_123456

Using SpringDataJPA repository, here is one way to query for the above test records:

List<Vehicle> findByVinEndingWith(String vin)

But the JPA query generated above uses a LIKE clause, like so:

where
    vehicle0_.vin like ?

How do I make a repository method to query for the above 3 test records using the last 6 characters that uses the index I created above so that it generates this query:

SELECT vin, year, make, model
FROM vehicle 
WHERE RIGHT(vin,6) = '123456';

The above query works fine when I execute it using pgadmin command line.

JB Nizet · Accepted Answer · 2015-06-29 12:26:01Z

2

JPQL supports (since JPA 2.1 IIRC) calling arbitrary database functions using function:

select v from Vehicle v
where function('RIGHT', v.vin, 6) = :endOfVin

answered Jun 29, 2015 at 12:26

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

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

1 Comment

SGB Over a year ago

Excellent. This worked for me. I was able to use EXPLAIN to confirm that it is using the relevant index with this query. Thanks

Collectives™ on Stack Overflow

JPA query using a database function to use a specific index

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related