Apache Derby ID列应自动一一递增-但事实并非如此,为什么? [英] Apache Derby ID column should auto increment one by one - but it does not, why?
问题描述
我正在尝试创建一个Apache Derby-Table,并通过JDBC接口在其中插入数据.
I'm trying to create an Apache Derby-Table and to insert data in it by the JDBC-interface.
这是我的实现的简短摘录:
Here is a short excerpt of my implementation:
public class SQLDatabase {
private Connection connection = null;
public final static String HOME_DIRECTORY = System.getProperty("user.home");
public final static String TABLE_NAME = "PORPHYRIE";
public SQLDatabase() {
setConnection();
if (!(isTableExisting(TABLE_NAME))) {
createTable();
}
}
// OTHER
public void createTable() {
String statement = "CREATE TABLE PORPHYRIE("
+ "ID int NOT NULL GENERATED ALWAYS AS IDENTITY"
+ "(START WITH 1,INCREMENT BY 1)," + "ENTRYDATE DATE NOT NULL,"
+ "DRUGTYPE varchar(255) NOT NULL,"
+ "UPPERLIMIT DOUBLE NOT NULL," + "NORMOSANG BOOLEAN NOT NULL,"
+ "MENSTRUATION BOOLEAN NOT NULL,"
+ "DRUGAMOUNT_MG DOUBLE NOT NULL,"
+ "DRUGAMOUNT_ML DOUBLE NOT NULL,"
+ "AMPOULE_NUMBERS DOUBLE NOT NULL,"
+ "RECORDDATE DATE NOT NULL," + "PRIMARY KEY(ID)" + ")";
updateStatement(statement);
}
public void dropTable() {
String statement = "DROP TABLE PORPHYRIE";
updateStatement(statement);
}
public void addData(EntryPoint entry) {
DrugAmount drugAmount = entry.getDrugAmountObject();
SimpleDateFormat form = new SimpleDateFormat("dd.MM.yyyy");
String statement = "INSERT INTO PORPHYRIE (ENTRYDATE,DRUGTYPE,UPPERLIMIT,NORMOSANG,MENSTRUATION,DRUGAMOUNT_MG,DRUGAMOUNT_ML,AMPOULE_NUMBERS,RECORDDATE)"
+ " values('"
+ form.format(entry.getEntryDate().getTime())
+ "','"
+ entry.DRUG_TYPE_DOLANTIN
+ "',"
+ entry.DRUG_UPPER_LIMIT
+ ","
+ entry.getNormoSang()
+ ","
+ entry.getMenstruation()
+ ","
+ drugAmount.getAmpoulesInMG()
+ ","
+ drugAmount.getAmpoulesInML()
+ ","
+ drugAmount.getNumberOfAmpoules() + ",CURRENT_DATE)";
System.out.println("Add data!");
updateStatement(statement);
}
private void setConnection() {
try {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
connection = DriverManager.getConnection("jdbc:derby:"
+ HOME_DIRECTORY + "\\MyDB;create=true");
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
private void updateStatement(String statementString) {
try {
PreparedStatement preparedStatement = getConnection()
.prepareStatement(statementString);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}
这就是我得到的:
这里的ID列有什么问题?
What is here the problem with the ID column?
第一次创建SQL-Database对象时,应创建表,该表工作正常. 然后,您应该可以通过addData()方法插入一些数据,这看起来也不错. 但是,如果您创建一个新的SQL数据库对象并在其中使用addData-method(),则它将添加数据(上图中的第5行),但不会正确自动递增.这是怎么了
The first time when a SQL-Database object is created, is should create the table, which works fine. Then you should able to insert some data by the addData()-method, which also looks fine. But if you create an new SQL-Database Object and use the addData-method() within, then it adds the data (row five in the picture above) but do not auto-increment correctly. What is wrong here?
推荐答案
生成的序列号中的空白是Derby的正确且有据可查的行为.
Gaps in the generated sequence numbers are a correct and documented behavior of Derby.
例如,参见 https://db.apache.org /derby/docs/10.9/ref/rrefproperpreallocator.html
并且: https://issues.apache.org/jira/browse/DERBY- 5151
这篇关于Apache Derby ID列应自动一一递增-但事实并非如此,为什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!