Getting error: "Incorrect decimal value: 'Apple Watch Series 6' for column 'price' at row 1." The wrong value is being set as price. what can i do?

Question

I'm making a wishlist website in Java, when I try to update a wish, there's an error with the price. I'm working in thymeleaf and springboot. heres is my form in my html(price and description/text is where i face a problem):

<form class="update" action="/wishlist/wish/update" method="post">
    <table>
        <tr>
            <td>
                <label for="name">Name:</label>
                <input id="name" type="text" name="name" th:value="${wish.name}">
            </td>
        </tr>
        <tr>
      **      <td>
                <label for="description">Description:</label>
                <input id="description" type="text" name="text" th:value="${wish.text}">
            </td>
        </tr>
        <tr>
            <td>
                <label for="price">Price:</label>
                <br>
                <input id="price" type="text" name="price" th:value="${wish.price}">
            </td>
        </tr>**
        <tr>
            <td>
                <label for="link">Link:</label>
                <input id="link" type="text" name="link" th:value="${wish.link}">
            </td>
        </tr>
        <tr>
            <td>
                isBought:
                <input type="radio" id="isBoughtFalse" name="isBought" value="false" th:checked="${wish.isBought == false}">
                <label for="isBoughtFalse">False</label>
                <input type="radio" id="isBoughtTrue" name="isBought" value="true" th:checked="${wish.isBought == true}">
                <label for="isBoughtTrue">True</label>
            </td>
        </tr>
    </table>
    <input type="hidden" name="id" th:value="${wish.id}">
    <button type="submit">Update</button>
    <button type="submit" form="cancel">Cancel</button>
</form>

here is the method in my repository class responsible for updating the wish in Mysql DB:

public void updateWish(int id, String name, String text, double price, String link, boolean isBought) {
        System.out.println("price: " + price);
        System.out.println("desc: " + text);
        System.out.println("name: " + name); /* added this to see if the right values got set in the parameters and they did*/
        System.out.println("link: " + link);
        System.out.println("isBought: " + isBought);
        String query = "UPDATE wish SET name = ?, text = ?, price = ?, link = ?,  is_bought = ? WHERE id = ?;";
        jdbcTemplate.update(query,id, name, text, price, link, isBought);
    }

heres MySQL script for DB:

CREATE TABLE user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE wishlist (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    name VARCHAR(100) NOT NULL,
    creation_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_wishlist
        FOREIGN KEY (user_id)
        REFERENCES User(id)
        ON DELETE CASCADE
);

CREATE TABLE wish (
    id INT PRIMARY KEY AUTO_INCREMENT,
    wishlist_id INT,
    name varchar(55),
    text VARCHAR(255) NOT NULL,
    price decimal(10,2),
    link VARCHAR(255),
    is_bought BOOLEAN DEFAULT FALSE, 
    is_reserved_by_user_id VARCHAR(255),
    CONSTRAINT fk_wishlist_wish
        FOREIGN KEY (wishlist_id)
        REFERENCES wishlist(id)
        ON DELETE CASCADE
);

the error I get is:

There was an unexpected error (type=Internal Server Error, status=500). org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [UPDATE wish SET name = ?, text = ?, price = ?, link = ?, is_bought = ? WHERE id = ?;]; SQL state [HY000]; error code [1366]; Incorrect decimal value: 'Apple Watch Series 6' for column 'price' at row 1

it seems to me that I try putting the text parameter as price?

I have changed my database to have price double instead of decimal. that didn't work. I've tried using text as a string but that causes a different problem. Im at a loss as to why it's not setting the price parameter as the price in DB. I checked that the parameters have the right values and they do.

Answer 1

You have mismatched the order of the query:

        String query = "UPDATE wish SET name = ?, text = ?, price = ?, link = ?,  is_bought = ? WHERE id = ?;";
        jdbcTemplate.update(query,id, name, text, price, link, isBought);

Take to attention: the first parameter in the query is name but in the update method is id, so JDBC tries to put the link value into the price column.

Try to put parameters appropriately:

public void updateWish(int id, String name, String text, double price, String link, boolean isBought) {
    String query = "UPDATE wish SET name = ?, text = ?, price = ?, link = ?,  is_bought = ? WHERE id = ?";
    jdbcTemplate.update(query, name, text, price, link, isBought, id);
}