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.

hibernate_12_output1

Tablolarimiz olustu ve kayitlarimiz eklendi.
Product tablomuz..

hibernate_12_product_table

Software tablomuz..

hibernate_12_software_table

Supplier tablomuz..

hibernate_12_software_table

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.

hibernate_12_executeSimpleHQL

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..

hibernate_12_updatedSuppliertable

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

 

Print Friendly, PDF & Email

Leave a Reply

Your email address will not be published. Required fields are marked *