How to make a Jpql query work in Spring Jpa Repository with Optional return?

Question

I am using Jpql queries in my JpaRepository. The problem is that every time I call a method with an optional return I get this error:

java.lang.ClassCastException: class [Ljava.lang.Object; cannot be cast to class com.netpos.pagvendas.campaignuser.entity.CampaignUser ([Ljava.lang.Object; is in module java.base of loader 'bootstrap'; com.netpos.pagvendas.campaignuser.entity.CampaignUser is in unnamed module of loader org.springframework.boot.devtools.restart.classloader.RestartClassLoader @1c069e07)

This is my Repository class, and the method that I am trying to use is :

findByCampaignCodUniqueCampaignAndCodTenant(String codUniqueCampaign, String codTenant)

@Repository
public interface CampaignUserRepository extends JpaRepository<CampaignUser, CampaignUserPk> {

    @Query("SELECT campaignUser, campaignUser.campaign from CampaignUser campaignUser " +
            "where campaignUser.codTenant = :codTenant " +
            "and campaignUser.flgFinished = false " +
            "and :now >= campaignUser.campaign.datStart and :now <= campaignUser.campaign.datEnd " +
            "order by campaignUser.datCreation ASC")
    List<CampaignUser> findActiveCampaign(String codTenant, ZonedDateTime now);

    @Query("SELECT campaignUser, campaignUser.campaign from CampaignUser campaignUser " +
            "where campaignUser.idtCampaign = :idtCampaign ")
    List<CampaignUser> findByIdtCampaign(long idtCampaign);

    @Query("SELECT campaignUser, campaignUser.campaign from CampaignUser campaignUser " +
            "where campaignUser.campaign.codUniqueCampaign = :codUniqueCampaign " +
            "and campaignUser.codTenant = :codTenant")
    Optional<CampaignUser> findByCampaignCodUniqueCampaignAndCodTenant(String codUniqueCampaign, String codTenant);
}

Is it possible to use Optional with Jpql queries in JpaRepository?

Answer 1

Why are there two objects in the selection? To load CampaignUser with Campaign by a single query you have to start the query this way

select campaignUser from CampaignUser campaignUser join fetch campaignUser.campaign campaign

or even

from CampaignUser campaignUser join fetch campaignUser.campaign campaign

 @Query("SELECT campaignUser from CampaignUser campaignUser join fetch campaignUser.campaign campaign " +
                "where campaign.codUniqueCampaign = :codUniqueCampaign " +
                "and campaignUser.codTenant = :codTenant")
 Optional<CampaignUser> findByCampaignCodUniqueCampaignAndCodTenant(String codUniqueCampaign, String codTenant);