Select Queries

If we are providing queries to retrieve data from DB, by default we uses the data base related queries, suppose I have return sql related queries and now I am trying to change the DB to mysql. In this case I need to update all of my queries to my sql understandable language it's not the better way to convert all the queries.

Hibernate has contains this mechanism if we provide hibernate related query in our java application and hibernate will convert those queries to DB understandable queries. This conversion will be done by using Dialect class

If we want to retrieve data from Db we have three ways in hibernate those are

1. HQL

2. Criteria

3. Native SQL

HQL:

HQL query deals with POJO objects and everything in HQL we have to provide the POJO objects

public class RetirveData

{

@SuppressWarnings("rawtypes")

public static void main(String[] args)

{

String query = "from Employee";

List list = selectQuery(query);

for (Object o : list)

{

if (o instanceof Employee)

{

Employee emp = (Employee)o;

System.out.println(emp.getEmpName());

}

@SuppressWarnings("rawtypes")

public static final List selectQuery(String query)

{

AnnotationConfiguration cfg = new AnnotationConfiguration();

cfg.configure();

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

Query hqlQuery = hsession.createQuery(query);

List list= hqlQuery.list();

tx.commit();

hsession.close();

return list;

}

Above program fetch all the employee records which are exist in Db.

HQL with Select query:

If we wanna to fetch particular columns we have to provide properties in select query. When we use the select query in HQL hibernate adds the object arrays to the array list. Object array size is number of properties which are added in select query.

EX: select empno,empName from Employee;

Here array list size 2.

AnnotationConfiguration cfg = new AnnotationConfiguration();

cfg.configure();

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

String query = "select empNo,empName from Employee";

Query hqlQuery = hsession.createQuery(query);

List list = hqlQuery.list();

for (Object emp : list)

{

Object[] o = (Object[]) emp;

System.out.println(o[0]);

System.out.println(o[1]);

}

tx.commit();

hsession.close();

Positional parameters:

The queries which contains the ? are called as positional parameters.

HQL positional parameters just like prepared statement parameters. If positional start from 0 and JDBC positional parameters start with 1

AnnotationConfiguration cfg = new AnnotationConfiguration();

cfg.configure();

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

String query = "from Employee where empNo=?";

Query hqlQuery = hsession.createQuery(query);

hqlQuery.setInteger(0, 1);

List<Employee> list = hqlQuery.list();

for (Employee emp : list)

{

System.out.println(emp.getEmpName());

}

tx.commit();

hsession.close();

Named Positional parameters:

Instead of passing the ? if we provide the names to the query, those parameters are called as named positional parameters.

AnnotationConfiguration cfg = new AnnotationConfiguration();

cfg.configure();

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

String query = "from Employee where empNo=:eno";

Query hqlQuery = hsession.createQuery(query);

hqlQuery.setInteger("eno", 1);

List<Employee> list = hqlQuery.list();

for (Employee emp : list)

{

System.out.println(emp.getEmpName());

}

tx.commit();

hsession.close();

Criteria:

Criteria deals with classes,

public class RetriveData

{

public static void main(String[] args)

{

AnnotationConfiguration cfg = new AnnotationConfiguration();

cfg.configure();

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

Criteria c = hsession.createCriteria(Employee.class);

List<Employee> list = c.list();

for (Employee emp : list)

{

System.out.println(emp.getEmpName());

}

tx.commit();

hsession.close();

}

list():

When we call the list method hibernate do some operations.

· list() convert hql query to sql query and sends it to the DB

· DB returns the result set object

· Hibernate convert this result set object to array list

· Now hibernate returns the

Native sql:

Native SQL is used to sends the Db specific queries

AnnotationConfiguration cfg = new AnnotationConfiguration();

cfg.configure();

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

String query = "select * from employee";

SQLQuery nativeSql = hsession.createSQLQuery(query);

List list = nativeSql.list();

Iterator i = list.iterator();

while (i.hasNext())

{

Object[] o = (Object[]) i.next();

System.out.println(o[1]);

}

tx.commit();

hsession.close();

Hibernate Single ton:

If we create single object for one class then that is called as single ton object.

public class HibernateSingleTon

{

private static Session hsession;

private static SessionFactory sf;

static

{

Configuration cfg = new AnnotationConfiguration();

cfg.configure();

sf = cfg.buildSessionFactory();

}

private static final Session openSession()

{

if (hsession == null)

{

hsession = sf.openSession();

}

return hsession;

}

public static Session getSession()

{

return openSession();

}

Generators:

Generators are used to generate the primary key values.

In real time applications we do not allow user to enter the id values. We will create programs to generate primary keys by default. Some of the techniques are

· find the max number in the table and increment by one.

select max(empNo) from Employee;

· Create sequence and generate primary key values.

To generate primary keys in hibernate we have so many techniques. Hibernate is having the lot of predefined classes. So of them are

· Assigned. class

· SequenceGenerator.class

· SelectGenarate.class

· SequenceHiloGenerator.class

· UUIDGenerator.class

All the above generator classes present in the org.hibenrate.id package

Every generator class is assigned with a unique key for example IncrementGenerator class assigned with increment

SequenceGenerator--------------sequence

SequenceHiLo----------------------hilo

SelectGenerator-------------------native

UUIDGenerator--------------------uuid

Table: Product Table(pid,pname,price)

Assigned Generator:

If assigned is used user should explicitly pass the primary key value

· Annotation

@Entity

@Table(name = "PRODUCT")

public class ProductSequence

{

@Id

@Column(name = "pid", nullable = false, unique = true)

private Integer pid;

@Column(name = "pname")

private String pname;

@Column(name = "price")

private Double price;

}

· HBM

<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE hibernate-mapping PUBLIC

"-//Hibernate/Hibernate Mapping DTD//EN"

"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

</id>

</class>

</hibernate-mapping>

Sequence Generator:

It will work based on the DB sequence.

Annotations

@Entity

@Table(name = "PRODUCT")

public class ProductIncreament

{

@Id

@Column(name = "pid", unique = true, nullable = false)

@GenericGenerator(name = "mygen1", strategy = "increment")

@GeneratedValue(generator = "mygen1")

private Integer pid;

@Column(name = "pname")

private String pname;

@Column(name = "price")

private Double price;

}

AnnotationConfiguration cfg = new AnnotationConfiguration();

cfg.configure("hibernateAnnotation.cfg.xml");

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

ProductIncreament prod = new ProductIncreament();

prod.setPname("Lux");

prod.setPrice(42.28);

hsession.save(prod);

tx.commit();

hsession.close();

· HBM files

<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE hibernate-mapping PUBLIC

"-//Hibernate/Hibernate Mapping DTD//EN"

"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<class name="com.ibm.pdc.ephs.hibernate.hbm.ProductIncreament"

table="Product">

</id>

</class>

</hibernate-mapping>

Configuration cfg = new Configuration();

cfg.configure("hibernatehbm.cfg.xml");

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

com.ibm.pdc.ephs.hibernate.hbm.ProductIncreament prod = new com.ibm.pdc.ephs.hibernate.hbm.ProductIncreament();

prod.setPname("Lux");

prod.setPrice(42.28);

hsession.save(prod);

tx.commit();

hsession.close();

Hilo:

Hilo is used to generate the hilo values in the data base table. For this create any of the table and insert into some values

CREATE TABLE HILO_TABLE (TEST_HILO number NOT NULL);

by default hilo insert 32767 value.

· Annotations:

@Entity

@Table(name = "PRODUCT")

public class ProductHilo

{

@Id

@Column(name = "pid", unique = true, nullable = false)

@GenericGenerator(name = "table-hilo-generator", strategy = "org.hibernate.id.TableHiLoGenerator", parameters = {

@Parameter(value = "HILO_TABLE", name = "table"), @Parameter(value = "TEST_HILO", name = "column"),

@Parameter(value = "1", name = "NEXT_HI") })

@GeneratedValue(generator = "table-hilo-generator")

private Integer pid;

@Column(name = "pname")

private String pname;

@Column(name = "price")

private Double price;

}

AnnotationConfiguration cfg = new AnnotationConfiguration();

cfg.configure("hibernateAnnotation.cfg.xml");

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

ProductHilo prod = new ProductHilo();

prod.setPname("Lux");

prod.setPrice(42.28);

hsession.save(prod);

tx.commit();

hsession.close();

· HBM files

<?xml version="1.0" encoding="utf-8"?>

<!DOCTYPE hibernate-mapping PUBLIC

"-//Hibernate/Hibernate Mapping DTD//EN"

"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<class name="com.ibm.pdc.ephs.hibernate.hbm.ProductHilo"

table="Product">

<param name="table">HILO_TABLE</param>

</generator>F

</id>

</class>

</hibernate-mapping>

Configuration cfg = new Configuration();

cfg.configure("hibernatehbm.cfg.xml");

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

com.ibm.pdc.ephs.hibernate.hbm.ProductHilo prod = new com.ibm.pdc.ephs.hibernate.hbm.ProductHilo();

prod.setPname("Lux");

prod.setPrice(42.28);

hsession.save(prod);

tx.commit();

hsession.close();

Composite Primary Key:

If a table having more than one primary key then we can call it as a composite primary key. Actually it's not recommended to maintain composite primary keys in DB.

Query:

create table address (aid number, eno number, street varchar2(20), city varchar2(20), state varchar2(20), primary key(aid,eno));

Here address is a composite primary key table and for that we have to create two java classes and one hbm file, java should be bellow. Here ID's class should be implementable Serializable

public class AddressCompositeId implements Serializable

{

private Integer aid;

private Integer eno;

}

public class AddressComposite

{

private AddressCompositeId id;

private String street;

private String state;

private String city;

}

<?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.ibm.pdc.ephs.hibernate.hbm.AddressComposite"

table="ADDRESS">

<composite-id name="id"

class="com.ibm.pdc.ephs.hibernate.hbm.AddressCompositeId">

<key-property name="aid" column="AID" />

<key-property name="eno" column="ENO" />

</composite-id>

</class>

</hibernate-mapping>

Configuration cfg = new Configuration();

cfg.configure("hibernatehbm.cfg.xml");

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

AddressComposite composite = new AddressComposite();

composite.setCity("KADAPA");

composite.setState("AP");

composite.setStreet("NML");

AddressCompositeId id = new AddressCompositeId();

id.setAid(1);

id.setEno(1);

composite.setId(id);

hsession.save(composite);

tx.commit();

hsession.close();

Relationships:

Relationships are 4 types.

1. one to one

2. one to many

3. many to one

4. many to many

Query:

· create table emp(eno number primary key, ename varchar2(20), salary number(20,5));

· create table addr(aid number primary key, eno number references emp(eno), street varchar2(20), city varchar2(20), state varchar2(20));

· SQL> create sequence emp_sequence start with 1 increment by 1;

· SQL> create sequence addr_sequence start with 1 increment by 1;

public class Emp

{

private Integer eno;

private String ename;

private Double salary;

private Set<Addr> addr = new HashSet<Addr>();

}

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

<param name="sequence">EMP_SEQUENCE</param>

</generator>

</id>

<one-to-many class="com.ibm.pdc.ephs.hibernate.hbm.Addr" />

</set>

</class>

</hibernate-mapping>

Here set is used to represent the one to many relation ship

public class Addr

{

private Integer aid;

private String street;

private String city;

private String state;

private Emp emp;

}

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

<param name="sequence">ADDR_SEQUENCE</param>

</generator>

</id>

<many-to-one name="emp" class="com.ibm.pdc.ephs.hibernate.hbm.Emp" column="ENO"></many-to-one>

</class>

</hibernate-mapping>

Here many to one is used to represent the

Main method:

Configuration cfg = new Configuration();

cfg.configure("hibernatehbm.cfg.xml");

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

Emp emp = new Emp();

emp.setEname("Chandra");

emp.setSalary(380000D);

Addr addr = new Addr();

addr.setStreet("Kadapa");

addr.setCity("Kadapa");

addr.setState("AP");

addr.setEmp(emp);

Addr addr1 = new Addr();

addr1.setStreet("Kadapa");

addr1.setCity("Kadapa");

addr1.setState("AP");

addr1.setEmp(emp);

Set<Addr> address = new HashSet<Addr>();

address.add(addr);

address.add(addr1);

emp.setAddr(address);

hsession.save(emp);

hsession.save(addr1);

hsession.save(addr);

tx.commit();

hsession.close();

Here we are saving all the objects and if we save only emp object here it will save only emp object related objects won't save in the Db.

If we want save related objects in Db we have to use cascade atribute

cascade:

This attribute is used to save the related objects in DB.

<one-to-many class="com.ibm.pdc.ephs.hibernate.hbm.Addr" />

</set>

Main:

Configuration cfg = new Configuration();

cfg.configure("hibernatehbm.cfg.xml");

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

Emp emp = new Emp();

emp.setEname("Chandra");

emp.setSalary(380000D);

Addr addr = new Addr();

addr.setStreet("Kadapa");

addr.setCity("Kadapa");

addr.setState("AP");

addr.setEmp(emp);

Addr addr1 = new Addr();

addr1.setStreet("Kadapa");

addr1.setCity("Kadapa");

addr1.setState("AP");

addr1.setEmp(emp);

Set<Addr> address = new HashSet<Addr>();

address.add(addr);

address.add(addr1);

emp.setAddr(address);

hsession.save(emp);

tx.commit();

hsession.close();

This cascade attribute takes the values like all, delete, save-update, save, update and none. default value is none

Inverse:

Inverse is used to save records by using two queries

if inverse is false hibernate first insert data in emp table and addr table then finds the emp table primary key and update the addr table with emp id value.

<one-to-many class="com.ibm.pdc.ephs.hibernate.hbm.Addr" />

</set>

the bellow queries will be executed:

Hibernate: select EMP_SEQUENCE.nextval from dual

Hibernate: select ADDR_SEQUENCE.nextval from dual

Hibernate: insert into emp (ENAME, SALARY, ENO) values (?, ?, ?)

Hibernate: insert into ADDR (STREET, CITY, STATE, ENO, AID) values (?, ?, ?, ?, ?)

Hibernate: update ADDR set ENO=? where AID=?

<one-to-many class="com.ibm.pdc.ephs.hibernate.hbm.Addr" />

</set>

bellow queries will be executed:

Hibernate: select EMP_SEQUENCE.nextval from dual

Hibernate: select ADDR_SEQUENCE.nextval from dual

Hibernate: insert into emp (ENAME, SALARY, ENO) values (?, ?, ?)

Hibernate: insert into ADDR (STREET, CITY, STATE, ENO, AID) values (?, ?, ?, ?, ?)

If inverse is false system inserting data in all the tables and references columns system sending update query

Lazy:

While retrieving data from the data from Db hibernate by default retrieve the all related objects when lazy is false. Default value is false

true

<one-to-many class="com.ibm.pdc.ephs.hibernate.hbm.Addr" />

</set>

Configuration cfg = new Configuration();

cfg.configure("hibernatehbm.cfg.xml");

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

String query = "from Emp";

Query hqlQuery = hsession.createQuery(query);

List<Emp> emps = hqlQuery.list();

for (Emp emp : emps)

{

System.out.println(emp.getEname());

}

tx.commit();

hsession.close();

Query fires to the emp table if lazy is true

Hibernate: select emp0_.ENO as ENO3_, emp0_.ENAME as ENAME3_, emp0_.SALARY as SALARY3_ from emp emp0_

Chandra

false

<one-to-many class="com.ibm.pdc.ephs.hibernate.hbm.Addr" />

</set>

lazy is false at time of list() method fires hibernate send the queries to the related tables

Hibernate: select emp0_.ENO as ENO3_, emp0_.ENAME as ENAME3_, emp0_.SALARY as SALARY3_ from emp emp0_

Hibernate: select addr0_.ENO as ENO1_, addr0_.AID as AID1_, addr0_.AID as AID2_0_, addr0_.STREET as STREET2_0_, addr0_.CITY as CITY2_0_, addr0_.STATE as STATE2_0_, addr0_.ENO as ENO2_0_ from ADDR addr0_ where addr0_.ENO=?

Chandra

Named Queries:

If hbm file which is having any named queries, hibernate converts these named queries to database queries while reading the contents from the hbm file. if we want to retrieve these named queries we have to use the getNamedQueries().

· Annotation

@Entity

@Table(name = "PRODUCT")

@SequenceGenerator(name = "dbSequence", sequenceName = "PRODUCT_SEQ", initialValue = 1, allocationSize = 1)

@NamedQueries({ @NamedQuery(name = "seelct_Product", query = "from ProductNamed") })

public class ProductNamed

{

@Id

@Column(name = "pid", nullable = false, unique = true)

@GeneratedValue(generator = "dbSequence", strategy = GenerationType.SEQUENCE)

private Integer pid;

@Column(name = "pname")

private String pname;

@Column(name = "price")

private Double price;

}

AnnotationConfiguration cfg = new AnnotationConfiguration();

cfg.configure("hibernateAnnotation.cfg.xml");

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

Query hqlQuery = hsession.getNamedQuery("seelct_Product");

List<ProductNamed> list = hqlQuery.list();

for (ProductNamed named : list)

{

System.out.println(named.getPrice());

}

tx.commit();

hsession.close();

· hbm file:

<?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.ibm.pdc.ephs.hibernate.hbm.ProductNamed"

table="Product">

<param name="sequence">PRODUCT_SEQ</param>

</generator>

</id>

</class>

<query name="select_product">from ProductNamed</query>

</hibernate-mapping>

Configuration cfg = new Configuration();

cfg.configure("hibernatehbm.cfg.xml");

SessionFactory sf = cfg.buildSessionFactory();

Session hsession = sf.openSession();

Transaction tx = hsession.beginTransaction();

Query hqlQuery = hsession.getNamedQuery("select_product");

List<com.ibm.pdc.ephs.hibernate.hbm.ProductNamed> list = hqlQuery.list();

for (com.ibm.pdc.ephs.hibernate.hbm.ProductNamed named : list)

{

System.out.println(named.getPrice());

}

tx.commit();

hsession.close();

ORM levels

1. Pure relational (Procedures)

2. Light ORM (JDBC)

3. Medium ORM (JDBC with Procedures)

4. Full ORM (Hibernate)

Hibernate States:

Hibernate states are 3, those are

1. Transient

2. Persistent

3. Detached

When we create object to POJO class then that state is called as transient state

If we attached the object to first level cache then that is called as persistent state

If object is removed from the first level cache then it is detached state

Chandra Blog

Saturday, 14 March 2015

Hibernate 2

HQL:

HQL with Select query:

Positional parameters:

Named Positional parameters:

Criteria:

list():

Native sql:

Hibernate Single ton:

Generators:

Assigned Generator:

</hibernate-mapping>

Sequence Generator:

Hilo:

Composite Primary Key:

Relationships:

Main method:

cascade:

Inverse:

Lazy:

true

false

Named Queries:

ORM levels

Hibernate States:

No comments:

Post a Comment

Blog Archive