CODEWITHSUNDEEP
javasqlsql-server-2008jdbc
Mike
Mike

Reputation: 1312

Get the primary key value from SQL table using java

I'm using java to insert users to a sql database table. The insert works great. However, the user id is the primary key and gets auto increment. I want to get the value of the user id after I inserted the new user to the table.

Here is the insert part:

PreparedStatement create_statement;
StringBuilder sb = new StringBuilder();
sb.append("INSERT INTO ");
sb.append(this.get_database());
sb.append(".dbo.");
sb.append(this.get_table());
sb.append(" ( username, password, email_addr, first_name, middle_name, last_name, dob, street_addr, phone_num, bio)   ");
sb.append(" VALUES( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? );");

try {
    create_statement = this.sql_connection.prepareStatement(sb.toString());
    create_statement.setString(1, this.username);
    create_statement.setString(2, this.password);
    create_statement.setString(3, this.email_addr);
    create_statement.setString(4, this.first_name);
    create_statement.setString(5, this.middle_name);
    create_statement.setString(6, this.last_name);
    create_statement.setString(7, this.dob);
    create_statement.setString(8, this.street_addr);
    create_statement.setString(9, this.phone_num);
    create_statement.setString(10, this.bio);
    create_statement.executeUpdate();

I'm looking for the answer for an hour, some do it by using ResultSet. However, I keep getting errors or null when trying to get the data from create_statement

Help :(

Upvotes: 0

Views: 2904

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

Compare executeQuery and executeUpdate here:

http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html

You won't get a ResultSet from executeUpdate because it returns an int. Nor can you use executeQuery here, because you're INSERTing, not SELECTing anything. Use this instead

create_statement = this.sql_connection.prepareStatement(sb.toString(),
                   Statement.RETURN_GENERATED_KEYS);
//your code
ResultSet res = create_statement.getGeneratedKeys();

And even when learning, variables should be named appropriately, it's not a create statement, is it?

Upvotes: 3

Related Questions