Implement to store or extract picture files from MYSQL database
In some cases, you need to store some binary files in the database, such as picture files, etc., at this time, storing data in the database is different from ordinary string storage, we need to use JAVA to deal with the binary stream API, and then store it. We need to take the following steps to achieve:
Use standard SQL statements, such as insert into database (column1, column2,..), when storing files in a database Values (v1Jing v2, …) Note that when you create a TABLE that holds binary files, the fields you store should use the BLOB type instead of the normal VARCHAR, and so on. BLOB is a type that specializes in storing binary files. It can also be divided into sizes, such as mediablob,logblob, to store binary files of different sizes. Mediablob is enough for general graphics files.
1 see the following code to store image files in MYSQL:
...
Private final String insertquery = "insert into employeephoto (Employee_ID,Binary_Photo,LastMod,Created) values (?, NOW (), NOW ())"
Public void doInsertStaffPic (String loginname,String source_URL) {
Connection conn = null
PreparedStatement pre = null
Try {
/ / make a database connection. Here I use the connection pool configured in STRUTS. Of course, I can also connect directly through JDBC myself.
Conn = DBProcess.getConnection ()
/ / get the picture object from the image source and write it to the cache
Image image = new ImageIcon (source_URL). GetImage ()
BufferedImage bImage = new BufferedImage (image.getWidth (null))
Image.getHeight (null), BufferedImage.TYPE_INT_RGB)
Graphics bg = bImage.getGraphics ()
Bg.drawImage (image, 0,0, null)
Bg.dispose ()
/ / write the picture to the binary output stream and put it into byte [] buf
ByteArrayOutputStream out = new ByteArrayOutputStream ()
ImageIO.write (bImage, "jpg", out)
Byte [] buf = out.toByteArray ()
/ / get the output stream and set it to BLOB
ByteArrayInputStream inStream = new ByteArrayInputStream (buf)
Pre = conn.prepareStatement (insertstaffpicquery)
Pre.setString (1, loginname)
Pre.setBinaryStream (2, inStream, inStream.available ())
/ / execute write such as data
Pre.executeUpdate ()
} catch (Exception exc) {
Exc.printStackTrace ()
}
Finally {
Try {
Pre.close ()
Conn.close ()
} catch (SQLException e) {
E.printStackTrace ()
}
}
}
2 the following code realizes that the image file is obtained from MYSQL and written to the local file system:
...
Private final String writeoutquery = "insert into employeephoto (Employee_ID,Binary_Photo,LastMod,Created) values (?, NOW (), NOW ())"
/ / retrive the picture data from database and write it to the local disk
Public void doGetAndShowStaffPic (String loginname, String dir) {
FileOutputStream output = null
InputStream input = null
Connection conn = null
ResultSet rs = null
PreparedStatement pre = null
Try {
Conn = DBProcess.getConnection ()
Pre = conn.prepareStatement (writeoutquery)
Pre.setString (1, loginname)
Rs = pre.executeQuery ()
If (rs.next ()) {
/ / get binary file data from the database
Blob image = rs.getBlob ("Binary_Photo")
/ / setup the streams
Input = image.getBinaryStream ()
Try {
/ / set the write-out path.
Output = new FileOutputStream (dir)
} catch (FileNotFoundException E1) {
E1.printStackTrace ()
}
/ / set read buffer size be careful not to set it too small, if it is too small, the picture may be incomplete
Byte [] rb = new byte [1024000]
Int ch = 0
/ / process blob
Try {
/ / write to the local file system
While ((ch = input.read (rb))! =-1) {
Output.write (rb, 0, ch)
}
} catch (IOException e) {
E.printStackTrace ()
}
Try {
Input.close ()
} catch (IOException e) {
E.printStackTrace ()
}
Try {
Output.close ()
} catch (IOException e) {
E.printStackTrace ()
}
}
} catch (SQLException e) {
E.printStackTrace ()
}
Finally {
Try {
Rs.close ()
Pre.close ()
Conn.close ()
} catch (SQLException e) {
E.printStackTrace ()
}
}
}
[@ more@]
