springboot+达梦数据库

什么是达梦数据库

达梦数据库是一款国产化数据库，多用于军工企业，参考orcal编写，故sql与函数用法与orcal一致

引入达梦数据库jar

1.先在代码src目录下新建一个lib目录，将Dm7JdbcDriver17.jar拷入该目录
2.在pom.xml中引入该jar
        <dependency>
            <groupId>com.dm</groupId>
            <artifactId>Dm7JdbcDriver</artifactId>
            <version>1.7</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/src/lib/Dm7JdbcDriver17.jar</systemPath>
        </dependency>
3.编译时配置将该jar打包
 <build>
        <plugins>

            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <includeSystemScope>true</includeSystemScope>
                </configuration>
            </plugin>
        </plugins>
    </build>

完整的pom文件如下

<?xml version="1.0" encoding="UTF-8"?>
<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>com.bdcloud</groupId>
    <artifactId>DMUtils</artifactId>
    <version>1.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.0.RELEASE</version>
    </parent>

    <dependencies>
        <dependency>
            <groupId>com.dm</groupId>
            <artifactId>Dm7JdbcDriver</artifactId>
            <version>1.7</version>
            <scope>system</scope>
            <systemPath>${project.basedir}/src/lib/Dm7JdbcDriver17.jar</systemPath>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>

            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <includeSystemScope>true</includeSystemScope>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

完整的application.xml文件如下

spring:
  datasource:
    url: jdbc:dm://202.61.89.33:16002/HIGHGAIN?zeroDateTimeBehavior=convertToNull&useUnicode=true&characterEncoding=utf-8
    username: xxxaaa
    password: xxxbbb
    driver-class-name: dm.jdbc.driver.DmDriver

主要功能实现-从文件中读取完整sql，并借用JdbcTemplate执行

package com.bdcloud.service;

import com.bdcloud.dao.UpdateDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;

@Service
public class UpdateService implements UpdateDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Override
    public int insert(String fileName) {

         String sql = loadSql(fileName);
        int update = jdbcTemplate.update(sql);
        System.out.println("执行成功！！！");
        return update;
    }

    @Override
    public String findTypeById(String id) {
        String sql = loadSql("C:\\Users\\Administrator\\Desktop\\DM\\AA.sql");
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        Map<String, Object> map = maps.get(0);
        return (String) map.get("JCCA20A020");
    }

    /****
     * 根据文件完整路径名读取sql，并返回
     * @param fileName
     * @return
     */
    public String loadSql(String fileName){
//        return "INSERT INTO \"HIGHGAIN\".\"JCCA20A\" (\"JCCA20A010\",\"JCCA20A020\",\"JCCA20A030\",\"JCCA20A040\",\"JCCA20A050\",\"JCCA20A055\",\"JCCA20A060\",\"JCCA20A070\",\"JCCA20A080\",\"JCCA20A090\",\"JCCA20A100\")VALUES\n" +
//                "('1d2def8e042a4d01b810a97e687b6897', 'BD', 2, '2019-05-21 14:13:05', 'JCD22222','YHD2222', 2,3,4,5, 'QX001');";

        FileInputStream inputStream = null;
        ByteArrayOutputStream baos = null;
        File file = null;
        try {
            file = new File(fileName);
            inputStream = new FileInputStream(new File(fileName));
            baos = new ByteArrayOutputStream();
            byte[] buffer = new byte[1024];
            int num = inputStream.read(buffer);
            while (num != -1) {
                baos.write(buffer, 0, num);
                num = inputStream.read(buffer);
            }
            baos.flush();
            return new String(baos.toByteArray());
        } catch (Exception e) {
            e.printStackTrace();

        }finally {
            try {
                //关闭俩个流
                inputStream.close();
                baos.close();
                //删除当前文件
                file.delete();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

}

调用实现过程

package com.bdcloud.test;

import com.bdcloud.service.UpdateService;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.io.File;

@SpringBootTest
@RunWith(SpringRunner.class)
public class DmUtilTest {

    @Autowired
    UpdateService service;

    @Test
    public void testInsert(){
        int insert = service.insert("1111");
        System.out.println(insert);
    }

    @Test
    public void testSelect(){
        String typeById = service.findTypeById("222");
        System.out.println(typeById);
    }

    /***
     * 循环遍历目录下的所有文件并执行插入
     */
    @Test
    public void testCricleUpdate() throws InterruptedException {

        //输入文件目录
        String fileFloader = "C:\\Users\\Administrator\\Desktop\\DM";

        while (true) {
            File floder = new File(fileFloader);
            File[] files = floder.listFiles();
            for (File file :
                    files) {
                //调用文件插入
                if (file.isFile() && "sql".equals(getFileLast(file.getName()))) {
                    //读取文件插入之前先休眠5秒
                    Thread.sleep(5000);
                    service.insert(file.getAbsolutePath());
                }
            }
        }
    }

    /***
     * 获取文件后缀
     * @param fileName
     * @return
     */
    public String getFileLast(String fileName){
        return fileName.substring(fileName.lastIndexOf(".")+1);
    }
}