在Java BinaryStream更新中通过SQLite将对象写入blob --非空约束冲突

Question

我试图以blob的形式将一个对象写入数据库，但我得到了java.sql.SQLException: [SQLITE_CONSTRAINT] Abort due to constraint violation (NOT NULL constraint failed: table_name.blob)。

最初，我尝试以SerialBlob的形式创建blob，并使用setBlob在PreparedStatement上设置blob，但得到了java.sql.SQLException: not implemented by SQLite JDBC driver。因此，在这个答案的指导下，我尝试为对象创建一个inputStream，并将其设置为二进制流。

我不知道为什么数据库认为这个约束被违反了--在下面的测试中，testStreamContentNotNull通过了，但是testDatabaseWrite抛出了前面提到的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

对于任何发现这个问题的人，多亏了这个职位，我才能通过阅读ByteArrayOutputStream::toBytes --即以下测试通过--来完成这个问题：

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

但是我不想问这个问题，因为我仍然不知道为什么这个问题会失败。