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