CODEWITHSUNDEEP
javajpa
Redfield
Redfield

Reputation: 105

Select non-entities with JPA?

Is it possible with JPA to retrieve a instances of a non-entity classes with native queries?
I have a non-entity class that wraps two entities:

class Wrap{
  Entity1 ent1;
  Entity2 ent2
}
@Entity
class Entity1{
  ...
}
@Entity
class Entity2{
  ...
}

How can I do something like that? 

Query q = entityManager.createNativeQuery("native select here");
List<Wrap> list = q.getResultList();

Upvotes: 9

Views: 17797

Answers (4)

Anton Podolskyy
Anton Podolskyy

Reputation: 89

Here is way how i did it.

My entity with a lot of fields

@Entity
@Getter
@Setter
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "outcoming_transaction", schema = "ibank_schema", catalog = "ibank")
public class OutcomingTransaction {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", updatable = false)
    private Long id;

    @NotEmpty(message = "Sender name cant be empty")
    @Size(max = 255, message = "Max size for sender name is 255 characters")
    @Column(name = "sender_name", updatable = false)
    private String senderName;

    @NotEmpty(message = "Sender surname cant be empty")
    @Size(max = 255, message = "Max size for sender surname is 255 characters")
    @Column(name = "sender_surname", updatable = false)
    private String senderSurname;

    @NotEmpty(message = "Sender account number cant be empty")
    @Size(max = 20, message = "Max size for sender account number is 20 characters")
    @Column(name = "sender_account_number", updatable = false)
    private String senderAccountNumber;

    @NotNull(message = "Money amount cant be empty")
    @Column(name = "money_amount", updatable = false)
    private BigDecimal moneyAmount;

    @NotEmpty(message = "Currency code cant be empty")
    @Size(max = 3, message = "Max size for currency code is 3 characters")
    @Column(name = "currency_code", updatable = false)
    private String currencyCode;

    @NotEmpty(message = "Recipient account number cant be empty")
    @Size(max = 20, message = "Max size for recipient account number is 20 characters")
    @Column(name = "recipient_account_number", updatable = false)
    private String recipientAccountNumber;

    @NotEmpty(message = "Recipient name cant be empty")
    @Size(max = 255, message = "Max size for recipient name is 255 characters")
    @Column(name = "recipient_name", updatable = false)
    private String recipientName;

    @NotEmpty(message = "Recipient surname cant be empty")
    @Size(max = 255, message = "Max size for sender surname is 255 characters")
    @Column(name = "recipient_surname", updatable = false)
    private String recipientSurname;

    @ManyToOne
    @JoinColumn(name = "recipient_bank", referencedColumnName = "id")
    @Valid // todo 30.08.2023 Anton - check if this working fine
    @NotNull(message = "Recipient bank cant be empty")
    private BankData recipientBank;

    @Size(max = 255, message = "Max size for payment purpose is 255 characters")
    @Column(name = "payment_purpose", updatable = false)
    private String paymentPurpose;

    @NotNull(message = "Transaction fee have to be filled up")
    @Column(name = "transaction_fee", updatable = false)
    private BigDecimal transactionFee;

    @Column(name = "transaction_time", updatable = false)
    private Timestamp transactionTime;



    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        OutcomingTransaction that = (OutcomingTransaction) o;
        return id.equals(that.id);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }

    public String toString() {
        return transactionTime.toString() + ": from " + senderSurname + " " + senderName + " to " + recipientSurname + " " + recipientName;
    }
}

So I have tons of transactions and I need to get sum for each currency. Repository:

@Repository  
public interface OutcomingTransactionRepository extends JpaRepository<OutcomingTransaction, Long> {  
   
@Query("SELECT NEW ru.k2.ibank.model.dto.TransactionSummaryDTO(t.currencyCode, SUM(t.moneyAmount)) FROM OutcomingTransaction t " +  
"WHERE t.transactionTime BETWEEN :dateFrom AND :dateTill " +  
"GROUP BY t.currencyCode")  
List<TransactionSummaryDTO> getSumWithinTimeWindow(  
@Param("dateFrom") Timestamp dateFrom,  
@Param("dateTill") Timestamp dateTill);  
  
}

DTO

@Getter
@Setter
@NoArgsConstructor
public class TransactionSummaryDTO {

    public TransactionSummaryDTO(String currencyCode, BigDecimal totalAmount) {
        this.currencyCode = currencyCode;
        this.totalAmount = totalAmount;
    }

    private String currencyCode;
    private BigDecimal totalAmount;

}

Here is JSON reply:

[
    {
        "currencyCode": "CNY",
        "totalAmount": 15120.25
    },
    {
        "currencyCode": "EUR",
        "totalAmount": 17850.25
    },
    {
        "currencyCode": "RUB",
        "totalAmount": 1233.54
    },
    {
        "currencyCode": "USD",
        "totalAmount": 15437306.50
    },
    {
        "currencyCode": "GBP",
        "totalAmount": 123450.25
    }
]

Upvotes: 1

user7499251
user7499251

Reputation: 11

JPA native query without entity - especially with complex queries (recursive, multiple joins, etc.) ?

This worked for me, but it's hibernate specific :

import org.hibernate.transform.Transformers;

Query query = entityManagerFactory.createEntityManager().createNativeQuery(SQL);
// Transform the results to MAP <Key, Value>
query.unwrap(org.hibernate.SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
query.setParameter("myNamedParam", myParam);
List<Map<String, Object>> list = query.getResultList();
for (Map<String, Object> map : list) {
    System.out.println(map);
}

cf. https://www.programmerall.com/article/89371766511/

Upvotes: 0

Lakshmi Hari
Lakshmi Hari

Reputation: 5

I think I found the solution. There is a way to use the NEW keyword in constructing the query. What I did to resovle this issue :

    public List<ProductType> getProductByName(String productName) {
        String sqlQuery = "select DISTINCT **NEW** project1.ProductType(o.name, o.revision) from Lhproduct o where o.name = :prodname";
        Query qry = getEntityManager().**createQuery(sqlQuery);**
        qry.setParameter("prodname",productName);
        return qry.getResultList();
 }

The ProductType is a non-entity object, a simple plain object implementing Serialiabale. But you need to define the appropriate constructor.

Happy coding :-)

Thanks and Regards, Hari

Upvotes: -2

Pascal Thivent
Pascal Thivent

Reputation: 570525

Is it possible with JPA to retrieve a instances of a non-entity classes with native queries?

No. Native queries can return entities only (if you tell them to do so by passing the resultClass or a resultSetMapping to the createNativeQuery method; if you don't, you will get collections of raw data).

In JPQL, you can use constructor expressions (SELECT NEW...) whith a non-entity constructor. But this is not supported for native queries, you'll have to do it manually.

Upvotes: 18

Related Questions