Query operations on database data in the Java Hibernate framework _java

The Hibernate Query Language (HQL) is an object-oriented query language, similar to SQL, but not to table and column operations, HQL to persistent objects and their properties. The HQL query is converted from hibernate to a traditional SQL query, which performs operations on the database on the lap.

Although you can use native SQL directly using SQL statements and hibernate, it is recommended that you use HQL to avoid database portability as much as possible and take advantage of the hibernate SQL generation and caching strategy.

are like select,from and where such keywords are case-insensitive, but such as the table name and column name properties are differentiated in hql sensitivity.

From statement
use the FROM clause if you want to load a complete persisted object into memory. The following is a simple syntax that uses the FROM clause:

String hql = "from Employee";
Query query = session.createquery (HQL);
List results = query.list ();

If you need to fully qualify a class name in HQL, simply specify the package and class name as follows:

String hql = "from Com.hibernatebook.criteria.Employee";
Query query = session.createquery (HQL);
List results = query.list ();

As statement
the AS clause can be used to alias an HQL query in a class, especially if there is a long query. For example, the simple example before us is the following:

String hql = "from Employee as E";
Query query = session.createquery (HQL);
List results = query.list ();

The AS keyword is optional, or you can specify an alias directly after the class name, as follows:

String hql = "from Employee E";
Query query = session.createquery (HQL);
List results = query.list ();

SELECT clause
the SELECT clause provides more control over the result set of the FROM clause. Use the SELECT clause if you want to get several properties of an object rather than an entire object. Here is a simple syntax for using the SELECT statement to get the employee object to be just a first_name field:

String hql = "Select E.firstname from Employee E";
Query query = session.createquery (HQL);
List results = query.list ();

It's worth noting that here, Employee.firstname is an attribute of the employee object, not a field in the Employee table.

WHERE clause
If you want to narrow down the specific objects returned from the store, you can use the WHERE clause. The following is a simple syntax that uses the WHERE clause:

String hql = "from Employee E WHERE e.id = ten";
Query query = session.createquery (HQL);
List results = query.list ();

ORDER BY clause
to sort the results of a HQL query, you will need to use the ORDER BY clause. You can sort results in ascending (ASC) or descending order (DESC) in the result set by any property in the object. The following is a simple syntax that uses the ORDER BY clause:

String hql = "from Employee E WHERE e.id > A by e.salary DESC";
Query query = session.createquery (HQL);
List results = query.list ();

If you want to sort by more than one attribute, you will simply add additional attributes to the comma separated by commas, as shown in the end of the command:

String hql = "from Employee E WHERE e.id > ten" +
       "ORDER by E.firstname DESC, E.salary DESC";
Query query = session.createquery (HQL);
List results = query.list ();

GROUP BY clause
This clause allows information to be extracted from Hibernate's database and group based on the value of its properties, and typically uses the result to include the total value. The following is a simple syntax that uses a GROUP BY clause:

String hql = "Select SUM (e.salary), e.firtname from Employee E" +
       "GROUP by E.firstname";
Query query = session.createquery (HQL);
List results = query.list ();

Using named parameters
hibernate named in its hql query parameter support. This makes it easy to write and accept input from the user without the need for SQL injection attacks to defend against HQL queries. The following is a simple syntax for using named arguments:

String hql = "from Employee E WHERE e.id =: employee_id";
Query query = session.createquery (HQL);
Query.setparameter ("employee_id");
List results = query.list ();

UPDATE clause
batch updates are new HQL with Hibernate3, as well as different deletion work, in Hibernate 3 and Hibernate2 alike. The query interface now contains a method named Executeupdate () to execute the HQL update or DELETE statement.

The UPDATE clause can be used to update one or more properties in one or more objects. The following is a simple syntax that uses the UPDATE clause:

String hql = "UPDATE Employee Set salary =: Salary" + 
       "WHERE id =: employee_id";
Query query = session.createquery (HQL);
Query.setparameter ("Salary", 1000);
Query.setparameter ("employee_id");
int result = Query.executeupdate ();
System.out.println ("Rows Affected:" + result);

DELETE clause
the DELETE clause can be used to delete one or more objects. The following is a simple syntax that uses the DELETE clause:

String hql = "DELETE from Employee" + 
       "WHERE id =: employee_id";
Query query = session.createquery (HQL);
Query.setparameter ("employee_id");
int result = Query.executeupdate ();
System.out.println ("Rows Affected:" + result);

INSERT clause
HQL supports inserts into clauses where only records can be inserted from one object to another object. The following is a simple syntax for using the INSERT INTO clause:

String hql = "INSERT into Employee (FirstName, LastName, salary)" + 
       "select FirstName, LastName, salary from Old_employ EE ";
Query query = session.createquery (HQL);
int result = Query.executeupdate ();
System.out.println ("Rows Affected:" + result);

Aggregation method
HQL supports a variety of aggregation methods, similar to SQL. They work in the same way as HQL in SQL and the following list of available features:

The DISTINCT keyword evaluates only the unique values that are set in the row. The following query will only return a unique count:

String hql = "SELECT count (Distinct e.firstname) from Employee E";
Query query = session.createquery (HQL);
List results = query.list ();

Use Query Paging
There are two methods for paging query interfaces.

• Query setfirstresult (int startposition)
• Query setmaxresults (int maxresult)

With both approaches, you can build a paging component in a Web site or swing application. Here is an example that can be extended to get 10 rows:

String hql = "from Employee";
Query query = session.createquery (HQL);
Query.setfirstresult (1);
Query.setmaxresults (ten);
List results = query.list ();

Query criteria
Hibernate provides an action object, followed by an alternate way of data available in the RDBMS table. One approach is the standard API, which allows you to create a standard query object program that can apply filtering rules and logical conditions.
When the session interface for Hibernate provides an instance of a class that can be used to create a returned persisted object, the application executes a condition to query the criteria Object Createcriteria () method.

The simplest example of a conditional query is to simply return each object that corresponds to the employee class.

Criteria cr = Session.createcriteria (employee.class);
List results = cr.list ();

Limitations and standards:
You can use the Add () method to add a restricted condition query to the criteria object. Here is an example of adding a limit to the salary returned by the record is equal to 2000:

Criteria cr = Session.createcriteria (employee.class);
Cr.add (Restrictions.eq ("salary");
List results = cr.list ();

Here are a few examples that cover a different scenario and can be used on request:

Criteria cr = Session.createcriteria (Employee.class);

To get records has salary more than Cr.add (restrictions.gt ("Salary", 2000));

To get records has salary less than Cr.add (restrictions.lt ("Salary", 2000));

To-Get records has fistname starting with Zara Cr.add (restrictions.like ("FirstName", "zara%"));
Case sensitive form of the above restriction.

Cr.add (Restrictions.ilike ("FirstName", "zara%"));

To get records has salary in between 1000 and Cr.add (Restrictions.between ("salary", 1000, 2000));

To check if the given property is null Cr.add (Restrictions.isnull ("salary"));

To check if the given property is not null Cr.add (Restrictions.isnotnull ("salary"));

To check if the given property is empty Cr.add (Restrictions.isempty ("salary"));
To check if the given property isn't empty Cr.add (Restrictions.isnotempty ("salary"));

You can create and or or use LogicalExpression to restrict the following criteria: Criteria CR = Session.createcriteria (Employee.class); Criterion Salary = restrictions.gt ("Salary", 2000);

Criterion name = Restrictions.ilike ("Firstnname", "zara%");
To get records matching with OR condistions logicalexpression orexp = restrictions.or (salary, name);


Cr.add (OREXP);
To get records matching with and condistions logicalexpression andexp = Restrictions.and (salary, name);

Cr.add (ANDEXP);

 List results = cr.list ();

Although all of the above conditions can be directly used by HQL in the previous tutorial.

Paging Usage criteria:
There are also standard interfaces for pagination in two ways.

• Public Criteria setfirstresult (int firstresult)
• Public Criteria setmaxresults (int maxresults)

With both of these approaches, we can build a paging component on our site or swing application. Here is an example that can be extended to get 10 rows at a time:

Criteria cr = Session.createcriteria (employee.class);
Cr.setfirstresult (1);
Cr.setmaxresults (ten);
List results = cr.list ();

The result of the sort:
The standard API provides the Org.hibernate.criterion.Order class sort to arrange your result set in ascending or descending order, depending on the object's properties. This example shows how to sort by using the result set of the Order class:

Criteria cr = Session.createcriteria (employee.class);
To get records has salary more than
Cr.add (restrictions.gt ("salary",));

To sort records in descening order
Crit.addorder (Order.desc ("salary"));

To sort records in ascending order
Crit.addorder (ORDER.ASC ("salary"));

List results = cr.list ();

Prediction and Aggregation:
The criteria API provides an attribute value that the Org.hibernate.criterion.Projections class can use to obtain an average value, a maximum or a minimum.  The projections class is similar to a class limit because it provides several static factory methods for obtaining projection instances. Provides the

The following are some examples of different scenarios that can be used as specified:

Criteria cr = Session.createcriteria (employee.class);

To get Total row count.
Cr.setprojection (Projections.rowcount ());

To get average of the A property.
Cr.setprojection (Projections.avg ("salary"));

To get distinct the count of a property.
Cr.setprojection (projections.countdistinct ("FirstName"));

To get maximum of the A property.
Cr.setprojection (Projections.max ("salary"));

To get minimum of the A property.
Cr.setprojection (Projections.min ("salary"));

To get sum of the A property.
Cr.setprojection (Projections.sum ("salary"));

Criteria Queries Example:
Consider the following Pojo class:

public class Employee {
  private int id;
  Private String firstName; 
  Private String lastName;  
  private int salary; 

  Public employee () {} public
  employee (string fname, string lname, int salary) {
   this.firstname = fname;
   This.lastname = lname;
   This.salary = salary;
  }
  public int getId () {return
   ID;
  }
  public void setId (int id) {
   this.id = ID;
  }
  Public String Getfirstname () {return
   firstName;
  }
  public void Setfirstname (String first_name) {
   this.firstname = first_name;
  }
  Public String Getlastname () {return
   lastName;
  }
  public void Setlastname (String last_name) {
   this.lastname = last_name;
  }
  public int getsalary () {return
   salary;
  }
  public void setsalary (int salary) {
   this.salary = salary;
  }
}

Let's create the following employee table to store the Employee object:

CREATE TABLE EMPLOYEE (
  ID INT not NULL auto_increment,
  first_name VARCHAR () default null,
  last_name varcha R () default NULL,
  salary   INT default null,
  PRIMARY KEY (ID)
);

The following will be mapped to the file.

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

 
 

Finally, we will create the application class's main () method to run, and we will use the criteria query application:

Import java.util.List;
Import Java.util.Date; 
 
Import Java.util.Iterator; 
Import org.hibernate.HibernateException; 
Import org.hibernate.Session;
Import org.hibernate.Transaction;
Import Org.hibernate.SessionFactory;
Import Org.hibernate.Criteria;
Import org.hibernate.criterion.Restrictions;
Import org.hibernate.criterion.Projections;

Import org.hibernate.cfg.Configuration; 
  public class Manageemployee {private static sessionfactory factory;
   public static void Main (string[] args) {try{factory = new Configuration (). Configure (). Buildsessionfactory ();
     }catch (Throwable ex) {System.err.println ("Failed to create Sessionfactory object." + ex); 
   throw new Exceptionininitializererror (ex);

   Manageemployee ME = new Manageemployee ();
   /* ADD few employee records in database */Integer empID1 = Me.addemployee ("Zara", "Ali", 2000);
   Integer empID2 = Me.addemployee ("Daisy", "Das", 5000);
   Integer empID3 = Me.addemployee ("John", "Paul", 5000); INteger empID4 = Me.addemployee ("Mohd", "Yasee", 3000);

   /* List down all the employees * * Me.listemployees ();

   /* Print Total Employee ' s count */Me.countemployee ();
  /* Print Toatl Salary * * me.totalsalary (); }/* method to CREATE a employee in the database/public Integer AddEmployee (String fname, string lname, int salary
   {Session session = Factory.opensession ();
   Transaction tx = NULL;
   Integer EmployeeID = null;
     try{tx = Session.begintransaction ();
     Employee Employee = new Employee (fname, lname, salary); 
     EmployeeID = (Integer) session.save (employee);
   Tx.commit ();
     }catch (hibernateexception e) {if (tx!=null) Tx.rollback (); 
   E.printstacktrace (); 
   }finally {session.close ();
  return EmployeeID; }/* method to READ all the employees has salary more than/public void Listemployees () {session Sessio
   n = factory.opensession ();
   Transaction tx = NULL; try{tx = Session.begiNtransaction ();
     Criteria cr = Session.createcriteria (Employee.class);
     ADD restriction.
     Cr.add (restrictions.gt ("Salary", 2000));

     List employees = cr.list (); for (Iterator iterator = Employees.iterator (); Iterator.hasnext ();) {Employee employee = (Employee) I 
      Terator.next (); 
      System.out.print ("Name:" + employee.getfirstname ()); 
      System.out.print ("Last Name:" + employee.getlastname ()); 
     System.out.println ("Salary:" + employee.getsalary ());
   } tx.commit ();
     }catch (hibernateexception e) {if (tx!=null) Tx.rollback (); 
   E.printstacktrace (); 
   }finally {session.close (); }/* method to print total number of records */public void Countemployee () {Session session = Factory.opensess
   Ion ();
   Transaction tx = NULL;
     try{tx = Session.begintransaction ();

     Criteria cr = Session.createcriteria (Employee.class);
     To get Total row count. Cr.setprojection (ProjEctions.rowcount ());

     List rowcount = Cr.list ();
     System.out.println ("Total coint:" + rowcount.get (0));
   Tx.commit ();
     }catch (hibernateexception e) {if (tx!=null) Tx.rollback (); 
   E.printstacktrace (); 
   }finally {session.close ();
   }/* method to print sum of salaries */public void Totalsalary () {Session session = Factory.opensession ();
   Transaction tx = NULL;
     try{tx = Session.begintransaction ();

     Criteria cr = Session.createcriteria (Employee.class);
     To the total salary.
     Cr.setprojection (Projections.sum ("salary"));

     List totalsalary = Cr.list ();
     System.out.println ("Total Salary:" + totalsalary.get (0));
   Tx.commit ();
     }catch (hibernateexception e) {if (tx!=null) Tx.rollback (); 
   E.printstacktrace (); 
   }finally {session.close ();

 }
  }
}

Compiling and executing:
Here is a step to compile and run the above application. Make sure that you have set up path and classpath appropriately before compiling and executing.

• Create a configuration section explanation in the Hibernate.cfg.xml configuration file.
• Create the Employee.hbm.xml mapping file, as shown in the previous illustration.
• Create the Employee.java source file, as shown in the figure above, and compile it.
• Create the Manageemployee.java source file, as shown in the figure above, and compile it.
• Executes the Manageemployee binary run program.

The following results are obtained, and records are created in the employee table.

$java Manageemployee

....... Various LOG MESSAGES would DISPLAY

here ... Name:daisy last Name:das salary:5000 a
name:john last Name:paul salary:5000 A-
NAME:MOHD Name:yasee salary:3000 Total
coint:4 Total
salary:15000

If you check the employee table, it should be recorded as follows:

Mysql> select * from EMPLOYEE;

 +----+------------+-----------+--------+ | id | first_name | last_name | salary | +---- +------------+-----------+--------+
| 14 | Zara |  Ali | 2000 | | 15 | Daisy |  Das | 5000 | | 16 | John |  Paul | 5000 | | 17 | Mohd |  Yasee |
3000 | +----+------------+-----------+--------+ 4 rows in Set (0.00 sec)