我们可以使用java从MySql数据库中获取添加的图像吗？

Question

我已将图像添加到 MySql 数据库中。我试图通过使用 java 创建一个新文件来从数据库中获取图像。问题是一切顺利，图像文件已创建，但图像文件不包含图像，它什么都没有，并且新创建的图像文件的大小与原始图像文件的大小不同...... Sql 代码：

CREATE TABLE `pictures` ( `id` int(11) NOT NULL auto_increment, `description` varchar(20) default NULL, `photo` blob, PRIMARY KEY (`id`) );
insert into pictures values('1','pic1','D:\java.jpg');

和 java 代码：

public class ReadBlobPicture
{
    static String url = "jdbc:mysql://localhost:3306/imgdatabase";
    static String username = "root";
    static String password = "tiger";

    public static void main(String[] args) 
        throws Exception 
    {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, username, password);
        String sql = "SELECT photo FROM pictures where id=1";
        Statement stmt = conn.prepareStatement(sql);
        ResultSet resultSet = stmt.executeQuery(sql);
        Object obj = resultSet;

        while (resultSet.next()) 
        {
            File image = new File("E:\\java12.jpg");
            FileOutputStream fos = new FileOutputStream(image);
            System.out.println(resultSet.getString(1));
            byte[] buffer = new byte[1];
            InputStream is2 = resultSet.getBinaryStream(1);
            System.out.println(is2.available());

            while (is2.read() > 0) 
            {
                fos.write(buffer);
                fos.flush();
            }

            fos.close();
        }

        conn.close();
    }
}

Answer 1

您从流中读取，但从未将读取的字符保存到缓冲区中...

while (is2.read() > 0) 
{
    fos.write(buffer);
    fos.flush();
}

类似：（

int x;

while((x = is2.read()) != -1)
{
   fos.write(x);
}

fos.flush();

编辑 - 来自另一个答案的评论...）

对于不插入图像的插入语句，请参阅 此链接

/*

Defining the Table: Oracle and MySql

create table MyPictures (
   id INT PRIMARY KEY,
   name VARCHAR(0),
   photo BLOB
);
*/
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertPictureToMySql {
  public static void main(String[] args) throws Exception, IOException, SQLException {
    Class.forName("org.gjt.mm.mysql.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/databaseName", "root", "root");
    String INSERT_PICTURE = "insert into MyPictures(id, name, photo) values (?, ?, ?)";

    FileInputStream fis = null;
    PreparedStatement ps = null;
    try {
      conn.setAutoCommit(false);
      File file = new File("myPhoto.png");
      fis = new FileInputStream(file);
      ps = conn.prepareStatement(INSERT_PICTURE);
      ps.setString(1, "001");
      ps.setString(2, "name");
      ps.setBinaryStream(3, fis, (int) file.length());
      ps.executeUpdate();
      conn.commit();
    } finally {
      ps.close();
      fis.close();
    }
  }
}

Answer 2

你在写入输出流时遇到问题，应该是这样的：

while ((buffer[0] = is2.read()) > 0) 
            {
                fos.write(buffer[0]);
                fos.flush();
            }

并且你的图片应该作为 BLOB 保存在数据库中！

PS 你的缓冲区是无用的，因为它的大小为 1，你应该将其设置为至少 512 或 1024 以避免逐字节读取。

希望有帮助。

Answer 3

insert into pictures values('1','pic1','D:\java.jpg');

不会将二进制 jpg 数据插入数据库，而是插入字符串 D:\java.jpg

Answer 4

// create a file called MySqlInsertBlob.java
//==========import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Blob;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.*;

class MySqlInsertBlob {
FileOutputStream image;
Connection conn = null;
PreparedStatement pstmt = null;
Statement stmt= null;
ResultSet res = null;
StringBuffer query=null;
String filename ="c:/backblue.gif";

public MySqlInsertBlob(){

try{

query=new StringBuffer("insert into blobs(filename,binarydata,name) values (?,?,?)");
File file= new File(filename);

Class.forName("com.mysql.jdbc.Driver")…
conn = DriverManager.getConnection("jdbc:mysql:…
stmt=conn.createStatement();
pstmt=conn.prepareStatement(query.toSt…
pstmt.setString(1, filename);
pstmt.setBinaryStream(2, new FileInputStream(filename),(int)file.leng…
pstmt.setString(3,"silviya");
pstmt.executeUpdate();
System.out.println("Successfully inserted into BLOBS .....");

ResultSet rs=stmt.executeQuery("select * from blobs where name='silviya'");
if (rs.next()) {
Blob test=rs.getBlob("binarydata");
InputStream x=test.getBinaryStream();
int size=x.available();
OutputStream out=new FileOutputStream("c:/xyz.gif");
byte b[]= new byte[size];
x.read(b);
out.write(b);


}
}catch(ClassNotFoundException cnfe){

System.out.println("ClassNotFoundExcep… occured :"+cnfe);
}catch(SQLException se){

System.out.println("SQLException occured :"+se);
}catch(FileNotFoundException fe){

System.out.println("File Not Found Exception occured :"+fe);
}catch(Exception e){

System.out.println("Exception occured :"+e);
}finally{
try{
stmt.close();
conn.close();
}catch(Exception e){}
}
Source(s):