Have a linking (or ref) table which has a dual primary key. Need to return the last primary key which was auto generated from the current connection in a selected table. This is in java. The database is in MS Access if that makes a difference. Sorry for the rushed description, but our team is working to a tight schedule.
有一个具有双主键的链接(或ref)表。需要返回从选定表中的当前连接自动生成的最后一个主键。这是在java中。如果这会产生影响,则数据库位于MS Access中。对于匆忙的描述感到抱歉,但我们的团队正在紧张地安排工作。
Any links or suggestions would be gladly appreciated.
任何链接或建议将不胜感激。
EDIT:
The database is populated using SQL. We enter the data for one form, but we do not know the auto generated number. Need to find out what this was so it can be entered into the ref table. We only know half the composite key, which is why we need the second one.
使用SQL填充数据库。我们输入一个表格的数据,但我们不知道自动生成的数字。需要找出这是什么,以便它可以输入到ref表中。我们只知道复合键的一半,这就是我们需要第二个键的原因。
Edit:
Re the best answer so far (cant seem to comment). I get the following error... "Exception in thread "AWT-EventQueue-0" java.lang.UnsupportedOperationException"
到目前为止是最好的答案(似乎无法评论)。我收到以下错误...“线程中的异常”AWT-EventQueue-0“java.lang.UnsupportedOperationException”
Any Advice?
3 个解决方案
#1
You need to do two things. First, you'll have to pass in an extra parameter when preparing your statement or executing your statement. If you're using prepared statements, do the following:
你需要做两件事。首先,在准备语句或执行语句时,您必须传入一个额外的参数。如果您正在使用预准备语句,请执行以下操作:
stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
If you're not using prepared statements, do the following when you call executeUpdate():
如果您没有使用预准备语句,请在调用executeUpdate()时执行以下操作:
stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
After you execute your statement, you can call
执行语句后,您可以调用
ResultSet rs = stmt.getGeneratedKeys()
This will give you a result set that contains any of the keys that were generated.
这将为您提供包含生成的任何键的结果集。
I haven't tried this approach with Access, but it works fine with SQL Server identity columns.
我没有尝试使用Access的这种方法,但它适用于SQL Server标识列。
#2
Statement.getGeneratedKeys() should do the trick...
Statement.getGeneratedKeys()应该做的诀窍......
#3
Put both values for the composite key into an array of Object with two elements and return that.
将复合键的两个值放入具有两个元素的Object数组中并返回该值。
Alternatively, put the new composite key in the instance you just saved and return that.
或者,将新的复合键放在刚保存的实例中并返回。
#1
You need to do two things. First, you'll have to pass in an extra parameter when preparing your statement or executing your statement. If you're using prepared statements, do the following:
你需要做两件事。首先,在准备语句或执行语句时,您必须传入一个额外的参数。如果您正在使用预准备语句,请执行以下操作:
stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
If you're not using prepared statements, do the following when you call executeUpdate():
如果您没有使用预准备语句,请在调用executeUpdate()时执行以下操作:
stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
After you execute your statement, you can call
执行语句后,您可以调用
ResultSet rs = stmt.getGeneratedKeys()
This will give you a result set that contains any of the keys that were generated.
这将为您提供包含生成的任何键的结果集。
I haven't tried this approach with Access, but it works fine with SQL Server identity columns.
我没有尝试使用Access的这种方法,但它适用于SQL Server标识列。
#2
Statement.getGeneratedKeys() should do the trick...
Statement.getGeneratedKeys()应该做的诀窍......
#3
Put both values for the composite key into an array of Object with two elements and return that.
将复合键的两个值放入具有两个元素的Object数组中并返回该值。
Alternatively, put the new composite key in the instance you just saved and return that.
或者,将新的复合键放在刚保存的实例中并返回。