Write object as blob via BinaryStream in Java SQLite update - NOT NULL constraint violation

Question

I'm trying to write an object into a database as a blob, but I'm getting java.sql.SQLException: [SQLITE_CONSTRAINT] Abort due to constraint violation (NOT NULL constraint failed: table_name.blob).

Initially, I tried creating the blob as a SerialBlob and setting it on a PreparedStatement with setBlob, but I got java.sql.SQLException: not implemented by SQLite JDBC driver. So, guided by this answer, I tried to create an inputStream for the object, and set that as the binary stream.

I don't know why the database believes that the constraint is being violated - in the tests below, testStreamContentNotNull passes, but testDatabaseWrite throws the aforementioned SQLException.

import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.Assert.fail;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;

import org.junit.Test;

public class StackOverflowTest {

    private static final String testStringToBeWrittenAsBlob = "abcdef";

    @Test
    public void testStreamContentNotNull() {
        try {
            try (ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
                try (ObjectOutputStream oos = new ObjectOutputStream(bos)) {
                    oos.writeObject(testStringToBeWrittenAsBlob);
                    oos.flush();
                    oos.close();
                    try (ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray())) {
                        try (ObjectInputStream ois = new ObjectInputStream(bis)) {
                            assertThat((String)ois.readObject()).isEqualTo(testStringToBeWrittenAsBlob);
                        }
                    }
                }
            }
        } catch (Exception e) {
            fail();
        }
    }

    @Test
    public void testDatabaseWrite() {
        Connection c = null;
        Statement stmt = null;
        String sql = null;

        try {
            // Initialize the connection
            Class.forName("org.sqlite.JDBC");
            c = DriverManager.getConnection("jdbc:sqlite::memory:");
            c.setAutoCommit(false);

            // Create the table
            stmt = c.createStatement();
            sql = "CREATE TABLE table_name " +
                    "(id INT PRIMARY KEY NOT NULL," +
                    "blob BLOB NOT NULL)";
            stmt.executeUpdate(sql);
            c.commit();
            stmt.close();

            // Table has been created - now write to it

            sql = "INSERT INTO table_name (id, blob) VALUES (?, ?)";
            PreparedStatement p_stmt = c.prepareStatement(sql);
            try(ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
                try(ObjectOutputStream oos = new ObjectOutputStream(bos)) {
                    oos.writeObject(testStringToBeWrittenAsBlob);
                    oos.flush();
                    oos.close();
                    try (ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray())) {
                        try (ObjectInputStream ois = new ObjectInputStream(bis)) {
                            p_stmt.setString(1, "test-id");
                            p_stmt.setBinaryStream(2, ois);
                            p_stmt.execute(); // <--- This is where the exception is thrown
                            c.commit();
                        }
                    }

                }
            }


            c.close();


        } catch (Exception e) {
            fail();
        }
    }
}

Answer 1

For anyone who finds this question, thanks to this post I got it to work by just reading from the ByteArrayOutputStream::toBytes - i.e. the following test passes:

@Test
public void testDatabaseWriteAsBytes() {
    Connection c = null;
    Statement stmt = null;
    String sql = null;

    try {
        // Initialize the connection
        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite::memory:");
        c.setAutoCommit(false);

        // Create the table
        stmt = c.createStatement();
        sql = "CREATE TABLE table_name " +
                "(id INT PRIMARY KEY NOT NULL," +
                "blob BLOB NOT NULL)";
        stmt.executeUpdate(sql);
        c.commit();
        stmt.close();

        // Table has been created - now write to it

        sql = "INSERT INTO table_name (id, blob) VALUES (?, ?)";
        PreparedStatement p_stmt = c.prepareStatement(sql);
        try(ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
            try(ObjectOutputStream oos = new ObjectOutputStream(bos)) {
                oos.writeObject(testStringToBeWrittenAsBlob);
                oos.flush();
                oos.close();

                p_stmt.setString(1, "test-id");
                p_stmt.setBytes(2, bos.toByteArray());
                p_stmt.execute(); // <--- No exception here now!
                c.commit();
            }
        }

        String selectSql = "SELECT blob FROM table_name WHERE id='test-id'";
        Statement selectStatement = c.createStatement();
        ResultSet resultSet = selectStatement.executeQuery(selectSql);
        if (resultSet.next()) {
            try (ObjectInputStream ois = new ObjectInputStream(resultSet.getBinaryStream(1))) {
                assertThat((String)ois.readObject()).isEqualTo(testStringToBeWrittenAsBlob);
            }
        } else {
            fail("Nothing in result set");
        }

        c.close();

    } catch (Exception e) {
        e.printStackTrace();
        fail();
    }
}

but I'm keeping this question open because I still don't know why this failed in the first place.