Hi,
I have a problem with the function "getGeneratedKeys()", it is either a
mistake I do but do not see, or a bug.
I have two tables created as follows:
private static final String typesTableCreationStr =
"CREATE TABLE types (" +
" id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
" name VARCHAR(128) NOT NULL UNIQUE," +
" shortName VARCHAR(64) NOT NULL" +
")";
private static final String methodsTableCreationStr =
"CREATE TABLE methods (" +
" id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
" class INT REFERENCES types (id)," +
" name VARCHAR(128) NOT NULL," +
" isConstructor VARCHAR(5) NOT NULL," +
" isStatic VARCHAR(5) NOT NULL," +
" returnType INT REFERENCES types (id)" +
")";
I want to add an entry in table "methods" and retrieve the generated key
(it works well with a simple INSERT INTO types).
I have already 4 entries in table types with id 1, 2, 3, and 4; table
methods is empty.
I do:
private static final String insertStr_method =
"INSERT INTO methods" +
" (class, name, isConstructor, isStatic, returnType)" +
" SELECT c.id, ?, ?, ?, r.id" +
" FROM types AS c" +
" , types AS r" +
" WHERE c.name = ?" +
" AND r.name = ?";
...
stmt = dbConnection.prepareStatement(insertStr_method,
Statement.RETURN_GENERATED_KEYS);
...
stmt.executeUpdate();
ResultSet res = stmt.getGeneratedKeys();
if ( res.next() ) {
id = res.getInt(1);
...
}
Then id equals 4. I would have expected 1 as it is the first insert into
table methods. 4 is the last generated key of table "types" but the
insert is done into table "methods", so I would have expected to get the
last generated key of table "methods".
I am using db-derby-10.4.2.0 and jdk1.6.0_11 under Linux
I attached the java files I coded.
So, is it a bug or something I do wrong.
Thank you,
Gurvan
package org.thinkcollabs.jmbrowser.db;
import java.io.*;
import java.sql.*;
import java.util.*;
/**
* This is a Data Accessor Object class.
* An instance of this class is used to store and retrieve data objects from a database.
* @author Nicolas Bonnel and Gurvan Le Guernic ({...@literal </a href="http://thinkcollabs.org";>ThinkCollabs</a>})
*/
class DAO {
/** Path to the file containing configuration information (default to {...@value}) */
private static final String propertiesFile = "./jmbrowser.cfg";
/** Name of the property containing the driver to use ({...@value})*/
private static final String dbDriver_propName = "DB_dbDriver";
/** The default driver to use ({...@value})*/
private static final String dbDriver_default = "org.apache.derby.jdbc.EmbeddedDriver";
private static final String protocol_propName = "DB_protocol";
private static final String protocol_default = "jdbc:derby:";
private static final String dbName_propName = "DB_dbName";
private static final String dbName_default = "JMBrowserDB";
private static final String typesTableCreationStr =
"CREATE TABLE types (" +
" id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
" name VARCHAR(128) NOT NULL UNIQUE," +
" shortName VARCHAR(64) NOT NULL" +
")";
private static final String methodsTableCreationStr =
"CREATE TABLE methods (" +
" id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
" class INT REFERENCES types (id)," +
" name VARCHAR(128) NOT NULL," +
" isConstructor VARCHAR(5) NOT NULL," +
" isStatic VARCHAR(5) NOT NULL," +
" returnType INT REFERENCES types (id)" +
")";
private static final String methodParametersTableCreationStr =
"CREATE TABLE methodParameters (" +
" method INT REFERENCES methods (id)," +
" position INT," +
" parameter INT REFERENCES types (id)" +
")";
private static final String insertStr_type =
"INSERT INTO types" +
" (name, shortName)" +
" VALUES (?, ?)";
private static final String insertStr_method =
"INSERT INTO methods" +
" (class, name, isConstructor, isStatic, returnType)" +
" SELECT c.id, ?, ?, ?, r.id" +
" FROM types AS c" +
" , types AS r" +
" WHERE c.name = ?" +
" AND r.name = ?";
private static final String insertStr_parameter =
"INSERT INTO methodParameters" +
" (method, position, parameter)" +
" SELECT ?, ?, p.id" +
" FROM types AS p" +
" WHERE p.name = ?";
private Properties dbProperties;
private Connection dbConnection;
private String dbDriver;
private String protocol;
private String dbName;
DAO() {
loadDbProperties();
loadDriver();
}
private boolean loadDbProperties() {
boolean success = true;
dbProperties = new Properties();
dbProperties.setProperty(dbDriver_propName, dbDriver_default);
dbProperties.setProperty(protocol_propName, protocol_default);
dbProperties.setProperty(dbName_propName, dbName_default);
InputStream is = null;
try{
is = new FileInputStream(propertiesFile);
dbProperties.loadFromXML(is);
is.close();
} catch (FileNotFoundException e) {
success = false;
System.out.println("Impossible to open file "+propertiesFile+".");
} catch (SecurityException e) {
success = false;
System.out.println("Read access to file "+propertiesFile+" has been denied.");
} catch (IOException e) {
success = false;
System.out.println("Problem loading properties from file "+propertiesFile+".");
/* e.printStackTrace(); */
}
dbDriver = dbProperties.getProperty(dbDriver_propName);
protocol = dbProperties.getProperty(protocol_propName);
dbName = dbProperties.getProperty(dbName_propName);
System.out.println("Loaded the DB properties.");
return success;
}
private boolean saveDbProperties() {
boolean success = true;
OutputStream os = null;
try{
os = new FileOutputStream(propertiesFile);
dbProperties.storeToXML(os,null);
os.flush();
os.close();
System.out.println("Saved the DB properties into "+propertiesFile+".");
} catch (FileNotFoundException e) {
success = false;
System.out.println("Impossible to open file "+propertiesFile+".");
} catch (SecurityException e) {
success = false;
System.out.println("Write access to file "+propertiesFile+" has been denied.");
} catch (IOException e) {
success = false;
System.out.println("Problem saving properties to file "+propertiesFile+".");
/* e.printStackTrace(); */
}
return success;
}
private void loadDriver() {
try {
Class.forName(dbDriver).newInstance();
System.out.println("Loaded the DB driver.");
} catch (ClassNotFoundException e) {
System.out.println("Unable to locate the class of DB driver '"+dbDriver+"'.");
System.out.println("Check your CLASSPATH!");
e.printStackTrace(System.err);
} catch (InstantiationException e) {
System.out.println("Unable to instantiate the DB driver '"+dbDriver+"'.");
e.printStackTrace(System.err);
} catch (IllegalAccessException e) {
System.out.println("Illegal access to the DB driver '"+dbDriver+"'.");
e.printStackTrace(System.err);
}
}
private static void printSQLException(SQLException e) {
System.out.println("SQL errors!");
while (e != null) {
System.err.println("\n --- SQL exception ---");
System.err.println(" SQL state: "+e.getSQLState());
System.err.println(" Error code: "+e.getErrorCode());
System.err.println(" Message: "+e.getMessage());
e = e.getNextException();
}
}
public boolean createDB() {
boolean successful = true;
try {
dbConnection = DriverManager.getConnection(protocol + dbName + ";create=true");
Statement s = dbConnection.createStatement();
s.execute(typesTableCreationStr);
s.execute(methodsTableCreationStr);
s.execute(methodParametersTableCreationStr);
} catch (java.sql.SQLException e) {
printSQLException(e);
successful = false;
}
System.out.println("DB created.");
return successful;
}
public boolean loadDB() {
boolean successful = true;
try {
dbConnection = DriverManager.getConnection(protocol + dbName);
} catch (java.sql.SQLException e) {
printSQLException(e);
successful = false;
}
System.out.println("DB loaded.");
return successful;
}
public int insertType(String shortName, String name) {
int id = -1;
PreparedStatement stmt;
try {
stmt = dbConnection.prepareStatement(insertStr_type, Statement.RETURN_GENERATED_KEYS);
stmt.setString(1, name);
stmt.setString(2, shortName);
stmt.executeUpdate();
ResultSet res = stmt.getGeneratedKeys();
if ( res.next() ) {
id = res.getInt(1);
}
} catch (java.sql.SQLException e) {
System.out.println("Error in DAO.insertType(String, String)");
printSQLException(e);
}
return id;
}
public int insertMethod(MethodRecord m) {
int id = -1;
PreparedStatement stmt;
try {
stmt = dbConnection.prepareStatement(insertStr_method, Statement.RETURN_GENERATED_KEYS);
stmt.setString(1, m.getMethodName());
stmt.setString(2, Boolean.toString(m.isConstructor()));
stmt.setString(3, Boolean.toString(m.isStatic()));
stmt.setString(4, m.getClassName());
stmt.setString(5, m.getResultType());
stmt.executeUpdate();
ResultSet res = stmt.getGeneratedKeys();
if ( res.next() ) {
id = res.getInt(1);
stmt = dbConnection.prepareStatement(insertStr_parameter);
Iterator<String> ite = m.getParameters().iterator();
int i = 0;
while ( ite.hasNext() ) {
String param = ite.next();
i = i + 1;
stmt.clearParameters();
System.out.println("\n P1 = " + id );
stmt.setInt(1, id);
System.out.println("\n P2 = " + i );
stmt.setInt(2, i);
System.out.println("\n P3 = " + param );
stmt.setString(3, param);
System.out.println("\n Stmt = " + stmt.getParameterMetaData() );
stmt.executeUpdate();
/* id = - id; */
}
}
} catch (java.sql.SQLException e) {
System.out.println("Error in DAO.insertMethod(MethodRecord)");
printSQLException(e);
}
return id;
}
private void displayTables() {
try {
Statement stmt = dbConnection.createStatement();
ResultSet res;
System.out.println("\n --- Table: types ---");
res = stmt.executeQuery("SELECT * FROM types");
while ( res.next() ) {
System.out.print("id = " + res.getInt("id") + "; ");
System.out.print("name = '" + res.getString("name") + "'; ");
System.out.print("shortName = '" + res.getString("shortName") + "'");
System.out.println();
}
System.out.println("\n --- Table: methods ---");
res = stmt.executeQuery("SELECT * FROM methods");
while ( res.next() ) {
System.out.print("id = " + res.getInt("id") + "; ");
System.out.print("class = " + res.getInt("class") + "; ");
System.out.print("name = '" + res.getString("name") + "'; ");
System.out.print("isConstructor = '" + res.getString("isConstructor") + "'; ");
System.out.print("isStatic = '" + res.getString("isStatic") + "'; ");
System.out.print("returnType = " + res.getInt("returnType"));
System.out.println();
}
System.out.println("\n --- Table: methodParameters ---");
res = stmt.executeQuery("SELECT * FROM methodParameters");
while ( res.next() ) {
System.out.print("method = " + res.getInt("method") + "; ");
System.out.print("position = " + res.getInt("position") + "; ");
System.out.print("parameter = " + res.getInt("parameter"));
System.out.println();
}
} catch (java.sql.SQLException e) {
System.out.println("Error in DAO.displayTables()");
printSQLException(e);
}
}
public static void main(String[] arg) {
DAO dao = new DAO();
/* dao.saveDbProperties(); */
File dbDir = new File(dao.dbName);
if ( dbDir.isDirectory() ) {
dao.loadDB();
} else {
if ( dbDir.exists() ) {
System.out.println("Problem: a non-directory file as the same name as the DB.");
} else {
dao.createDB();
}
}
dao.insertType("int", "int");
dao.insertType("C1", "P.C1");
dao.insertType("C2", "P.C2");
dao.insertType("C3", "P.C3");
String[] params = {"int", "P.C2"};
dao.insertMethod(new MethodRecord(true, false, "P.C1", "P.C1", "C1", new Vector(Arrays.asList(params))));
dao.displayTables();
}
}package org.thinkcollabs.jmbrowser.db;
import java.util.*;
import java.io.*;
/**
* Objects of this class represent method records to be stored into
* (or retrieved from) the database.
* @author Nicolas Bonnel and Gurvan Le Guernic (ThinkCollabs)
*/
public class MethodRecord implements Serializable {
private boolean isConstructor;
private boolean isStatic;
private String result;
private String className;
private String methodName;
private Vector<String> parameters;
/**
* Constructor of method record
* @param constr must be true iff the method is a contructor
* @param stat must be true iff the method is static
* @param res full name of the type returned by the method
* @param cn full name of the class of the method
* @param mn method name
* @param p vector of full name of parameters type
*/
MethodRecord(boolean constr, boolean stat, String res, String cn, String mn, Vector<String> p) {
isConstructor = constr;
isStatic = stat;
result = res;
className = cn;
methodName = mn;
parameters = p;
}
/**
* Test if the method is a constructor
* @return true iff the method is a constructor
*/
public boolean isConstructor() { return isConstructor; }
/**
* Test if the method is static
* @return true iff the method is static
*/
public boolean isStatic() { return isStatic; }
/**
* Accessor of the type of the result returned by the method.
* @return a string corresponding to the full name of the type of the result
*/
public String getResultType() { return result; }
/**
* Accessor of the class in which the method is declared.
* @return a string corresponding to the full name of the class of the method
*/
public String getClassName() { return className; }
/**
* Accessor of the name of the method
* @return a string corresponding to the name of the method
*/
public String getMethodName() { return methodName; }
/**
* Accessor of the method parameters
* @return a vector of strings. Each string corresponds to the full name of the class of a parameter of the method
*/
public Vector<String> getParameters() { return parameters; }
/**
* Transform an object of this class into a string describing it.
* @return a string representing the object
*/
public String toString() {
String pStr = "";
int nbParams = parameters.size();
if (nbParams > 0) { pStr = parameters.get(0); }
for (int i = 1; i < nbParams; i++) {
pStr = pStr + ", " + parameters.get(i);
}
if (isConstructor) {
return ((String) "new " + className + "(" + pStr + ")");
} else {
String staticStr = "";
if (isStatic) { staticStr = "static "; }
return ((String) staticStr + result + " " + className + "." + methodName + "(" + pStr + ")");
}
}
}
