javabean

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
}

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)

A Weird Hibernate Exception : org.hibernate.ObjectNotFoundException: No row with the given identifier exists

Today i met a weird hibernate exception, somehow it show that my User object does not exists. A little bit weird because i’m querying Comment tables, but the exceptions show “object user does not exist”.

	A Weird Hibernate Error : org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [com.edw.entity.User#anonymousUser]
    org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [com.edw.entity.User#anonymousUser]
	at org.hibernate.impl.SessionFactoryImpl$1.handleEntityNotFound(SessionFactoryImpl.java:377)
	at org.hibernate.event.def.DefaultLoadEventListener.load(DefaultLoadEventListener.java:145)
	at org.hibernate.event.def.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:195)
	at org.hibernate.event.def.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:103)
	at org.hibernate.impl.SessionImpl.fireLoad(SessionImpl.java:878)
	at org.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:846)
	at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:557)
	at org.hibernate.type.EntityType.resolve(EntityType.java:379)
	at org.hibernate.engine.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:120)

After some minutes digging on my sourcecode, i found out that javabean Comment has a @ManyToOne (Hibernate annotation for many to one relationship) to javabean User. Too bad one Comment object, still has a foreign key to a deleted User object.

This is my previous User class and Comment class, the main culprit is on line 30.

package com.edw.entity;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;

@Entity
@Table(name="tbl_comment")
public class Comment implements Serializable {

	@Id
	@GeneratedValue
	private Long id;
	
	@Temporal(TemporalType.TIMESTAMP)
	private Date timestamp;
	
	private String content;
		
	@ManyToOne
	private User user;
		
	@ManyToOne
	private News news;

	public Long getId() {
		return id;
	}
	// other setter and getter
}
package com.edw.entity;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="tbl_user")
public class User implements Serializable {

	@Id
    private String username;
    private String password;
    private Boolean status;
    
    private String email;
    private String name;
    private String address;
    private String city;
    private String job;
    private String about;
    private String relationship;
    private String privacy;
    
    private String role;
    
    private String tokenRegistration;
    private String tokenForgotPassword;
    
    public User() {}
    public User(String username) {
    	this.username = username;
    }   	
}

The workaround is by adding @NotFound annotation to ignore the condition when Hibernate unable to find the User class.

package com.edw.entity;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction;

@Entity
@Table(name="tbl_comment")
public class Comment implements Serializable {

	@Id
	@GeneratedValue
	private Long id;
	
	@Temporal(TemporalType.TIMESTAMP)
	private Date timestamp;
	
	private String content;
	
	@NotFound(action=NotFoundAction.IGNORE)
	@ManyToOne
	private User user;
	
	@NotFound(action=NotFoundAction.IGNORE)
	@ManyToOne
	private News news;

	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
}

Hope it helped others, have fun 😉

MyBatis Caching Using OSCache

In this example im trying to create a simple application using MyBatis cache ability. What is cache anyway? A cache is designed to reduce traffic between your application and the database by conserving data already loaded from the database and put it whether in memory or in file. Database access is necessary only when retrieving data that is not currently available in the cache. So basically not all queries are taken from database, but from cache instead.

As you can see here, MyBatis has lots of caching products. But on this example im using OSCache.
First, as always, a simple mysql table

CREATE TABLE contoh
    (
        nama VARCHAR(10) NOT NULL,
        alamat VARCHAR(200),
        PRIMARY KEY (nama)
    )
	
insert into contoh (nama, alamat) values ('edw', 'Jakarta');
insert into contoh (nama, alamat) values ('danu', 'Ciledug');
insert into contoh (nama, alamat) values ('kamplenk', 'Tangerang');
insert into contoh (nama, alamat) values ('tebek', 'BSD');
insert into contoh (nama, alamat) values ('nugie', 'Pamulang');
insert into contoh (nama, alamat) values ('samsu', 'Bandung');

And a simple java bean to represent my table,

package com.edw.bean;

import java.io.Serializable;

public class Contoh implements Serializable {
    private String nama;
    private String alamat;

	// setter and getter

    @Override
    public String toString(){
        return nama+" : "+alamat;
    }
}

And a simple java interface to create query method

package com.edw.mapper;

import com.edw.bean.Contoh;
import java.util.List;

public interface ContohMapper {    
    List<Contoh> selectAll();
}

My xml query, please take a look at line 5.

<?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.mapper.ContohMapper" >
    
	<cache type="org.mybatis.caches.oscache.OSCache"/>

    <resultMap id="ContohMap" type="com.edw.bean.Contoh" >
        <id column="nama" property="nama" jdbcType="VARCHAR" />
        <result column="alamat" property="alamat" jdbcType="VARCHAR" />
    </resultMap>
    
    <select id="selectAll" resultMap="ContohMap">
        SELECT * FROM contoh
    </select>
</mapper>

And my xml configuration to load all my xml queries,

<?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/xml/ContohMapper.xml" />
    </mappers>
</configuration>

A java class to load all my xml configurations,

package com.edw.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 {

    protected static final SqlSessionFactory FACTORY;

    static {
        try {
            Reader reader = Resources.getResourceAsReader("com/edw/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;
    }
}

Now i create my main java class.

package com.edw.main;

import com.edw.bean.Contoh;
import com.edw.config.MyBatisSqlSessionFactory;
import com.edw.mapper.ContohMapper;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;

public class Main {
    
    private Logger logger = Logger.getLogger(this.getClass());
    
    public Main(){       
    }
    
    private void execute(){
        // test caching by doing select queries for 10 times
        for (int i = 0; i < 10; i++) {
            SqlSession session = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession();
            ContohMapper mapper = session.getMapper(ContohMapper.class);
            List<Contoh> contohs = mapper.selectAll();
            for (Contoh contoh : contohs) {
                logger.debug(contoh);
            }
            session.close();
            
            try {
                logger.debug("sleeping for 3 seconds");
                Thread.sleep(3000);
            } catch (Exception e) {
            }
        }        
    }

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

Last, is create 2 properties file. One is for log4j configuration, and another one for oscache configuration.
This is my 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

And this is my oscache.properties

cache.capacity=1000
cache.memory=true
cache.algorithm=com.opensymphony.oscache.base.algorithm.LRUCache

If you run the application, you’ll notice that for the second queries it will take from cache instead of database. This is what written on my log console.

2012-05-20 02:06:11,687 [Connection] DEBUG java.sql.Connection:27 - ooo Connection Opened
2012-05-20 02:06:11,688 [AbstractConcurrentReadCache] DEBUG com.opensymphony.oscache.base.algorithm.AbstractConcurrentReadCache:694 - get called (key=1247278887:1786588787:com.edw.mapper.ContohMapper.selectAll:0:2147483647:SELECT * FROM contoh)
2012-05-20 02:06:11,688 [LoggingCache] DEBUG org.apache.ibatis.cache.decorators.LoggingCache:27 - Cache Hit Ratio [com.edw.mapper.ContohMapper]: 0.6666666666666666
2012-05-20 02:06:11,689 [Main] DEBUG com.edw.main.Main:31 - edw : Jakarta
2012-05-20 02:06:11,689 [Main] DEBUG com.edw.main.Main:31 - danu : Ciledug
2012-05-20 02:06:11,692 [Main] DEBUG com.edw.main.Main:31 - kamplenk : Tangerang
2012-05-20 02:06:11,692 [Main] DEBUG com.edw.main.Main:31 - tebek : BSD
2012-05-20 02:06:11,692 [Main] DEBUG com.edw.main.Main:31 - nugie : Pamulang
2012-05-20 02:06:11,693 [Main] DEBUG com.edw.main.Main:31 - samsu : Bandung
2012-05-20 02:06:11,693 [Connection] DEBUG java.sql.Connection:27 - xxx Connection Closed
2012-05-20 02:06:11,693 [Main] DEBUG com.edw.main.Main:36 - sleeping for 3 seconds
BUILD STOPPED (total time: 8 seconds)

This is my project structure on Netbeans7
mybatis oscache project structure

How to Create a Simple Servlet to Handle JSon Requests

On this example, im trying to create a simple servlet to handle JSon request. Im using this servlet to handle JSon request from my front end such as web or even desktop application. Thanks to GSon library, it really helped on simplifying javabean convert to json strings and the other way around.

First as usual, a simple java bean to handle request and response format

package com.edw.bean;

public class Student {
 
    private String name;
    private int age;
    private byte gender;

	// other setter and getter
}
package com.edw.bean;

public class Status {
    private boolean success;
    private String description;
	
	// other setter and getter
}

And this is my servlet,

package com.edw.servlet;

import com.edw.bean.Status;
import com.edw.bean.Student;
import com.google.gson.Gson;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class JsonParserServlet extends HttpServlet {

    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("application/json");        
        Gson gson = new Gson();

        try {
            StringBuilder sb = new StringBuilder();
            String s;
            while ((s = request.getReader().readLine()) != null) {
                sb.append(s);
            }

            Student student = (Student) gson.fromJson(sb.toString(), Student.class);

            Status status = new Status();
            if (student.getName().equalsIgnoreCase("edw")) {
                status.setSuccess(true);
                status.setDescription("success");
            } else {
                status.setSuccess(false);
                status.setDescription("not edw");
            }
            response.getOutputStream().print(gson.toJson(status));
            response.getOutputStream().flush();
        } catch (Exception ex) {
            ex.printStackTrace();
            Status status = new Status();
            status.setSuccess(false);
            status.setDescription(ex.getMessage());
            response.getOutputStream().print(gson.toJson(status));
            response.getOutputStream().flush();
        } 
    }

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

This is a screenshot on how my message actually look like

Have Fun (H)