Muhammad Catubig Muhammad Catubig - 4 months ago 10
MySQL Question

Why is this code inserting more than one row in the database?

I have this code that adds Product to Products table in the database but it is adding more than one row.

Here's the code:

public int addProduct(Products product, String supplierName) {

//find a product
String checkAllProducts = "SELECT * FROM products WHERE product_name = ?";

//Insert product and supplier id where supplier exist in suppliers table sql statement
String insertSql = "INSERT INTO products (product_name, product_type, supplier_id, number_of_stocks, price_per_unit, packaging_type) SELECT ?,?,suppliers.supplier_id,?,?,? FROM products, suppliers WHERE products.supplier_id = suppliers.supplier_id AND suppliers.supplier_name = ?";

//Get connection
Connection conn = DbUtil.getConnection();

//Resultset for checking existing products
ResultSet resultSet = null;

int inserted = 0;

try {

//Prepare check all products statement
allProducts = conn.prepareStatement(checkAllProducts);
allProducts.setString(1, product.getProductName());
resultSet = allProducts.executeQuery();

//If doesn't exist in products table
if (!resultSet.next()) {
//Prepare insert statement
addProduct = conn.prepareStatement(insertSql);
//Get product parameter's data
addProduct.setString(1, product.getProductName());
addProduct.setString(2, product.getProductType());
addProduct.setInt(3, product.getNumberOfStocks());
addProduct.setBigDecimal(4, product.getPricePerUnit());
addProduct.setString(5, product.getPackagingType());
addProduct.setString(6, supplierName);

//Confirm insert
int confirmation = JOptionPane.showConfirmDialog(null, "Are you sure you want to insert this product?", "Insert Confirm", JOptionPane.YES_NO_OPTION, JOptionPane.WARNING_MESSAGE);
if (confirmation == JOptionPane.YES_OPTION) {
//execute insert
inserted = addProduct.executeUpdate();
}

}//Else don't insert and show error messages.
else {
JOptionPane.showMessageDialog(null, "Product already exists.", "Invalid insert.", JOptionPane.ERROR_MESSAGE);
}

} catch (SQLException ex) {
Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
} finally {
DbUtil.close(conn, allProducts, resultSet);
DbUtil.close(conn, addProduct, resultSet);
}

return inserted;
}


As you can see in the code above, I'm checking if a product doesn't exist in the table then insert with confirmation. It's successfully adding but it's adding more than one row. For example, assume that I have initialised the productDao object and I test it out in this way:
productDAO.addProduct(new Products("Hotdogs", "Full", 55, new BigDecimal(0.30), "Box"), "Wing Yip");
. After doing that, it inserted 14 of these as you can see in the image's link below;

14 Duplicate Rows

Does anyone know why this is happening? Please let me know thanks.

Answer

The second part of your insert statement is a select statement:

SELECT
    ?, ?,
    suppliers.supplier_id,
    ?, ?, ? 
FROM products, suppliers
WHERE 
    products.supplier_id = suppliers.supplier_id 
    AND suppliers.supplier_name = ?

This has the ability to select all records that have that supplier name, which is 14 records.

To fix this add LIMIT 1 to your select statement.

INSERT INTO products (
    product_name, 
    product_type,
    supplier_id,
    number_of_stocks,
    price_per_unit, 
    packaging_type
) SELECT 
    ?, ?,
    suppliers.supplier_id,
    ?, ?, ?
FROM products, suppliers
WHERE
    products.supplier_id = suppliers.supplier_id 
    AND suppliers.supplier_name = ?
LIMIT 1