Search code examples
mysqlhibernatejpapersistence

Bulk/batch search in MySql using JPA/Native query

I have a MySQL database table with 10 million entities. I have to search for entities with multiple parameters. Example:

select * from result_table where roll=12345 and reg=6789 and exam='ABC';

I'm looking for an efficient way to batch entity search using JPA or native query using a single query or any other optimized option. Example:

select * from result_table where roll=1234 and reg=1234 and exam='ABC';
select * from result_table where roll=1234 and reg=1234 and exam='DEF';
select * from result_table where roll=2222 and reg=3333 and exam='XYZ';

Mysql IN Operator doesn't resolve my issue because roll and reg can be same in different exam. Thank you.

Solution

  • Please try this,

    create table

    create table Test(id integer,roll integer, reg integer, exam varchar(100));

    insert records

    insert into Test(id,roll,reg, exam) values(1,1234,1234, "ABC");

insert into Test(id,roll,reg, exam) values(1,1234,1234, "DEF");

insert into Test(id,roll,reg, exam) values(1,2222,3333, "XYZ");

    select query

    select * from Test where CONCAT(roll,reg,exam) in ('12341234ABC','12341234DEF','22223333XYZ');