22 June 2014
...without the XML parser thinking you are starting or ending an XML tag:
<select id="selectNames"
parameterType="int"
resultType="com.manniwood.some.Type">
<![CDATA[
select name
from some_table
where id > #{id}
]]>
</select>
25 May 2014
In your MyBatis config file, you may as well be specific about the transaction isolation level you want. This can be set using the defaultTransactionIsolationLevel setting. The recommented setting for PostgreSQL is the default setting of 2 (read committed).
MyBatis has a nice feature where every property named "driver.foo" will get put into the properties handed to each database connection is it is made (but renamed to just "foo"). Later versions of the PostgreSQL JDBC driver introduced a new "ApplicationName" property ("driver.ApplicationName" in MyBatis) which will fill in the application_name column of the pg_stat_activity table when you are looking at database connections. This can be very helpful for debuggin purposes.
Here is how to use both properties in the main MyBatis config file:
<?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="org.postgresql.Driver"/>
<property name="url" value="jdbc:postgresql://localhost:5432/test_db"/>
<property name="username" value="test_user"/>
<property name="password" value="test_pass"/>
<!-- set transaction isolation level to 2, TRANSACTION_READ_COMMITTED, PostgreSQL's default -->
<property name="defaultTransactionIsolationLevel" value="2"/>
<!-- set pg_stat_activity.application_name for this connection -->
<property name="driver.ApplicationName" value="Test App"/>
</dataSource>
</environment>
</environments>
</configuration>
Here's how to make MyBatis, PostgreSQL, and UUIDs all play nice. UUIDs have to be compiled into PostgreSQL (not the topic of this writeup).
Here's a bean whose attributes I would like to store in my database. One of its attributes is of type UUID.
src/main/java/com/manniwood/beans/TestBean.java
package com.manniwood.beans;
import java.util.UUID;
public class TestBean {
private UUID testId;
private String name;
public TestBean() {
// null constructor keeps mybatis happy
}
public UUID getTestId() {
return testId;
}
public void setTestId(UUID testId) {
this.testId = testId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
Here's a MyBatis TypeHandler I wrote for the UUID type.
src/main/java/com/manniwood/mybatis/typehandlers/UUIDTypeHandler.java
package com.manniwood.mybatis.typehandlers; import java.sql.CallableStatement; @MappedJdbcTypes(JdbcType.OTHER) @MappedTypes(UUID.class) public class UUIDTypeHandler extends BaseTypeHandler{ @Override public void setNonNullParameter(PreparedStatement ps, int i, UUID parameter, JdbcType jdbcType) throws SQLException { ps.setObject(i, parameter, jdbcType.TYPE_CODE); } @Override public UUID getNullableResult(ResultSet rs, String columnName) throws SQLException { return (UUID)rs.getObject(columnName); } @Override public UUID getNullableResult(ResultSet rs, int columnIndex) throws SQLException { return (UUID)rs.getObject(columnIndex); } @Override public UUID getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { return (UUID)cs.getObject(columnIndex); } }
Here's the table I would like to store my data in. My bean attributes, shown above, corresponds nicely to the columns in this table.
Table "public.test"
┌─────────┬──────┬───────────┐
│ Column │ Type │ Modifiers │
├─────────┼──────┼───────────┤
│ test_id │ uuid │ not null │
│ name │ text │ not null │
└─────────┴──────┴───────────┘
Here is the MyBatis config file I will use. Note, in particular, the section that configures any TypeHandlers found in my com.manniwood.mybatis.typehandlers Java package. Note, too, how I'm ensuring JDBC uses PostgreSQL's default transaction isolation level, READ COMMITTED, so that transactions exhibit the behaviour I would expect from PostgreSQL.
src/main/resources/mybatis/config.xml
<?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>
<typeAliases>
<typeAlias alias="TestBean" type="com.manniwood.beans.TestBean" />
<typeAlias alias="UUID" type="java.util.UUID" />
<typeAlias alias="UUIDTypeHandler" type="com.manniwood.mybatis.typehandlers.UUIDTypeHandler" />
</typeAliases>
<typeHandlers>
<package name="com.manniwood.mybatis.typehandlers"/>
</typeHandlers>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="poolMaximumActiveConnections" value="10"/>
<property name="poolMaximumIdleConnections" value="5"/>
<property name="poolMaximumCheckoutTime" value="20000"/> <!-- milliseconds -->
<property name="driver" value="org.postgresql.Driver"/>
<property name="url" value="jdbc:postgresql://localhost:5432/test_db"/>
<property name="username" value="test_user"/>
<property name="password" value="test_pass"/>
<property name="defaultTransactionIsolationLevel" value="2"/> <!-- 2 == TRANSACTION_READ_COMMITTED -->
<!-- set pg_stat_activity.application_name for this connection -->
<property name="driver.ApplicationName" value="Test App"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mybatis/test_app.xml"/>
</mappers>
</configuration>
Here's the MyBatis mapper file I use, with a sample insert and select that show how to use the UUID TypeHandler I wrote. It's kind of verbose (hopefully the clever MyBatis people will find a terser way to express this) but it works!
src/main/resources/mybatis/test_app.xml
<?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="test">
<insert id="insertTest" parameterType="TestBean">
insert into test (test_id, name) values (#{testId,javaType=UUID,jdbcType=OTHER,typeHandler=UUIDTypeHandler}, #{name})
</insert>
<resultMap id="selectTestResultMap" type="TestBean">
<id property="testId" column="test_id" typeHandler="UUIDTypeHandler" />
<result property="name" column="name" />
</resultMap>
<select id="selectTest"
parameterType="TestBean"
resultMap="selectTestResultMap">
select test_id,
name
from test
where test_id = #{testId,javaType=UUID,jdbcType=OTHER,typeHandler=UUIDTypeHandler}
and name = #{name}
</select>
</mapper>
Here's a TestNG class that can be used to test the mapper using the above classes and configuration.
src/test/java/com/manniwood/mybatis/test/MybatisUUIDTest.java
package com.manniwood.mybatis.test;
import java.io.IOException;
import java.io.Reader;
import java.util.UUID;
import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.testng.Assert;
import org.testng.annotations.BeforeClass;
import org.testng.annotations.Test;
@Test
public class MybatisUUIDTest {
private static Logger log = LoggerFactory.getLogger(MybatisUUIDTest.class);
private static final String TEST_NAME = "foo";
private static final String TEST_UUID_STR = "37a82ee2-114c-4044-ba90-c073bf6d7830";
private static final String MYBATIS_CONF_FILE = "mybatis/config.xml";
private SqlSessionFactory sqlSessionFactory = null;
public MybatisUUIDTest() {
// empty constructor
}
@BeforeClass
protected void initMybatis() {
log.info("********* initializing sqlSessionFactory with conf file {} ******", MYBATIS_CONF_FILE);
Reader myBatisConfReader = null;
try {
myBatisConfReader = Resources.getResourceAsReader(MYBATIS_CONF_FILE);
} catch (IOException e) {
throw new RuntimeException("problem trying to read mybatis config file: ", e);
}
if (myBatisConfReader == null) {
throw new RuntimeException("mybatis conf reader is null");
}
try {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(myBatisConfReader);
} catch (PersistenceException e) {
throw new RuntimeException("problem trying to set up database connection: ", e);
}
if (sqlSessionFactory == null) {
throw new RuntimeException("sqlSessionFactory is null");
}
}
@Test
public void testUUID() {
TestBean t = new TestBean();
t.setTestId(UUID.fromString(TEST_UUID_STR));
t.setName(TEST_NAME);
SqlSession session = sqlSessionFactory.openSession();
try {
session.insert("test.insertTest", t);
session.commit(true);
} finally {
session.close(); // org.apache.ibatis.executor.BaseExecutor does rollback if an exception is thrown
}
TestBean result;
SqlSession session = sqlSessionFactory.openSession();
try {
result = session.selectOne("test.selectTest", t);
session.rollback(true); // just a select; rollback
} finally {
session.close(); // org.apache.ibatis.executor.BaseExecutor does rollback if an exception is thrown
}
Assert.assertEquals(UUID.fromString(TEST_UUID_STR).toString(), result.getTestId().toString(), "Test id needs to be " + TEST_UUID_STR);
Assert.assertEquals(TEST_NAME, result.getName(), "Test name needs to be " + TEST_NAME);
}
}