Java: 使用jdbc 连接MySQL数据库(五) 处理BLOB及CLOB
默认分类
2020-01-15
300
0
BLOB(binary large object) 二进制大对象,是一个可以存储二进制数据的容器.基本上是文档,图像,音频或者其他二进制对象. 一般数据库支持BLOB的并不多
CLOB(character large object) 字符大对象是一个可以存储字符的容器,基本上用来存储文本文档(如text,XML)
BLOB样本数据建立
create database if not exists demo; use demo; drop table if exists employees; CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `last_name` varchar(64) DEFAULT NULL, `first_name` varchar(64) DEFAULT NULL, `email` varchar(64) DEFAULT NULL, `department` varchar(64) DEFAULT NULL, `salary` DECIMAL(10,2) DEFAULT NULL, `resume` BLOB, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (4,'Williams','David','david.williams@foo.com', 'HR', 120000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (5,'Johnson','Lisa','lisa.johnson@foo.com', 'Engineering', 50000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (6,'Smith','Paul','paul.smith@foo.com', 'Legal', 100000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (7,'Adams','Carl','carl.adams@foo.com', 'HR', 50000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (8,'Brown','Bill','bill.brown@foo.com', 'Engineering', 50000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (9,'Thomas','Susan','susan.thomas@foo.com', 'Legal', 80000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (10,'Davis','John','john.davis@foo.com', 'HR', 45000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (11,'Fowler','Mary','mary.fowler@foo.com', 'Engineering', 65000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (12,'Waters','David','david.waters@foo.com', 'Legal', 90000.00);
Blob存储
import java.io.File; import java.io.FileInputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; /** * * @author www.luv2code.com * */ public class WriteBlobDemo { public static void main(String[] args) throws Exception { Connection myConn = null; PreparedStatement myStmt = null; FileInputStream input = null; try { // 1\. Get a connection to database myConn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/demo", "student", "student"); // 2\. Prepare statement String sql = "update employees set resume=? where email='john.doe@foo.com'"; myStmt = myConn.prepareStatement(sql); // 3\. Set parameter for resume file name File theFile = new File("sample_resume.pdf"); input = new FileInputStream(theFile); myStmt.setBinaryStream(1, input); System.out.println("Reading input file: " + theFile.getAbsolutePath()); // 4\. Execute statement System.out.println("\nStoring resume in database: " + theFile); System.out.println(sql); myStmt.executeUpdate(); System.out.println("\nCompleted successfully!"); } catch (Exception exc) { exc.printStackTrace(); } finally { if (input != null) { input.close(); } close(myConn, myStmt); } } private static void close(Connection myConn, Statement myStmt) throws SQLException { if (myStmt != null) { myStmt.close(); } if (myConn != null) { myConn.close(); } } }
BLOB读取
import java.io.File; import java.io.FileOutputStream; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * * @author www.luv2code.com * */ public class ReadBlobDemo { public static void main(String[] args) throws Exception { Connection myConn = null; Statement myStmt = null; ResultSet myRs = null; InputStream input = null; FileOutputStream output = null; try { // 1\. Get a connection to database myConn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/demo", "student", "student"); // 2\. Execute statement myStmt = myConn.createStatement(); String sql = "select resume from employees where email='john.doe@foo.com'"; myRs = myStmt.executeQuery(sql); // 3\. Set up a handle to the file File theFile = new File("resume_from_db.pdf"); output = new FileOutputStream(theFile); if (myRs.next()) { input = myRs.getBinaryStream("resume"); System.out.println("Reading resume from database..."); System.out.println(sql); byte[] buffer = new byte[1024]; while (input.read(buffer) > 0) { output.write(buffer); } System.out.println("\nSaved to file: " + theFile.getAbsolutePath()); System.out.println("\nCompleted successfully!"); } } catch (Exception exc) { exc.printStackTrace(); } finally { if (input != null) { input.close(); } if (output != null) { output.close(); } close(myConn, myStmt); } } private static void close(Connection myConn, Statement myStmt) throws SQLException { if (myStmt != null) { myStmt.close(); } if (myConn != null) { myConn.close(); } } }
CLOB样本建立
create database if not exists demo; use demo; drop table if exists employees; CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `last_name` varchar(64) DEFAULT NULL, `first_name` varchar(64) DEFAULT NULL, `email` varchar(64) DEFAULT NULL, `department` varchar(64) DEFAULT NULL, `salary` DECIMAL(10,2) DEFAULT NULL, `resume` LONGTEXT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (1,'Doe','John','john.doe@foo.com', 'HR', 55000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (2,'Public','Mary','mary.public@foo.com', 'Engineering', 75000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (3,'Queue','Susan','susan.queue@foo.com', 'Legal', 130000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (4,'Williams','David','david.williams@foo.com', 'HR', 120000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (5,'Johnson','Lisa','lisa.johnson@foo.com', 'Engineering', 50000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (6,'Smith','Paul','paul.smith@foo.com', 'Legal', 100000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (7,'Adams','Carl','carl.adams@foo.com', 'HR', 50000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (8,'Brown','Bill','bill.brown@foo.com', 'Engineering', 50000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (9,'Thomas','Susan','susan.thomas@foo.com', 'Legal', 80000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (10,'Davis','John','john.davis@foo.com', 'HR', 45000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (11,'Fowler','Mary','mary.fowler@foo.com', 'Engineering', 65000.00); INSERT INTO `employees` (`id`,`last_name`,`first_name`,`email`, `department`, `salary`) VALUES (12,'Waters','David','david.waters@foo.com', 'Legal', 90000.00);
CLOB写入
import java.io.File; import java.io.FileInputStream; import java.io.FileReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Statement; /** * * @author www.luv2code.com * */ public class WriteClobDemo { public static void main(String[] args) throws Exception { Connection myConn = null; PreparedStatement myStmt = null; FileReader input = null; try { // 1\. Get a connection to database myConn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/demo", "student", "student"); // 2\. Prepare statement String sql = "update employees set resume=? where email='john.doe@foo.com'"; myStmt = myConn.prepareStatement(sql); // 3\. Set parameter for resume file name File theFile = new File("sample_resume.txt"); input = new FileReader(theFile); myStmt.setCharacterStream(1, input); System.out.println("Reading input file: " + theFile.getAbsolutePath()); // 4\. Execute statement System.out.println("\nStoring resume in database: " + theFile); System.out.println(sql); myStmt.executeUpdate(); System.out.println("\nCompleted successfully!"); } catch (Exception exc) { exc.printStackTrace(); } finally { if (input != null) { input.close(); } close(myConn, myStmt); } } private static void close(Connection myConn, Statement myStmt) throws SQLException { if (myStmt != null) { myStmt.close(); } if (myConn != null) { myConn.close(); } } }
CLOB读取
import java.io.File; import java.io.FileOutputStream; import java.io.FileWriter; import java.io.InputStream; import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /** * * @author www.luv2code.com * */ public class ReadClobDemo { public static void main(String[] args) throws Exception { Connection myConn = null; Statement myStmt = null; ResultSet myRs = null; Reader input = null; FileWriter output = null; try { // 1\. Get a connection to database myConn = DriverManager.getConnection( "jdbc:mysql://localhost:3306/demo", "student", "student"); // 2\. Execute statement myStmt = myConn.createStatement(); String sql = "select resume from employees where email='john.doe@foo.com'"; myRs = myStmt.executeQuery(sql); // 3\. Set up a handle to the file File theFile = new File("resume_from_db.txt"); output = new FileWriter(theFile); if (myRs.next()) { input = myRs.getCharacterStream("resume"); System.out.println("Reading resume from database..."); System.out.println(sql); int theChar; while ((theChar = input.read()) > 0) { output.write(theChar); } System.out.println("\nSaved to file: " + theFile.getAbsolutePath()); System.out.println("\nCompleted successfully!"); } } catch (Exception exc) { exc.printStackTrace(); } finally { if (input != null) { input.close(); } if (output != null) { output.close(); } close(myConn, myStmt); } } private static void close(Connection myConn, Statement myStmt) throws SQLException { if (myStmt != null) { myStmt.close(); } if (myConn != null) { myConn.close(); } } }
0条评论