Spring 操作达梦数据库

一、前提条件

本篇博客以访问本地达梦数据库（DM8）为基础进行演示。（前提：本地已经安装了 DM8 数据库！）

关于 Windows 安装达梦数据库，请参考博客：Windows 安装达梦数据库

关于 Docker 安装达梦数据库，请参考博客：Docker 安装达梦数据库

关于JDBC 方式操作达梦数据库，请参考博客：JDBC 方式操作达梦数据库

关于 MyBatis Plus 操作达梦数据库，请参考博客：MyBatis Plus 操作达梦数据库

关于 SpringBoot MyBatis Plus 整合达梦数据库，请参考博客：SpringBoot MyBatis Plus 整合达梦数据库

二、准备 Sping 相关依赖包

Spring 依赖包下载地址：JAVA_Spring_lib.zip

下载解压后，效果如下：

三、Spring 操作达梦数据库

1、创建 Maven 工程

1）新建 Maven 工程 dm-spring

（工程名根据实际情况命名，此处仅作为示例参考），工程结构如下：

2）将 lib 中的 jar 文件添加到 Libararies 中

Project Struture --> Project Settings --> Libararies --> +（添加），引入 lib 目录下的 jar 文件。

或者

右键点击 lib 文件夹，“Add as Libararies” --> Create Libarary，输入仓库名，即可将lib目录下的jar文件，引入到工程中使用。

2、工程文件

1）BigDataMapper.java

package dameng.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import dameng.pojo.BigData;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface BigDataMapper extends BaseMapper<BigData> {
}

2）ProductCategoryMapper.java

package dameng.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import dameng.pojo.ProductCategory;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface ProductCategoryMapper extends BaseMapper<ProductCategory> {
}

3）BigData.java

package dameng.pojo;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

@TableName(value = "PRODUCTION.BIG_DATA")
public class BigData {
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;
    private byte[] photo; //mybatis 将 Image 和 Blob 映射成 byte[]
    private byte[] describe;
    private String txt; //mybatis 将 Clob 映射成 String

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public byte[] getPhoto() {
        return photo;
    }

    public void setPhoto(byte[] photo) {
        this.photo = photo;
    }

    public byte[] getDescribe() {
        return describe;
    }

    public void setDescribe(byte[] describe) {
        this.describe = describe;
    }

    public String getTxt() {
        return txt;
    }

    public void setTxt(String txt) {
        this.txt = txt;
    }

    @Override
    public String toString() {
        return "TestBigData [id=" + id + ", txt=" + txt + "]";
    }

    public BigData(Long id, byte[] photo, byte[] describe, String txt) {
        super();
        this.id = id;
        this.photo = photo;
        this.describe = describe;
        this.txt = txt;
    }

    public BigData() {
        super();
    }
}

4）ProductCategory.java

package dameng.pojo;


import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

@TableName("PRODUCTION.PRODUCT_CATEGORY")
public class ProductCategory {
    @TableId(value = "product_categoryid", type = IdType.AUTO)
    private Integer product_categoryid;
    private String name;

    public ProductCategory(Integer product_categoryid, String name) {
        this.product_categoryid = product_categoryid;
        this.name = name;
    }

    public Integer getProduct_categoryid() {
        return product_categoryid;
    }

    public void setProduct_categoryid(Integer product_categoryid) {
        this.product_categoryid = product_categoryid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "ProductCategory{" +
                "product_categoryid=" + product_categoryid +
                ", name='" + name + '\'' +
                '}';
    }
}

5）TestBigData.java

package dameng.test;

import dameng.dao.BigDataMapper;
import dameng.pojo.BigData;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import javax.annotation.Resource;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.List;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"classpath:spring-dao.xml"})
public class TestBigData {

    @Resource
    BigDataMapper bigDataMapper = null;

    //测试插入大字段表
    @Test
    public void testInsert() {
        try {
            String filePath = "D:\\DM8特点.jpg";
            File file = new File(filePath);
            String filePath2 = "D:\\达梦产品简介.txt";
            File file2 = new File(filePath2);
            InputStream in;
            in = new BufferedInputStream(new FileInputStream(file));
            byte[] bytes1 = new byte[1024000];
            byte[] bytes2 = new byte[1024000];
            in.read(bytes1);
            InputStream in2 = new BufferedInputStream(new FileInputStream(file));
            in2.read(bytes2);
            BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(file2), StandardCharsets.UTF_8));
            StringBuffer stringBuffer = new StringBuffer("");
            String str = null;
            while ((str = reader.readLine()) != null) {
                stringBuffer.append(str);
                stringBuffer.append("\n");
            }
            BigData bigData = new BigData(null, bytes1, bytes2, stringBuffer.toString());
            bigDataMapper.insert(bigData);
            in.close();
            in2.close();
            reader.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //测试查询大字段表
    @Test
    public void testSelect() {
        List<BigData> list = bigDataMapper.selectList(null);
        try {
            for (BigData big : list) {
                //打印出id
                System.out.println("id = " + big.getId());
                //将 photo 列信息输出到指定路径
                FileOutputStream fos = new FileOutputStream("D:/" + big.getId() + "_DM8特点.jpg");
                fos.write(big.getPhoto());
                //将 describe 列信息输出到指定路径
                FileOutputStream fos2 = new FileOutputStream("D:/" + big.getId() + "_Blob_DM8特点.jpg");
                fos2.write(big.getDescribe());
                //将 photo 列信息输出到控制台
                System.out.println("txt=" + big.getTxt());
                fos.close();
                fos2.close();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

6）TestProductCategory.java

package dameng.test;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import dameng.dao.ProductCategoryMapper;
import dameng.pojo.ProductCategory;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"classpath:spring-dao.xml"})
public class TestProductCategory {
    @Autowired
    ProductCategoryMapper productCategoryMapper = null;

    //测试插入信息
    @Test
    public void testInstert() {
        productCategoryMapper.insert((new ProductCategory(null, "语文")));
    }

    //测试修改信息
    @Test
    public void testUpdate() {
        ProductCategory productCategory = productCategoryMapper.selectById(4);
        productCategory.setName("英语");
        productCategoryMapper.updateById(productCategory);
    }

    //测试根据 id 查询指定人信息
    @Test
    public void testSelectPersonById() {
        ProductCategory productCategory = productCategoryMapper.selectById(1);
        System.out.println(productCategory);
    }

    //测试全查
    @Test
    public void testSelectAll() {
        List<ProductCategory> selectList = productCategoryMapper.selectList(null);
        for (ProductCategory p : selectList) {
            System.out.println(p);
        }
    }

    //测试删除
    @Test
    public void testDelete() {
        productCategoryMapper.deleteById(5);
    }
}

7）BigDataMapper.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="dameng.dao.BigDataMapper">
</mapper>

8）ProductCategoryMapper.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="dameng.dao.ProductCategoryMapper">
</mapper>

9）jdbc.properties

jdbc.driver=dm.jdbc.driver.DmDriver
jdbc.url=jdbc:dm://localhost:5236
jdbc.username=SYSDBA
jdbc.password=SYSDBA

10）log4j.properties

#log4j.rootLogger=DEBUG,console,dailyFile
log4j.rootLogger=DEBUG,dailyFile
log4j.additivity.org.apache=true

# 控制台(console)
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.ImmediateFlush=true
log4j.appender.console.Target=System.out
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%-5p] %d(%r) --> [%t] %l: %m %x %n

# 日志文件(logFile)
log4j.appender.logFile=org.apache.log4j.FileAppender
log4j.appender.logFile.Threshold=DEBUG
log4j.appender.logFile.ImmediateFlush=true
log4j.appender.logFile.Append=true
log4j.appender.logFile.File=D:/logs/dm80.log
log4j.appender.logFile.layout=org.apache.log4j.PatternLayout
log4j.appender.logFile.layout.ConversionPattern=[%-5p] %d(%r) --> [%t] %l: %m %x %n

# 回滚文件(rollingFile)
log4j.appender.rollingFile=org.apache.log4j.RollingFileAppender
log4j.appender.rollingFile.Threshold=DEBUG
log4j.appender.rollingFile.ImmediateFlush=true
log4j.appender.rollingFile.Append=true
log4j.appender.rollingFile.File=D:/logs/dm81.log
log4j.appender.rollingFile.MaxFileSize=20MB
log4j.appender.rollingFile.MaxBackupIndex=50
log4j.appender.rollingFile.layout=org.apache.log4j.PatternLayout
log4j.appender.rollingFile.layout.ConversionPattern=[%-5p] %d(%r) --> [%t] %l: %m %x %n

# 定期回滚日志文件(dailyFile)
log4j.appender.dailyFile=org.apache.log4j.DailyRollingFileAppender
log4j.appender.dailyFile.Threshold=DEBUG
log4j.appender.dailyFile.ImmediateFlush=true
log4j.appender.dailyFile.Append=true
log4j.appender.dailyFile.File=D:/logs/dm82.log
log4j.appender.dailyFile.DatePattern='.'yyyy-MM-dd
log4j.appender.dailyFile.layout=org.apache.log4j.PatternLayout
log4j.appender.dailyFile.layout.ConversionPattern=[%-5p] %d(%r) --> [%t] %l: %m %x %n
# 应用于socket
log4j.appender.socket=org.apache.log4j.RollingFileAppender
log4j.appender.socket.RemoteHost=localhost
log4j.appender.socket.Port=5001
log4j.appender.socket.LocationInfo=true
# Set up for Log Factor 5
log4j.appender.socket.layout=org.apache.log4j.PatternLayout
log4j.appender.socket.layout.ConversionPattern=[%-5p] %d(%r) --> [%t] %l: %m %x %n
# Log Factor 5 Appender
log4j.appender.LF5_APPENDER=org.apache.log4j.lf5.LF5Appender
log4j.appender.LF5_APPENDER.MaxNumberOfRecords=2000
# 发送日志到指定邮件
log4j.appender.mail=org.apache.log4j.net.SMTPAppender
log4j.appender.mail.Threshold=FATAL
log4j.appender.mail.BufferSize=10
log4j.appender.mail.From = xxx@mail.com
log4j.appender.mail.SMTPHost=mail.com
log4j.appender.mail.Subject=Log4J Message
log4j.appender.mail.To= xxx@mail.com
log4j.appender.mail.layout=org.apache.log4j.PatternLayout
log4j.appender.mail.layout.ConversionPattern=[%-5p] %d(%r) --> [%t] %l: %m %x %n

# 应用于数据库
log4j.appender.database=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.database.URL=jdbc:dm://localhost:5236
log4j.appender.database.driver=dm.jdbc.driver.DmDriver
log4j.appender.database.user=SYSDBA
log4j.appender.database.password=SYSDBA
log4j.appender.database.sql=INSERT INTO LOG4J (Message) VALUES('=[%-5p] %d(%r) --> [%t] %l: %m %x %n')
log4j.appender.database.layout=org.apache.log4j.PatternLayout
log4j.appender.database.layout.ConversionPattern=[%-5p] %d(%r) --> [%t] %l: %m %x %n

11）spring-dao.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:jee="http://www.springframework.org/schema/jee"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.0.xsd
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">

    <!-- 配置整合 mybatis-plus 过程-->
    <!-- 1、配置数据库相关参数 properties 的属性：${url}-->
    <context:property-placeholder location="classpath:jdbc.properties"/>
    <!-- 2、配置数据库连接池-->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"/>
        <property name="jdbcUrl" value="${jdbc.url}"/>
        <property name="user" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <!-- mybatis 的 sqlsessionFactorybean：org.mybatis.spring.SqlSessionFactoryBean-->
    <!-- 3、配置 mybatis-plus 的 sqlSessionFactory -->
    <bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="typeAliasesPackage" value="dameng.pojo"/>
    </bean>
    <!-- 4、DAO 接口所在包名，Spring 会自动查找其下的类 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="dameng.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    </bean>
</beans>