The Many-to-One link is the foundation of persistent relations. It links a source table to a destination with a database REFERENCES column. Many-to-One adds two capabilities: SQL extensions for links and direct lookup of target beans through field references.

Demo

Files in this tutorial

Model: Database and Annotated Classes

Database Schema

A many-to-one relation links one table to another. In this example, each student entry links to the student's house. The database schema might look like:

CREATE TABLE house (
  id BIGINT PRIMARY KEY auto_increment,

  name VARCHAR(250),
)

CREATE TABLE student (
  id BIGINT PRIMARY KEY auto_increment,

  name VARCHAR(250),

  house BIGINT REFERENCES house(id)
)

The House bean has two fields, id and name, to model the house columns. House annotates getId with @Id to mark it as a primary key. It annotates getName with @Basic to mark it as a data column.

@Entity
public class House {
  @Id@Column(name="id")
  public long getId()

  @Basic
  public String getName()
}

The Student bean adds a many-to-one field, house to the id and name columns. It marks the getHouse getter with @ManyToOne to mark it as a many-to-one column.

@Entity
public class Student {
  @Id@Column(name="id")
  public long getId()

  @Basic
  public String getName()

  @ManyToOne@JoinColumn(name="house")
  public House getHouse()
}

@ManyToOne

@ManyToOne marks the field as a many-to-one field. The @ManyToOne annotation can also specify the target bean. By default, the field's type determines the target bean.

@JoinColumn

@JoinColumn specifies the SQL column name and the target column for a many-to-one field.

The default column name is the name of the many-to-one field.

The default target column is the primary key of the target table.

Client Servlet

The many-to-one relation provides two capabilities to the client:

1. Extended SQL relation capabilities
2. A direct lookup to a persistent bean through the field.

Query Extensions

The first relation capability extends SQL with relation paths using the '.' operator like Java's field reference. If s is a student, then s.house is the student's house and s.house.name is the name of the student's house. Resin translates the extended SQL to plain SQL automatically.

The following example uses the extended SQL to return all students in a given house.

private void doService(PrintWriter out)
  throws java.io.IOException
{
  String sql = "SELECT s FROM Student s WHERE s.house.name=?1";
    
  Query houseStudents = _entityManager.createQuery(sql);
  houseStudents.setParameter(1, "Gryffindor");

  students = houseStudent.getResultList();

  out.println("<h3>Gryffindor Students</h3>");

  for (int i = 0; i < students.size(); i++) {
    Student student = (Student) students.get(i);

    out.println(student.getName() + "<br>");
  }
}

Java References

The second relation capability returns a live, persistent bean through the getHouse() method. Amber will perform any necessary database lookup.

The example queries all students and prints their names and house names.

private void doService(PrintWriter out)
  throws java.io.IOException
{
  Query allStudent = _entityManager.createQuery("SELECT o FROM Student o");
    
  List students = allStudent.getResultList();

  for (int i = 0; i < students.size(); i++) {
    Student student = (Student) students.get(i);

    out.println(student.getName() + " lives in " +
                student.getHouse().getName() + "<br>");
  }
}

Demo