Here is a simple exampled based on the classic DEPT table.
1. We are working with a DEPT table in SQLFire defined as follows.
DEPT table
sqlf> describe dept; COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL& ------------------------------------------------------------------------------ DEPTNO |INTEGER |0 |10 |10 |NULL |NULL |NO DNAME |VARCHAR |NULL|NULL|14 |NULL |28 |YES LOC |VARCHAR |NULL|NULL|13 |NULL |26 |YES 3 rows selected
2. Setup your pom.xml to include the following.
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>sqlfire-mybatis</groupId>
<artifactId>sqlfire-mybatis</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sqlfire-mybatis</name>
<properties>
<spring.version>3.1.2.RELEASE</spring.version>
<mybatis.version>3.1.1</mybatis.version>
<mybatis.spring.version>1.1.1</mybatis.spring.version>
<dbcp.version>1.4</dbcp.version>
<cglib.version>2.2.2</cglib.version>
</properties>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>${mybatis.spring.version}</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>${dbcp.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>${cglib.version}</version>
</dependency>
</dependencies>
<repositories>
<repository>
<id>mybatis-snapshot</id>
<name>MyBatis Snapshot Repository</name>
<url>https://oss.sonatype.org/content/repositories/snapshots</url>
</repository>
</repositories>
</project>
3. Create the domain modelclass Dept.java, with the standard getter/setters for each attribute. Omitting the full class here.
Dept.java
package pas.au.vmware.se.mybatis.domain;
public class Dept
{
private int deptno;
private String dname;
private String loc;
public Dept()
{
}
public Dept(int deptno, String dname, String loc)
{
super();
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
......
4.Create a DeptMapper.xml file. I prefer to keep SQL out of the java class wheever I can , hence avoided using annotations here.
DeptMapper.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="pas.au.vmware.se.mybatis.mapper.DeptMapper">
<resultMap id="result" type="pas.au.vmware.se.mybatis.domain.Dept">
<result property="deptno" column="DEPTNO"/>
<result property="dname" column="DNAME"/>
<result property="loc" column="LOC"/>
</resultMap>
<select id="getAll" resultMap="result">
SELECT * FROM DEPT
</select>
<select id="getById" parameterType="int" resultMap="result">
SELECT * FROM DEPT WHERE DEPTNO = #{deptno}
</select>
<insert id="insertDept" parameterType="pas.au.vmware.se.mybatis.domain.Dept">
INSERT INTO dept (deptno, dname, loc)
VALUES (#{deptno}, #{dname}, #{loc})
</insert>
</mapper>
5. Create a DeptMapper interface file. Notice how this is a clean interface and no annotations to specify the SQL we are using as that's in the DeptMapper.xml file above.
DeptMapper.java
package pas.au.vmware.se.mybatis.mapper;
import java.util.List;
import pas.au.vmware.se.mybatis.domain.Dept;
public interface DeptMapper
{
public List<Dept> getAll();
public Dept getById (int deptno);
public void insertDept (Dept dept);
}
6. Create a service class for the DEPT table as shown below.
DeptService.java
package pas.au.vmware.se.mybatis.service;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import pas.au.vmware.se.mybatis.domain.Dept;
import pas.au.vmware.se.mybatis.mapper.DeptMapper;
@Service("deptService")
public class DeptService
{
@Autowired
private DeptMapper deptMapper;
public DeptMapper getDeptMapper() {
return deptMapper;
}
public void setDeptMapper(DeptMapper deptMapper) {
this.deptMapper = deptMapper;
}
public List<Dept> getAll()
{
return getDeptMapper().getAll();
}
public Dept getById(int deptno)
{
return getDeptMapper().getById(deptno);
}
@Transactional
public void insertDept (Dept dept)
{
getDeptMapper().insertDept(dept);
}
}
7. Create a spring application context file as shown below.
application-context.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:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.vmware.sqlfire.jdbc.ClientDriver" />
<property name="url" value="jdbc:sqlfire://localhost:1527/" />
<property name="username" value="app" />
<property name="password" value="app" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<tx:annotation-driven />
<context:component-scan base-package="pas.au.vmware.se.mybatis.service" />
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="pas.au.vmware.se.mybatis.mapper" />
</bean>
</beans>
8. Finally create a test class to verify the setup as shown below.
SpringDeptTest.java
package pas.au.vmware.se.mybatis.test;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import pas.au.vmware.se.mybatis.domain.Dept;
import pas.au.vmware.se.mybatis.service.DeptService;
public class SpringDeptTest
{
/**
* @param args
*/
public static void main(String[] args)
{
ApplicationContext context = new ClassPathXmlApplicationContext(
"application-context.xml");
DeptService deptService = (DeptService) context.getBean("deptService");
Dept dept = new Dept(99, "MYBATIS-TEST", "BUNDOORA");
deptService.insertDept(dept);
System.out.println("New DEPT added to SQLFire\n");
List<Dept> deps = deptService.getAll();
for (Dept d: deps)
{
System.out.println(d);
}
System.out.println("\n" + deptService.getById(20));
}
}
9. Run SpringDeptTest.java and verfy output as shown below.
Feb 20, 2013 9:14:39 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1fff7a1e: startup date [Wed Feb 20 21:14:39 EST 2013]; root of context hierarchy
Feb 20, 2013 9:14:39 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [application-context.xml]
Feb 20, 2013 9:14:40 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@57ac3379: defining beans [dataSource,sqlSessionFactory,transactionManager,org.springframework.aop.config.internalAutoProxyCreator,org.springframework.transaction.annotation.AnnotationTransactionAttributeSource#0,org.springframework.transaction.interceptor.TransactionInterceptor#0,org.springframework.transaction.config.internalTransactionAdvisor,deptService,org.springframework.context.annotation.internalConfigurationAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,org.mybatis.spring.mapper.MapperScannerConfigurer#0,org.springframework.context.annotation.ConfigurationClassPostProcessor$ImportAwareBeanPostProcessor#0,deptMapper]; root of factory hierarchy
New DEPT added to SQLFire
Dept [deptno=10, dname=ACCOUNTING, loc=NEW YORK]
Dept [deptno=20, dname=RESEARCH, loc=DALLAS]
Dept [deptno=30, dname=SALES, loc=CHICAGO]
Dept [deptno=40, dname=OPERATIONS, loc=BRISBANE]
Dept [deptno=50, dname=MARKETING, loc=ADELAIDE]
Dept [deptno=60, dname=DEV, loc=PERTH]
Dept [deptno=70, dname=SUPPORT, loc=SYDNEY]
Dept [deptno=99, dname=MYBATIS-TEST, loc=BUNDOORA]
Dept [deptno=20, dname=RESEARCH, loc=DALLAS]
The full project structure is as follows showing where the XML files exists in this setup.
