JDBC: Database operations: BLOB data processing

Clob mainly saves huge amounts of text, while BLOBs are dedicated to storing binary data: including, pictures, music, movies. such as

In MySQL, blob types use the Longblob declaration to store up to 4G of content.

Create a table:

CREATE TABLE Userblob (    intnull  auto_increment,    name varchar (),    photo Longblob);

Code:

 Packageclass set;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.SQLException;Importjava.sql.PreparedStatement;ImportJava.io.File;ImportJava.io.FileInputStream;ImportJava.io.InputStream; Public classblobdemo01{//define a MySQL database driver     Public Static FinalString dbdriver = "Org.gjt.mm.mysql.Driver" ; //Define the connection address of the MySQL database     Public Static FinalString Dburl = "Jdbc:mysql://localhost:3306/sys" ; //connection user name for MySQL database     Public Static FinalString DBUSER = "root" ; //connection password for MySQL database     Public Static FinalString Dbpass = "AAAAAA" ;  Public Static voidMain (String args[])throwsexception{//all exceptions ThrownConnection conn =NULL;//Database Connection       PreparedStatement pstmt = null ; String name= "Xiao Hua" ; String SQL= "INSERT into Userblob (Name,photo) VALUES (?,?)" ;    Class.forName (Dbdriver); //Load Driverconn =drivermanager.getconnection (Dburl,dbuser,dbpass); pstmt = conn.preparestatement (SQL); File F         = new File ("D:" + File.separator + "Picture 1. JPG "); Picture files inputstream input =NULL ; input = new FileInputStream (f); pstmt.setstring (         1,name); Set the first "?" of the contentPstmt.Setbinarystream(2,input, (int) f.length ());// Set the input stream pstmt.executeupdate (); Update Database Pstmt.close ();            Conn.close (); //Database Shutdown    }};

Query execution Results:

The picture must not be queried, so you should read the picture and save a different file.

 Packageclass set;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.PreparedStatement;ImportJava.io.File;ImportJava.io.FileOutputStream;ImportJava.sql.ResultSet;ImportJava.io.InputStream;ImportJava.io.OutputStream; Public classblobdemo01{//define a MySQL database driver     Public Static FinalString dbdriver = "Org.gjt.mm.mysql.Driver" ; //Define the connection address of the MySQL database     Public Static FinalString Dburl = "Jdbc:mysql://localhost:3306/sys" ; //connection user name for MySQL database     Public Static FinalString DBUSER = "root" ; //connection password for MySQL database     Public Static FinalString Dbpass = "AAAAAA" ;  Public Static voidMain (String args[])throwsexception{//all exceptions ThrownConnection conn =NULL;//Database ConnectionPreparedStatement pstmt =NULL ; ResultSet rs = null; intID = 1 ; String SQL= "Select Name,photo from Userblob WHERE id=?" ;    Class.forName (Dbdriver); //Load Driverconn =drivermanager.getconnection (Dburl,dbuser,dbpass); Pstmt=conn.preparestatement (SQL); Pstmt.setint (1, id); rs = Pstmt.executequery (); Execute Query        if (Rs.next ()) {String name= rs.getstring (1) ; System.out.println ("Name:" +name); InputStream input= Rs.getbinarystream (2) ; File F = new File ("D:" + File.separator + "load picture 1.gif"); Picture file             OutputStream out = null; out = new FileOutputStream (f); inttemp = 0 ;  while((Temp=input.read ()) {!=-1) {//Read side write out.write (temp) ;            } Input.close ();        Out.close ();        } pstmt.close ();            Conn.close (); //Database Shutdown    }};

After processing, the file is found in the D folder, indicating that the read was successful.

The above program is read through the operation stream of IO

For ease of reading, Java specifically provides a BLOB class for binary file read operations.

Blob class

The Blob class provides the following methods:

The operation example code is as follows:

 Packageclass set;Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.SQLException;Importjava.sql.PreparedStatement;ImportJava.sql.Blob;ImportJava.sql.ResultSet;ImportJava.io.File;ImportJava.io.FileOutputStream;ImportJava.io.InputStream;ImportJava.io.OutputStream; Public classblobdemo01{//define a MySQL database driver     Public Static FinalString dbdriver = "Org.gjt.mm.mysql.Driver" ; //Define the connection address of the MySQL database     Public Static FinalString Dburl = "Jdbc:mysql://localhost:3306/sys" ; //connection user name for MySQL database     Public Static FinalString DBUSER = "root" ; //connection password for MySQL database     Public Static FinalString Dbpass = "AAAAAA" ;  Public Static voidMain (String args[])throwsexception{//all exceptions ThrownConnection conn =NULL;//Database ConnectionPreparedStatement pstmt =NULL ; ResultSet RS=NULL ; intID = 1 ; String SQL= "Select Name,photo from Userblob WHERE id=?" ;    Class.forName (Dbdriver); //Load Driverconn =drivermanager.getconnection (Dburl,dbuser,dbpass); Pstmt=conn.preparestatement (SQL); Pstmt.setint (1, id); RS= Pstmt.executequery ();//Execute Query        if(Rs.next ()) {String name= rs.getstring (1) ; System.out.println ("Name:" +name); Blob b = Rs.getblob (2) ; File F            = new File ("D:" + File.separator + "load picture 2.gif"); Picture file outputstream out = null; Out   = new  FileOutputStream (f); Out.write (b.getbytes (            1, (int ) b.length ()))           ;        Out.close ();        } pstmt.close ();            Conn.close (); //Database Shutdown    }};

Operation Result:

Discovery successfully read and created the file.

Using BLOBs is a bit simpler, but it is unwise to put large files in the database from a practical perspective. is often done by mapping paths.

JDBC: Database operations: BLOB data processing