MyBatis小试

Spring MyBatis小试

之前了解过Hibernate，感觉不写SQL语句好爽啊。后来写了一个小程序用来计算东西，用Hibernate，但是War包实在是太大了，想要减小下体积，于是试了试MyBatis，感觉不错。写下来方便以后使用。主要是参考了MyBatis官方的文章MyBatis和MyBatis-Spring。

搭建环境

IDE用的Intellij IDEA，数据库用的H2 Database，包管理Gradle，日志Log4j，然后MyBatis，数据用的是Oracle数据库自带的测试数据UTLSAMPLE.sql。其中build.gradle如下。

build.gradle

apply plugin: 'java'
apply plugin: 'idea'

repositories {
    jcenter()
}

sourceCompatibility = 1.8
targetCompatibility = 1.8

dependencies {
    compile "org.mybatis:mybatis-spring:$mybatisSpringVersion"
    compile "org.mybatis:mybatis:$mybatisVersion"

    compile "com.h2database:h2:$h2Version"
    compile "org.springframework:spring-jdbc:$springVersion"
    compile "org.springframework:spring-context:$springVersion"

    compile "org.slf4j:jcl-over-slf4j:$slf4jVersion"
    compile "org.slf4j:slf4j-log4j12:$slf4jVersion"
    compile "org.apache.logging.log4j:log4j-core:$log4jVersion"
	
    testCompile "junit:junit:$junitVersion"
    testCompile "org.springframework:spring-test:$springVersion"

    configurations.all {
        exclude group: "commons-logging", module: "commons-logging"
    }
}

配置Spring和MyBatis

参照官方文档，建立配置文件JdbcConfig.java。

JdbcConfig.java

@Configuration
@PropertySource(ignoreResourceNotFound = true, value = "classpath:jdbc.properties") //读取JDBC配置信息
@MapperScan(basePackages = "com.liangwu.mybatis.model.mapper") //扫描mapper接口
public class JdbcConfig {
    @Value("${jdbc.driverClassName:org.h2.Driver}")
    private String driverClassName;
    @Value("${jdbc.url:jdbc:h2:~/mybatis}")
    private String url;
    @Value("${jdbc.username:sa}")
    private String username;
    @Value("${jdbc.password:sa}")
    private String password;
    @Value("${jdbc.initialSize}")
    private int initialSize;
    @Value("${jdbc.maxWait}")
    private long maxWait;
    @Value("${jdbc.minIdle}")
    private int minIdle;
    @Value("${jdbc.maxActive}")
    private int maxActive;
    @Value("${jdbc.maxIdle}")
    private int maxIdle;

    @Bean //配置DataSource
    public DataSource dataSource() {
        // H2配置
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(driverClassName);
        dataSource.setUrl(url);
        dataSource.setUsername(username);
        dataSource.setPassword(password);
        // MySQL配置
//        BasicDataSource dataSource = new BasicDataSource();
//        dataSource.setDriverClassName(driverClassName);
//        dataSource.setUrl(url);
//        dataSource.setUsername(username);
//        dataSource.setPassword(password);
//        dataSource.setInitialSize(initialSize);
//        dataSource.setMaxTotal(maxActive);
//        dataSource.setMaxIdle(maxIdle);
//        dataSource.setMinIdle(minIdle);
//        dataSource.setMaxWaitMillis(maxWait);
        return dataSource;
    }

    @Bean //配置SqlSessionFactoryBean
    public SqlSessionFactoryBean sqlSessionFactory(DataSource dataSource) throws IOException {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        // 设置TypeAliase，避免在使用MyBatis时使用类的全限定名
        sqlSessionFactoryBean.setTypeAliasesPackage("com.liangwu.mybatis.model");
        // 读取MyBatis Mapper XML文件
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath*:mapper/**/*.xml"));
        return sqlSessionFactoryBean;
    }

    @Bean // 事务管理
    public DataSourceTransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

ORM Model类配置

根据测试数据的结构，主要使用了EMP和DEPT两个表，就设置两个Model类Employee与Department。

Employee.java

public class Department implements Serializable {
    private Integer id;
    private String name;
    private String location;
    private List<Employee> employeeList = new ArrayList<>();

    /*
    ...
    /*
}

Department.java

public class Employee implements Serializable {
    private Integer id;
    private String name;
    private String job;
    private Employee mgr;
    private Date hiredate;
    private Double sal;
    private Double comm;
    private Department department;
    
    /*
    ...
    */
}

配置Mapper

创建Mapper接口类，SQL语句可以使用Annotation或XML文件。其实我一直蛮喜欢用Annotation来配置的，比如Spring，但是MyBatis用Annotation来配置SQL语句的话，使用代码特别长，不好看，就改用XML方式了。

UtliMapper.java

public interface UtliMapper {
    Department selectDeptById(@Param("id") int id);

    Employee selectEmpById(@Param("id") int id);

    List<Employee> findAllEmp();
}

嵌入Select查询

在处理上面查询一个Employy的时候，属性包含Employee mgr和Department deparment，数据库里面是ID。mgr里面还有mgr，一直这样下去，而Department里面又有List<Employee>，如果查询方式为Eager的话，就会一次查询导致在数据库N多次查询。刚开始感觉还是Hibernate的简单，配置@ManyToOne和@OneToMany基本就解决了。MyBatis就自己写吧，哈哈。这里有个坑，就是配置ResultMap的时候，association和collection时，需要放最后。我理解的话就是，assocation对应XxxToOne，collection对应OneToXxx。

UtilMapper.xml

<mapper namespace="com.liangwu.mybatis.model.mapper.UtliMapper">
    <!--<cache />-->

    <resultMap id="dept" type="Department">
        <id property="id" column="deptno"/>
        <result property="name" column="dname"/>
        <result property="location" column="loc" />
        <collection property="employeeList" column="deptno" fetchType="lazy" select="selectEmpByDeptno" />
    </resultMap>

    <resultMap id="emp" type="Employee">
        <id property="id" column="empno"/>
        <result property="name" column="ename" />
        <result property="job" column="job" />
        <result property="hiredate" column="hiredate" />
        <result property="sal" column="sal" />
        <result property="comm" column="comm" />
        <association property="mgr" column="mgr" fetchType="lazy" select="selectEmpById"/>
        <association property="department" column="deptno" fetchType="lazy" select="selectDeptById" />
    </resultMap>

    <select id="selectDeptById" resultMap="dept">
        SELECT
          *
        FROM DEPT
        WHERE deptno=#{id}
    </select>

    <select id="selectEmpByDeptno" resultType="List" resultMap="emp">
        SELECT
          *
        FROM EMP e
        WHERE e.deptno=#{id}
    </select>

    <select id="selectEmpById" resultMap="emp" >
        SELECT
          *
        FROM EMP e
        WHERE e.empno=#{id}
    </select>

    <select id="findAllEmp" resultType="List" resultMap="emp">
        SELECT * FROM EMP
    </select>
</mapper>

嵌入Select结果

还有一种是直接写好SQL语句，一次查询完结果返回，这样对于复杂的结构性能上应该会好一些，毕竟查询一次只用跟数据库打一次交道，而上一个方法一次查询可能需要跟数据库交流好几次。这个就是按业务要求拼SQL语句了。

UtilMapper.xml

<resultMap id="dept" type="Department">
    <id property="id" column="deptno"/>
    <result property="name" column="dname"/>
    <result property="location" column="loc" />
    <collection property="employeeList" ofType="List" resultMap="emp" />
</resultMap>

<resultMap id="emp" type="Employee">
    <id property="id" column="empno"/>
    <result property="name" column="ename" />
    <result property="job" column="job" />
    <result property="hiredate" column="hiredate" />
    <result property="sal" column="sal" />
    <result property="comm" column="comm" />
    <association property="mgr" resultMap="emp" columnPrefix="mgr_"/>
    <association property="department" resultMap="dept" />
</resultMap>

<select id="selectDeptById" resultMap="dept">
    SELECT
      d.deptno as deptno,
      dname,
      loc,
      empno,
      ename,
      job,
      mgr,
      hiredate,
      sal,
      comm
    FROM dept d
      LEFT OUTER JOIN emp e ON d.deptno=e.deptno
    WHERE
      d.deptno=#{id}
</select>

<select id="selectEmpById" resultMap="emp">
    SELECT
      e.empno,
      e.ename,
      e.job,
      e.hiredate,
      e.sal,
      e.comm,
      d.deptno,
      d.dname,
      d.loc,
      e2.empno as mgr_empno,
      e2.ename as mgr_ename,
      e2.job as mgr_job,
      e2.hiredate as mgr_hiredate,
      e2.sal as mgr_sal,
      e2.comm as mgr_sal,
      e2.deptno as mgr_deptno
    FROM emp e
      LEFT OUTER JOIN dept d on e.deptno=d.deptno
      LEFT OUTER JOIN emp e2 on e.mgr=e2.empno
    WHERE e.empno=#{id}
</select>

测试

用Junit来单元测试吧。这样就应该可以看到结果咯。

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = {JdbcConfig.class})
public class JuintTest {
    @Autowired
    private UtliMapper mapper;

    @Test
    public void test(){
        Department dept = mapper.selectDeptById(10);
        System.out.println(dept);
        for (Employee emp : dept.getEmployeeList()){
            System.out.println(emp);
        }
        
        Employee emp = mapper.selectEmpById(7782);
        System.out.println(emp);
        System.out.println(emp.getMgr());

        for(Employee emp : mapper.findAllEmp()){
            System.out.println(emp);
        }
    }
}

FetchType中Eager和Lazy的小区别

下面是分别在Select嵌入查询时使用Eager和Lazy的区别。可以看到Eager的时候，是SQL里再嵌套；而Lazy的时候，是分别查询。这样在分析业务和调优的时候可以针对性使用了。

eager

DEBUG [main] - ==>  Preparing: SELECT * FROM dept WHERE deptno=? 
DEBUG [main] - ==> Parameters: 10(Integer)
TRACE [main] - <==    Columns: DEPTNO, DNAME, LOC
TRACE [main] - <==        Row: 10, ACCOUNTING, NEW YORK
DEBUG [main] - ====>  Preparing: SELECT e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno FROM emp e WHERE e.deptno=? 
DEBUG [main] - ====> Parameters: 10(BigDecimal)
TRACE [main] - <====    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <====        Row: 7782, CLARK, MANAGER, 7839, 1981-06-09, 2450.00, null, 10
TRACE [main] - <====        Row: 7839, KING, PRESIDENT, null, 1981-11-17, 5000.00, null, 10
TRACE [main] - <====        Row: 7934, MILLER, CLERK, 7782, 1982-01-23, 1300.00, null, 10
DEBUG [main] - <====      Total: 3
DEBUG [main] - <==      Total: 1

lazy

DEBUG [main] - ==>  Preparing: SELECT * FROM dept WHERE deptno=? 
DEBUG [main] - ==> Parameters: 10(Integer)
TRACE [main] - <==    Columns: DEPTNO, DNAME, LOC
TRACE [main] - <==        Row: 10, ACCOUNTING, NEW YORK
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: SELECT e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno FROM emp e WHERE e.deptno=? 
DEBUG [main] - ==> Parameters: 10(BigDecimal)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
TRACE [main] - <==        Row: 7782, CLARK, MANAGER, 7839, 1981-06-09, 2450.00, null, 10
TRACE [main] - <==        Row: 7839, KING, PRESIDENT, null, 1981-11-17, 5000.00, null, 10
TRACE [main] - <==        Row: 7934, MILLER, CLERK, 7782, 1982-01-23, 1300.00, null, 10
DEBUG [main] - <==      Total: 3

嵌入Select查询和嵌入Select结果的区别

其实这个通过配置Mapper的时候就可以看出来了，一个是通过几次查询，一个是通过一次查询来解决问题的。测试了下mapper.selectEmpById(7782)，看看结果有什么不一样。

嵌入Select查询

DEBUG [main] - ==>  Preparing: SELECT * FROM emp e WHERE e.empno=? 
DEBUG [main] - ==> Parameters: 7782(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DEPARTMENT_DEPTNO, MGR_EMPNO
TRACE [main] - <==        Row: 7782, CLARK, MANAGER, 7839, 1981-06-09, 2450.00, null, 10, null, null
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: SELECT * FROM emp e WHERE e.empno=? 
DEBUG [main] - ==> Parameters: 7839(BigDecimal)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DEPARTMENT_DEPTNO, MGR_EMPNO
TRACE [main] - <==        Row: 7839, KING, PRESIDENT, null, 1981-11-17, 5000.00, null, 10, null, null
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: SELECT * FROM dept WHERE deptno=? 
DEBUG [main] - ==> Parameters: 10(BigDecimal)
TRACE [main] - <==    Columns: DEPTNO, DNAME, LOC
TRACE [main] - <==        Row: 10, ACCOUNTING, NEW YORK
DEBUG [main] - <==      Total: 1
Employee{id=7782, name='CLARK', job='MANAGER', mgr=KING, hiredate=1981-06-09, sal=2450.0, comm=null, department=ACCOUNTING}

嵌入Select结果

DEBUG [main] - ==>  Preparing: SELECT e.empno, e.ename, e.job, e.hiredate, e.sal, e.comm, d.deptno, d.dname, d.loc, e2.empno as mgr_empno, e2.ename as mgr_ename, e2.job as mgr_job, e2.hiredate as mgr_hiredate, e2.sal as mgr_sal, e2.comm as mgr_sal, e2.deptno as mgr_deptno FROM emp e LEFT OUTER JOIN dept d on e.deptno=d.deptno LEFT OUTER JOIN emp e2 on e.mgr=e2.empno WHERE e.empno=? 
DEBUG [main] - ==> Parameters: 7782(Integer)
TRACE [main] - <==    Columns: EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO, DNAME, LOC, MGR_EMPNO, MGR_ENAME, MGR_JOB, MGR_HIREDATE, MGR_SAL, MGR_SAL, MGR_DEPTNO
TRACE [main] - <==        Row: 7782, CLARK, MANAGER, 1981-06-09, 2450.00, null, 10, ACCOUNTING, NEW YORK, 7839, KING, PRESIDENT, 1981-11-17, 5000.00, null, 10
DEBUG [main] - <==      Total: 1

其它

之前在看MyBatis的时候，有时候需要自己配置其它的SQL语句，比如用Map来进行动态查询，Key值表示查询的列，Value表示查询值。select * from emp where ${key}=#{value}，注意到$和#的区别，$的参数直接输出，#的参数会被替换为?，然后传入参数值执行。通过看Log文件大概就知道，一个是在生成SQL语句的时候就把${}替换（替换后SQL语句就生成了），另一个是在查询的时候把#{}代入?当中。