Close

JPA Criteria API - CriteriaBuilder String Manipulation Methods

[Updated: Mar 7, 2019, Created: Mar 5, 2019]

CriteriaBuilder provides following methods for string manipulation:

package javax.persistence.criteria;
 ..........
public interface CriteriaBuilder {
    ..........
    //Create an expression for string concatenation.
    Expression<String> concat(Expression<String> x, Expression<String> y);
    Expression<String> concat(Expression<String> x, String y);
    Expression<String> concat(String x, Expression<String> y);
	
    //Create an expression for substring extraction.
    Expression<String> substring(Expression<String> x, Expression<Integer> from);
    Expression<String> substring(Expression<String> x, int from);
    Expression<String> substring(Expression<String> x, Expression<Integer> from, Expression<Integer> len);
    Expression<String> substring(Expression<String> x, int from, int len);
	   
    //Used to specify how strings are trimmed.
	public static enum Trimspec { 
        //Trim from leading end.  
        LEADING,
        //Trim from trailing end.
        TRAILING, 
        //Trim from both ends.
        BOTH 
    }
	
    //Create expression to trim blanks from both ends of
    Expression<String> trim(Expression<String> x);
    Expression<String> trim(Trimspec ts, Expression<String> x);
    Expression<String> trim(Expression<Character> t, Expression<String> x);
    Expression<String> trim(Trimspec ts, Expression<Character> t, Expression<String> x);
    Expression<String> trim(char t, Expression<String> x);
    Expression<String> trim(Trimspec ts, char t, Expression<String> x);
	
    //Create expression for converting a string to lowercase.
    Expression<String> lower(Expression<String> x);
    //Create expression for converting a string to uppercase.
    Expression<String> upper(Expression<String> x);
	
    //Create expression to return length of a string.
    Expression<Integer> length(Expression<String> x);
	
    //Create expression to locate the position of one string within another
    Expression<Integer> locate(Expression<String> x, Expression<String> pattern);
    Expression<Integer> locate(Expression<String> x, String pattern);
    Expression<Integer> locate(Expression<String> x, Expression<String> pattern, Expression<Integer> from);
    Expression<Integer> locate(Expression<String> x, String pattern, int from);
    .........
}

Check out the equivalent JPQL string functions here.

Example

Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String firstName;
  private String lastName;
  private String dept;
  private String address;
    .............
}

Using string manipulation methods

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeeFullName();
          findLongestNamedEmployee();
          findAllCities();
          findByCity();
          findByDept();
      } finally {
          entityManagerFactory.close();
      }
  }

  private static void findEmployeeFullName() {
      System.out.println("-- Employees full names --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<String> query = criteriaBuilder.createQuery(String.class);//create query object
      Root<Employee> employeeRoot = query.from(Employee.class);
      query.select(
              criteriaBuilder.concat(
                      criteriaBuilder.concat(employeeRoot.get(Employee_.FIRST_NAME), " "),
                      employeeRoot.get(Employee_.LAST_NAME)));
      TypedQuery<String> typedQuery = entityManager.createQuery(query);
      typedQuery.getResultList()
                .forEach(System.out::println);
      entityManager.close();
  }

  private static void findLongestNamedEmployee() {
      System.out.println("-- Find employee having the longest full name --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);//create query object
      Root<Employee> employeeRoot = query.from(Employee.class);

      Subquery<Integer> maxLengthSubQuery = query.subquery(Integer.class);
      Root<Employee> subQueryEmployeeRoot = maxLengthSubQuery.from(Employee.class);
      maxLengthSubQuery.select(
              criteriaBuilder.max(criteriaBuilder.length(
                      criteriaBuilder.concat(
                              criteriaBuilder.concat(subQueryEmployeeRoot.get(Employee_.FIRST_NAME), " "),
                              subQueryEmployeeRoot.get(Employee_.LAST_NAME))))
      );

      query.select(employeeRoot)
           .where(criteriaBuilder.equal(
                   criteriaBuilder.length(
                           criteriaBuilder.concat(
                                   criteriaBuilder.concat(employeeRoot.get(Employee_.FIRST_NAME), " "),
                                   employeeRoot.get(Employee_.LAST_NAME))
                   ),
                   maxLengthSubQuery)
           );

      TypedQuery<Employee> typedQuery = entityManager.createQuery(query);
      typedQuery.getResultList()
                .forEach(System.out::println);
      entityManager.close();
  }


  private static void findAllCities() {
      System.out.println("-- All Cities --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<String> query = criteriaBuilder.createQuery(String.class);//create query object
      Root<Employee> employeeRoot = query.from(Employee.class);

      Expression<String> citySubstringExpr = createCitySubstringExpression(criteriaBuilder, employeeRoot);
      query.select(citySubstringExpr)
           .distinct(true);

      List<String> resultList = entityManager.createQuery(query).getResultList();
      resultList.forEach(System.out::println);
      entityManager.close();
  }

  private static Expression<String> createCitySubstringExpression(CriteriaBuilder criteriaBuilder,
                                                                  Root<Employee> employeeRoot) {
      return criteriaBuilder.trim(
              criteriaBuilder.substring(employeeRoot.get(Employee_.ADDRESS),
                      criteriaBuilder.sum(
                              criteriaBuilder.locate(employeeRoot.get(Employee_.ADDRESS), ","),
                              1))
      );
  }

  private static void findByCity() {
      System.out.println("-- Find By City 'Sunland' --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employeeRoot = query.from(Employee.class);
      query.select(employeeRoot)
           .where(criteriaBuilder.equal(createCitySubstringExpression(criteriaBuilder, employeeRoot), "Sunland"));
      entityManager.createQuery(query)
                   .getResultList()
                   .forEach(System.out::println);
      entityManager.close();
  }

  private static void findByDept() {
      System.out.println("-- Find By Dept 'Admin' ignore case --");
      EntityManager entityManager = entityManagerFactory.createEntityManager();
      CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Employee> query = criteriaBuilder.createQuery(Employee.class);
      Root<Employee> employeeRoot = query.from(Employee.class);
      query.select(employeeRoot)
           .where(criteriaBuilder.equal(
                   criteriaBuilder.lower(employeeRoot.get(Employee_.dept))
                   , "admin"));
      entityManager.createQuery(query)
                   .getResultList()
                   .forEach(System.out::println);
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", "John", "IT", "111 Round Drive, Sunland");
      Employee employee2 = Employee.create("Rose", "Burgess", "Admin", "2623  Elmwood Avenue, Scottsdale");
      Employee employee3 = Employee.create("Denise", "Clarkson", "admin", "4000 Johnny Lane, Mars Town");
      Employee employee4 = Employee.create("Mike", "Young", "IT", "323 Tik Tok Road, Hiland");
      Employee employee5 = Employee.create("Linda", "McDonald", "Sales", "2000 Wet Road, Sunland");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.persist(employee5);
      em.getTransaction().commit();

      System.out.println("-- Employees persisted --");
      Query query = em.createQuery(
              "SELECT e FROM Employee e");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }
}
-- Employees persisted --
Employee{id=1, firstName='Diana', lastName='John', dept='IT', address='111 Round Drive, Sunland'}
Employee{id=2, firstName='Rose', lastName='Burgess', dept='Admin', address='2623 Elmwood Avenue, Scottsdale'}
Employee{id=3, firstName='Denise', lastName='Clarkson', dept='admin', address='4000 Johnny Lane, Mars Town'}
Employee{id=4, firstName='Mike', lastName='Young', dept='IT', address='323 Tik Tok Road, Hiland'}
Employee{id=5, firstName='Linda', lastName='McDonald', dept='Sales', address='2000 Wet Road, Sunland'}
-- Employees full names --
Diana John
Rose Burgess
Denise Clarkson
Mike Young
Linda McDonald
-- Find employee having the longest full name --
Employee{id=3, firstName='Denise', lastName='Clarkson', dept='admin', address='4000 Johnny Lane, Mars Town'}
-- All Cities --
Hiland
Mars Town
Scottsdale
Sunland
-- Find By City 'Sunland' --
Employee{id=1, firstName='Diana', lastName='John', dept='IT', address='111 Round Drive, Sunland'}
Employee{id=5, firstName='Linda', lastName='McDonald', dept='Sales', address='2000 Wet Road, Sunland'}
-- Find By Dept 'Admin' ignore case --
Employee{id=2, firstName='Rose', lastName='Burgess', dept='Admin', address='2623 Elmwood Avenue, Scottsdale'}
Employee{id=3, firstName='Denise', lastName='Clarkson', dept='admin', address='4000 Johnny Lane, Mars Town'}

Example Project

Dependencies and Technologies Used:

  • hibernate-core 5.4.1.Final: Hibernate's core ORM functionality.
    Implements javax.persistence:javax.persistence-api version 2.2
  • hibernate-jpamodelgen 5.4.1.Final: Annotation Processor to generate JPA 2 static metamodel classes.
  • h2 1.4.198: H2 Database Engine.
  • JDK 1.8
  • Maven 3.5.4

CriteriaBuilder String manipulation API Select All Download
  • jpa-criteria-api-string-operations
    • src
      • main
        • java
          • com
            • logicbig
              • example
                • ExampleMain.java
          • resources
            • META-INF

    See Also