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.
If you need more information, you can leave us a comment or send an email to refactorizando.web@gmail.com You can also contact us through our social media channels on Facebook or twitter and we will be happy to assist you!!