Jdbc BASICS (3) Big text, binary data processing, and jdbc Data Processing

Jdbc BASICS (3) Big text, binary data processing, and jdbc Data Processing

LOB (Large Objects)Divided:CLOBAndBLOBBig text and big binary data

CLOB: used to store large text

BLOB: used to store binary data, such as images, sounds, and binary files.

In mysql, onlyBLOB, No CLOB, mysql for storing big textTEXT

 

TEXTTINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT

BLOBIt can be divided into TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

The value range is as follows:

 

The specific code implementation is as follows:

1 package com. cream. ice. jdbc; 2 3 import java. io. file; 4 import java. io. fileNotFoundException; 5 import java. io. fileReader; 6 import java. io. fileWriter; 7 import java. io. reader; 8 import java. io. writer; 9 import java. SQL. connection; 10 import java. SQL. preparedStatement; 11 import java. SQL. resultSet; 12 import java. SQL. SQLException; 13 14 import org. junit. test; 15 16/** 17 * big text data operation 18*19 * assume that the table te already exists in the database. St: 20 * create table test (21 * id int primary key, 22 * content longtext23 *); 24*25 * @ author ice26 * 27 */28 public class ClobDemo {29 30 Connection connection = null; 31 PreparedStatement statement = null; 32 ResultSet resultSet = null; 33 34 @ Test35 public void add () {36 try {37 connection = JdbcUtils. getConnection (); 38 statement = connection. prepareStatement ("insert into test (id, content) values (?,?) "); 39 statement. setInt (1, 1); 40 41 // large text uses the stream format. Add the content of d:/test.txt to the content field 42 File file = new File ("d:/test.txt") of the record; 43 Reader reader = new FileReader (file ); 44 // The long parameter cannot be used. because mysql does not support such large data, 45 statement is not implemented. setCharacterStream (2, reader, (int) file. length (); 46 47 int I = statement.exe cuteUpdate (); 48 if (I> 0) 49 System. out. println ("inserted successfully"); 50 51} catch (SQLException e) {52 e. printStackTrace (); 53} catch (FileNotFoundException e) {54 e. printSta CkTrace (); 55} finally {56 JdbcUtils. releaseResources (null, statement, connection); 57} 58} 59 60 @ Test61 public void read () {62 try {63 connection = JdbcUtils. getConnection (); 64 statement = connection. prepareStatement ("select * from test where id =? "); 65 statement. setInt (1, 1); 66 67 // Save the read content to the edisk 68 resultSet = statement.exe cuteQuery (); 69 while (resultSet. next () {70 Reader reader = resultSet. getCharacterStream ("content"); 71 Writer writer Writer = new FileWriter ("e:/test.txt"); 72 char buffer [] = new char [1024]; 73 int len =-1; 74 while (len = reader. read (buffer ))! =-1) {75 writer. write (buffer, 0, len); 76} 77 reader. close (); 78 writer. close (); 79} 80} catch (Exception e) {81 e. printStackTrace (); 82} finally {83 JdbcUtils. releaseResources (resultSet, statement, connection); 84} 85} 86}

1 package com. cream. ice. jdbc; 2 3 import java. io. fileInputStream; 4 import java. io. fileOutputStream; 5 import java. io. inputStream; 6 import java. io. outputStream; 7 import java. SQL. connection; 8 import java. SQL. preparedStatement; 9 import java. SQL. resultSet; 10 11 import org. junit. test; 12 13/** 14 * big binary data operation 15*16 * assume that the database already has a table test: 17 * create table test (18 * id int primary key, 19 * content longblob 20 *); 21*22 * @ author ice23 * 24 */25 public class BlobDemo {26 Connection connection = null; 27 PreparedStatement statement = null; 28 ResultSet resultSet = null; 29 30 @ Test31 public void add () {32 try {33 connection = JdbcUtils. getConnection (); 34 statement = connection. prepareStatement ("insert into test (id, content) values (?,?) "); 35 statement. setInt (1, 1); 36 37 InputStream in = new FileInputStream ("d:/test.jpg"); 38 statement. setBinaryStream (2, in, in. available (); 39 40 int I = statement.exe cuteUpdate (); 41 if (I> 0) 42 System. out. println ("inserted successfully"); 43 44} catch (Exception e) {45 e. printStackTrace (); 46} finally {47 JdbcUtils. releaseResources (null, statement, connection); 48} 49} 50 51 @ Test52 public void read () {53 try {54 connec Tion = JdbcUtils. getConnection (); 55 statement = connection. prepareStatement ("select * from test where id =? "); 56 statement. setInt (1, 1); 57 58 // save to edisk 59 resultSet = statement.exe cuteQuery (); 60 while (resultSet. next () {61 InputStream in = resultSet. getBinaryStream ("content"); 62 OutputStream out = new FileOutputStream ("e:/test.jpg"); 63 byte B [] = new byte [1024]; 64 int len =-1; 65 while (len = in. read (B ))! =-1) {66 out. write (B, 0, len); 67} 68 out. close (); 69 in. close (); 70} 71} catch (Exception e) {72 e. printStackTrace (); 73} finally {74 JdbcUtils. releaseResources (resultSet, statement, connection); 75} 76} 77}

 

Here I used the JdbcUtils tool class in the previous jdbc basic, and also used unit testing to test two member methods. The Code has been tested and can be run.

 

Csdn blog address: jdbc BASICS (iii) Big text and binary data processing