I create a query on a method that is an object and I don't know how how to make it return the object

Question

I have been searching and I haven't found an answer for my question.

I have the following code:

public ScriptInfo findByForeignKey(int id) {
    Session currentSession = entityManager.unwrap(Session.class);
        
    Query<ScriptInfo> theQuery =
        currentSession.createQuery("Select *
        from SCRIPT_INF AND SPRINT
        where FK_ID_SPRINT=:idSprint
        AND FK_ID_SPRINT=SPRINT.ID_SPRINT");
        
    theQuery.setParameter("idSprint", id);
        
}

I don't know how to put the return so it returns the object ScriptInfo filtered.

Answer 1

Query offers various methods to obtain the result of a query:

• getResultList()
• getFirstResult()
• getSingleResult()

The correct one depends on what exactly you expect and want to return. Their respective behaviour is described in the JavaDoc of Query. For them to properly work you need to provide information which class you expect the query to return, by adding it as an argument to createQuery

Your final code will look somewhat similar to this:

public ScriptInfo findByForeignKey(int id) {
        
    Query<ScriptInfo> theQuery = entityManager.createQuery("select si from ScriptInfo si where si.sprint.id = :idSprint", ScriptInfo.class);
        
    theQuery.setParameter("idSprint", id);   

    return theQuery.getFirstResult();     
}

Note 1:

You use createQuery which expects an HQL or JPQL query, but what you pass in seems to be SQL. This won't work. Use either createSQLQuery() or use JPQL instead. I recommend the later since I think you should only fall back to SQL when you can't express your query with JPQL. Feel free to create a new question when you need assistance with the conversion. Just make sure to include your domain model.

Note 2:

You don't need to unwrap the Session from the EntityManager. All the functionality you use from the Session is also available in the EntityManager. Only createSQLQuery is named createNativeQuery.