Wednesday, April 8, 2009

How to get auto generated field values (primary keys etc) after inserting a data entry to the database [ for PostgreSQL]

In these days I'm involved with a project using a PostgreSQL database. This is the first time I'm working with PGSQL, So I could learn some new features in PGSQL. One of the requirement I needed while coding is to find an easy way to get the auto generated primary key value when inserting a data record to the PGSQL database. I could find several ways to do that and one of method I found was really easy way. Those methods I found are described in this post.

First Method:
public int insertData( String name, String address, String tp_no)
{

int insertedKey = 0;
try {
Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection("jdbc:postgresql://localhost/MyDb", "username", "password");

// Database has a primary key named "recordId"
String insertSql = "INSERT INTO \"MyTable\" (name, address, tp_no)" + " VALUES (?, ?, ?);";
PreparedStatement pstmt = con.prepareStatement(insertSql);
pstmt.setString(1, name);
pstmt.setString(2, address);
pstmt.setString(3, tp_no);
pstmt.executeUpdate();

// each table has a sequence named "TABLENAME_PRIMARYKEY_seq"
// my table name is "MyTable" and it has the auto incremented
// primary key field called "recordId". So sequence related to
// following query is "MyTable_recordId_seq"
String getRecordIdSql = "select currval('\"MyTable_recordId_seq\"');";
Statement stmtGetRecId = con.createStatement();
ResultSet res = stmtGetRecId.executeQuery(getRecordIdSql);

if (res.first())
{
insertedKey = res.getInt(1);
}

con.close();

}
catch (Exception e)
{
System.out.println("Exception occurred: Details: " + e.getLocalizedMessage());
}

return insertedKey;

}

Second & Easy Method:

public int insertData( String name, String address, String tp_no)
{

int insertedKey = 0;
try {
Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection("jdbc:postgresql://localhost/MyDb", "username", "password");
// See the end of the query string
// Database has a primary key named "recordId"
String insertSql = "INSERT INTO \"MyTable\" (name, address, tp_no)" + " VALUES (?, ?, ?) RETURNING recordId;";
PreparedStatement pstmt = con.prepareStatement(insertSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
pstmt.setString(1, name);
pstmt.setString(2, address);
pstmt.setString(3, tp_no);
ResultSet res = pstmt.executeQuery();
// Get record id of the newly inserted record
if (res.first()) {
insertedKey = res.getInt(1);
}
res.close();
con.close();
} catch (Exception e) {
// logging
System.out.println("Exception occurred: Details: " + e.getLocalizedMessage());
}
return insertedKey;

}

Another Method:
I found later an another way to get the generated keys using JDBC, but I didn't have time to test that. But you can find an example to that method in this article http://www.java-tips.org/other-api-tips/jdbc/how-to-get-auto-generated-keys-from-java-class-3.html


~~~


No comments: