Hi,
thanks four your answers!
dougrand wrote:
I don't believe Criteria can join multiple tables. As far as I can tell it is very useful for building queries in pieces for a single table. Why is using HQL a problem?
Criteria and simple joins are not the problem. An example can you find in my class ProductMain in the method simpleJoin().
The example below is not a real world scenario, only an example to demonstrate my problem. I have a Web application with a search form. The search consits of more than 20 items. The Criteria Api makes it very easy to combine the search elements. I like the Criteria API very much!
My problem is the use of criteria queries in combination join and aggregation of the second table.
If anybody could help me with this (
Method testB()), I would be very lucky (and give credits).
Regards,
Detlev
Code:
package eg;
import java.util.*;
import org.hibernate.*;
import org.hibernate.cfg.*;
import org.hibernate.criterion.*;
public class ProductMain
{
private SessionFactory sessionFactory;
private Session session;
public void configure() throws HibernateException
{
sessionFactory = new Configuration()
.addClass(ProductCategory.class)
.addClass(Item.class)
.buildSessionFactory();
session = sessionFactory.openSession();
}
/**
* lists all product categories and their product items
*/
private void testAll()
{
System.out.println("\nTEST All categories with items.");
List list = session.createCriteria(ProductCategory.class)
.list();
for (int i = 0; i < list.size(); i++)
{
ProductCategory procat = (ProductCategory) list.get(i);
System.out.println(procat.getName() + " (" + procat.getId() + ")" + ", last update: " + procat.getLastUpdate());
List items = procat.getItems();
for (Iterator iter = items.iterator(); iter.hasNext();)
{
Item element = (Item) iter.next();
System.out.println("==> " + element.getName() + " (" + element.getId() + "), discount: "
+ element.getDiscount() + ", last update: " + element.getLastUpdate());
}
}
}
private void simpleJoin()
{
System.out.println("\nCriteria Test: All Items belonging to the car group " +
"and with a 30% discount.");
List items = session.createCriteria(Item.class)
.add(Restrictions.eq("discount", new Integer(30)))
.createAlias("category", "cat")
.add(Restrictions.eq("cat.name", "Car"))
.list();
for (int i = 0; i < items.size(); i++)
{
Item item = (Item) items.get(i);
System.out.println("item: " + item.getName() + ", discount: " + item.getDiscount()
+ ", category: " + item.getCategory().getName());
}
}
/**
* Result should be the name of the last edited product category
*/
private void testA()
{
// .uniqueResult(); not sure if more than one row
System.out.println("\nTest A");
System.out.println("\n First solution");
List list1 = session.createCriteria(ProductCategory.class)
.addOrder(Order.desc("lastUpdate"))
.setMaxResults(1)
.list();
ProductCategory pc1 = (ProductCategory) list1.get(0);
System.out.println("Category: " + pc1.getName() + ", " + pc1.getLastUpdate());
System.out.println("\n Second solution");
DetachedCriteria dc2 = DetachedCriteria.forClass(ProductCategory.class)
.setProjection(Property.forName("lastUpdate").max());
ProductCategory pc2 = (ProductCategory) session.createCriteria(ProductCategory.class)
.add(Property.forName("lastUpdate").eq(dc2))
.uniqueResult();
System.out.println("Category: " + pc2.getName() + ", " + pc2.getLastUpdate());
System.out.println("\n Third solution");
DetachedCriteria dc3 = DetachedCriteria.forClass(ProductCategory.class)
.setProjection(Projections.max("lastUpdate"));
ProductCategory pc3 = (ProductCategory) session.createCriteria(ProductCategory.class)
.add(Subqueries.propertyEq("lastUpdate", dc3))
.uniqueResult();
System.out.println("Category: " + pc3.getName() + ", " + pc3.getLastUpdate());
}
/**
* Result should be a list with product categories and the last added item to this category
*
* For instance:
* Food ==> Juice (4), discount: 30, last update: 2005-08-27 00:00:00.0
* Car ==> Ugly car (7), discount: 40, last update: 2005-08-28 00:00:00.0
*/
private void testB()
{
System.out.println("\nTest B");
System.out.println("\nHQL");
Query q = session.createQuery("SELECT procat, it FROM ProductCategory procat, Item it " +
"WHERE it.category = procat AND " +
"it.lastUpdate = " +
"(SELECT MAX(it.lastUpdate) FROM Item it " +
"WHERE procat = it.category)");
List f = q.list();
System.out.println("size: " + f.size());
for (int i = 0; i < f.size(); i++)
{
Object obj = f.get(i);
Object[] array = (Object[]) obj;
ProductCategory pc = (ProductCategory) array[0];
Item item = (Item) array[1];
System.out.println(pc.getId() + ", "
+ pc.getName() + " ==> "
+ item.getName() + " ("
+ item.getId() + "), discount: "
+ item.getDiscount() + ", last update: "
+ item.getLastUpdate());
}
// next one trial of many
System.out.println("\nCriteria API (the following result is wrong and not complete!!)");
List list = session.createCriteria(Item.class)
.setProjection(Projections.projectionList()
.add(Projections.max("lastUpdate"))
.add(Projections.groupProperty("category")))
.list();
System.out.println("size: " + list.size());
for (Iterator iter = list.iterator(); iter.hasNext();)
{
Object element = (Object) iter.next();
Object[] array = (Object[]) element;
Object o = array[0];
ProductCategory pc = (ProductCategory) array[1];
System.out.println("Cat: " + pc.getName() + ", " + o);
}
}
/**
* Result should be a list with product categories and the last added item to this category
* which has the given discount
*
* For instance (discount=10):
* Food ==> Butter (3), discount: 10, last update: 2005-08-22 00:00:00.0
*
* @param discount
*/
private void testC(int discount)
{
System.out.println("\nTest C");
// like Test C but additional with discount = 10
}
public static void main(String[] args)
{
ProductMain m = new ProductMain();
m.configure();
m.testAll();
m.simpleJoin();
//m.testA();
m.testB();
//m.testC(10);
}
}
Code:
package eg;
import java.sql.*;
import java.util.*;
public class ProductCategory
{
private Long id;
private String name;
private Timestamp lastUpdate;
private List items;
public Long getId()
{
return id;
}
public void setId(Long id)
{
this.id = id;
}
public Timestamp getLastUpdate()
{
return lastUpdate;
}
public void setLastUpdate(Timestamp lastUpdate)
{
this.lastUpdate = lastUpdate;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public List getItems()
{
return items;
}
public void setItems(List items)
{
this.items = items;
}
}
Code:
package eg;
import java.sql.*;
public class Item
{
private long id;
private String name;
private int discount;
private ProductCategory category;
private Timestamp lastUpdate;
public ProductCategory getCategory()
{
return category;
}
public void setCategory(ProductCategory category)
{
this.category = category;
}
public int getDiscount()
{
return discount;
}
public void setDiscount(int discount)
{
this.discount = discount;
}
public long getId()
{
return id;
}
public void setId(long id)
{
this.id = id;
}
public Timestamp getLastUpdate()
{
return lastUpdate;
}
public void setLastUpdate(Timestamp lastUpdate)
{
this.lastUpdate = lastUpdate;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
}
SQL (HSQL):
Code:
CREATE TABLE productcategory (
id INT NOT NULL
, name VARCHAR(50) NOT NULL
, last_update TIMESTAMP NOT NULL
, PRIMARY KEY (id)
);
CREATE TABLE item (
id INT NOT NULL
, name VARCHAR(50) NOT NULL
, discount INT NOT NULL
, category INT NOT NULL
, last_update TIMESTAMP NOT NULL
, PRIMARY KEY (id)
, CONSTRAINT FK_item_1 FOREIGN KEY (category)
REFERENCES productcategory (id)
);
Code:
INSERT INTO productcategory(id, name, last_update) VALUES(1, 'Food', '2005-08-20');
INSERT INTO productcategory(id, name, last_update) VALUES(2, 'Car', '2005-08-21');
INSERT INTO item(id, name, discount, category, last_update) VALUES(1, 'Bread', 10, 1, '2005-08-20');
INSERT INTO item(id, name, discount, category, last_update) VALUES(2, 'Chocolate', 30, 1, '2005-08-25');
INSERT INTO item(id, name, discount, category, last_update) VALUES(3, 'Butter', 10, 1, '2005-08-22');
INSERT INTO item(id, name, discount, category, last_update) VALUES(4, 'Juice', 30, 1, '2005-08-27');
INSERT INTO item(id, name, discount, category, last_update) VALUES(5, 'Nice car', 30, 2, '2005-08-21');
INSERT INTO item(id, name, discount, category, last_update) VALUES(6, 'Beautiful car', 25, 2, '2005-08-22');
INSERT INTO item(id, name, discount, category, last_update) VALUES(7, 'Ugly car', 40, 2, '2005-08-28');
INSERT INTO item(id, name, discount, category, last_update) VALUES(8, 'Yellow car', 30, 2, '2005-08-26');
Login
FAQ
Search
