public String generatePreparedFlatCalculationSQL(HashMap<String, String> valueMap) { String tableName = loadingConfig.getSchemaTableNames().get("featureTable"); String[][] table = loadingConfig.getTableMatrix("flatCalculationTable"); String nameKey = "Calculation/@name"; String nameStr = valueMap.get(nameKey); // Note that calculation can be null, thus nameStr can be null String[] names = {"area"}; if (nameStr != null) names = nameStr.split(DELIM); StringBuffer queryBuf = new StringBuffer("INSERT INTO " + tableName + "("); for (int i = 0; i < table.length; i++) { String[] map = table[i]; String attribute = map[1]; if (i != 0) queryBuf.append(", "); queryBuf.append(attribute); } for (int i = 0; i < names.length; i++) queryBuf.append(", " + names[i]); queryBuf.append(") VALUES("); for (int i = 0; i < table.length; i++) { String value = "?"; if (i != 0) queryBuf.append(", "); queryBuf.append(value); } for (int i = 0; i < names.length; i++) queryBuf.append(",?"); queryBuf.append(")"); return queryBuf.toString(); }
public boolean addBatchRegionQuery(PreparedStatement pstmt, HashMap<String, String> valueMap) { return setQueryParams( pstmt, loadingConfig.getSchemaTableNames().get("regionTable"), valueMap, loadingConfig.getTableMatrix("regionTable")); }
public List<String> generateCreateTableStatements() { List<String> result = new ArrayList<String>(); Map<String, String> tablesNames = loadingConfig.getSchemaTableNames(); /* * An entry in tablesNames maps a name for the table which is not important in this method * to the real name of the table in the database. A table can have many definitions, this * means, the real table name in the database appears as a value for many keys. The * final query contains the aggregation of all the columns in all definitions. */ Map<String, String> processedDefinitions = new HashMap<String, String>(); for (Iterator<Map.Entry<String, String>> tablesNamesIterator = tablesNames.entrySet().iterator(); tablesNamesIterator.hasNext(); ) { Map.Entry<String, String> oneDefinition = tablesNamesIterator.next(); // Remove the definition to find all other definitions for the same table tablesNamesIterator.remove(); // Process this definition if (!oneDefinition.getValue().equals("") && !processedDefinitions.containsKey(oneDefinition.getKey())) { // Find all other definitions for this table Map<String, String> allDefinitions = new HashMap<String, String>(); allDefinitions.put(oneDefinition.getKey(), oneDefinition.getValue()); // restOfTheDefinitionsIterator = tablesNamesIterator without the definition being processed for (Iterator<Map.Entry<String, String>> restOfTheDefinitionsIterator = tablesNames.entrySet().iterator(); restOfTheDefinitionsIterator.hasNext(); ) { Map.Entry<String, String> table = restOfTheDefinitionsIterator.next(); if (table.getValue().equals(oneDefinition.getValue())) { allDefinitions.put(table.getKey(), table.getValue()); processedDefinitions.put(table.getKey(), table.getValue()); } } String query = ""; query += "CREATE TABLE " + oneDefinition.getValue() + "("; for (Iterator<Map.Entry<String, String>> allDefinitionsIterator = allDefinitions.entrySet().iterator(); allDefinitionsIterator.hasNext(); ) { Map.Entry<String, String> definition = allDefinitionsIterator.next(); // Process the columns String[][] columns = loadingConfig.getTableMatrix(definition.getKey()); for (int i = 0; i < columns.length; i++) { query += "\n\t" + columns[i][1] + "\t\t\t" + columns[i][2]; if (i < (columns.length - 1) || allDefinitions.size() > 1) query += ","; } allDefinitionsIterator.remove(); } query += "\n)"; result.add(query); } } return result; }
public List<String> generateDropTableStatements() { List<String> result = new ArrayList<String>(); Map<String, String> tablesNames = loadingConfig.getSchemaTableNames(); for (String tableName : tablesNames.values()) { if (!tableName.equals("")) result.add("DROP TABLE " + tableName); } return result; }
public boolean addBatchMarkupQuery(PreparedStatement pstmt, HashMap<String, String> valueMap) { String markupType = valueMap.get("GeometricShape/@xsitype"); if ("Polygon".equals(markupType)) { return setQueryParams( pstmt, markupPolygonTableName, valueMap, loadingConfig.getTableMatrix("markupPolygonTable")); } return false; }
public boolean addBatchFlatCalculationQuery( PreparedStatement pstmt, HashMap<String, String> valueMap) { String[][] table = loadingConfig.getTableMatrix("flatCalculationTable"); String nameKey = "Calculation/@name"; String valueKey = "CalculationResult/@value"; String nameStr = valueMap.get(nameKey); if (nameStr == null) return true; String valueStr = valueMap.get(valueKey); String[] names = nameStr.split(DELIM); String[] values = valueStr.split(DELIM); if (names.length != values.length) return false; for (int i = 0; i < table.length; i++) { String[] map = table[i]; String key = map[0]; if (key.contains("partitionKey")) { String tileName = valueMap.get("Region/@name"); try { pstmt.setInt(i + 1, partitioningKeyGenerator.getPartitionKeyByTilename(tileName)); } catch (SQLException e) { e.printStackTrace(); return false; } } else { String value = valueMap.get(key); try { pstmt.setString(i + 1, value); } catch (SQLException e) { e.printStackTrace(); return false; } } } for (int i = 0; i < values.length; i++) try { String value = values[i]; if (value.equals("NaN")) value = "0"; pstmt.setString(i + table.length + 1, value); } catch (SQLException e) { e.printStackTrace(); } try { pstmt.addBatch(); } catch (SQLException e) { e.printStackTrace(); return false; } return true; }
public String generatePareparedObservationSQL(String quantificationType) { StringBuffer queryBuf = new StringBuffer( "INSERT INTO " + loadingConfig.getSchemaTableNames().get("observationOrdinalMultiTable") + "("); ; String[][] table = loadingConfig.getTableMatrix("observationOrdinalMultiTable"); if ("Nominal".equals(quantificationType)) { queryBuf = new StringBuffer( "INSERT INTO " + loadingConfig.getSchemaTableNames().get("observationNominalMultiTable") + "("); table = loadingConfig.getTableMatrix("observationNominalMultiTable"); } for (int i = 0; i < loadingConfig.getTableMatrix("observationSingleTable").length; i++) { String attribute = loadingConfig.getTableMatrix("observationSingleTable")[i][1]; if (i != 0) queryBuf.append(", "); queryBuf.append(attribute); } queryBuf.append(","); for (int i = 0; i < table.length; i++) { String attribute = table[i][1]; if (i != 0) queryBuf.append(", "); queryBuf.append(attribute); } queryBuf.append(") VALUES("); // Add single value attributes for (int i = 0; i < loadingConfig.getTableMatrix("observationSingleTable").length; i++) { if (i != 0) queryBuf.append(", "); queryBuf.append("?"); } queryBuf.append(","); // Add multi-value attributes for (int i = 0; i < table.length; i++) { if (i != 0) queryBuf.append(", "); queryBuf.append("?"); } queryBuf.append(")"); return queryBuf.toString(); }
public String generatePreparedAnnotationMarkupSQL() { return generatePreparedSQL( loadingConfig.getSchemaTableNames().get("annotationMarkupTable"), loadingConfig.getTableMatrix("annotationMarkupTable")) .toString(); }
public String generatePreparedMarkupSQL() { return generatePreparedSQL( markupPolygonTableName, loadingConfig.getTableMatrix("markupPolygonTable")) .toString(); }
public String generatePreparedRegionSQL() { return generatePreparedSQL( loadingConfig.getSchemaTableNames().get("regionTable"), loadingConfig.getTableMatrix("regionTable")) .toString(); }
public boolean addBatchObservationQueries( PreparedStatement opstmt, PreparedStatement npstmt, HashMap<String, String> annMap) { PreparedStatement pstmt = null; String quantificationTypeStr = annMap.get("Quantification/@xsitype"); if (quantificationTypeStr == null) return false; String[] quantificationTypes = quantificationTypeStr.split(DELIM); String[] singleValues = new String[loadingConfig.getTableMatrix("observationSingleTable").length]; String[][] multiOrdinalValues = new String[loadingConfig.getTableMatrix("observationOrdinalMultiTable").length] [quantificationTypes.length]; String[][] multiNominalValues = new String[loadingConfig.getTableMatrix("observationNominalMultiTable").length] [quantificationTypes.length]; String[][] multiValues = null; for (int i = 0; i < loadingConfig.getTableMatrix("observationSingleTable").length; i++) { String key = loadingConfig.getTableMatrix("observationSingleTable")[i][0]; singleValues[i] = annMap.get(key); } for (int i = 0; i < loadingConfig.getTableMatrix("observationOrdinalMultiTable").length; i++) { String[] table = loadingConfig.getTableMatrix("observationOrdinalMultiTable")[i]; String key = table[0]; String valueStr = annMap.get(key); if (valueStr == null) multiOrdinalValues[i] = null; else multiOrdinalValues[i] = valueStr.split(DELIM); } for (int i = 0; i < loadingConfig.getTableMatrix("observationNominalMultiTable").length; i++) { String[] table = loadingConfig.getTableMatrix("observationNominalMultiTable")[i]; String key = table[0]; String valueStr = annMap.get(key); if (valueStr == null) multiNominalValues[i] = null; else multiNominalValues[i] = valueStr.split(DELIM); } for (int count = 0; count < quantificationTypes.length; count++) { // For each observation instance <Observation>, generate a new query String quantificationType = quantificationTypes[count]; String[][] table = loadingConfig.getTableMatrix("observationOrdinalMultiTable"); multiValues = multiOrdinalValues; if ("Nominal".equals(quantificationType)) { pstmt = npstmt; table = loadingConfig.getTableMatrix("observationNominalMultiTable"); multiValues = multiNominalValues; } else pstmt = opstmt; // Add single value attributes for (int i = 0; i < loadingConfig.getTableMatrix("observationSingleTable").length; i++) { String[] map = loadingConfig.getTableMatrix("observationSingleTable")[i]; String key = map[0]; String value = annMap.get(key); try { pstmt.setString(i + 1, value); } catch (SQLException e) { e.printStackTrace(); } } // Add multi-value attributes for (int i = 0; i < table.length; i++) { if (table[i][0].contains("partitionKey")) { String tileName = annMap.get("Region/@name"); try { pstmt.setInt( i + 1 + loadingConfig.getTableMatrix("observationSingleTable").length, partitioningKeyGenerator.getPartitionKeyByTilename(tileName)); } catch (SQLException e) { e.printStackTrace(); return false; } } else { String value = null; if (multiValues[i] != null) value = multiValues[i][count]; try { pstmt.setString( i + 1 + loadingConfig.getTableMatrix("observationSingleTable").length, value); } catch (SQLException e) { e.printStackTrace(); } } } try { pstmt.addBatch(); } catch (SQLException e) { e.printStackTrace(); return false; } } return true; }
private QueryGenerator(String file) { loadingConfig = new LoadingConfig(file); markupPolygonTableName = loadingConfig.getSchemaTableNames().get("markupPolygonTable"); }