Supposing that we create 2 tables with below SQL :
create table Supplier (id int, name VARCHAR, count int);
create table Product (id int, name VARCHAR, description VARCHAR, price double, supplierId int);
Models:
public class Supplier {
private int id;
private String name;
private int count;
public int getId(){ return id;}
public void setId(int id){ this.id = id; }
public String getName(){ return name;}
public void setName(String name){ this.name = name;}
public int getCount() { return count;}
public void setCount(int count) { this.count = count;}
}
AND
public class Product {
private int id;
private String name;
private String description;
private Double price;
private Supplier supplier;
public int getId() { return id;}
public void setId(int id) { this.id = id; }
public String getName() { return name;}
public void setName(String name) { this.name = name;}
public String getDescription() { return description;}
public void setDescription(String description) { this.description = description; }
public Double getPrice() {return price;}
public void setPrice(Double price) { this.price = price;}
@OneToOne(targetEntity=ProductAssignment.class, mappedBy = "supplierId", fetch = FetchType.LAZY)
public Supplier getSupplier() { return supplier;}
public void setSupplier(Supplier supplier) { this.supplier = supplier; }
}
If I want to select all products order by count in supplier I can use the below code :
Criteria crit = session.createCriteria(Product.class);
Criteria critSupplier = crit.createCriteria("supplier");
critSupplier.addOrder(Order.desc("count"));
But now, I want to select all suppliers order by price in Product table.
if I want to use MySQL, the below is the script:
select * from supplier s inner join product p ON s.id = p.supplierId order by p.price
Now I want to transfer this SQL into Hibernate Criteria query in java code?
Please help me in this case?
Here you have a bidirectional relationship between two models: Supplier and Product. It is a bidirectional relationship since you want both the models to be aware of each other, and recollect each other information, based on the link that joins them (supplierId). The relationship is also a one(Supplier)-toMany(Products)
So, first off, you are missing the fact that also Supplier must be aware of the existence of the relationship. You have to express this "awareness" by modifying the Supplier model and add to it the list products:
public class Supplier implements Serializable{
private int id;
private String name;
private int count;
private List<Product> products;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getCount() {
return count;
}
public void setCount(int count) {
this.count = count;
}
public List<Product> getProducts() {
return products;
}
public void setProducts(List<Product> products) {
this.products = products;
}
@Override
public String toString() {
return "Supplier{" + "name=" + name + '}';
}
The second step is to communicate the ORM(in your case hibernate) the relationship between your two models. Online you can find plenty of documentation that explains this subtle "step" of hibernate. in your case, something like this should do.
Hibernate mapping of Supplier:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.xxx.stackoverflowdb.model.Supplier" table="Supplier">
<id column="id" name="id" type="int">
<generator class="assigned"/>
</id>
<property column="name" name="name" type="string"/>
<property column="count" name="count" type="int"/>
<bag name="products" table="product" inverse="true" lazy="false" fetch="select">
<key>
<column name="id"/>
</key>
<one-to-many class="com.xxx.stackoverflowdb.model.Product"/>
</bag>
</class>
</hibernate-mapping>
Hibernate mapping of Product:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.xxx.stackoverflowdb.model.Product" table="PRODUCT">
<id column="id" name="id" type="int">
<generator class="assigned"/>
</id>
<property column="name" name="name" type="string"/>
<property column="description" name="description" type="string"/>
<property column="price" name="price" type="double"/>
<many-to-one name="supplierId" class="com.xxx.stackoverflowdb.model.Supplier" column="supplierId" insert="false" update="false" lazy="false"/>
</class>
</hibernate-mapping>
As you can see, both mapping files declare the relationship. With this set, you can write the Criteria and have it do the job. Since it now hibernate knows about the relationship, it can help you. I've created a simple tester class that demonstrates it:
public class Tester {
public static void main(String[] args) {
//gets a session, assuming your cg file is in a folder called hibernate_dispatcher
//under classpath
SessionFactory sessionFactory = new Configuration().configure("/hibernate_dispatcher/hibernate.cfg.xml")
.buildSessionFactory();
Session session = sessionFactory.openSession();
//gets a session, assuming your cg file is in a folder called hibernate_dispatcher
//under classpath
//YOUR own query --> gets all products order by count in supplier
Criteria criteria1 = session.createCriteria(Product.class);
criteria1.createAlias("supplierId", "supp");
criteria1.addOrder(Order.desc("supp.count"));
for(Object p:criteria1.list()){
Product nthP=(Product)p;
System.out.println(nthP);
}
//YOUR own query --> gets all products order by count in supplier
//the query you've asked --> gets all products order by price in Product
Criteria criteria2 = session.createCriteria(Supplier.class);
criteria2.createAlias("products", "prod");
criteria2.addOrder(Order.desc("prod.price"));
for(Object s:criteria2.list()){
Supplier nthS=(Supplier)s;
System.out.println(nthS);
}
//the query you've asked --> gets all products order by price in Product
}
}