CODEWITHSUNDEEP
javamysqljspjdbcmyisam
user1045680
user1045680

Reputation: 865

MySQL Error when using Statement.RETURN_GENERATED_KEYS

I inherited an old Servlet/JSP website, which has been updated to Java 1.8.0_201 and MySQL 5.7.28. Recently, I started to get this error when adding new records to the database:

Generated keys not requested. You need to specify Statement.RETURN_GENERATED_KEYS to Statement.executeUpdate(), Statement.executeLargeUpdate() or Connection.prepareStatement().

I googled what was going on, and found that I need to add Statement.RETURN_GENERATED_KEYS to my Statement.executeUpdateQuery, so I did. However, I still get the error. The updated code, and the error occurs at the statement result = stmt.getGeneratedKeys();:

            stmt = con.createStatement();
            switch(queryType) {
                case INSERT_QUERY:
                    stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
                    int autoIncKey = -1;
                    result = stmt.getGeneratedKeys();
                    if (result.next()) {
                        autoIncKey = result.getInt(1);
                    }
                    rows = stmt.getUpdateCount();
                    svr.setGeneratedKey(autoIncKey);
                    obj.setGeneratedKey(autoIncKey);
                    svr.setRows(rows); //Insert/Update/Delete
                    if (rows > 0)
                        svr.setSuccess(true);
                    else
                        svr.setSuccess(false);
                    break;

However, the insert works and the data is put into the database.

I then thought I should update the Mysql Connector library, so I updated from version 5.4 to mysql-connector-java-8.0.18.jar. Still getting the same error.

I am not using any prepared statements, just a String for the query text. This is the query string:

INSERT INTO Flights(rocket_name, weight, angle, baseline, egg_id, shockcord_id, notes, date, rocketModelID, mission_specialists, flight_engineers, teacher_id) VALUES ('asdfasdfasd', 98.0, 60.0, 60.0, 2, 2, 'sfdg sfdg sdg sfdg sdfg sfdg sfdg', '2020-01-07', 4,'asdfasdf', 'asdfasdfas', 13);

The table definition for Flights:

| Flights | CREATE TABLE `Flights` (
  `flight_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `teacher_id` int(11) NOT NULL DEFAULT '0',
  `egg_id` int(10) unsigned NOT NULL DEFAULT '0',
  `shockcord_id` int(10) unsigned NOT NULL DEFAULT '0',
  `rocket_name` varchar(100) NOT NULL DEFAULT '',
  `weight` decimal(10,4) unsigned NOT NULL DEFAULT '0.0000',
  `angle` decimal(10,5) NOT NULL DEFAULT '0.00000',
  `baseline` decimal(10,5) NOT NULL DEFAULT '0.00000',
  `date` date NOT NULL DEFAULT '0000-00-00',
  `rocketModelID` int(11) unsigned NOT NULL DEFAULT '0',
  `flight_engineers` varchar(100) NOT NULL DEFAULT '',
  `mission_specialists` varchar(100) NOT NULL DEFAULT '',
  `notes` text,
  PRIMARY KEY (`flight_id`),
  FULLTEXT KEY `search1` (`mission_specialists`),
  FULLTEXT KEY `search2` (`flight_engineers`),
  FULLTEXT KEY `search3` (`flight_engineers`,`mission_specialists`)
) ENGINE=MyISAM AUTO_INCREMENT=562 DEFAULT CHARSET=latin1

I am not sure how to proceed. Any suggestions would be greatly appreciated!

Mark

Upvotes: 1

Views: 978

Answers (1)

Andreas
Andreas

Reputation: 159135

Recommend changing your code as follows:

  • Name the key column

  • Get the row count from the executeUpdate call

  • Don't call getGeneratedKeys() if no rows were inserted

rows = stmt.executeUpdate(query, new String[] { "flight_id" });
int autoIncKey = -1;
if (rows > 0) {
    result = stmt.getGeneratedKeys();
    if (result.next()) {
        autoIncKey = result.getInt(1);
    }
}
svr.setGeneratedKey(autoIncKey);
obj.setGeneratedKey(autoIncKey);
svr.setRows(rows); //Insert/Update/Delete
svr.setSuccess(rows > 0);

Though, really, a single INSERT statement using VALUES will always insert exactly one row, so checking row count is entirely unnecessary. If the row wasn't inserted, an exception would have been thrown, so your code could be reduced to:

stmt.executeUpdate(query, new String[] { "flight_id" });
try (ResultSet result = stmt.getGeneratedKeys()) {
    result.next();
    int autoIncKey = result.getInt(1);
    svr.setGeneratedKey(autoIncKey);
    obj.setGeneratedKey(autoIncKey);
}
svr.setRows(1);
svr.setSuccess(true);

Upvotes: 2

Related Questions