CODEWITHSUNDEEP
javajpajdbc
Raam
Raam

Reputation: 29

JPA : Is there any way to run a simple SELECT statement that only access a few columns?

I'm new to JPA so forgive me if my question seems silly.

We have used JPA in our project. I see that every entity object has a direct mapping with a table and each row in the table is an object of that entity type.

But, suppose I only want to access one or two columns of a table, how do i go about doing it ? The reason I'm asking is because of the task i have in hand.

There are two tables. The first table has everything set up with JPA so that each row can be cast into an object type. The first table has a column that is referenced in the second table i.e. say, table A has column CLOTH_ID and Table B has columns CLOTH_ID and CLOTH_DESCRIPTION. CLOTH_ID is used in both Table A and B; But B has the CLOTH_DESCRIPTION columns which corresponds to CLOTH_ID.

I'm displaying Table A in my webpage but I also need to display : CLOTH_DESCRIPTION in my webpage. Is there a JPA oriented way to do this or Am i better off using regular JDBC to extract the CLOTH DESCRIPTION values ?

Upvotes: 1

Views: 551

Answers (1)

Tomasz Nurkiewicz
Tomasz Nurkiewicz

Reputation: 340953

I assume you have the following setup:

@Entity
@Table(name="A")
class A {

    @ManyToOne
    @JoinColumn(name="CLOTH_ID")
    private B cloth;

    //...

}

@Entity
@Table(name="B")
class B {
    @Id
    @Column(name="CLOTH_ID")
    private int id;

    @Column(name="CLOTH_DESCRIPTION")
    private String description;

    //...
}

If you don't... you're doing it wrong (i.e. it is not idiomatic JPA usage). You have the following options:

Simply fetch A

In this case @ManyToOne relationship will be fetched eagerly by default as well. Then simply call in Java:

a.getCloth().getDescription()

Prefer this approach as it is the simplest and most idiomatic unless the number of columns in B is huge.

Use JPA query with custom columns:

SELECT a, a.b.description
FROM A a
WHERE a.id = :id

In this case the query returns List<Object[]>, where Object[] actually contains two elements: A and String.

Same as above but with custom DTO:

class Adto {

    private final A a;
    private final String description;

    public Adto(A a, String description) {
        this.a = a;
        this.description = description;
    }

}

And slightly modified query:

SELECT new Adto(a, a.b.description)
FROM A a
WHERE a.id = :id

Upvotes: 4

Related Questions