MyBatis与达梦数据库整合实践

1 问题描述

在负责最近项目时，由于国产化需要，数据库必须使用国产化的数据库-达梦数据库。由于之前并没有操作过达梦数据库，因此还是遇到了一些问题。本文主要是聚焦于在程序编写时整合MyBatis框架与达梦数据库，类似的关系型数据库诸如MySQL、SqlServer、Oracle，MyBatis都能做到很好的支持。在总后做党费收缴模块开发时，笔者使用MyBatis操作MongoDB，表明MyBatis框架对于非关系型数据库的工作也有很好的支持。

2 MyBatis配置

2.1 pom引入相关依赖

POM中要引入达梦数据库依赖，由于在项目中使用了Druid框架，因此也要引入相应的依赖。

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>${mybatis-spring-boot-starter.version}</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>${druid-spring-boot-starter.version}</version>
</dependency>
<dependency>
    <groupId>com.dameng</groupId>
    <artifactId>Dm7JdbcDriver17</artifactId>
    <version>7.6.0.142</version>
</dependency>

2.1 application.properties

在配置类中要指定mybaits的配置文件，以及数据库连接的用户名，代码块如下：

#------------------------mybatis------------------------
mybatis.type-aliases-package=com.cetc.camp.domain
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.config-locations=classpath:mybatis-config.xml

其中元素mybatis.config-locations的值表明该配置文件位于classpath下，即resources目录
另外也要指定数据库连接使用的用户名和密码：

# 达梦数据库地址
spring.druid.driverClassName=dm.jdbc.driver.DmDriver
spring.druid.url=jdbc:dm://200.52.200.165:5236/DmServiceDMSERVER?STU&zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
spring.druid.username=SYSDBA
spring.druid.password=SYSDBA

2.2 mybatis-config.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>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <typeAlias type="com.cetc.camp.domain.entity.ServiceEntity" alias="ServiceEntity"></typeAlias>
    </typeAliases>
</configuration>

上述代码演示了typeAlias的使用，具体可以参见mybatis 配置之别名配置元素设置
关于MyBatis配置，可以参考MyBatis中文手册

2.3配置类

package com.cetc.camp.config;


import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.cetc.camp.mapper")
@Slf4j
public class MybatisConfig {
    @Value("${mybatis.config-locations}")
    private String configLocation;

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource ds) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(ds);
        bean.setVfs(SpringBootVFS.class);

        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        bean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));

        return bean.getObject();
    }


}

其中配置类包含下句：

bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));

这表明对应众多sql的xml文件位于resources目录下mapper文件夹下。
实际如下图所示：

如果配置类没有明确指定mapper的位置、如下所示：

package com.cetc.camp.config;


import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.DefaultResourceLoader;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.cetc.camp.mapper")
@Slf4j
public class MybatisConfig {
    @Value("${mybatis.config-locations}")
    private String configLocation;

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource ds) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(ds);
        bean.setVfs(SpringBootVFS.class);
        bean.setConfigLocation(new DefaultResourceLoader().getResource(configLocation));

        return bean.getObject();
    }


}

即没有调用setMapperLocations设置mapper的位置，则mapper默认应该遵循如下的位置：

2.4 mapper位置

关于mapper的位置，阐述如上，不再赘述。

2.5 Druid配置

package com.cetc.camp.config;

import com.alibaba.druid.filter.Filter;
import com.alibaba.druid.filter.stat.StatFilter;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

@Configuration
public class DruidConfig {
    private static final long SLOW_SQL_MILLIS = 5000;

    @ConfigurationProperties(prefix = "spring.druid")
    @Bean(initMethod = "init", destroyMethod = "close")
    public DruidDataSource dataSource() throws Exception {
        DruidDataSource dataSource = new DruidDataSource();
        List list = new ArrayList();
        Collections.addAll(list, statFilter());
        dataSource.setProxyFilters(list);
        return dataSource;
    }

    @Bean
    public Filter statFilter() {
        StatFilter filter = new StatFilter();
        filter.setSlowSqlMillis(SLOW_SQL_MILLIS);
        filter.setLogSlowSql(true);
        filter.setMergeSql(true);
        return filter;
    }

    @Bean
    public ServletRegistrationBean servletRegistrationBean() {
        return new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
    }

}

访问地址如下：

http:/<host>:<port>/<context>/druid

具体可参见使用Druid监控SQL执行状态

2.6 serviceManageMapper.xml

为演示的需要，把项目中的一个用于数据库访问的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="com.cetc.camp.mapper.ServiceManageMapper">
    <resultMap id="Service" type="com.cetc.camp.domain.entity.ServiceEntity">
        <id column="id" jdbcType="VARCHAR" property="id"></id>
        <result column="service_id" jdbcType="VARCHAR" property="serviceId"></result>
        <result column="service_name" jdbcType="VARCHAR" property="serviceName"></result>
        <result column="server_ip" jdbcType="VARCHAR" property="serverIp"></result>
        <result column="server_port" jdbcType="VARCHAR" property="serverPort"></result>
        <result column="service_state" jdbcType="VARCHAR" property="serviceState"></result>
        <result column="service_type" jdbcType="VARCHAR" property="serviceType"></result>
        <result column="task_state" jdbcType="VARCHAR" property="taskState"></result>
        <result column="create_time" jdbcType="VARCHAR" property="createTime"></result>
        <result column="update_time" jdbcType="VARCHAR" property="updateTime"></result>
    </resultMap>

    <insert id="insertService" parameterType="com.cetc.camp.domain.entity.ServiceEntity">
        insert into "ZHYQ"."SERVICE" ("service_id","service_name","server_ip","server_port","service_state","service_type","task_state","create_time","update_time")
        VALUES(#{serviceId}, #{serviceName},#{serverIp},#{serverPort}, #{serviceState},#{serviceType},#{taskState}, #{createTime},#{updateTime});
    </insert>

    <update id="updateServiceStateById" parameterType="com.cetc.camp.domain.entity.ServiceEntity">
        update "ZHYQ"."SERVICE"
        <set>
            <if test="serviceId != null">"service_id"=#{serviceId},</if>
            <if test="serviceState != null">"service_state"=#{serviceState},</if>
            <if test="serverIp != null">"server_ip"=#{serverIp},</if>
            <if test="serverPort != null">"server_port"=#{serverPort},</if>
            <if test="taskState != null">"task_state"=#{taskState},</if>
            <if test="updateTime != null">"update_time"=#{updateTime},</if>
        </set>
        where "id" = #{id};
    </update>

    <update id="updateServiceStateByServiceId" parameterType="com.cetc.camp.domain.entity.ServiceEntity">
        update "ZHYQ"."SERVICE"
        <set>
            <if test="serviceState != null">"service_state"=#{serviceState},</if>
            <if test="taskState != null">"task_state"=#{taskState},</if>
            <if test="updateTime != null">"update_time"=#{updateTime},</if>
        </set>
        where "service_id" = #{serviceId};
    </update>

    <delete id="deleteService" parameterType="java.lang.String">
        delete from "ZHYQ"."SERVICE"
        where "service_id" = #{serviceId};
    </delete>

    <select id="selectRunningServiceByServiceId" resultMap="Service">
        select * from "ZHYQ"."SERVICE" where "service_id" = #{serviceId}
        and "service_state" = 'Running';
    </select>

    <select id="selectAllService" resultMap="Service">
        select * from "ZHYQ"."SERVICE";
    </select>

    <select id="selectAllRunningService" resultMap="Service">
        select * from "ZHYQ"."SERVICE" where "service_state"='Running';
    </select>

    <select id="faceServiceForImage" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='FaceService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'image'
    </select>

    <select id="faceService" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='FaceService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'stream'
    </select>

    <select id="humanServiceForImage" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='PersonAttributeService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'image'
    </select>

    <select id="militaryService" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='MilitaryRankService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'stream'
    </select>

    <select id="humanService" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='PersonAttributeService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'stream'
    </select>

    <select id="crowdDensityService" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='CrowdDensityService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'stream'
    </select>

    <select id="securityService" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='SecurityService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'stream'
    </select>

    <!--由于视频拼接服务和一点即视任务均调用此处来获取服务的ip和端口等信息，
     中并不保存一点即视任务，因此无法根据task_state进行查询
    而在数据库-->
    <select id="videoMosaicService" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='VideoStitchService'
          and "service_state"='Running'
          and "service_type" = 'stream'
    </select>


    <select id="videoConcentrationService" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='VideoConcentrateService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'stream'
    </select>

    <select id="behaviorService" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='BehaviorService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'stream'
    </select>

    <select id="vehicleService" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='VehicleService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'stream'
    </select>


    <select id="vehicleServiceForImage" resultMap="Service">
        select top 1 * from "ZHYQ"."SERVICE"
         where "service_name"='VehicleService'
          and "service_state"='Running'
          and "task_state"='Idle'
          and "service_type" = 'image'
    </select>

    <select id="getServiceInfo" resultMap="Service">
        select * from "ZHYQ"."SERVICE"
        where "service_name"=#{serviceName}
          and "server_ip"=#{ip}
          and "server_port"=#{port}
          and "service_state"='Running'
    </select>
</mapper>

上述文件中包含了常见的增删改查操作，供爱好者参考。
另外，关于达梦数据库的增删改查最好还是参照官方文档，解压压缩包之后文件名为DM7_SQL.pdf.安装包下载路径如下：
链接：https://pan.baidu.com/s/18fGo3wY3DKB2mIlHstcljA&shfl=sharepset
提取码：x76k

如果想要直接找到该文档可以网上下载，达梦数据库DM7_SQL语言使用手册

2.7 Mapper类

在项目中每一个sql的xml文件应该对应于一个Mapper类，由该类引导调用相应的xml。对应于上面的serviceMangeMapper.xml文件，笔者编写了如下的Mapper类

package com.cetc.camp.mapper;

import com.cetc.camp.domain.entity.ServiceEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @author songquanheng
 * 2019年9月27日11:27:03
 */
@Mapper
@Component
public interface ServiceManageMapper {
    /**
     * 插入一条启动服务的记录
     */
    void insertService(ServiceEntity serviceEntity);

    /**
     * 根据id更新一条启动服务状态的记录
     */
    void updateServiceStateById(ServiceEntity serviceEntity);

    /**
     * 根据service_id更新一条启动服务状态的记录
     */
    void updateServiceStateByServiceId(ServiceEntity serviceEntity);


    /**
     * 根据serviceId删除一条服务记录
     */
    void deleteService(String serviceId);

    /**
     * 根据serviceId查找正在运行服务记录
     */
    List<ServiceEntity> selectRunningServiceByServiceId(String serviceId);

    /**
     * 查找所有的服务记录
     *
     * @return 返回 ServiceEntity对象列表
     */
    List<ServiceEntity> selectAllService();

    /**
     * 查找所有的正在运行的服务记录
     *
     * @return 返回 ServiceEntity对象列表
     */
    List<ServiceEntity> selectAllRunningService();

    /**
     * 获取用于人脸图片取特征的服务的ip和端口
     *
     * @return 返回 ServiceEntity对象，其中包含了ip和端口
     */
    ServiceEntity faceServiceForImage();

    /**
     * 获取用于人脸检测的服务的ip和端口
     *
     * @return 返回 ServiceEntity对象，其中包含了ip和端口
     */
    ServiceEntity faceService();

    /**
     * 获取可用的军中军衔服务的ip和端口
     *
     * @return 返回ServiceEntity对象，包含可用的军种军衔服务的ip和端口
     */
    ServiceEntity militaryService();

    /**
     * 获取可用的用于人体图片特征提取服务的ip和端口
     *
     * @return 返回ServiceEntity对象，包含可用的人体图片特征提取的ip和端口
     */
    ServiceEntity humanServiceForImage();

    /**
     * 获取可用的用于人体属性分析服务的ip和端口
     *
     * @return 返回ServiceEntity对象，包含可用的人体图片特征提取的ip和端口
     */
    ServiceEntity humanService();


    /**
     * 获取可用的用于人流密度服务的ip和端口
     *
     * @return 返回ServiceEntity对象，包含可用的人体图片特征提取的ip和端口
     */
    ServiceEntity crowdDensityService();

    /**
     * 用于获取可用的安防服务ip和端口
     *
     * @return 返回ServiceEntity对象，包含可用的安防服务的ip和端口
     */
    ServiceEntity securityService();

    /**
     * 用于获取可用的视频拼接和一点即视ip和端口
     *
     * @return 返回ServiceEntity对象，包含可用的视频拼接和一点即视的ip和端口
     */
    ServiceEntity videoMosaicService();

    /**
     * 用于获取可用的视频浓缩服务ip和端口
     *
     * @return 返回ServiceEntity对象，包含可用的视频浓缩服务的ip和端口
     */
    ServiceEntity videoConcentrationService();

    /**
     * 用于获取可用的行为分析服务的ip和端口
     *
     * @return 返回ServiceEntity对象，包含可用的行为分析服务的ip和端口
     */
    ServiceEntity behaviorService();


    /**
     * 用于获取车辆检测的ip和端口
     *
     * @return 返回ServiceEntity对象，包含可用的车辆服务的ip和端口
     */
    ServiceEntity vehicleService();

    /**
     * 用于获取车辆图片检测的ip和端口
     *
     * @return 返回ServiceEntity对象，包含可用的车辆服务的ip和端口
     */
    ServiceEntity vehicleServiceForImage();

    /**
     * 根据服务名、ip、端口等信息查询对应的正在运行的服务信息，用以更新状态
     *
     * @param serviceName 服务名称
     * @param ip          服务所在资源的ip
     * @param port        服务对应的端口
     * @return 获取任务对应的服务信息
     * @see com.cetc.camp.common.ServiceNameEnum
     */
    ServiceEntity getServiceInfo(@Param("serviceName") String serviceName, @Param("ip") String ip, @Param("port") String port);
}

2.8 客户端

package com.cetc.camp.controller;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cetc.camp.common.*;
import com.cetc.camp.config.MergeConfig;
import com.cetc.camp.config.MosaicClickConfig;
import com.cetc.camp.config.ServerConfig;
import com.cetc.camp.domain.Task;
import com.cetc.camp.domain.dto.Page;
import com.cetc.camp.domain.entity.ServiceEntity;
import com.cetc.camp.domain.entity.TaskEntity;
import com.cetc.camp.mapper.SecurityStatusMapper;
import com.cetc.camp.mapper.ServiceManageMapper;
import com.cetc.camp.service.EndDeviceService;
import com.cetc.camp.service.InAdequateResourceException;
import com.cetc.camp.service.TaskService;
import com.cetc.camp.util.CommonReturn;
import com.cetc.camp.util.GeneralUtil;
import com.cetc.camp.util.HikException;
import com.cetc.camp.util.HttpUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 任务管理类用于创建各类任务
 *
 * @author SongQuanHeng
 * @Time: 2019/7/11-16:54
 */
@RestController
@RequestMapping("/api/task")
public class TaskController {
    private Logger logger = LoggerFactory.getLogger(this.getClass());
    private static final String CONCENTRATE_FILE_NAME_PREFIX = "ns_";

    @Autowired
    private TaskService taskService;
    @Autowired
    private ServerConfig serverConfig;
    @Autowired
    private EndDeviceService endDeviceService;
    @Autowired
    private ServiceManageMapper serviceManageMapper;

    
    @Autowired
    private SecurityStatusMapper securityStatusMapper;

    /**
     * 用于文件合并的设置
     */
    @Autowired
    private MergeConfig mergeConfig;

    @Autowired
    private MosaicClickConfig mosaicClickConfig;

    @Value("${report.cycle}")
    private int reportCyle;
	...
}

2.9 总结构

3 达梦数据库语法

由于笔者在导出时使用了Onenote导出成了一张图片，导致有些地方可能看不清楚，希望对开发爱好者有所帮助。
需要尤其注意的几点如下：

1. 达梦数据库访问时模式名和表名要用双引号，列名要用双引号括起来，而字符串要用单引号括起来。

2.10 MyBatis插

mybatis三大插件使用方法
其实这三个插件笔者都没用过，但有一个自动跳转的插件，即可以自动从mapper转到相应的xml的插件，笔者倒是十分喜爱,下载位置mybatisX
检索idea官方插件地址，搜索mybatis关键字
可以看到如下几个插件，应该都比较好用，只是笔者仅仅用过MyBatisX这个插件

3总结

本文的主要内容包括DM数据库与MyBatis的使用，行文详细的介绍了mapper是一步一步如何编写出来的，有哪些配置文件，文章也对MyBatis的插件进行了简要提及。实践是检验真理的唯一标准，笔者把实践记录下来，一方面是为了升华实践，加深理解，另一方面也是践行不断积累、水滴石穿的做事方法。文章内容也包括了Druid框架的使用，希望能对Spring Boot环境下MyBatis与DM7的整合有问题的人提供一些小小的帮助。

								2019-10-19 19:07于杭州拱墅区湖墅新村