Mysql- using Date function.

introduction:

                     we have to handle Date in our program. So we need to include Date function into our table student.

Step 1:

            Create Database I have created database as Navalady.

Note: No need to create Database. Because we have already created Database named as Navalady)

Step 2:

            Already we have created table name as student. So also no need to create table.

Step 3:

We have to use the date in our program. So we include joining date of student into our table.

How to add new field into our existing table?

Mysql > alter table table_name ADD(field_name field_type);

Mysql > alter table student ADD(joindate Date);

Step 4:

                Insert new records into student table.

Step 5:

Insert joining date into already include student.

Successfully we have included the join date to student.

Hibernate Criteria Query-BetweenDate

AIM:

                        From this post we will learn how to use betweenDate to get result.

Problem:

                        To get student who get admission between two dates.

Solution:

Made some changes in student table.

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 BetweenDate.jsp

Include following coding into the BetweenDate.jsp

< %@page import="java.util.*"% >
< %@page import="org.hibernate.*" % >
< %@page import="org.hibernate.cfg.Configuration" % >
< %@page import="org.hibernate.cfg.*" % >
< %@page import="org.hibernate.criterion.*" % >
< %@page import="Hiber.Student" % >

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

Criteria crit=s.createCriteria(Student.class);

Date startDate=new Date("1999/06/22");
Date endDate=new Date("1999/06/26");

crit.add(Expression.between("joindate", startDate,endDate));

crit.setMaxResults(4);
List list=crit.list();
Iterator iterator=list.iterator();
while(iterator.hasNext())
{
Student ss=(Student)iterator.next();
out.println(ss.getStuname());
}

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


Output:

To check manually using mysql query.

mysql > select stuname from student where joindate between ('1999/06/22') and (‘1999/06/26’);

Hibernate Criteria Query-GreaterThan

Hibernate Criteria Expression- greater than

Aim:

            To learn how to use greater than in our hibernate.

Problem:

To get stuname from table student in navalady database who get greater than 70 marks in tamil using greater than criteria expression.

Solution:

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 GreaterThan.jsp

Include following coding into the GreaterThan.jsp

Hibernate Criteria Query-LessthanEqual

Hibernate Criteria Expression- lessthan

Aim:

            To learn how to use lessthan in our hibernate.

Problem:

To get stuname from table student in navalady database who get less than 70 marks in tamil using lessthan criteria expression.

Solution:

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 LessThanEx.jsp

Include following coding into the LessThanEx.jsp

Hibernate Criteria Query-Between

Hibernate Criteria Query-Between

AIM:

                        From this post we will learn how to use between to get result.

Problem:

                        To get English marks between 60 and 75.

Solution:

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 BetweenInteger.jsp

Include following coding into the BetweenInteger.jsp

< %@page import="java.util.*"% >
< %@page import="org.hibernate.*" % >
< %@page import="org.hibernate.cfg.Configuration" % >
< %@page import="org.hibernate.cfg.*" % >
< %@page import="org.hibernate.criterion.*" % >
< %@page import="Hiber.Student" % >

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

Criteria crit=s.createCriteria(Student.class);
crit.add(Expression.between("english", new Integer(50),new Integer(80)));
crit.setMaxResults(4);
List list=crit.list();
Iterator iterator=list.iterator();
while(iterator.hasNext())
{
Student ss=(Student)iterator.next();
out.println(ss.getEnglish());
}

}
catch(Exception e)
{

}
% >
< /body >
< /html >

Output:

If you want to check the result is right or wrong.

Goto the mysql

Give the following query

Hibernate Criteria Query

·        Criteria interface allows us to create and execute object-oriented queries.

·        The interface org.hibernate.Criteria is used to create the criterion for the search.

·        Hibernate Criteria Query is better than Hibernate Query Language (HQL).

·        Used to get all records from table without query.

Problem:

              We are going to select all records from table student in navalady database.

Solution:

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 CriteriaExample.jsp

Include following coding into the CriteriaExample.jsp

< %@page contentType="text/html" pageEncoding="UTF-8"% >

< %@page import="Hiber.Student"% >
< %@page import="org.hibernate.*" % >
< %@page import="org.hibernate.cfg.Configuration" % >
< %@page import="java.util.*" % >
< html >
< head >
< title > Criteria Example < /title >
< /head >
< body >
< h1 > Navaladiyan College < /h1 >
< %
try
{
SessionFactory sessionFactory=new Configuration().configure().buildSessionFactory();
Session s=sessionFactory.openSession();

Criteria crit=s.createCriteria(Student.class);

List list=crit.list();

Iterator it=list.iterator();
% >
< table border="1" >
< caption > Student Records < /caption >
< tr >
< td > Student Name < /td >
< td > Student No < /td >
< td > Tamil < /td >
< td > English < /td >
< td > Mathematics < /td >
< td > Science < /td >
< td > Social Science < /td >
< /tr >
< %
while(it.hasNext())
{
Student stu=(Student)it.next();
out.println(" < tr > < td > "+stu.getStuname()+" < /td > ");

out.println(" < td > "+stu.getStuno()+" < /td > ");

out.println(" < td > "+stu.getTamil()+" < /td > ");

out.println(" < td > "+stu.getEnglish()+" < /td > ");

out.println(" < td > "+stu.getMaths()+" < /td > ");

out.println(" < td > "+stu.getScience()+" < /td > ");

out.println(" < td > "+stu.getSocialscience()+" < /td > ");

}
% >
< /table >
< %

}
catch(Exception e)
{

}
% >
< /body >
< /html >

Step 5:

            Run the program CriteriaExample.jsp

 

Step 6:

            Get the Output

If you want to check the result is right or wrong.

Goto the mysql

Give the following query

Mysql > select * from student;

Thank you

S.Sureshkumar, M.Sc, M.Phil.

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;