What is the simplest way to assign an ID to a result from a JPA native query?

Question

I have a complex SQL query that I'm implementing as a native query in JPA. The entity manager is automatically converting each row of my result set into a Person object, resulting in List<Person>

String sql = "select name, address, date_of_birth from ...";
Query q = entityManager.createNativeQuery(sql, Student.class);
List<Student> results = q.getResultList();
return results;

The entityManager demands that the Student bean is annotated with @Entity, and this requires an @Id. The API also requires that all fields are matched. Unfortunately, my select clause pulls from various sources, so there isn't really an Id. In fact, I'd like to later have an id generated when I call StudentRepostiory.save(student).

I'm in a catch 22 situation. I need an id for the object to be generated, but I don't have an id as yet.

What should I do?

• I've tried making the ID null. It makes all results null.
• I've tried making the ID zero. It makes all results the same.
• I've tried adding a Generation Strategy annotation. Didn't do anything.

For your reference

Student.java

@Entity // This forces me to have an Id.
class Student {

    @Id Long id  // I'm forced to include this, but my query doesn't have one

    String name;
    String dateOfBirth;
    String address;

    // Getters and Setters

}

Answer 1

You can use @SqlResultSetMapping and @ConstructorResult to map the results of your native sql query to a POJO/bean, without requiring it to have an id.

Note: @ConstructorResult is only available in JPA 2.1.

First, you need to have a constructor in your Student entity that takes 3 parameters: name, dateOfBirth and address. Since this is an entity, it requires you to have a default no-arg constructor, so you need to declare that too. So you have 2 constructors, one no-arg and another with 3 args.

Now you declare your SqlResultSetMapping using @ConstructorResult:

@SqlResultSetMapping(name="StudentMapping",
    classes={
        @ConstructorResult(targetClass=Student.class,
            columns={
                @ColumnResult(name="name", type=String.class),
                @ColumnResult(name="address", type=String.class),
                @ColumnResult(name="date_of_birth", type=String.class)
            })
    }
)
@Entity
public class Student {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private int id;

    private String String name;
    private String address;
    private String dateOfBirth;

    public Student () {}

    public Student(String name, String address, String dateOfBirth) {
        this.name = name;
        this.address = address;
        this.dateOfBirth = dateOfBirth;
    }

    // Setters and Getters
}

Now when you call the createNativeQuery, you can pass your SQL string together with the name of the mapping you declared.

String sql = "select name, address, date_of_birth from ...";
Query q = entityManager.createNativeQuery(sql, "StudentMapping");
List results = q.getResultList();

Then, later on you can iterate the Student objects in your list and persist them. Since Student is an entity and objects in your list does not have a value for the id field yet, calling persist on these objects will work.

You'll see a sample of this implementation in my github repo.