Jdbc-clob and BLOBs

-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