HSQL Fetch Associations : HSQL « Hibernate « Java Tutorial






File: Main.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");
  }
}

File: Product.hbm.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping
   PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="Product">
      <id name="id" type="int">
         <generator class="increment"/>
      </id>

      <property name="name" type="string"/>
      <property name="description" type="string"/>
      <property name="price" type="double"/>
      <many-to-one name="supplier" class="Supplier" column="supplierId"/>

   </class>
   
   <query name="Product.HQLpricing">
     select product.price from Product product 
  </query>   
   <sql-query name="Product.SQLpricing">
   <return-scalar column="price" type="double"/>
     select product.price from Product as product where product.price = 25.0
  </sql-query>     
</hibernate-mapping>

File: Product.java

public class Product
{
    private int id;
    private Supplier supplier;
    
    private String name;
    private String description;
    private double price;
    
    public Product()
    {
        super();
    }
    
    public Product(String name, String description, double price)
    {
        super();
        this.name = name;
        this.description = description;
        this.price = price;
    }
    
    public String getDescription()
    {
        return description;
    }
    public void setDescription(String description)
    {
        this.description = description;
    }
    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 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;
    }
}

File: Software.hbm.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping
   PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <joined-subclass name="Software" extends="Product">
      <key column="Id"/>
      <property name="version" type="string"/>
   </joined-subclass>
</hibernate-mapping>

File: Software.java

public class Software extends Product
{
    private String version;
    
    public Software()
    {
        super();
    }
    
    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;
    }
}

File: Supplier.hbm.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-mapping
   PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
   <class name="Supplier" >
      <id name="id" type="int">
         <generator class="increment"/>
      </id>

      <property name="name" type="string"/>
      <bag name="products" inverse="true" cascade="all,delete-orphan">
        <key column="supplierId"/>
        <one-to-many class="Product"/>
      </bag>


   </class>
</hibernate-mapping>

File: Supplier.java

import java.util.ArrayList;
import java.util.List;

public class Supplier
{
    private int id;
    private String name;
    private List products = new ArrayList();
    
    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 List getProducts()
    {
        return products;
    }
    public void setProducts(List products)
    {
        this.products = products;
    }
}

File: hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!-- Database connection settings -->
        <property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
        <property name="connection.url">jdbc:hsqldb:data/tutorial</property>
        <property name="connection.username">sa</property>
        <property name="connection.password"></property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.HSQLDialect</property>
        <!-- Enable Hibernate's current session context -->
        <property name="current_session_context_class">org.hibernate.context.ManagedSessionContext</property>

        <property name="hibernate.cache.use_second_level_cache">false</property>
        <property name="hibernate.cache.use_query_cache">false</property>
        
        <!-- Disable the second-level cache  -->
        <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>        
        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>
        
        <!-- Mapping files -->
        <mapping resource="Product.hbm.xml"/>
        <mapping resource="Software.hbm.xml"/>
        <mapping resource="Supplier.hbm.xml"/>
    </session-factory>
</hibernate-configuration>
  Download:  HibernateHSQLFetchAssociations.zip( 4,866 k)








21.11.HSQL
21.11.1.Query a Class Name (a table)
21.11.2.Query on a field in a class
21.11.3.HSQL Where Clause And Condition
21.11.4.HSQL Table Alias
21.11.5.HSQL Reference Object Type Field
21.11.6.HSQL Order By Ascending Order
21.11.7.HSQL Order By clause
21.11.8.HSQL Named Query
21.11.9.HSQL: Set Max Result
21.11.10.HSQL Join Two Classes
21.11.11.HSQL Inner Join Class And Its Field
21.11.12.HSQL pageable result: First, Max Result
21.11.13.HSQL Fetch Associations
21.11.14.HSQL Count function