Archive for the ‘PostgreSQL’ Category

Hibernate Basics & Simple Example

April 13, 2011

Hibernate is an open source Object Relational Mapping framework used to transform from object oriented programming to relational databases.

It supports object oriented concepts Association, Polymorphism, Inheritance & Composition through persistent classes. Also Hibernate facilitates to write queries in variant forms,  HQL(Hibernate Query Language), Native SQL, or Criteria API.

The following question may raise while learning about Hibernate.

What’s the difference between JDBC & Hibernate?

JDBC API’s are easy to use for small projects. But Hibernate framework is well suited for enterprise level applications and acts as mediator between object & relational approaches.

Hibernate Architecture
Hibernate performs the following operations;

  • opens a database connection
  • converts HQL statements to database specific statements
  • execute it  & receive data from the database
  • result set being mapped to equivalent java objects to be processed by application.
Hibernate Architecture

Hibernate Architecture

This blog gives you a basic idea about how to connect and use Hibernate with a database.

Simple Application : BookApps

BookApps is an example application. This application main function is to add, update, delete, retrieve books from the database.

Note : This application uses a PostgreSQL database, therefore run an instance of PostgreSQL. Go through this blog, to know the basics of PostgreSQL. Also download and run the same.

Click here to download the hibernate & unzip the file hibernate-distribution-3.6.0.Final-dist.zip

Hibernate configuration file : hibernate.cfg.xml
This will have all the information to connect Hibernate to a database. In this example, PostgreSQL database is used.
Note : Hibernate configurations can also be done through hibernate.properties file.

 <!--<span class="hiddenSpellError" pre=""-->DOCTYPE hibernate-configuration PUBLIC
 "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
 <a href="http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"</a>>

 <hibernate-configuration>
 <session-factory>
 <!-- Database connection settings -->
 jdbc:postgresql://localhost:5432/postgres
 <property name="connection.driver_class">org.postgresql.Driver</property>
 <property name="connection.username">postgres</property>
 <property name="connection.password">******</property>
 <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>

 <!-- SQL dialect - generate SQL for a particular database -->
 <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>

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

 <!-- Enable Hibernate's automatic session context management -->
 <property name="current_session_context_class">thread</property>
<!-- Mapping resource configuration files -->
 <mapping resource="src/com/bookstore/bookapp.hbm.xml"/>
 </session-factory>
 </hibernate-configuration>
 

Property to drop and re-create the database schema on startup

<property name="hbm2ddl.auto">create</property>

By adding this property, hibernate will create tables for you. This will be executed only once ie) after first run comment this code. because every time tables will get dropped.

Hibernate mapping file : bookapp.hbm.xml
For all objects to be persisted in the database,  Hibernate needs this XML file. The mapping file tells Hibernate how to load and store objects, what table in the database it has to access and what columns in the table should use.
This file should be saved with .hbm.xml file extension.

 <?xml version="1.0"?>
 <!--<span class="hiddenSpellError" pre=""-->DOCTYPE hibernate-mapping PUBLIC
 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
 <a href="http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"</a>>

 <hibernate-mapping package="com.bookstore">
 BookDetails" table="BookStore">
 BookId">
 <generator/>
 </id>
 bookName" type="string" column="BookName"/>
 authorName" type="string" column="AuthorName"/>
 bookCost" type="int" column="BookCost"/>
 </class>
 </hibernate-mapping>
 

Persistent class : BookDetails.java
The persistent class works in the form of POJO(Plain Old Java Object) model. It’s a java bean class having getter and setter methods to access the properties of an app.

 package com.bookstore;

 public class BookDetails{
 private int bookId;
 private String bookName;
 private String authorName;
 private int bookCost;

 BookDetails(){
 }

 public int getBookId(){
 return bookId;
 }
 public void setBookId(int bookId){
 this.bookId = bookId;
 }

 public String getBookName(){
 return bookName;
 }
 public void setBookName(String bookName){
 this.bookName = bookName;
 }

 public String getAuthorName(){
 return authorName;
 }
 public void setAuthorName(String authorName){
 this.authorName = authorName;
 }

 public int getBookCost(){
 return bookCost;
 }
 public void setBookCost(int bookCost){
 this.bookCost = bookCost;
 }

 public String toString(){
 StringBuffer sb = new StringBuffer();
 sb.append("BookName : ").append(bookName);
 sb.append(" ,AuthorName : ").append(authorName);
 sb.append(" , BookCost : ").append(bookCost);
 return sb.toString();
 }
 }
 

Hibernate communication with database : HibernateUtil.java
Load the hibernate configuration file and mapping files automatically, create a session factory from the configured object, create a session to execute the HQL query and get the result set of java objects.

 package com.bookstore;

 import org.hibernate.SessionFactory;
 import org.hibernate.cfg.Configuration;

 public class HibernateUtil {

 private static final SessionFactory sessionFactory;

 static {
 try {
 // Create the SessionFactory from hibernate.cfg.xml
 sessionFactory = new Configuration().configure().buildSessionFactory();
 } 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;
 }

 }

Application class : BookApp.java
A simple application class which performs CRUD operations in the database through HQL, Persistent Objects, Native SQL & Criteria API.

package com.bookstore;

 import java.util.List;
 import java.util.Iterator;
 import org.hibernate.Session;
 import org.hibernate.Query;
 import org.hibernate.Criteria;
 import org.hibernate.criterion.Restrictions;
 public class BookApp{
 public static void main(String args[]){
 BookMethods bm = new BookMethods();

 //Adding books
 bm.addBook("Professional Hibernate","Eric Pugh, Gradecki", 350);
 bm.addBook("Hibernate in Action","Christian Bauer, Gavin King", 499);
 bm.addBook("Java Persistence With Hibernate","Bauer, Gavin King", 423);
 bm.addBook("Begining Hibernate","Dave Minter, Jeff Linwood", 1500);

 //Listing added books
 bm.listBooks();

 //Getting book details for a particular book name.
 bm.getBookDetails("Hibernate in Action");

 //To see no. of books
 bm.countBook();

 //Update particular book cost
 bm.updateBook("Begining Hibernate",1000);

 //Listing available books
 bm.listBooks();

 //Deleting a book
 bm.deleteBook("Java Persistence With Hibernate");

 //Listing available books After executing delete
 bm.listBooks();
 }
 }

 class BookMethods{
 //Using persistent objects to store data into the database.
 public void addBook(String bookName, String authorName, int bookCost){
 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 session.beginTransaction();
 BookDetails bd = new BookDetails();
 bd.setBookName(bookName);
 bd.setAuthorName(authorName);
 bd.setBookCost(bookCost);
 session.save(bd);
 session.getTransaction().commit();
 }

 //Using persistence object to delete a row from the database.
 public void deleteBook(String bookName){
 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 session.beginTransaction();
 List list = session.createQuery("from BookDetails where bookName='"+bookName+"'").list();
 Iterator itr = list.iterator();
 while(itr.hasNext()){
 BookDetails bd = (BookDetails)itr.next();
 System.out.println("delete : "+bd);
 session.delete(bd);
 }
 session.getTransaction().commit();
 }

 //Using Criteria API to update the book cost
 public void updateBook(String bookName, int bookCost){
 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 session.beginTransaction();
 Criteria cri = session.createCriteria(BookDetails.class);
 cri = cri.add(Restrictions.eq("bookName", bookName));
 List list = cri.list();
 BookDetails bd = (BookDetails)list.iterator().next();
 bd.setBookCost(bookCost);
 session.update(bd);
 session.getTransaction().commit();
 }

 //Using HQL - Hibernate Query Language
 public void getBookDetails(String bookName){
 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 session.beginTransaction();
 Query q = session.createQuery("from BookDetails where bookName=:bn");
 q.setString("bn", bookName);
 List list = q.list();
 System.out.println("Getting Book Details using HQL. \n"+list);

 //The above query can also be achieved with Criteria & Restrictions API.
 Criteria cri = session.createCriteria(BookDetails.class);
 cri = cri.add(Restrictions.eq("bookName", bookName));
 list = cri.list();
 System.out.println("Getting Book Details using Criteria API. \n"+list);

 session.getTransaction().commit();
 }

 //Aggregate function.
 public void countBook(){
 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 session.beginTransaction();
 List list = session.createQuery("select count(*) from BookDetails").list();
 System.out.println("Aggregate function count \n"+list);
 session.getTransaction().commit();
 }

 //Native SQL Query
 public void listBooks(){
 Session session = HibernateUtil.getSessionFactory().getCurrentSession();
 session.beginTransaction();
 List list = session.createSQLQuery("select * from BookStore").addEntity(BookDetails.class).list();
 Iterator itr = list.iterator();
 while(itr.hasNext()){
 BookDetails bd = (BookDetails)itr.next();
 System.out.println(bd);
 }
 session.getTransaction().commit();

 }
 }

Compile & Run the simple application : BookApp.

Setting classpath
Following jars are required to compile and run the code. Copy the below code and save it in a file called classpath.bat & run this batch file.

set HIBERNATE_HOME=C:\Hibernate\hibernate-distribution-3.6.0.Final
set CLASSPATH=%HIBERNATE_HOME%\hibernate3.jar;%HIBERNATE_HOME%\lib\required\dom4j-1.6.1.jar;%HIBERNATE_HOME%\lib\required\slf4j-api-1.6.1.jar;%HIBERNATE_HOME%\lib\jpa\hibernate-jpa-2.0-api-1.0.0.Final.jar;%HIBERNATE_HOME%\lib\required\commons-collections-3.1.jar;%HIBERNATE_HOME%\lib\required\javassist-3.12.0.GA.jar;%HIBERNATE_HOME%\lib\required\jta-1.1.jar;%HIBERNATE_HOME%\lib\required\antlr-2.7.6.jar;.;C:\PostgreSQL\postgresql-9.0-801.jdbc3.jar

Compile all the java files

C:\Hibernate\hibernate-distribution-3.6.0.Final\example\booksapp>javac -d . src\com\bookstore\*.java

Run the app

C:\Hibernate\hibernate-distribution-3.6.0.Final\example\booksapp>java com.bookstore.BookApp

Output

SLF4J: Failed to load class “org.slf4j.impl.StaticLoggerBinder”.
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.

Hibernate: select max(BookId) from BookStore
Hibernate: insert into BookStore (BookName, AuthorName, BookCost, BookId) values (?, ?, ?, ?)
Hibernate: insert into BookStore (BookName, AuthorName, BookCost, BookId) values (?, ?, ?, ?)
Hibernate: insert into BookStore (BookName, AuthorName, BookCost, BookId) values (?, ?, ?, ?)
Hibernate: insert into BookStore (BookName, AuthorName, BookCost, BookId) values (?, ?, ?, ?)

Hibernate: select * from BookStore

BookName : Professional Hibernate ,AuthorName : Eric Pugh, Gradecki , BookCost : 350
BookName : Hibernate in Action ,AuthorName : Christian Bauer, Gavin King , BookCost : 499
BookName : Java Persistence With Hibernate ,AuthorName : Bauer, Gavin King , BookCost : 423
BookName : Begining Hibernate ,AuthorName : Dave Minter, Jeff Linwood , BookCost : 1500

Hibernate: select bookdetail0_.BookId as BookId0_, bookdetail0_.BookName as BookName0_, bookdetail0_.AuthorName as AuthorName0_, bookdetail0_.BookCost as BookCost0_ from BookStore bookdetail0_ where bookdetail0_.BookName=?
Getting Book Details using HQL.
[BookName : Hibernate in Action ,AuthorName : Christian Bauer, Gavin King , BookCost : 499]

Hibernate: select this_.BookId as BookId0_0_, this_.BookName as BookName0_0_, this_.AuthorName as AuthorName0_0_, this_.BookCost as BookCost0_0_ from BookStore this_ where this_.BookName=?
Getting Book Details using Criteria API.
[BookName : Hibernate in Action ,AuthorName : Christian Bauer, Gavin King , BookCost : 499]

Hibernate: select count(*) as col_0_0_ from BookStore bookdetail0_
Aggregate function count
[4]

Hibernate: select this_.BookId as BookId0_0_, this_.BookName as BookName0_0_, this_.AuthorName as AuthorName0_0_, this_.BookCost as BookCost0_0_ from BookStore this_ where this_.BookName=?
Hibernate: update BookStore set BookName=?, AuthorName=?, BookCost=? where BookId=?

Hibernate: select * from BookStore
BookName : Professional Hibernate ,AuthorName : Eric Pugh, Gradecki , BookCost : 350
BookName : Hibernate in Action ,AuthorName : Christian Bauer, Gavin King , BookCost : 499
BookName : Java Persistence With Hibernate ,AuthorName : Bauer, Gavin King , BookCost : 423
BookName : Begining Hibernate ,AuthorName : Dave Minter, Jeff Linwood , BookCost : 1000

Hibernate: select bookdetail0_.BookId as BookId0_, bookdetail0_.BookName as BookName0_, bookdetail0_.AuthorName as AuthorName0_, bookdetail0_.BookCost as BookCost0_ from BookStore bookdetail0_ where bookdetail0_.BookName=’Java Persistence With Hibernate’
delete : BookName : Java Persistence With Hibernate ,AuthorName : Bauer, Gavin King , BookCost : 423
Hibernate: delete from BookStore where BookId=?

Hibernate: select * from BookStore
BookName : Professional Hibernate ,AuthorName : Eric Pugh, Gradecki , BookCost : 350
BookName : Hibernate in Action ,AuthorName : Christian Bauer, Gavin King , BookCost : 499
BookName : Begining Hibernate ,AuthorName : Dave Minter, Jeff Linwood , BookCost : 1000

C:\Hibernate\hibernate-distribution-3.6.0.Final\example\booksapp>

Click here to view/download the source of book apps.


PostgreSQL Basics

February 10, 2011

PostgreSQL is an ORDBMS for managing data stored in relations. It offers many features such as complex queries, triggers, transactional integrity, multiversion concurrency control.
Also, PostgreSQL can be extended by the user in many ways, data types, functions,aggregate functions, index methods.

PostgreSQL uses a client/server model.

server : postgres is a database server program, running as a process to perform these actions.,

  • manages the database files.
  • accept database connections from the client applications.
  • performs database actions on behalf of the clients.

client : front end application wants to perform database operations, client applications could be

  • text-oriented tool
  • database maintenance tool
  • a web server that accesses the database to display web pages.

The PostgreSQL server can handle multiple concurrent connections from clients.

Download & Install the PostgreSQL for windows OS, click here.

After completing the installation, open the client SQL Shell(psql) for executing the queries.  Following  snapshots will guide you.

Provide the correct authentication details to login psql

Basic PSQL commands

Learn these basic psql slash commands which will aid you to use SQL Shell easily. Also these commands are completely different from MySQL.

Command
Actions
\l List all databases
\c dbname Connect to new database.
\dt To view list of relations/tables
\d tablename Describe the details of given table.
\h Get a help on syntax of SQL commands
\? Lists all psql slash commands.
\set System variables list.
\q Quit psql

Basic SQL operations in PostgreSQL

Database Creation & Deletion
To create a new database

createdb dbname

Remove the database

dropdb dbname

CRUDCreate Read Update Delete

These are the four basic functions to be performed on relational databases. Almost Postgres CRUD  SQL statements are similar to MySQL statements.

Create – Creating table & inserting the values.
Table Creation : Books

CREATE TABLE Books(BookId int, BookName text, BookCost int);

Table Values Insertion

INSERT INTO Books VALUES(1001, 'Who will cry when you die', 299);
INSERT INTO Books VALUES(1002, 'Practical PostgreSQL', 1722);
INSERT INTO Books VALUES(1003, 'Beginning Databases with PostgreSQL', 1080);

Read – Retrieve table data.

SELECT * FROM Books;

bookid |              bookname               | bookcost
——–+————————————-+———-
1001 | Who will cry when you die           |      299
1002 | Practical PostgreSQL                |     1722
1003 | Beginning Databases with PostgreSQL |     1080
(4 rows)

Update

UPDATE Books SET BookCost=1100 WHERE BookId=1003;
SELECT * FROM Books WHERE BookId=1003;

bookid |              bookname               | bookcost
——–+————————————-+———-
1003 | Beginning Databases with PostgreSQL |     1100
(4 rows)

Delete – Delete row(s) from the relation.

DELETE FROM Books WHERE BookId=1001;
SELECT * FROM Books;

bookid |              bookname               | bookcost
——–+————————————-+———-
1002 | Practical PostgreSQL                |     1722
1003 | Beginning Databases with PostgreSQL |     1100
(3 rows)

Other Significant Features

Inheritance
PostgreSQL allows you to implements table inheritance. This should be defined with a special keyword INHERITS in the table design.

CREATE TABLE BookAuthors(Author1 text, Author2 text) INHERITS (Books);

Table inheritance has certain limits in inserting data into table. As Postgres won’t allow to perform such operations. Lets illustrates the possible fail & success cases.

case 1: You can’t insert directly into BookAuthors table.

INSERT INTO BookAuthors VALUES('Richard','Matthew');

ERROR:  invalid input syntax for integer: “Richard”
LINE 1: INSERT INTO BookAuthors VALUES(‘Richard’,’Matthew’);

case 2: It does not allow to insert into child table columns (Author1, Author2) while inserting data into parent table.

INSERT INTO Books VALUES(1006, 'Greatness Guide', 400, 'Robin','Sharm
a');

ERROR:  INSERT has more expressions than target columns
LINE 1: …T INTO Books VALUES(1006, ‘Greatness Guide’, 400, ‘Robin’,’S…
^

Possible valid insert statements are

case 1: This will insert data into Books table alone.

INSERT INTO Books VALUES(1006, 'Greatness Guide', 400);

case 2: This will insert data into both parent & child tables, Books & BookAuthors.

INSERT INTO BookAuthors VALUES(1005, 'PostgreSQL 9.0 High Performance
', 1500, 'Gregory Smith');

Retrieving the child table BookAuthors will also retrieve the parent table column values.

select * from BookAuthors;

bookid |            bookname             | bookcost |    author1    | author2
——–+———————————+———-+—————+———
1005 | PostgreSQL 9.0 High Performance |     1500 | Gregory Smith |
(1 row)

ONLY Keyword
This keyword indicates that the query should be run only on the specified table(Books) and not tables in the inheritance hierarchy. ie., Insertion done through child table will not be shown here.

Note : ONLY Keyword will be applicable for SELECT, UPDATE & DELETE.

SELECT * FROM ONLY Books;

bookid |              bookname               | bookcost
——–+————————————-+———-
1002 | Practical PostgreSQL                |     1722
1004 | Beginning Databases with PostgreSQL |     1100
(2 rows)

Row(BookId : 1005) is not shown here because this has been inserted through child table BookAuthors.

Composite Types
This represents the structure of a row or record. PostgreSQL allows composite types to be used as the same ways of simple types are used. For instance, a column of a table can be declared as a composite type. See the below example.

Creating two composite types FullName & Address, each has 2 & 3 columns respectively.

 CREATE TYPE FullName AS(FirstName text, LastName text);
 
 CREATE TYPE Address AS(DoorNo text, StreetName text, Place text);
 

\dT command can be used to see the defined composite types.

List of data types
Schema |   Name   | Description
——–+———-+————-
public | address  |
public | fullname |
(2 rows)

Defined composite types FullName & Address being used in the Employee table creation.

CREATE TABLE Employee(EmpId int PRIMARY KEY, EmpName FullName, EmpAddress Address);

Inserting data into the composite column values can be done in two ways.
ROW – expression used to construct composite values
() – Simply include the composite values in the bracket.

 INSERT INTO Employee VALUES(100, ROW('Neil','Matthew'), ROW('No. 6','Vivekanandar Street','Dubai'));
 

Inserted data.

empid |    empname     |              empaddress
——-+—————-+—————————————
100 | (Neil,Matthew) | (“No. 6″,”Vivekanandar Street”,Dubai)
(1 row)

Want to access a field of a composite column, use this notation. (composite_type_name).fieldname. For multi-table queries you can use table name as well, like this (table_name.composite_type_name).fieldname

 select empid, (empname).firstname, (empaddress).place from Employee;
 

empid | firstname | place
——-+———–+——-
100 | Neil      | Dubai
(1 row)