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

Posted Sat, 10/31/2009 - 11:56 by manjula

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-k...

Bookmark/Search this post with: