Java Tutorial/Hibernate/HSQL

HSQL Count function

File: Main.java

   <source lang="java">

import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table Product(id int, name varchar, description varchar, price decimal(6,2), supplierid int)");
    
   hibernateUtil
   .executeSQLCommand("create table Supplier (id int , name varchar)");
   hibernateUtil
   .executeSQLCommand("create table Software(id int, name varchar, description varchar, price decimal(6,2), supplierid int, version varchar)");
   
   Session session = hibernateUtil.getSession();
   Supplier superCorp = new Supplier();
   superCorp.setName("Supplier1");
   session.save(superCorp);
   
   Supplier megaInc = new Supplier();
   megaInc.setName("Supplier2");
   session.save(megaInc);        
   Product mouse = new Product("Product1","first product", 20.0);
   mouse.setSupplier(superCorp);
   superCorp.getProducts().add(mouse);
   session.flush();
   Product mouse2 = new Product("Product2","second product", 22.0);
   mouse2.setSupplier(superCorp);
   superCorp.getProducts().add(mouse2);        
   
   Product keyboard = new Product("Product3", "third product", 30.0);
   keyboard.setSupplier(megaInc);
   megaInc.getProducts().add(keyboard);
   Software webBrowser = new Software("Web Browser","new browser", 75.0, "2.0");
   webBrowser.setSupplier(superCorp);
   superCorp.getProducts().add(webBrowser);
   Software email = new Software("Email","email client", 49.99, "4.1 Edition");
   email.setSupplier(megaInc);
   megaInc.getProducts().add(email);    
   session.flush();
   
   
   
   String hql = "select min(product.price), max(product.price) from Product product";
   Query query = session.createQuery(hql);
   List results = query.list();
   System.out.println(results);
   
   
   
   session.close();
   hibernateUtil.checkData("select * from Product");
   hibernateUtil.checkData("select * from Software");
   hibernateUtil.checkData("select * from Supplier");
 }

}</source>

HSQL Fetch Associations

File: Main.java

   <source lang="java">

import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table Product(id int, name varchar, description varchar, price decimal(6,2), supplierid int)");
    
   hibernateUtil
   .executeSQLCommand("create table Supplier (id int , name varchar)");
   hibernateUtil
   .executeSQLCommand("create table Software(id int, name varchar, description varchar, price decimal(6,2), supplierid int, version varchar)");
   
   Session session = hibernateUtil.getSession();
   Supplier superCorp = new Supplier();
   superCorp.setName("Supplier1");
   session.save(superCorp);
   
   Supplier megaInc = new Supplier();
   megaInc.setName("Supplier2");
   session.save(megaInc);        
   Product mouse = new Product("Product1","first product", 20.0);
   mouse.setSupplier(superCorp);
   superCorp.getProducts().add(mouse);
   session.flush();
   Product mouse2 = new Product("Product2","second product", 22.0);
   mouse2.setSupplier(superCorp);
   superCorp.getProducts().add(mouse2);        
   
   Product keyboard = new Product("Product3", "third product", 30.0);
   keyboard.setSupplier(megaInc);
   megaInc.getProducts().add(keyboard);
   Software webBrowser = new Software("Web Browser","new browser", 75.0, "2.0");
   webBrowser.setSupplier(superCorp);
   superCorp.getProducts().add(webBrowser);
   Software email = new Software("Email","email client", 49.99, "4.1 Edition");
   email.setSupplier(megaInc);
   megaInc.getProducts().add(email);    
   session.flush();
   
   
   
   String hql = "from Supplier s inner join fetch s.products as p";
   Query query = session.createQuery(hql);
   List results = query.list();
   System.out.println(results);
   
   
   
   session.close();
   hibernateUtil.checkData("select * from Product");
   hibernateUtil.checkData("select * from Software");
   hibernateUtil.checkData("select * from Supplier");
 }

}</source>

HSQL Inner Join Class And Its Field

File: Main.java

   <source lang="java">

import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table Product(id int, name varchar, description varchar, price decimal(6,2), supplierid int)");
    
   hibernateUtil
   .executeSQLCommand("create table Supplier (id int , name varchar)");
   hibernateUtil
   .executeSQLCommand("create table Software(id int, name varchar, description varchar, price decimal(6,2), supplierid int, version varchar)");
   
   Session session = hibernateUtil.getSession();
   Supplier superCorp = new Supplier();
   superCorp.setName("Supplier1");
   session.save(superCorp);
   
   Supplier megaInc = new Supplier();
   megaInc.setName("Supplier2");
   session.save(megaInc);        
   Product mouse = new Product("Product1","first product", 20.0);
   mouse.setSupplier(superCorp);
   superCorp.getProducts().add(mouse);
   session.flush();
   Product mouse2 = new Product("Product2","second product", 22.0);
   mouse2.setSupplier(superCorp);
   superCorp.getProducts().add(mouse2);        
   
   Product keyboard = new Product("Product3", "third product", 30.0);
   keyboard.setSupplier(megaInc);
   megaInc.getProducts().add(keyboard);
   Software webBrowser = new Software("Web Browser","new browser", 75.0, "2.0");
   webBrowser.setSupplier(superCorp);
   superCorp.getProducts().add(webBrowser);
   Software email = new Software("Email","email client", 49.99, "4.1 Edition");
   email.setSupplier(megaInc);
   megaInc.getProducts().add(email);    
   session.flush();
   
   
   
   String hql = "from Product p inner join p.supplier as s";
   Query query = session.createQuery(hql);
   List results = query.list();
   System.out.println(results);
   
   
   
   session.close();
   hibernateUtil.checkData("select * from Product");
   hibernateUtil.checkData("select * from Software");
   hibernateUtil.checkData("select * from Supplier");
 }

}</source>

HSQL Join Two Classes

File: Main.java

   <source lang="java">

import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table Product(id int, name varchar, description varchar, price decimal(6,2), supplierid int)");
    
   hibernateUtil
   .executeSQLCommand("create table Supplier (id int , name varchar)");
   hibernateUtil
   .executeSQLCommand("create table Software(id int, name varchar, description varchar, price decimal(6,2), supplierid int, version varchar)");
   
   Session session = hibernateUtil.getSession();
   Supplier superCorp = new Supplier();
   superCorp.setName("Supplier1");
   session.save(superCorp);
   
   Supplier megaInc = new Supplier();
   megaInc.setName("Supplier2");
   session.save(megaInc);        
   Product mouse = new Product("Product1","first product", 20.0);
   mouse.setSupplier(superCorp);
   superCorp.getProducts().add(mouse);
   session.flush();
   Product mouse2 = new Product("Product2","second product", 22.0);
   mouse2.setSupplier(superCorp);
   superCorp.getProducts().add(mouse2);        
   
   Product keyboard = new Product("Product3", "third product", 30.0);
   keyboard.setSupplier(megaInc);
   megaInc.getProducts().add(keyboard);
   Software webBrowser = new Software("Web Browser","new browser", 75.0, "2.0");
   webBrowser.setSupplier(superCorp);
   superCorp.getProducts().add(webBrowser);
   Software email = new Software("Email","email client", 49.99, "4.1 Edition");
   email.setSupplier(megaInc);
   megaInc.getProducts().add(email);    
   session.flush();
   
   
   
   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();
   System.out.println(results);
   
   
   
   session.close();
   hibernateUtil.checkData("select * from Product");
   hibernateUtil.checkData("select * from Software");
   hibernateUtil.checkData("select * from Supplier");
 }

}</source>

HSQL Named Query

File: Main.java

   <source lang="java">

import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table Product(id int, name varchar, description varchar, price decimal(6,2), supplierid int)");
    
   hibernateUtil
   .executeSQLCommand("create table Supplier (id int , name varchar)");
   hibernateUtil
   .executeSQLCommand("create table Software(id int, name varchar, description varchar, price decimal(6,2), supplierid int, version varchar)");
   
   Session session = hibernateUtil.getSession();
   Supplier superCorp = new Supplier();
   superCorp.setName("Supplier1");
   session.save(superCorp);
   
   Supplier megaInc = new Supplier();
   megaInc.setName("Supplier2");
   session.save(megaInc);        
   Product mouse = new Product("Product1","first product", 20.0);
   mouse.setSupplier(superCorp);
   superCorp.getProducts().add(mouse);
   session.flush();
   Product mouse2 = new Product("Product2","second product", 22.0);
   mouse2.setSupplier(superCorp);
   superCorp.getProducts().add(mouse2);        
   
   Product keyboard = new Product("Product3", "third product", 30.0);
   keyboard.setSupplier(megaInc);
   megaInc.getProducts().add(keyboard);
   Software webBrowser = new Software("Web Browser","new browser", 75.0, "2.0");
   webBrowser.setSupplier(superCorp);
   superCorp.getProducts().add(webBrowser);
   Software email = new Software("Email","email client", 49.99, "4.1 Edition");
   email.setSupplier(megaInc);
   megaInc.getProducts().add(email);    
   session.flush();
   
   
   
   Query query = session.getNamedQuery("Product.HQLpricing");
   List results = query.list();
   System.out.println(results);
   
   
   
   session.close();
   hibernateUtil.checkData("select * from Product");
   hibernateUtil.checkData("select * from Software");
   hibernateUtil.checkData("select * from Supplier");
 }

}</source>

HSQL Order By Ascending Order

File: Main.java

   <source lang="java">

import java.util.ArrayList; import java.util.Date; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
   Session session = hibernateUtil.getSession();
   Survey survey = new Survey();
   survey.setName("Survey");
   survey.setPurchaseDate(new Date());
   session.save(survey);
   
   survey = new Survey();
   survey.setName("Survey1");
   survey.setPurchaseDate(new Date());    
   session.save(survey);
   
   session.flush();
   
   String hql = "from Survey p order by p.price asc";
   Query query = session.createQuery(hql);
   List results = query.list();
   
   System.out.println(results);
   
   session.close();
   hibernateUtil.checkData("select * from survey");
 }

}</source>

HSQL Order By clause

File: Main.java

   <source lang="java">

import java.util.ArrayList; import java.util.Date; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
   Session session = hibernateUtil.getSession();
   Survey survey = new Survey();
   survey.setName("Survey");
   survey.setPurchaseDate(new Date());
   session.save(survey);
   
   survey = new Survey();
   survey.setName("Survey1");
   survey.setPurchaseDate(new Date());    
   session.save(survey);
   
   session.flush();
   
   String hql = "from Survey p where p.Id > 0 order by p.name desc";
   Query query = session.createQuery(hql);
   List results = query.list();
   
   System.out.println(results);
   
   session.close();
   hibernateUtil.checkData("select * from survey");
 }

}</source>

HSQL pageable result: First, Max Result

File: Main.java

   <source lang="java">

import java.util.Date; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
   Session session = hibernateUtil.getSession();
   Survey survey = new Survey();
   survey.setName("Survey");
   survey.setPurchaseDate(new Date());
   session.save(survey);
   
   survey = new Survey();
   survey.setName("Survey1");
   survey.setPurchaseDate(new Date());    
   session.save(survey);
   
   session.flush();
   
   Query query = session.createQuery("from Survey");
   query.setFirstResult(1);
   query.setMaxResults(2);
   List results = query.list();
   System.out.println(results);
   
   session.close();
   hibernateUtil.checkData("select * from survey");
 }

}</source>

HSQL Reference Object Type Field

File: Main.java

   <source lang="java">

import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table Product(id int, name varchar, description varchar, price decimal(6,2), supplierid int)");
    
   hibernateUtil
   .executeSQLCommand("create table Supplier (id int , name varchar)");
   hibernateUtil
   .executeSQLCommand("create table Software(id int, name varchar, description varchar, price decimal(6,2), supplierid int, version varchar)");
   
   Session session = hibernateUtil.getSession();
   Supplier superCorp = new Supplier();
   superCorp.setName("Supplier1");
   session.save(superCorp);
   
   Supplier megaInc = new Supplier();
   megaInc.setName("Supplier2");
   session.save(megaInc);        
   Product mouse = new Product("Product1","first product", 20.0);
   mouse.setSupplier(superCorp);
   superCorp.getProducts().add(mouse);
   session.flush();
   Product mouse2 = new Product("Product2","second product", 22.0);
   mouse2.setSupplier(superCorp);
   superCorp.getProducts().add(mouse2);        
   
   Product keyboard = new Product("Product3", "third product", 30.0);
   keyboard.setSupplier(megaInc);
   megaInc.getProducts().add(keyboard);
   Software webBrowser = new Software("Web Browser","new browser", 75.0, "2.0");
   webBrowser.setSupplier(superCorp);
   superCorp.getProducts().add(webBrowser);
   Software email = new Software("Email","email client", 49.99, "4.1 Edition");
   email.setSupplier(megaInc);
   megaInc.getProducts().add(email);    
   session.flush();
   
   
   
   String hql = "from Product p order by p.supplier.name asc, p.price asc";
   Query query = session.createQuery(hql);
   List results = query.list();
   System.out.println(results);
   
   
   
   session.close();
   hibernateUtil.checkData("select * from Product");
   hibernateUtil.checkData("select * from Software");
   hibernateUtil.checkData("select * from Supplier");
 }

}</source>

HSQL: Set Max Result

File: Main.java

   <source lang="java">

import java.util.ArrayList; import java.util.Date; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
   Session session = hibernateUtil.getSession();
   Survey survey = new Survey();
   survey.setName("Survey");
   survey.setPurchaseDate(new Date());
   session.save(survey);
   
   survey = new Survey();
   survey.setName("Survey1");
   survey.setPurchaseDate(new Date());    
   session.save(survey);
   
   session.flush();
   
   String hql = "from Survey where Id > 0";
   Query query = session.createQuery(hql);
   query.setMaxResults(1);
   Survey product = (Survey) query.uniqueResult();
   //test for null here if needed
   
   List results = new ArrayList();
   results.add(product);
   System.out.println(results);
   
   session.close();
   hibernateUtil.checkData("select * from survey");
 }

}</source>

HSQL Table Alias

File: Main.java

   <source lang="java">

import java.util.Date; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
   Session session = hibernateUtil.getSession();
   Survey survey = new Survey();
   survey.setName("Survey");
   survey.setPurchaseDate(new Date());
   session.save(survey);
   
   survey = new Survey();
   survey.setName("Survey1");
   survey.setPurchaseDate(new Date());    
   session.save(survey);
   
   session.flush();
   
   Query query = session.createQuery("select sur.name, sur.id from Survey sur");
   List results = query.list();
   System.out.println(results);
   
   session.close();
   hibernateUtil.checkData("select * from survey");
 }

}</source>

HSQL Where Clause And Condition

File: Main.java

   <source lang="java">

import java.util.Date; import java.util.List; import org.hibernate.Query; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
   Session session = hibernateUtil.getSession();
   Survey survey = new Survey();
   survey.setName("Survey");
   survey.setPurchaseDate(new Date());
   session.save(survey);
   
   survey = new Survey();
   survey.setName("Survey1");
   survey.setPurchaseDate(new Date());    
   session.save(survey);
   
   session.flush();
   
   Query query = session.createQuery("from Survey where Id > 0 and name like "S%"");
   List results = query.list();
   System.out.println(results);
   
   session.close();
   hibernateUtil.checkData("select * from survey");
 }

}</source>

Query a Class Name (a table)

File: Main.java

   <source lang="java">

import java.util.Date; import java.util.Iterator; import java.util.List; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
   Session session = hibernateUtil.getSession();
   Survey survey = new Survey();
   survey.setName("Survey");
   survey.setPurchaseDate(new Date());
   System.out.println(survey.getId());
   session.save(survey);
   session.flush();
   List cds = session.createQuery("from Survey").list();
   Iterator iter = cds.iterator();
   while (iter.hasNext()) {
     Survey cd = (Survey) iter.next();
     System.out.println(cd.getName());
   }
   session.close();
   hibernateUtil.checkData("select * from survey");
 }

}</source>

Query on a field in a class

File: Main.java

   <source lang="java">

import java.util.Date; import java.util.Iterator; import java.util.List; import org.hibernate.Session; public class Main {

 public static void main(String[] args) throws Exception {
   HibernateUtil hibernateUtil = new HibernateUtil();
   hibernateUtil
       .executeSQLCommand("create table survey (id int,name varchar, purchasedate date);");
   Session session = hibernateUtil.getSession();
   Survey survey = new Survey();
   survey.setName("Survey");
   survey.setPurchaseDate(new Date());
   session.save(survey);
   session.flush();
   
   List cds = session.createQuery("select s from s in class Survey where s.id = 0").list();
   Iterator iter = cds.iterator();
   while (iter.hasNext()) {
     Survey cd = (Survey) iter.next();
     System.out.println(cd.getName());
   }
   session.close();
   hibernateUtil.checkData("select * from survey");
 }

}</source>