What will be the PreparedStatements for the below PostgreSQL table?

lets_code 0
Viewed 32

I want to use JDBC for preparedStatement in Java for inserting data into a PostgreSQL database. I have the following DDL

CREATE SEQUENCE serial_no;

CREATE TABLE distributors (
    did   DECIMAL(3)  DEFAULT NEXTVAL('serial_no'), 
    dname  VARCHAR(40) DEFAULT 'lusofilms'
);

I want to insert data into this table. Should the PreparedStatement be

INSERT INTO distributors (did,dname) VALUES (?,?);

And if so how do I do insertions in PreparedStatements for default values?

prepared-statement jdbc java postgresql
2 Answers
Do Nhu Vy0

I have no experience with default values (maybe you must set a java default constant), I normally leave them out of the SQL. For the rest you can get the generated key as follows:

String SQL = "INSERT INTO distributors (dname) VALUES (?)";
try (PreparedStatement statement = connection.prepareStatement(SQL,
               Statement.RETURN_GENERATED_KEYS)) {
    statement.setString(1, null); // Unsure, doubt this.
    int updateCount = statement.executeUpdate();
    try (ResultSet keyRS = statement.getGeneratedKeys()) {
        if (keyRSs.next()) {
            int dId = keyRS.getInt(1));
            ...
        }
    }
}

The try-with-resources syntax - though weird - ensures that statementand keyRS are both closed (they are AutoCloseable), whatever happens, break/return/exception. You could return inside the if.

Here the update count would be 1.

The result set covers having inserted multiple records, and possible generating more keys per record. You must request this in advance with an extra parameter to prepareStatement.

Joop Eggen0
Related