mysql插入二进制文件，blob类型，遇到问题及解决办法:

首先是数据库建立要准备的：
我们要把放置二进制字段设置为Blob类型，根据文件的大小选择合适的Blob类型，一下是各个Blob类型所能容纳二进制文件的大小
MySQL的四种BLOB类型

类型         大小(单位：字节)
TinyBlob     最大 255
Blob         最大 65K
MediumBlob   最大 16M
LongBlob     最大 4G

一下是具体操作代码：
        /**
         *
         * 把二进制文件(该二进制文件可以是本地硬盘路径，也可以是一个网络路径)存入数据库
         *  create date:2009-5-13 author:Administrator
         *
         * @param file
         *            可以是本地文件也可以是网络文件
         * @param conn
         */
        public void saveBinary(String file, Connection conn) {

                // 注意二进制文件写入数据库时所用到的类，以及类包装转换过程
                File f = null;
                if (file.toLowerCase().contains("http:"))
                        f = DownLoadWithUrl.downLoadFile(file);
                else
                        f = new File(file);
                if (f != null) {
                        try {
                                InputStream is = new FileInputStream(f);
                                PreparedStatement ps = conn
                                                .prepareStatement("insert into bankVoice(name,text) values (?,?)");
                                ps.setString(1, file);
                                int i = is.available();
                                ps.setBinaryStream(2, is, is.available());
                                ps.executeUpdate();
                                System.out.println("二进制文件插入成功");

                                ps.clearParameters();
                                ps.close();
                                is.close();

                        } catch (Exception e) {
                                e.printStackTrace();
                                System.out.println("二进制文件插入时出现异常");
                        }
                }

        }
注意在操作时候会出现以下异常，那么我们只需做一下设置：以我本地为例：进入D:\MySql5.0\mysql- 5.0.51b-win32 目录，有以下文件可以看到：my-large.ini、my-small.ini、my-medium.ini、my-huge.ini
我们把只需把mysql服务现在加载的ini文件中的配置项：max_allowed_packet 改为 16M
即是：max_allowed_packet = 16M  默认的是1M我们改为16M，然后重启mysql服务器，这样就不会出现下面的异常了。

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1048587 > 1047552). You can change this value on the server by setting the max_allowed_packet' variable.
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2632)
        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2618)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1551)
        at com.mysql.jdbc.ServerPreparedStatement.storeStream(ServerPreparedStatement.java:2180)
        at com.mysql.jdbc.ServerPreparedStatement.serverLongData(ServerPreparedStatement.java:1199)
        at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1004)
        at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:670)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1159)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1076)
        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1061)
        at SaveBinaryToDB.SaveBinaryToDB.saveBinary(SaveBinaryToDB.java:33)
        at SaveBinaryToDB.SaveBinaryToDB.main(SaveBinaryToDB.java:17)

        /**
         * 从数据库中读取二进制文件 create date:2009-5-13 author:Administrator
         *
         * @param file
         * @param conn
         */
        public void getBinary(String file, Connection conn) {

                // 注意二进制文件从数据库中读取时所用到的类，以及类的包装转换过程
                try {
                        PreparedStatement ps = conn
                                        .prepareStatement("select text from bankVoice where name=?");
                        ps.setString(1, file);
                        Blob blob = null;
                        ResultSet rs = ps.executeQuery();
                        if (rs.next()) {
                                blob = (Blob) rs.getBlob("text");
                        }
                        FileOutputStream fos = new FileOutputStream("D:\\test1.mp3");
                        fos.write(blob.getBytes(1, (int) blob.length()));
                        System.out.println("二进制文件获得成功");

                        ps.clearParameters();
                        ps.close();
                        fos.close();

                } catch (Exception e) {
                        e.printStackTrace();
                        System.out.println("二进制文件读取时出现异常");
                }
        }

package SaveBinaryToDB;

/**
 * 本程序的功能实现网络下载
 * 把指定url的文件下载到本地硬盘
 *
 */
import java.io.*;
import java.net.*;

/**
 * @todo 将网上获取的图像,mp3等文件存储到本地
 *
 * @version 1.0
 */

public class DownLoadWithUrl {

        public static File downLoadFile(String fromUrl) {
                URL url;
                File file = null;
                try {
                        // url = new
                        // URL("http://count.koubei.com/showphone/showphone.php?f=jpg&w=96&h=10&bc=255,255,255&fc=0,0,0&fs=10&fn=arial&phone=NzMwNzIyNTE1#6aWCXtTNZYkxASrj ");
                        url = new URL(fromUrl);
                        URLConnection uc = url.openConnection();
                        InputStream is = uc.getInputStream();

                        // 根据下载文件类型的不同，进行相应的文件命名
                        file = new File("D:\\forever.mp3");
                        FileOutputStream out = new FileOutputStream(file);

                        /*
                         * 该注释内的也是一种写入文件的方法，不过通常下载mp3或者比mp3更小图片
                         * 等这些文件用这种带缓冲的方法写文件比较慢，所以说小文件下载通常用下面 的写文件方法就可以了 // byte[] b = new
                         * byte[102400*3]; // int size = 0; // // while ((size = is.read(b)) !=
                         * -1) { // out.write(b, 1, size); // // }
                         */

                        int i = 0;
                        while ((i = is.read()) != -1) {
                                out.write(i);
                        }
                        out.flush();

                        is.close();
                } catch (Exception e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                }

                return file;

        }

        /**
         * 删除本地磁盘指定路径的文件 create date:2009-5-13 author:Administrator
         *
         * @param file
         */
        public static void delFile(String file) {
                File f = new File(file);
                if (f.exists())
                        f.delete();
                System.out.println(file "已经被删除");
        }

        public static void main(String[] args) {
                // delFile("D:\\forever.mp3");
                downLoadFile("");
        }
}

评论 (0) All

登陆 还没注册？