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 ProjectDependencies 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
|
|
