In this article, we will see how to use the API Criteria for Regular Expressions with PostgreSql.

We will analyze two specific cases: firstly, dealing with a PostgreSQL range type and retrieving all records with a number less than the upper bound of the range; secondly, using an expression to match and retrieve all records that satisfy the given expression.

For the examples, we will utilize the Specification interface, as seen in a previous article.

Regular Expression for PostgreSql with Int4Range Type

Int4Range is a data type in PostgreSql that represents a range of 32-bit integer values. It specifies a closed range where the endpoints are included and can represent any set of integer values within that range.

The Int4Range data type is useful when storing and querying ranges of integer values in a database. For example, it can be used to represent a range of ages, a range of prices, or any other type of numerical range.

This data type provides functions and operators that allow efficient operations and queries on ranges. Some common operations include intersecting ranges, combining ranges, checking if a value is within a range, and finding overlapping ranges.

In summary, Int4Range is a data type in PostgreSql that facilitates the manipulation and querying of 32-bit integer value ranges.

The Int4Range type represents a range of integers, such as [7, 12]. To work with this type in our Spring Boot application, we will use the following library:

    <dependency>
      <groupId>com.vladmihalcea</groupId>
      <artifactId>hibernate-types-52</artifactId>
      <version>${hibernate-types-52.version}</version>
    </dependency>

This library enables us to work with ranges as if we were dealing directly with PostgreSql types.

Example of Criteria API with Regular Expression for PostgreSql using a Range Type

Consider the following database object:

@Getter
@Setter
@NoArgsConstructor
@Table(name = "car")
@Entity
@EqualsAndHashCode(of = {"id"}, callSuper = false)
public class CarDbo{

  @Id
  private Long id;

  @NotNull
  @Column
  private String description;

  @Column
  private String brand;

  @Column
  private Range<Integer> kilometresRange;

}

As shown, we have created a field of type Range<Integer>, representing a range of kilometers [0,10000].

Now, let’s retrieve all cars where the kilometers are less than 10000. For that, we will create the following class using the Criteria API:

@Component
public class CarSpecification implements Specification<CarDbo> {

private static Long MAX_KM = 10000L;

  private static String REG_EXP_EXTRACT_MAX_OF_RANGE = "(\\d+)(?!.*\\d)";

private static Specification<CarDbo> findCarLessThanTenThousand() {
    return (Specification<CarDbo>) (carRoot, query, cb) -> {
      List<Predicate> predicates = new ArrayList<>();

        Expression<String> range = carRoot.get(CarDbo_.kilometresRange)
            .as(String.class);

        Expression<Long> valueMaxRange = cb
            .function("substring", String.class, range, cb.literal(REG_EXP_EXTRACT_MAX_OF_RANGE)).as(Long.class);

          predicates.add(cb.lessThan(valueMaxRange, MAX_KM));

      return cb.and(predicates.toArray(Predicate[]::new));

}

In this example, we have converted the range type to a string and applied a regular expression to retrieve all records that are less than the specified value. It’s important to note that we have created a custom “substring” function specific to PostgreSql.

Example of Criteria API with Regular Expression for PostgreSql using a String Type

Building upon the previous CarDbo class, let’s search for all cars in the description field that contain the word “Ford”. Since the description field can contain various text, it is best to use a regular expression to find the word. We create the following regular expression: ‘.(Ford)+.‘.

@Component
public class CarSpecification implements Specification<CarDbo> {


  private static String REG_EXP_EXTRACT_WORD = ".*(_)+.*";

private static Specification<CarDbo> findCarsByNameInDescription(String name) {
    return (Specification<CarDbo>) (carRoot, query, cb) -> {
      List<Predicate> predicates = new ArrayList<>();

        Expression<String> nameExpression = carRoot.get(CarDbo_.description)
                .as(String.class);

            Expression<String> findWordExpression = cb
                .function("substring", String.class, nameExpression,
                    cb.literal(name))
                .as(String.class);

            predicates.add(
                cb.equal(findWordExpression, REG_EXP_EXTRACT_WORD.replace("_",name)));

      return cb.and(predicates.toArray(Predicate[]::new));

}

In the previous class, what we have done is create an expression using a criteria builder. We created a function called “substring” (to be recognized by PostgreSql), where we specify the field to search in. Then, we inserted the parameter “name” into the defined regular expression, replacing “_” with the actual name. This resulted in the expression “.(Ford)+.”.

If we translate this to an SQL statement, it would be as follows:

    select
        car.name
    from
        car car     
    where    
        substring(car_.nombre, '.*(Ford)+.*') = 'Ford'

Conclusion

In this article, API Criteria for Regular Expressions with PostgreSql, we have seen how to obtain results in PostgreSql based on a regular expression.