- import java.io.*;
- import java.sql.*;
- /**
- * 测试操作blob数据
- */
- public class BlobTest {
- Connection con = null;
- private static String filepath = "d:/test/smile.jpg";
- private static String fileoutpath = "d:/test/smilecopy.jpg";
- /**
- * 往数据库中添加BLOB数据
- */
- public void addBlob() {
- try {
- // 创建一个PreparedStatement实例
- PreparedStatement pstmt = con
- .prepareStatement("insert into blobtest values(1,?)");
- File file = new File(filepath);
- FileInputStream fis = new FileInputStream(file);
- // 把输入流设置为预处理语句的对象。
- pstmt.setBinaryStream(1, fis, (int) file.length());
- // 执行更新
- pstmt.executeUpdate();
- pstmt.close();
- fis.close();
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- /**
- * 创建一个表
- */
- public void createTable() {
- try {
- con.createStatement().execute(
- "create table blobtest (id int ,pic blob,"
- + "constraint pk_blobtest primary key(id));");
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- /**
- * 从数据库中读取BLOB数据
- */
- public void getBlob() {
- try {
- // 创建一个Statement实例
- Statement stmt = con.createStatement();
- ResultSet rst = stmt
- .executeQuery("select * from blobtest where id=1");
- rst.next();
- // 获得blob数据和它的输入流,然后通过输入流把数据写到文件中。
- Blob blob = rst.getBlob("pic");
- FileOutputStream out = new FileOutputStream(new File(
- fileoutpath));
- InputStream in = blob.getBinaryStream();
- int i;
- while ((i = in.read()) != -1)
- out.write(i);
- // 关闭输入、输出流.
- in.close();
- out.close();
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- public static void main(String[] args) throws Exception {
- Class.forName("com.mysql.jdbc.Driver").newInstance();
- BlobTest test = new BlobTest();
- test.con = java.sql.DriverManager.getConnection(
- "jdbc:mysql://localhost:3306/test", "root", "123456");
- test.createTable();
- test.addBlob();
- test.getBlob();
- }
- }
MySQL有四种BLOB类型:
·tinyblob:仅255个字符
·blob:最大限制到65K字节
·mediumblob:限制到16M字节
·longblob:可达4GB