JDBC.java

//Query From https://data.dft.gov.uk/anonymised-mot-test/MOT_user_guide_v4.docx
private final String getlatestByVehicleSQL = "select " +
"tr.*, " +
"ft.FUEL_TYPE, " +
"tt.TESTTYPE AS TYPENAME, " +
"to2.RESULT, " +
"ti.*, " +
"fl.*, " +           "tid.MINORITEM,tid.RFRDESC,tid.RFRLOCMARKER,tid.RFRINSPMANDESC,tid.RFRADVISORYTEXT,tid.TSTITMSETSECID, " +
"b.ITEMNAME AS LEVEL1, " +
"c.ITEMNAME AS LEVEL2, " +
"d.ITEMNAME AS LEVEL3, " +
"e.ITEMNAME AS LEVEL4, " +
"f.ITEMNAME AS LEVEL5  " +
"from TESTRESULT tr " +
"LEFT JOIN TESTITEM ti  on ti.TESTID = tr.TESTID " +
"LEFT JOIN FUEL_TYPES ft on ft.TYPECODE = tr.FUELTYPE " +
"LEFT JOIN TEST_TYPES tt on tt.TYPECODE  = tr.TESTTYPE " +
 "LEFT JOIN TEST_OUTCOME to2 on to2.RESULTCODE  = tr.TESTRESULT " +
"LEFT JOIN FAILURE_LOCATION fl on ti.LOCATIONID = fl.FAILURELOCATIONID " +
"LEFT JOIN TESTITEM_DETAIL AS tid ON ti.RFRID = tid.RFRID AND tid.TESTCLASSID = tr.TESTCLASSID " +
"LEFT JOIN TESTITEM_GROUP AS b ON tid.TSTITMID = b.TSTITMID AND tid.TESTCLASSID = b.TESTCLASSID " +
"LEFT JOIN TESTITEM_GROUP AS c ON b.PARENTID = c.TSTITMID AND b.TESTCLASSID = c.TESTCLASSID " +
"LEFT JOIN TESTITEM_GROUP AS d ON c.PARENTID = d.TSTITMID AND c.TESTCLASSID = d.TESTCLASSID " +
"LEFT JOIN TESTITEM_GROUP AS e ON d.PARENTID = e.TSTITMID AND d.TESTCLASSID = e.TESTCLASSID " +
"LEFT JOIN TESTITEM_GROUP AS f ON e.PARENTID = f.TSTITMID AND e.TESTCLASSID = f.TESTCLASSID " +
"WHERE  tr.TESTID = (SELECT TESTID FROM TESTRESULT WHERE VEHICLEID=? LIMIT 1)";


public String getMOTResultInJSON(String identifier) {
   long identifierLong;
   jsonObj = new JSONObject();
   // Check we aren't a new thread - if we are we need a new conneciton.
   try {
       identifierLong = Long.valueOf(identifier);
       //Pick a prepared statement from out list of readers randomly
       PreparedStatement getTestStmt = readConnections.get(ThreadLocalRandom.current().nextInt(0, readConnections.size()));
       getTestStmt.setLong(1, identifierLong);
       ResultSet testResult = getTestStmt.executeQuery();
       ResultSetMetaData metaData = testResult.getMetaData();
    
   // Create JSON from a set of Rows
       String[] topFieldNames = { "TESTID", "VEHICLEID", "TESTTYPE", "TESTRESULT", "TESTDATE", "TESTCLASSID", "TYPENAME","TESTMILEAGE", "POSTCODEREGION", "MAKE", "MODEL", "COLOUR", "FUELTYPE", "FUEL_TYPE", "CYLCPCTY","FIRSTUSEDATE","RESULT" };
    
   String[] itemFieldNames = { "RFRID", "RFRTYPE", "DMARK", "LOCATIONID", "LAT", "LONGITUDINAL", "VERTICAL","MINORITEM", "RFRDESC","RFRLOCMARKER",               "RFRINSPMANDESC", "RFRADVISORYTEXT", "LEVEL1", "LEVEL2", "LEVEL3", "LEVEL4", "LEVEL5" };


       boolean firstRow = true;
       JSONArray itemsJSON = new JSONArray();
       while (testResult.next()) {
;
           JSONObject itemJSON = new JSONObject();
           for (int col = 1; col <= metaData.getColumnCount(); col++) {
               String label = metaData.getColumnLabel(col);
              
               if (firstRow && Arrays.asList(topFieldNames)
                                     .contains(label.toUpperCase())) {
                   Object val = testResult.getObject(col);
                  
                   jsonObj.put(label.toLowerCase(), val);
               }
               // All Rows add to the Items array - this is a simple JSON structure
               // Wiith just one top level array of objects
               if (Arrays.asList(itemFieldNames).contains(label.toUpperCase())) {
                   Object val = testResult.getObject(col);
                   itemJSON.put(label.toLowerCase(), val);
               }
           }
           /* If our item isnt blank add it to the items JSONArray */
           if (itemJSON.optInt("rfrid", -1) != -1) {
               itemsJSON.put(itemJSON);
           }
           firstRow = false;
       }
       jsonObj.put("testitems", itemsJSON);
       testResult.close();
       return jsonObj.toString();
   } catch (Exception e) {
       e.printStackTrace();
       logger.error(e.toString());
   }
   return jsonObj.toString();
}