ibatis Posts

Creating a Simple Reporting Application using JasperReport and iBatis Framework

In this article i’m trying to create a very simple reporting application. In my several past projects, i used to do reporting with java swing and jasper report, and im connecting both of them using a simple JDBC and java.sql.Connection. But in recent project, i had to used iBatis Framework for replacing JDBC. So i had to spend several hours finding a workaround on how connecting Jasper with iBatis, but thanks to uncle Google i finally found a very simple but elegant workaround.
Enough with the chit-chat, i’ll show you the code. First, as always, a simple database named “test” and a simple table named “contoh”.

CREATE DATABASE 'test'
USE 'test'

CREATE TABLE `contoh` (
  `nama` varchar(30) NOT NULL DEFAULT '',
  `alamat` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`nama`)
)

next step is creating a java bean for database mapping

package com.edw.bean;

public class Contoh {

    private String nama;
    private String alamat;

    public String getAlamat() {
        return alamat;
    }

    public void setAlamat(String alamat) {
        this.alamat = alamat;
    }

    public String getNama() {
        return nama;
    }

    public void setNama(String nama) {
        this.nama = nama;
    }
    
}

and an xml mapping for database queries, i named it contoh.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" 
"http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="contoh" >

  <resultMap id="result" class="com.edw.bean.Contoh">
        <result property="nama" column="NAMA" columnIndex="1"/>
        <result property="alamat" column="ALAMAT" columnIndex="2"/>
    </resultMap>

    <insert id="insertContoh" parameterClass="com.edw.bean.Contoh" >
    insert into contoh (nama, alamat)
    values (#nama:VARCHAR#, #alamat:VARCHAR#)
    </insert>

    <select id="selectAllContoh" resultMap="result">
    select nama, alamat
    from contoh
    </select>

</sqlMap>

and ibatis’ main xml configuration, sqlmapconfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <settings
        useStatementNamespaces="true"
        lazyLoadingEnabled="true"
        enhancementEnabled="true"
        maxSessions="20"
        />

    <transactionManager type="JDBC" commitRequired="false">
        <dataSource type="SIMPLE">

            <property name="SetAutoCommitAllowed" value="false"/>
            <property name="DefaultAutoCommit" value="false"/>
            
            <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
            <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost/test"/>
            <property name="JDBC.Username" value="root"/>
            <property name="JDBC.Password" value="password"/>
   
        </dataSource>
    </transactionManager>

    <sqlMap resource="com/edw/sqlmap/contoh.xml"/>
</sqlMapConfig>

next step is creating a simple java class to load all iBatis’ configuration file.

package com.edw.config;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.IOException;
import java.io.Reader;


/**
 *
 * @author edw
 */
public class SqlMapConfig {

    protected static final SqlMapClient sqlMap;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("com/edw/sqlmap/sqlmapconfig.xml");
            sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
        } catch (IOException e) {
            throw new RuntimeException("Fatal Error, ga dapet sqlmapconfignya.  Cause: " + e, e);
        } catch (Exception e){
            throw new RuntimeException("Fatal Error.  Cause: " + e, e);
        }
    }

    public static SqlMapClient getSqlMap() {
        return sqlMap;
    }
}

I create a very simple reporting using JasperReport, i named it Iseng.jrxml and compile it into Iseng.jasper

<?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="Iseng" 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"/>
	<parameter name="nama1" class="java.lang.String"/>
	<queryString>
		<![CDATA[]]>
	</queryString>
	<field name="nama" class="java.lang.String"/>
	<field name="alamat" class="java.lang.String"/>
	<background>
		<band splitType="Stretch"/>
	</background>
	<title>
		<band height="56" splitType="Stretch">
			<staticText>
				<reportElement x="0" y="0" width="555" height="46"/>
				<textElement textAlignment="Center" verticalAlignment="Middle">
					<font size="24"/>
				</textElement>
				<text><![CDATA[Testing Report]]></text>
			</staticText>
		</band>
	</title>
	<pageHeader>
		<band height="13" splitType="Stretch"/>
	</pageHeader>
	<columnHeader>
		<band height="34" splitType="Stretch">
			<staticText>
				<reportElement x="0" y="11" width="100" height="20"/>
				<textElement textAlignment="Center" verticalAlignment="Middle"/>
				<text><![CDATA[Nama]]></text>
			</staticText>
			<staticText>
				<reportElement x="225" y="11" width="100" height="20"/>
				<textElement textAlignment="Center" verticalAlignment="Middle"/>
				<text><![CDATA[Alamat]]></text>
			</staticText>
		</band>
	</columnHeader>
	<detail>
		<band height="27" splitType="Stretch">
			<textField>
				<reportElement x="32" y="0" width="100" height="20"/>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{nama}]]></textFieldExpression>
			</textField>
			<textField>
				<reportElement x="225" y="0" width="100" height="20"/>
				<textElement/>
				<textFieldExpression class="java.lang.String"><![CDATA[$F{alamat}]]></textFieldExpression>
			</textField>
		</band>
	</detail>
	<columnFooter>
		<band height="45" splitType="Stretch"/>
	</columnFooter>
	<pageFooter>
		<band height="54" splitType="Stretch"/>
	</pageFooter>
	<summary>
		<band height="42" splitType="Stretch"/>
	</summary>
</jasperReport>

And this is my main Swing class. iBatis generate a List of Beans class for its result set, and im using Jasper’s JRBeanCollectionDataSource class to map iBatis’ resultSet.

package com.edw.ui;

import com.edw.bean.Contoh;
import com.edw.config.SqlMapConfig;
import com.ibatis.sqlmap.client.SqlMapClient;
import java.awt.Container;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.BufferedInputStream;
import java.io.FileInputStream;

import java.util.HashMap;
import java.util.List;

import javax.swing.JButton;
import javax.swing.JFrame;

import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperReport;
import net.sf.jasperreports.engine.data.JRBeanCollectionDataSource;
import net.sf.jasperreports.engine.util.JRLoader;
import net.sf.jasperreports.view.JasperViewer;
import org.apache.log4j.Logger;

/**
 *
 * @author edw
 */
public class Main extends JFrame implements ActionListener {

    private Logger logger = Logger.getLogger(this.getClass());
    private JButton cmdPrint = new JButton("cetak");
    private SqlMapClient sqlMapClient = SqlMapConfig.getSqlMap();

    public Main() {
        Container conn = getContentPane();
        conn.setLayout(new FlowLayout());
        conn.add(cmdPrint);
        setLocationRelativeTo(null);
        setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        cmdPrint.addActionListener(this);
        pack();
    }

     public void actionPerformed(ActionEvent e) {
        if (e.getSource() == cmdPrint) {
            try {
                //load report location
                FileInputStream fis = new FileInputStream("Iseng.jasper");
                BufferedInputStream bufferedInputStream = new BufferedInputStream(fis);

                // do query and get list of Contoh
                List<Contoh> contohs = sqlMapClient.queryForList("contoh.selectAllContoh");

                //fill it in JRBeanCollectionDS
                JRBeanCollectionDataSource jrbcds = new JRBeanCollectionDataSource(contohs);

                //compile report
                JasperReport jasperReport = (JasperReport) JRLoader.loadObject(bufferedInputStream);
                JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, new HashMap(), jrbcds);

                //view report to UI
                JasperViewer.viewReport(jasperPrint, false);

            } catch (Exception ex) {
                logger.error(ex.getMessage(), ex);
            }

        }
    }

    public static void main(String[] args) {
        Main main = new Main();
        main.setVisible(true);
    }
}

and my log4j configuration, i named it log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG,stdout

# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d [%c{1}] %-5p %c:%L - %m%n

this is my NetBeans’ project structure,
Netbeans 6.9 project structure

dont forget to put your .jasper file in the same folder as your build.xml file,
Netbeans 6.9 file structure

this is what happen when i run my application, on the background you can see iBatis’ resultSet.
iBatis UI and its ResultSet

and this is what happen when i push my report button
JasperViewer showing iBatis' resultSet

I hope my article can be useful for others, thank you and have fun using iBatis.
(H)

Google+

Yet Another Simple Swing, Spring and Ibatis Integration Example

I was working on my side project while drinking a cup of (C) when suddenly i had an idea, why cant i rely on Spring’s Transaction instead of Ibatis’ plain transaction. I see that Spring’s Transaction with annotation is much much simpler than Ibatis’ old fashioned transaction with try-catch and commit-rollback method. Okay, enough with the chit-chat. Let me show you the code.

First, i create a very simple database, with one table consist of 2 simple columns.

CREATE DATABASE 'test'
USE 'test'

CREATE TABLE 'contoh' (
  'nama' varchar(30) NOT NULL,
  'alamat' varchar(100) DEFAULT NULL,
  PRIMARY KEY ('nama')
) 

next step is creating a java bean for database mapping

package com.edw.bean;

public class Contoh {

    private String nama;
    private String alamat;

    public String getAlamat() {
        return alamat;
    }

    public void setAlamat(String alamat) {
        this.alamat = alamat;
    }

    public String getNama() {
        return nama;
    }

    public void setNama(String nama) {
        this.nama = nama;
    }
    
}

and an xml mapping for database queries, i named it contoh.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" 
"http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="contoh" >

  <resultMap id="result" class="com.edw.bean.Contoh">
        <result property="nama" column="NAMA" columnIndex="1"/>
        <result property="alamat" column="ALAMAT" columnIndex="2"/>
    </resultMap>

    <insert id="insertContoh" parameterClass="com.edw.bean.Contoh" >
    insert into contoh (nama, alamat)
    values (#nama:VARCHAR#, #alamat:VARCHAR#)
    </insert>

    <select id="selectAllContoh" resultMap="result">
    select nama, alamat
    from contoh
    </select>

</sqlMap>

and ibatis’ main xml configuration, sqlmapconfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>

    <settings
          cacheModelsEnabled="true"
          enhancementEnabled="true"
          lazyLoadingEnabled="true"
          maxRequests="128"
          maxSessions="24"
          maxTransactions="12"
          defaultStatementTimeout="60"
          useStatementNamespaces="true"
       />

    <sqlMap resource="com/edw/sqlmap/contoh.xml"/>

</sqlMapConfig>

because im using Spring, i need one main xml configuration for my Spring app, i named it spring.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:p="http://www.springframework.org/schema/p"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">


    <context:component-scan base-package="com.edw"/>
    <tx:annotation-driven transaction-manager="transactionManager"/>
    <context:annotation-config/>

    <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
        <property name="configLocation" value="com/edw/sqlmap/sqlmapconfig.xml"/>
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost/test"/>
        <property name="username" value="root"/>
        <property name="password" value="password"/>
    </bean>

    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

</beans>

and 1 singleton class to load my configuration,

package com.edw.config;

/**
 *
 * @author edw
 */
import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class ApplicationContextProvider {

    private Logger logger = Logger.getLogger(ApplicationContextProvider.class);
    private ApplicationContext applicationContext;
    private static final ApplicationContextProvider provider = new ApplicationContextProvider();

    private ApplicationContextProvider() throws ExceptionInInitializerError {
        try {
            this.applicationContext = new ClassPathXmlApplicationContext("spring.xml");
        } catch (Throwable ex) {
            logger.error("Initial ApplicationContext creation failed -- " + ex.getMessage(), ex);
            throw new ExceptionInInitializerError(ex);
        }
    }

    public synchronized static ApplicationContextProvider getInstance() throws ExceptionInInitializerError {
        return provider;
    }

    public ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    public void setApplicationContext(ApplicationContext applicationContext) {
        this.applicationContext = applicationContext;
    }
}

i create a DAO class, to connect to my ibatis layer,

package com.edw.dao;

import com.edw.bean.Contoh;
import com.ibatis.sqlmap.client.SqlMapClient;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import org.springframework.stereotype.Repository;

/**
 *
 * @author edw
 */
@Repository(value = "contohDao")
public class ContohDao extends SqlMapClientDaoSupport {

    // empty constructor
    public ContohDao() {       
    }
     
    // autowired constructor
    // i dont know why, but i cant autowired sqlMapClient directly
    // it will cause  java.lang.IllegalArgumentException: Property 'sqlMapClient' is required
    @Autowired
    public ContohDao(SqlMapClient sqlMapClient) {
        setSqlMapClient(sqlMapClient);
    }

    public void insert(Contoh contoh){
        getSqlMapClientTemplate().insert("contoh.insertContoh",contoh);
    }

    public List<Contoh> select(){
        return getSqlMapClientTemplate().queryForList("contoh.selectAllContoh");
    }
}

and my Service class, i use it to connect to my DAO layer,

package com.edw.service;

import com.edw.bean.Contoh;
import com.edw.dao.ContohDao;
import java.util.List;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author edw
 */
@Service("contohService")
public class ContohService  {

    @Autowired
    private ContohDao contohDao;
    
    private Logger logger = Logger.getLogger(ContohService.class);

    @Transactional(isolation=Isolation.SERIALIZABLE)
    public void insert(Contoh contoh) {
        logger.debug("begin insert at "+this.getClass().getName());
        contohDao.insert(contoh);
        logger.debug("end insert at "+this.getClass().getName());
    }

    public List<Contoh> select() {
        logger.debug("begin select at "+this.getClass().getName());
        List<Contoh> contohs = contohDao.select();
        logger.debug("end select at "+this.getClass().getName());
        return contohs;
    }    
}

please be careful with isolation level, from what i’ve read,

In a typical application, multiple transactions run concurrently, often working
with the same data to get their job done. Concurrency, while necessary, can lead
to the following problems:

  • Dirty read—Dirty reads occur when one transaction reads data that has been
    written but not yet committed by another transaction. If the changes are
    later rolled back, the data obtained by the first transaction will be invalid.
  • Nonrepeatable read—Nonrepeatable reads happen when a transaction performs
    the same query two or more times and each time the data is different.
    This is usually due to another concurrent transaction updating the data
    between the queries.
  • Phantom reads—Phantom reads are similar to nonrepeatable reads. These
    occur when a transaction (T1) reads several rows, and then a concurrent
    transaction (T2) inserts rows. Upon subsequent queries, the first transaction
    (T1) finds additional rows that were not there before.

That’s why, im using Isolation.SERIALIZABLE, because

ISOLATION_SERIALIZABLE
This fully ACID-compliant isolation level ensures that dirty
reads, nonrepeatable reads, and phantom reads are all
prevented. This is the slowest of all isolation levels
because it is typically accomplished by doing full table
locks on the tables involved in the transaction.

this is my UI

package com.edw.ui;

import com.edw.bean.Contoh;
import com.edw.config.ApplicationContextProvider;
import com.edw.service.ContohService;
import java.awt.Container;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JTextField;
import org.apache.log4j.Logger;
import org.springframework.context.ApplicationContext;

/**
 *
 * @author edw
 */
public class MenuUtama extends JFrame implements ActionListener {

    private JTextField txtNama = new JTextField();
    private JTextField txtAlamat = new JTextField();
    private JButton cmdButton = new JButton("Save");
    private Logger logger = Logger.getLogger(MenuUtama.class);

    public MenuUtama() {
        setLayout(new GridLayout(3, 3));
        Container con = this.getContentPane();
        con.add(new JLabel("nama : "));
        con.add(txtNama);
        con.add(new JLabel("Alamat : "));
        con.add(txtAlamat);
        con.add(cmdButton);

        cmdButton.addActionListener(this);
    }

    public void actionPerformed(ActionEvent e) {
        if (e.getSource() == cmdButton) {
            Contoh contoh = new Contoh();
            contoh.setNama(txtNama.getText());
            contoh.setAlamat(txtAlamat.getText());

            try {
                ApplicationContext ac = ApplicationContextProvider.getInstance().getApplicationContext();
                ContohService service = (ContohService) ac.getBean("contohService");
                service.insert(contoh);
                logger.debug("success");
            } catch (Exception ex) {
                logger.error(ex.getMessage(), ex);
            }
        }
    }

    public static void main(String[] args) {
        MenuUtama menuUtama = new MenuUtama();
        menuUtama.setVisible(true);
        menuUtama.setSize(300, 150);
        menuUtama.setLocationRelativeTo(null);
    }
}

this is my junit test case

package com.edw.test;

import com.edw.bean.Contoh;
import com.edw.config.ApplicationContextProvider;
import com.edw.service.ContohService;
import java.util.List;
import org.apache.log4j.Logger;
import org.junit.Test;
import org.springframework.context.ApplicationContext;


/**
 *
 * @author edw
 */
public class TestKoneksi {

    @Test
    public void testingMain(){

        Logger logger = Logger.getLogger(TestKoneksi.class);

        ApplicationContext ac = ApplicationContextProvider.getInstance().getApplicationContext();
        ContohService service = (ContohService)ac.getBean("contohService");
        List<Contoh> contohs = service.select();

        for (Contoh contoh : contohs) {
            logger.debug(contoh.getNama()+" - "+contoh.getAlamat());
        }

        Contoh contoh = new Contoh();
        contoh.setNama("Ngorroc");
        contoh.setAlamat("Morroc");
        service.insert(contoh);

    }
}

this is the appereance of my swing app
ibatis swing spring app

this is what written on my console

[SqlMapClientTemplate] DEBUG org.springframework.orm.ibatis.SqlMapClientTemplate:177 - Opened SqlMapSession [com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl@b05acd] for iBATIS operation
[Connection] DEBUG java.sql.Connection:27 - {conn-100000} Connection
[SqlMapClientTemplate] DEBUG org.springframework.orm.ibatis.SqlMapClientTemplate:194 - Obtained JDBC Connection [com.mysql.jdbc.JDBC4Connection@17ec9f7] for iBATIS operation
[Connection] DEBUG java.sql.Connection:27 - {conn-100000} Preparing Statement:      insert into contoh (nama, alamat)     values (?, ?)     
[PreparedStatement] DEBUG java.sql.PreparedStatement:27 - {pstm-100001} Executing Statement:      insert into contoh (nama, alamat)     values (?, ?)     
[PreparedStatement] DEBUG java.sql.PreparedStatement:27 - {pstm-100001} Parameters: [ibatisInsert, testing masuk Ibatis]
[PreparedStatement] DEBUG java.sql.PreparedStatement:27 - {pstm-100001} Types: [java.lang.String, java.lang.String]
[ContohService] DEBUG com.edw.service.ContohService:28 - end insert at com.edw.service.ContohService
[DataSourceTransactionManager] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager:730 - Initiating transaction commit
[DataSourceTransactionManager] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager:259 - Committing JDBC transaction on Connection [com.mysql.jdbc.JDBC4Connection@17ec9f7]
[DataSourceUtils] DEBUG org.springframework.jdbc.datasource.DataSourceUtils:193 - Resetting isolation level of JDBC Connection [com.mysql.jdbc.JDBC4Connection@17ec9f7] to 4
[DataSourceTransactionManager] DEBUG org.springframework.jdbc.datasource.DataSourceTransactionManager:314 - Releasing JDBC Connection [com.mysql.jdbc.JDBC4Connection@17ec9f7] after transaction
[DataSourceUtils] DEBUG org.springframework.jdbc.datasource.DataSourceUtils:312 - Returning JDBC Connection to DataSource
[MenuUtama] DEBUG com.edw.ui.MenuUtama:51 - success

my NB Project Structure and libraries that i used
Netbeans Project structure, libraries and stuffs

btw, i highly recommend these books to understand more about Spring and Ibatis framework.

  1. Manning Spring in Action 2nd Edition
  2. Manning Ibatis in Action

thank you, have fun and good luck.
:-[

Google+

encrypting ibatis’ sqlmapconfig.xml

after im writing this article, i keep wondering “how can i keep database’s password secure.?”. As you all know, i keep database’s password plain at SqlMapConfig.xml. Therefore, we need to keep our database’s password secure. One of the simplest method is to encrypting SqlMapConfig’s properties.

Im using Swing, jasypt library for basic encryption, and iBatis 2.3.4. Jasypt also need 2 additional jars, commons-lang and commons-codec, you can find them at apache’s website.

first i create a properties file, named db.properties

JDBC.Driver=1rzI2NrjkRaiwdZso6qZaI0THnqKx/wkAROxbfaCL/E=
JDBC.ConnectionURL=7EpsURgD/FFzdzuDTKYtdcT3iGPePc8uklqBweCnbCkw1wjUAKPyEA==
JDBC.Username=ciUNsgpnvS6bEkkB1F/Q8g==
JDBC.Password=c5dvo6UUKK5t633Dt6lvma0WAm5snxb+

after that, i create a singleton class to do all the Encryption-Decryption functions

package com.edw.util;

import org.jasypt.util.text.BasicTextEncryptor;

/**
 * @author edw
 */
public class BasicEncryption {

    private static final BasicEncryption basicEncryption = new BasicEncryption();
    private final String CONSTANT = "busuk";

    private BasicTextEncryptor textEncryptor = new BasicTextEncryptor();

    private BasicEncryption(){        
        textEncryptor.setPassword(CONSTANT);
    }

    public static BasicEncryption getInstance(){
        return basicEncryption;
    }

    public String encrypt(String word){
        return textEncryptor.encrypt(word);
    }

    public String decrypt(String word){
        return textEncryptor.decrypt(word);
    }

}

after that, we modified SqlMapConfig.java to put decrypted properties into SqlMapConfig.xml

package com.edw.config;

import com.edw.util.BasicEncryption;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.File;
import java.io.FileInputStream;

import java.io.IOException;
import java.io.Reader;
import java.util.Properties;

/**
 *
 * @author edw
 */
public class SqlMapConfig {

    protected static final SqlMapClient sqlMap;

    static {
        try {

            File file = new File("db.properties");
            FileInputStream fileInputStream = new FileInputStream(file);
            Properties properties = new Properties();            
            properties.load(fileInputStream);
            fileInputStream.close();

            // load encryption class
            BasicEncryption basicEncryption = BasicEncryption.getInstance();

            properties.setProperty("JDBC.Driver", basicEncryption.decrypt(properties.getProperty("JDBC.Driver")));
            properties.setProperty("JDBC.ConnectionURL", basicEncryption.decrypt(properties.getProperty("JDBC.ConnectionURL")));
            properties.setProperty("JDBC.Username", basicEncryption.decrypt(properties.getProperty("JDBC.Username")));
            properties.setProperty("JDBC.Password", basicEncryption.decrypt(properties.getProperty("JDBC.Password")));

            Reader reader = Resources.getResourceAsReader("com/edw/sqlmap/sqlmapconfig.xml");
            sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader, properties);
        } catch (IOException e) {
            throw new RuntimeException("Fatal Error, ga dapet sqlmapconfignya.  Cause: " + e, e);
        } catch (Exception e){
            throw new RuntimeException("Fatal Error.  Cause: " + e, e);
        }
    }

    public static SqlMapClient getSqlMap() {
        return sqlMap;
    }
}

and we set the variable at sqlmapconfig.xml to fit decrypted properties values.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <settings
        useStatementNamespaces="true"
        lazyLoadingEnabled="true"
        enhancementEnabled="true"
        maxSessions="20"
        />

    <transactionManager type="JDBC" commitRequired="false">
        <dataSource type="SIMPLE">

            <property name="SetAutoCommitAllowed" value="false"/>
            <property name="DefaultAutoCommit" value="false"/>
            
            <property name="JDBC.Driver" value="${JDBC.Driver}"/>
            <property name="JDBC.ConnectionURL" value="${JDBC.ConnectionURL}"/>
            <property name="JDBC.Username" value="${JDBC.Username}"/>
            <property name="JDBC.Password" value="${JDBC.Password}"/>
   
        </dataSource>
    </transactionManager>


    <!-- dont forget to register your sql map configs -->
    <sqlMap resource="com/edw/sqlmap/contoh.xml"/>


</sqlMapConfig>

this is my project structure
structure
structure2

this is what will happen if we submit the form
success

you can check it in the database
database

Google+

A Simple Swing and iBatis Integration Example

According to Wikipedia, iBATIS is a persistence framework which automates the mapping between SQL databases and objects in Java, .NET, and Ruby on Rails. In Java, the objects are POJOs (Plain Old Java Objects). The mappings are decoupled from the application logic by packaging the SQL statements in XML configuration files. The result is a significant reduction in the amount of code that a developer needs to access a relational database using lower level APIs like JDBC and ODBC.

Here, we are trying to create a simple java application using iBatis framework. Im using Netbeans as my IDE and MySql for my database. First of all, we create a simple “test” database, and “contoh” table consisting of two varchar fields.

CREATE DATABASE 'test'
USE 'test'

CREATE TABLE 'contoh' (
  'nama' varchar(30) NOT NULL,
  'alamat' varchar(100) DEFAULT NULL,
  PRIMARY KEY ('nama')
) 

next step is creating a java bean for database mapping

package com.edw.bean;

public class Contoh {

    private String nama;
    private String alamat;

    public String getAlamat() {
        return alamat;
    }

    public void setAlamat(String alamat) {
        this.alamat = alamat;
    }

    public String getNama() {
        return nama;
    }

    public void setNama(String nama) {
        this.nama = nama;
    }
    
}

and an xml mapping for database queries, i name it contoh.xml, and place it in

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >
<sqlMap namespace="contoh" >

  <insert id="insertContoh" parameterClass="com.edw.bean.Contoh" >
    insert into contoh (nama, alamat)
    values (#nama:VARCHAR#, #alamat:VARCHAR#)
  </insert>
</sqlMap>

and one xml file to contain all of our basic database connection and configuration files

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>
    <settings
        useStatementNamespaces="true"
        lazyLoadingEnabled="true"
        enhancementEnabled="true"
        maxSessions="20"
        />

    <transactionManager type="JDBC" commitRequired="false">
        <dataSource type="SIMPLE">

            <property name="SetAutoCommitAllowed" value="false"/>
            <property name="DefaultAutoCommit" value="false"/>
            
            <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
            <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost/test"/>
            <property name="JDBC.Username" value="root"/>
            <property name="JDBC.Password" value=""/>
   
        </dataSource>
    </transactionManager>


    <!-- dont forget to register your sql map configs -->
    <sqlMap resource="com/edw/sqlmap/contoh.xml"/>

</sqlMapConfig>

dont forget to register you xml queries here (line 28), or iBatis wont find it. And as you can see, at line 11, we can set our maximum session to database. 20 maxSessions means, there wont be more than 20 concurrent connection to database. Dont worry, iBatis can also connect to your connection pooling or JNDI as Datasource.

next step is, we create a singleton java class, to load our iBatis configuration.

package com.edw.config;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

import java.io.IOException;
import java.io.Reader;

public class SqlMapConfig {

    protected static final SqlMapClient sqlMap;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("com/edw/sqlmap/sqlmapconfig.xml");
            sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
        } catch (IOException e) {
            throw new RuntimeException("Fatal Error, ga dapet sqlmapconfignya.  Cause: " + e, e);
        } catch (Exception e){
            throw new RuntimeException("Fatal Error.  Cause: " + e, e);
        }
    }

    public static SqlMapClient getSqlMap() {
        return sqlMap;
    }
}

and after that, we create our UI Class. Im using a simple Swing class for example.

package com.edw.ui;

import com.edw.bean.Contoh;
import com.edw.config.SqlMapConfig;
import com.ibatis.sqlmap.client.SqlMapClient;
import java.awt.Container;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.SQLException;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JTextField;

public class FrameUtama extends JFrame implements ActionListener {

    private JTextField txtNama = new JTextField();
    private JTextField txtAlamat = new JTextField();
    private JButton cmdButton = new JButton("Save");

    public FrameUtama(){
        setLayout(new GridLayout(3, 3));
        Container con = this.getContentPane();
        con.add(new JLabel("nama : "));
        con.add(txtNama);
        con.add(new JLabel("Alamat : "));
        con.add(txtAlamat);
        con.add(cmdButton);

        cmdButton.addActionListener(this);
    }

    public void actionPerformed(ActionEvent e) {

    }

    public static void main(String[] edw) {
        FrameUtama frameUtama = new FrameUtama();
        frameUtama.setVisible(true);
        frameUtama.setSize(300,150);
        frameUtama.setLocationRelativeTo(null);
    }

}

and we just put this simple snippet code to connect our Swing UI code to database via iBatis.

 public void actionPerformed(ActionEvent e) {
        if(e.getSource() == cmdButton){
            Contoh contoh = new Contoh();
            contoh.setNama(txtNama.getText());
            contoh.setAlamat(txtAlamat.getText());

            SqlMapClient sqlMapClient = SqlMapConfig.getSqlMap();
            try {
                sqlMapClient.insert("contoh.insertContoh", contoh);
                System.out.println("Success");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

well, this is the project file structure in NB 6.8.
my ibatis project structure

and this is the UI layout
Application GUI

this is what happen if we submit a data
successfully submit data

the data we submitted is in the database
mysql data

dont forget to download ibatis jars here, im using iBatis 2.3.4 currently.
Thanks.

Google+