Oracle JDBC Workaround für getGeneratedKeys()

[Thomas Darimont]

Hallo!

Oracle unterstützt mit dem ojdbc14.jdbc Treiber der Oracle 9.x und Oracle 10.x beiliget leider nicht das getGeneratedKeys() Konstrukt der PreparedStatement-Klasse.

Deshalb hier mal ein Beispiel für einen kleinen Workaround

/**
 * 
 */
package de.tutorials;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import oracle.jdbc.pool.OracleDataSource;

/**
 * @author Tom
 * 
 */
public class OracleJDBCExample {

	/**
	 * SQL> create table foo(id int not null, data varchar(32));
	 * 
	 * Table created.
	 * 
	 * SQL> create sequence seq_foo start with 1 increment by 1;
	 * 
	 * Sequence created.
	 * 
	 * @param args
	 */
	public static void main(String[] args) throws Exception {
		OracleDataSource ods = new OracleDataSource();
		ods.setServerName("localhost");
		ods.setPortNumber(1521);
		ods.setUser("scott");
		ods.setPassword("foobar");
		ods.setDriverType("thin");
		ods.setDatabaseName("orcl");

		Connection con = ods.getConnection();
		try {
			// PreparedStatement pstmt = con.prepareStatement("INSER INTO foo VALUES (seq_foo.nextval,?)",Statement.RETURN_GENERATED_KEYS);
			// pstmt.setString(1,"Foooooo");
			// --> Führt zu:
			// Exception in thread "main" java.sql.SQLException: Nicht unterstützte Funktion

			//Hier der Workaround
			Statement stmt = con.createStatement();
			ResultSet rs = stmt
					.executeQuery("select seq_foo.nextval from dual");
			rs.next();
			
			int generatedKey = rs.getInt(1);
			stmt.close();
			rs.close();
			

			PreparedStatement pstmt = con
					.prepareStatement("INSERT INTO foo VALUES (?,?)");
			pstmt.setInt(1,generatedKey);
			pstmt.setString(2,"Foo:"+System.currentTimeMillis());
			
			pstmt.execute();
			pstmt.close();

		} finally {
			if (con != null) {
				con.close();
			}
		}
	}
}

Gruß Tom