-clob (Character Large Object)
-For storing large amounts of text data
-There are some special characters, different database processing methods, the operation of large large segments is often handled in a streaming manner. Rather than a normal field, you can read the data at once.
-related types in MySQL
-Tinytext text column with a maximum length of 255 (2^8-1) characters
-Text (M) text column with a maximum length of 65535 (2^16-1) characters
-Mediumtext text column with a maximum length of 16777215 (2^24-1) characters
-longtext text column with a maximum length of 4294967295 or 4GB (2^32-1) characters
Example:
Store the big data file in the database, or store the string in the program in a CLOB field, and remove the value from the Clob field in the DB.
Package Com.yf.jdbc.test;import Java.io.bufferedreader;import java.io.bytearrayinputstream;import Java.io.filenotfoundexception;import Java.io.filereader;import Java.io.ioexception;import Java.io.inputstreamreader;import Java.io.reader;import Java.sql.clob;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.preparedstatement;import java.sql.resultset;import java.sql.SQLException;/** * Test the use of CLOB (Text large object) * @author YANGF **/ Public classDemo09 { Public Static voidMain (string[] args) {Connection con=NULL; PreparedStatement PS1=NULL; PreparedStatement PS2=NULL; ResultSet RS=NULL; Try { //Load Database driverClass.forName ("Com.mysql.jdbc.Driver"); //get Connection object Setup and database connectioncon = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/testjdbc","Root","123456"); PS1= Con.preparestatement ("INSERT INTO T_user (username,myinfo) VALUES (?,?)"); Ps1.setstring (1,"YANGF"); Ps1.setclob (2,NewFileReader ("D:a.txt")); //or log a string in the program to the DBPs1.setclob (2,NewBufferedReader (NewInputStreamReader (NewBytearrayinputstream ("YANGFYANGF". GetBytes ()))); PS2= Con.preparestatement ("SELECT * from t_user where id =?"); Ps2.setint (1,22009); RS=Ps2.executequery (); while(Rs.next ()) {Clob C= Rs.getclob ("MyInfo"); Reader R=C.getcharacterstream (); intLen =0; while(-1! = (len =R.read ())) {System. out. Print (Char) R.read ()); }} ps1.execute (); } Catch(Exception e) {e.printstacktrace (); } finally { if(rs! =NULL) { Try{rs.close (); } Catch(SQLException e) {e.printstacktrace (); } } if(PS1! =NULL) { Try{ps1.close (); } Catch(SQLException e) {e.printstacktrace (); } } if(Con! =NULL) { Try{con.close (); } Catch(SQLException e) {e.printstacktrace (); } } } }}
-BLOB (Binary Large Object)
-For storing large amounts of binary data
-There are some special characters, different database processing methods are not the same. The operation of large print segments is often handled in a stream manner. Instead of a normal field, you can read the data at once.
-related types in MySQL
-Tinyblob a BLOB column with a maximum length of 255 (2^8-1) bytes.
-Blob (M) a BLOB column with a maximum length of 65535 (2^16-1) bytes.
-Mediumblob a BLOB column with a maximum length of 16777215 (2^24-1) bytes.
-Longblob a BLOB column with a maximum length of 4294967295 or 4GB (2^32-1) bytes.
Example: Storing big Data in a BLOB field in db, removing the contents of a BLOB field from the DB.
Package Com.yf.jdbc.test;import Java.io.fileinputstream;import java.io.fileoutputstream;import Java.io.inputstream;import Java.sql.blob;import Java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import java.sql.SQLException;/** * Test the use of CLOB (Text large object) * @author YANGF **/ Public classDemo10 { Public Static voidMain (string[] args) {Connection con=NULL; PreparedStatement PS1=NULL; PreparedStatement PS2=NULL; ResultSet RS=NULL; Try { //Load Database driverClass.forName ("Com.mysql.jdbc.Driver"); //get Connection object Setup and database connectioncon = drivermanager.getconnection ("Jdbc:mysql://localhost:3306/testjdbc","Root","123456"); PS1= Con.preparestatement ("INSERT INTO T_user (username,headimg) VALUES (?,?)"); Ps1.setstring (1,"yangf123"); Ps1.setblob (2,NewFileInputStream ("d:/head.jpg")); Ps1.execute (); //Remove BLOB information from the databasePS2 =con.preparestatement ("SELECT * from t_user where id =?"); Ps2.setint (1,22010); RS=Ps2.executequery (); while(Rs.next ()) {blob blob= Rs.getblob ("headimg"); InputStream is=Blob.getbinarystream (); intLen =0; FileOutputStream OS=NewFileOutputStream ("d:/yangf.jpg"); while(len = is. read ())! =-1) {os.write (len); } } } Catch(Exception e) {e.printstacktrace (); } finally { if(rs! =NULL) { Try{rs.close (); } Catch(SQLException e) {e.printstacktrace (); } } if(PS1! =NULL) { Try{ps1.close (); } Catch(SQLException e) {e.printstacktrace (); } } if(Con! =NULL) { Try{con.close (); } Catch(SQLException e) {e.printstacktrace (); } } } }}
Jdbc-clob and BLOBs