Mybaties 使用mapper方式实现

之前写过一篇mybaties以xml方式实现，随着对mybaties的理解，觉得xml方式不方便，写了mysql还得再实现一种方法，代码读起来也比较费时。最近走通了以mapper的方式实现，特别像 springboot中使用mybaties，代码结构更佳，写此贴让我们远离JDBC编程，走上快乐开发路途。

先看看目录树，其中有些无关的代码在帖子中不在贴出。目录结构遵循 springboot结构，dao.mapper主要是映射数据库的操作接口，domain存放实体类，resources放配置文件和sql操作文件

1. 数据库信息 db.properties，类似如下

mysql.driver = com.mysql.jdbc.Driver
#mysql.url = jdbc:mysql://xxx:3306/cloud_style?characterEncoding=utf-8&useAffectedRows=true
#mysql.username = xx
#mysql.password = xxxx

2. mybaties.xml为 mybaties的配置文件，这里面配置 database信息和sql操作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>
        <!-- 加载数据库属性文件 -->
        <properties resource="db.properties"/>

        <!-- 可设置多个连接环境信息 -->
        <environments default="mysql_developer">
            <!-- 连接环境信息，取一个任意唯一的名字 -->
            <environment id="mysql_developer">
                <!-- mybatis使用jdbc事务管理方式 -->
                <transactionManager type="jdbc"/>
                <!-- mybatis使用连接池方式来获取连接 -->
                <dataSource type="pooled">
                    <!-- 配置与数据库链接信息 -->
                    <property name="driver" value="${mysql.driver}"/>
                    <property name="url" value="${mysql.url}"/>
                    <property name="username" value="${mysql.username}"/>
                    <property name="password" value="${mysql.password}"/>
                </dataSource>
            </environment>
        </environments>
        <mappers>
            <!--映射sql操作的文件 -->
            <mapper resource="sql.xml"/>
        </mappers>

</configuration>

3. sql.xml 这里面写sql语句，其中操作结果是实体类的，可以直接指定 resultMap属性，这种映射更自由，数据库中某表字段不需要全取时，就需要resultMap自定义映射规则。一般类型可指定 resultType即可，这两个属性注意使用方式。需要注意的是指定mapper的namespace空间类似如下文件，这里面包含了实体类映射，基本类映射，单参数，多参数，动态表名等操作，可自行学习修改。

<?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="dao.mapper.WarehouseMapper">

  <resultMap type="domain.LogMetaBean" id="logMetaMap">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="clusterId" column="clusterId"/>
    <result property="path" column="path"/>
    <result property="timeFormat" column="timeFormat"/>
    <result property="storeTime" column="storeTime"/>
    <result property="block" column="block"/>
    <result property="frequency" column="frequency"/>
    <result property="delay" column="delay"/>
    <result property="cronTime" column="cronTime"/>
    <result property="commandId" column="commandId"/>
    <result property="type" column="type"/>
    <result property="state" column="state"/>
    <result property="priority" column="priority"/>
    <result property="warehouseId" column="warehouseId"/>
    <result property="dbName" column="dbName"/>
    <result property="tableName" column="tableName"/>
    <result property="description" column="description"/>
    <result property="createTime" column="createTime"/>
    <result property="updateTime" column="updateTime"/>
  </resultMap>

  <resultMap type="domain.LogDetailBean" id="logDetailMap">
    <result property="fileNum" column="fileNum"/>
    <result property="size" column="size"/>
    <result property="jobId" column="jobId"/>
  </resultMap>

  <resultMap type="domain.DagJobsBean" id="dagJobsMap">
    <result property="diagnosis" column="diagnosis"/>
    <result property="clusterName" column="clusterName"/>
  </resultMap>

  <!--<select id="selectFromLogMeta" parameterType="int" resultType="domain.LogMetaBean"></select>-->
  <select id="selectFromLogMeta" parameterType="int" resultMap="logMetaMap">
        SELECT *  FROM dm_log_meta WHERE id=#{id};
  </select>

  <select id="selectDistinctIdFromLogMeta" resultType="java.lang.Integer">
        SELECT DISTINCT id  FROM dm_log_meta;
  </select>

  <select id="selectParents" parameterType="int" resultType="java.lang.Integer">
        SELECT DISTINCT srcLogId  FROM dm_log_dependency WHERE logId=#{logId};
  </select>

  <select id="selectChildren" parameterType="int" resultType="java.lang.Integer">
        SELECT DISTINCT logId FROM dm_log_dependency WHERE srcLogId=#{srcLogId};
  </select>

  <select id="selectLogDetailByLogId"  resultMap="logDetailMap">
        SELECT fileNum, `size`, jobId FROM dm_log_details WHERE logId=#{logId} AND `time` LIKE #{timeFuzzyMatch};
  </select>

  <select id="selectDiagnosisAndClusterFromDagJobs" parameterType="int" resultMap="dagJobsMap">
        SELECT diagnosis, clusterName FROM dag_jobs WHERE id=#{id};
  </select>

  <select id="selectCostFromJobinfo" resultType="java.lang.Float">
        SELECT cost FROM ${tableName} WHERE jobId=#{jobId};
  </select>

</mapper>

4. 写mapper映射接口，不需要实现。实体类具体就不贴代码了，自己根据自己需要创建。多参数需要使用Param注解，参数名字和sql.xml中一致

package dao.mapper;

import domain.DagJobsBean;
import domain.LogDetailBean;
import domain.LogMetaBean;
import java.util.List;
import org.apache.ibatis.annotations.Param;

public interface WarehouseMapper {
  List<Integer> selectDistinctIdFromLogMeta();

  List<Integer> selectParents(int srcLogId);

  List<Integer> selectChildren(int logId);

  LogMetaBean selectFromLogMeta(int id);

  List<LogDetailBean> selectLogDetailByLogId(@Param("logId") int logId, @Param("timeFuzzyMatch") String timeFuzzyMatch);

  DagJobsBean selectDiagnosisAndClusterFromDagJobs(int id);

  float selectCostFromJobinfo(@Param("tableName") String tableName, @Param("jobId") String jobId);
}

5.建立 sqlsession 工厂，能够获取mapper对象 如下：

import dao.mapper.WarehouseMapper;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

class SessionFactory {

  public static WarehouseMapper getWarehouseMapper() throws IOException {
    String resource = "mybatis.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession session = sqlSessionFactory.openSession();
    return session.getMapper(WarehouseMapper.class);
  }
}

6. 在需要的地方取 mapper即可操作

import dao.mapper.WarehouseMapper;
import domain.LogMetaBean;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;

public class WarehouseMain {

  private static WarehouseMapper mapper;
  

  private static void init() throws IOException {
    mapper = SessionFactory.getWarehouseMapper();
  }

  public static void main(String[] args) throws IOException {
    init();
    WarehouseCost warehouseCost = new WarehouseCost(mapper, price);
    List<Integer> warehouseIdList = mapper.selectDistinctIdFromLogMeta();
   
    for (HashMap.Entry<Integer, LogMetaBean> entry : warehouseIdList.entrySet()) {
      System.out.println(entry.getKey() + "   " + entry.getValue());
    }
  }
}