Getting sequence from SQL INSERT with JDBC

Question

So I'm trying to add to database using JDBC from files. And now when INSERTING I need to get the ID before I insert and it would be better If the database would still use the sequence.

In folder resources I have a file called insert_plant.sql that contains this query:

INSERT INTO PLANTS (id, plantname, species)
  VALUES (NEXT VALUE FOR sequence, ?, null);

And the table is generated with this:

DROP SCHEMA public CASCADE;

CREATE SEQUENCE sequence START WITH 1;

CREATE TABLE PLANTS (
   id BIGINT NOT NULL PRIMARY KEY,
   plantname VARCHAR(255) NOT NULL,
   species VARCHAR(255) NULL,
);

And now in Java I am calling this:

public static void insertIntoOrderTable(BasicDataSource basicDataSource, String plantname) throws  SQLException{
    Connection conn = null;
    PreparedStatement stmt = null;

    try {
        conn = basicDataSource.getConnection();
        stmt = conn.prepareStatement(Util.readFileFromClasspath("insert_plant.sql"));
        stmt.setString(1, plantname);
        stmt.executeUpdate();

        //Below is the line 57 (in error)
        ResultSet rs = stmt.executeQuery("SELECT sequence.NEXTVAL FROM PLANTS");
        if(rs.next()){
            System.out.println("ID" + rs.getInt(1));
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

And the error I get:

java.sql.SQLFeatureNotSupportedException: feature not supported
at org.hsqldb.jdbc.JDBCUtil.notSupported(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at database.PlantDao.insertIntoOrderTable(PlantDao.java:57)
at database.PlantDao.main(PlantDao.java:19)

So the question

It's probably not the source of your problem but it's not a good idea to use reserved keywords for variable names, naming your sequence sequence in this case. — StephaneM
– StephaneM, Commented Oct 15, 2018 at 14:37

user9692854 · Accepted Answer · 2018-10-15 15:34:15Z

1

As already commented by ´The Impaler` there is no need to execute a second statement, with JDBC you can use

PreparedStatement stmt = conn.prepareStatement(sql, new String[]{"id"});

And to get the value out of it:

ResultSet rs = stmt.getGeneratedKeys();
if(rs.next()){
    System.out.println(rs.getLong(1));
}

So your method would look something like this:

public static void insertIntoOrderTable(BasicDataSource basicDataSource, String plantname) throws  SQLException{
    Connection conn = null;
    PreparedStatement stmt = null;

    try {
        conn = basicDataSource.getConnection();
        stmt = conn.prepareStatement(Util.readFileFromClasspath("insert_plant.sql"), new String[]{"id"});
        stmt.setString(1, plantname);
        stmt.executeUpdate();

        ResultSet rs = stmt.getGeneratedKeys();
        if(rs.next()){
            System.out.println(rs.getLong(1));
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

answered Oct 15, 2018 at 15:34

user9692854

Sign up to request clarification or add additional context in comments.

1 Comment

sproketboy Over a year ago

This still does not work with MSSQL github.com/Microsoft/mssql-jdbc/issues/656 :(

Collectives™ on Stack Overflow

Getting sequence from SQL INSERT with JDBC

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related