Posts Tagged ‘JDBC’

Using Hibernate’s HQL

Friday, May 28th, 2010
Hibernate uses a powerful query language called as HQL that is similar to SQL. Compared with SQL, however, HQL is fully object-oriented and understands notions like inheritance, polymorphism and association. Hibernate generates the SQL from these HQL’s and executes against the RDBMS. HQL supports Clauses, Aggregate functions and Subqueries with it’s syntax. Other advantage of using HQL is, it can give us the results as Objects. And the queries written in HQL can be executed in all the databases with out the changing it, so it makes our application most of the time independent of database flavor we choose.We’ll discuss about the clauses and the examples of Clauses.
Clauses in HQL
HQL supports five types of Clauses, they are

  • from Clause
  • where Clause
  • select Clause
  • order by Clause
  • group by Clause
We’ll look at the from clause. And for all of these operation we’ll take the Singer domain class which we have used it in our previous post Simple CRUD Operation Using Hibernate. We have two properties to a Singer which is ID and the Singer Name. We’ll take a closer look at the CRUDTest.listSinger() method, we were using the “from Clause” to select the result from Singer table. We’ll modify that slightly to use the Query to use the from clause. So i’ll be updating listerSinger() method as follows.
			public void listSinger() {
				Session session = FactoryUtil.getSessionFactory().openSession();
				Transaction transaction = null;
				try {
						transaction = session.beginTransaction();
						Query qry = session.createQuery("from Singer singer");
						for (Iterator iterator = qry.iterate(); iterator.hasNext();) {
							Singer singer = (Singer) iterator.next();
							System.out.println(singer.getSingerName());
						}
				}
				catch (HibernateException e) {
					transaction.rollback();
					e.printStackTrace();
				}
				finally {
					session.close();
				}
			}
			
Now, We’ll look at the where clause. The where clause is smilar to where clause in SQL. In HQL Where clause allows us to set the parameters, Compound Path expressions. In case of HQL, the Compound path expressions are making it more useful and easy for the developers too. Consider our Singer example and we are going to add, Event table to store his stage events.This events may have one more attribute called address of the events, which can be put into an other table called address. Now consider the situation of getting all the events performed by a singer, which of the city Bangalore. if some one is asking to get the details of all the singer’s name then i’ll be writing the following query in SQL.
			SELECT sing.singer_name FROM SINGER sing LEFT OUTER JOIN SINGER_EVENTS eve ON eve.singer_id = sing.singer_id LEFT OUTER JOIN EVENT_ADDRESS addr ON addr.event_id = eve.event_id WHERE addr.city = 'Bangalore'
		
Writing these kind of queries is not so easy always and as java developer we won’t be concentrating on writing queries. So HQL provides a easy way to address this using Compound Path Expressions. I can rewrite the above SQL as following using HQL.
		SELECT sing.singer_name FROM SINGER sing WHERE sing.eve.addr.city = 'Bangalore'
	
isn’t that looks easy for us? The other good example i can look at here is, binding the javabean properties directly to HQL’s. This is almost similar to our Prepared Statements in JDBC. Look at the following code snipet,
				Singer singer = (Singer) session.get(Singer.class, singerId);
				singer.setSingerName("Jerry");
				query = session.createQuery(" from Singer sing where sing.name=:singername ");
				query.setProperties(singer);
	
The above code is self explantory, where we set the singer name property using the setProperties() method from Query. We’ll be able to set multiple parameters to it. I suppose this will be really helpful when work with “Dependency Injection”, i.e Inject these values through Spring Config file and call these queries.
The next clause we’ll take it here is, Select Clause. HQL offers may functionalities like SQL queries, We have Aggregate functions, expressions. The most important thing i feel like discussing here is, returning the results as objects, lists and sometimes it can be ad DTO’s also. We’ll consider the above example of getting singer’s who has performed some events in “Banglore”. We’ll add some more parameters to return. We’ll add Event Name and the place of the event to return. We’ll update the CRUDTest.listSinger() to the following,
			public void listSinger() {
				Session session = FactoryUtil.getSessionFactory().openSession();
				Transaction transaction = null;
				try {
						transaction = session.beginTransaction();
						List singerDetails = session.createQuery("select new list(singer.singer_name,eve.event_name) from Singer singer where singer.eve.addr.city='Bangalore' ").list();
						for (Iterator iterator = List.iterator(); iterator.hasNext();) {
							List tempDetail = (List) iterator.next();
							System.out.println(tempDetail.get(0));
						}
				}
				catch (HibernateException e) {
					transaction.rollback();
					e.printStackTrace();
				}
				finally {
					session.close();
				}
			}
			
Basically this returns the list where we have the Singer name and event name. HQL allows us to use the “new” opertor combined with domain objects also. Say for example instead of returning as list, if we may wanted to return as domain object then the code looks like the following,
			public void listSinger() {
				Session session = FactoryUtil.getSessionFactory().openSession();
				Transaction transaction = null;
				try {
						transaction = session.beginTransaction();
						List singerDetails = session.createQuery("select new Singer(singer.singer_name,eve.event_name) from Singer singer where singer.eve.addr.city='Bangalore' ").list();
						for (Iterator iterator = List.iterator(); iterator.hasNext();) {
							Singer tempDetail = (Singer) iterator.next();
							System.out.println(tempDetail.getSingerName());
						}
				}
				catch (HibernateException e) {
					transaction.rollback();
					e.printStackTrace();
				}
				finally {
					session.close();
				}
			}
			
On the same way we use the map also to return the results from DB. For Order by clause and Group by Clause HQL will looks similar to SQL.
Warning
To execute the above code we may need to do a lot of change with the Singer.hbm.xml to accomadate the one-many relationship between Singer and the events. A best alternate could be, we can remove the join tables and return only the singer details.
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Hibernate, the ORM Library for Java

Wednesday, May 5th, 2010
Object-relational mapping
Which helps you in converting the relational databases to object oriented programming languages,which help a developer to keep the persistence of the domain object.Basically ORM tools will provide the bridge between the database and the application by storing application objects in the database for the developer, rather than requiring the developer to write and maintain mountains of code to store and retrieve objects.
In other words we can say, Object/relational mapping (ORM) is the process of persisting objects in a relational database. ORM bridges the gap between object and relational schemas, allowing your application to persist objects directly without requiring you to convert objects to and from a relational format.There are List of ORM softwares for Java.
All among the list of Software’s Hibernate, requires a small amount of metadata for each persistent object. Hibernate operates independently of application architecture, allowing it to be used in various applications. It provides full object/relational mapping,meaning that it supports all the available object-oriented features that relational databases lack.
Hibernate - Free GPL,ORM Library
The primary feature’s are,

  • Mapping - Hibernate maps the java classes a.k.a domain objects to db tables through xml configuration. This xml configuration tells the Hibernate how to persist these objects.
  • Persistence - Hibernate provides transparent persistence for Plain Old Java Objects (POJOs). The only strict requirement for a persistent class is a no-argument constructor, not necessarily public
  • Hibernate Query Language (HQL) - Hibernate provides a SQL inspired language called Hibernate Query Language (HQL) which allows SQL-like queries to be written against Hibernate’s data objects. Criteria Queries are provided as an object-oriented alternative to QL.
Mapping XML
Mapping definitions, also called mapping documents, are used to provide Hibernate with information to persist objects to a relational database.The mapping files also provide support features, such as creating the database schema from a collection of mapping files.The mapping file tells Hibernate what table in the database it has to access,and what columns in that table it should use.
<?xml version="1.0"?>
<hibernate-mapping package="com.techmaddy.domain">
[...]
</hibernate-mapping>
Now we’ll try to add the domain object Book, which holds Author, Edition. By this we are telling the Hibernate to persist this object and map from RDBMS to java code.So the above mentioned XML will be changed to the following.
<?xml version="1.0"?>
<hibernate-mapping package="com.techmaddy.domain">
<class name="com.techmaddy.domain.Book" table="HBD_BOOKS">
	<property  name="authorName" type="string" column="HBD_AUTHNAME" not-null="true" />
	<property  name="edition" type="string" column="HBD_EDITION" not-null="true" />
</class>
</hibernate-mapping>
From the above mentioned config file, we’ll end up generating the domain class, with two member. The name attribute tells the hibernate to generate the getter and setter for that property. So in our exmaple it’ll be like getAuthorName() and getEdition(). And the same for setters also. Now we’ll see one more scenario, every domain object we add may have the primary key associated. Most of the time this key will be autogenerated, the property tag doesn’t have any special attribute to specify this. So we are going to make use of the id tag, which tells the hibernate, this column is the primary key and it’s going to be generated automatically. So i’m going to add some more tags in the above xml.
<?xml version="1.0"?>
<hibernate-mapping package="com.techmaddy.domain">
<class name="com.techmaddy.domain.Book" table="HBD_BOOKS">
	<id name="id" column="EVENT_ID">
		<generator class="native"/>
	</id>
	<property  name="authorName" type="string" column="HBD_AUTHNAME" not-null="true" />
	<property  name="edition" type="string" column="HBD_EDITION" not-null="true" />
</class>
</hibernate-mapping>
The generator creates the primary key value for the persistent class.Native generators provide portability for mapping documents since the framework can determine the generator method supported by the database. Generators using the native class will use identity or sequence columns depending on available database support. If neither method is supported, the native generator falls back to a high/low generator method to create unique primary key values.
The other generators are Assigned and Select. The Assign lets you generate and assign the object ID.Select generator, which retrieves the primary key value by selecting a value from a database trigger. The generator type you choose determines its behavior based on the underlying database.
Hibernate Components & Configuration
Hibernate can be used with Web Application and with an Standalone java applications also. The below image will give you the basic components of Hibernate.
hibernate.cfg.xml & hibernate.properties - These are used to configure the hibernate service. Both these files does the samething.When these two are present in the classpath, then hibernate.cfg.xml will overrides the hibernate.properties file. Basically these files are used to basic connection info, whcih we are going to use it in our application.
Session Factory - SessionFactory allows application to create the Hibernate Sesssion by reading the configuration from hibernate.cfg.xml file.It implements a design pattern, that ensures that only one instance of the session is used per thread.
Session - represents a single-threaded unit of work.Session instances are your primary interface to the Hibernate persistence service.
By using the Hibernate we are going to connect to DB, So the Hibernate should be instructed to pick the drivers and get the connection.The following XML, will gives us the skeleton of the hibernate.cfg.xml
		<hibernate-configuration>
			<session-factory>
				[...Properties for JDBC Connection]
				....
				[...]
				[..Mapping resources]
				....
				[...]
			</session-factory>
		</hibernate-configuration>
For the above hibernate configuration, we’ll try to add the properties for HSQL database. Then sessionfactory tag will becomes the following
			<session-factory>
				<property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
				<property name="connection.url">jdbc:hsqldb:hsql://localhost</property>
				<property name="connection.username">sa</property>
				<property name="connection.password"></property>
				<property name="connection.pool_size">1</property>

				<property name="dialect">org.hibernate.dialect.HSQLDialect</property>
			</session-factory>
This configures the connection properties, along with the SQL Dialect. The SQL dialect tells the hibernate that we are using a specific JDBC drivers. In this example we are going to make use of HSQL DB.The List of dialects are available here.
Now we’ll add the domain object mappings to hibernate.cfg.xml. The following syntax shows how to add an mapping xml for an domain object.
			<mapping resource="com/techmaddy/domain/Book.hbm.xml"/>
Adding this completes the hibernate basic configuration, along with this we may need to add the libraries for the hibernate.Creating the Startups for creating the session factory and helpers we’ll be dicuss in the next article.
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Connection Pooling in jBoss with MySQL and Oracle

Sunday, August 2nd, 2009
Handling Connection Pooling in Weblogic will be much easier, since WL itself provides the driver classes.But handling in jBoss is little trickier since, we need little diff configurations. Connection pooling in jBoss can be handled through the DataSouce. jBoss can manage the database connections by pooling them and by providing connections to the application when it needs them. Application developers need to specify the configurations about their connection.
OK, Now How will i configure DS for my application in jBoss. The DS in jBoss can be configured by using the config file called ABCApplication-ds.xml. What is this ABCApplication-ds.xml,OK here it goes, jBoss allows you to configure multiple DS’’s and picks automatically when the container loads, Since the -ds.xml is needed in naming our data source config file. So how it’’s loading the file, I don”t have the answer, I think it uses the Annotations/Reflection concept available in java. I”m telling this because the Action Class in Struts 2.0 will be picked on the same manner, i mean all the action class in Struts 2.0 should be the following format *Action.java. Hmm Now we”ll be back to our topic. For example if we are going to configure DS for my Travel application so i”ll be naming it as Travel-ds.xml. OKie, My First step is done, What is my next step. Where will i pick up my template specific to my database vendor i”m using in my application.
Yeahh, I have the answer donworry, This is where i like the Open Source always. Here goes Jboss 4.0.5docsexamplesjca. You have the templete for all the database vendors. For MySQL we have the template called “mysql-ds.xml”, So How i have modified for my application lool @ the following Travel-ds.xml
		<?xml version="1.0" encoding="UTF-8"?>
			<datasources>
				<local-tx-datasource>
					<jndi-name>MySqlDS</jndi-name>
					<connection-url>jdbc:mysql://localhost:3306/Travel</connection-url>				<driver-class>com.mysql.jdbc.Driver</driver-class>
					<user-name>root</user-name>
					<password>myTravelAppDB</password>
					<min-pool-size>10</min-pool-size>
					<max-pool-size>30</max-pool-size>
					<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
						<metadata>
							<type-mapping>mySQL</type-mapping>
						</metadata>
					</local-tx-datasource>
			</datasources>
		
OKie, in this MySqlDS is my datasource name. Apart from that i”m specifying some other parameters like driver-class, username, password.
For Oracle we have the template called “oracle-ds.xml”. So Travel-ds.xml will become like the following
			<?xml version="1.0" encoding="UTF-8"?>
				<datasources>
					<local-tx-datasource>
						<jndi-name>OracleDS</jndi-name>
						<connection-url>jdbc:oracle:thin:@localhost:1521:travelsid</connection-url>			<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
						<user-name>oracleUser</user-name>
						<password>myTravelAppDB</password>
						<min-pool-size>10</min-pool-size>
						<max-pool-size>30</max-pool-size>
						<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>	<metadata>
							<type-mapping>Oracle9i</type-mapping>
						</metadata>
					</local-tx-datasource>
				</datasources>
			
As of now, We are done with the configurations, Where will i place this file. First i kept it in WEB-INF folder since we used to do that for most of the time, Where i went wrong. Then where should we place this, Alright, this is my path Jboss 4.0.5serverdefaultdeployTravel-ds.xml. So jBoss automatically picks the file and loads it when it starts-up. jBoss treats this as an seperate application/service.
And now What will i do with the library, OKie go ahead and place it under here Jboss 4.0.5serverdefaultlib. We are down with our donfiguations. I”m ready to write my DAOUtil class. You know what i did, wrongly i kept the JDBC library for MySQL in Jboss 4.0.5/server/default/deploy. But still need to find out the reason, How it’s picking, may be it’’s picking while starting itself. :-) , I read some wher if you keep your file in deploy folder, then jBoss will treat it as a Service/Application, get back here after a week, Will let you know.
			package com.techmaddy;
			/****
			*	@author Kumarasamy Mani<kumarasamy@techmaddy.com>
			****/
			import java.sql.Connection;
			import java.sql.ResultSet;
			import java.sql.SQLException;
			import java.sql.Statement;
			import javax.naming.InitialContext;
			import javax.naming.NamingException;
			import javax.sql.DataSource;

			public class DAOUtil {
				public void excuteQueries() {
					Connection con = null;
					try {
						InitialContext jndiCntx = new InitialContext();
						DataSource ds = (DataSource)jndiCntx.lookup("java:/MySqlDS");
						con = ds.getConnection();
						Statement stmt = con.createStatement();
						ResultSet rs = stmt.executeQuery("select * from book");
						while(rs.next()) {
							System.out.println("n************nnThe title is "+rs.getString("title")+"nn*************n");
						}
					}
					catch(Exception e) {
						e.printStackTrace();
					}
					finally {
						try {
							con.close();
						}
						catch(Exception ex) {
							ex.printStackTrace();
						}
					}
				} //End of excuteQueries
			} //End of DAOUtil
		
We are done with DS implementation in jBoss.
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)
Get Adobe Flash player