Hibernate Query Language-Where clause

Hibernate Query Language-where clause

Use:

We get records depending upon the given condition using where clause.

Problem:

To get student name who have passed in tamil.

We assume that the pass mark is above 50.

Procedure:

Note:

Already we have created project named as HibernateProject. Also we have created Folder for storing jsp and Hibernate files.

Step 1:

            Create Hibernate Reverse Engineering wizard

Step 2:

            Create HibernateUtil.java

Step 3:

            Create POJO class and mapping files

Step 4:           

            Create jsp file named as WhereExam

Include following coding into WhereExam

< %@page contentType="text/html" pageEncoding="UTF-8"% >
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
http://www.w3.org/TR/html4/loose.dtd >
< %@page import="org.hibernate.SessionFactory" % >
< %@page import="org.hibernate.Session" % >
< %@page import="org.hibernate.cfg.Configuration" % >
< %@page import="org.hibernate.*" % >
< %@page import="java.util.*" % >
< %@page import="Hiber.Student" % >
< html >
< head >
< title > Hibernate where clause Example < /title >
< /head >
< body >

< %
try
{
SessionFactory sessionFactory=new Configuration().configure().buildSessionFactory();
Session s=sessionFactory.openSession();
String sql="select stuname from Student student where tamil>50";

Query query=s.createQuery(sql);
List list=query.list();
out.println("Students who have passed in Tamil/n");
for(int i=0;i="" <=""

Check the result

Open mysql

Given below query

mysql > select stuname from Student where tamil>50;

Hibernate Query Language-Aggregate function-avg()

Hibernate Query Language-Aggregate function-avg()

Use:

Sometime we want to get average value from table,at that time we have to use avg(aggregate function).

Problem:

            To get average mark of tamil from student table in navalady database.

Procedure:

Note:

Already we have created project named as HibernateProject. Also we have created Folder for storing jsp and Hibernate files.

Step 1:

            Create Hibernate Reverse Engineering wizard

Step 2:

            Create HibernateUtil.java

Step 3:

            Create POJO class and mapping files

Step 4:           

            Create jsp file named as AvgExam

Include following coding into AvgExam

< %@page contentType="text/html" pageEncoding="UTF-8"% >
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
http://www.w3.org/TR/html4/loose.dtd >
< %@page import="org.hibernate.SessionFactory" % >
< %@page import="org.hibernate.Session" % >
< %@page import="org.hibernate.cfg.Configuration" % >
< %@page import="org.hibernate.*" % >
< %@page import="java.util.*" % >
< %@page import="Hiber.Student" % >
< html >
< head >
< title > Hibernate Avg(aggregate function)Example < /title >
< /head >
< body >

< %
try
{
SessionFactory sessionFactory=new Configuration().configure().buildSessionFactory();
Session s=sessionFactory.openSession();
String sql="select avg(tamil) from Student student";

Query query=s.createQuery(sql);
List list=query.list();
out.println("Getting average TAMIL Mark : "+list.get(0));

}
catch(Exception e)
{
out.println(e);
}
% >
< /body >
< /html >

Output:

Check the result

Open mysql

Given below query

mysql > select avg(tamil) from student;

Hibernate Query Language-Aggregate function-min()

Hibernate Query Language-Aggregate function-min

Use:

Sometime we want to get lowest value from table,at that time we have to use max(aggregate function).

Problem:

            To get minimum mark of tamil from student table in navalady database.

Procedure:

Note:

Already we have created project named as HibernateProject. Also we have created Folder for storing jsp and Hibernate files.

Step 1:

            Create Hibernate Reverse Engineering wizard

Step 2:

            Create HibernateUtil.java

Step 3:

            Create POJO class and mapping files

Step 4:           

            Create jsp file named as MaxExam

Include following coding into MaxExam

< %@page contentType="text/html" pageEncoding="UTF-8"% >
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
http://www.w3.org/TR/html4/loose.dtd >
< %@page import="org.hibernate.SessionFactory" % >
< %@page import="org.hibernate.Session" % >
< %@page import="org.hibernate.cfg.Configuration" % >
< %@page import="org.hibernate.*" % >
< %@page import="java.util.*" % >
< %@page import="Hiber.Student" % >
< html >
< head >
< title > Hibernate Min(aggregate function)Example < /title >
< /head >
< body >

< %
try
{
SessionFactory sessionFactory=new Configuration().configure().buildSessionFactory();
Session s=sessionFactory.openSession();
String sql="select min(tamil) from Student student";

Query query=s.createQuery(sql);
List list=query.list();
out.println("Getting Minimum TAMIL Mark : "+list.get(0));

}
catch(Exception e)
{
out.println(e);
}
% >
< /body >
< /html >

Output:

Check the result

Open mysql

Given below query

mysql > select * from Student;

Hibernate Query Language-max()

Hibernate Query Language-Aggregate function-max

Use:

Sometime we want to get max value from table,at that time we have to use max(aggregate function).

Problem:

            To get maximum mark of mathematics from student table in navalady database.

Procedure:

Note:

Already we have created project named as HibernateProject. Also we have created Folder for storing jsp and Hibernate files.

Step 1:

            Create Hibernate Reverse Engineering wizard

Step 2:

            Create HibernateUtil.java

Step 3:

            Create POJO class and mapping files

Step 4:           

            Create jsp file named as MaxExam

Include following coding into MaxExam

< %@page contentType="text/html" pageEncoding="UTF-8"% >
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
http://www.w3.org/TR/html4/loose.dtd >
< %@page import="org.hibernate.SessionFactory" % >
< %@page import="org.hibernate.Session" % >
< %@page import="org.hibernate.cfg.Configuration" % >
< %@page import="org.hibernate.*" % >
< %@page import="java.util.*" % >
< %@page import="Hiber.Student" % >
< html >
< head >
< title > Hibernate Max(aggregate function)Example < /title >
< /head >
< body >

< %
try
{
SessionFactory sessionFactory=new Configuration().configure().buildSessionFactory();
Session s=sessionFactory.openSession();
String sql="select max(tamil) from Student student";

Query query=s.createQuery(sql);
List list=query.list();
out.println("Getting Maximum TAMIL Mark : "+list.get(0));

}
catch(Exception e)
{
out.println(e);
}
% >
< /body >
< /html >

output:

Check the result

Open mysql

Given below query

mysql > select * from Student;

Hibernate Query Language-count

Hibernate Query Language-count(Aggregate function)
Creating DataBase using Mysql


DataBase Name: Navalady

Table Name: Student

Open MYSQL command line Client


Step 1:

Create Database named as Navalady.

Syntax:

mysql > create Database Name;

mysql > create Navalady;

Step 2:

To use Created Database. We use the following statement.

Syntax:

mysql > use Database Name;

mysql > use Navalady;

Step 3:

Create Table named as Student.

Mysql > create table table_name(field_name datatypes,filed_name datatypes….);

Mysql > create table Student(stuname varchar(40),stuno int(20),tamil int(20),English int(20),maths int(20), science int(20),socialscience int(20),primary key(stuno));

Step 4:

Insert the records into Student table.

We have to insert five records.

Hibernate Query Language-Aggregate function-count.

There are many aggregate function available in hibernate. Such as count, sum, max, min etc,

Use:

            Sometimes we want to get number of rows. At that time we use count() function to get total number of rows depending upon condition.

Example:

            Already we have created database as Navalady and table name is Student.

            Now we have to get how many students get hundred marks in mathematics.

Query is select count(*) from Student where maths=”100”;

Procedure:

Note:
· Already we have created project named as HibernateProject.· Also we have created Folder for storing jsp and Hibernate files.


Step 1:

            Create Hibernate Reverse Engineering wizard

Step 2:

             Create HibernateUtil.java

Step 3:
             
               Create POJO class and mapping files

Step 4: 

                 Create jsp file named as CountExam

Include following coding into CountExam

< %@page contentType="text/html" pageEncoding="UTF-8"% >
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
http://www.w3.org/TR/html4/loose.dtd >
< %@page import="org.hibernate.SessionFactory" % >
< %@page import="org.hibernate.Session" % >
< %@page import="org.hibernate.cfg.Configuration" % >
< %@page import="org.hibernate.*" % >
< %@page import="java.util.*" % >
< %@page import="Hiber.Student" % >
< html >
< head >
< title > Hibernate count(aggregate function)Example < /title >
< /head >
< body >
< %

try
{
SessionFactory sessionFactory=new Configuration().configure().buildSessionFactory();
Session s=sessionFactory.openSession();
String sql="select count(*) from Student student where maths=100";
Query query=s.createQuery(sql);
Iterator it=query.iterate();

while(it.hasNext())
{
out.println("Total Studnet get 100 in maths "+it.next());
}
s.close();
}
catch(Exception e)
{
out.println(e);
}
% >
< /body >
< /html >

OUTPUT:

The output is 0.

Because nobody get hundreds in mathematic subject.

Insert new records into the student table in navalady database;

Mysql > insert into Student values(‘Mahinda’,7,66,77,100,100,100);

Mysql > insert into Student values(‘Maahi’,8,66,77,100,100,100);

And Run the CountExam.jsp again.

Hibernate Query Language-Select Clause

Hibernate:

AIM:

            We are going to select the record from table using select clause.

Syntax:

            Select employee.empname,employee.empno from Employee employee;

Procedure:

Step 1:

            Create Hibernate Reverse Engineering wizard

Step 2:

            Create HibernateUtil.java

Step 3:

            Create POJO class and mapping files

Step 4:           

            Create jsp file named as SelectClause

Include following coding into SelectClause

< %@page contentType="text/html" pageEncoding="UTF-8"% >
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
http://www.w3.org/TR/html4/loose.dtd >
< %@page import="org.hibernate.SessionFactory,org.hibernate.Session" % >
< %@page import="org.hibernate.cfg.Configuration" % >
< %@page import="org.hibernate.*" % >
< %@page import="Hiber.Employee" % >
< %@page import="java.util.*" % >
< html >
< head >
< title > Hibernate-using select clause < /title >
< /head >
< body >
< %
try
{
SessionFactory sessionFactory=new Configuration().configure().buildSessionFactory();
Session s=sessionFactory.openSession();

String sql="select employee.empname,employee.empno from Employee employee";
Query query=s.createQuery(sql);

Iterator it=query.iterate();
% >
< table border="5" >
< caption > < b > Example For Hibernate- Select Clause < /b > < /caption >
< tr > < th > EmployeeName < /th > < th > EmployeeID < /th > < /tr >
< %
while(it.hasNext())
{
Object[] obj=(Object[])it.next();
% >
< tr >
< td >
< %
out.println(obj[0]);
% >
< /td >
< td >
< %
out.println(obj[1]);
% >
< /td >
< /tr >
< %

}
% >
< /table >
< %
}
catch(Exception e)
{
out.println(e);
}
% >
< /body >
< /html >

Output:

Structure Of Hibername Select Clause

Hibernate Query Language- From Clause

Hibernate Query Language (HQL)

AIM:

            We use from clause in hibernate to select records from database

Syntax

From Employee employee;

Procedure:

Step 1:

            Create Hibernate Reverse Engineering wizard

Step 2:

            Create HibernateUtil.java

Step 3:

            Create POJO class and mapping files

Step 4:

            Create jsp file named as FromClause

           Include following coding into FromClause

< %@page import="org.hibernate.SessionFactory,org.hibernate.Session" % >
< %@page import="org.hibernate.cfg.Configuration,org.hibernate.*" % >
< %@page import="Hiber.Employee" % >
< %@page import="java.util.*" % >
< %@page contentType="text/html" pageEncoding="UTF-8"% >
< !DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
http://www.w3.org/TR/html4/loose.dtd >

< html >
< head >
< title > JSP Page < /title >
< /head >
< body >
< %
try
{
SessionFactory sessionFactory=new Configuration().configure().buildSessionFactory();
Session s=sessionFactory.openSession();

String sql="from Employee employee";

Query query=s.createQuery(sql);
Iterator it=query.iterate();
% >
< table border="5" >
< caption > < b > Example For Hibernate- From Clause < /b > < /caption >
< tr > < th > EmployeeName < /th > < th > EmployeeID < /th > < /tr >
< %
while(it.hasNext())
{
Employee e=(Employee)it.next();
% >
< tr >
< td > < %= e.getEmpname() % > < /td >
< td > < %= e.getEmpno() % > < /td >
< /tr >

< %
}
% >
< /table >
< %
}
catch(Exception e)
{
out.println(e);
}
% >
< /body >
< /html >

Step 5:

               Run the FromClause.

               Right click on FromClause.jsp and select Run

Step 6:

Structure Of the Hibernate-From Clause