cannot save image as blob to sqlite

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

File file = new File(url);
  try {
    fis = new FileInput开发者_如何转开发Stream(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.

Any ideas? Thanks

---

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

You'll need to use the setBytes method instead.

---

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.

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

and here is my dbconfig.properties file

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

Try using this code.

---

To use it with Hibernate you must use a proper 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:

@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.

---

Have submitted a patch to Xerial containing the implementation of setBinaryStream.

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

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

---

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