The challenges of doing a national integration continue to plague Sergio. More specifically, the “solutions” left behind by his predecessors continue to annoy.

Sergio has inherited a system which needs to plug in to a national database. As the national integration was something which was added after the business processes were already determined, that means that certain terms/descriptors/captions/etc. are used internally than are required externally, and vice versa. So, for example, one laboratory test Sergio’s company performs might be called “QD1” internally, but is known by the government as “F3+”.

As you might imagine, Sergio’s predecessors solved this with a database table called MAPPINGS. It contains all the mappings, so it might map lab test names, city codes, units of measure, group codes- just anything that could have a name that is possibly in conflict with the government’s requirements is in there. So the table has three key fields:

COD1 is the internal “code” for a thing. COD2 is the government’s code. And MAP_GROUP is a category tag, probably hastily added after a naming conflict between two different TLAs.

So far, so good. That all makes sense.

So let’s take a look at how they query the database.

	     public static String MapLabTests(String original) {
	        String result = "";
	        Logger logger = new Logger("ThisClass.MapLabTests");
	        StringBuffer sbQuery = new StringBuffer();
	        AccessBdNonXa accessBdNonXa = new AccessBdNonXa();
	        HashMap<String, String> parameters = new HashMap<String, String>();
	        try {
	            sbQuery.append(" SELECT  COD2 FROM MAPPINGS ");
	            sbQuery.append("  WHERE COD1= ?");
	            sbQuery.append("  AND   MAP_GROUP= ?");
	            parameters.put("1", original);
	            parameters.put("2", "LAB_CODES");

            	@SuppressWarnings("rawtypes")
	            Vector vectorBD = accessBdNonXa.lookup(sbQuery.toString(), parameters);
	            logger.debug(" Query bbdd" + sbQuery.toString() + " " + parameters);
	            if (vectorBD != null && vectorBD.size() > 0) {
	            	@SuppressWarnings("rawtypes")
	                HashMap hData = (HashMap) vectorBD.get(0);
	                result = (String) hData.get("COD2");
	            }
	        } catch (Exception e) {
	            logger.error("Exception: " + e.getMessage());
	            e.printStackTrace();
	        } finally {
	            if (accessBdNonXa != null) {
	                accessBdNonXa.close();
	            }
	        }
	        return result;
	    }

So, first off, the AccessBdNonXa class is the completely in-house, rolled from the ground-up data access wrapper. Sergio didn’t provide any of that code, but it’s a reinvented wheel.

Beyond that, this isn’t good code, but it’s hardly a true WTF. One has to wonder why they use a StringBuffer to construct a one-line query. I like the leading whitespace there, as it shows that there’s probably some copy-paste in its history, and the one character has been propagated everywhere that block is needed.

In fact, I know it was copy-pasted in a bunch of places.

That’s where this line comes in: parameters.put("2", "LAB_CODES");

A hard-coded value for a SQL query parameter should instantly make you suspicious.

What happens if you want to, I dunno, map city codes?

	    public static String MapDetGroup(String original) {

	        String result = "";
	        Logger logger = new Logger("ThisClass.MapCities");
	        StringBuffer sbQuery = new StringBuffer();
	        AccessBdNonXa accessBdNonXa = new AccessBdNonXa();
	        HashMap<String, String> parameters = new HashMap<String, String>();
	        try {
	            sbQuery.append(" SELECT  COD2 FROM MAPPINGS ");
	            sbQuery.append("  WHERE COD1= ?");
	            sbQuery.append("  AND   MAP_GROUP= ?");
	            parameters.put("1", original);
	            parameters.put("2", "GROUP_CITY_CODE");
            	@SuppressWarnings("rawtypes")
	            Vector vectorBD = accessBdNonXa.lookup(sbQuery.toString(), parameters);
	            logger.debug(" Query bbdd" + sbQuery.toString());
	            if (vectorBD != null && vectorBD.size() > 0) {
	            	@SuppressWarnings("rawtypes")
	                HashMap hData = (HashMap) vectorBD.get(0);
	                result = (String) hData.get("COD2");
	            }
	        } catch (Exception e) {
	            logger.error("Exception: " + e.getMessage());
	            e.printStackTrace();
	        } finally {
	            if (accessBdNonXa != null) {
	                accessBdNonXa.close();
	            }
	        }
	        return result;
	    }

There is one version of this method, copy/pasted, for each kind of category that could be looked up- at least eight at the moment, but these methods also aren’t quite centralized in any one place, so there honestly may be more, and as new codes and categories for codes are added, more of these methods will need to be added.

If only there were some tool that they could use, some way to parameterize this query, so that they could write the method once and pass different parameters to it. If only…

[Advertisement] Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!