How do I determine retrieved value type in Java?

Question

Having searched unsuccessfully for days, I thought I would ask.

I am retrieving a value from a table, whose columns are all varchar. How do we go about identifying the type of value. For e.g.

accountnumber may have values such as 123456, 123456:78910

    ...
    try(ResultSet rs = stmt.executeQuery("select * from table")){
      if(rs.next()){
        //Cannot use anything other than 'getString'
        String var = rs.getString("accountnumber");                 
        //Determine if var is an Integer
        System.out.println(var instanceof Integer)  //returns false because 'var' is of type String.
      }
    }
   ...

Any pointers are much appreciated.

Are you asking if the String could be converted to a number or not? Try parsing it. — Peter Lawrey, Sep 29 '16 at 09:15
@PeterLawrey - `parse` seems to be the way to go. I completely forgot about that. — Bala, Sep 29 '16 at 09:29
Note Integer.parseInt(String0 will only parse `int` values. You might want Long.parseLong(String) or BigInteger.valueOf(String) for larger integers. — Peter Lawrey, Sep 29 '16 at 09:38

score 2 · Answer 1 · answered Sep 29 '16 at 09:23

The thing works as intended. The columns are typed as varchar in the schema, so the database and the Java SQL interface can't know if you actually have numbers or names or dates or whatever stored inside. Only getString should work.

It's up to you to try to parse the string results in ways that make sense.

For example, the Integer.parseInt method would transform a string to an integer, or throw an exception if it cannot. There's equivalent versions for Long or Double etc.

However, looking at the example accountnumbers, they aren't really integers, but rather have a more complex structure. You might want to model that with a class with two fields (primaryAccountNumber, theOtherAccountNumber) etc, and it should take care of parsing.

On a different note, you should never select * from a database, but rather be explicit about the fields that you need. This protects against schema changes which remove fields you were dependent on (the select will fail, instead of some code later down the line), or pulling in too much data and just using a bit of it.

score 1 · Answer 2 · answered Sep 29 '16 at 09:18

1

ResultSet#getMetaData(); will give you the required informations. ResultMetaData has some methods that will help eg. getColumnType(int) or getColumnClassName(int)

answered Sep 29 '16 at 09:18

Antoniossss

31,590
6
57
99

This won't work because all the columns are varchar. Even a date value, numbers are all stored in varchar. – Bala Sep 29 '16 at 09:22
@Bala then you have big, big problem. You will have to manually eg. try to convert value to Integer, if it fails, try to convert/read is as date, if it fails, ..... so on so on till you finally run out of possible data types and MAYBE you will succeed. – Antoniossss Sep 29 '16 at 09:23
@Bala my best bet would be to ask database designer how to "guess column type" efficiently ;) – Antoniossss Sep 29 '16 at 09:27

score 0 · Answer 3 · answered Sep 29 '16 at 09:22

0

You can use pattern matching to check its a number or a string.

  String var = rs.getString("accountnumber");
  Matcher ss1 = Pattern.compile("([0-9]*)").matcher(var);
  if (ss1.matches()) {
      // its a number
  } else {
      // its a string
  }

answered Sep 29 '16 at 09:22

bob

4,595
2
25
35

tht can be get from this ques : http://stackoverflow.com/questions/12643009/regular-expression-for-floating-point-numbers – bob Sep 29 '16 at 09:30
For checking floating point number , just the regular expression needs to be changed, the regex can be get from http://stackoverflow.com/questions/12643009/regular-expression-for-floating-point-numbers – bob Sep 29 '16 at 09:32
@Antoniossss It's known to be an integer, but it could be negative. – Peter Lawrey Sep 29 '16 at 09:39

How do I determine retrieved value type in Java?

3 Answers3