参考网址：http://www.cnblogs.com/jway1101/p/5815658.html

首先是建表语句，需要实现将表建立好。

CREATE TABLE `blobtest` (

  `primary_id` varchar(32) NOT NULL,

  `bank_id` varchar(32) NOT NULL,

  `bank_name` varchar(64) NOT NULL,

  `blob_data` blob NOT NULL,

  PRIMARY KEY (`primary_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Blob类型数据存取测试用表'

然后是数据库连接的工具类：

package blobtest;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

public class JDBCUtil {

    /*使用静态代码块完成驱动的加载*/

    static {

        try {

            String driverName = "com.mysql.jdbc.Driver";

            Class.forName(driverName);

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

    /*提供连接的方法*/

    public static Connection getConnection() {

        Connection con = null;

        try {

            //连接指定的MMySQL数据库，三个参数分别是：数据库地址、账号、密码

            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test?useUnicode=true&characterEncoding=utf8", "root", "w513723");

        } catch (Exception e) {

            e.printStackTrace();

        }

        return con;

    }

    /*关闭连接的方法*/

    public static void close(ResultSet rs, Statement stmt, Connection con) {

        try {

            if (rs != null)

                rs.close();

        } catch (Exception ex) {

            ex.printStackTrace();

        }

        try {

            if (stmt != null)

                stmt.close();

        } catch (Exception ex) {

            ex.printStackTrace();

        }

        try {

            if (con != null)

                con.close();

        } catch (Exception ex) {

            ex.printStackTrace();

        }

    }

}

接下来是真正的插入数据库的java代码：

package blobtest;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class ReadWriteBlobwithMysql

{

    private Connection con;

    private Statement stmt;

    public Statement getStmt()

    {

        return stmt;

    }

    public void setStmt(Statement stmt)

    {

        this.stmt = stmt;

    }

    public ResultSet getRs()

    {

        return rs;

    }

    public void setRs(ResultSet rs)

    {

        this.rs = rs;

    }

    ResultSet rs;

    public Connection getCon()

    {

        return con;

    }

    public void setCon(Connection con)

    {

        this.con = con;

    }

    public void insert(Connection con) throws SQLException

    {

        String fileName = "E:\\JavaProject\\HelloWorld\\src\\blobtest\\test.html";

        File file = new File(fileName);

        try

        {

            FileInputStream fis = new FileInputStream(file);

            String sql = "insert into blobtest values('12','0000','平安银行',?)";

             PreparedStatement prest = con.prepareStatement(sql);

             prest.setBlob(1, fis,file.length());

             prest.execute();

        } catch (FileNotFoundException e)

        {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    public ReadWriteBlobwithMysql(Connection con)

    {

        this.setCon(con);

        try

        {

            stmt = con.createStatement();

        } catch (SQLException e)

        {

            e.printStackTrace();

        }

    }

    public static void main(String[] args)

    {

        Connection con = JDBCUtil.getConnection();

        ReadWriteBlobwithMysql dao = new ReadWriteBlobwithMysql(con);

        try

        {

//            dao.createTable();

            dao.insert(con);

        } catch (SQLException e)

        {

            e.printStackTrace();

        } finally

        {

            JDBCUtil.close(dao.getRs(), dao.getStmt(), dao.getCon());

        }

    }

}

使用select语句查看一下执行结果，第二条是使用程序插入的，其他的是使用sql语句直接插入的：

下面是增加了从数据库读取blob数据的java代码，增加了queryBlob(。。。)函数：

package blobtest;

import java.io.BufferedInputStream;

import java.io.BufferedOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

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 java.sql.Statement;

public class ReadWriteBlobwithMysql

{

    private Connection con;

    private Statement stmt;

    public Statement getStmt()

    {

        return stmt;

    }

    public void setStmt(Statement stmt)

    {

        this.stmt = stmt;

    }

    public ResultSet getRs()

    {

        return rs;

    }

    public void setRs(ResultSet rs)

    {

        this.rs = rs;

    }

    ResultSet rs;

    public Connection getCon()

    {

        return con;

    }

    public void setCon(Connection con)

    {

        this.con = con;

    }

    public void insert(Connection con) throws SQLException

    {

        String fileName = "E:\\JavaProject\\HelloWorld\\src\\blobtest\\test.html";

        File file = new File(fileName);

        try

        {

            FileInputStream fis = new FileInputStream(file);

            String sql = "insert into blobtest values('12','0000','平安银行',?)";

            PreparedStatement prest = con.prepareStatement(sql);

            prest.setBlob(1, fis, file.length());

            prest.execute();

        } catch (FileNotFoundException e)

        {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    public void queryBlob(String id, Connection con) throws IOException

    {

        String fileName = "E:\\JavaProject\\HelloWorld\\src\\blobtest\\test1.html";

        String sql = "select * from blobtest where primary_id= ?";

        try

        {

            PreparedStatement prest = con.prepareStatement(sql);

            prest.setString(1, id);

            ResultSet rs = prest.executeQuery();

            while (rs.next())

            {

                Blob bl = rs.getBlob("blob_data");// 数据保存在表的blob_data字段中，这里取出这里保存的数据。

                InputStream is = bl.getBinaryStream(); // 查看blob,可以通过流的形式取出来。  注意一定要是用流的方式读取出来

                BufferedInputStream buffis = new BufferedInputStream(is);

                // 保存到buffout

                BufferedOutputStream buffout = new BufferedOutputStream(new FileOutputStream(fileName));

                byte[] buf = new byte[1024];

                int len = buffis.read(buf, 0, 1024);

                while (len > 0)

                {

                    buffout.write(buf);

                    len = buffis.read(buf, 0, 1024);

                }

                buffout.flush();

                buffout.close();

                buffis.close();

            }

        } catch (SQLException e)

        {

            // TODO Auto-generated catch block

            e.printStackTrace();

        }

    }

    public ReadWriteBlobwithMysql(Connection con)

    {

        this.setCon(con);

        try

        {

            stmt = con.createStatement();

        } catch (SQLException e)

        {

            e.printStackTrace();

        }

    }

    public static void main(String[] args)

    {

        Connection con = JDBCUtil.getConnection();

        ReadWriteBlobwithMysql dao = new ReadWriteBlobwithMysql(con);

        try

        {

            // dao.createTable();

//            dao.insert(con);

            dao.queryBlob("12",con);

        } catch (IOException e)

        {

            e.printStackTrace();

        } finally

        {

            JDBCUtil.close(dao.getRs(), dao.getStmt(), dao.getCon());

        }

    }

}

最新文章

  1. MS SQL Could not obtain information about Windows NT group/user 'domain\login', error code 0x5. [SQLSTATE 42000] (Error 15404)
  2. OOP
  3. [Unity2D]精灵
  4. CentOS 6.4 升级 Mysq5.5l方法 和 用户远程登录数据库
  5. java.util.Arrays的使用
  6. 对springMVC的简单理解
  7. JS HTML标签尺寸距离位置定位计算
  8. 移动互联与O2O的完美衔接
  9. CMarkUp读写XML（转）
  10. 【模拟】【HDU1443】 Joseph
  11. 201521123109《java程序设计》第六周学习总结
  12. 布局神器display:flex
  13. 大型EMR电子病历源码三甲医院医疗信息管理系统软件网络版
  14. Python Learning: 02
  15. echarts 隐藏Y轴最大最小值label及分割线 ----障眼大发好使
  16. VS中展开和折叠代码，还有其他快捷操作
  17. Java_Runtime&Process&ProcessBuilder
  18. Linux下载软件
  19. tf训练OTSU
  20. [HihoCoder1369]网络流一·Ford-Fulkerson算法

热门文章

  1. LeetCode Judge Route Circle
  2. jsp中引入JavaScript的方法
  3. ajax返回
  4. 第一章计算机网络和因特网-day01
  5. 在系统学习javaEE开发的颠覆者Springboot时遇到的localhost无法访问的问题
  6. Proxmox qm命令应用实例
  7. Azure上批量创建OS Disk大于30G的Linux VM
  8. laravel csrf保护
  9. 本地dns服务器到底是什么？有没有精确的概念？
  10. java中如何将OutputStream转换为InputStream