CODEWITHSUNDEEP
javasqlh2
alovaros
alovaros

Reputation: 476

h2 getGeneratedKeys throws exception

I try to put some Data in my H2 database but I'm a total noob in databases so it throws error over error since more than a hour. Normaly I can fix it somehow but now I got a new problem I try to use getGeneratedKeys() first I tried to use AUTO_INCREMENT(1,1) but that didn't works too function but it won't work rigth.

The exception my programm throws is

org.h2.jdbc.JdbcSQLException: Funktion "GETGENERATEDKEYS" nicht gefunden Function "GETGENERATEDKEYS" not found; SQL statement: insert into logTbl values( getGeneratedKeys(),Webservice->startThread0: Thread0) [90022-173]

an my database function looks like this

    public void createTable(String Log) {

    try {
        Class.forName("org.h2.Driver");
    } catch (ClassNotFoundException e) {
        System.err.println("TREIBER FEHLER");
        e.printStackTrace();
    }
    Connection conn = null;
    try {
        conn = DriverManager.getConnection("jdbc:h2:~/DBtest/Logs");

        Statement stat = conn.createStatement();


        stat.execute("CREATE TABLE IF NOT EXISTS logTbl(ID INT PRIMARY KEY, LOG VARCHAR(255))");

        //stat.execute("insert into test values(1, 'Hello')");
        for (int i = 0; i < 20; i++) {
            stat.execute("insert into logTbl values( getGeneratedKeys()," + Log + ")");
        }
            stat.close();
            conn.close();

    } catch (SQLException e) {
        System.err.println("SQL FEHLER");
        e.printStackTrace();
    }
}

hope you can help me to fix my error as I said I'm totaly new and just had some code example as "tutorial" because I don't found a good tutorial

Upvotes: 1

Views: 2349

Answers (1)

user330315
user330315

Reputation:

If you want to automatically generate primary key values, you need to first change the definition of your table:

CREATE TABLE IF NOT EXISTS logTbl
(
  ID integer AUTO_INCREMENT PRIMARY KEY, 
  LOG VARCHAR(255)
);

You should also use a PreparedStatement rather than concatenating values.

So your Java code would look something like this:

String insert = "insert into logTbl (log) values(?)";
PreparedStatement pstmt = connection.prepareStatement(insert,  Statement.RETURN_GENERATED_KEYS);
pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();
long id = -1;
while (rs.next()) 
{
   rs.getLong(1);
}

It might be that you need to use the overloaded version of prepareStatement() where you supply the column to be returned. Not sure which one works with H2:

prepareStatement(insert, new String[] {"ID"});

Btw: there is nothing "magic" about 255 as the length of a varchar column. There is no performance difference between varchar(500), varchar(20)or varchar(255). You should use the length that you expect you need, not some "magic" limit you think performs better.

Upvotes: 4

Related Questions