Guten Abend,
so langsam bin ich am Verzweifeln mit der Criteria API und Subqueries. Mit HQL habe ich die selben Abfragen hinbekommen. Ich möchte aber gerne die Criteria API nehmen weil man damit leichter und übersichtlicher komplexe Suchabfragen gestalten kann. Nachfolgend ist ein stark vereinfachtes Beispiel welches vielleicht nicht ganz sinnvoll ist aber gut das Problem zeigt.
Probleme bereiten mir die Abfragen B (wie komme ich an die Item Objekte) und C.
Nachfolgend der komplette Code, vielleicht findet ja jemand eine ganz einfache Criteria Abfrage und ich habe Tomaten auf den Augen.
Grüsse
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());
}
}
}
/**
* 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");
// next one trial of many
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.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;
}
}
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="eg">
<class name="ProductCategory" table="productcategory" lazy="true">
<id name="id" column="id">
<generator class="increment"/>
</id>
<property name="name" column="name" not-null="true" unique="true"/>
<property name="lastUpdate" column="last_update" />
<bag name="items" inverse="true" lazy="true" cascade="all">
<key column="category"/>
<one-to-many class="Item"/>
</bag>
</class>
</hibernate-mapping>
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="eg">
<class name="Item" table="item" dynamic-update="true">
<id name="id" column="id">
<generator class="increment"/>
</id>
<property name="name" column="name" not-null="true"/>
<property name="discount" column="discount" not-null="true"/>
<property name="lastUpdate" column="last_update" type="timestamp"/>
<many-to-one name="category" column="category" not-null="true"/>
</class>
</hibernate-mapping>
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
