029_jdbc-mysql二进制数据

1. MySQL支持的二进制数据类型:tinyblob, blob, mediumblob和longblob。

2. JDBC中我们用PrepareStatement的SetBlob和GetBlob获取和设置二进制数据。

3. 新建一个JDBC_BLOB工程, 使用我们之前的JDBCUtil.java和jdbc.properties属性文件, 同时在Resources目录下存放一个图片

4. 创建BlobDao.java接口

package com.lywgames.dao;

public interface BlobDao {
	public void createTable();
	
	public void insert(String filePath);
	
	public void findAll();
	
	public void deleteTable();
}

5. 创建BlobDaoImpl.java来对二进制数据进行存储和获取

package com.lywgames.dao.impl;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.lywgames.dao.BlobDao;
import com.lywgames.util.JDBCUtil;

public class BlobDaoImpl implements BlobDao {
	@Override
	public void createTable() {
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement("create table photos(id int(11) not null auto_increment, photo blob, primary key(id))");
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}
	}

	@Override
	public void insert(String filePath) {
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement("insert into photos values(null, ?)");
			ps.setBlob(1, new FileInputStream(filePath));
			ps.executeUpdate();
		} catch (SQLException | IOException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}
	}

	@Override
	public void findAll() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement("select photo from photos");
			rs = ps.executeQuery();
			int length = -1;
			byte[] buffer = new byte[1024 * 10];
			while(rs.next()) {
				Blob blob = rs.getBlob(1);
				InputStream is = blob.getBinaryStream();
				FileOutputStream fos = new FileOutputStream("Resources/" + System.currentTimeMillis() + ".jpg");
				while((length = is.read(buffer, 0, buffer.length)) != -1) {
					fos.write(buffer, 0, length);
				}
				fos.flush();
				fos.close();
				is.close();
			}
		} catch (SQLException | IOException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps, rs);
		}
	}

	@Override
	public void deleteTable() {
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			conn = JDBCUtil.getConn();
			ps = conn.prepareStatement("drop table photos");
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(conn, ps);
		}
	}
}

6. 使用BlobDaoImpl类

package com.lywgames.myjdbc;

import com.lywgames.dao.BlobDao;
import com.lywgames.dao.impl.BlobDaoImpl;

public class MyJDBC {
	public static void main(String[] args) {
		BlobDao blobDao = new BlobDaoImpl();
		blobDao.createTable();
		blobDao.insert("Resources/1.jpg");
		blobDao.findAll();
		blobDao.deleteTable();
	}
}

7. 运行项目在Resources目录下多了一个图片。注: eclipse里查看图片不正常, 尝试去文件系统里查看。