jondinham jondinham - 4 months ago 20
SQL Question

How to fetch entire row as array of objects with JDBC

I have to make a 'query' method for my class which accesses MySQL thru' JDBC.

The input parameter to the method is a full SQL command (with values included), so I don't know the names of columns to fetch out.

Some of the columns are strings, some others are integers, etc.

The method needs to return the value of type

ArrayList<HashMap<String,Object>>

where each HashMap is 1 row, and the ArrayList contains all rows of result.

I'm thinking of using
ResultSet.getMetaData().getColumnCount()
to get the number of columns then fetch cell by cell out of the current row, but is this the only solution? any better ones?

Answer

I have the example code here, just in case anybody need it. ('Con' in the code is the standard JDBC connection).

//query a full sql command
public static ArrayList<HashMap<String,Object>> 
rawQuery(String fullCommand) {
  try {

    //create statement
    Statement stm = null;
    stm = con.createStatement();

    //query
    ResultSet result = null;
    boolean returningRows = stm.execute(fullCommand);
    if (returningRows)
      result = stm.getResultSet();
    else
      return new ArrayList<HashMap<String,Object>>();

    //get metadata
    ResultSetMetaData meta = null;
    meta = result.getMetaData();

    //get column names
    int colCount = meta.getColumnCount();
    ArrayList<String> cols = new ArrayList<String>();
    for (int index=1; index<=Col_Count; index++)
      cols.add(meta.getColumnName(index));

    //fetch out rows
    ArrayList<HashMap<String,Object>> rows = 
    new ArrayList<HashMap<String,Object>>();

    while (result.next()) {
      HashMap<String,Object> row = new HashMap<String,Object>();
      for (String colName:cols) {
        Object val = Result.getObject(colName);
        row.put(colName,val);
      }
      rows.add(row);
    }

    //close statement
    stm.close();

    //pass back rows
    return tows;
  }
  catch (Exception ex) {
    System.out.print(ex.getMessage());
    return new ArrayList<HashMap<String,Object>>();
  }
}//raw_query
Comments