What will be the PreparedStatements for the below PostgreSQL table?

Question

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?

Is there any particular reason you want to insert `did` manually even if it's a serial? If not, just remove it from the `insert`. — Federico klez Culloca, Sep 07 '22 at 07:21
Yes I want to test the database performance against various insert scenarios so have to use it @FedericoklezCulloca — lets_code, Sep 07 '22 at 07:25

Vy Do · Answer 1 · 2022-09-07T07:32:14.897

2

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

edited Sep 07 '22 at 07:32

answered Sep 07 '22 at 07:27

Vy Do

46,709
59
215
313

If I were to pass null for both dname and did should'nt the nextval and lusofilms value be used as opposed to solution 2 where we will always use nextval? Sorry to ask this but I am a beginner in databases and java so just to clear my doubts. – lets_code Sep 07 '22 at 07:37
You cannot insert null like this `INSERT INTO distributors ( ) VALUES ( );` . – Vy Do Sep 07 '22 at 07:40
I meant INSERT INTO distributors (did,dname) VALUES (?,? ) and later I can insert values from the sequence for did and lusofilms for dname if I pass null for either of them or my own values for both. – lets_code Sep 07 '22 at 08:11
You can do like this `INSERT INTO distributors (did,dname) VALUES (nextval('serial_no'), nextval('name_foo'));` – Vy Do Sep 07 '22 at 08:17

Joop Eggen · Answer 2 · 2022-09-07T07:42:31.703

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.

What will be the PreparedStatements for the below PostgreSQL table?

2 Answers2