之前写过一篇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 = xxxx2. 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()); } } }
