Cannot get auto generated ID from MSSQL with JDBC

Question

    public String getKey() {

    Connection con = null;
    Statement stmt = null;
    String generatedKey = null;
    try {
        con = dataSrc.getConnection();
        stmt = con.createStatement();

        // ask to return generated key
        int affectedRows = stmt.executeUpdate("Insert into CountTab(t) value('0')",
                Statement.RETURN_GENERATED_KEYS);

        if (affectedRows == 0) {
            throw new SQLException(
                    "Creating row failed, no rows affected.");
        }

        try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
             ResultSetMetaData rsmd = generatedKeys.getMetaData();
             int columnCount = rsmd.getColumnCount();

             if (Log4j.log.isEnabledFor(Level.INFO)) {
                    Log4j.log.info("count: " + columnCount);
            }

            if (generatedKeys.next()) {
                generatedKey = generatedKeys.getString(1);

                if (Log4j.log.isEnabledFor(Level.INFO)) {
                    Log4j.log.info("key: " + generatedKey);
                }
            } else {
                throw new SQLException(
                        "Creating row failed, no ID obtained.");
            }
        }

    } catch (SQLException se) {

        // Handle any SQL errors
        throw new RuntimeException("A database error occured. "
                + se.getMessage());

    } finally {

        // Clean up JDBC resources
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException se) {
                se.printStackTrace(System.err);
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace(System.err);
            }
        }

    }
    return generatedKey;

}

CountTab{---this is my table(designed by other)
    id char(10) NOT NULL, ----Auto increament from 0000000001
    t char(1) NOT NULL,   ----just for insert to get the id
    PRIMARY KEY(id)
};

I'm trying to use java 1.7.0 to get unique ID that is generated from MS SQL DB, and the code above is what I use to do this; I also got the sqljdbc_4.1.5605.100 from MSDN and added the jar to the classpath for my program.

My problem: The value I got from getKey() is NULL. I'm not sure why it happens because this function do added new row into my table at each run, but it didn't reply me the Key value but NULL(even in Log; But I do got 1 from columnCount).

I dig around the Stackoverflow and I didn't see any answer which is similar or fits my condition. Please help me if there are any solution.

==============

Update:

This is table schema

CREATE TABLE [CountTab](
[id] [char](10) NOT NULL,
[t] [char](1) NOT NULL,
CONSTRAINT [PK_CountTab] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I think I might figured out why he/she made such table: Because the ID need to be started with date form like this yyMMddxxxx.

For example: If I have 3 insertions today, then I can see 3 record in table(1609060000, 1609060001, 1609060002); next day, new records will have its new beginning date(1609070000, 1609070001, 1609070002).

That is tricky, but might be useful for some area(but still I can't get the Key from RETURN_GENERATED_KEYS).

i am wondering that you do not get an exception. Because Count is a keyword in SQL. Please rename your table — Jens, Sep 06 '16 at 10:52
Possible duplicate of [How to get the insert ID in JDBC?](http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc) — Paolo, Sep 06 '16 at 10:54
@Paolo and NO, I do get code from there but it didn't reply me the KEY as it said. So this isn't same as THAT. — user6309529, Sep 06 '16 at 10:57
what about initializing the statement with the actual content at the beginning instead of creating it 'empty' and setting query & options only when running it? — Paolo, Sep 06 '16 at 11:00
well, this code works partially. my requirement is to use that table to generate unique insert ID for other purpose, but now it just inserts new rows of data and didn't give me the ID I want. — user6309529, Sep 06 '16 at 11:03
Please [edit] your question add add the **exact** `create table` statement for the table in question. A `char(10)` can't be an "auto-increment" column in SQL Server — , Sep 06 '16 at 11:04
@a_horse_with_no_name hmm, i'll try to dump this table's schema out. I didn't designed this table so I'm not sure how the "Table Creator" do this (The data inside this table DO have auto-increamental sequence) — user6309529, Sep 06 '16 at 11:10
@a_horse_with_no_name - Minor nit: A `char(10)` column could conceivably "auto-increment" (perhaps via an INSERT trigger) but it cannot be an IDENTITY column. — Gord Thompson, Sep 06 '16 at 11:22

score 0 · Accepted Answer · answered Sep 09 '16 at 03:42

0

Nevermind, I created another table with auto increment identity and RETURN_GENERATED_KEYS works now. Look like it does not support the Key which is not identity.

answered Sep 09 '16 at 03:42

user6309529

153
1
3
16

Cannot get auto generated ID from MSSQL with JDBC

1 Answers1