Simple Sightseeing Application Part 2

In the part 1, we have seen how to work on sight seeing application locally by having some
static values. In this part, we will see how we can connect to the database using hibernate and
retrive the values from the table





Now we will connect with mysql using Hibernate 4.1.1

Step : 1

First let us create a table in mysql as follows
Database Name : sampledb
Table Name    : resort
Script

CREATE TABLE `resort`
(
 
`ID` INT(11) NOT NULL AUTO_INCREMENT,
`_description` VARCHAR(255) DEFAULT NULL,
`_latitude` DOUBLE NOT NULL,
`_longitude` DOUBLE NOT NULL,
`_name` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY  (`ID`)
)
ENGINE=INNODB DEFAULT CHARSET=latin1

Insert the following values

INSERT INTO resort(_description,_latitude,_longitude,_name)
VALUES( 'Insolite ! D\351couvrez la Tour Eiffel autrement.','48.858559939099997D', '2.2945332527D', 'La Tour Eiffel')

INSERT INTO resort(_description,_latitude,_longitude,_name)
VALUES('Buckingham Palace is the official London residence of the sovereign.','51.501369818199997D',
'-0.14166891570000001D', 'Buckingham Palace')

Step 2:

If you are new to Hibernate, i would suggest first read the following posts


Hibernate Introduction
http://emrpms.blogspot.in/search/label/Hibernate%20Introduction

Set up Hibernate Environment

1. Hibernate 4.1.1
2. JDBC Driver for Mysql ((mysql-connector-java-5.1.19.zip))


Unzip the hibernate buddle and you should have the following structure after you unzip

Documentation folder
lib Folder
Project folder
log text file
hibernate logo image
license text file

1. Right Click on Resorthib project in the eclipse folder structure
2. Select New-> Folder.
3. Give the folder Name as lib

Now copy the following files into lib folder
(Eclipse project explorer accepts all copy and paste operation. For example, you can copy file  or folder in windows explorer and then you can come back to eclipse project explorer and paste    it)

All the files in Hibernate->Lib->required.
  All the files in Hibernate->Lib->jpa.
  All the files in Hibernate->Lib->envers
  mysql-connector-java-5.1.18-bin.jar


Now we need to setup the build Path. All the above jar files should be in the Class Build Path
     
1. Right Click on on Resorthib project in the eclipse folder structure
2. Select build Path->configure build path.
3. Go to Libraries-> External jars,  browse lib folder you created earlier. Then add external
     jars by selecting all jar files that you copied to lib folder in one of previous step

Step 3:

Modifying the Java Bean with Hibernate Annotation.

1. Expand Src Folder
2. Expand org.zkforge.resort.model
3. Expand Resort.Java
4. Double Click on Resort Class

Paste the following code

package org.zkforge.resort.model;


import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;


@Entity
@Table(name = "resort")
public class Resort {


private Integer id;
private String _name;
private String _description;
private double _latitude;
private double _longitude;


public Resort() {
}


public Resort(String name, String description, double latitude,
double longitude) {
_name = name;
_description = description;
_latitude = latitude;
_longitude = longitude;
}


@Id
@GeneratedValue
@Column(name = "ID")
public Integer getId() {
return id;
}


public void setId(Integer id) {
this.id = id;
}


@Column(name = "_name")
public String getName() {
return _name;
}


public void setName(String name) {
_name = name;
}


@Column(name = "_description")
public String getDescription() {
return _description;
}


public void setDescription(String description) {
_description = description;
}


@Column(name = "_latitude")
public double getLatitude() {
return _latitude;
}


public void setLatitude(double latitude) {
_latitude = latitude;
}


@Column(name = "_longitude")
public double getLongitude() {
return _longitude;
}


public void setLongitude(double longitude) {
_longitude = longitude;
}
}


Step 4:

Now let us create hibernate configuration file where we will have the information
such as DB Name, user id and Password.


1. Right Click Src folder
2. Click New -> Other
3. In the New Window, Type File for the wizards.
4. Select File from general
5. Give the File Name as hibernate.cfg.xml


Copy the following XML into that file



<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<!-- Database connection settings -->
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost/sampledb</property>
<property name="connection.username">root</property>
<property name="connection.password">123</property>


<!-- JDBC connection pool (use the built-in) -->
<property name="connection.pool_size">1</property>


<!-- SQL dialect -->
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>


<!-- Enable Hibernate's automatic session context management -->
<property name="current_session_context_class">thread</property>


<!-- Disable the second-level cache -->
<property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>


<!-- Echo all executed SQL to stdout -->
<property name="show_sql">true</property>


<!-- Drop and re-create the database schema on startup -->
<property name="hbm2ddl.auto">update</property>


<!-- Mapping Classes -->
<mapping class="org.zkforge.resort.model.Resort" />


</session-factory>
</hibernate-configuration>

Please take care to change the DB Name, username and password.


Step 5:

Next we will create Hibernate Utility class, where we can obtain session to data access


1. Expand Src Folder
2. Expand org.zkforge.resort.ui
3. Right Click -> New-> Class
4. Give the Class Name as HibernateUtil


Paste the following code

package org.zkforge.resort.ui;


import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;


public class HibernateUtil {
private static SessionFactory sessionFactory = buildSessionFactory();
private static ServiceRegistry serviceRegistry;


private static SessionFactory buildSessionFactory() {
try {
// Create the SessionFactory from hibernate .cfg.xml
Configuration configuration = new Configuration();
configuration.configure();
serviceRegistry = new ServiceRegistryBuilder().applySettings(
configuration.getProperties()).buildServiceRegistry();
sessionFactory = configuration.buildSessionFactory(serviceRegistry);
return sessionFactory;
} catch (Throwable ex) {
// Make sure you log the exception, as it might be swallowed
System.err.println("Initial SessionFactory creation failed." + ex);
throw new ExceptionInInitializerError(ex);
}
}


public static SessionFactory getSessionFactory() {
return sessionFactory;
}
}


Step 6:

Next Step, we will create DAO for resort class from where we can have all our
DB related activites

1. Expand Src Folder
2. Expand org.zkforge.resort.ui
3. Right Click -> New-> Class
4. Give the Class Name as ResortDAO

Paste the following code


package org.zkforge.resort.ui;


import java.util.List;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.Query;


public class ResortDAO {


@SuppressWarnings("rawtypes")
public List getAll() {
List allrecords = null;
Transaction trns = null;
Session session = HibernateUtil.getSessionFactory().openSession();
try {
trns = session.beginTransaction();
Query q1 = session.createQuery("from Resort");
allrecords = q1.list();
session.getTransaction().commit();
} catch (RuntimeException e) {
if (trns != null) {
trns.rollback();
}
e.printStackTrace();
} finally {
session.flush();
session.close();
}
return allrecords;
}
}

Step 7:

Now go Back to the ResortController

Replace the following line
lb.setModel(new ListModelList(Resort.getAll()));

with
lb.setModel(new ListModelList(new ResortDAO().getAll()));


Step 8:  Run the application

       Double click resort.zul and right click and say run as -> Run on server.

       You can see the output in the internal browser