mybatis Posts

java.sql.SQLException: Numeric Overflow When Connecting to Oracle using MyBatis

Im using mybatis framework, and it was working very nice untuil today ive found a very weird error. A very weird error, because im only doing select queries and not insert anything.

This is the complete stacktrace,

Cause: java.sql.SQLException: Numeric Overflow
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
	at com.sun.proxy.$Proxy190.selectByExample(Unknown Source)
Caused by: java.sql.SQLException: Numeric Overflow
	at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4381)
	at oracle.jdbc.driver.NumberCommonAccessor.getShort(NumberCommonAccessor.java:353)
	at oracle.jdbc.driver.OracleResultSetImpl.getShort(OracleResultSetImpl.java:1118)
	at oracle.jdbc.driver.OracleResultSet.getShort(OracleResultSet.java:418)
	at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getShort(DelegatingResultSet.java:272)
	at org.apache.tomcat.dbcp.dbcp.DelegatingResultSet.getShort(DelegatingResultSet.java:272)
	at sun.reflect.GeneratedMethodAccessor198.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

This is my java bean,

public class TblInvalid implements Serializable {
    private short id;

    private String attribute7;

    // other setter and getter
}

My Oracle DDL looks like this,

CREATE TABLE TBL_INVALID
    (
        ID NUMBER,
        ATTRIBUTE7 VARCHAR2(255)
 )

The workaround is actually so simple, changing from Short into BigDecimal makes the problem goes away.

public class TblInvalid implements Serializable {
    private BigDecimal id;

    private String attribute7;

    // other setter and getter
}
Google+

Using HashMaps as MyBatis’ Parameter and Return Values

Usually im using javabean as both parameter and return values for MyBatis. But actually, MyBatis also able to use HashMaps as both parameter and return values. Here is the example,

First is a simple pom file to load all the libraries needed,

<?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>com.edw</groupId>
    <artifactId>MyBatisSelectMap</artifactId>
    <version>1.0</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.7</maven.compiler.source>
        <maven.compiler.target>1.7</maven.compiler.target>
    </properties>
    
    <dependencies>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.2</version>
        </dependency>
    </dependencies>
</project>

And my configuration.xml file, to hold my MyBatis’ configuration

<?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="UNPOOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost/test"/>
                <property name="username" value="root"/>
                <property name="password" value="password"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/edw/mybatisselectmap/sqlmap/SelectMapper.xml" />
    </mappers>
</configuration>

An xml file, to hold all my queries, i named it SelectMapper.xml.
Please note the difference between using #{} and ${}. Hashtags sign means a prepared statement variables, while dollar sign means a simple string replace.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.edw.mybatisselectmap.mapper.SelectMapper" >    
    <select resultType="java.util.Map"  parameterType="java.util.Map" id="select" >
        SELECT
                *
        FROM
                testing 
        WERE  
                name LIKE #{myname}
        ORDER BY ${orderBy} 
    </select>    
</mapper>

A simple java file to map my xml queries,

package com.edw.mybatisselectmap.mapper;

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

public interface SelectMapper {
    List<HashMap<Object, Object>> select(HashMap<Object, Object> hashMap);
}

and my java configuration file, to load all my MyBatis’ configurations

package com.edw.mybatisselectmap.config;

import java.io.IOException;
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/mybatisselectmap/sqlmap/Configuration.xml");
            FACTORY = new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            throw new RuntimeException("Fatal Error. Cause: " + e, e);
        }
    }

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

And finally my main java file,

package com.edw.mybatisselectmap;

import com.edw.mybatisselectmap.config.MyBatisSqlSessionFactory;
import com.edw.mybatisselectmap.mapper.SelectMapper;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;

public class Main {

    private static Logger logger = Logger.getLogger(Main.class);

    public static void main(String[] args) {
        SqlSession sqlSession = null;
        try {
            
            HashMap<Object, Object> hashMapParameter = new HashMap<Object, Object>();
            hashMapParameter.put("orderBy", "id");
            hashMapParameter.put("myname", "%edw%");
            
            sqlSession = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(true);
            SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
            
            List<HashMap<Object, Object>> hashMapResults = mapper.select(hashMapParameter);
            
            for (HashMap<Object, Object> hashMapResult : hashMapResults) {
                logger.debug(hashMapResult);
            }
        } catch (Exception e) {
            logger.error(e, e);
        } finally {
            if (sqlSession != null) {
                sqlSession.close();
            }
        }
    }
}

You can find this post’s sourcecode at my github page here, https://github.com/edwinkun/MyBatisSelectMap.

Google+

[Book Review] Packt Publishing’s Ebook, Java Persistence with MyBatis 3

Among lots of java frameworks, MyBatis is a very popular especially because it is a database-centric-framework and it has a shallow and short learning curve, and today im trying to do a book review for it. The book that i want to review is Packt Publishing’s Java Persistence with MyBatis 3. You can see the ebook on this link, http://www.packtpub.com/java-persistence-with-mybatis-3/book.

This book’s author, K. Siva Prasad Reddy, really show the reasons of why MyBatis is very popular by providing with various code samples, from simple “down to earth” codes to a much more complicated codes, such as Spring Framework integration and Caching strategy.

These are the Table of Contents of the book,
Chapter 1: Getting started with MyBatis
Chapter 2: Bootstrapping
Chapter 3: SQL Mappers using XML
Chapter 4: SQL Mappers using Annotations
Chapter 5: Integration with Spring

So needless to say, this is the kind of ebook that i wish i had read since long time ago.

Google+

A Simple Caching Example on MyBatis using EhCache

Today i will show a simple example on how to combine ehcache caching framework with MyBatis ORM. I use Maven as my build tool, and Netbeans as my IDE.

Okay, here is my pom.xml,

<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>com.edw.ehcache</groupId>
    <artifactId>MyBatisEhCache</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <name>MyBatisEhCache</name>
    <url>http://maven.apache.org</url>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>3.8.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.caches</groupId>
            <artifactId>mybatis-ehcache</artifactId>
            <version>1.0.2</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>      
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>net.sf.ehcache</groupId>
            <artifactId>ehcache</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.5</version>
        </dependency>     
    </dependencies>
</project>

And i create a simple mysql table,

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=11 DEFAULT CHARSET=latin1;

and a simple bean and xml representation from my table,

package com.edw.mybatisehcache.bean;

import java.io.Serializable;

public class Testing implements Serializable  {

    private Integer id;
    private String name;
    private String address;

    // setter and getter

    @Override
    public String toString() {
        return "testing{" + "id=" + id + ", name=" + name + ", address=" + address + '}';
    }            
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.edw.mybatisehcache.mapper.TestingMapper" >
  
     <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
    
    <resultMap id="Testings" type="com.edw.mybatisehcache.bean.Testing" >
        <id column="id" property="id" jdbcType="BIGINT" />
        <result column="name" property="name" jdbcType="VARCHAR" />
        <result column="address" property="address" jdbcType="VARCHAR" />
    </resultMap>  

    <select id="select" resultMap="Testings">
        select 
        *
        from testing    
    </select>   
</mapper>

An xml configuration to load my database connection,

<?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="UNPOOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost/test"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>          
    </environments>
     <mappers>        		
        <mapper resource="com/edw/mybatisehcache/xml/TestingMapper.xml" />  
    </mappers>              		
</configuration>

A java code to load my xml configuration,

package com.edw.mybatisehcache.config;

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/mybatisehcache/xml/Configuration.xml");
            FACTORY = new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e){
            throw new RuntimeException("Fatal Error.  Cause: " + e, e);
        }
    }

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

A java interface to do handle queries,

package com.edw.mybatisehcache.mapper;

import com.edw.mybatisehcache.bean.Testing;
import java.util.List;

public interface TestingMapper {
    public List<Testing> select();    
}

And this is my ehcache.xml configuration,

<?xml version="1.0" encoding="UTF-8"?>
<!--
    caching configuration
-->
<ehcache>
    
    <diskStore path="F:\\cache" />
    
    <defaultCache eternal="true" maxElementsInMemory="1000"
                   overflowToDisk="true" diskPersistent="true" timeToIdleSeconds="0"
                   timeToLiveSeconds="0" memoryStoreEvictionPolicy="LRU" statistics="true" />
</ehcache>

This is my main java class, as you can see i try to do a repeated simple select queries,

package com.edw.mybatisehcache.main;

import com.edw.mybatisehcache.bean.Testing;
import com.edw.mybatisehcache.config.MyBatisSqlSessionFactory;
import com.edw.mybatisehcache.mapper.TestingMapper;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.log4j.Logger;

public class Main {

    private static Logger logger = Logger.getLogger(Main.class);

    public static void main(String[] args) {

        for (int i = 0; i < 3; i++) {
            SqlSessionFactory sqlSessionFactory = MyBatisSqlSessionFactory.getSqlSessionFactory();
            SqlSession sqlSession = sqlSessionFactory.openSession();
            TestingMapper testingMapper = sqlSession.getMapper(TestingMapper.class);

            List<Testing> testings = testingMapper.select();
            for (Testing testing : testings) {
                logger.debug(testing);
            }
            sqlSession.close();

            try {
                Thread.sleep(3000);
            } catch (Exception e) {
                logger.error(e, e);
            }
        }
    }
}

This is what is written on my netbeans’ console,

2013-07-25 15:30:10,648 [Segment] DEBUG net.sf.ehcache.store.disk.Segment:779 - fault removed 0 from heap
2013-07-25 15:30:10,648 [Segment] DEBUG net.sf.ehcache.store.disk.Segment:796 - fault added 0 on disk
2013-07-25 15:30:13,722 [Cache] DEBUG net.sf.ehcache.Cache:1970 - Cache: com.edw.mybatisehcache.mapper.TestingMapper store hit for 2026218237:1652924294:com.edw.mybatisehcache.mapper.TestingMapper.select:0:2147483647:select 
        *
        from testing
2013-07-25 15:30:13,722 [Main] DEBUG com.edw.mybatisehcache.main.Main:24 - testing{id=1, name=edw, address=Ciledug}
2013-07-25 15:30:13,722 [Main] DEBUG com.edw.mybatisehcache.main.Main:24 - testing{id=2, name=kamplenk, address=Cikokol}
2013-07-25 15:30:13,722 [Main] DEBUG com.edw.mybatisehcache.main.Main:24 - testing{id=3, name=nugie, address=Pamulang}
2013-07-25 15:30:13,722 [Main] DEBUG com.edw.mybatisehcache.main.Main:24 - testing{id=4, name=tebek, address=Karawaci}

Here is my Netbeans project structure

Have fun (K)

Google+

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+