What will be the PreparedStatements for the below PostgreSQL table?-CodePudding

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?

CodePudding user response：

Solution 1

INSERT INTO distributors (dname) VALUES (?);

use trigger. Reference document: https://www.postgresql.org/docs/current/sql-createtrigger.html

Solution 2

INSERT INTO distributors (did,dname) VALUES (nextval('serial_no'), ?);

See https://stackoverflow.com/a/21397740/3728901

CodePudding user response：

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.