JDBC处理大数据

一.JDBC处理大数据概述

1.大数据分类

大数据(Large Objects)存储类型CLOB(Character Large Object)存储大文本（文章，段落，文本等）BLOB(binary large object)存储二进制数据（图像，声音，视频等）

2.关键技术

• 在JDBC开发中操作LOB必须使用PreparedStatement并结合IO流对其进行存储。

二.JDBC处理CLOB

1.CLOB在数据库中数据类型

• 在MySQL中可用 text 类型存放CLOB数据

2.创建表

-- 创建表
DROP TABLE IF EXISTS clob01;
CREATE TABLE clob01(id INT PRIMARY KEY auto_increment,words text
);

3.JDBCUtils工具类

package cn.com.demo;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class JDBCUtils {// 加载驱动并建立数据库连接public static Connection getConnection() throws SQLException, ClassNotFoundException {Class.forName("com.mysql.jdbc.Driver");String databaseUrl = "jdbc:mysql://localhost:3306/mydb";String username = "root";String password = "test";Connection connection = DriverManager.getConnection(databaseUrl, username, password);return connection;}// 关闭数据库连接释放资源public static void release(Statement statement) {if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}statement = null;}}// 关闭数据库连接释放资源public static void release(Connection connection) {if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}connection = null;}}// 关闭数据库连接释放资源public static void release(Statement statement, Connection connection) {if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}statement = null;}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}connection = null;}}// 关闭数据库连接释放资源public static void release(ResultSet resultSet, Statement statement, Connection connection) {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}resultSet = null;}release(statement, connection);}
}

4.将CLOB数据存入数据库

package cn.com.demo;import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestCLOB {public static void main(String[] args) {TestCLOB test=new TestCLOB();test.saveCLOBToDatabase();}/*** 从本地文件E:\\test.txt中读取CLOB数据并存至数据库*/public void saveCLOBToDatabase() {Connection connection = null;PreparedStatement  preparedStatement = null;try {connection = JDBCUtils.getConnection();String sql = "INSERT INTO clob01 values(?,?)";preparedStatement = connection.prepareStatement(sql);File file = new File("E:\\test.txt");FileInputStream fileInputStream=new FileInputStream(file);Reader reader = new InputStreamReader(fileInputStream,"utf-8");preparedStatement.setInt(1,1);int length= (int) file.length();// 关键操作preparedStatement.setCharacterStream(2,reader,length);preparedStatement.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.release(null, preparedStatement, connection);}}}

5.从数据库读取CLOB数据

package cn.com.demo;import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestCLOB {public static void main(String[] args) {TestCLOB test=new TestCLOB();test.getCLOBFromDatabase();}/*** 从数据库读取CLOB数据并将其保存至本地test02.txt文件中*/public void getCLOBFromDatabase() {Connection connection = null;PreparedStatement preparedStatement  = null;ResultSet resultSet = null;try {connection = JDBCUtils.getConnection();String sql = "SELECT * FROM clob01";preparedStatement  = connection.prepareStatement(sql);resultSet = preparedStatement.executeQuery();if (resultSet.next()) {// 关键操作Reader reader = resultSet.getCharacterStream("words");Writer writer = new FileWriter("test02.txt");int ch;while ((ch = reader.read()) != -1) {writer.write(ch);}writer.close();reader.close();}} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.release(resultSet, preparedStatement , connection);}}
}

三.JDBC处理BLOB

1.BLOB在数据库中数据类型

• 在MySQL中可用 BLOB类型存放BLOB数据

2.创建表

-- 创建表
DROP TABLE IF EXISTS blob01;
CREATE TABLE blob01(id INT PRIMARY KEY auto_increment,image BLOB
);

3.JDBCUtils工具类

package cn.com.demo;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class JDBCUtils {// 加载驱动并建立数据库连接public static Connection getConnection() throws SQLException, ClassNotFoundException {Class.forName("com.mysql.jdbc.Driver");String databaseUrl = "jdbc:mysql://localhost:3306/mydb";String username = "root";String password = "test";Connection connection = DriverManager.getConnection(databaseUrl, username, password);return connection;}// 关闭数据库连接释放资源public static void release(Statement statement) {if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}statement = null;}}// 关闭数据库连接释放资源public static void release(Connection connection) {if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}connection = null;}}// 关闭数据库连接释放资源public static void release(Statement statement, Connection connection) {if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}statement = null;}if (connection != null) {try {connection.close();} catch (SQLException e) {e.printStackTrace();}connection = null;}}// 关闭数据库连接释放资源public static void release(ResultSet resultSet, Statement statement, Connection connection) {if (resultSet != null) {try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}resultSet = null;}release(statement, connection);}
}

4.将BLOB数据存入数据库

package cn.com.demo;import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestBLOB {public static void main(String[] args) {TestBLOB test=new TestBLOB();test.saveBLOBToDatabase();}/*** 将本地文件E:\\test.jpg读取BLOB数据并存至数据库*/public void saveBLOBToDatabase() {Connection connection = null;PreparedStatement  preparedStatement = null;try {connection = JDBCUtils.getConnection();String sql = "INSERT INTO blob01 values(?,?)";preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1, 1);File file = new File("E:\\test.jpg");InputStream inputStream = new FileInputStream(file);int length= (int) file.length();// 关键操作preparedStatement.setBinaryStream(2,inputStream,length);		preparedStatement.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.release(null, preparedStatement, connection);}}
}

5.从数据库读取BLOB数据

package cn.com.demo;import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestBLOB {public static void main(String[] args) {TestBLOB test=new TestBLOB();test.getBLOBFromDatabase();}/*** 从数据库读取BLOB数据并保存至本地mytest.jpg*/public void getBLOBFromDatabase() {Connection connection = null;PreparedStatement preparedStatement = null;ResultSet resultSet = null;try {connection = JDBCUtils.getConnection();String sql = "SELECT * FROM tblob01 where id = ?";preparedStatement = connection.prepareStatement(sql);preparedStatement.setInt(1, 1);// 关键操作resultSet = preparedStatement.executeQuery();if (resultSet.next()) {InputStream inputStream = resultSet.getBinaryStream("image");BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);OutputStream outputStream = new FileOutputStream("mytest.jpg");OutputStream bufferedOutputStream = new BufferedOutputStream(outputStream);int b;while ((b = bufferedInputStream.read()) != -1) {bufferedOutputStream.write(b);}bufferedOutputStream.close();bufferedInputStream.close();}} catch (Exception e) {e.printStackTrace();} finally {JDBCUtils.release(resultSet, preparedStatement, connection);}}
}