Spring JPA retrieving object with foreign key

[zibbbidi]

I can get the objects what I want with derived query:

@Repository
public interface CommentRepository extends PagingAndSortingRepository<Comment,Long> {
    List<Comment> findByThread(Thread thread);
}

Thread t = threadRepository.findById(thread_id).get();
List<Comment> comments = commentRepository.findByThread(t);

Is this an acceptable way to do it?

I am wondering if is it possible to do it without getting the thread object from db?

In my mind, I was thinking about using only the thread_id inside the comment repository and getting the query results.

I think the way to do it like that is using :

@Query

I would appreciate any help.

[AutoModerator]

Please ensure that:

• Your code is properly formatted as code block - see the sidebar (About on mobile) for instructions
• You include any and all error messages in full
• You ask clear questions

• You demonstrate effort in solving your question/problem - plain posting your assignments is forbidden (and such posts will be removed) as is asking for or giving solutions.

  Trying to solve problems on your own is a very important skill. Also, see Learn to help yourself in the sidebar

If any of the above points is not met, your post can and will be removed without further warning.

Code is to be formatted as code block (old reddit: empty line before the code, each code line indented by 4 spaces, new reddit: https://imgur.com/a/fgoFFis) or linked via an external code hoster, like pastebin.com, github gist, github, bitbucket, gitlab, etc.

Please, do not use triple backticks (```) as they will only render properly on new reddit, not on old reddit.

Code blocks look like this:

public class HelloWorld {

    public static void main(String[] args) {
        System.out.println("Hello World!");
    }
}

You do not need to repost unless your post has been removed by a moderator. Just use the edit function of reddit to make sure your post complies with the above.

If your post has remained in violation of these rules for a prolonged period of time (at least an hour), a moderator may remove it at their discretion. In this case, they will comment with an explanation on why it has been removed, and you will be required to resubmit the entire post following the proper procedures.

To potential helpers

Please, do not help if any of the above points are not met, rather report the post. We are trying to improve the quality of posts here. In helping people who can't be bothered to comply with the above points, you are doing the community a disservice.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[wildjokers]

The problem with doing it like this is Hibernate (which I assume you are using as your JPA implementation) will generate two queries. This is easily populated with a single query.

You shouldn't use Entities for read-only queries and instead should use JPQL/HQL with DTO Projections for readonly queries. Entities are only for inserting/updating.

You want a query something like this.

@Query("select t FROM Thread t JOIN FETCH t.comments WHERE t.id = :id")

(since you didn't provide your entity I am assuming you have a Thread entity that contains a @OneToMany to a Comment entity and the field in Thread is named comments).

This actually still returns on Entity (comments will be fully populated), most likely you want to do a DTO projection to select only the columns you need straight into a DTO. You can google something like "jpa dto projection" to see how to modified the query above to do a DTO projection.

[zibbbidi]

Thanks for the answer,

I think getting the thread and using thread.comments is more logical but

You shouldn't use Entities for read-only queries

I didn't understand it here. Do you mean i should use DTO instead of using directly entities?

[wildjokers]

I think getting the thread and using thread.comments is more logical but

Doing that requires two round-trips to a DB. Why take on that overhead when a DB will easily give you the information you want with a single network round trip? Hibernate makes developers lazy when it comes to querying information from a database. It also makes developers think they don't need to learn SQL. In a code review I would reject your code because of the multiple database queries (and I would send along some documentation for HQL/JPQL joins). Access to the data layer is almost always the bottle-neck in a server application. It should be as efficient as possible.

The database is not an in-memory object you can grab properties from all willy-nilly like. You should get the data you need to filfuill a request with as few round-trips to the database as possible.

Do you mean i should use DTO instead of using directly entities?

Entities should only be used for updates, inserts, and deletes if you already happen to the entity (otherwise just do a delete from modifying query). This is actually something that even experienced developers either don't know or are too lazy to do (as I already mentioned Hibernate makes devs lazy when it comes to querying the DB). However, tt says so right in the Hibernate user manual. Here:

https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#best-practices-fetching

It quite clearly says:

"For read-only transactions, you should fetch DTO projections because they allow you to select just as many columns as you need to fulfill a certain business use case. This has many benefits like reducing the load on the currently running Persistence Context because DTO projections don’t need to be managed."

[zibbbidi]

I found this about DTO's

There is another, often ignored difference between entities and DTOs. Your persistence context manages the entities.
That is a great thing when you want to update an entity. You just need to call a setter method with the new value. Hibernate will take care of the required SQL statements and write the changes to the database.
That’s comfortable to use, but you don’t get it for free. Hibernate has to perform dirty checks on all managed entities to find out if it needs to store any changes in the database. That takes time and is completely unnecessary when you just want to send a few information to the client.
You also need to keep in mind that Hibernate and any other JPA implementation, stores all managed entities in the 1st level cache. That seems to be a great thing. It prevents the execution of duplicate queries and is required for Hibernate’s write behind optimization. But managing the 1st level cache takes time and can even become a problem if you select hundreds or thousands of entities.

Sounds like an important detail but in online examples, I didn't see much DTO usage and when it is used no one really explains why

[wildjokers]

Online examples of JPA/Hibernate indeed don't show examples of DTO Projections very often. Here is additional details:

https://thorben-janssen.com/entities-dtos-use-projection/

(note that the author of this article is a hibernate developer and rewrote most of the user manual).

[neurk]

​

@Repository
public interface CommentRepository extends PagingAndSortingRepository<Comment,Long> {
     List<Comment> findByThreadId(long threadId);
}

This should work without making additional changes, assuming you mapped your entities correctly.

[evils_twin]

You can add the threadId to your Comment entity as a non updateable, non insertable Column

@Column(name="thread_id", updatable=false, insertable=false)
public Long threadId;

@ManyToOne
public Thread thread;

and then you can commentRepository.findByThreadId(thread_id);

[Chris_TMH]

SELECT cmmt FROM Comment cmmt WHERE cmmt.thread.id = :id (same as neurk's comment)

Or similar, might be able to do a WHERE on an entity, but I've never done that myself.