-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Search for multiple conditions
PostPosted: Thu Jul 28, 2011 9:30 am 
Newbie

Joined: Mon Jul 18, 2011 11:23 am
Posts: 4
Hi,

I am using JPA 2.0 with hibernate3. Database is SQL Server.

The relationship b/w my entities is depicted below -


Customer
Addressses [0...*]
Orders [0...*]
OrderItems [0..*]
OrderTags [0...*]


The entities and their relationships are mentioned in the code snippets below -

Code:

import java.io.Serializable;
import java.util.LinkedList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Version;
import org.hibernate.annotations.IndexColumn;
import org.hibernate.envers.Audited;

@Entity @Audited @org.hibernate.annotations.Entity(dynamicInsert=true,dynamicUpdate=true)
public class Customer implements Serializable{
   private static final long serialVersionUID = -4050433485259292947L;
   @Id @GeneratedValue
   private long customerId;
   public long getCustomerId() {
      return customerId;
   }
   public void setCustomerId(long customerId) {
      this.customerId = customerId;
   }
   public String getFirstName() {
      return firstName;
   }
   public void setFirstName(String firstName) {
      this.firstName = firstName;
   }
   public String getLastName() {
      return lastName;
   }
   public void setLastName(String lastName) {
      this.lastName = lastName;
   }
   private String firstName;
   private String lastName;
   @Version
   private long recordVersionNumber;
   public long getRecordVersionNumber() {
      return recordVersionNumber;
   }
   public void setRecordVersionNumber(long recordVersionNumber) {
      this.recordVersionNumber = recordVersionNumber;
   }
   
   @OneToMany(cascade = CascadeType.ALL, orphanRemoval= true)
   @JoinColumn(name = "customer_id")
   @IndexColumn(name = "indx", base = 0)
   private List<Order> orders;
   public List<Order> getOrders() {
      if(orders == null)
         orders = new LinkedList<Order>();
      return orders;
   }
   public void setOrders(List<Order> orders) {
      if(orders == null)
         orders = new LinkedList<Order>();   
      this.orders = orders;
   }
   
   @OneToMany(cascade = CascadeType.ALL, orphanRemoval= true)
   @JoinColumn(name = "customer_id")
   @IndexColumn(name = "indx", base = 0)
   private List<Address> addresses;
   public List<Address> getAddresses() {
      if(addresses == null)
         addresses = new LinkedList<Address>();
      return addresses;
   }
   public void setAddresses(List<Address> addresses) {
      if(addresses == null)
         addresses = new LinkedList<Address>();
      this.addresses = addresses;
   }

}

-----------------------------------------------------------------------------

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Version;
import org.hibernate.envers.Audited;
@Entity(name="Addresses") @Audited
public class Address implements Serializable{
   private static final long serialVersionUID = 4438802452890577501L;
   @Id @GeneratedValue
   private long addressId;
   public long getAddressId() {
      return addressId;
   }
   public void setAddressId(long addressId) {
      this.addressId = addressId;
   }
   public long getAppt() {
      return appt;
   }
   public void setAppt(long appt) {
      this.appt = appt;
   }
   public String getStreet() {
      return street;
   }
   public void setStreet(String street) {
      this.street = street;
   }
   public String getCity() {
      return city;
   }
   public void setCity(String city) {
      this.city = city;
   }
   public String getState() {
      return state;
   }
   public void setState(String state) {
      this.state = state;
   }
   
   private long appt;
   private String street;
   private String city;
   private String state;
   @Version
   private long recordVersionNumber;
   public long getRecordVersionNumber() {
      return recordVersionNumber;
   }
   public void setRecordVersionNumber(long recordVersionNumber) {
      this.recordVersionNumber = recordVersionNumber;
   }

}


---------------------------------------------------------------------

import java.io.Serializable;
import java.util.LinkedList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.Version;
import org.hibernate.annotations.IndexColumn;
import org.hibernate.annotations.Type;
import org.hibernate.envers.Audited;
import org.joda.time.DateTime;
@Entity (name="Orders") @Audited @org.hibernate.annotations.Entity(dynamicInsert=true,dynamicUpdate=true)
public class Order implements Serializable{
   private static final long serialVersionUID = -4823870377932200112L;
   @Id @GeneratedValue
   private long orderId;
   private Boolean hazmatOrder;
   @Type(type="org.joda.time.contrib.hibernate.PersistentDateTime")
   protected DateTime createdTimestamp;
   public DateTime getCreatedTimestamp() {
      return createdTimestamp;
   }
   public void setCreatedTimestamp(DateTime createdTimestamp) {
      this.createdTimestamp = createdTimestamp;
   }
   public Boolean getHazmatOrder() {
      return hazmatOrder;
   }
   public void setHazmatOrder(Boolean hazmatOrder) {
      this.hazmatOrder = hazmatOrder;
   }
   public long getOrderId() {
      return orderId;
   }
   public void setOrderId(long orderId) {
      this.orderId = orderId;
   }
   public String getOrderDesc() {
      return orderDesc;
   }
   public void setOrderDesc(String orderDesc) {
      this.orderDesc = orderDesc;
   }
   public long getRecordVersionNumber() {
      return recordVersionNumber;
   }
   public void setRecordVersionNumber(long recordVersionNumber) {
      this.recordVersionNumber = recordVersionNumber;
   }
   private String orderDesc;
   @Version
   private long recordVersionNumber;
   
   @OneToMany(cascade = CascadeType.ALL, orphanRemoval= true)
   @JoinColumn(name = "order_id")
   @IndexColumn(name = "indx", base = 0)
   private List<OrderItems> orderItems;
   public List<OrderItems> getOrderItems() {
      if(orderItems == null)
         orderItems = new LinkedList<OrderItems>();
      return orderItems;
   }
   public void setOrderItems(List<OrderItems> orderItems) {
      if(orderItems == null)
         orderItems = new LinkedList<OrderItems>();   
      this.orderItems = orderItems;
   }
   
   @OneToMany(cascade = CascadeType.ALL, orphanRemoval= true)
   @JoinColumn(name = "order_id")
   @IndexColumn(name = "indx", base = 0)
   private List<OrderTags> orderTags;
   public List<OrderTags> getOrderTags() {
      if(orderTags == null)
         orderTags = new LinkedList<OrderTags>();
      return orderTags;
   }
   public void setOrderTags(List<OrderTags> orderTags) {
      if(orderTags == null)
         orderTags = new LinkedList<OrderTags>();   
      this.orderTags = orderTags;
   }
   

}
-----------------------------------------------------------------

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Version;
import org.hibernate.envers.Audited;
@Entity (name="OrderItems") @Audited @org.hibernate.annotations.Entity(dynamicInsert=true,dynamicUpdate=true)
public class OrderItems implements Serializable{
   private static final long serialVersionUID = 5033388370324454714L;
   @Id @GeneratedValue
   private long orderItemId;
   public long getOrderItemId() {
      return orderItemId;
   }
   public void setOrderItemId(long orderItemId) {
      this.orderItemId = orderItemId;
   }
   
   public long getRecordVersionNumber() {
      return recordVersionNumber;
   }
   public void setRecordVersionNumber(long recordVersionNumber) {
      this.recordVersionNumber = recordVersionNumber;
   }
   private String orderItemDesc;
   public String getOrderItemDesc() {
      return orderItemDesc;
   }
   public void setOrderItemDesc(String orderItemDesc) {
      this.orderItemDesc = orderItemDesc;
   }
   public double getOrderItemPrice() {
      return orderItemPrice;
   }
   public void setOrderItemPrice(double orderItemPrice) {
      this.orderItemPrice = orderItemPrice;
   }
   private double orderItemPrice;
   @Version
   private long recordVersionNumber;
   

}
-----------------------------------------------------------------------


import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Version;
import org.hibernate.envers.Audited;
@Entity (name="OrderTags") @Audited @org.hibernate.annotations.Entity(dynamicInsert=true,dynamicUpdate=true)
public class OrderTags implements Serializable{
   private static final long serialVersionUID = 5033388370324454714L;
   @Id @GeneratedValue
   private long orderTagId;
   public long getOrderTagId() {
      return orderTagId;
   }
   public void setOrderTagId(long orderTagId) {
      this.orderTagId = orderTagId;
   }
   
   public long getRecordVersionNumber() {
      return recordVersionNumber;
   }
   public void setRecordVersionNumber(long recordVersionNumber) {
      this.recordVersionNumber = recordVersionNumber;
   }
   private String orderTagDesc;
   public String getOrderTagDesc() {
      return orderTagDesc;
   }
   public void setOrderTagDesc(String orderTagDesc) {
      this.orderTagDesc = orderTagDesc;
   }
   public double getOrderTagPrice() {
      return orderTagPrice;
   }
   public void setOrderTagPrice(double orderTagPrice) {
      this.orderTagPrice = orderTagPrice;
   }
   private double orderTagPrice;
   @Version
   private long recordVersionNumber;
   

}







To get all entities using single query, I am using below HQL -
Code:

StringBuffer queryStrBuff = new StringBuffer(" SELECT DISTINCT c FROM Customer c " +
                        " LEFT JOIN FETCH c.addresses a " +
                        " LEFT JOIN FETCH c.orders o " +
                        " LEFT JOIN FETCH o.orderItems oi " +
                        " LEFT JOIN FETCH o.orderTags ot "  +
                        "   ");




To execute the FIND operation, I will get a list of "FindCondition" objects as input.

Code:
public class FindCondition{

//Search fields for customer entity
private String firstName;
private String lastName;

//Search fields for order entity
private String orderDesc;
private String orderId;

//Search fields for orderItem entity
private String orderItemDesc;
private double orderItemPrice;

//Search fields for orderTag entity
private String orderTagDesc;
private double orderTagPrice;

}


For each "FindCondition" I need to retrieve the Customer entities with all related child entities
and send the combined list back as output.

None of the fields in "FindCondtion" are mandatory and any combination of fields can be used.

I think UNION clause is not supported in HQL.
Does it mean that I need to execute the query for each instance of "FindCondtion" and then combine the lists.

Is there any other way?

Actually the above scenario is a test scenario I am using. My actual requirement has more entities and
deeper relationships.

Regards,
Jacob


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.