使用mysql的Blob字段存取图片

2019独角兽企业重金招聘Python工程师标准>>>

只是做实验，没有考虑buffer存取和性能。

建立表：

CREATE TABLE example (name VARCHAR(100),city VARCHAR(100),image BLOB,Phone VARCHAR(100));

使用一张图片qqq.jpg

存图片的代码：

package test;

import java.sql.*;
import java.io.*;

public class SaveImageToDatabase {
	public static void main(String[] args) throws SQLException {
		// declare a connection by using Connection interface
		Connection connection = null;
		/*
		 * Create string of connection url within specified format with machine
		 * name, port number and database name. Here machine name id localhost
		 * and database name is mahendra.
		 */
		String connectionURL = "jdbc:mysql://localhost:3306/test";
		/*
		 * declare a resultSet that works as a table resulted by execute a
		 * specified sql query.
		 */
		ResultSet rs = null;
		// Declare prepare statement.
		PreparedStatement psmnt = null;
		// declare FileInputStream object to store binary stream of given image.
		FileInputStream fis;
		try {
			// Load JDBC driver "com.mysql.jdbc.Driver"
			Class.forName("com.mysql.jdbc.Driver").newInstance();

			/*
			 * Create a connection by using getConnection() method that takes
			 * parameters of string type connection url, user name and password
			 * to connect to database.
			 */
			connection = DriverManager.getConnection(connectionURL, "root",
					"root");
			// create a file object for image by specifying full path of image
			// as parameter.
			File image = new File("D:/qqq.jpg");
			/*
			 * prepareStatement() is used for create statement object that is
			 * used for sending sql statements to the specified database.
			 */
			psmnt = connection
					.prepareStatement("insert into example(name, city, image, Phone) "
							+ "values(?,?,?,?)");
			psmnt.setString(1, "michael");
			psmnt.setString(2, "Delhi");
			psmnt.setString(4, "123456");
			fis = new FileInputStream(image);
			psmnt.setBinaryStream(3, (InputStream) fis, (int) (image.length()));
			/*
			 * executeUpdate() method execute specified sql query. Here this
			 * query insert data and image from specified address.
			 */
			int s = psmnt.executeUpdate();
			if (s > 0) {
				System.out.println("Uploaded successfully !");
			} else {
				System.out.println("unsucessfull to upload image.");
			}
		}

		// catch if found any exception during rum time.
		catch (Exception ex) {
			System.out.println("Found some error : " + ex);
		} finally {
			// close all the connections.
			connection.close();
			psmnt.close();
		}
	}
}

将图片从数据库中取出来，放到文件中：

package test;

import java.sql.*;
import java.io.*;

public class GetImageFromDatabase {
	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		String connectionURL = "jdbc:mysql://localhost:3306/test";
		ResultSet rs = null;
		PreparedStatement psmnt = null;
		// declare FileInputStream object to store binary stream of given image.
		FileOutputStream fos;
		try {
			File imageout = new File("D:/bbb.jpg");
			fos=new FileOutputStream(imageout);
			
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(connectionURL, "root",
					"root");
			psmnt = connection
					.prepareStatement("select image from example where name=? ");
			psmnt.setString(1, "michael");
			rs=psmnt.executeQuery();
			rs.next();
			Blob image_blob=rs.getBlob("image");
			InputStream is=image_blob.getBinaryStream();
			
			 int ch = 0;  
			    try {  
			        while((ch=is.read()) != -1){  
			            fos.write(ch);  
			        }  
			    } catch (IOException e1) {  
			        e1.printStackTrace();  
			    } finally{  
			                  
			        fos.close();  
			        is.close();  
			    }
		}
		// catch if found any exception during rum time.
		catch (Exception ex) {
			System.out.println("Found some error : " + ex);
		} finally {
			// close all the connections.
			connection.close();
			psmnt.close();
		}
	}
}

转载于:https://my.oschina.net/forrest420/blog/85705