Description:
While working through the tutorial for JDBC on
http://dev.mysql.com/tech-resources/articles/autoincrement-with-connectorj.html
I encountered a problem, when using getGeneratedKeys() as well as the 3rd example wont work for me, when adding a row to a table with 2 primary keys.
By doing the same on a table with only one primary key it works as expected.
I use a Table for languages:
CREATE TABLE Lang (
idLang INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Language VARCHAR(30) NOT NULL,
Abbr VARCHAR(5) NOT NULL,
PRIMARY KEY(idLang)
)
TYPE=InnoDB;
and a table for Descriptions:
CREATE TABLE Descriptions (
idDesc INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
idLang INTEGER UNSIGNED NOT NULL,
Description VARCHAR(255) NOT NULL,
PRIMARY KEY(idDesc, idLang),
FOREIGN KEY(idLang) REFERENCES Lang(idLang)
)
TYPE=InnoDB;
Everything works when doing:
ResultSet res = getResultSet("SELECT idLang, Language, Abbr FROM Lang");
res.last();
System.out.println( res.getInt(1) ); // output: e.g. 2
res.moveToInsertRow();
res.updateString(2, "TestLang");
res.updateString(3, "td");
res.insertRow();
res.last();
System.out.println( res.getInt(1) ); // output: e.g. 3
But fails when doing:
ResultSet res = getResultSet("SELECT idDesc, idLang, Description FROM Descriptions");
res.last();
System.out.println( res.getInt(1) ); // output: e.g. 3183
res.moveToInsertRow();
res.updateInt(2, 1); // defining the language ID
res.updateString(3, "Some Description");
res.insertRow();
res.last();
System.out.println( res.getInt(1) ); // output: 0
When looking, after res.insertRow() is done (using the eclipse debugger), into the Table the Dataset is inserted into the table, having a new ID.
It doesn't matter if I use res.last() or res.moveToCurrentRow() or res.next() after res.insertRow(). Always the same.
// Method
public ResultSet getResultSet(String select) {
ResultSet res = null;
Statement s = null;
try {
s = cn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
res = s.executeQuery(select);
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
Thanx for help.
Fabian
PS: As a workaround I use "SELECT LAST_INSERT_ID()" but it isn't as elegant as I like to have it.
How to repeat:
// Method
public ResultSet getResultSet(String select) {
ResultSet res = null;
Statement s = null;
try {
s = cn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
res = s.executeQuery(select);
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
// Code
ResultSet res = getResultSet("SELECT idDesc, idLang, Description FROM Descriptions");
res.last();
System.out.println( res.getInt(1) ); // output: e.g. 3183
res.moveToInsertRow();
res.updateInt(2, 1); // defining the language ID
res.updateString(3, "Some Description");
res.insertRow();
res.last();
System.out.println( res.getInt(1) ); // output: 0
