BLOb：不能读取所有数据，但少数kb [英] BLOb: cannot read all data,but few kb

问题描述

我使用BLOb支持插入和读取从MySQl。（JDBC）
我可以这样做，但当它读取，它只有几个kb。我不知道为什么。
这里是工作代码：

I am using BLOb support for inserting and reading from MySQl.(JDBC) I can do that,but when it reads,its only few kb's.I dont know why. here is the working code:

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

public class InsertAndRetrieveImage {

 public static void main(String[] args) throws SQLException, FileNotFoundException, IOException {
  int id=7;
String connectionURL = "jdbc:mysql://127.0.0.1:3306/newdb";;
Connection con=null;
try{
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(connectionURL, "root", "sesame");

 PreparedStatement ps = null;

 ps=con.prepareStatement("INSERT IGNORE INTO image VALUES(?,?,?)");

         File file = new File("e:/anarkali.wav");


         FileInputStream fs = new FileInputStream(file);

     try{
        System.out.println(fs.available());
         ps.setInt(1,id);
         ps.setBinaryStream(2,fs,fs.available());
         String filen=file.getName();
      ps.setString(3,filen);


      int i = ps.executeUpdate();



        String filename="filename";OutputStream os=null;byte[] content=null;
        ps= con.prepareStatement("SELECT fs from image where id=7");
        ResultSet rs = ps.executeQuery();
         System.out.println(rs);
      while(rs.next()) {


            Blob blob = rs.getBlob("fs");

        content = blob.getBytes(1, (int) blob.length());


        os = new FileOutputStream(new File("e://testanar.wav"));

    }
    os.write(content);
    os.close();
    con.close();
    ps.close();}
     catch(SQLException e)
     {System.out.println(e.getMessage());
     }

  }catch(Exception ex){}


  }

读取或写入时出现问题？我的BLOb的大小有点 65535 。这是错误？

Is the problem while reading or writing?my BLOb's size is somewhat 65535.Is this is the bug?

推荐答案

您的程序是正确的。如果文件大小很小，那么它会工作正常。如果您使用 BLOB 数据类型来存储该二进制数据，请将其更改为 LONGBLOB 。因为如果您尝试在 BLOB 数据字段中保存文件，并且如果文件大小超过BLOB数据类型的最大允许大小，则内容将被截断，您将丢失一些文件内容（取决于文件大小）。即使您使用 LONGBLOB 数据类型，还是有一些必须检查的事情。我将解释

Your program is correct. If the file size is small then it will work fine. If you are using BLOB datatype to store that binary data,change it to LONGBLOB. Because if you tried to save a file in BLOB data field, and if the file size is more than max allowed size of BLOB datatype then the content will be truncated and you will loss some content of file(depends on file size). Even if you use LONGBLOB datatype there are some things you must check. I'll explain

在mysql网站中，我们可以看到 LONGBLOB 的最大文件大小是4GB。但它取决于许多事情。要存储大文件，你必须检查一些东西。第一件事是在 my.ini 文件中，有一个名为 max_allowed_pa​​cket 的属性，它指定了最大可能的包传输到或从一个mysql客户端或服务器。您应该将 max_allowed_pa​​cket 属性设置为更高的值。并重新启动mysql。某些文件系统将不允许4GB文件。在这种情况下，你无法加载那么多的大文件。

In mysql website we can see that max file size of LONGBLOB is 4GB. But it depends on many things. To store large file you have to check some things. First thing is in my.ini file, there is a property called max_allowed_packet, it specifies largest possible packet that can be transmitted to or from a mysql client or server. You should set max_allowed_packet property to a higher value. And restart mysql. Some file system won't allow 4GB files. In that case you cant load that much big file.

我认为在你的情况下将 max_allowed_pa​​cket 属性设置为更高的值将解决这个问题。

I think in your case setting max_allowed_packet property to a higher value will fix the problem.

我想你可以使用下面的表创建脚本

I think you can use following table creation script

CREATE TABLE `image` (
    `id` INT(10) NULL DEFAULT NULL,
    `fs` LONGBLOB NULL,
    `filen` VARCHAR(50) NULL DEFAULT NULL
)

这篇关于BLOb：不能读取所有数据，但少数kb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！