Oracle Java JDBC: Get Primary Key Of Inserted Record
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle Java JDBC: Get Primary Key Of Inserted Record,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3872字,纯文字阅读大概需要6分钟。
内容图文
Here is a small write-up which should help those who still write plain Java JDBC code. I know we have some wonderful persistence frameworks like Hibernate that make ones life comfortable but the reality is we still have to deal with plain old JDBC apis. If you are poor chap like me, below code should make your life easy.
Problem statement:
I just inserted a record in Oracle database using Java JDBC. The primary key column was auto populated by a sequence value. How should I get the last inserted records auto generated primary key?
Solution:
The solution should be getGeneratedKeys(). This method was added in JDBC 3.0 and it should be used to get last auto generated key value.
See code snippet below:
PreparedStatement prepareStatement = connection.prepareStatement( "insert..." ,
new String[] { "your_primary_key_column_name" });
prepareStatement.executeUpdate();
ResultSet generatedKeys = prepareStatement.getGeneratedKeys();
if ( null != generatedKeys && generatedKeys.next()) {
Long primaryKey = generatedKeys.getLong( 1 );
}
|
The above code should give us auto generated primary key value. The one thing to note here is method prepareStatement(). We passed two arguments first the insert query string and second an array of column name. The column name should be the primary key column name of table where you inserting the record.
Check below source code to see complete solution.
Full solution
We have a database table called STUDENTS
. We also have an oracle sequence called STUDENT_SEQ
that we uses to generate primary key for STUDENTS table.
CREATE TABLE STUDENTS
(
STUDENT_ID NUMBER NOT NULL PRIMARY KEY ,
NAME VARCHAR2 (50 BYTE),
EMAIL VARCHAR2 (50 BYTE),
BIRTH_DATE DATE
);
CREATE SEQUENCE STUDENT_SEQ
START WITH 0
MAXVALUE 9999999999999999999999999999
MINVALUE 0;
|
In Java, we use plain JDBC calls to insert a record in STUDENTS
table. We uses sequence STUDENT_SEQ
to generate primary key. Once the record is inserted, we want the last inserted primary value.
String QUERY = "INSERT INTO students " +
" VALUES (student_seq.NEXTVAL," +
" ‘Harry‘, ‘harry@hogwarts.edu‘, ‘31-July-1980‘)" ;
// load oracle driver
Class.forName( "oracle.jdbc.driver.OracleDriver" );
// get database connection from connection string
Connection connection = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:sample" , "scott" , "tiger" );
// prepare statement to execute insert query
// note the 2nd argument passed to prepareStatement() method
// pass name of primary key column, in this case student_id is
// generated from sequence
PreparedStatement ps = connection.prepareStatement(QUERY,
new String[] { "student_id" });
// local variable to hold auto generated student id
Long studentId = null ;
// execute the insert statement, if success get the primary key value
if (ps.executeUpdate() > 0 ) {
// getGeneratedKeys() returns result set of keys that were auto
// generated
// in our case student_id column
ResultSet generatedKeys = ps.getGeneratedKeys();
// if resultset has data, get the primary key value
// of last inserted record
if ( null != generatedKeys && generatedKeys.next()) {
// voila! we got student id which was generated from sequence
studentId = generatedKeys.getLong( 1 );
}
}
|
The above code is filled with comments and is pretty self explanatory. Finally we have last inserted value in studentId
variable.
The getGeneratedKeys()
method is key here. It gives us the result set of all auto generated key values. In our case as we have only one auto generated value (for student_id column) we get only single record in this result set.
Oracle Java JDBC: Get Primary Key Of Inserted Record
标签:
本文系统来源:http://www.cnblogs.com/hephec/p/4563150.html
内容总结
以上是互联网集市为您收集整理的Oracle Java JDBC: Get Primary Key Of Inserted Record全部内容,希望文章能够帮你解决Oracle Java JDBC: Get Primary Key Of Inserted Record所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。