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.
2 comments:
Great article.
BTW could you share the source code of your project ?
Thank you!
The source code for this project is as follows.
https://dl.dropbox.com/u/15829935/blog/sqlfire-mybatis.zip
Post a Comment