Union of Maps

[Marilyn de Queiroz]

My code is is running multiple SQL queries against different databases (different location, same fields). I put each record of the result into a Map and put the Maps into a List. I want to print the maps to a file.
I add the maps from the second query to the list the same way I add the maps from the first query. I am using TreeMaps because I thought they would sort the keys the same (same key names are in each Map), but it's not working the way I expected.

I iterate through the List to get the Maps, using the first map's keys for the column headers, which works fine for the values in the first database, but the order of the key/value pairs is different between the maps in the list from the first database and the maps in the list from the second database.

So I end up with a column in my file that looks like this

BOARDED
------------
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
388
388

where the first 10 results are from the first database and the last 2 are from a second database.
or this column

COUNT
----------
746
8458
773
4556
201
1566
11056
17122
837
10125
PROBLEM_REPORTS
WORK_ORDERS

I could hard code the keys and use map.get(key), but I was hoping I could do something more dynamic so I can still iterate over the keys but always get them in the same order.
I see in the documentation that the TreeMap uses the "Natural Order", but if the keys are Strings, wouldn't that be alpha order for the Maps in both Lists?
Any suggestions?  

[Dave Tolls]

Why are you putting the rows into Maps instead of creating a model class that represents a row?
That way you won't have to be concerned at all about the ordering of the keys.

[Junilu Lacar]

Hi, Marilyn! Wow, long time no see.

Maybe you should test your assumptions on what your different map's set of keys are. Your assumption that they all have the same set of keys may not be true. Also, I agree with Dave, a model object would probably be a better way to go than lists of maps.

[Marilyn de Queiroz]

Thanks for the responses, Dave & Junilu (Nice to be back)
The advantage of using maps is that they are flexible. I can read the field name and use it as the "attribute" rather than trying to make the SQL fit into the existing object attributes. If I create a Row class, I have to change the attributes when I change the SQL. Plus I have to add a toString() method to use with the print stuff which means hard-coding the attributes. It seems like I end up with the same decreased flexibility using Rows that I get by hard-coding the code to call each value by key. If I change the SQL, I would have to change the Row, but I don't have to change the Map.

[Junilu Lacar]

What does your code look like? Maybe all you need are fresh pair(s) of eyes to see what's going wrong.

[Campbell Ritchie]

Marilyn de Queiroz wrote:. . . Nice to be back . . . .

. . . And good to see you back

[Marilyn de Queiroz]

[quote]What does your code look like? Maybe all you need are fresh pair(s) of eyes to see what's going wrong. [/quote] Hi again, This is obviously a work in progress, but I think you can get the idea. Thanks for taking the time. [code=java]/* import java.io.*; import java.sql.*; import java.text.*; import java.util.*; import java.util.Date; import static java.lang.System.*; public class BillingReportsStagingCounts { private static final Integer ZERO = new Integer("0"); private static String getActiveSchemas = "SELECT ACCOUNT_ID. ACCOUNT_NAME, BOARDED_FLAG, SCHEMA_CD, CLASS " + "FROM table1 AX " + "JOIN table2 CC ON AX.GSMRT_ID = CC.COMPANY_ID " + "JOIN table3 SC ON TBCREATOR = SC.SCHEMA_CD " + "WHERE TBNAME IS NOT NULL"; private static String getCount = "SELECT COUNT(*) \"COUNT\" FROM %schema%.%table% WHERE %date_field% BETWEEN" + " '%lfm_day1%' AND '%lfm_day2%'"; private static String connectionData1 = "jdbc:1.2.3.4:5000/SCHEMA1"; private static String connectionData2 = "jdbc:2.3.4.6:4000/SCHEMA2"; private static String userId1 = "mardeq"; private static String password1 = "XXX"; private static String userId2 = "marilyn"; private static String password2 = "yYY"; private static Timestamp startDttm; private static Timestamp stopDttm; private static Writer writer = null; private static Calendar firstDateOfLFM; private static Timestamp currentTimestamp; // load new driver static { } // get Report Dates static { firstDateOfLFM = Calendar.getInstance(); Calendar lastDateOfLFM = Calendar.getInstance(); startDttm = new Timestamp(firstDateOfLFM.getTimeInMillis()); stopDttm = new Timestamp(lastDateOfLFM.getTimeInMillis()); currentTimestamp = new Timestamp(System.currentTimeMillis()); } // write csv file private static void writeResults(List dataList, String fileName) { System.out.println("Printing"); try { writer = new BufferedWriter(new FileWriter(fileName)); // write headers Map firstRecord = (Map)dataList.get(0); Set headerKeys = firstRecord.keySet(); Iterator iterator = headerKeys.iterator(); while (iterator.hasNext()) { writer.write((String)iterator.next()); writer.write(","); } writer.write("\n"); writer.flush(); // write values for (int i = 0; i < dataList.size(); i++) { Map data = (Map)dataList.get(i); Set keys = data.keySet(); iterator = keys.iterator(); while (iterator.hasNext()) { String key = (String)iterator.next(); String field = (data.get(key).toString()); writer.write(field); writer.write(","); } SimpleDateFormat dfYearMonth = new SimpleDateFormat("yyyy-MM"); SimpleDateFormat dfYearMonthDay = new SimpleDateFormat("yyyy-MM-dd"); String sd = dfYearMonth.format(startDttm); writer.write(sd + ","); String currDt = dfYearMonthDay.format(currentTimestamp); writer.write(currDt); writer.write("\n"); writer.flush(); } } catch (Exception e) { System.out.println("Unable to get file writer"); } } private static ResultSet executeCountSql(Statement statement, String schema, String sql, ResultSet rs) { if (sql.length() > 0) { try { rs = statement.executeQuery(sql); } catch (SQLException e) {} return rs; } private static List

[Junilu Lacar]

Line 193 looks suspicious to me. It creates a HashMap, which makes no guarantees on the order of keys, and adds it as the first map in the list. Too early for me to look carefully at all of the rest of the code but I wouldn't be surprised if that was the problem.

[Junilu Lacar]

Another thing you can do is temporarily add "tags" to your data values, like "(in as FIELD)" and "(out af FIELD)" where FIELD is the key used.

// tag incoming values
map.put(key, tag("in", key, value));

// tag outgoing values
String value = tag("out", key, map.get(key));

private String tag(String dir, String key, Object val) {
    return String.format("(%s as %s) %s", dir, key, val);
}

[Junilu Lacar]

Although now that I think about it, there's not much that can go wrong with putting/getting values from a Map using the same key.  The problem has to be with the order in which the keys are used, unless there's code that clobbers each key with the wrong value. The tagging would still help though, except you'd only tag on the way out:

String value = tag(key, map.get(key));

private String tag(String key, Object value) {
    return String.format("(%s) %s", key, value);
}

[Marilyn de Queiroz]

Thanks, Junilu. I'll try those suggestions.

[Marilyn de Queiroz]

Yes!! The HashMap was the culprit. Thank you so much, Junilu. It's working correctly now.  

[Junilu Lacar]

Good that it's working now. I know you said it's still a WIP but that code could use some love and refactoring ...