JDBC: Database operations: BLOB data processing

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.