import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;

public class FloatTest {

	public static Connection con;
	public static List<Float> floats = new LinkedList<Float>();
	public static List<Double> doubles = new LinkedList<Double>();

	public static void main(String[] args) {
		try {			
			BasicDataSource dataSource = new BasicDataSource();
			dataSource.setDriverClassName("com.mysql.jdbc.Driver");
			dataSource.setUrl("jdbc:mysql://localhost/float_test?user=test&password=test&useUnicode=true&characterEncoding=utf8");
			con = dataSource.getConnection();
	
			// Delete all existing data
			con.prepareStatement("DELETE FROM numbers").executeUpdate();
			
			// Put some data
			putDouble(Math.PI);
			putDouble(Math.PI*1000d);
			putDouble(3.14159673456d);
			putDouble(3.14158473456d);
			putFloat(3.14159f);
			putFloat(2.15625f);
			putFloat(2.0f + 0.5f + 0.125f + 0.0625f + 0.03125f);
			putFloat(2.0f + 0.5f + 0.125f + 0.0625f + 0.03125f + 0.015625f);
			putDouble(2.0f + 0.5f + 0.125f + 0.0625f + 0.03125f + 0.015625f + 0.0078125d + 0.000244140625d);
			putDouble(0.00001234234535d);
			
			
			// Show all
			ResultSet rSet = con.prepareStatement("SELECT float_val, double_val, round(float_val,50), round(double_val,100) FROM numbers ORDER BY id").executeQuery();
			Iterator<Float> fitr = floats.iterator();
			Iterator<Double> ditr = doubles.iterator();
			while (rSet.next())
			{
				float real_float = fitr.next().floatValue();
				double real_double = ditr.next().doubleValue();
				
				float db_float = rSet.getFloat(1);
				double db_double = rSet.getDouble(2);
				
				float db_float_round = rSet.getFloat(3);
				double db_double_round = rSet.getDouble(4);
				
				System.out.println("Original: \t" + real_float + "\t"+ real_double);
				System.out.println("DB:");
				System.out.println( "\t\t" + db_float+ "\t" + db_double);
				System.out.println( "\t\t" +(real_float-db_float) + "\t" + (real_double-db_double));
				System.out.println("Rounded:");
				System.out.println( "\t\t" + db_float_round+ "\t" + db_double_round);
				System.out.println( "\t\t" + (real_float-db_float_round) + "\t" + (real_double-db_double_round));
				
				System.out.println("--------------------------------------------------------------------------------------------");
			}
			
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		
	}
	
	public static void putFloat(float val) throws SQLException
	{
		putMixed(val, val);
	}
	
	public static void putDouble(double val) throws SQLException
	{
		putMixed((float)val, val);
	}

	public static void putMixed(float fval, double dval) throws SQLException
	{
		floats.add(new Float(fval));
		doubles.add(new Double(dval));
		
		String sql_query = "INSERT INTO numbers (float_val, double_val) VALUES (?,?)";
		PreparedStatement stmt = con.prepareStatement(sql_query);
		
		stmt.setFloat(1, fval);
		stmt.setDouble(2, dval);
		stmt.executeUpdate();
	}
}