CODEWITHSUNDEEP
postgresqljdbc
Y. Leonce Eyog
Y. Leonce Eyog

Reputation: 903

PostgreSQL Insert multiple row if not exist

I'm trying to insert rows into a two column table,PRODUCTS_CAT, if the rows don't exist already, through my java application. The rows are read from xml queues.

This is the method in which I get the queues.

public void syncProduct(final ProductInfoExt prod) throws BasicException {
    Transaction t = new Transaction(s) {
        public Object transact() throws BasicException {
            // Sync the Product in a transaction

            // Try to update
            if (new PreparedSentence(
                s,
                "UPDATE PRODUCTS SET REFERENCE = ?, CODE = ?, NAME = ?, PRICEBUY = ?, PRICESELL = ?, CATEGORY = ?, TAXCAT = ?, IMAGE = ? WHERE ID = ?",
                SerializerWriteParams.INSTANCE).exec(new DataParams() {
                    public void writeValues() throws BasicException {
                        setString(1, prod.getReference());
                        setString(2, prod.getCode());
                        setString(3, prod.getName());
                        // setBoolean(x, p.isCom());
                        // setBoolean(x, p.isScale());
                        setDouble(4, prod.getPriceBuy());
                        setDouble(5, prod.getPriceSell());
                        setString(6, prod.getCategoryID());
                        setString(7, prod.getTaxCategoryID());
                        setBytes(8, ImageUtils.writeImage(prod.getImage()));
                        // setDouble(x, 0.0);
                        // setDouble(x, 0.0);
                        setString(9, prod.getID());
                    }
            }) == 0) {
                // If not updated, try to insert
                new PreparedSentence(
                        s,
                        "INSERT INTO PRODUCTS (ID, REFERENCE, CODE, NAME, ISCOM, ISSCALE, PRICEBUY, PRICESELL, CATEGORY, TAXCAT, IMAGE, STOCKCOST, STOCKVOLUME) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
                        SerializerWriteParams.INSTANCE)
                        .exec(new DataParams() {
                            public void writeValues() throws BasicException {
                                setString(1, prod.getID());
                                setString(2, prod.getReference());
                                setString(3, prod.getCode());
                                setString(4, prod.getName());
                                setBoolean(5, prod.isCom());
                                setBoolean(6, prod.isScale());
                                setDouble(7, prod.getPriceBuy());
                                setDouble(8, prod.getPriceSell());
                                setString(9, prod.getCategoryID());
                                setString(10, prod.getTaxCategoryID());
                                setBytes(11, ImageUtils.writeImage(prod
                                        .getImage()));
                                setDouble(12, 0.0);
                                setDouble(13, 0.0);
                            }
                        });
            }
                    /* Insert in catalog */
            new StaticSentence(
                    s,
                    /*
                     * leyonce - Insert into the product catalog if the
                     * products aren't already there
                     */
                    "INSERT INTO  PRODUCTS_CAT(PRODUCT,CATORDER) SELECT  ?,NULL WHERE NOT EXISTS (SELECT (?,NULL) FROM PRODUCTS_CAT)  ",
                    SerializerWriteString.INSTANCE).exec(prod.getID());
            return null;
        }
    };
    t.execute();
}

The first row is inserted and all other rows are not. It apparently does the insert if the table is empty.

Upvotes: 0

Views: 391

Answers (1)

Volune
Volune

Reputation: 4339

(SELECT (?,NULL) FROM PRODUCTS_CAT) always exists (return a row) as soon as there is a row in PRODUCTS_CAT. To test if this id is already inserted, you should do:

SELECT 1 FROM PRODUCTS_CAT WHERE PRODUCT = ?

Full insert statement:

INSERT INTO  PRODUCTS_CAT(PRODUCT,CATORDER) SELECT  ?,NULL WHERE NOT EXISTS (SELECT 1 FROM PRODUCTS_CAT WHERE PRODUCT = ?)

Also you need to bind the id to each question mark "?":

new StaticSentence(
    s,
    "INSERT INTO PRODUCTS_CAT(PRODUCT,CATORDER) SELECT ?,NULL WHERE NOT EXISTS (SELECT 1 FROM PRODUCTS_CAT WHERE PRODUCT = ?)",
    new SerializerWriteBasic(Datas.STRING, Datas.STRING)
    ).exec(prod.getID(), prod.getID());

Upvotes: 2

Related Questions