我有一个java应用程序,它使用sqlite数据库捕获“statusevent”对象。statusevent如下所示:
public class StatusEvent
{
private final String statusId;
private final RefreshValue value;
private final Instant createdAt;
public StatusEvent(String id, RefreshValue currentValue)
{
Objects.requireNonNull(id, "StatusId must not be null");
Objects.requireNonNull(currentValue, "RefreshValue must not be null");
statusId = id;
value = currentValue;
createdAt = Instant.now();
}
// Simple getters omitted
}
其中refreshvalue是一个对象,可以包含不同类型的状态值,如int、float或string。重要的是statusevent是不可变的,并且在构造之前检查statusid是否为null。
然后我使用下面的代码接收statusevent对象并将它们存储在linkedblockingqueue中,以便创建批处理。每隔200毫秒,执行器就会运行“writebatch”,这会耗尽队列,并尝试提交当前批处理中所有statusevents的数据。
问题是,我在写入数据库时偶尔会遇到一个错误:“org.sqlite.sqliteexception:[sqlite\u constraint\u notnull]一个not null约束失败(not null constraint failed:status.status\u id)”在给定不可变的statusevent类的情况下,我看不出这是怎么可能的。在给定数据库配置方式的情况下,在离开应用程序和访问数据库之间是否有某种方法会丢失数据?此外,根据接收到的statusevent对象的数量,对数据库的写入速度似乎不够快。在持续的压力期之后,statusevent(即构造函数中的时间戳)的创建与数据库中的“created_at”时间之间可能有几秒钟的时间(statuseventdb.accept()几乎在创建statusevents之后立即接收它们)。正在寻找任何帮助来解决这些问题中的一个或两个。
public class StatusEventDB
{
private static final String DROP_TABLE = "DROP TABLE IF EXISTS status";
private static final String MAKE_TABLES =
"CREATE TABLE status (id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"status_id TEXT NOT NULL, " + "value TEXT NOT NULL, " +
"time TIMESTAMP NOT NULL, " +
"created_at TIMESTAMP DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f')))";
private static final String SE_STRING =
"INSERT INTO status (status_id, value, time) VALUES(?,?,?)";
private final Connection connection;
private final PreparedStatement insertSEStatement;
private final ScheduledExecutorService scheduler =
Executors.newSingleThreadScheduledExecutor();
protected final LinkedBlockingQueue<StatusEvent> buffer =
new LinkedBlockingQueue<>();
StatusEventDB() throws SQLException
{
SQLiteConfig config = new SQLiteConfig();
config.setJournalMode(SQLiteConfig.JournalMode.WAL);
config.setCacheSize(32768);
config.setTransactionMode(SQLiteConfig.TransactionMode.EXCLUSIVE);
config.setSynchronous(SQLiteConfig.SynchronousMode.NORMAL);
config.setTempStore(TempStore.MEMORY);
config.setBusyTimeout(5000);
this.connection = DriverManager
.getConnection("jdbc:sqlite:/mydir/test.db",
config.toProperties());
try (Statement statement = connection.createStatement())
{
connection.setAutoCommit(false);
statement.executeUpdate(DROP_TABLE);
statement.executeUpdate(MAKE_TABLES);
}
insertSEStatement = connection.prepareStatement(SE_STRING);
scheduler.scheduleAtFixedRate(this::writeBatch, 200, 200,
TimeUnit.MILLISECONDS);
}
public void accept(StatusEvent se)
{
buffer.add(se);
}
private void writeBatch()
{
int bufferSize = buffer.size();
if (0 < bufferSize)
{
List<StatusEvent> commitBatch = new ArrayList<>(bufferSize);
buffer.drainTo(commitBatch, bufferSize);
try
{
for (StatusEvent se : commitBatch)
{
insertSEStatement.setString(1, se.getStatusId());
insertSEStatement.setString(2, se.getValue().getString());
insertSEStatement.setString(3, se.createdAt().toString());
insertSEStatement.addBatch();
}
insertSEStatement.executeBatch();
connection.commit();
}
catch (SQLException e)
{
System.err.println(e.getMessage());
}
}
}
}
暂无答案!
目前还没有任何答案,快来回答吧!