• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC returning values using Scientific Notation

 
Dar Var
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am selecting from the database a value using

select
sum(myTable.myColumn) col1
from myTable

The value coming back using dbaccess is: 113297618.3795
The value coming back using JBDC is: 1.1329761837946805E8

Notice the JDBC format using Expontenials e.g. E8

How do get rid of this Scientific Notation
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
JDBC provides database connectivity it doesn't reformat numbers. The program is taking the value from a JDBC recordset and printing it in scientific notation format.

You can use java.text.NumberFormat to print the value in a different format.
[ September 15, 2006: Message edited by: Scott Johnson ]
 
Dar Var
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using DbVisualizer and it is nor doing any conversion
 
Dar Var
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is my problem anyway:

1. I created a table in my Informix database:
CREATE TABLE scentific_notation(floatvalues float)

2. I inserted these values:
insert into scentific_notation values(2835121.634679258)
insert into scentific_notation values(978696.1697495114)
insert into scentific_notation values(2307377.07214382)
insert into scentific_notation values(2336955.5022556856)
insert into scentific_notation values(1888293.6396578003)
insert into scentific_notation values(2304406.572121913)

3. I do a sum of the values
SELECT sum(floatvalues)FROM scentific_notation


Result from JDBC:
1.265085059060799E7

Result from dbaccess:
12650850.59061
 
Paul Clapham
Sheriff
Posts: 21322
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
JDBC just returns a number. And numbers don't have any inherent format in Java. If you want to display it in some particular format (scientific or otherwise) then use a NumberFormat object to do that.

Also, if you don't like the way that this DbVisualizer thing is formatting your numbers, then your complaint is about DbVisualizer and not about JDBC. JDBC just returns a number, it doesn't format the number.
 
Dar Var
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well I decided to create my own JDBC class and this brings it back with the Scentific notation also so its not DbVisualizer.

Could it possibly be the Informix JDBC driver?

Does a driver do any formatting of large values
 
Dar Var
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Further investigation shows that this is not Informix or the informix driver either this behaviour is definitely without doubt JDBC. This is the same for mySQL db.

I added the following large value to a column using Database Type float 126508500000000000.59061000000000000

Coming back using JDBC is:
1.26508500004225248E17

I'm am doing no formatting here.

[ September 18, 2006: Message edited by: Dar Var ]
[ September 18, 2006: Message edited by: Dar Var ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Further investigation shows that this is not Informix or the informix driver either this behaviour is definitely without doubt JDBC. This is the same for mySQL db.

I beg to differ, JDBC plays no part here. Re-read Paul Clapham's comment and try running this program:


How is the floating point number rendered?
[ September 18, 2006: Message edited by: Paul Sturrock ]
 
Dar Var
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is my code:



The data is stored as a float in the db
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So the getObject() method of ResultSet is probably returning a Float or a Double (you could easily test this in you code if you want to find out). And you are adding this value to a StringBuffer which you then output to the console, without imposing any formatting (i.e. just using the default formatting provided by the JRE).

So, update my test class to do this:


And see what format the number takes. Then re-read Paul Clapham's post (paying particular attention to the line "...numbers don't have any inherent format in Java") and read the JavaDocs for the NumberFormat class.
 
Sivaraman Lakshmanan
Ranch Hand
Posts: 231
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Did you tried using java.math.BigDecimal class?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Sivaraman Lakshmanan:
Hi,
Did you tried using java.math.BigDecimal class?


Changing the type is not addressing the underlying problem; that numbers in Java have no inherent format. If you want a number to be displayed in a particular format, you are going to have to define that format.
 
Dar Var
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay I'll to format each value coming back. Pitty it doesn't keep the existing format in the db.

I hope formating a large number of values from the database is performant.
[ September 20, 2006: Message edited by: Dar Var ]
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Pitty it doesn't keep the existing format in the db.

Databases, like Java don't hold any information about the format of a number. Why should they? In mathematics the format is irrelevant.


I hope formating a large number of values from the database is performant.

I would be amazed if this had any noticable effect on the performance of your application. But then you are in the position to see - test it and find out.
[ September 20, 2006: Message edited by: Paul Sturrock ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic