CODEWITHSUNDEEP
javahibernatepostgresqljpajpql
AKIWEB
AKIWEB

Reputation: 19612

Join JPA OneToOne relationship on Primary key columns

I have two tables - Table one and Table two as shown below -

Table two:

    model    | customer_capacity| total

    samsung  | 300              | 20000

where model is a primary key of table two.

Table one:

    model     | count1 | addition | stuff

    samsung   | 20     | 34       | 2

where model is a primary key of table one

Below are the classes for each entity (tables) that I have created -

//Table one:-

    @Entity
    @Table(name = "one")
    @NamedQueries({
            @NamedQuery(name = "getModelData",
                    query = "SELECT a.count1, a.addition, d.customerCapacity  FROM one a  JOIN FETCH two d where d.model = a.model and a.model = :model")
    })

public class One {
    @Id
    @Column(name = "model")
    private String model;

    @OneToOne
    @JoinColumn(name = "model", insertable = false, updatable = false)
    private Two t0;


    @Column(name = "count1")
    private double count1;

    @Column(name = "addition")
    private double addition;

    // getters and setters
}

//Table two entity:

@Entity
@Table(name = "two")
public class Two {
    @Id
    @Column(name = "model")
    private String model;

    @Column(name = "customer_capacity")
    private int customerCapacity;

    @Column(name = "total")
    private String total;

    // getters and setters
}

Problem Statement:-

I wanted to write a query in JPA which should return me following data:

Select a.count1, a.addition, b.customer_capacity from one a, two b where a.model = b.model and a.model = 'samsung';

Output:

count1 | addition   | customer_capacity
20     |  34        | 300

But I am not sure how to make this work in JPA? I am trying above named query that I created in my above two classes but everytime it is giving me error - Here is the code I am using to extract the data - 

 // method to get the data
public One getModelData(final String model) throws Exception {

    EntityManager em = factory.getPEntityManager();
    try {

        final Query q = em.createNamedQuery("getModelData");
        q.setParameter("model", mdoel);
        model = (One) q.getSingleResult();

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        factory.closeEntityManager(em);
    }
    return model;
}

And the exception I am getting is - 

java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to com.hello.jpa.One

How should I write such join query using JPQL in Java?

Upvotes: 0

Views: 599

Answers (2)

Nishat
Nishat

Reputation: 921

select a.count1, a.addition, a.t0.customerCapacity from One a where a.model = 'samsung';

And you will not get Object of One by simply,It will return array of Object model = (One) q.getSingleResult();

To get object of One,you will need to write,Proper constructor in One class having params as above three fields and query will change into

select new One(a.count1, a.addition, a.t0.customerCapacity) from One a where a.model = 'samsung';

Upvotes: 0

KrisUMCS
KrisUMCS

Reputation: 34

If you work on NetBeans, you can check you query, just right-click on persistence.xml file, then 'Run JPQL Query' . There you can test your query quickly and choose good parameters.

Upvotes: 1

Related Questions