Hibernate for multi column IN clause

Data-sets sometimes do not make sense on their own and make sense only when they are viewed as pairs rather than individually. Composite keys that we use in the relational databases are such examples where although both of the values do make sense on their own but for a specific set of data the individual value is not enough and you need a pair or a set of keys to identify a record or to make sense of a record.

For example in the Forex market only a currency pair makes sense when you are looking for some prices. This is because the price of a unit of currency makes sense only in terms of the units of another currency. For example as of today GBP 1.0 gives you USD 1.3, however without the GBP the 1.3 USD does not make any sense.

I such scenarios when we want to look up for a list of values in the SQL IN clause, we need to be able to provide a pair rather than a single value, i.e., running the IN clause over two columns. Something like follows.

    SELECT DISTINCT notional_currency, other_notional_currency
    FROM instrument_descriptor 
    WHERE (notional_currency, other_notional_currency) 
    IN (('AUD', 'EUR'), ('EUR', ‘GBP’), ('AUD', 'USD''))
    EORDER BY notional_currency, other_notional_currency

However, when it comes to implement that in Hibernate. To achieve that we have to create a separate component that contains these two columns and then use that component to populate the IN clause.

    @Embeddable
    public class InstrumentDescriptorCurrencyPair {

 private String notionalCurrency;
 private String otherNotionalCurrency;
 
 // No argument constructor required by Hibernate
 public InstrumentDescriptorCurrencyPair() {
 }
 
 // Getters and Setters .. 
 public String getNotionalCurrency() {
  return notionalCurrency;
 }
 ......
    }

Note that the column names has to be the same as defined in the original entity.

Then we need to "embed" the component in the original entity.

    // Entity class .. 
    public class InstrumentDescriptor extends PersistentObject {

        private static final long serialVersionUID = 2946565846321427118L;
        private static final int BASE_HASH = InstrumentDescriptor.class.getName().hashCode();

        // Non related fields omitted for brevity .. 
        private String notionalCurrency;
        private String otherNotionalCurrency;
        
        // Embed the component .. 
        @Embedded
        private InstrumentDescriptorCurrencyPair currencyPair;
        
        // Component getters and setters .. 
        public InstrumentDescriptorCurrencyPair getCurrencyPair() {
            return currencyPair;
        }
        public void setCurrencyPair(InstrumentDescriptorCurrencyPair currencyPair) {
            this.currencyPair = currencyPair;
        }

        ......

    }

Now we can use the Hibernate to run the SQL with multiple columns in the IN clause.

    @SuppressWarnings("unchecked")
    public IterableScrollableResults listInstruments2(List quotePairs, Date startDate, Date endDate) {
     
     InstrumentDescriptorCurrencyPair columns;
     List currencyPairs = new ArrayList<>();
     
     // Create a list of pairs using the Component
     for (QuotePair quotePair : quotePairs) {
      columns = new InstrumentDescriptorCurrencyPair(quotePair.getBaseCurrency(), quotePair.getTermCurrency());
      currencyPairs.add(columns);
 }

     // Notice that we are using the name of the component as defined in the Entity class
                   String sql = "FROM InstrumentDescriptor AS instrument "
          + “WHERE instrument.currencyPair IN (:currencyPair) "
          + "ORDER BY instrument.notionalCurrency, instrument. otherNotionalCurrency";
        
            ScrollableResults results = (ScrollableResults) template.execute((Session session) ->{
         
            Query query = session.createQuery(sql);
            query.setParameterList("currencyPair", currencyPairs);
            return query.scroll();
        });

        return results;
    }

For those of you how are using the configurations instead of the annotations, we can do that in the .hbm.xml files as well.

Just update the .hbm.xml files for the entity and add the new component's configuration.

    <component name="currencyPair" class="net.worldflow.dvega.common.model.InstrumentDescriptorCurrencyPair">
     <property name="notionalCurrency" access="field" length="3" insert="false" update="false" />
     <property name="otherNotionalCurrency" access="field" length="3" insert="false" update="false" />
    </component>

Notice that the properties are added as read-only in the Component configuration.