BLOb：无法读取所有数据，但kb很少(BLOb: cannot read all data,but few kb)

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

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.这是错误吗？

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){} }

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

最满意答案

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

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

我认为在您的情况下将max_allowed_packet属性设置为更高的值将解决问题。

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

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

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

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.

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 )

更多推荐