Most commercial applications dealing with any form of data provide a way to publish reports from the application in some form or manner. And when this application uses data from the back-end database, JasperReport is ideal to publish them in variety of formats (PDF, Excel, XML etc.). How do we program them is the real question? If you want to hand design the report go for iReport and design a JRXML template otherwise you can go dynamic with DynamicJasper as we shall see down the line.
Basic Convention
JasperReport library provides primarily two different usages to generate database reports.
1. Embedding SQL queries into a JRXML template.
2. Database reporting via data source.
Embedding SQL Queries into a JRXML Template
We can use the <queryString…> element to embed SQL queries into a JRXML file. The XML code for the report is as follows:
Listing 1: report1.jrxml
<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance" xsi_schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="null" language="groovy" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="9e4dbbf4-99d5-414f-8b4a-6769adf2df86">
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<style name="Title" fontName="Times New Roman" fontSize="50" isBold="true" pdfFontName="Times-Bold"/>
<style name="SubTitle" forecolor="#736343" fontName="Arial" fontSize="18"/>
<style name="Column header" forecolor="#666666" fontName="Arial" fontSize="12" isBold="true"/>
<style name="Detail" fontName="Arial" fontSize="12"/>
<style name="Row" mode="Transparent">
<conditionalStyle>
<conditionExpression><![CDATA[$V{REPORT_COUNT}%2 == 0]]></conditionExpression>
<style backcolor="#E6DAC3"/>
</conditionalStyle>
</style>
<queryString language="SQL">
<![CDATA[SELECT
EMPLOYEE.`ID` AS EMPLOYEE_ID,
EMPLOYEE.`BDATE` AS EMPLOYEE_BDATE,
EMPLOYEE.`DESIGNATION` AS EMPLOYEE_DESIGNATION,
EMPLOYEE.`EMPNAME` AS EMPLOYEE_EMPNAME,
EMPLOYEE.`SALARY` AS EMPLOYEE_SALARY
FROM
`EMPLOYEE` EMPLOYEE]]>
</queryString>
<field name="EMPLOYEE_ID" class="java.lang.Integer"/>
<field name="EMPLOYEE_BDATE" class="java.sql.Date"/>
<field name="EMPLOYEE_DESIGNATION" class="java.lang.String"/>
<field name="EMPLOYEE_EMPNAME" class="java.lang.String"/>
<field name="EMPLOYEE_SALARY" class="java.lang.Float"/>
<background>
<band splitType="Stretch"/>
</background>
<title>
<band height="88" splitType="Stretch">
<staticText>
<reportElement style="Title" x="160" y="0" width="412" height="45" uuid="6df13f4e-594d-4189-84cf-438473f5e2b3"/>
<textElement textAlignment="Right" verticalAlignment="Middle">
<font size="36" pdfFontName="Times-Roman"/>
</textElement>
<text><![CDATA[XYZ Corporation]]></text>
</staticText>
<staticText>
<reportElement style="SubTitle" x="160" y="45" width="412" height="22" uuid="44d84998-2da9-4973-ab79-6aeca035f672"/>
<textElement textAlignment="Right">
<font fontName="Times New Roman" pdfFontName="Times-Roman"/>
</textElement>
<text><![CDATA[List of Employees]]></text>
</staticText>
</band>
</title>
<pageHeader>
<band splitType="Stretch"/>
</pageHeader>
<columnHeader>
<band height="16" splitType="Stretch">
<line>
<reportElement positionType="FixRelativeToBottom" x="0" y="15" width="802" height="1" uuid="07efa7bd-1315-42ed-b5e3-a9a6392da586"/>
<graphicElement>
<pen lineWidth="0.5" lineColor="#999999"/>
</graphicElement>
</line>
<staticText>
<reportElement style="Column header" x="0" y="0" width="160" height="15" forecolor="#736343" uuid="ca74c714-ea80-4e5e-816e-0c31710ac2c1"/>
<text><![CDATA[ID]]></text>
</staticText>
<staticText>
<reportElement style="Column header" x="135" y="0" width="160" height="15" forecolor="#736343" uuid="660123e7-cb9e-4aea-a6f2-50d0ebfdcb33"/>
<text><![CDATA[BirthDate]]></text>
</staticText>
<staticText>
<reportElement style="Column header" x="320" y="0" width="160" height="15" forecolor="#736343" uuid="23d7a717-aca4-4111-876a-14cd945f9740"/>
<text><![CDATA[Designation]]></text>
</staticText>
<staticText>
<reportElement style="Column header" x="480" y="0" width="160" height="15" forecolor="#736343" uuid="d1a3e6c3-2fd3-4ed6-bd84-315894889986"/>
<text><![CDATA[Name]]></text>
</staticText>
<staticText>
<reportElement style="Column header" x="640" y="0" width="160" height="15" forecolor="#736343" uuid="d79b47a4-8afc-4182-89d1-aa7997ada796"/>
<text><![CDATA[Salary]]></text>
</staticText>
</band>
</columnHeader>
<detail>
<band height="15" splitType="Stretch">
<frame>
<reportElement style="Row" mode="Opaque" x="0" y="0" width="802" height="15" uuid="15475ecc-bada-4c79-b54a-9f0411f8b5c6"/>
<textField isStretchWithOverflow="true">
<reportElement style="Detail" x="0" y="0" width="114" height="15" uuid="e81e67da-2ee9-4ed6-a5c6-025bfce14561"/>
<textFieldExpression><![CDATA[$F{EMPLOYEE_ID}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true">
<reportElement style="Detail" x="135" y="0" width="160" height="15" uuid="4128a636-ca1e-4099-a9a5-9e307366cdbb"/>
<textFieldExpression><![CDATA[$F{EMPLOYEE_BDATE}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true">
<reportElement style="Detail" x="320" y="0" width="160" height="15" uuid="27089e3f-adf5-46ba-b83a-f685c1727f3a"/>
<textFieldExpression><![CDATA[$F{EMPLOYEE_DESIGNATION}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true">
<reportElement style="Detail" x="480" y="0" width="160" height="15" uuid="20c38162-1b00-44d4-b34c-ab54179dd9d7"/>
<textFieldExpression><![CDATA[$F{EMPLOYEE_EMPNAME}]]></textFieldExpression>
</textField>
<textField isStretchWithOverflow="true">
<reportElement style="Detail" x="640" y="0" width="160" height="15" uuid="09d0a547-7374-4c5f-9bce-25d842ed5dc3"/>
<textFieldExpression><![CDATA[$F{EMPLOYEE_SALARY}]]></textFieldExpression>
</textField>
</frame>
</band>
</detail>
<columnFooter>
<band height="6" splitType="Stretch">
<line>
<reportElement positionType="FixRelativeToBottom" x="0" y="3" width="802" height="1" uuid="e39eb7aa-00fe-46d6-bd75-dc6ffbe9014e"/>
<graphicElement>
<pen lineWidth="0.5" lineColor="#999999"/>
</graphicElement>
</line>
</band>
</columnFooter>
<pageFooter>
<band height="25" splitType="Stretch">
<frame>
<reportElement mode="Opaque" x="-21" y="1" width="843" height="24" forecolor="#D0B48E" backcolor="#F2EBDF" uuid="1cb890b4-269b-49aa-bf41-0e9f4aa6918c"/>
<textField evaluationTime="Report">
<reportElement style="Column header" x="783" y="1" width="40" height="20" forecolor="#736343" uuid="dac5ce19-4958-47c2-9869-8723ba686bb2"/>
<textElement verticalAlignment="Middle">
<font size="10" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression>
</textField>
<textField>
<reportElement style="Column header" x="703" y="1" width="80" height="20" forecolor="#736343" uuid="04737a56-4421-4ae1-8c52-c25dd6976fd0"/>
<textElement textAlignment="Right" verticalAlignment="Middle">
<font size="10" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA["Page "+$V{PAGE_NUMBER}+" of"]]></textFieldExpression>
</textField>
<textField pattern="EEEEE dd MMMMM yyyy">
<reportElement style="Column header" x="22" y="1" width="197" height="20" forecolor="#736343" uuid="e11d7468-f377-4a69-b37a-1909f9be64e3"/>
<textElement verticalAlignment="Middle">
<font size="10" isBold="false"/>
</textElement>
<textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>
</textField>
</frame>
</band>
</pageFooter>
<summary>
<band splitType="Stretch"/>
</summary>
</jasperReport>
The java class to fill the report from the database and to view it is as follows
Listing 2: DataReport.java
//...import statements
public class DataReport {
Connection con;
public void createReport() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase?zeroDateTimeBehavior=convertToNull", "user1", "secret");
JasperPrint jp;
jp = JasperFillManager.fillReport("report/report1.jasper", new HashMap(), con);
JasperViewer jv = new JasperViewer(jp);
jv.setVisible(true);
con.close();
} catch (ClassNotFoundException | SQLException | JRException ex) {
ex.printStackTrace();
}
}
public static void main(String[] args) {
new DataReport().createReport();
}
}
Database Reporting via Data Source
Suppose we want to feed the SQL statement from Java code and not from a JRXML template. In that case remove the <queryString…> element from JRXML as shown in Listing 1 and write the code given in Listing 3.
Listing 3: DataReport2.java
//...import statements
public class DataReport2 {
Connection con;
Statement stmt;
ResultSet rs;
public void createReport() {
try {
String query="select e.ID as EMPLOYEE_ID, e.BDATE as EMPLOYEE_BDATE,e.DESIGNATION as EMPLOYEE_DESIGNATION,e.EMPNAME as EMPLOYEE_EMPNAME,e.SALARY as EMPLOYEE_SALARY from EMPLOYEE e";
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase?zeroDateTimeBehavior=convertToNull", "user1", "secret");
stmt=con.createStatement();
rs=stmt.executeQuery(query);
JRResultSetDataSource rsdt=new JRResultSetDataSource(rs);
JasperPrint jp;
jp = JasperFillManager.fillReport("report/report1.jasper", new HashMap(), rsdt);
JasperViewer jv = new JasperViewer(jp);
jv.setVisible(true);
con.close();
} catch (ClassNotFoundException | SQLException | JRException ex) {
ex.printStackTrace();
}
}
public static void main(String[] args) {
new DataReport2().createReport();
}
}
A Little Deviation
In our next application we are going to deviate a little while fiddling with – DynamicJasper, JasperReport, and Hibernate Library and integrate them to bring out the chemistry dynamic database reporting.
Listing 4: Entity bean Employee.java
//...import statements
@Table(name = "EMPLOYEE")
@Entity
public class Employee implements Serializable {
@Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "ID")
private int empId;
@Column(name = "EMPNAME")
private String name;
@Column(name = "BDATE") @Temporal(TemporalType.DATE)
private Date birthDate;
@Column(name = "DESIGNATION")
private String designation;
@Column(name = "SALARY")
private float salary;
// constructors, getters and setters
}
Listing 5: Utility class for configuring Hibernate, HibernateUtil.java
//...import statements
public class HibernateUtil {
private static final SessionFactory sessionFactory;
private static final ServiceRegistry serviceRegistry;
static {
try {
Configuration config = getConfiguration();
serviceRegistry = new ServiceRegistryBuilder().applySettings(
config.getProperties()).buildServiceRegistry();
config.setSessionFactoryObserver(new SessionFactoryObserver() {
private static final long serialVersionUID = 1L;
@Override
public void sessionFactoryCreated(SessionFactory factory) {
}
@Override
public void sessionFactoryClosed(SessionFactory factory) {
ServiceRegistryBuilder.destroy(serviceRegistry);
}
});
sessionFactory = config.buildSessionFactory(serviceRegistry);
} catch (HibernateException ex) {
System.err.println("Initial SessionFactory creation failed." + ex);
throw new ExceptionInInitializerError(ex);
}
}
public static Session openSession() {
return sessionFactory.openSession();
}
private static Configuration getConfiguration() {
Configuration cfg = new Configuration();
cfg.addAnnotatedClass(Employee.class);
cfg.setProperty("hibernate.connection.driver_class", "com.mysql.jdbc.Driver");
cfg.setProperty("hibernate.connection.url", "jdbc:mysql://localhost:3306/mydatabase?zeroDateTimeBehavior=convertToNull");
cfg.setProperty("hibernate.connection.username", "user1");
cfg.setProperty("hibernate.connection.password", "secret");
cfg.setProperty("hibernate.show_sql", "true");
cfg.setProperty("hibernate.dialect", "org.hibernate.dialect.MySQLDialect");
cfg.setProperty("hibernate.hbm2ddl.auto", "create-drop");
cfg.setProperty("hibernate.cache.provider_class", "org.hibernate.cache.NoCacheProvider");
cfg.setProperty("hibernate.current_session_context_class", "thread");
return cfg;
}
}
Listing 6: Data Access object, EmployeeDao.java
//...import statements
public class EmployeeDao {
public Employee save(Employee emp){
Session session=HibernateUtil.openSession();
session.beginTransaction();
session.saveOrUpdate(emp);
session.getTransaction().commit();
session.close();
return emp;
}
public Collection<Employee> findAll(){
Session session=HibernateUtil.openSession();
return session.createQuery("from Employee").list();
}
}
Listing 7: Dynamic reporting class, EmployeeReport.java
//...import statements
public class EmployeeReport {
private final Collection<Employee> list=new ArrayList<>();
public EmployeeReport(Collection<Employee> p){
list.addAll(p);
}
public JasperPrint getReport() throws ColumnBuilderException, JRException, ClassNotFoundException {
Style headerStyle = createHeaderStyle();
Style detailTextStyle = createDetailTextStyle();
Style detailNumberStyle = createDetailNumberStyle();
//FastReportBuilder firstReport = getReport(headerStyle, detailTextStyle,detailNumberStyle);
DynamicReport dynaReport = getReport(headerStyle, detailTextStyle,detailNumberStyle);
JasperPrint jp = DynamicJasperHelper.generateJasperPrint(dynaReport, new ClassicLayoutManager(), new JRBeanCollectionDataSource(list));
return jp;
}
private Style createHeaderStyle() {
StyleBuilder sb=new StyleBuilder(true);
sb.setFont(Font.VERDANA_MEDIUM_BOLD);
sb.setBorder(Border.THIN());
sb.setBorderBottom(Border.PEN_2_POINT());
sb.setBorderColor(Color.BLACK);
sb.setBackgroundColor(Color.ORANGE);
sb.setTextColor(Color.BLACK);
sb.setHorizontalAlign(HorizontalAlign.CENTER);
sb.setVerticalAlign(VerticalAlign.MIDDLE);
sb.setTransparency(Transparency.OPAQUE);
return sb.build();
}
private Style createDetailTextStyle(){
StyleBuilder sb=new StyleBuilder(true);
sb.setFont(Font.VERDANA_MEDIUM);
sb.setBorder(Border.DOTTED());
sb.setBorderColor(Color.BLACK);
sb.setTextColor(Color.BLACK);
sb.setHorizontalAlign(HorizontalAlign.LEFT);
sb.setVerticalAlign(VerticalAlign.MIDDLE);
sb.setPaddingLeft(3);
return sb.build();
}
private Style createDetailNumberStyle(){
StyleBuilder sb=new StyleBuilder(true);
sb.setFont(Font.VERDANA_MEDIUM);
sb.setBorder(Border.DOTTED());
sb.setBorderColor(Color.BLACK);
sb.setTextColor(Color.BLACK);
sb.setHorizontalAlign(HorizontalAlign.RIGHT);
sb.setVerticalAlign(VerticalAlign.MIDDLE);
sb.setPaddingRight(5);
return sb.build();
}
private AbstractColumn createColumn(String property, Class type,
String title, int width, Style headerStyle, Style detailStyle)
throws ColumnBuilderException {
AbstractColumn columnState = ColumnBuilder.getNew()
.setColumnProperty(property, type.getName()).setTitle(
title).setWidth(Integer.valueOf(width))
.setStyle(detailStyle).setHeaderStyle(headerStyle).build();
return columnState;
}
private DynamicReport getReport(Style headerStyle, Style detailTextStyle, Style detailNumStyle) throws ColumnBuilderException, ClassNotFoundException {
DynamicReportBuilder report=new DynamicReportBuilder();
AbstractColumn columnEmpId = createColumn("empId", Integer.class,"ID", 10, headerStyle, detailNumStyle);
AbstractColumn columnName = createColumn("name", String.class,"Name", 30, headerStyle, detailTextStyle);
AbstractColumn columnBirthDate = createColumn("birthDate", Date.class,"Salary", 20, headerStyle, detailTextStyle);
AbstractColumn columnDesignation = createColumn("designation", String.class,"Commission", 30, headerStyle, detailTextStyle);
AbstractColumn columnSalary = createColumn("salary", Float.class,"Commission", 15, headerStyle, detailNumStyle);
report.addColumn(columnEmpId) .addColumn(columnName).addColumn(columnBirthDate).addColumn(columnDesignation).addColumn(columnSalary);
StyleBuilder titleStyle=new StyleBuilder(true);
titleStyle.setHorizontalAlign(HorizontalAlign.CENTER);
titleStyle.setFont(new Font(20, Font._FONT_GEORGIA, true));
StyleBuilder subTitleStyle=new StyleBuilder(true);
subTitleStyle.setHorizontalAlign(HorizontalAlign.CENTER);
subTitleStyle.setFont(new Font(Font.MEDIUM, Font._FONT_GEORGIA, true));
report.setTitle("Employee Report");
report.setTitleStyle(titleStyle.build());
report.setSubtitle("List of Employees");
report.setSubtitleStyle(subTitleStyle.build());
report.setUseFullPageWidth(true);
return report.build();
}
}
Listing 8: main class to feed data into the database and show report, JavaDBReport2.java
//...import statements
public class JavaDBReport2 {
public static void main(String[] args) {
Employee e1 = new Employee("John Smith", new Date(), "System Analyst", 55000.00f);
//...
Employee e13 = new Employee("Tom Hanks", new Date(), "Sr. Programmer", 73000.00f);
EmployeeDao ed = new EmployeeDao();
ed.save(e1);
//...
ed.save(e13);
EmployeeReport report = new EmployeeReport(ed.findAll());
try {
JasperPrint jp = report.getReport();
JasperViewer jasperViewer = new JasperViewer(jp);
jasperViewer.setVisible(true);
} catch (JRException | ClassNotFoundException ex) {
}
}
}
Conclusion
The three sample applications demonstrate a few of the ways a report can be generated using the JasperReport library. The last application shows the unison of three important frameworks altogether and how to create a dynamic report from the database as well. Creating dynamic reports has one disadvantage. It is not possible to preview the looks of the report whereas we can see the preview to JRXML template at design time. Both ways are great; choose according to your requirement and preference.
