JPQL provides following built-in string functions:
CONCAT(string, string, .... more strings) concatenates two or more strings into one string.
CONCAT('one', '-', 'two') returns one-two
SUBSTRING(string, startIndex, length) The second and third arguments of the SUBSTRING function denote the starting position and length of the substring to be returned. These arguments are integers. The third argument is optional, if it is not specified, the substring from the start position to the end of the string is returned.
SUBSTRING('world', 3) returns rld
SUBSTRING('world', 3, 2) returns rl
SUBSTRING('world', 1, 3) returns wor
TRIM([[LEADING|TRAILING|BOTH][char] FROM] string) The TRIM function trims the specified character from a string.
The keywords LEADING, TRAILING, BOTH are all optional, if not specified BOTH is assumed.
If the 'char' to be trimmed is not specified, it will be assumed to be space (or blank).
TRIM(' world ') returns world
TRIM(TRAILING '*' FROM 'world****') returns world
TRIM(BOTH '*' FROM '***world****') returns world
UPPER(string) It converts a string to upper case.
UPPER('world') returns WORLD
LOWER(string) It converts a string to lower case.
LOWER('World') returns world
LENGTH(string) It returns the length of a string as an integer.
LENGTH('world') returns 5
LENGTH('') returns 0
LOCATE(searchString, inputString, position) It returns the position of a given string within a string, starting the search at an optional specified position.
It returns the first position at which the string was found as an integer.
The first argument is the string to be located; the second argument is the string to be searched; the optional third argument is an integer that represents the string position at which the search is started (by default, the beginning of the string to be searched). The first position in a string is denoted by 1. If the string is not found, 0 is returned.
LOCATE('a', 'banana') returns 2
LOCATE('a', 'banana', 3) returns 4
LOCATE('z', 'banana') returns 0
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 functions
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();
}
}
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();
}
private static void findEmployeeFullName() {
System.out.println("-- Employees full names --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT CONCAT(e.firstName, ' ', e.lastName) FROM Employee e");
List<String> resultList = query.getResultList();
resultList.forEach(System.out::println);
em.close();
}
private static void findLongestNamedEmployee() {
System.out.println("-- Find employee having the longest full name name --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e FROM Employee e where "
+ "LENGTH(CONCAT(e.firstName, ' ', e.lastName)) = "
+ "(SELECT MAX(LENGTH(CONCAT(e2.firstName, ' ', e2.lastName))) FROM Employee e2)");
List<Employee> resultList = query.getResultList();
resultList.forEach(System.out::println);
em.close();
}
private static void findAllCities() {
System.out.println("-- All Cities --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT DISTINCT TRIM(SUBSTRING(e.address, LOCATE(',', e.address)+1)) as city"
+ " FROM Employee e order by city");
List<String> resultList = query.getResultList();
resultList.forEach(System.out::println);
em.close();
}
private static void findByCity() {
System.out.println("-- Find By City 'Sunland' --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e FROM Employee e WHERE TRIM(SUBSTRING(e.address, LOCATE(',', e.address)+1))='Sunland'");
List<Employee> resultList = query.getResultList();
resultList.forEach(System.out::println);
em.close();
}
private static void findByDept() {
System.out.println("-- Find By Dept 'Admin' --");
EntityManager em = entityManagerFactory.createEntityManager();
Query query = em.createQuery(
"SELECT e FROM Employee e WHERE LOWER(e.dept)='admin'");
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 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' --
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: - h2 1.4.197: H2 Database Engine.
- hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
Implements javax.persistence:javax.persistence-api version 2.1 - JDK 1.8
- Maven 3.3.9
|
|
