CODEWITHSUNDEEP
javadatabasejdbc
Vini
Vini

Reputation: 119

How to get values of a column without knowing the column type

I am creating a query dynamically based on a user choosing fields to be displayed in the result. The problem is that when data is to be retrieved from the database I normally use getXXX() methods to retrieve the appropriate type. But in this case I do not know what the appropriate type is since the columns are randomly chosen.

Is there any way that I can get data in string format without specifying the data type XXX? I am using MySQL and Servlets.

Upvotes: 8

Views: 4240

Answers (3)

Nisarg Pipaliya
Nisarg Pipaliya

Reputation: 71

First, Get ResultRetMetadata then, use it's method named as getColumnTypeName(int indexOfCol) to get it's type, then compare it to the datatypes using if else and use appropriate method getxxx().

    import java.sql.Types;
        // your code....
          ResultSetMetaData rsmd = resultset.getMetaData();
          int n = rsmd.getColumnCount();
          for(int i = 1; i <=n; i++){
              if(rsmd.getColumnTypeName(i) == Types.INTEGER){
                    System.out.println(resultset.getInt(i));
               }
              else if(rsmd.getColumnTypeName(i) == Types.VARCHAR){
                    System.out.println(resultset.getString(i));
               }
              // and so on ...
          }
        }

http://www.java2s.com/example/java-api/java/sql/types/other-0.html Can Checkout this page for further reference :)

Upvotes: 2

Raffaele
Raffaele

Reputation: 20885

As far as I know, you can simply use getString() if you want the result in a Java String object. I think the MySQL driver takes care of deserializing the byte stream in whatever Java object you specify via getXXX(). Given the following init script:

DROP TABLE IF EXISTS thing;

CREATE TABLE thing (
  a_int int,
  a_string text,
  a_date date
);

INSERT INTO thing (a_int, a_string, a_date)
   VALUES ('1234', 'Hello world', CURRENT_DATE() );

and the code:

public static void main(String[] args) throws Exception {
    MysqlDataSource db = new MysqlDataSource();
    db.setDatabaseName("test");
    Connection conn = db.getConnection("test", "test");

    ResultSet res = conn.prepareStatement("SELECT * FROM thing").executeQuery();
    res.next();
    System.out.println("Get a int as a string: " + res.getString("a_int"));

}

It simply prints Get a int as a string: 1234. You can even get the a_date column as a Java int, and it's converted to 2013 on my machine. So it appears that the binding happens after the driver has retrieved an untyped stream of bytes from the server. Sometimes the conversion fails, for example if you try getInt() on the TEXT column.

Upvotes: -1

Denys S&#233;guret
Denys S&#233;guret

Reputation: 382130

You can use the generic getObject method :

Gets the value of the designated column in the current row of this ResultSet object as an Object in the Java programming language.

This method will return the value of the given column as a Java object. The type of the Java object will be the default Java object type corresponding to the column's SQL type, following the mapping for built-in types specified in the JDBC specification. If the value is an SQL NULL, the driver returns a Java null.

Upvotes: 13

Related Questions