自动增量问题-向数据库添加条目并同时显示它们的最佳方式
我使用 SQLite 数据库和 Java GUI。在 GUI 上输入的信息将添加到数据库的表中。该表包含自动增量。同时我想在 GUI 上显示信息并稍后更改它。
创建数据库：
stat.executeUpdate("create table t1(ROWID INTEGER PRIMARY KEY AUTOINCREMENT, Value);";
向数据库添加值：
Statement stat = con.createStatement();
String sql = "insert into t1 values ($next_id,'"+value+');";
stat.executeUpdate(sql);
如何在程序中保存 ID，使其与数据库中的 ID 相同，并且可以轻松访问数据库？
编辑： 我尝试了评论中提到的解决方案并遇到了 NYI 异常...
String sql = "insert into t1 values($next_id,'"+value+"');";
PreparedStatement stmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet res = stmt.getGeneratedKeys();
while (res.next()){
int id = res.getInt(1);
System.out.println(id);
}
con.commit();
还尝试了 Russel 的解决方案并得到了另一个异常（“SQLite JDBC 驱动程序未实现”）：
String sql = "insert into t1 values($next_id,'"+value+"');";
Statement stat = con.createStatement();
stat.executeUpdate(sql);
stat.executeUpdate("SELECT LAST_INSERT_ROWID() from t1;", Statement.RETURN_GENERATED_KEYS);
ResultSet res = stat.getGeneratedKeys();
while (res.next()){
int id = res.getInt(1);
System.out.println(id);
}
我做错了什么？
I use a SQLite Database and a Java GUI. The information entered on the GUI will be added to a table in the database. This table contains an autoincrement. On the same time I want to display the information on the GUI and change it later.
creating the database:
stat.executeUpdate("create table t1(ROWID INTEGER PRIMARY KEY AUTOINCREMENT, Value);";
adding values to the database:
Statement stat = con.createStatement();
String sql = "insert into t1 values ($next_id,'"+value+');";
stat.executeUpdate(sql);
How can I save the ID in my program so that it will be the same as in the database and I have easy access to my database?
Edit:
I tried the solution mentioned in the comments and run into a NYI exception ...
String sql = "insert into t1 values($next_id,'"+value+"');";
PreparedStatement stmt = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet res = stmt.getGeneratedKeys();
while (res.next()){
int id = res.getInt(1);
System.out.println(id);
}
con.commit();
tried also the solution of Russel and got another exception ("not implemented by SQLite JDBC driver"):
String sql = "insert into t1 values($next_id,'"+value+"');";
Statement stat = con.createStatement();
stat.executeUpdate(sql);
stat.executeUpdate("SELECT LAST_INSERT_ROWID() from t1;", Statement.RETURN_GENERATED_KEYS);
ResultSet res = stat.getGeneratedKeys();
while (res.next()){
int id = res.getInt(1);
System.out.println(id);
}
What did I wrong?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
只调用
SELECT LAST_INSERT_ID()怎么样？这将返回为您的连接生成的最后一个自动增量值（不受其他客户端操作的影响）。或者，查看 Javadoc，您似乎应该能够使用普通的
Statement来完成此操作：What about just calling
SELECT LAST_INSERT_ID()? This returns the last auto-increment value generated for your connection (not affected by other clients' actions).Alternately, looking at the Javadoc, it seems you should be able to do this with an ordinary
Statement:如果您不介意使用 ORM 库，请尝试 sormula。它将为您完成身份列的所有工作。所需要的只是要自动递增的 POJO 字段上的 @Column(identity=true) 注释。
org.sormula.tests.identity 包中的测试用例向您展示了如何操作。包含 Sqlite 测试设置和 sqlitejdbc-v056.jar jar。更改 build.properties 以运行 db.dir=sqlitejdbc。
If you don't mind using an ORM library, try sormula. It will do all of the work for identity column for you. All that is required is @Column(identity=true) annotation on the POJO field that is to be auto incremented.
The test cases in org.sormula.tests.identity package shows you how. Sqlite test set up and sqlitejdbc-v056.jar jar is included. Change build.properties to run db.dir=sqlitejdbc.