mysql Posts

Connecting MyBatis ORM to C3P0 Connection Pooling

In this example, im trying to connecting my MyBatis application to my database through C3P0 connection pooling. According to its website, C3P0 is an easy-to-use library for making traditional JDBC drivers “enterprise-ready” by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2.

Okay, so the first thing i do is, creating a database named “Test” and table “Testing”

CREATE DATABASE `Test`;
USE `Test`;
CREATE TABLE `testing` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  `address` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `ix` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

insert into `table`(`Id`,`name`,`address`) values (1,'edw','Ciledug');
insert into `table`(`Id`,`name`,`address`) values (2,'kamplenk','Cikokol');
insert into `table`(`Id`,`name`,`address`) values (3,'nugie','Pamulang');
insert into `table`(`Id`,`name`,`address`) values (4,'tebek','Karawaci');

And a java bean class to represent my sql table,

package com.edw.bean;

public class Testing {

    private int id;
    private String name;
    private String address;

    public Testing() {
    }

    // setter and getter method

    @Override
    public String toString() {
        return "Testing{" + "id=" + id + ", name=" + name + ", address=" + address + '}';
    }
}

and dont forget, a java interface class to handle my query

package com.edw.mapper;

import com.edw.bean.Testing;
import java.util.List;
import org.apache.ibatis.annotations.Select;
 
public interface TestingMapper {
    @Select("SELECT * FROM testing")
    List<Testing> selectAll();
}

Next is creating c3p0 connection configuration on context.xml file under META-INF folder

<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/MyBatisC3P0">
	
    <Resource name="testjndi" 
		 auth="Container"
		 driverClass="com.mysql.jdbc.Driver"
         jdbcUrl="jdbc:mysql://localhost:3306/test"
         user="root"
         password="xxxx"					 		 
         factory="org.apache.naming.factory.BeanFactory" 
         type="com.mchange.v2.c3p0.ComboPooledDataSource" 
         maxPoolSize="30" 					 
         minPoolSize="10" 
         acquireIncrement="3" 
         acquireRetryAttempts = "0"
         acquireRetryDelay = "3000"
         breakAfterAcquireFailure = "false"
         maxConnectionAge = "20"
         maxIdleTime = "15"
         maxIdleTimeExcessConnections = "15"
         idleConnectionTestPeriod = "10"
         testConnectionOnCheckout = "true"					 
         preferredTestQuery = "SELECT 1"
         debugUnreturnedConnectionStackTraces = "true"					 
         autoCommitOnClose="true"
        />	
</Context>

next is registering your JNDI on web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
    <servlet>
        <servlet-name>myBatisServlet</servlet-name>
        <servlet-class>com.edw.servlet.MyBatisServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>myBatisServlet</servlet-name>
        <url-pattern>/myBatisServlet</url-pattern>
    </servlet-mapping>
    <welcome-file-list>
        <welcome-file>myBatisServlet</welcome-file>
    </welcome-file-list>
    <resource-ref>
        <description>My Connection Pool</description>
        <res-ref-name>testjndi</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>
</web-app>

and calling your connection pooling jndi from MyBatis’ xml code

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
       <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="JNDI">
                <property name="data_source" value="java:/comp/env/testjndi"/>
            </dataSource>
        </environment>          
    </environments>
    <mappers />
</configuration>

next is creating a java class to load my MyBatis’ xml configuration

package com.edw.config;

import com.edw.mapper.TestingMapper;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisSqlSessionFactory {

    private static final SqlSessionFactory FACTORY;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("com/edw/sqlmap/Configuration.xml");
            FACTORY = new SqlSessionFactoryBuilder().build(reader);
            FACTORY.getConfiguration().addMapper(TestingMapper.class);
        } catch (Exception e){
            throw new RuntimeException("Fatal Error.  Cause: " + e, e);
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return FACTORY;
    }
}

and finally, i create a servlet as my presentation layer

package com.edw.servlet;

import com.edw.bean.Testing;
import com.edw.config.MyBatisSqlSessionFactory;
import com.edw.mapper.TestingMapper;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.ibatis.session.SqlSession;

public class MyBatisServlet extends HttpServlet {

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        SqlSession sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true);
        try {
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet MyBatisServlet</title>");
            out.println("</head>");
            out.println("<body>");

            TestingMapper testingMapper = sqlSession.getMapper(TestingMapper.class);
            List<Testing> testings = testingMapper.selectAll();
            for (Testing testing : testings) {
                out.println(testing.getName()+" - "+testing.getAddress()+"<br />");
            }
            
            out.println("</body>");
            out.println("</html>");
        } finally {
            sqlSession.close();
            out.flush();            
            out.close();            
        }
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }
}

This is what the result of my servlet when im running it on my browser

This is the screenshot of my database connection, you can see that c3p0 automatically create 10 free connections.

And this is the screenshot of my netbeans project

Have fun with C3P0 and MyBatis (H)

Google+

Error “Communications link failure” When Connecting Glassfish v3 to MySQL

Today i spent a ridiculously amount of time finding out why suddenly my application cannot connect to database. Im using Glassfish 3 connected to MySQL via connection pool on an Ubuntu server with an IP public. What makes it difficult is that it can connect smoothly before but suddenly an error happened.

This is the detail exception from my Glassfish’s log.

[#|2011-11-07T15:26:24.411+0700|WARNING|glassfish3.0.1|javax.enterprise.resource.resourceadapter.com.sun. enterprise.connectors.service|_ThreadID=30;_ThreadName=http-thread-pool-4848-(5);|RAR8054: Exception while creating an unpooled [test] 
connection for pool [ dbepay ], Connection could not be allocated because: Communications link failure

Last packet sent to the server was 0 ms ago.|#]

[#|2011-11-07T15:26:30.982+0700|WARNING|glassfish3.0.1|javax.enterprise.resource.resourceadapter.com. sun.enterprise.resource.pool|_ThreadID=31;_ThreadName=http-thread-pool-4848-(2);|RAR8023: 
Flush Connection Pool did not happen as pool - dbepay is not initialized|#]

After some browsing i found out that it’s due to mysql’s binding issue. Exception happened because MySQL server and Glassfish are installed on the same host, and in MySQL configuration on my.cnf have binded to a public ip address instead of localhost.
This is my ifconfig looks like

root@portal:~/edw/glassfishv3/glassfish/modules# ifconfig
eth0      Link encap:Ethernet  HWaddr 00:50:56:01:00:0e  
          inet addr:xxx.xx.xxx.xx  Bcast:xxx.xx.xxx.xx  Mask:255.255.255.128
          inet6 addr: xxxxx :e/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:2082924 errors:0 dropped:0 overruns:0 frame:0
          TX packets:1065587 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:668840899 (668.8 MB)  TX bytes:222095600 (222.0 MB)

lo        Link encap:Local Loopback  
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:16436  Metric:1
          RX packets:4888314 errors:0 dropped:0 overruns:0 frame:0
          TX packets:4888314 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:0 
          RX bytes:406280073 (406.2 MB)  TX bytes:406280073 (406.2 MB)

All i do is edit MySQL’s my.cnf, adding a new bind address to 127.0.0.1 and remarking previous binding.

[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user		= mysql
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#this is my previous value, binded to a public ip address
#bind-address		= xxx.xxx.xxx.xxx 

#this is my new value
bind-address		= 127.0.0.1

restart both of mysql and glassfish and suddenly, my application run smoothly again.
Thanks uncle Google, you’ve helped me alot.

Google+

Set MySQL Connection TimeOut

Sometimes you find a condition where your database connection (MySQL) is time out because of your queries are spending too much time. This is a hint on how you increase your MySQL time out configuration.

First you query your default connection timeout.

SHOW VARIABLES LIKE 'connect_timeout';

As you can see below, i have approximately 10 seconds before my mysql connection time out.

Next is updated it to 60 seconds.

SET GLOBAL connect_timeout=60;

Simple isn’t it. ;)

Google+

Menyimpan Objek Java di Database

Untuk menyimpan suatu java object ke table, dibutuhkan field dengan tipe data BLOB, berikut adalah cara menyimpan java object (dalam hal ini javabean) kedalam database, gw pake JDK 6.0 dan MySQL 5. Misalkan ada javabean dengan nama BeanTest (usahakan selalu implements Serializable), dengan struktur dibawah ini :

import java.io.Serializable;

public class BeanTest implements Serializable {

	private String nama;
	private String alamat;

	/**
	 * @return the nama
	 */
	public String getNama() {
		return nama;
	}

	/**
	 * @param nama
	 *            the nama to set
	 */
	public void setNama(String nama) {
		this.nama = nama;
	}

	/**
	 * @return the alamat
	 */
	public String getAlamat() {
		return alamat;
	}

	/**
	 * @param alamat
	 *            the alamat to set
	 */
	public void setAlamat(String alamat) {
		this.alamat = alamat;
	}

}

lalu buat table di database

create database testest;
use testest;

CREATE TABLE IF NOT EXISTS x (
satu varchar(5) NOT NULL DEFAULT ” ,
dua blob ,
PRIMARY KEY (satu)
);

Lalu buatlah class yang akan digunakan untuk berhubungan dengan database, gunakan method setData() untuk memasukkan object ke database, dan getData() untuk mengambil data dari database.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Main {

	public static void main(String[] args) {
		Main main = new Main();

		// simpan data ke database
		main.setData();

		//tarik isi database
		main.getData();
	}

	private void setData() {
		try {

			BeanTest bean = new BeanTest();

			bean.setNama("edwin");
			bean.setAlamat("jakarta");

			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testest", "root", "");
			java.sql.PreparedStatement statement = con.prepareStatement("insert into x values (?,?)");

			statement.setString(1, "00001");
			statement.setBytes(2, toBytes(bean));

			statement.executeUpdate();

			statement.close();
			con.close();
		} catch (Exception ex) {
			Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
		}
	}

	private byte[] toBytes(Object object) {
		java.io.ByteArrayOutputStream baos = new java.io.ByteArrayOutputStream();
		try {
			java.io.ObjectOutputStream oos = new java.io.ObjectOutputStream(baos);
			oos.writeObject(object);
			oos.flush();
			oos.close();
		} catch (Exception ioe) {
			ioe.getMessage();
		}

		return baos.toByteArray();
	}

	private void getData() {
		try {
			Class.forName("com.mysql.jdbc.Driver");

			Connection con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/testest", "root", "");
			Statement statement = con.createStatement();
			ResultSet res = statement.executeQuery("select * from x");

			while (res.next()) {
				Object o = toObject(res.getBytes(2));

				if (o instanceof BeanTest) {
					BeanTest beanTest = (BeanTest) o;
					System.out.println(beanTest.getNama());
					System.out.println(beanTest.getAlamat());

				}
			}
			statement.close();
			con.close();
		} catch (Exception ex) {
			Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
		}
	}

	private Object toObject(byte[] bytes) {
		Object object = null;
		try {
			object = new java.io.ObjectInputStream(
					new java.io.ByteArrayInputStream(bytes)).readObject();
		} catch (Exception cnfe) {
			cnfe.getMessage();
		}
		return object;
	}
}

sekian dan terima kasih,

Wassalam.

Google+