java - SQLite:如何插入未完整填充列的文档？

Question

在我的 Java 应用程序中，我使用 SQLite。我有 62 列的表。这里的代码尝试在此表中插入文档。

@Override
protected int insertToDB(Connection connection, String tableName, int pkDocsID, Map < Integer, String > fieldMap) throws SQLException {
    PreparedStatement ps = null;
    try {
        String parametersAsQMarks = DBManager.getInsertParametersAsQMarks(fieldMap.size() + COUNT_RESERVED_FIELDS);
        ps = connection.prepareStatement("INSERT INTO " + tableName + " VALUES (" + parametersAsQMarks + ")");
        ps.setInt(COUNT_RESERVED_FIELDS, pkDocsID); // reserved field
        int step = COUNT_RESERVED_FIELDS + 1;
        for (int index = 0; index < fieldMap.size(); index++) {
            String fieldValue = fieldMap.get(index + 1);
            ps.setString(index + step, fieldValue);
        }
        return ps.executeUpdate();
    } finally {
        if (ps != null) {
            ps.close();
        }
    }
}

但是我收到错误:

 [java] [23.01.2018 20:57:28.312] 

md.deeplace.ca.db.SQLiteStrategy.insertM2Document(SQLiteStrategy.java:125) ERROR:
     [java]     table OUTDocsBS has 62 columns but 37 values were supplied
     [java] java.sql.SQLException: table OUTDocsBS has 62 columns but 37 values were supplied
     [java]     at org.sqlite.DB.throwex(DB.java:288)
     [java]     at org.sqlite.NestedDB.prepare(NestedDB.java:115)
     [java]     at org.sqlite.DB.prepare(DB.java:114)
     [java]     at org.sqlite.PrepStmt.<init>(PrepStmt.java:37)
     [java]     at org.sqlite.Conn.prepareStatement(Conn.java:231)
     [java]     at org.sqlite.Conn.prepareStatement(Conn.java:224)
     [java]     at org.sqlite.Conn.prepareStatement(Conn.java:213)
     [java]     at com.myproject.ca.db.SQLiteStrategy.insertToDB(SQLiteStrategy.java:64)

我想在未填充所有列时插入文档。在本例中为 37 列。我怎样才能做到这一点？

Answer 1

您需要为列命名。而不是:

..."INSERT INTO " + tableName + " VALUES (" + parametersAsQMarks + ")");

尝试

..."INSERT INTO " + tableName + "(columnName1, columnName2, ... columnName37) VALUES (" + parametersAsQMarks + ")");

其中每个命名列与相应的 fieldMap 匹配要插入的值。根据代码，更改 fieldMap 可能会有用。至Map<String, String> ，其中键是列名称(而不是列号)。

当然，其他剩余列必须允许为空。