Birt Dynamic Table Creation from JDBC Mysql Connection with Maven

In this post refers how to create dynamic Birt reports using given customizing fields list using Birt Runtime 4.2.2

Step 01 : First need to download birt runtime. Download from here
For view/Edit the rptdesign files, it's better to use Eclipes 4.2.2 Birt integration. Download eclipes here

Step 02 : Extract the birt-runtime-4_2_2. and set the birt runtime home environment variable.

e.g. in my case i'm set the environment variable in .bash_profile since i'm using Fedora. (we can specify the Birt home in java code as wel)

export BIRT_HOME=/home/malith/usr/eclips/birt-runtime-4_2_2


Step 03 : Create Maven project and insert the dependency for Birt Runtime

        <dependency>
            <groupId>org.eclipse.birt.runtime</groupId>
            <artifactId>org.eclipse.birt.runtime</artifactId>
            <version>4.2.2</version>
        </dependency>

This is my project structure and I used the Intelj Idea as my IDE.

Buld the project in console to download all the birt-runtime dependecies

e.g. mvn clean install
 







pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>birt</groupId>
    <artifactId>birt-dynamic</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.eclipse.birt.runtime</groupId>
            <artifactId>org.eclipse.birt.runtime</artifactId>
            <version>4.2.2</version>
        </dependency>
    </dependencies>
   
</project>

Step 4: Add test.rptdesign file. This file could be the basic birt design or template which suitable for target sample_jdbc.rptdesign file. Following empty design could be created from the Eclipes.

test.rptdesign

<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.23" id="1">
    <property name="createdBy">Eclipse BIRT Designer Version 4.2.2.v201301221637 Build &lt;4.2.2.v20130206-1509></property>
    <property name="units">in</property>
    <property name="iconFile">/templates/blank_report.gif</property>
    <property name="bidiLayoutOrientation">ltr</property>
    <property name="imageDPI">96</property>
    <styles>
        <style name="report" id="4">
            <property name="fontFamily">sans-serif</property>
            <property name="fontSize">10pt</property>
        </style>
        <style name="crosstab-cell" id="5">
            <property name="borderBottomColor">#CCCCCC</property>
            <property name="borderBottomStyle">solid</property>
            <property name="borderBottomWidth">1pt</property>
            <property name="borderLeftColor">#CCCCCC</property>
            <property name="borderLeftStyle">solid</property>
            <property name="borderLeftWidth">1pt</property>
            <property name="borderRightColor">#CCCCCC</property>
            <property name="borderRightStyle">solid</property>
            <property name="borderRightWidth">1pt</property>
            <property name="borderTopColor">#CCCCCC</property>
            <property name="borderTopStyle">solid</property>
            <property name="borderTopWidth">1pt</property>
        </style>
        <style name="crosstab" id="6">
            <property name="borderBottomColor">#CCCCCC</property>
            <property name="borderBottomStyle">solid</property>
            <property name="borderBottomWidth">1pt</property>
            <property name="borderLeftColor">#CCCCCC</property>
            <property name="borderLeftStyle">solid</property>
            <property name="borderLeftWidth">1pt</property>
            <property name="borderRightColor">#CCCCCC</property>
            <property name="borderRightStyle">solid</property>
            <property name="borderRightWidth">1pt</property>
            <property name="borderTopColor">#CCCCCC</property>
            <property name="borderTopStyle">solid</property>
            <property name="borderTopWidth">1pt</property>
        </style>
    </styles>
    <page-setup>
        <simple-master-page name="Simple MasterPage" id="2">
            <page-footer>
                <text id="3">
                    <property name="contentType">html</property>
                    <text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
                </text>
            </page-footer>
        </simple-master-page>
    </page-setup>
</report>

Step 5: Implement java class for create dynamic tables.
 (In my example, I have used the mysql database call "local_db" and table called "localization" with several fields mention in java code)

In here we use the eclipes birt model API as dependency for implementations. Build the project once as mention in Step 3 if dependencies not available in your local repositories.

BirtDynamicJDBC.java

import com.ibm.icu.util.ULocale;
import org.eclipse.birt.core.framework.Platform;
import org.eclipse.birt.report.model.api.*;
import org.eclipse.birt.report.model.api.activity.SemanticException;
import org.eclipse.birt.report.model.api.elements.structures.ComputedColumn;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * Dynamic Table BIRT Design Engine API (DEAPI) demo using JDBC mysql connection.
 */
public class BirtDynamicTableJDBC {

    ReportDesignHandle designHandle = null;
    ElementFactory designFactory = null;
    StructureFactory structFactory = null;

    public static void main(String[] args) {
        try {
            BirtDynamicTableJDBC de = new BirtDynamicTableJDBC();
            /**
             * In my case, I have created database call local_db and table call localization including the following
             * fields. Add more fields than followings.
             */
            List<String> al = new ArrayList<String>();
            al.add("local_id");
            al.add("local_description");
            al.add("local_key");
            de.buildReport((ArrayList) al, "From localization");
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SemanticException e) {
            e.printStackTrace();
        }
    }

    void buildDataSource() throws SemanticException {
        OdaDataSourceHandle dsHandle = designFactory.newOdaDataSource("elg_report", "org.eclipse.birt.report.data.oda.jdbc");
        dsHandle.setProperty("odaDriverClass", "com.mysql.jdbc.Driver");
        dsHandle.setProperty("odaURL", "jdbc:mysql://localhost:3306/local_db?createDatabaseIfNotExist=true");
        dsHandle.setProperty("odaUser", "root");
        dsHandle.setProperty("odaPassword", "password");
        designHandle.getDataSources().add(dsHandle);
    }

    void buildDataSet(ArrayList cols, String fromClause) throws SemanticException {
        OdaDataSetHandle dsHandle = designFactory.newOdaDataSet("ds", "org.eclipse.birt.report.data.oda.jdbc.JdbcSelectDataSet");
        dsHandle.setDataSource("elg_report");
        String qry = "Select ";
        for (int i = 0; i < cols.size(); i++) {
            qry += " " + cols.get(i);
            if (i != (cols.size() - 1)) {
                qry += ",";
            }
        }
        qry += " " + fromClause;
        dsHandle.setQueryText(qry);
        designHandle.getDataSets().add(dsHandle);
    }

    void buildReport(ArrayList cols, String fromClause) throws IOException, SemanticException {
        //Configure the Engine and start the Platform
        DesignConfig config = new DesignConfig();
        // When using maven dependency for birt runtime, it's no need to add birt runtime path like below
        config.setProperty("BIRT_HOME", "/home/malith/usr/eclips/birt-runtime-4_2_2/");
        IDesignEngine engine = null;
        try {
            Platform.startup(config);
            IDesignEngineFactory factory = (IDesignEngineFactory) Platform.createFactoryObject(IDesignEngineFactory.EXTENSION_DESIGN_ENGINE_FACTORY);
            engine = factory.createDesignEngine(config);
        } catch (Exception ex) {
            engine = new DesignEngine(config);
            ex.printStackTrace();
        }
        SessionHandle session = engine.newSessionHandle(ULocale.ENGLISH);
        try {
            /**
             * Open a design or a template which already created
             * Change the following path according to your local machine environment
             */
            designHandle = session.openDesign("/home/malith/Projects/elg/training/samples/birt-dynamic/rptdesigns/test.rptdesign");
            designFactory = designHandle.getElementFactory();
            buildDataSource();
            buildDataSet(cols, fromClause);
            TableHandle table = designFactory.newTableItem("table", cols.size());
            table.setWidth("100%");
            table.setDataSet(designHandle.findDataSet("ds"));
            PropertyHandle computedSet = table.getColumnBindings();
            ComputedColumn cs1 = null;
            for (int i = 0; i < cols.size(); i++) {
                cs1 = StructureFactory.createComputedColumn();
                cs1.setName((String) cols.get(i));
                cs1.setExpression("dataSetRow[\"" + (String) cols.get(i) + "\"]");
                computedSet.addItem(cs1);
            }
            // table header
            RowHandle tableheader = (RowHandle) table.getHeader().get(0);
            for (int i = 0; i < cols.size(); i++) {
                LabelHandle label1 = designFactory.newLabel((String) cols.get(i));
                label1.setText((String) cols.get(i));
                CellHandle cell = (CellHandle) tableheader.getCells().get(i);
                cell.getContent().add(label1);
            }
            // table detail
            RowHandle tabledetail = (RowHandle) table.getDetail().get(0);
            for (int i = 0; i < cols.size(); i++) {
                CellHandle cell = (CellHandle) tabledetail.getCells().get(i);
                DataItemHandle data = designFactory.newDataItem("data_" + (String) cols.get(i));
                data.setResultSetColumn((String) cols.get(i));
                cell.getContent().add(data);
            }
            designHandle.getBody().add(table);
            /**Save the design and closeit
             * Change the following path according to your local machine environment
             */
            designHandle.saveAs("/home/malith/Projects/elg/training/samples/birt-dynamic/rptdesigns/sample_jdbc.rptdesign"); //$NON-NLS-1$
            designHandle.close();
            System.out.println("Finished JDBC");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Note : Change the paths according to your environment.

clone sample code from git hub or refer the code here

git clone https://github.com/malithmeee/birt-dynamic-sample.git