mysql float type

Question

I have a database order with price and deposit fields set to float type. I am also implemeting a java gui to search the order, the problem is when I try to search for the orders in the database I dont find anything because it saves price=55.0 when I convert from string to float and in the database it saves as 55. What is the problem? what type should I use to represent currency from the two sides, java side and mysql side?

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {        
    try{
        //collect arguments
        String category = this.jTextField8.getText();
        String pattern=this.jTextArea1.getText();
        String color=this.jTextArea2.getText();
        float price = Float.valueOf(this.jTextField14.getText()).floatValue();
        float deposit = Float.valueOf(this.jTextField15.getText()).floatValue();

        //create new order
        int row=this.customerSelectedRow;
        Order newOrder=new order(this.customerModel.dataVector.get(row),category,pattern,color,price,deposit);
        newOrder.search();           
        //refresh
        this.jDialogNewOrder.setVisible(false);

    }catch(Exception e){
         System.err.println (" Error message: " + e.getMessage ());
    }

}

here is the code for the search method

try {
                s = c.conn.prepareStatement("SELECT id FROM `"+this.table+
                        "` WHERE customerId=? AND category=? and pattern=? and color=? and deposit=? and price=?");
                s.setInt(1,this.customer.getId());
                s.setString (2, this.category);
                s.setString (3, this.pattern);
                s.setString (4, this.color);
                s.setFloat(5,this.deposit);
                s.setFloat(6,this.price);
                System.err.println(s.toString());
                ResultSet res = s.executeQuery();

                System.out.println("printing ids :");
                while (res.next()) {
                  int i = res.getInt(1);
                  //assigning the correct id to the inserted customer
                  this.id=i;
                  System.out.println("id" + "=" + i);
                }
            } catch (SQLException e) {
                System.err.println ("find id Error message: " + e.getMessage ());
                System.err.println ("find id Error number: " + e.getErrorCode ());

score 10 · Accepted Answer · edited May 23 '17 at 10:29

Are you dealing with currency with floating point values?

Please don't.

Floating point values cannot represent exact decimal values, as they are representation of binary fractions. Unless you want to end up with values such as $1.0000000000001, use a data type that is integral, such as a decimal type.

The reason why comparison using an == operator does not work as intended is because many numbers that can be represented in decimal cannot be represented in floating point, therefore, there is no exact match.

Here's a little example:

System.out.println(1.00000001f == 1.00000002f);
System.out.println(200000.99f - 0.50f);

Outputs:

true
200000.48

Those two examples should show that relying on floating point values for currency would not be such a good idea.

As for the storage of currency values, it appears that there is a DECIMAL type in MySQL as well, so I would think that would be a better choice, unless there is some CURRENCY type -- I'm not familiar enough with SQL databases to give any informed opinion here.

Here's a little information from the MySQL 5.1 Reference Manual, Section 10.2: Numeric Types:

The DECIMAL and NUMERIC data types are used to store exact numeric data values. In MySQL, NUMERIC is implemented as DECIMAL. These types are used to store values for which it is important to preserve exact precision, for example with monetary data.

Here's a related question:

score 1 · Answer 2 · answered Jun 16 '09 at 02:56

When you compare floating point numbers you must always compare them within a small range, say plus or minus half a penny when dealing with currency. If you compare them with exact equality, you will always get errors because floating point does not represent most decimal numbers precisely.

It is because of this problem that money is usually stored in mySql as DECIMAL rather than floating point. DECIMAL does not suffer from the same inaccuracies.

mysql float type

2 Answers2