Posts Tagged ‘SQL’

Introduction to Jasper Reports

Tuesday, August 31st, 2010
JasperReports is an open source Java reporting tool that can display the report on your screen, to a printer or it can export into the following formats PDF, HTML, Excel, RTF, ODT, Comma-separated values and XML files and now even in Office 2007 formats(DOCX).It can be used in Java-enabled applications, including Java EE or Web applications, to generate dynamic content. Generally the format of the report design is in XML and Jasperreports has it’s own format called .jrxml. With JasperReports, you can design, deploy or embed our world-class reporting tools in your applications or stand alone reporting environment.
Features
  • Output in PDF, RTF, XML, XLS, CSV, HTML, XHTML, text, DOCX or OpenOffice.
  • Reports can include Graphs also, Renders through JFreeChart
  • The Type of charts are Pie, Bar, Stacked Bar, Line, Area, Scatter Plot, Bubble, and Time series
  • Advanced Flash based visualization with mapping, animation, and improved interactivity*
  • Various forms of DataSources like JDBC, XML, POJO, EJB, MDX, CSV and custom
  • Seperate Extensions to support Oracle PL/SQL
  • Scriptlets which the report definition can invoke at any point to perform additional processing. The scriptlet is built using Java, and has many hooks that can be invoked before or after stages of the report generation, such as Report, Page, Column or Group.
  • Sub report, where in the for drill down analysis
  • Crosstabs, for Dynamic Columns in the report
Report Generation
As i mentioned earlier, Jasper reports uses a special format for designing and generating the output. This format is called JRXML. This JRXML can be manually written using or it can be generated from any tools which helps us to design the Jasper design like iReport, Dynamicreports and Jasper Assistant. The following image illustrate about the flow of an JRXML file into a report output.
IDE for Report’s Design
We have many options for designing the reports. Since this is following an XML based design, we can manually write the JRXML and then we can generate the report or we can use an IDE to design the report. There are many tools available for this, they are,

  • iReport - An Open Source jasper report designer and maintained by Jasper soft. This was designed by the jasper report developers. It provides most extensive options to design the jasper report.
  • DynamicReports - Yet another open source report designer for jasper reports.
  • WebReportBuilder,an open source Java EE web application that allows web based developers and non developers to create basic and advanced Reports based on JasperReports to be used as a Web Report Server
  • And it can be easily integrated to popular IDE’s like Eclise and Netbeans for report design. There are plugins available for these IDE’s.
Structure of an JRXML/Jasper Report
JasperReports’ reports are defined in XML files, which by convention have an extension of jrxml. A typical jrxml file contains the following elements:

  • <jasperReport> – the root element.
  • <title> – its contents are printed only once at the beginning of the report
  • <pageHeader> – its contents are printed at the beginning of every page in the report.
  • <detail> – contains the body of the report.
  • <pageFooter> – its contents are printed at the bottom of every page in the report.
  • <band> – defines a report section, all of the above elements contain a band element as its only child element.
    • All of the elements are optional, except for the root jasperReport element.
Simple JRXML File
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="DBReport" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20">
	<property name="ireport.zoom" value="1.0"/>
	<property name="ireport.x" value="0"/>
	<property name="ireport.y" value="0"/>
	<queryString>
		<![CDATA[SELECT
     tblbm_issuedetails.`issue_number`,
     tblbm_issuedetails.`issue_createddate`
FROM
     `tblbm_issuedetails` tblbm_issuedetails]]>
	</queryString>
	<field name="issue_number" class="java.lang.String"/>
	<field name="issue_createddate" class="java.sql.Timestamp"/>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band height="38" splitType="Stretch">
			<staticText>
				<reportElement x="0" y="0" width="200" height="38"/>
				<textElement textAlignment="Center">
					<font fontName="Tahoma" size="24"/>
				</textElement>
				<text><![CDATA[My First DB Report]]></text>
			</staticText>
		</band>
	</title>
	<columnHeader>
		<band height="20" splitType="Stretch">
			<staticText>
				<reportElement x="0" y="0" width="100" height="20" isRemoveLineWhenBlank="true" backcolor="#D4D0C8"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Tahoma" isBold="true"/>
				</textElement>
				<text><![CDATA[Issue Number]]></text>
			</staticText>
			<staticText>
				<reportElement x="100" y="0" width="100" height="20" isRemoveLineWhenBlank="true" backcolor="#D4D0C8"/>
				<textElement verticalAlignment="Middle">
					<font fontName="Tahoma" isBold="true"/>
				</textElement>
				<text><![CDATA[Created Date]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="21" splitType="Stretch">
			<textField>
				<reportElement x="0" y="0" width="100" height="20" isRemoveLineWhenBlank="true"/>
				<textElement>
					<font fontName="Tahoma"/>
				</textElement>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{issue_number}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="100" y="0" width="100" height="20" isRemoveLineWhenBlank="true"/>
				<textElement>
					<font fontName="Tahoma"/>
				</textElement>
				<textFieldExpression class="java.sql.Timestamp"><![CDATA[$F{issue_createddate}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<pageFooter>
		<band splitType="Stretch"/>
	</pageFooter>
	<summary>
		<band splitType="Stretch"/>
	</summary>
</jasperReport>
The Preview of the Above Report Design and Report Preview
Report Design
Report Design
Report Preview
Report Design
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)

Reverse Engineering a Live DB with Visio 2003 and MySQL WorkBench

Wednesday, August 4th, 2010
Reverse Engineering is a very powerful technique for discovering the structures of databases.It is particularly useful for operational databases for which no up-to-date documentation is available. This will be very useful when you are taking up an application for either support/development. Basically this gives the understanding between the tables and their relationships. We have many commercial tools available to generate the Reverse Engineering, Now we’ll see the MS Visio 2003 to generate the reverse engineering in Oracle 9i.
The steps for creating the reverse engineering in Visio as follows,

  1. Create a User DSN in Administrative Tools -> Data Sources (ODBC)
  2. Set-up the ODBC Bridge Details for Oracle. These two steps creates the DSN for you.
  3. Go to DataBase -> Reverse Engineer in Visio.
  4. In Reverse Eng Wizard -> Select the Appropriate Installed Visio Drivers -> Setup
  5. In ODBC Generic Driver Setup -> Select your associated Drivers and Select the DSN also
  6. In Reverse Eng Wizard -> Select the DSN added -> Next
  7. In Reverse Eng Wizard -> Select the Objects to Reverse Engineer -> Next
  8. In Reverse Eng Wizard -> Select the table to Reverse Engineer -> Finish
Create a User DSN in Administrative Tools -> Data Sources (ODBC)
Adding DSN
Set-up the ODBC Bridge Details for Oracle.
Configure DSN
Go to DataBase -> Reverse Engineer in Visio.
Reverse Engineering Visio
Select the Appropriate Installed Visio Drivers.
Selecting the DSN
Select your associated Drivers and Select the DSN also.
Select the Drivers
Select the DSN added -> Next
Selecting the DSN
Select the Objects to Reverse Engineer
Object Type
Select the table to Reverse Engineer
Select Tables
Final ER Diagram
DBBM-Reverse Engg
Reverse Eng. With MySQL Workbench
The exact procedure to do reverse engineering with MySQL Workbench is available here.
Final Reverse Eng. With MySQL Workbench
MySqlWB-Reverse Engineering
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)

Generating Domain Objects Using Hibernate

Thursday, May 20th, 2010
In my previous post Hibernate, the ORM Library for Java, we have seen the General introduction of ORM Tools and how to configure the hibernate to use it in our applications. In this post we’ll look at the simple examples of DB Operations using Hibernate.
Please have all the libraries needed to work with MySQL with Hibernate, the most important libraries are hibernate3.jar and mysqlconnector.jar.(If you are using MyEclipse, that’ll help you in adding those jars). For using the MySQL the hibernate configuration will look like the following.
		<hibernate-configuration>
			<session-factory>
			<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
			<property name="hibernate.connection.url">jdbc:mysql://localhost/Singer</property>
			<property name="hibernate.connection.username">root</property>
			<property name="connection.password">demo</property>
			<property name="connection.pool_size">1</property>
			<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
			<property name="show_sql">true</property>
			<property name="hbm2ddl.auto">create</property>
			</session-factory>
		</hibernate-configuration>
	
For domain class we’ll take the example of Singer, Who performs some stage shows. As of now we’ll add two properties for him, Which is id and the name of the singer. So we’ll have to configure the Singer.hbm.xml file for Singer as follows.
		<hibernate-mapping>
		<class name="com.techmaddy.singer" table="TBL_SINGER">
			<meta attribute="class-description">Domain Object for Singer</meta>
			<id name="singerId" type="long" column="SINGER_ID">
				<generator class="native"/>
			</id>
			<property name="singerName" type="string" column="SINGER_NAME" not-null="true" />
		</class>
		</hibernate-mapping>
	
We can make use of the MyEclipse’s “Hibernate Reverse Engineering” option from the database perspective, so that we can easily generate the Singer.hbm.xml and Singer.java domain class also.
			package com.techmaddy.singer;

			public class Singer implements java.io.Serializable {

				private Long singerId;

				private String singerName;

				public Singer() {

				}

				public Singer(String singerName) {
					this.singerName = singerName;
				}

				public Long getSingerId() {
					return this.singerId;
				}

				public void setSingerId(Long singerId) {
					this.singerId = singerId;
				}

				public String getSingerName() {
					return this.singerName;
				}

				public void setSingerName(String singerName) {
					this.singerName = singerName;
				}

			}
	
This above code ss an domain class for Singer, It’s just nothing but an POJO or we can say an JavaBean. The other important component fo Hibernate is SessionFactory. The SessionFactory provides the session to work with RDBMS. SessionFactory should be created only once per application on web container. SessionFactory can be created many ways, most of the time SessionFactory will be created as a Singleton utility class.By using the following methods, we can creating the SessionFactory, they are

  • An Utility Class with Singleton - A Singleton class with the option to create an SessionFactory and then the object can be made available to JVM.
  • From an Servlet - A servlet which is loaded on startup of the container and the service method pushes the object into an Singleton Utility class
  • Static Block - inside a static block in some sort of Utility Java class that can be used in program code to retrieve SessionFactory

We’ll implement the third option to create the SessionFactory.


		package com.techmaddy.singer

		public class FactoryUtil {

			//Static Session Factory
			private static org.hibernate.SessionFactory sessionFactory;

			private FactoryUtil() {

			}

			static {
				//Creates the SessionFactory based on the XML Configuration
				Configuration configs = new Configuration();
				sessionFactory = configs.configure().buildSessionFactory();
			}

			public static SessionFactory getInstance() {
				return sessionFactory;
			}

			public Session openSession() {
				return sessionFactory.openSession();
			}

			public Session getCurrentSession() {
				return sessionFactory.getCurrentSession();
			}

			public static void close() {
				if (sessionFactory != null)
					sessionFactory.close();
				sessionFactory = null;
			}
		}
	
The above code creates the SessionFactory, since the creation of SessionFactory is in Static block, we’ll be able control the creation of SessionFactory object. By this way we”l have the control of creating the session obejcts. The SessionFactory reads the hibernate cfg xml and then it creates the session object for our application. We are all set to go and write our class, to test the hibernate.
Basically we’ll look into the example of CRUD by making use of the Hibernate Session object.

		package com.techmaddy.singer;

		import org.hibernate.HibernateException;
		import org.hibernate.Session;
		import org.hibernate.Transaction;

		public class CRUDTest {

			public static void main(String[] args) {
				CRUDTest crudTest = new CRUDTest();
				Long singer = crudTest.insertSinger("Jason");
				singer = crudTest.insertSinger("Jerry");
				singer = crudTest.insertSinger("Chun");
			}

			public Long insertSinger(String singerName) {
				Session session = FactoryUtil.getSessionFactory().openSession();
				Transaction transaction = null;
				Long singerId = null;
				try {
					transaction = session.beginTransaction();
					Singer singer = new Singer();
					singer.setSingerName(singerName);
					singerId = (Long)session.save(singer);
					transaction.commit();
				} catch (HibernateException e) {
					transaction.rollback();
					e.printStackTrace();
				} finally {
					session.close();
				}
				return singerId;
			}
		}
	
When we run this following code, we’ll get the output in the console as follows,this is since we mentioned “show_sql” property in hibernate.cfg.xml to “true”.
Console
After executing this statement, we can check the database for inserted values.

Transaction

I’ve added the update and delete operations in CRUDTest and it has been updated as follows
		package com.techmaddy.singer;

		import java.util.Iterator;
		import java.util.List;

		import org.hibernate.HibernateException;
		import org.hibernate.Session;
		import org.hibernate.Transaction;

		public class CRUDTest {

			public static void main(String[] args) {
				CRUDTest crudTest = new CRUDTest();
				Long singer = crudTest.insertSinger("William");
				singer = crudTest.insertSinger("Jerry");
				Long tempSinger = singer;
				singer = crudTest.insertSinger("Stanley");
				crudTest.listSinger();

				crudTest.updateSinger(singer,"Chun");

				crudTest.deleteSinger(tempSinger);
			}

			public Long insertSinger(String singerName) {
				Session session = HibernateSessionFactory.getSessionFactory().openSession();
				Transaction transaction = null;
				Long singerId = null;
				try {
					transaction = session.beginTransaction();
					Singer singer = new Singer();
					singer.setSingerName(singerName);
					singerId = (Long)session.save(singer);
					transaction.commit();
				} catch (HibernateException e) {
					transaction.rollback();
					e.printStackTrace();
				} finally {
					session.close();
				}
				return singerId;
			}

			public void listSinger() {
				Session session = HibernateSessionFactory.getSessionFactory().openSession();
				Transaction transaction = null;
				try {
					transaction = session.beginTransaction();
					List singers = session.createQuery("FROM Singer").list();
					for (Iterator iterator = singers.iterator(); iterator.hasNext();) {
						Singer singer = (Singer) iterator.next();
						System.out.println(singer.getSingerName());
					}
					transaction.commit();
				}
				catch (HibernateException e) {
					transaction.rollback();
					e.printStackTrace();
				}
				finally {
					session.close();
				}
			}

			public void updateSinger(Long singerId, String singerName) {
				Session session = HibernateSessionFactory.getSessionFactory().openSession();
				Transaction transaction = null;
				try {
					transaction = session.beginTransaction();
					Singer singer = (Singer) session.get(Singer.class, singerId);
					singer.setSingerName(singerName);
					session.update(singer);
					transaction.commit();
				} catch (HibernateException e) {
					transaction.rollback();
					e.printStackTrace();
				} finally {
					session.close();
				}
			}

			public void deleteSinger(Long singerId) {
				Session session = HibernateSessionFactory.getSessionFactory().openSession();
				Transaction transaction = null;
				try {
					transaction = session.beginTransaction();
					Singer singer = (Singer) session.get(Singer.class, singerId);
					session.delete(singer);
					transaction.commit();
				} catch (HibernateException e) {
					transaction.rollback();
					e.printStackTrace();
				} finally {
					session.close();
				}
			}

		}
	
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