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.