trjhtr

Very frequently I have to load many small tables into memory. I was tired of writing repetitive code, hence I wrote the following.

TableData class to load the table into this object:

public static class TableData 
 List<String> headerNames;
 List<List<Object>> data;
 public final int COLS;
 public final int ROWS;

 public static TableData emptyTable() 
 return new TableData();
 

 public TableData(List<String> headerNames, List<List<Object>> data) 
 super();
 this.headerNames = headerNames;
 this.data = data;
 COLS = headerNames.size();
 ROWS = data.size();
 

 /**
 * Returns value at j-th column at i-th row, with indices starting
 * at 0, and typecast the result with the class provided as argument
 * 
 * throws <code>ClassCastException</code> if the given class is not
 * applicable to the object returned
 **/
 public <T> T getValue(int i, int j, Class<T> cls) throws ClassCastException
 return cls.cast(data.get(i).get(j));
 

 @Override
 public String toString() 
 return "TableData [headerNames=" + headerNames + ", data=" + data + "]";
 

 private TableData() 
 ROWS = 0;
 COLS = 0;
 

DBManager class (wrapper to DB interface) has the following function to load the whole table into a TableData object:

/**
 * Returns <code>TableData</code> object for a given <code> tableName</code>
 *<p>
 * 
 * @param tableName the table name in DB, for which we need to retreive data.
 * @param args Optional argument which is comma separated column names if we want
 * to load only these columns, not every column in memory
 * @return a <code>TableData</code> object that contains the data produced
 * by the given query along with headerNames; Returns an Empty table in case of an exception.
 */
 public TableData getCompleteTable(String tablename, String... args) 
 String query = "select * from " + tablename;
 if (args.length > 0) 
 query = "select " + args[1] + " from " + tablename;
 
 try (Statement stmt = createReadStatement(); ResultSet rs = stmt.executeQuery(query);) 
 ResultSetMetaData meta = rs.getMetaData();
 ArrayList<String> headerNames = new ArrayList<>();
 List<List<Object>> data = new ArrayList<>();

 for (int i = 1; i <= meta.getColumnCount(); i++) 
 headerNames.add(meta.getColumnName(i));
 

 while (rs.next()) 
 ArrayList<Object> cols = new ArrayList<>(meta.getColumnCount());
 for (int i = 1; i <= meta.getColumnCount(); i++) 
 // TODO: How stable it is, for atleast JDBC mysql driver?
 cols.add(rs.getObject(i));
 
 data.add(cols);
 
 rs.close();
 stmt.close();
 return new TableData(headerNames, data);
 catch (Exception e) 
 // TODO: Throw exception upstream or return empty table to mark error?
 e.printStackTrace();
 return TableData.emptyTable();
 
 

The above pattern helped me increase my productivity as now I simply do the following to browse the whole table in the code:

TableData t = dlDb.getCompleteTable("users");
if(t.ROWS==0)
 //TODO: Either Table is empty or we were unable to retrieve, handle accordignly

for (int i = 0; i < t.ROWS; i++) 
 //As the developer, we know the correct datatype of our columns. 
 // We can also have a mapping from column index to class.
 nameToId.put(t.getValue(i, 1, String.class), t.getValue(i, 0, Integer.class));

Do you think it is good practice to use this pattern? I have been using it a lot in my code.

I can see the following red flags:

1. Only load small tables that can fit well in the memory.


2. 
   getObject() might not be implemented well with some DB drivers, so I need to be careful with that.

There is one problem that I see with your API and that is that the columns are referred by an int index instead of by name. Not only you have to remember the (arbitrary) position of the columns, but think what if you add or remove a column from one of your tables? considering that tableData already holds a list of the column names (and does nothing with it except print it) it is trivial to change getValue() to receive a String column name (or at the very least add an overloaded version).

My next point is not an exact "Code Review" answer, However, I believe this is a "trying to invent the wheel" case. What you did here was develop a kind of an ORM (Object relational mapping) library that maps DB tables to List of Objects. There is a small Apache library that does exactly that : Apache Commons DbUtils

You can still have a Tabledata class for the purpose of the type-safe getValue()

 public static class TableData 
 // List of Maps: each item in the List corresponds to one DB row
 // The Map keys are column names, map values are, well, values
 List<Map<String, Object>> data = null;

 public static TableData emptyTable() 
 return new TableData();
 

 public TableData(List<Map<String, Object>> data) 
 this.data = data;
 

 /**
 * Returns value of given column at i-th row, with indices starting at 0, and
 * typecast the result with the class provided as argument
 * 
 * throws <code>ClassCastException</code> if the given class is not applicable
 * to the object returned
 **/
 public <T> T getValue(int i, String column, Class<T> cls) throws ClassCastException 
 return cls.cast(data.get(i).get(column));
 

 private TableData() 
 
 

Getting a complete table is also simplified:

 import org.apache.commons.dbutils.*;
 import org.apache.commons.dbutils.handlers.*;

 public TableData getCompleteTable(String tablename, String... args) 
 String query = "select * from " + tablename;
 if (args.length > 0) 
 query = "select " + args[1] + " from " + tablename;
 
 try (Connection conn = ...) 
 MapListHandler handler = new MapListHandler();
 QueryRunner run = new QueryRunner();

 List<Map<String, Object>> result = run.query(conn, query, handler);
 return new TableData(data);
 catch (Exception e) 
 // TODO: Throw exception upstream or return empty table to mark error?
 e.printStackTrace();
 return TableData.emptyTable();