Question 114777 in PostgreSQL Question posted in
The official documentation can be found here.

What will be the PreparedStatements for the below PostgreSQL table?

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?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search