Preparing table for HQL Examples

In this lesson we will create insurance table and populate it with the data. We will use insurance table for rest of the HQL tutorial.

To create the insurance table and insert the sample data, run the following sql query:


/*Table structure for table `insurance` */

drop table if exists `insurance`;

CREATE TABLE `insurance` (
  `ID` int(11) NOT NULL default '0',
  `insurance_name` varchar(50) default NULL,
  `invested_amount` int(11) default NULL,
  `investement_date` datetime default NULL,
  PRIMARY KEY  (`ID`)
) TYPE=MyISAM;

/*Data for the table `insurance` */


insert into `insurance` values 
(1,'Car Insurance',1000,'2005-01-05 00:00:00');
insert into `insurance` values 
(2,'Life Insurance',100,'2005-10-01 00:00:00');
insert into `insurance` values 
(3,'Life Insurance',500,'2005-10-15 00:00:00');
insert into `insurance` values 
(4,'Car Insurance',2500,'2005-01-01 00:00:00');
insert into `insurance` values 
(5,'Dental Insurance',500,'2004-01-01 00:00:00');
insert into `insurance` values
 (6,'Life Insurance',900,'2003-01-01 00:00:00');
insert into `insurance` values 
(7,'Travel Insurance',2000,'2005-02-02 00:00:00');
insert into `insurance` values 
(8,'Travel Insurance',600,'2005-03-03 00:00:00');
insert into `insurance` values 
(9,'Medical Insurance',700,'2005-04-04 00:00:00');
insert into `insurance` values 
(10,'Medical Insurance',900,'2005-03-03 00:00:00');
insert into `insurance` values 
(11,'Home Insurance',800,'2005-02-02 00:00:00');
insert into `insurance` values 
(12,'Home Insurance',750,'2004-09-09 00:00:00');
insert into `insurance` values 
(13,'Motorcycle Insurance',900,
'2004-06-06 00:00:00');
insert into `insurance` values 
(14,'Motorcycle Insurance',780
,'2005-03-03 00:00:00');

Above Sql query will create insurance table and add the following data:

ID	insurance_name	invested_amount	investement_date
1	Car Insurance	1000	2005-01-05 00:00:00
2	Life Insurance	100	2005-10-01 00:00:00
3	Life Insurance	500	2005-10-15 00:00:00
4	Car Insurance	2500	2005-01-01 00:00:00
5	Dental Insurance	500	2004-01-01 00:00:00
6	Life Insurance	900	2003-01-01 00:00:00
7	Travel Insurance	2000	2005-02-02 00:00:00
8	Travel Insurance	600	2005-03-03 00:00:00
9	Medical Insurance	700	2005-04-04 00:00:00
10	Medical Insurance	900	2005-03-03 00:00:00
11	Home Insurance	800	2005-02-02 00:00:00
12	Home Insurance	750	2004-09-09 00:00:00
13	Motorcycle Insurance	900	2004-06-06 00:00:00
14	Motorcycle Insurance	780	2005-03-03 00:00:00

In the future lessons we will use this table to write different HQL examples.

� View all related tutorials

Current Comments

13 comments so far (post your own) View All Comments Latest 10 Comments:

How can i select the data using hql from the table contains multiple primary keys

Posted by nagendra on Wednesday, 10.15.08 @ 10:24am | #81090

View This Comment Separately

can i use sun.jdbc.odbc.JdbcOdbcDriver for hibernate

Posted by tapan on Friday, 12.14.07 @ 13:48pm | #42227

View This Comment Separately

hi,

i want to know where the most of collection interface and reflection-api is used and plz send me some examples for collection and reflection-api interface along with materilas

Posted by allabakash on Wednesday, 10.17.07 @ 12:00pm | #34259

View This Comment Separately

hi,

one thing i want to know that most of the hibernate applications run in my eclipse i want to run my application in tomcat or weblogic will u tell me how hiberante applications runs in tomcat or weblogic plz give me a small example for this

Posted by sreenivaasarao.s on Wednesday, 10.17.07 @ 11:57am | #34258

View This Comment Separately

iam new to hibernate techonolgy one thing to know that

in hibernate if we want to map the mapping between two tables then how to configure data(i mean where to map)will u plz give one with example

Posted by sreenivaasarao.s on Wednesday, 10.17.07 @ 11:50am | #34257

View This Comment Separately

Dear i m new in hibernate technology. now i am trying to delete data from my table but it is not working. i tried to delete by the following example test

package roseindia.tutorial.hibernate;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class DeleteHQLExample {
/**
* @author vinod Kumar
*
* http://www.roseindia.net Hibernate Criteria Query Example
*
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Session sess = null;
try {
SessionFactory fact = new Configuration().configure().buildSessionFactory();
sess = fact.openSession();
String hql = "delete from Insurance insurance where id = 1";
Query query = sess.createQuery(hql);
int row = query.executeUpate();
if (row == 0){
System.out.println("Doesn't deleted any row!");
}
else{
System.out.println("Deleted Row: " + row);
}
sess.close();
}
catch(Exception e){
System.out.println(e.getMessage());
}
}
}

please help me hoe can i delete data in hibernate

Posted by akter on Thursday, 10.4.07 @ 11:00am | #31348

View This Comment Separately

I am new for the Hibernate Technology.
I have a 2 tables called 'bank' and 'branch' one to many relationship.
When I delete a bank details then It want to check related branches.

So, how can I implement this delete function

pls help any one

Posted by nayeem on Friday, 09.28.07 @ 15:20pm | #30146

View This Comment Separately

"SELECT sum(sessioncount) from sta_attendancedetail sad,sta_attendancemaster sam where staffid=? and reason='CASUAL LEAVE' and year(absentdt)=year(?)and sad.attid=sam.attid"

How i convert SQL to HQL?

plz help me.....

thanks
Mohan

Posted by mohan on Wednesday, 06.27.07 @ 17:32pm | #20281

View This Comment Separately

its a gr8 site....... whenever i need help i turn to roseindia.........

Posted by Som on Tuesday, 05.22.07 @ 10:50am | #16977

View This Comment Separately

I have tables like
=>Country_Master
Country_Id
Name
=>State_Master
State_Id
State_Name
Country_Id
=>Town_Master
Town_Id
Town_Name
State_Id

Like this many hierarchy.

I want to know how i will get the selected fields from table.
SQL: select Name,State_Name,Town_Name from Country_Master C,State_Master S, Town_Master T where S.Country_Id=C.Country_Id and T.State_Id=S.State_Id

I want to know how i will get information.

Posted by Prabhat Kumar Krishna on Friday, 05.4.07 @ 13:06pm | #15260

View This Comment Separately