Pfthb

I have a table in a DB that I cannot modify, and I have a list of pairs of values that I have to use in order to select the records from that table that exactly match both the values in the list.

For example my list of pairs consists of ('A1', 'A2'), ('B1', 'B2'), ('C1', 'C2'), and my table consists of the following data

--------------------------
| ID | COLUMN1 | COLUMN2 |
--------------------------
| 1 | 'A1' | 'A2' |
| 2 | 'B1' | 'B2' |
| 3 | 'A1' | 'B2' |
| 4 | 'B1' | 'A2' |
| 5 | 'C1' | 'C2' |
--------------------------

The correct result set should be

1, 'A1', 'A2'
2, 'B1', 'B2'
5, 'C1', 'C2'

which means that records like

3, 'A1', 'B2'
4, 'B1', 'A2'

should not be in it.

How do I achieve such a result using HQL?
Performance is an obstacle in my situation, this is why a solution implemented via concatenation of values is not viable for me.

EDIT:

if this can help, the Java entity is very simple and it looks like this:

package org.example;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;

@Entity
@Table(name = "MY_TABLE")
public class ExampleEntity implements Serializable 
 @Id
 @Column(name = "ID", unique = true)
 private int id;

 @Column(name = "COLUMN1")
 private String column1;

 @Column(name = "COLUMN2")
 private String column2;

 public int getId() 
 return id;
 

 public void setId(int id) 
 this.id = id;
 

 public String getColumn1() 
 return column1;
 

 public void setColumn1(String column1) 
 this.column1 = column1;
 

 public String getColumn2() 
 return column2;
 

 public void setColumn2(String column2) 
 this.column2 = column2;
 

I try to resume the possible solutions, considering comments in the question.

Pay attention

In these solutions, I'll write in SQL because I don't know your objects, but these query can be re-write in HQL if you write your object structures

Solution 1

Consider ONLY input data set

SELECT *
FROM yourtable
WHERE column1 = 'A1' AND column2 = 'A2'
OR column1 = 'B1' AND column2 = 'B2'
OR column1 = 'C1' AND column2 = 'C2'

Solution 2

If you can create a separated table with good couple, as follow:

CREATE TABLE okcouples (col1 varchar(10), col2 varchar(10))

SELECT yt.*
FROM yourtable yt
JOIN okcouples oc
 ON yt.column1 = oc.col1
 AND yt.column2 = oc.col2

Solution 3

This is the same scenario of Solution 2 (create a separated table) but instead of JOIN you can use EXISTS clause, as follow:

SELECT *
FROM yourtable yt
WHERE EXISTS
 (SELECT 'OK'
 FROM okcouples oc
 WHERE oc.col1 = yt.column1
 AND oc.col2 = yt.column2)