数据库BLOB的处理

文件是一种抽象的数据形式，将文件存入数据库需要转为二进制形式存入BLOB字段，一般数据库管理工具无法对其直接操作，需要程序来存取。

下面是postgre数据库往达梦数据库迁移数据实例，踩了很多坑

先来看一下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>org.example</groupId>
    <artifactId>ptd</artifactId>
    <version>1.0</version>
    <dependencies>

        <!--俩数据库驱动-->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.5</version>
        </dependency>
        <dependency>
            <groupId>com.dm</groupId>
            <artifactId>Dm7JdbcDriver18</artifactId>
            <version>1.0.0</version>
        </dependency>
    </dependencies>
    <build>
        <plugins>
            <!--jdk8声明-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                </configuration>
            </plugin>
            <!--打包时引入依赖包，在清单中写入程序入口-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>3.2.1</version>
                <configuration>
                    <createDependencyReducedPom>false</createDependencyReducedPom>
                </configuration>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <transformers>
                                <transformer
                                        implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <!--这里写你的main函数所在的类的路径名，也就是Class.forName的那个字符串-->
                                    <mainClass>ptd</mainClass>
                                </transformer>
                            </transformers>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>
</project>

不在POM中声明jdk版本会报错，不引打包插件会导致清单中没有主程序入口，不引入依赖包的问题。

再看一下主程序，博客园这个java代码高亮真捞呀要交给运维，所以使用了外部配置文件

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;



public class ptd {
    public static void main(String[] args) {
        try (InputStream is = new FileInputStream("D:\\ptd.properties");) {     //引入配置文件
            //props.getProperty()从配置文件读取数据
            Properties props = new Properties();
            props.load(is);
            Class.forName("dm.jdbc.driver.DmDriver");              //实际上需要提前装载驱动，postgre驱动比较高级不用
            try (Connection conn = DriverManager.getConnection(    //建立两个数据库连接
                    props.getProperty("postgre_url"), props.getProperty("postgre_user"), props.getProperty("postgre_password"));
                 Connection con1 = DriverManager.getConnection(
                         props.getProperty("dm_url"), props.getProperty("dm_user"), props.getProperty("dm_password")
                 );) {
                String SQL_SELECT = "Select id_,rev_,name_,deployment_id_,bytes_,generated_ from " + props.getProperty("postgre_table");
                PreparedStatement statement = conn.prepareStatement(SQL_SELECT);
                ResultSet resultSet = statement.executeQuery();
                String SQL_IN = "insert into " + props.getProperty("dm_table") + " values(?,?,?,?,?,?)";
                PreparedStatement statement1 = con1.prepareStatement(SQL_IN);
                int i = 0;
                while (resultSet.next()) {
                    i++;
                    String id_p = resultSet.getString(1);
                    int rev_p = resultSet.getInt(2);
                    String name_p = resultSet.getString(3);
                    String deployment_id_p = resultSet.getString(4);
                    byte[] blob_p = resultSet.getBytes(5);
                    //Blob blob_p= resultSet.getBlob(5);       因为文件比较大getBlob()会报错，曲线救国使用byte数据来存取
                    int generated_p = (resultSet.getBoolean(6) ? 1 : 0);
                    statement1.setString(1, id_p);
                    statement1.setInt(2, rev_p);
                    statement1.setString(3, name_p);
                    statement1.setString(4, deployment_id_p);
                    statement1.setBlob(5, new ByteArrayInputStream(blob_p));
                    statement1.setInt(6, generated_p);
                    statement1.addBatch();
                }
                statement1.executeBatch();
                System.out.println("Successfully migrated " + i + " rows of data");
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
}