CODEWITHSUNDEEP
javajpa
user1227591
user1227591

Reputation: 96

Named Query Possibility

I have a fields of customerName, MembershipNumber, nationality and some other field in my customer table and if i get one value from the above three. I need to write only one named query to get the value from the Customer table from the value i got. Is there any possibility to do that by named query without use of normal query in jpa?...

 StringBuilder s=new StringBuilder();
 s.append("select c from customerdetail c where )
 if(customerName!=null)
 {
   s.append(" c.customerName = :customerName")
 }
else if(memberShipNumber!=null)
 {
   s.append(" c.memberShipNumber = :memberShipNumber")
 }
else if(nationality!=null)
 {
   s.append(" nationality = :nationality)
 }

Here i use the same table with three conditions. So is there any possiblity to write only one named query or any other static query to satisfy all the three conditions in jpa?

Upvotes: 0

Views: 1139

Answers (3)

Nayan Wadekar
Nayan Wadekar

Reputation: 11602

Named queries are static & their scope is persistence context, they can't be altered at runtime.

Below is the sample for adding parameter based on condition using Criteria API.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<CustomerDetail> cq = cb.createQuery(CustomerDetail.class);
Metamodel m = em.getMetamodel();
EntityType<CustomerDetail> CustomerDetail_ = m.entity(CustomerDetail.class);
Root<CustomerDetail> detail = cq.from(CustomerDetail.class);

if(customerName != null)
cq.where(cb.equal(detail.get(CustomerDetail_.customerName), customerName));

if(memberShipNumber != null)
cq.where(cb.equal(detail.get(CustomerDetail_.memberShipNumber), memberShipNumber));

if(nationality != null)
cq.where(cb.equal(detail.get(CustomerDetail_.nationality), nationality));

cq.select(detail);
TypedQuery<CustomerDetail> q = em.createQuery(cq);
List<CustomerDetail> customerList= q.getResultList();

Else, you can go with building a query with string by appending conditions, instead of named query.

Upvotes: 1

Teg
Teg

Reputation: 1302

You can use projection with NamedQuery, this example will obtain a single customerName field if MembershipNumber is unique or a List with every customerName that matches the where condition:

@Entity
@NamedQuery(name="getCustomerName", query="SELECT c.customerName FROM Customer c WHERE c.membershipNumber = :memNum")
public class Customer {
...
}

Then you can call it with: (em is your EntityManager)

String customerName = em.createNamedQuery("getCustomerName")
.setParameter("memNum", myMembershipNumber)
.getSingleResult();

Upvotes: 0

d1e
d1e

Reputation: 6442

Try reading ObjectDB's manual on JPA Queries. It provides information on selecting JPA entities and different variations with its custom fields. It has query examples expressed as in JPQL so with use of Criteria. And yes, you can define namedQuery using JPQL and later use-re-use it.

Upvotes: 1

Related Questions