java 无法将图像保存为 blob 到 sqlite

cannot save image as blob to sqlite

提问by Mara

I am using SQLite and I cannot save a image to the database. This is my code :

我正在使用 SQLite，但无法将图像保存到数据库中。这是我的代码：

File file = new File(url);
  try {
    fis = new FileInputStream(file);
  } catch (FileNotFoundException e) {}
fileLenght = (int) file.length();

stat.executeUpdate("create table "+tableName+" (id int,name String ,image Blob, features String);");
prep = conn.prepareStatement("insert into "+tableName+" values (?, ?, ?, ?);");
prep.setBinaryStream(3, fis, fileLenght);

This is the error i am getting:

这是我得到的错误：

java.sql.SQLException: not implemented by SQLite JDBC driver
  at org.sqlite.Unused.unused(Unused.java:29)
  at org.sqlite.Unused.setBinaryStream(Unused.java:58)

I am using the following jar : sqlitejdbc-v056.jar.

我正在使用以下 jar：sqlitejdbc-v056.jar。

Any ideas? Thanks

有任何想法吗？谢谢

回答by Femi

The SQLite JDBC implementation you're using doesn't implement setBinaryStream(hence the accurate not implemented by SQLite JDBC drivererror message).

您正在使用的 SQLite JDBC 实现未实现setBinaryStream（因此SQLite JDBC 驱动程序错误消息未实现准确）。

You'll need to use the setBytesmethod instead.

您需要改用该setBytes方法。

回答by Ronald

You can insert an image very easily into database say SQLite see my following code. I did used a properties file for DBase Connection that u can put anywhere across.

您可以很容易地将图像插入数据库，比如 SQLite，请参阅我的以下代码。我确实为 DBase Connection 使用了一个属性文件，你可以将它放在任何地方。

public class JDBCSqliteConn
{
    public static void main(String args[]) throws FileNotFoundException, IOException
    {
        Connection connection = null;
        //ResultSet resultSet = null;
        PreparedStatement ps = null;
        String file = "C:\Fingerprint\histoImg_med.png";
        Properties prop = new Properties();
        int s = 0;
        byte[] person_image = null;
        File image = new File(file);
        FileInputStream fis = new FileInputStream(image);
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        byte[] buf = new byte[1024];

        try {
            for (int readNum; (readNum = fis.read(buf)) != -1;)
            {
                bos.write(buf, 0, readNum);
                //no doubt here is 0
                /*Writes len bytes from the specified byte array starting at offset
                off to this byte array output stream.*/
                System.out.println("read " + readNum + " bytes,");
            }
        } catch (IOException ex) {
            System.err.println(ex.getMessage());
        }
        person_image = bos.toByteArray();

        try {
            prop.load(new FileInputStream("C:\dbconfig.properties"));  
            Class.forName(prop.getProperty("driver"));
            connection = DriverManager.getConnection(prop.getProperty("url"));

            ps = connection.prepareStatement("INSERT INTO epmc_tbl_test_img (hhld_photo) VALUES (?)");
            ps.setBytes(1, person_image);
            s = ps.executeUpdate();
            if (s > 0)
            {
                System.out.println("Image Uploaded");
            }
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                ps.close();
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

The logic behind is am converting the images to bytearray and then storing to database,SQLite accepts byte array for Image type BLOB

背后的逻辑是将图像转换为字节数组，然后存储到数据库，SQLite 接受图像类型 BLOB 的字节数组

and here is my dbconfig.properties file

这是我的 dbconfig.properties 文件

driver = org.sqlite.JDBC
url = jdbc:sqlite:C:\Ronald\Personal\epmc\JavaJ2EE\EPMC.db

Try using this code.

尝试使用此代码。

回答by Daniel Jurado

To use it with Hibernate you must use a proper UserType:

要将它与 Hibernate 一起使用，您必须使用正确的 UserType：

public class PersistentFileAsBlob implements EnhancedUserType, Serializable {

    public static final PersistentFileAsBlob INSTANCE = new PersistentFileAsBlob();

    private static final int[] SQL_TYPES = new int[] { Types.BLOB };

    @Override
    public int[] sqlTypes() {
        return SQL_TYPES;
    }

    @Override
    public Class<?> returnedClass() {
        return File.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        if (x == y) {
            return true;
        }
        if (x == null || y == null) {
            return false;
        }
        File dtx = (File) x;
        File dty = (File) y;
        return dtx.equals(dty);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)
            throws HibernateException, SQLException {
        if (owner == null) {
            return null;
        }

        FileOutputStream fos = null;
        GzipCompressorInputStream cis = null;
        try {
            File file = File.createTempFile(String.valueOf(owner.hashCode()),
                    "");
            byte[] bytes = rs.getBytes(names[0]);
            System.out.println(bytes.length);
            fos = new FileOutputStream(file);
            cis = new GzipCompressorInputStream(new BufferedInputStream(
                    new ByteArrayInputStream(bytes)));
            int n = 0;
            final byte[] buffer = new byte[1024];
            while (-1 != (n = cis.read(buffer))) {
                fos.write(buffer, 0, n);
            }
            fos.close();
            return file;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            if (fos != null)
                try {
                    fos.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            if (cis != null)
                try {
                    cis.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
        }
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index)
            throws HibernateException, SQLException {
        if (value == null) {
            StandardBasicTypes.BINARY.nullSafeSet(st, null, index);
            return;
        }

        File file = (File) value;

        FileInputStream fis = null;
        ByteArrayOutputStream bos = null;
        GzipCompressorOutputStream cos = null;
        try {
            fis = new FileInputStream(file);
            bos = new ByteArrayOutputStream();
            cos = new GzipCompressorOutputStream(new BufferedOutputStream(bos));
            int n = 0;
            final byte[] buffer = new byte[1024];
            while (-1 != (n = fis.read(buffer))) {
                cos.write(buffer, 0, n);
            }
            cos.close();
            StandardBasicTypes.BINARY.nullSafeSet(st, bos.toByteArray(), index);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            if (fis != null)
                try {
                    fis.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
            if (cos != null)
                try {
                    cos.close();
                } catch (IOException e) {
                    throw new RuntimeException(e);
                }
        }
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return value;
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) value;
    }

    @Override
    public Object assemble(Serializable cached, Object owner)
            throws HibernateException {
        return cached;
    }

    @Override
    public Object replace(Object original, Object target, Object owner)
            throws HibernateException {
        return original;
    }

    @Override
    public String objectToSQLString(Object value) {
        throw new UnsupportedOperationException();
    }

    @Override
    public String toXMLString(Object value) {
        return value.toString();
    }

    @Override
    public Object fromXMLString(String xmlValue) {
        return new File(xmlValue);
    }

}

Then you annotate you type, referring to this UserType:

然后你注释你的类型，指的是这个 UserType：

@Entity
public class Mensagem {

    @Column(nullable = false)
    @Type(type = "package.to.class.PersistentFileAsBlob")
    private File file;

}

As you can see I'm compressing data before saving and decompressing before retrieval. Take a look at this site.

如您所见，我在保存和解压缩之前压缩数据，然后再检索。看看这个网站。

回答by stringaling

Have submitteda patch to Xerial containing the implementation of setBinaryStream.

是否有提交补丁Xerial包含的setBinaryStream的实施。

Using setBytes wasn't an option for me, as I am using hibernate to access the database.

使用 setBytes 不是我的选择，因为我使用休眠来访问数据库。

diff -r 144ade82d1fe -r 5a141e1b82f0 src/main/java/org/sqlite/PrepStmt.java
--- a/src/main/java/org/sqlite/PrepStmt.java        Thu Jun 09 17:15:36 2011 +0900
+++ b/src/main/java/org/sqlite/PrepStmt.java        Wed Oct 26 11:02:15 2011 -0700
@@ -16,7 +16,9 @@

 package org.sqlite;

+import java.io.ByteArrayOutputStream;
 import java.io.IOException;
+import java.io.InputStream;
 import java.io.Reader;
 import java.sql.Date;
 import java.sql.ParameterMetaData;
@@ -254,6 +256,38 @@
         batch(pos, value);
     }   

+    @Override
+    public void setBinaryStream(int pos, InputStream istream, int length) throws SQLException
+    {
+      ByteArrayOutputStream baos = new ByteArrayOutputStream();
+      try 
+      {
+        int bval = 0;
+   
+        while ((bval = istream.read()) != -1)
+        {   
+          baos.write(bval);
+        }   
+        baos.flush();
+        setBytes(pos, baos.toByteArray());
+      }   
+      catch (IOException e)
+      {
+        throw new SQLException("Cannot read from binary stream, exception message: " + e.getMessage());
+      }
+      finally
+      {
+        try 
+        {   
+          baos.close();
+        }   
+        catch (IOException e)
+        {   
+          throw new SQLException("Can't close stream");
+        }   
+      }
+    }
+   
     public void setCharacterStream(int pos, Reader reader, int length) throws SQLException {
         try {
             // copy chars from reader to StringBuffer

回答by Ted Hopp

It appears that you are not binding to all four parameters in your statement.

您似乎没有绑定到语句中的所有四个参数。