#Trouble with commit mysql

1 messages · Page 1 of 1 (latest)

honest capeBOT
#

<@&987246584574140416> please have a look, thanks.

native oasis
# 
public int getProductID(String productName, String productCategory, String productBrand, String productInformation, String productImagePath) {
        try (PreparedStatement preparedStatement = connection.prepareStatement("SELECT " + COLUMN_PRODUCT_ID + " FROM " + TABLE_PRODUCT + " WHERE " + COLUMN_PRODUCT_NAME + " = ? AND " + COLUMN_PRODUCT_CATEGORY + " = ? AND " + COLUMN_PRODUCT_BRAND + " = ? AND " + COLUMN_PRODUCT_INFORMATION + " = ? AND " + COLUMN_PRODUCT_IMAGE_PATH + " = ?")) {

            preparedStatement.setString(1, productName);
            preparedStatement.setString(2, productCategory);
            preparedStatement.setString(3, productBrand);
            preparedStatement.setString(4, productInformation);
            preparedStatement.setString(5, productImagePath);

            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                return resultSet.getInt(1);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
#

method 1 ^

# 
public void insertNewProduct(String productName, String productCategory, String productBrand, String productInformation, Double productPrice, Double productMinPrice, String productImagePath) {

        try (PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO " + TABLE_PRODUCT + " (" + COLUMN_PRODUCT_NAME + ", " + COLUMN_PRODUCT_CATEGORY + ", " + COLUMN_PRODUCT_BRAND + ", " + COLUMN_PRODUCT_INFORMATION + ", " + COLUMN_PRODUCT_PRICE + ", " + COLUMN_PRODUCT_MIN_PRICE + ", " + COLUMN_PRODUCT_IMAGE_PATH + ") VALUE (?, ?, ?, ?, ?, ?, ?)")) {

            preparedStatement.setString(1, productName);
            preparedStatement.setString(2, productCategory);
            preparedStatement.setString(3, productBrand);
            preparedStatement.setString(4, productInformation);
            preparedStatement.setBigDecimal(5, BigDecimal.valueOf(productPrice));
            preparedStatement.setBigDecimal(6, BigDecimal.valueOf(productMinPrice));
            preparedStatement.setString(7, productImagePath);

            preparedStatement.execute();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
#

method 2 ^

# 
public void insertProductQuantity(String productName, String productCategory, String productBrand, String productInformation, String productImagePath, int quantity) {

        int productID = getProductID(productName, productCategory, productBrand, productInformation, productImagePath);
        try (PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO " + TABLE_INVENTORY + " (" + COLUMN_INVENTORY_PRODUCT_ID + ", " + COLUMN_INVENTORY_PRODUCT_QUANTITY + ") VALUE (?, ?)")) {
            preparedStatement.setInt(1, quantity);
            preparedStatement.setInt(2, productID);

            preparedStatement.execute();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
#

method 3 ^

#

i call the 2nd then 3rd method

#

but for some reason 3rd method returns an error saying that he cant find the ID that was just inserted

#

the 2nd method does its job and it inserts the data however the 3rd method cant retrieve it

#

heres the error

java.sql.SQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails
native oasis
#

Trouble with commit mysql

native oasis
#

@honest cape

#

mistake was in method 3 pepekek
i reversed these 2
preparedStatement.setInt(1, quantity);
preparedStatement.setInt(2, productID);
fml