The annotation @OrderBy Specifies the ordering of the elements of a collection valued association or element collection at the point when the association or collection is retrieved.
This annotation can be used with @ElementCollection or @OneToMany/@ManyToMany relationships.
When @OrderBy used with @ElementCollection
If the collection is of basic type, then ordering will be by the value of the basic objects. For example following will arrange phoneNumbers in their natural ordering:
@ElementCollection
@OrderBy
private List<String> phoneNumbers;
If the collection is of @Embeddable type, the dot (".") notation is used to refer to an attribute within the embedded attribute. For example following will arrange addresses by country names.
@ElementCollection
@OrderBy("city.country DESC")
private List<Address> addresses;
Where Address is defined as:
@Embeddable
public class Address {
...
@Embedded
private City city
....
}
ASC | DESC can be used to specify whether ordering is ascending or descending. Default is ASC.
When @OrderBy used with a relationship
@OrderBy only works with direct properties if used with a relationship (@OneToMany or @ManyToMany). For example:
@ManyToMany
@OrderBy("supervisor")
private List<Task> tasks;
Where Task entity is:
@Entity
public class Task {
....
@OneToOne
private Employee supervisor;
...
}
Dot (".") access doesn't work in case of relationships. Attempting to use a nested property e.g. @OrderBy("supervisor.name") will end up in a runtime exception.
If the ordering element is not specified for an entity association (i.e. the annotation is used without any value), ordering by the primary key of the associated entity is assumed. For example:
@ManyToMany
@OrderBy
private List<Task> tasks;
In above case tasks collection will be ordered by Task#id.
@OrderBy vs @OrderColumn
The order specified by @OrderBy is only applied during runtime when a query result is retrieved.
Whereas, the usage of @OrderColumn (last tutorials) results in a permanent ordering of the related data. In this case a dedicated database column is used to maintain the ordering.
Example
Entities
@Entity
public class Employee {
@Id
@GeneratedValue
private long id;
private String name;
@ElementCollection
@OrderBy//order by strings
private List<String> phoneNumbers;
@ManyToMany(cascade = CascadeType.ALL)
@OrderBy("supervisor")//order by task.supervisor (employee.id)
private List<Task> tasks;
@ElementCollection
@OrderBy("city.country DESC")//desc order by address.city.country
private List<Address> addresses;
.............
}
@Entity
public class Task {
@Id
@GeneratedValue
private long id;
private String name;
@OneToOne
private Employee supervisor;
.............
}
@Embeddable
public class Address {
private String street;
@Embedded
private City city;
.............
}
@Embeddable
public class City {
private String name;
private String country;
.............
}
Table mappings
Let's see our entities are mapped to what database tables:
public class TableMappingMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
nativeQuery("SHOW TABLES");
nativeQuery("SHOW COLUMNS FROM EMPLOYEE");
nativeQuery("SHOW COLUMNS FROM EMPLOYEE_PHONENUMBERS");
nativeQuery("SHOW COLUMNS FROM EMPLOYEE_TASK");
nativeQuery("SHOW COLUMNS FROM TASK");
nativeQuery("SHOW COLUMNS FROM EMPLOYEE_ADDRESSES");
} finally {
entityManagerFactory.close();
}
}
public static void nativeQuery(String s) {
EntityManager em = entityManagerFactory.createEntityManager();
System.out.printf("'%s'%n", s);
Query query = em.createNativeQuery(s);
List list = query.getResultList();
for (Object o : list) {
if (o instanceof Object[]) {
System.out.println(Arrays.toString((Object[]) o));
} else {
System.out.println(o);
}
}
em.close();
}
}'SHOW TABLES'
[EMPLOYEE, PUBLIC]
[EMPLOYEE_ADDRESSES, PUBLIC]
[EMPLOYEE_PHONENUMBERS, PUBLIC]
[EMPLOYEE_TASK, PUBLIC]
[TASK, PUBLIC]
'SHOW COLUMNS FROM EMPLOYEE'
[ID, BIGINT(19), NO, PRI, NULL]
[NAME, VARCHAR(255), YES, , NULL]
'SHOW COLUMNS FROM EMPLOYEE_PHONENUMBERS'
[EMPLOYEE_ID, BIGINT(19), NO, , NULL]
[PHONENUMBERS, VARCHAR(255), YES, , NULL]
'SHOW COLUMNS FROM EMPLOYEE_TASK'
[EMPLOYEE_ID, BIGINT(19), NO, , NULL]
[TASKS_ID, BIGINT(19), NO, , NULL]
'SHOW COLUMNS FROM TASK'
[ID, BIGINT(19), NO, PRI, NULL]
[NAME, VARCHAR(255), YES, , NULL]
[SUPERVISOR_ID, BIGINT(19), YES, , NULL]
'SHOW COLUMNS FROM EMPLOYEE_ADDRESSES'
[EMPLOYEE_ID, BIGINT(19), NO, , NULL]
[COUNTRY, VARCHAR(255), YES, , NULL]
[NAME, VARCHAR(255), YES, , NULL]
[STREET, VARCHAR(255), YES, , NULL]
H2 database SHOW statements
Retrieval of collections
public class ExampleMain {
private static EntityManagerFactory entityManagerFactory =
Persistence.createEntityManagerFactory("example-unit");
public static void main(String[] args) {
try {
persistEmployees();
findEmployees();
} finally {
entityManagerFactory.close();
}
}
public static void persistEmployees() {
Task task1 = Task.create("Development");
Task task2 = Task.create("Documentation");
Task task3 = Task.create("Designing");
Task task5 = Task.create("Refactoring");
Task task6 = Task.create("Testing");
Employee employee1 = Employee.create("Diana", Arrays.asList(task1, task2, task6),
Arrays.asList(Address.create("111 Round Drive", "Papineau", "Sundland"),
Address.create("2623 Elmwood Avenue", "Scottsdale", "Zwonga")),
"111-111-111", "666-666-666", "222-222-222");
Employee employee2 = Employee.create("Denise", Arrays.asList(task2, task3),
Arrays.asList(Address.create("23 Estate Avenue", "Papineau", "Ugrela"),
Address.create("367 Rose Route", "Scottsdale", "Mreyton")),
"444-444-444", "333-333-333");
Employee employee3 = Employee.create("Linda", Arrays.asList(task1, task5),
Arrays.asList(Address.create("345 Little Way", "Fries", "Tospus"),
Address.create("91 Vine Lane", "Binesville", "Oblijan")),
"555-555-555");
EntityManager em = entityManagerFactory.createEntityManager();
task1.setSupervisor(employee2);
task2.setSupervisor(employee1);
task3.setSupervisor(employee3);
task5.setSupervisor(employee1);
task6.setSupervisor(employee3);
em.getTransaction().begin();
em.persist(employee1);
em.persist(employee2);
em.persist(employee3);
em.getTransaction().commit();
}
private static void findEmployees() {
EntityManager em = entityManagerFactory.createEntityManager();
List<Employee> employees = em.createQuery("Select e from Employee e")
.getResultList();
for (Employee employee : employees) {
System.out.println("---");
System.out.println(employee);
System.out.println("-- Tasks --");
for (Task task : employee.getTasks()) {
System.out.println("task: " + task);
System.out.println("supervisor: " + task.getSupervisor());
}
System.out.println("-- addresses --");
employee.getAddresses().forEach(System.out::println);
}
}
}---
Employee{id=1, name='Diana', phoneNumbers=[111-111-111, 222-222-222, 666-666-666]}
-- Tasks --
task: Task{id=3, name='Documentation'}
supervisor: Employee{id=1, name='Diana', phoneNumbers=[111-111-111, 222-222-222, 666-666-666]}
task: Task{id=2, name='Development'}
supervisor: Employee{id=5, name='Denise', phoneNumbers=[333-333-333, 444-444-444]}
task: Task{id=4, name='Testing'}
supervisor: Employee{id=7, name='Linda', phoneNumbers=[555-555-555]}
-- addresses --
Address{street='2623 Elmwood Avenue', city=City{name='Scottsdale', country='Zwonga'}}
Address{street='111 Round Drive', city=City{name='Papineau', country='Sundland'}}
---
Employee{id=5, name='Denise', phoneNumbers=[333-333-333, 444-444-444]}
-- Tasks --
task: Task{id=3, name='Documentation'}
supervisor: Employee{id=1, name='Diana', phoneNumbers=[111-111-111, 222-222-222, 666-666-666]}
task: Task{id=6, name='Designing'}
supervisor: Employee{id=7, name='Linda', phoneNumbers=[555-555-555]}
-- addresses --
Address{street='23 Estate Avenue', city=City{name='Papineau', country='Ugrela'}}
Address{street='367 Rose Route', city=City{name='Scottsdale', country='Mreyton'}}
---
Employee{id=7, name='Linda', phoneNumbers=[555-555-555]}
-- Tasks --
task: Task{id=8, name='Refactoring'}
supervisor: Employee{id=1, name='Diana', phoneNumbers=[111-111-111, 222-222-222, 666-666-666]}
task: Task{id=2, name='Development'}
supervisor: Employee{id=5, name='Denise', phoneNumbers=[333-333-333, 444-444-444]}
-- addresses --
Address{street='345 Little Way', city=City{name='Fries', country='Tospus'}}
Address{street='91 Vine Lane', city=City{name='Binesville', country='Oblijan'}}
As seen above:
Each employee's phoneNumbers collection elements are arranged in their natural ordering.
Each employee's tasks collection elements are arranged by supervisor's ids.
Each employee's addresses collection elements are arranged in descending order by the country names.
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
|
|
