Hibernate Ders 12 – Hibernate Queries – 03

Merhaba Arkadaslar ,
Son 2 yazimizda HQL (Hibernate Query Language) uzerinde ornek yapiyorduk. Bu ornegimizde de query uzerinde devam edecegiz.

update, order by, inner join , min , avg, disctinct kisacasi temel SQL cumleciklerini ve fonksiyonlarini kullanacagiz.

Uc sinifimiz olacak bunlar Product ,Software, Supplier.

Product.java sinifimizin degiskenlerini ve @Entity notasyonumuzu ve getter/setterlarimizi ve constructorimizi olusturalim.

private int id;
private Supplier supplier;
private String name;
private String description;
private double price;

Not: constructor imizi olustururken, id yi otomatik olarak halledecegimiz icin hesaba katmiyoruz. Bununla birlikte supplier i de biz otomatik ekleyelim.

public Product( String name, String description, double price) {
this.name = name;
this.description = description;
this.price = price;
}

Supplier sinifimizda

private int id;
private String name;
private List products = new ArrayList<>();

getter/setter ve gerekli notasyonlarimizi ekliyoruz..

3.sinifimiz olarak Software.java sinifimiz…Bu sinifimiz Product sinifimizdan kalitilacak..

Software.java

package hibernate12_Queries3;

import java.io.Serializable;
import javax.persistence.Entity;

@Entity
public class Software extends Product implements Serializable {

private String version;

public Software() {
}

public Software(String name, String description,
double price, String version) {
super(name, description, price);
this.setVersion(version);
}

public String getVersion() {
return version;
}

public void setVersion(String version) {
this.version = version;
}
}

Supplier.java

package hibernate12_Queries3;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
public class Supplier implements Serializable
{
private int id;
private String name;
private List products = new ArrayList();

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
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;
}

@OneToMany(cascade=CascadeType.ALL)
//@Cascade(org.hibernate.annotations.CascadeType.DELETE_ORPHAN)

public List getProducts() {
return products;
}

public void setProducts(List products) {
this.products = products;
}
}

Product.java

package hibernate12_Queries3;

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToOne;

@Entity
public class Product implements Serializable
{
private int id;
private Supplier supplier;

private String name;
private String description;
private double price;

public Product() {
}

public Product(String name, String description, double price) {
this.name = name;
this.description = description;
this.price = price;
}

public String getDescription() {
return description;
}

public void setDescription(String description) {
this.description = description;
}

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
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;
}

@ManyToOne
public Supplier getSupplier() {
return supplier;
}

public void setSupplier(Supplier supplier) {
this.supplier = supplier;
}

public double getPrice() {
return price;
}

public void setPrice(double price) {
this.price = price;
}
}

HQLExample.java

package hibernate12_Queries_3;

import hibernate11_Queries_2.Workers_2;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

public class HQLExample {

public void executeSimpleHQL(Session session) {
Query query = session.createQuery("from Product");
List results = query.list();
displayProductsList(results);
}

public void executeProjectionHQL(Session session) {
Query query = session
.createQuery("select product.name, product.price from Product product");
List results = query.list();
displayObjectsList(results);
}

public void executeHQLForRestrictions(Session session) {
String hql = "from Product where price > 25.0 and name like 'Mou%'";
Query query = session.createQuery(hql);
List results = query.list();
displayProductsList(results);
}

public void executeNamedParametersHQL(Session session) {
String hql = "from Product where price > :price";
Query query = session.createQuery(hql);
query.setDouble("price", 25.0);
List results = query.list();
displayProductsList(results);
}

public void executeObjectNamedParametersHQL(Session session) {
String supplierHQL = "from Supplier where name='MegaInc'";
Query supplierQuery = session.createQuery(supplierHQL);
Supplier supplier = (Supplier) supplierQuery.list().get(0);

String hql = "from Product as product where product.supplier=:supplier";
Query query = session.createQuery(hql);
query.setEntity("supplier", supplier);
List results = query.list();
displayProductsList(results);
}

public void executePagingHQL(Session session) {
Query query = session.createQuery("from Product");
query.setFirstResult(1);
query.setMaxResults(2);
List results = query.list();
displayProductsList(results);
}

public void executeUniqueResultHQL(Session session) {
String hql = "from Product where price>25.0";
Query query = session.createQuery(hql);
query.setMaxResults(1);
Product product = (Product) query.uniqueResult();
// test for null here if needed

List results = new ArrayList();
results.add(product);
displayProductsList(results);
}

public void executeOrderHQL(Session session) {
String hql = "from Product p where p.price>25.0 order by p.price desc";
Query query = session.createQuery(hql);
List results = query.list();
displayProductsList(results);
}

public void executeOrderTwoPropertiesHQL(Session session) {
String hql = "from Product p order by p.supplier.name asc, p.price asc";
Query query = session.createQuery(hql);
List results = query.list();
displayProductsList(results);
}

public void executeAssociationsHQL(Session session) {
String hql = "select s.name, p.name, p.price from Product p inner join p.supplier as s";
Query query = session.createQuery(hql);
List results = query.list();
displayObjectsList(results);
}

public void executeCountHQL(Session session) {
String hql = "select count(distinct product.supplier.name) from Product product";
Query query = session.createQuery(hql);
Long result = (Long) query.uniqueResult();
System.out.println(result);
}

public void executeMaxMinHQL(Session session) {
String hql = "select min(product.price), max(product.price) from Product product";
Query query = session.createQuery(hql);
List results = query.list();
displayObjectsList(results);
}

public void executeScalarSQL(Session session) {
String sql = "select avg(product.price) as avgPrice from Product product";

SQLQuery query = session.createSQLQuery(sql);
query.addScalar("avgPrice", Hibernate.DOUBLE);
List results = query.list();
displayObjectList(results);

}

public void executeSelectSQL(Session session) {
String sql = "select {supplier.*} from Supplier supplier";

SQLQuery query = session.createSQLQuery(sql);
query.addEntity("supplier", Supplier.class);
List results = query.list();
displaySupplierList(results);

}

public void executeUpdateHQL(Session session) {
String hql = "update Supplier set name = :newName where name = :name";
Query query = session.createQuery(hql);
query.setString("name", "SuperCorp");
query.setString("newName", "MegaCorp");
int rowCount = query.executeUpdate();
System.out.println("Rows affected: " + rowCount);

// See the results of the update
query = session.createQuery("from Supplier");
List results = query.list();

displaySupplierList(results);
}

public void populate(Session session) {

Supplier superCorp = new Supplier();
superCorp.setName("SuperCorp");
session.save(superCorp);

Supplier megaInc = new Supplier();
megaInc.setName("MegaInc");
session.save(megaInc);

Product mouse = new Product("Mouse", "Optical Wheel Mouse", 20.0);
mouse.setSupplier(superCorp);
superCorp.getProducts().add(mouse);

Product mouse2 = new Product("Mouse", "One Button Mouse", 22.0);
mouse2.setSupplier(superCorp);
superCorp.getProducts().add(mouse2);

Product keyboard = new Product("Keyboard", "101 Keys", 30.0);
keyboard.setSupplier(megaInc);
megaInc.getProducts().add(keyboard);

Software webBrowser = new Software("Web Browser", "Blocks Pop-ups",
75.0, "2.0");
webBrowser.setSupplier(superCorp);
superCorp.getProducts().add(webBrowser);

Software email = new Software("Email", "Blocks spam", 49.99,
"4.1 RMX Edition");
email.setSupplier(megaInc);
megaInc.getProducts().add(email);
}

public void displayObjectList(List list) {
Iterator iter = list.iterator();
if (!iter.hasNext()) {
System.out.println("No objects to display.");
return;
}
while (iter.hasNext()) {
Object obj = iter.next();
System.out.println(obj.getClass().getName());
System.out.println(obj);
}
}

public void displayObjectsList(List list) {
Iterator iter = list.iterator();
if (!iter.hasNext()) {
System.out.println("No objects to display.");
return;
}
while (iter.hasNext()) {
// System.out.println("New object");
Object[] obj = (Object[]) iter.next();
for (int i = 0; i < obj.length; i++) {
System.out.println(obj[i]+" ");
}
System.out.println();

}
}

public void displayProductsList(List list) {
Iterator iter = list.iterator();
if (!iter.hasNext()) {
System.out.println("No products to display.");
return;
}
while (iter.hasNext()) {
Product product = (Product) iter.next();
String msg = product.getSupplier().getName() + "\t";
msg += product.getName() + "\t";
msg += product.getPrice() + "\t";
msg += product.getDescription();
System.out.println(msg);
}
}

public void displaySoftwareList(List list) {
Iterator iter = list.iterator();
if (!iter.hasNext()) {
System.out.println("No software to display.");
return;
}
while (iter.hasNext()) {
Software software = (Software) iter.next();
String msg = software.getSupplier().getName() + "\t";
msg += software.getName() + "\t";
msg += software.getPrice() + "\t";
msg += software.getDescription() + "\t";
msg += software.getVersion();
System.out.println(msg);
}
}

public void displaySupplierList(List list) {
Iterator iter = list.iterator();
if (!iter.hasNext()) {
System.out.println("No suppliers to display.");
return;
}
while (iter.hasNext()) {
Supplier supplier = (Supplier) iter.next();
String msg = supplier.getName();
System.out.println(msg);
}
}

public static void main(String args[]) throws Exception {

HQLExample example = new HQLExample();

AnnotationConfiguration config = new AnnotationConfiguration();
config.addAnnotatedClass(Product.class);
config.addAnnotatedClass(Software.class);
config.addAnnotatedClass(Supplier.class);
config.configure("hibernate3.cfg.xml");

//new SchemaExport(config).create(true, true);

SessionFactory factory = config.buildSessionFactory();
Session session = factory.getCurrentSession();
session.beginTransaction();

String action = "executeScalarSQL";
if (action.equalsIgnoreCase("populate")) {
example.populate(session);
} else {

Method method = HQLExample.class.getMethod(action,
new Class[]{Session.class});
method.invoke(example, new Object[]{session});
}

session.getTransaction().commit();
//session.close();
}
}

HQLExample.java sinifimizi aciklamaya calisalim !

AnnotationConfiguration config = new AnnotationConfiguration();
config.addAnnotatedClass(Product.class);
config.addAnnotatedClass(Software.class);
config.addAnnotatedClass(Supplier.class);
config.configure("hibernate3.cfg.xml");

kisminda 3 Pojo sinifimizi yazmayi unutmuyoruz.

if (action.equalsIgnoreCase(action)) {
example.populate(session);
} else {
action = "execute" + action;

Method method = HQLExample.class.getMethod(action,
new Class[]{Session.class});
method.invoke(example, new Object[]{session});
}

Bu kisimda ise String action degerine gore ornegimiz calisiyor, oncelikle tablolarimizi olusturup kayit eklememiz gerektigi icin, populate metodunu cagiriyoruz boylece kayitlarimiz ekleniyor. ornegi ikinci defa calistirdigimizda

Not: String action degiskenimize diger metotlarimizin adini veriyoruz.
Boylece tum metotlarimizin ciktilarini gorebiliriz.

Oncelikle populate metodumuzu cagiralim ve sonucumuzu gorelim.

Tablolarimiz olustu ve kayitlarimiz eklendi.
Product tablomuz..

Software tablomuz..

Supplier tablomuz..

Kayitlarimiz uzerinde sorgulari calistirmak icin diger metotlarimizi sirayla deneyecegiz.
Tablo kayitlarimiz ucmasin diye

//new SchemaExport(config).create(true, true);
haline getiriyoruz.
Bir de hibernate3.cfg.xml dosyamizda

update seklinde olduguna dikkat edelim. Eger create olsaydi tablomuzdaki kayitlarimiz ucardi !

String action="executeSimpleHQL";

yapip sonucu gorelim.

String action=”executeProjectionHQL”; icin sorgumuzda product.name ve product price i getiriyoruz..

String action=”executeHQLForRestrictions”; icin where ve like i sorgumuzda kullaniyoruz.

public void executeHQLForRestrictions(Session session) {
String hql = "from Product where price > 21.0 and name like 'Mou%'";
Query query = session.createQuery(hql);
List results = query.list();
displayProductsList(results);
}

String action=”executeNamedParametersHQL”;
Burada dikkat edersek where sorgumuzda :price degiskeni kullaniyoruz ve daha sonra query.setDouble ile buraya bir deger atiyabiliriz.

public void executeNamedParametersHQL(Session session) {
String hql = "from Product where price > :price";
Query query = session.createQuery(hql);
query.setDouble("price", 25.0);
List results = query.list();
displayProductsList(results);
}

Bu metot bize 25 ten buyuk price a sahip urunleri getirir…

SuperCorp Web Browser 75.0 Blocks Pop-ups
MegaInc Email 49.99 Blocks spam
MegaInc Keyboard 30.0 101 Keys

seklinde ciktimizi elde ederiz.

executeOrderHQL metodumuzda order by kullanimi..

public void executeOrderHQL(Session session) {
String hql = "from Product p where p.price>25.0 order by p.price desc";
Query query = session.createQuery(hql);
List results = query.list();
displayProductsList(results);
}

executeAssociationsHQL metodumuzda inner join islemi…

public void executeAssociationsHQL(Session session) {
String hql = "select s.name, p.name, p.price from Product p inner join p.supplier as s";
Query query = session.createQuery(hql);
List results = query.list();
displayObjectsList(results);
}

executeCountHQL metodumuzda distinct ve count..

public void executeCountHQL(Session session) {
String hql = "select count(distinct product.supplier.name) from Product product";
Query query = session.createQuery(hql);
Long result = (Long) query.uniqueResult();
System.out.println(result);
}

Son olarak update icin..

executeUpdateHQL

public void executeUpdateHQL(Session session) {
String hql = "update Supplier set name = :newName where name = :name";
Query query = session.createQuery(hql);
query.setString("name", "SuperCorp");
query.setString("newName", "MegaCorp");
int rowCount = query.executeUpdate();
System.out.println("Rows affected: " + rowCount);

// See the results of the update
query = session.createQuery("from Supplier");
List results = query.list();

displaySupplierList(results);
}

SuperCorp olan supplier ismini MegaCorp olarak guncelledik..

Dersimizin burada sonuna geliyoruz, her metodu tek tek deneyerek ciktilari gorebilirsiniz.

Kaynak kodlar:HibernateTutorial_v12

Herkese Bol Javali Gunler dilerim.
Be an oracle man , import java.*;
Levent Erguder
injavawetrust