Problem with add data to mySql with String.format in Java

Question

I have problem when i try to add data to mySql Table with String.format.

MySql table:

CREATE TABLE Product (
Product_id int AUTO_INCREMENT,
Name varchar(255),
Description varchar(255),
Price double(9,2),
Primary Key(Product_id)

);

Method, that adds a record to the table. Problem is with column price. In mysql i defined it: Double(9,2). When I add it like this, everything works fine.

public void insertProduct(Product product) {

    this.databaseService.performDML(
            String.format("INSERT INTO product (name,description,price) " +
                            "VALUES ('%s', '%s', '"+product.getPrice()+"')",
                    product.getName(),
                    product.getDescription())
    );
}

but when adding looks like this, i have exception: "Data truncated for column 'Price' at row 1"

 public void insertProduct(Product product) {
    this.databaseService.performDML(
            String.format("INSERT INTO product (name,description,price) " +
                            "VALUES ('%s', '%s', '%f')",
                    product.getName(),
                    product.getDescription(),
                    product.getPrice())
    );
}

Can someone tell me the difference, and how i can add data properly with String.format?

I'm actually surprised the first one works since the price field takes a numerical value and I always thought that table fields declared as numerical data types shouldn't be enclosed in apostrophes ( ' ... ' ). Try it like this: String.format("INSERT INTO product (name,description,price) VALUES ('%s', '%s', %.3f);", product.getName(), product.getDescription(), product.getPrice()));. On the same token, I've never seen anyone use the String#format() method to build an SQL statement before. I personally have always used Prepared Statements (in Java). It takes care of the apostrophe game. — DevilsHnd - 退した
– DevilsHnd - 退した, Commented Jun 10, 2022 at 18:55

Nightlord · Accepted Answer · 2022-06-10 18:40:55Z

1

Here's the conclusion, use prepared statements instead of building a string yourself for SQL statements.

But if you are really wondering about the difference between the two strings... the main difference is that the price gets padding zeros after the point for doubles if you use String.format() method. You could simply print the two strings in console to see the difference.

For example, for the price is 12.345:

String.format("INSERT INTO product (name,description,price) " +
"VALUES ('%s', '%s', '"+product.getPrice()+"')",
product.getName(),
product.getDescription())

The first string gets "12.345".

String.format("INSERT INTO product (name,description,price) " +
"VALUES ('%s', '%s', '%f')",
product.getName(),
product.getDescription(),
product.getPrice())

The second string gets "12.345000".

answered Jun 10, 2022 at 18:40

Nightlord

565 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem with add data to mySql with String.format in Java

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related