Multi-Column Query using IN clause in Java

We all use IN clause every time we need to fetch multiple results from database at once. If the query was over a single primary key it’s perfect!

But the challenge I faced was how to do the query over multiple columns together ?

I tried looking for the sources to understand how does it actually work and how can I leverage hibernate and JPA to query the list of items using multiple columns ?

So, I assume the problem statement is pretty clear till here…

1. We need to query multiple items at once to avoid the round trips.
2. We need to make sure the columns and their respective values must not go out of order i.e the values to the IN clause should be in order per column.

In MySQL, we have a concept of composite key i.e a row which can be uniquely identified by using multiple columns which has been declared to be unique together.

There is a concept called row constructor in MySQL which allows simultaneous comparisons of multiple values. Another interesting questions which branches out from here is — what about indexes ? How will they behave?

Answer is- When using composite key columns few things should be kept in mind-

• Ordering of columns is important i.e if we have 4 keys which uniquely identify a row we must make sure to use them in the order.

→ How to check order?

Use SHOW INDEX FROM <TABLE_NAME>; and check the “Seq_in_index” column. This indicates the sequence number of the column in the index. If multiple columns are part of the index, the sequence number will be assigned based on how the columns were ordered during the index creation time. Sequence number starts from 1.

Based on this we can arrange the columns in where clause.

→ How to know if index will work or not?

On a Table suppose Index is created on multiple columns → A1, A2, A3

So the general rule is- Composite Indexes will be used only when where clause covers columns in the order of index creation.

if order of A1 is 1, order of A2 is 3 and order of A3 is 2. We must make sure order is maintained else index won’t take effect and performance will degrade.

Lets understand with few combinations

A1 = ? ,

A1= ? and A2 = ?,

A1=? and A3= ?,

A 1= ? and A2= ? and A3= ?

→ Index will work in all these cases since we have maintained the relative ordering.

A2=? and A1=?

A3=? and A2=?

A3=?

A2=?

→ In all these cases, it won’t work since A1 which stands 1st at sequence is used at wrong order/ not used at all.

Another option is to look by cardinality of the column. Higher the cardinality, higher the chances that optimiser might select that column. So try both and see which works out for you the best.

→ How to check query efficiency?

Always use EXPLAIN ANALYZE ( <QUERY>) to understand the underlying execution of queries and its run time. It gives a better picture.

Implementation:

For solving this problem, we have to find some way to pass a bunch of column names together in IN clause and also make sure to pass bunch of values corresponding to those columns and hibernate should translate them correctly in order 🤔

One solution, I stumbled upon was @ Embeddable and @ EmbeddedId annotation in hibernate which are useful when you want to create a combination of columns / properties.

Step 1: Create a class and annotated with @ Embeddable so that it can easily embedded in another class which will requires this.

@Embeddable
@NoArgsConstructor
@EqualsAndHashCode
public class CompositePrimaryId implements Serializable {

    @Column(name="ab")
    private String ab;

    @Column(name = "ac")
    private String ac;

    @Column(name = "ad")
    private String ad;

    public CompositePrimaryId(String ab, String ac, String ad) {
        this.ab = ab;
        this.ac = ac;
        this.ad = ad;
    }

It is just like separating some common properties from the classes and embedding them wherever required to avoid duplicate code.

Step 2: Embed the required columns into the class.

@Getter
@Setter
@Table(name = "<table_name>")
@Entity
public class MainTable implements Serializable {

    @EmbeddedId
    private CompositePrimaryId compositePrimaryId;
     
    private boolean check;
    // other columns here..

}

This behaviour can be visualised like splitting tables into 2 separate tables (logically) and then embedding the required columns when required without actually altering the table structure. That’s the power of Hibernate ! 🔥

Step 3: Get the repository, create the entity manager and use the query builder to create the native query and pass the parameters as list.

@Autowired
MainTableRepository mainTableRepository;
List<CompositePrimaryId> compositeIds=new ArrayList<>();
// add all your composite ids here
CompositePrimaryId cpId1=new CompositePrimaryId();
cpId1.setAb(123);
cpId1.setAc('E');
cpId1.setAd('ZSR');
compositeIds.add(cpId1);
CompositePrimaryId cpId2=new CompositePrimaryId();
cpId2.setAb(143);
cpId2.setAc('P');
cpId2.setAd('ZSR');
compositeIds.add(cpId2);
mainTableRepository.getResultsFromMainTable(compositeIds);
@Repository
class MainTableRepository{
@Autowired
EntityManager entityManager;
public List<MainTableDto> getResultsFromMainTable(List<CompositePrimaryId> compositeIds) {
    String sqlQuery = "SELECT new com.test.MainTableDto(mt.compositePrimaryId.ab,mt.check) FROM MainTable mt WHERE mt.compositeid IN (?1)";
    Query query = entityManager.createQuery(sqlQuery, MainTableDto.class);
    query.setParameter(1, compositeIds);
    return query.getResultList();
}
}

Step 4- Check the query 👀

Final query will look something like this-

SELECT ab, check FROM MainTable WHERE ab=123 and ac=”E” and ad=”ZER” OR ab=143 and ac=”P” and ad=”ZSR”;

We are done !! 😀

If you like this post please provide your support by claps :) and let me know your thoughts. Thank you !!