我们可以使用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

// 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):

Answer 2

您在写入outputstream时遇到问题，应该是这样的：

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

你的照片应该以水滴的形式保存在数据库中

另外，你的缓冲区是无用的，因为它的大小是1，你应该使它至少为512或1024，以避免逐字节读取

希望有帮助

Answer 3

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

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

Answer 4

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

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

比如：

int x;

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

fos.flush();

（编辑-来自对另一个答案的评论…）

有关不插入图像的insert语句，请参见this link

/*

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();
    }
  }
}