Spring 操作达梦数据库-526互联

Spring 操作达梦数据库

一、前提条件

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

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

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

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

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

二、准备 MyBatis Plus 相关依赖包

MyBatis Plus 依赖包下载地址：JAVA_Spring_lib.zip

下载解压后，效果如下：

三、Spring 操作达梦数据库

1、创建 Maven 工程

1）新建 Maven 工程 dm-spring

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

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

Project Struture --> Project Settings --> Libararies --> +（添加），引入 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 org.apache.ibatis.annotations.Mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import dameng.pojo.ProductCategory;

@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 java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.*;
import com.baomidou.mybatisplus.core.MybatisSqlSessionFactoryBuilder;
import dameng.dao.BigDataMapper;
import dameng.pojo.BigData;

public class TestBigData {
    SqlSession sqlSession = null;
    BigDataMapper bigDataMapper = null;

    public void init() {
        try {
            //1. 生成 sqlsession factory biulder 对象
            MybatisSqlSessionFactoryBuilder sfb = new MybatisSqlSessionFactoryBuilder();
            //2. 加载配置文件作为一个输入流
            //这里 Resources 使用的包是 ibatis 包
            InputStream resourceAsStream;
            resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
            //3. 通过会话工厂构造器对象和配置文件流构建一个会话构造工厂
            SqlSessionFactory factory = sfb.build(resourceAsStream);
            //4. 通过 SQL 会话工厂 //true 设置 mybatis 事务自动提交
            sqlSession = factory.openSession(true);
            bigDataMapper = sqlSession.getMapper(BigDataMapper.class);
            resourceAsStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        TestBigData test = new TestBigData();
        test.init();
        test.testInsert();
        test.testSelect();
    }

    //测试插入大字段表
    private 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();
        }
    }

    //测试查询大字段表
    private 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.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import com.baomidou.mybatisplus.core.MybatisSqlSessionFactoryBuilder;
import dameng.dao.ProductCategoryMapper;
import dameng.pojo.ProductCategory;

public class TestProductCategory {
    SqlSession sqlSession = null;
    ProductCategoryMapper productCategoryMapper = null;

    public void init() {
        try {
            //1. 生成 sqlsession factory builder 对象
            MybatisSqlSessionFactoryBuilder builder = new MybatisSqlSessionFactoryBuilder();
            //2. 加载配置文件作为一个输入流
            InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactory factory = builder.build(resourceAsStream);
            //这里 Resources 使用的包是 ibatis 包
            //3. 通过会话工厂构造器对象和配置文件流构建一个会话构造工厂
            //4. 通过 SQL 会话工厂 //true 设置 mybatis 事务自动提交
            sqlSession = factory.openSession(true);
            productCategoryMapper = sqlSession.getMapper(ProductCategoryMapper.class);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //测试插入信息
    @Test
    public void testInsert() {
        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);
    }

    //测试增删改查
    public static void main(String[] args) {
        TestProductCategory test = new TestProductCategory();
        test.init();
//        test.testInsert();
//        test.testSelectPersonById();
//        test.testUpdate();
        test.testSelectAll();
//        test.testDelete();
    }
}

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）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>
    <!-- <!– 引入 db.properties 文件 –> -->
    <properties resource="jdbc.properties"></properties>
    <!-- 配置 mybatis 运行环境-->
    <environments default="development">
        <environment id="development">
            <!-- 配置事务管理，采用 JDBC 的事务管理 -->
            <transactionManager type="JDBC"/>
            <!-- POOLED:mybatis 自带的数据源，JNDI：基于 Tomcat 的数据源 -->
            <!--使用 DB.properties-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- 将 mapper 文件加入到配置文件中 mapper 文件是写 SQL 语句的文件 -->
    <mappers>
        <!--普通数据的增删改查/-->
        <mapper resource="mapper/ProductCategoryMapper.xml"/>
        <!--大字段的操作/-->
        <mapper resource="mapper/BigDataMapper.xml"/>
    </mappers>
</configuration>