Add full-text search queries with Hibernate

Open the file AddressRepository.java from the directory src/main/java/com/sap/hana/hibernate/sample/repositories.

The current implementation uses a standard LIKE query to retrieve matching addresses

public List<Address> findByAddressContaining(String address) {
  Query query = this.em
      .createQuery( "select a from Address a where upper(a.address) like upper(:address)", Address.class );

  query.setParameter( "address", "%" + address + "%" );

  query.setMaxResults( 20 );

  List<Address> addresses = query.getResultList();

  return addresses;
}

As there is no Hibernate support for SAP HANA’s full-text search capabilities you’ll need to change the query to a native SQL query. Then you can use the CONTAINS predicate to perform a full-text search on the address table.

public List<Address> findByAddressContaining(String address) {
  Query query = this.em
      .createNativeQuery(
            "select a.base_id, a.address, a.address_number, a.address_number_suffix, a.cnn, a.street_name, a.street_type, a.x, a.y, a.zip_code, a.location "
          + "from address a "
          + "where CONTAINS(a.address, :address, FUZZY(0.7, 'similarCalculationMode=searchCompare, textSearch=compare')) "
          + "order by SCORE() desc "
          + "limit 20",
          Address.class );

  query.setParameter( "address", address );

  List<Address> addresses = query.getResultList();

  return addresses;
}

The CONTAINS predicate used in the query matches the input from the :address parameter against the address column of the address table using full-text search. It then calculates a similarity score for each row. If the similarity score is at least 0.7 as specified in the FUZZY option, the value is included in the result set.

The similarity score is also used to sort the result, so the best matches are returned at the top of the list.

You can find more information about the CONTAINS predicate and fuzzy search in the SAP HANA Search Developer Guide.

Note that the address parameter doesn’t need the surrounding % characters any more.

Since the query is a native query now, query.setMaxResults() can’t be used any more. Instead, the limit is included in the query via a limit clause.

Save the AddressRepository.java file.

To use term mappings the term mapping table must be populated first.

Open the file DatabaseInitializer.java from the directory src/main/java/com/sap/hana/hibernate/sample/app and find the method importTermMappings.

@EventListener
@Transactional
@Order(3)
public void importTermMappings(ContextRefreshedEvent event) {
  Query query = this.em.createQuery( "delete from TermMapping" );
  query.executeUpdate();

  importTermMapping( "ADDRESS", "street", "st", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "avenue", "ave", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "boulevard", "blvd", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "circle", "cir", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "court", "ct", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "drive", "dr", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "hill", "hl", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "highway", "hwy", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "lane", "ln", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "place", "pl", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "plaza", "plz", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "road", "rd", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "stairway", "stwy", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "terrace", "ter", BigDecimal.valueOf( 1 ) );

  importTermMapping( "ADDRESS", "wy", "way", BigDecimal.valueOf( 1 ) );
  importTermMapping( "ADDRESS", "prk", "park", BigDecimal.valueOf( 1 ) );
}

This method already defines mappings for the street abbreviations. What is still missing is the implementation of the importTermMapping method which actually writes the mappings to the database.

This method should take a term and map it to another term using a given term mapping list ID and a given weight.

To improve the search results even more, all substrings of the first term should also be mapped to the second term to allow searches while the user is still typing. Otherwise there wouldn’t be any matches until the user has finished typing the entire word, for example, ‘boulevard’. To make sure that short string that might also match other terms don’t distort the search result, the respective term weight will be exponentially decreased with the term length.

private void importTermMapping(String listId, String term1, String term2, BigDecimal weight) {
  for ( int i = term1.length(); i > 1; i-- ) {
    String subTerm = term1.substring( 0, i );
    if ( subTerm.equals( term2 ) ) {
      continue;
    }

    TermMapping mapping = new TermMapping();
    mapping.setListId( listId );
    mapping.setMappingId( UUID.randomUUID().toString() );
    mapping.setTerm1( subTerm );
    mapping.setTerm2( term2 );
    mapping.setWeight( weight.multiply( BigDecimal.valueOf( Math.pow( 0.8, term1.length() - i ) ) ) );

    this.em.persist( mapping );
  }
}

As you can see, the method uses a for loop to iterate over the length of the term to be mapped. For each substring until the length 2 it creates a term mapping of that string to the mapping term using the given term mapping list ID, a new random mapping ID, the substring and the mapping term, and a weight calculated by multiplying the given weight by a factor which exponentially decreases with the term length.

Save the DatabaseInitializer.java file.

Now that the term mapping table is populated the full-text search needs to be configured to use it.

Open the file AddressRepository.java from the directory src/main/java/com/sap/hana/hibernate/sample/repositories.

Two new configuration options need to be added in addition to the ones already used in the query (similarCalculationMode and textSearch). The new options are termMappingListId for specifying which term mapping list to use, and termMappingTable for specifying the table where the term mapping list is stored.

public List<Address> findByAddressContaining(String address) {
  Query query = this.em
      .createNativeQuery(
            "select a.base_id, a.address, a.address_number, a.address_number_suffix, a.cnn, a.street_name, a.street_type, a.x, a.y, a.zip_code, a.location "
          + "from Address a "
          + "where contains(a.address, :address, FUZZY(0.7, 'similarCalculationMode=searchCompare, textSearch=compare, termMappingListId=ADDRESS, termMappingTable=TERM_MAPPING')) "
          + "order by score() desc "
          + "limit 20",
          Address.class );

  query.setParameter( "address", address );

  List<Address> addresses = query.getResultList();

  return addresses;
}

Save the AddressRepository.java file.