Home > Articles > Data > MySQL

End-to-End JPA Collections with MySQL, Part 2

  • Print
  • + Share This
Having difficulty modifying your JPA collection code? Do you think stored procedures are very complicated? Stephen Morris provides simple, end-to-end working code illustrating how to modify JPA collection code. Also, the area of MySQL stored procedures is looked at, using some simple examples in this the concluding part of a two-part series.
Like this article? We recommend

Like this article? We recommend

As programmers, we have to try to keep up to date as technology changes. The area of databases in general and object relational mapping (ORM) in particular are good examples of such areas. Indeed, ORM underpins many of the most popular frameworks, such as Spring, EJB3, etc.

I've often thought that the greatest skill in programming lies not in writing new code but in modifying existing or legacy code. Many of us prefer to write new code rather than delve into old code. Very often, the author of the legacy code may have left the organization or is too busy to help, or has even forgotten how the code works!

I recall one case where a departed contractor had written some extremely complex code that was failing. After sifting through many pages of source code, I noticed that there was a comment indicating that most of the code had been downloaded from a forum by the contractor and that he was at a loss to understand it. Not something to inspire confidence as a large chunk of code had to be excised and rewritten to fix the original problem.

Regardless of the ownership of the code, in many cases, the underlying business is generally built on legacy code. So, learning to change code may in fact be a more critical skill than writing greenfield code.

In this, the second of a two-part article series, I'll be demonstrating how to extend an existing (or legacy) collection-based JPA model. The model used comes from Part 1, where we built a simple JPA unidirectional one-to-many mapping. To conclude Part 2, I'll also look at an important server-side technology: stored procedures in the context of MySQL.

A One-to-Many Unidirectional Collection

In Part 1, you saw an example of a simple entity that contains a collection as illustrated in Listing 1.

Listing 1—A Department Entity Containing a Collection of Persons

@Table(name = "DEPARTMENT")
        query="select OBJECT(d) from Department d where d.name = :name")
public class Department implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "DEPT_ID")
    private Long id;
    @Column(name = "name", unique=true)
    private String name;
    private List<Person> persons = new ArrayList<Person>();
    public Department() {
    public Department(String name) {
        this.name = name;
    public Long getId() {
        return id;
    public String getName() {
        return name;
    public void setName(String name) {
        this.name = name;
    @OneToMany(cascade=CascadeType.ALL, mappedBy="department")
    public List<Person> getPersons() {
        return persons;
    public void setPersons(List<Person> persons) {
        this.persons = persons;
    public String toString() {
        return "Department [id=" + id + ", name=" + name + "]";

You can see from the line

private List<Person> persons = new ArrayList<Person>();

that the Listing 1 Department entity contains a collection of class Person. This is coded as a one-to-many unidirectional relationship. Nothing too complicated there. Indeed, the magic of annotations provides extraordinary power in transforming a piece of Java code into a rich and complex piece of database modelling and persistence logic.

Let's now imagine that a new business requirement arises and you want to extend the entity relationship in Listing 1—e.g., suppose we wanted to change this from a unidirectional relationship into a bidirectional one. It's not too hard to do this—in fact, just three changes are needed to the following:

  • SQL code
  • Person code
  • Adding a person to a department code

Let's start with the SQL code because it's a relatively simple change. Listing 2 illustrates the modified SQL code.

Listing 2—A Change to the PERSON Table DDL

CREATE TABLE `quickstart`.`PERSON` (
  `firstName` VARCHAR(45) NOT NULL,
  `lastName` VARCHAR(45) NOT NULL,
  `friends` VARCHAR(45) NOT NULL,
  `DEPARTMENT_DEPT_ID` int unsigned,

Can you see the change to the SQL? Well, I've simply added a new column called DEPARTMENT_DEPT_ID, which is a foreign key on the DEPT_ID from the DEPARTMENT table. This establishes a relationship between the rows in the PERSON table and the rows in the DEPARTMENT table.

The other required change is the addition of a new annotated @ManyToOne Department field to the Person Java class, as illustrated in Listing 3.

Listing 3—Modifying the Person Class

    private String friends;
    Department department;
    public Person() {

Each instance of the Person class must now maintain a Department instance field, and the multiplicity of this relationship is ManyToOne—i.e., there are potentially zero, one, or many persons in one department.

A useful thing to remember about JPA mapping is that the annotations should always make sense—i.e., in this case you have potentially many persons in a given department. This is a simple litmus test for your mapping code.

One other required change to the Person class is a setter and getter method for the new field:

    public Department getDepartment() {
        return department;
    public void setDepartment(Department department) {
        this.department = department;

So, how do we test this relationship?

  • + Share This
  • 🔖 Save To Your Account