Osheep

时光不回头,当下最重要。

JDBC大数据的处理

在实际开发中,程序需要把大文本或二进制数据保存到数据库。

基本概念:大数据也称之为LOB(Large Objects),LOB又分为clob(大文本,text)
和blob(二进制数据,图像、声音、二进制文等)

对MySQL而言只有blob,而没有clob,MySQL存储大文本采用的是Text,Text和blob分别又分为:
TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
TINYBLOB、BLOB、MEDIUMBLOB和BLOB

对于MySQL中的Text类型,可调用如下方法设置:

PreparedStatement.setCharacterStream(index,reader,length);
//注意length长度须设置,并且为int型

对于MySQL中的Text类型,可调用如下方法设置:

reader = resultSet.getCharacterStream(i);
reader = resultSet.getClob(i).getCharacterStream();
String s = resultSet.getString(i);

先把数据库连接代码写好
db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testclob
username=root
password=root

JdbcUtils

package cn.itcast.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class JdbcUtils {

    private static Properties prop = new Properties();

    static {
        try {
            prop.load(JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"));
            Class.forName(prop.getProperty("driver"));
        } catch (Exception e) {
            throw new ExceptionInInitializerError(e);
        }
    }

    public static Connection getConnection() throws Exception {
        return DriverManager.getConnection(prop.getProperty("url"), prop.getProperty("username"), prop.getProperty("password"));
    }


    public static void release(Connection conn, Statement st, ResultSet rs) {

        if (rs != null) {
            try {
                rs.close();   //throw new
            } catch (Exception e) {
                e.printStackTrace();
            }
            rs = null;
        }
        if (st != null) {
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            st = null;
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

然后在src下面新建一个1.txt文件

mysql中建表:

create table testClob(
   id varchar(40) primary key,
   resume text
);

写入数据:

 @Test
    public void insert() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "insert into testclob(id,resume) values(?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "1");
            //第二个问号是一个大文本

            //注意,现在是java工程,web工程不这样写,如果非要这么写,1.txt文件应该在bin目录下
            File file = new File("src/1.txt");
            FileReader reader = new FileReader(file);
            int length = (int) file.length();
            ps.setCharacterStream(2, reader, length);//reader流与文件1.txt相关联
            int num = ps.executeUpdate();
            if (num > 0) {
                System.out.println("插入成功 ");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, ps, rs);
        }
    }

读出数据:将数据读出来重新写到D盘

 @Test
    public void read() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id,resume from testclob where id='1'";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                //读数据
                Reader reader = rs.getCharacterStream("resume");//作为流返回
                //写数据到D盘下
                FileWriter writer = new FileWriter("D:\\copy.txt");
                //从流里读数据
                try {
                    int len = 0;
                    char buffer[] = new char[1024];
                    while ((len = reader.read(buffer)) > 0) {
                        writer.write(buffer, 0, len);
                    }
                } finally {
                    if (reader != null) {
                        reader.close();
                    }
                    if (writer != null) {
                        writer.close();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, ps, rs);
        }
    }

对于MySQL中的BLOB类型,可调用如下方法设置:

PreparedStatement.setBinaryStream(i,inputStream,length);
//注意length长度须设置,并且为int型

对于MySQL中的BLOB类型,可调用如下方法设置:

InputStream in = resultSet.getBinaryStream(i);
InputStream in = resultSet.getBlob(i).getBinaryStream();

mysql中建表:

create table testblob(
   id varchar(40) primary key,
   image blob
);

读写数据

@Test
    public void insert() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "insert into testblob(id,image) values(?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "1");
            //注意,现在是java工程,web工程不这样写,如果非要这么写,1.txt文件应该在bin目录下
            File file = new File("src/1.jpg");
            FileInputStream in = new FileInputStream(file);
            int length = (int) file.length();
            ps.setBinaryStream(2, in, length);
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, ps, rs);
        }
    }

    @Test
    public void read() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "select id,image from testblob where id='1'";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                InputStream in = rs.getBinaryStream("image");
                OutputStream out = new FileOutputStream("D:\\1.jpg");
                try {
                    int len = 0;
                    byte[] buffer = new byte[1024];
                    while ((len = in.read(buffer)) > 0) {
                        out.write(buffer, 0, len);
                    }
                } finally {
                    if (in != null) {
                        in.close();
                    }
                    if (out != null) {
                        out.close();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, ps, rs);
        }
    }

一般情况下,不会把大数据直接存到数据库里,而是存在硬盘上。

点赞