0

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 :(

Mike
  • 1,302
  • 6
  • 23
  • 43
  • Did you check into database?? Value is getting added?? And please post code where you are inserting.. – Rohit Jain Oct 07 '12 at 17:42
  • Does this help http://stackoverflow.com/questions/1419606/how-can-i-get-the-last-inserted-primary-key – Nathan Villaescusa Oct 07 '12 at 17:46
  • Yes, user id values are getting incremented each insert. This is the code I'm inserting. I want to get the value back to java from this statment: **SELECT id FROM table_name WHERE username='username'** – Mike Oct 07 '12 at 17:47
  • http://stackoverflow.com/questions/6799738/primary-key-value-after-insertion-of-row-in-sql-server-2005 as well. – Nathan Villaescusa Oct 07 '12 at 17:48

1 Answers1

3

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?

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262