CODEWITHSUNDEEP
javamysqlsqljpaorm
user3025978
user3025978

Reputation: 469

JPA join tables through query

I have strange problem. I want to create database like this: One student can has a lot of subjects. Student has one evaluation for one subject. So I have class student with ID, Name, Surname and A_I id, like:

@Id
@GeneratedValue
long id_student;

In subject class I have:

    @Id
    @GeneratedValue
    long id_subject;
    String name;
double graduate

I have third class, named StudentWithGraduate:

@Id
@GeneratedValue
long id;
double evaluation;
@OneToOne
Student student;
@OneToOne
Subject subject;

I think I could do it better, but I don't know how. But it isn't a main problem. This, what I write up is working, but I want to do some joins in query like:

Vector<Object[]>  v = (Vector<Object[]>) em.createQuery(
"select p.name, o.graduate from Student s 
left join StudentWithGraduate o on s.id_student=o.student
left join Subject p on p.id_subject=o.subject where
s.surname='"+name+"'").getResultList();

And it throw an error: Exception Description: Object comparisons can only be used with OneToOneMappings. Other mapping comparisons must be done through query keys or direct attribute level comparisons.

How can I change this DB scheme or change that query?

Sorry for my english.

PS. When I was doing research I found @joinTables, but I don't know how to use it..

Upvotes: 0

Views: 2449

Answers (1)

V G
V G

Reputation: 19002

You need a short introduction in JPQL, but I will try to quickly explain some missing parts:

In a JPQL query you do not write which are the JOIN conditions (i.e the ON expresions), and instead you navigate through your Entity Graph, beginning with an entity (below I begin with the StudentWithGraduate entity):

SELECT p.name, o.graduate FROM StudentWithGraduate o
    LEFT JOIN o.student s
    LEFT JOIN o.subject p
    WHERE s.surname=:name

The ":name" is called a named parameter, which helps you agains SQL injections. In order to set a value for it, you write the following:

Query query = em.createQuery(aboveQuery);
query.setParameter("name", parameterValue);
//....the rest of parameters + getResultList();

Upvotes: 3

Related Questions