List of usage examples for java.sql Connection rollback
void rollback() throws SQLException;
Connection object. From source file:ca.sqlpower.matchmaker.address.AddressPool.java
/** * Inserts and updates the contents of the result table with the * {@link AddressResult} contents in this {@link AddressPool}. * AddressResults that are marked as {@link StorageState#DIRTY} are assumed * to be already in the database and are updated. AddressResults that are * marked as {@link StorageState#NEW} are assumed to be new entries that do * no yet exist in the database and are inserted. * // w w w .jav a2 s . c o m * It is worth noting that at the moment, new Address results won't have an * output street number yet (since they have not been validated yet) but a * {@link NullPointerException} gets thrown if we try to insert a null * Integer, so for the time being, I've set the 'null' steet number to be * -1, since I don't believe there's anyone with a negative street number, * but if I'm wrong, this will have to be changed. * * @throws SQLException * @throws SQLObjectException */ public void store(Logger engineLogger, boolean useBatchExecute, boolean debug) throws SQLException, SQLObjectException { setStarted(true); setFinished(false); setCancelled(false); setProgress(0); List<AddressResult> dirtyAddresses = new ArrayList<AddressResult>(); List<AddressResult> deleteAddresses = new ArrayList<AddressResult>(); List<AddressResult> newAddresses = new ArrayList<AddressResult>(); for (List<Object> key : addresses.keySet()) { AddressResult result = addresses.get(key); if (result.getStorageState() == StorageState.DELETE) { deleteAddresses.add(result); } else if (result.getStorageState() == StorageState.DIRTY) { dirtyAddresses.add(result); } else if (result.getStorageState() == StorageState.NEW) { newAddresses.add(result); } } setJobSize(deleteAddresses.size() + dirtyAddresses.size() + newAddresses.size()); engineLogger.debug("# of Delete Address Records:" + deleteAddresses.size()); engineLogger.debug("# of Dirty Address Records:" + dirtyAddresses.size()); engineLogger.debug("# of New Address Records:" + newAddresses.size()); Connection con = null; PreparedStatement ps = null; Statement stmt = null; StringBuilder sql = null; AddressResult result = null; try { con = project.createResultTableConnection(); con.setAutoCommit(false); boolean useBatchUpdates = useBatchExecute && con.getMetaData().supportsBatchUpdates(); SQLTable resultTable = project.getResultTable(); int keySize = project.getSourceTableIndex().getChildCount(); if (deleteAddresses.size() > 0) { stmt = con.createStatement(); for (AddressResult currentResult : deleteAddresses) { sql = new StringBuilder("DELETE FROM "); appendFullyQualifiedTableName(sql, resultTable); sql.append(" WHERE "); int j = 0; for (Object keyValue : currentResult.getKeyValues()) { if (j > 0) { sql.append("AND "); } if (keyValue == null) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append(" is null "); } else if (keyValue instanceof String || keyValue instanceof Character) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j) .append("=" + SQL.quote(keyValue.toString()) + " "); } else { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append("=" + keyValue + " "); } j++; } engineLogger.debug("Preparing the following address result to be deleted: " + currentResult); engineLogger.debug("Executing statement " + sql); stmt.execute(sql.toString()); incrementProgress(); } if (stmt != null) stmt.close(); stmt = null; } Map<String, Integer> columnMetaData = this.getColumnMetaData(engineLogger, resultTable); /* For backward compatibility, see if old column names are being used. * NOTE: the database may return column names as upper case. */ boolean usingNewNames = true; if (columnMetaData.containsKey(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME)) { usingNewNames = false; } engineLogger.debug("Using new shorter names? " + usingNewNames); if (dirtyAddresses.size() > 0) { //First, create and UPDATE PreparedStatement to update dirty records sql = new StringBuilder(); sql.append("UPDATE "); appendFullyQualifiedTableName(sql, resultTable); sql.append(" SET "); sql.append(INPUT_ADDRESS_LINE1).append("=?, "); // 1 sql.append(INPUT_ADDRESS_LINE2).append("=?, "); // 2 sql.append(INPUT_MUNICIPALITY).append("=?, "); // 3 sql.append(INPUT_PROVINCE).append("=?, "); // 4 sql.append(INPUT_COUNTRY).append("=?, "); // 5 sql.append(INPUT_POSTAL_CODE).append("=?, "); // 6 sql.append(OUTPUT_COUNTRY).append("=?, "); // 7 if (usingNewNames) { sql.append(OUTPUT_DELIVERY_INSTALLATION_NAME).append("=?, "); // 8 sql.append(OUTPUT_DELIVERY_INSTALLATION_TYPE).append("=?, "); // 9 } else { sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME).append("=?, "); // 8 sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_TYPE).append("=?, "); // 9 } sql.append(OUTPUT_DIRECTION_PREFIX).append("=?, "); // 10 sql.append(OUTPUT_FAILED_PARSING_STRING).append("=?, "); // 11 sql.append(OUTPUT_GENERAL_DELIVERY_NAME).append("=?, "); // 12 sql.append(OUTPUT_LOCK_BOX_NUMBER).append("=?, "); // 13 sql.append(OUTPUT_LOCK_BOX_TYPE).append("=?, "); // 14 sql.append(OUTPUT_MUNICIPALITY).append("=?, "); // 15 sql.append(OUTPUT_POSTAL_CODE).append("=?, "); // 16 sql.append(OUTPUT_PROVINCE).append("=?, "); // 17 sql.append(OUTPUT_RURAL_ROUTE_NUMBER).append("=?, "); // 18 sql.append(OUTPUT_RURAL_ROUTE_TYPE).append("=?, "); // 19 sql.append(OUTPUT_STREET_DIRECTION).append("=?, "); // 20 sql.append(OUTPUT_STREET_NAME).append("=?, "); // 21 sql.append(OUTPUT_STREET_NUMBER).append("=?, "); // 22 sql.append(OUTPUT_STREET_NUMBER_SUFFIX).append("=?, "); // 23 if (usingNewNames) { sql.append(OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append("=?, "); // 23.5 } else { sql.append(OLD_OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append("=?, "); // 23.5 } sql.append(OUTPUT_STREET_TYPE).append("=?, "); // 24 sql.append(OUTPUT_STREET_TYPE_PREFIX).append("=?, "); // 25 sql.append(OUTPUT_SUITE).append("=?, "); // 26 sql.append(OUTPUT_SUITE_PREFIX).append("=?, "); // 27 sql.append(OUTPUT_SUITE_TYPE).append("=?, "); // 28 sql.append(OUTPUT_TYPE).append("=?, "); // 29 sql.append(OUTPUT_UNPARSED_ADDRESS).append("=?, "); // 30 sql.append(OUTPUT_URBAN_BEFORE_RURAL).append("=?, "); // 31 sql.append(OUTPUT_VALID).append("=? "); // 32 sql.append("WHERE "); String baseStatement = sql.toString(); int batchCount = 0; for (int i = 0; i < dirtyAddresses.size(); i++) { sql = new StringBuilder(baseStatement); result = dirtyAddresses.get(i); int j = 0; // I really wish there was a better way to handle this, // but unfortunately in SQL, <column> = null and <column> is // null are not the same thing, and you usually want 'is // null' Why they couldn't just use '= null' is beyond me. // Otherwise, we could just use a single prepared statement // for all the records. The main reason we had to switch // back to using prepared statements is because different RDBMS // platforms handle Booleans differently (some support // boolean explicitly, others use an integer (1 or 0) for (Object keyValue : result.getKeyValues()) { if (j > 0) { sql.append("AND "); } if (keyValue == null) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append(" is null "); // 18+ } else if (keyValue instanceof String || keyValue instanceof Character) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j) .append("=" + SQL.quote(keyValue.toString()) + " "); // 18+ } else { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append("=" + keyValue + " "); // 18+ } j++; } ps = con.prepareStatement(sql.toString()); Address inputAddress = result.getInputAddress(); this.adjustInputAddress(inputAddress, columnMetaData); engineLogger.debug( "Setting input unparsed address line 1 to " + inputAddress.getUnparsedAddressLine1()); ps.setString(1, inputAddress.getUnparsedAddressLine1()); engineLogger.debug( "Setting input unparsed address line 2 to " + inputAddress.getUnparsedAddressLine2()); ps.setString(2, inputAddress.getUnparsedAddressLine2()); engineLogger.debug("Setting input municipality to " + inputAddress.getMunicipality()); ps.setString(3, inputAddress.getMunicipality()); engineLogger.debug("Setting input province to " + inputAddress.getProvince()); ps.setString(4, inputAddress.getProvince()); engineLogger.debug("Setting input country to " + inputAddress.getCountry()); ps.setString(5, inputAddress.getCountry()); engineLogger.debug("Setting input postal code to " + inputAddress.getPostalCode()); ps.setString(6, inputAddress.getPostalCode()); Address outputAddress = result.getOutputAddress(); this.adjustOutputAddress(outputAddress, columnMetaData, usingNewNames); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(7, outputAddress.getSuite()); engineLogger.debug("Setting output delivery installation name to " + outputAddress.getDeliveryInstallationName()); ps.setString(8, outputAddress.getDeliveryInstallationName()); engineLogger.debug("Setting output delivery nstallation type to " + outputAddress.getDeliveryInstallationType()); ps.setString(9, outputAddress.getDeliveryInstallationType()); engineLogger.debug("Setting output direction prefix to " + outputAddress.isDirectionPrefix()); ps.setBoolean(10, outputAddress.isDirectionPrefix()); engineLogger.debug( "Setting output failed parsing string to " + outputAddress.getFailedParsingString()); ps.setString(11, outputAddress.getFailedParsingString()); engineLogger.debug( "Setting output general delivery name to " + outputAddress.getGeneralDeliveryName()); ps.setString(12, outputAddress.getGeneralDeliveryName()); engineLogger.debug("Setting output lock box number to " + outputAddress.getLockBoxNumber()); ps.setString(13, outputAddress.getLockBoxNumber()); engineLogger.debug("Setting output lock box type to " + outputAddress.getLockBoxType()); ps.setString(14, outputAddress.getLockBoxType()); engineLogger.debug("Setting output municipality to " + outputAddress.getMunicipality()); ps.setString(15, outputAddress.getMunicipality()); engineLogger.debug("Setting output postal code to " + outputAddress.getPostalCode()); ps.setString(16, outputAddress.getPostalCode()); engineLogger.debug("Setting output province to " + outputAddress.getProvince()); ps.setString(17, outputAddress.getProvince()); engineLogger .debug("Setting output rural route number to " + outputAddress.getRuralRouteNumber()); ps.setString(18, outputAddress.getRuralRouteNumber()); engineLogger.debug("Setting output rural route type to " + outputAddress.getRuralRouteType()); ps.setString(19, outputAddress.getRuralRouteType()); engineLogger.debug("Setting output street direciton to " + outputAddress.getStreetDirection()); ps.setString(20, outputAddress.getStreetDirection()); engineLogger.debug("Setting output street to " + outputAddress.getStreet()); ps.setString(21, outputAddress.getStreet()); engineLogger.debug("Setting output street number to " + outputAddress.getStreetNumber()); Integer streetNumber = outputAddress.getStreetNumber(); if (streetNumber == null) { ps.setNull(22, Types.INTEGER); } else { ps.setInt(22, streetNumber); } engineLogger.debug( "Setting output street number suffix to " + outputAddress.getStreetNumberSuffix()); ps.setString(23, outputAddress.getStreetNumberSuffix()); engineLogger.debug("Setting output street number suffix separate to " + outputAddress.isStreetNumberSuffixSeparate()); Boolean isStreetNumberSuffixSeparate = outputAddress.isStreetNumberSuffixSeparate(); if (isStreetNumberSuffixSeparate == null) { ps.setNull(24, Types.BOOLEAN); } else { ps.setBoolean(24, outputAddress.isStreetNumberSuffixSeparate()); } engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(25, outputAddress.getStreetType()); engineLogger.debug("Setting output streetTypePrefix to " + outputAddress.isStreetTypePrefix()); ps.setBoolean(26, outputAddress.isStreetTypePrefix()); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(27, outputAddress.getSuite()); engineLogger.debug("Setting output suitePrefix to " + outputAddress.isSuitePrefix()); ps.setBoolean(28, outputAddress.isSuitePrefix()); engineLogger.debug("Setting output suiteType to " + outputAddress.getSuiteType()); ps.setString(29, outputAddress.getSuiteType()); engineLogger.debug("Setting output type to " + outputAddress.getType()); RecordType type = outputAddress.getType(); ps.setString(30, type == null ? null : type.toString()); engineLogger.debug( "Setting output unparsedAddressLine1 to " + outputAddress.getUnparsedAddressLine1()); ps.setString(31, outputAddress.getUnparsedAddressLine1()); engineLogger.debug("Setting output urbanBeforeRural to " + outputAddress.isUrbanBeforeRural()); Boolean urbanBeforeRural = outputAddress.isUrbanBeforeRural(); if (urbanBeforeRural == null) { ps.setNull(32, Types.BOOLEAN); } else { ps.setBoolean(32, outputAddress.isUrbanBeforeRural()); } engineLogger.debug("Setting valid to " + result.isValid()); ps.setBoolean(33, result.isValid()); if (useBatchUpdates) { engineLogger.debug("Adding to batch"); ps.addBatch(); batchCount++; // TODO: The batchCount should be user setable if (batchCount > 1000) { engineLogger.debug("Executing batch"); ps.executeBatch(); batchCount = 0; } } else { engineLogger.debug("Executing statement"); ps.execute(); } incrementProgress(); } // Execute remaining batch statements if (batchCount > 0 && useBatchUpdates) { ps.executeBatch(); } if (ps != null) ps.close(); ps = null; } if (newAddresses.size() > 0) { //Next, let's meke an INSERT PreparedStatement to insert new records sql = new StringBuilder(); sql.append("INSERT INTO "); appendFullyQualifiedTableName(sql, resultTable); sql.append("("); for (int i = 0; i < keySize; i++) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(i).append(", "); } sql.append(INPUT_ADDRESS_LINE1).append(", "); sql.append(INPUT_ADDRESS_LINE2).append(", "); sql.append(INPUT_MUNICIPALITY).append(", "); sql.append(INPUT_PROVINCE).append(", "); sql.append(INPUT_COUNTRY).append(", "); sql.append(INPUT_POSTAL_CODE).append(", "); sql.append(OUTPUT_COUNTRY).append(", "); if (usingNewNames) { sql.append(OUTPUT_DELIVERY_INSTALLATION_NAME).append(", "); sql.append(OUTPUT_DELIVERY_INSTALLATION_TYPE).append(", "); } else { sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME).append(", "); sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_TYPE).append(", "); } sql.append(OUTPUT_DIRECTION_PREFIX).append(", "); sql.append(OUTPUT_FAILED_PARSING_STRING).append(", "); sql.append(OUTPUT_GENERAL_DELIVERY_NAME).append(", "); sql.append(OUTPUT_LOCK_BOX_NUMBER).append(", "); sql.append(OUTPUT_LOCK_BOX_TYPE).append(", "); sql.append(OUTPUT_MUNICIPALITY).append(", "); sql.append(OUTPUT_POSTAL_CODE).append(", "); sql.append(OUTPUT_PROVINCE).append(", "); sql.append(OUTPUT_RURAL_ROUTE_NUMBER).append(", "); sql.append(OUTPUT_RURAL_ROUTE_TYPE).append(", "); sql.append(OUTPUT_STREET_DIRECTION).append(", "); sql.append(OUTPUT_STREET_NAME).append(", "); sql.append(OUTPUT_STREET_NUMBER).append(", "); sql.append(OUTPUT_STREET_NUMBER_SUFFIX).append(", "); if (usingNewNames) { sql.append(OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append(", "); } else { sql.append(OLD_OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append(", "); } sql.append(OUTPUT_STREET_TYPE).append(", "); sql.append(OUTPUT_STREET_TYPE_PREFIX).append(", "); sql.append(OUTPUT_SUITE).append(", "); sql.append(OUTPUT_SUITE_PREFIX).append(", "); sql.append(OUTPUT_SUITE_TYPE).append(", "); sql.append(OUTPUT_TYPE).append(", "); sql.append(OUTPUT_UNPARSED_ADDRESS).append(", "); sql.append(OUTPUT_URBAN_BEFORE_RURAL).append(", "); sql.append(OUTPUT_VALID).append(")"); sql.append("VALUES("); for (int i = 0; i < keySize; i++) { sql.append("?, "); } sql.append( "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); ps = con.prepareStatement(sql.toString()); int batchCount = 0; for (int i = 0; i < newAddresses.size(); i++) { result = newAddresses.get(i); int j = 1; for (Object keyValue : result.getKeyValues()) { ps.setObject(j, keyValue); j++; engineLogger.debug("Setting key value " + j + " to " + keyValue); } Address inputAddress = result.getInputAddress(); this.adjustInputAddress(inputAddress, columnMetaData); engineLogger.debug( "Setting input unparsed address line 1 to " + inputAddress.getUnparsedAddressLine1()); ps.setString(j, inputAddress.getUnparsedAddressLine1()); engineLogger.debug( "Setting input unparsed address line 2 to " + inputAddress.getUnparsedAddressLine2()); ps.setString(j + 1, inputAddress.getUnparsedAddressLine2()); engineLogger.debug("Setting input municipality to " + inputAddress.getMunicipality()); ps.setString(j + 2, inputAddress.getMunicipality()); engineLogger.debug("Setting input province to " + inputAddress.getProvince()); ps.setString(j + 3, inputAddress.getProvince()); engineLogger.debug("Setting input country to " + inputAddress.getCountry()); ps.setString(j + 4, inputAddress.getCountry()); engineLogger.debug("Setting input postal code to " + inputAddress.getPostalCode()); ps.setString(j + 5, inputAddress.getPostalCode()); Address outputAddress = result.getOutputAddress(); this.adjustOutputAddress(outputAddress, columnMetaData, usingNewNames); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 6, outputAddress.getSuite()); engineLogger.debug("Setting output delivery installation name to " + outputAddress.getDeliveryInstallationName()); ps.setString(j + 7, outputAddress.getDeliveryInstallationName()); engineLogger.debug("Setting output delivery nstallation type to " + outputAddress.getDeliveryInstallationType()); ps.setString(j + 8, outputAddress.getDeliveryInstallationType()); engineLogger.debug("Setting output direction prefix to " + outputAddress.isDirectionPrefix()); ps.setBoolean(j + 9, outputAddress.isDirectionPrefix()); engineLogger.debug( "Setting output failed parsing string to " + outputAddress.getFailedParsingString()); ps.setString(j + 10, outputAddress.getFailedParsingString()); engineLogger.debug( "Setting output general delivery name to " + outputAddress.getGeneralDeliveryName()); ps.setString(j + 11, outputAddress.getGeneralDeliveryName()); engineLogger.debug("Setting output lock box number to " + outputAddress.getLockBoxNumber()); ps.setString(j + 12, outputAddress.getLockBoxNumber()); engineLogger.debug("Setting output lock box type to " + outputAddress.getLockBoxType()); ps.setString(j + 13, outputAddress.getLockBoxType()); engineLogger.debug("Setting output municipality to " + outputAddress.getMunicipality()); ps.setString(j + 14, outputAddress.getMunicipality()); engineLogger.debug("Setting output postal code to " + outputAddress.getPostalCode()); ps.setString(j + 15, outputAddress.getPostalCode()); engineLogger.debug("Setting output province to " + outputAddress.getProvince()); ps.setString(j + 16, outputAddress.getProvince()); engineLogger .debug("Setting output rural route number to " + outputAddress.getRuralRouteNumber()); ps.setString(j + 17, outputAddress.getRuralRouteNumber()); engineLogger.debug("Setting output rural route type to " + outputAddress.getRuralRouteType()); ps.setString(j + 18, outputAddress.getRuralRouteType()); engineLogger.debug("Setting output street direciton to " + outputAddress.getStreetDirection()); ps.setString(j + 19, outputAddress.getStreetDirection()); engineLogger.debug("Setting output street to " + outputAddress.getStreet()); ps.setString(j + 20, outputAddress.getStreet()); engineLogger.debug("Setting output street number to " + outputAddress.getStreetNumber()); Integer streetNumber = outputAddress.getStreetNumber(); if (streetNumber == null) { ps.setNull(j + 21, Types.INTEGER); } else { ps.setInt(j + 21, streetNumber); } engineLogger.debug( "Setting output street number suffix to " + outputAddress.getStreetNumberSuffix()); ps.setString(j + 22, outputAddress.getStreetNumberSuffix()); engineLogger.debug("Setting output street number suffix separate to " + outputAddress.isStreetNumberSuffixSeparate()); Boolean isStreetNumberSuffixSeparate = outputAddress.isStreetNumberSuffixSeparate(); if (isStreetNumberSuffixSeparate == null) { ps.setNull(j + 23, Types.BOOLEAN); } else { ps.setBoolean(j + 23, outputAddress.isStreetNumberSuffixSeparate()); } engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 24, outputAddress.getStreetType()); engineLogger.debug("Setting output streetTypePrefix to " + outputAddress.isStreetTypePrefix()); ps.setBoolean(j + 25, outputAddress.isStreetTypePrefix()); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 26, outputAddress.getSuite()); engineLogger.debug("Setting output suitePrefix to " + outputAddress.isSuitePrefix()); ps.setBoolean(j + 27, outputAddress.isSuitePrefix()); engineLogger.debug("Setting output suiteType to " + outputAddress.getSuiteType()); ps.setString(j + 28, outputAddress.getSuiteType()); engineLogger.debug("Setting output type to " + outputAddress.getType()); RecordType type = outputAddress.getType(); ps.setString(j + 29, type == null ? null : type.toString()); engineLogger.debug( "Setting output unparsedAddressLine1 to " + outputAddress.getUnparsedAddressLine1()); ps.setString(j + 30, outputAddress.getUnparsedAddressLine1()); engineLogger.debug("Setting output urbanBeforeRural to " + outputAddress.isUrbanBeforeRural()); Boolean urbanBeforeRural = outputAddress.isUrbanBeforeRural(); if (urbanBeforeRural == null) { ps.setNull(j + 31, Types.BOOLEAN); } else { ps.setBoolean(j + 31, outputAddress.isUrbanBeforeRural()); } engineLogger.debug("Setting valid to " + result.isValid()); ps.setBoolean(j + 32, result.isValid()); engineLogger.debug("Preparing the following address to be inserted: " + result); if (useBatchUpdates) { engineLogger.debug("Adding to batch"); ps.addBatch(); batchCount++; // TODO: The batchCount should be user setable if (batchCount > 1000) { engineLogger.debug("Executing batch"); ps.executeBatch(); batchCount = 0; } } else { engineLogger.debug("Executing statement"); ps.execute(); } incrementProgress(); } // Execute remaining batch statements if (batchCount > 0 && useBatchUpdates) { ps.executeBatch(); } if (ps != null) ps.close(); ps = null; } if (debug) { engineLogger.debug("Rolling back changes"); con.rollback(); } else { engineLogger.debug("Committing changes"); con.commit(); } for (AddressResult ar : addresses.values()) { ar.markClean(); } } catch (Exception ex) { try { con.rollback(); } catch (SQLException sqlEx) { engineLogger.error("Error while rolling back. " + "Suppressing this exception to prevent it from overshadowing the orginal exception.", sqlEx); } throw new RuntimeException("Unexpected exception while storing address validation results.\n" + "SQL statement: " + ((sql == null) ? "null" : sql.toString()) + "\n" + "Current result: " + ((result == null) ? "null" : "Input Address:\n" + result.getInputAddress() + "\n" + "Output Address:\n" + result.getOutputAddress()), ex); } finally { setFinished(true); if (ps != null) try { ps.close(); } catch (SQLException e) { engineLogger.error("Error while closing PreparedStatement", e); } if (stmt != null) try { stmt.close(); } catch (SQLException e) { engineLogger.error("Error while closing Statement", e); } if (con != null) try { con.close(); } catch (SQLException e) { engineLogger.error("Error while closing Connection", e); } } }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public void addSerde(String dbName, String tblName, AddSerdeDesc addSerdeDesc) throws InvalidOperationException, MetaException { Connection con; PreparedStatement ps = null;/*ww w.ja v a 2 s .c om*/ boolean success = false; dbName = dbName.toLowerCase(); tblName = tblName.toLowerCase(); try { con = getSegmentConnection(dbName); } catch (MetaStoreConnectException e1) { LOG.error("add serde error, db=" + dbName + ", tbl=" + tblName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("add serde error, db=" + dbName + ", tbl=" + tblName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement( "select tbl_id, is_compressed, input_format, output_format, serde_lib, tbl_location " + " from tbls where db_name=? and tbl_name=?"); ps.setString(1, dbName); ps.setString(2, tblName); boolean isTblFind = false; long tblID = 0; String serdeLib = null; String inputFormat = null; String location = null; String outputFormat = null; boolean isCompressed = false; Properties schema = new Properties(); ResultSet tblSet = ps.executeQuery(); while (tblSet.next()) { isTblFind = true; tblID = tblSet.getLong(1); isCompressed = tblSet.getBoolean(2); inputFormat = tblSet.getString(3); outputFormat = tblSet.getString(4); location = tblSet.getString(6); break; } serdeLib = addSerdeDesc.getSerdeName(); tblSet.close(); ps.close(); if (!isTblFind) { throw new MetaException("can not find table " + dbName + ":" + tblName); } if (inputFormat == null || inputFormat.length() == 0) { inputFormat = org.apache.hadoop.mapred.SequenceFileInputFormat.class.getName(); } schema.setProperty(org.apache.hadoop.hive.metastore.api.Constants.FILE_INPUT_FORMAT, inputFormat); if (outputFormat == null || outputFormat.length() == 0) { outputFormat = org.apache.hadoop.mapred.SequenceFileOutputFormat.class.getName(); } schema.setProperty(org.apache.hadoop.hive.metastore.api.Constants.FILE_OUTPUT_FORMAT, outputFormat); schema.setProperty(org.apache.hadoop.hive.metastore.api.Constants.META_TABLE_NAME, tblName); if (location != null) { schema.setProperty(org.apache.hadoop.hive.metastore.api.Constants.META_TABLE_LOCATION, location); } schema.setProperty(org.apache.hadoop.hive.metastore.api.Constants.BUCKET_COUNT, "0"); if (isCompressed) { schema.setProperty(org.apache.hadoop.hive.metastore.api.Constants.COMPRESS, "true"); } else { } if (serdeLib == null) { throw new MetaException("serde lib for the table " + dbName + ":" + tblName + " is null"); } if (serdeLib != null) { schema.setProperty(org.apache.hadoop.hive.serde.Constants.SERIALIZATION_LIB, serdeLib); } String modifyUser = addSerdeDesc.getUser(); Map<String, String> tblParamMap = new HashMap<String, String>(); ps = con.prepareStatement( "select param_key, param_value from table_params where tbl_id=? and param_type='TBL'"); ps.setLong(1, tblID); ResultSet paramSet = ps.executeQuery(); while (paramSet.next()) { tblParamMap.put(paramSet.getString(1), paramSet.getString(2)); } paramSet.close(); ps.close(); boolean containTime = false; boolean contailUser = false; if (tblParamMap.containsKey("last_modified_time")) containTime = true; if (tblParamMap.containsKey("last_modified_by")) contailUser = true; if (containTime && contailUser) { ps = con.prepareStatement( "update table_params set param_value=? where tbl_id=? and param_type='TBL' and param_key=?"); ps.setString(1, String.valueOf(System.currentTimeMillis() / 1000)); ps.setLong(2, tblID); ps.setString(3, "last_modified_time"); ps.addBatch(); ps.setString(1, modifyUser); ps.setLong(2, tblID); ps.setString(3, "last_modified_by"); ps.addBatch(); ps.executeBatch(); ps.close(); } else if (!containTime && !contailUser) { ps = con.prepareStatement("insert into table_params(tbl_id, param_type, param_key, param_value) " + " values(?,?,?,?)"); ps.setLong(1, tblID); ps.setString(2, "TBL"); ps.setString(3, "last_modified_time"); ps.setString(4, String.valueOf(System.currentTimeMillis() / 1000)); ps.addBatch(); ps.setLong(1, tblID); ps.setString(2, "TBL"); ps.setString(3, "last_modified_by"); ps.setString(4, modifyUser); ps.addBatch(); ps.executeBatch(); ps.close(); } else if (containTime && !contailUser) { ps = con.prepareStatement( "update table_params set param_value=? where tbl_id=? and param_type='TBL' and param_key=?"); ps.setString(1, String.valueOf(System.currentTimeMillis() / 1000)); ps.setLong(2, tblID); ps.setString(3, "last_modified_time"); ps.addBatch(); ps.executeBatch(); ps.close(); ps = con.prepareStatement("insert into table_params(tbl_id, param_type, param_key, param_value) " + " values(?,?,?,?)"); ps.setLong(1, tblID); ps.setString(2, "TBL"); ps.setString(3, "last_modified_by"); ps.setString(4, modifyUser); ps.addBatch(); ps.executeBatch(); ps.close(); } else { ps = con.prepareStatement( "update table_params set param_value=? where tbl_id=? and param_type='TBL' and param_key=?"); ps.setString(1, modifyUser); ps.setLong(2, tblID); ps.setString(3, "last_modified_by"); ps.addBatch(); ps.executeBatch(); ps.close(); ps = con.prepareStatement("insert into table_params(tbl_id, param_type, param_key, param_value) " + " values(?,?,?,?)"); ps.setLong(1, tblID); ps.setString(2, "TBL"); ps.setString(3, "last_modified_time"); ps.setString(4, String.valueOf(System.currentTimeMillis() / 1000)); ps.addBatch(); ps.executeBatch(); ps.close(); } ps = con.prepareStatement( "select param_key, param_value from table_params where tbl_id=? and (param_type='SERDE' or param_type='TBL')"); ps.setLong(1, tblID); ResultSet sdParamSet = ps.executeQuery(); while (sdParamSet.next()) { schema.setProperty(sdParamSet.getString(1), sdParamSet.getString(2)); } sdParamSet.close(); ps.close(); ps = con.prepareStatement( "select column_name, type_name, comment from columns where tbl_id=? order by column_index asc"); ps.setLong(1, tblID); StringBuilder colNameBuf = new StringBuilder(); StringBuilder colTypeBuf = new StringBuilder(); List<FieldSchema> colList = new ArrayList<FieldSchema>(); ResultSet colSet = ps.executeQuery(); boolean first = true; while (colSet.next()) { String name = colSet.getString(1); String type = colSet.getString(2); String comment = colSet.getString(3); FieldSchema field = new FieldSchema(); field.setName(name); field.setType(type); field.setComment(comment); colList.add(field); if (!first) { colNameBuf.append(","); colTypeBuf.append(":"); } colNameBuf.append(colSet.getString(1)); colTypeBuf.append(colSet.getString(2)); first = false; } colSet.close(); ps.close(); String colNames = colNameBuf.toString(); String colTypes = colTypeBuf.toString(); schema.setProperty(org.apache.hadoop.hive.metastore.api.Constants.META_TABLE_COLUMNS, colNames); schema.setProperty(org.apache.hadoop.hive.metastore.api.Constants.META_TABLE_COLUMN_TYPES, colTypes); schema.setProperty(org.apache.hadoop.hive.serde.Constants.SERIALIZATION_DDL, getDDLFromFieldSchema(tblName, colList)); Deserializer deserializer = SerDeUtils.lookupDeserializer(serdeLib); deserializer.initialize(hiveConf, schema); List<FieldSchema> newColList = null; try { newColList = MetaStoreUtils.getFieldsFromDeserializer(tblName, deserializer); } catch (SerDeException e) { throw new MetaException("Error in getting fields from serde. " + e.getMessage()); } catch (MetaException e) { throw new MetaException("Error in getting fields from serde." + e.getMessage()); } ps = con.prepareStatement("delete from columns where tbl_id=?"); ps.setLong(1, tblID); ps.executeUpdate(); ps.close(); ps = con.prepareStatement("insert into columns(column_index, tbl_id, column_name " + ",type_name, comment) values(?,?,?,?,?)"); long index = 0; for (FieldSchema field : newColList) { ps.setLong(1, index); ps.setLong(2, tblID); ps.setString(3, field.getName()); ps.setString(4, field.getType()); ps.setString(5, field.getComment()); ps.addBatch(); index++; } ps.executeBatch(); ps.close(); if ((addSerdeDesc.getProps() != null) && (addSerdeDesc.getProps().size() > 0)) { ps = con.prepareStatement("select param_key, param_value from table_params where tbl_id=? and " + "param_type='SERDE'"); ps.setLong(1, tblID); ResultSet oldParamSet = ps.executeQuery(); Map<String, String> needUpdateMap = new HashMap<String, String>(); Map<String, String> needAddMap = new HashMap<String, String>(); Map<String, String> oldParamMap = new HashMap<String, String>(); while (oldParamSet.next()) { oldParamMap.put(oldParamSet.getString(1), oldParamSet.getString(2)); } oldParamSet.close(); ps.close(); for (Map.Entry<String, String> entry : addSerdeDesc.getProps().entrySet()) { if (oldParamMap.containsKey(entry.getKey())) { needUpdateMap.put(entry.getKey(), entry.getValue()); } else { needAddMap.put(entry.getKey(), entry.getValue()); } } if (!needAddMap.isEmpty()) { ps = con.prepareStatement( "insert into table_params(tbl_id, param_key, param_value, param_type) values(?,?,?,?)"); for (Map.Entry<String, String> entry : needAddMap.entrySet()) { ps.setLong(1, tblID); ps.setString(2, entry.getKey()); ps.setString(3, entry.getValue()); ps.setString(4, "SERDE"); ps.addBatch(); } ps.executeBatch(); ps.close(); } if (!needUpdateMap.isEmpty()) { ps = con.prepareStatement( "update table_params set param_value=? where tbl_id=? and param_type='SERDE' and param_key=?"); for (Map.Entry<String, String> entry : needUpdateMap.entrySet()) { ps.setString(1, entry.getValue()); ps.setLong(2, tblID); ps.setString(3, entry.getKey()); ps.addBatch(); } ps.executeBatch(); ps.close(); } } ps = con.prepareStatement("update tbls set serde_lib=? where tbl_id=?"); ps.setString(1, addSerdeDesc.getSerdeName()); ps.setLong(2, tblID); ps.executeUpdate(); ps.close(); con.commit(); success = true; } catch (Exception ex) { LOG.error("add serde error, db=" + dbName + ", tbl=" + tblName + ", msg=" + ex.getMessage()); ex.printStackTrace(); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
public void addPartition(String dbName, String tblName, AddPartitionDesc addPartitionDesc) throws InvalidObjectException, MetaException { boolean success = false; Connection con = null; PreparedStatement ps = null;/*w w w . j av a 2 s . c o m*/ Statement stmt = null; dbName = dbName.toLowerCase(); tblName = tblName.toLowerCase(); boolean isPathMaked = false; ArrayList<Path> pathToMake = new ArrayList<Path>(); Warehouse wh = new Warehouse(hiveConf); long tblID = 0; try { con = getSegmentConnection(dbName); } catch (MetaStoreConnectException e1) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); stmt = con.createStatement(); String tblType = null; boolean hasPriPart = false; boolean hasSubPart = false; String priPartKey = null; String subPartKey = null; String priPartType = null; String subPartType = null; String priKeyType = null; String subKeyType = null; ResultSet tblSet = null; boolean isTblFind = false; boolean isColFind = false; String tblFormat = null; String tblLocation = null; PrimitiveTypeInfo pti = null; ObjectInspector StringIO = null; ObjectInspector ValueIO = null; ObjectInspectorConverters.Converter converter1 = null; ObjectInspectorConverters.Converter converter2 = null; ArrayList<String> partToAdd = new ArrayList<String>(); String sql = null; HiveConf hconf = (HiveConf) hiveConf; boolean externalPartition = hconf.getBoolVar(HiveConf.ConfVars.HIVESUPPORTEXTERNALPARTITION); if (addPartitionDesc.getLevel() == 0) { sql = "SELECT tbl_id, tbl_type, pri_part_type, pri_part_key, tbl_format, tbl_location" + " from TBLS where db_name='" + dbName + "' and tbl_name='" + tblName + "'"; tblSet = stmt.executeQuery(sql); isTblFind = false; while (tblSet.next()) { isTblFind = true; tblID = tblSet.getLong(1); tblType = tblSet.getString(2); priPartKey = tblSet.getString(4); priPartType = tblSet.getString(3); tblFormat = tblSet.getString(5); tblLocation = tblSet.getString(6); if (priPartType != null && !priPartType.isEmpty()) { hasPriPart = true; } break; } tblSet.close(); if (!isTblFind) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "can not find table " + dbName + ":" + tblName); throw new MetaException("can not find table " + dbName + ":" + tblName); } if (!tblType.equalsIgnoreCase("MANAGED_TABLE")) { if (tblType.equalsIgnoreCase("EXTERNAL_TABLE") && tblFormat != null && tblFormat.equalsIgnoreCase("pgdata")) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + tblType + ":" + tblFormat + " can not support alter partition"); throw new MetaException(tblType + ":" + tblFormat + " can not support alter partition"); } if (externalPartition && tblType.equalsIgnoreCase("EXTERNAL_TABLE") && (tblFormat == null || !tblFormat.equalsIgnoreCase("pgdata"))) { } else { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + tblType + " can not support alter partition"); throw new MetaException(tblType + " can not support alter partition"); } } if (!hasPriPart) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "table " + dbName + ":" + tblName + " is not pri-partitioned"); throw new MetaException("table " + dbName + ":" + tblName + " is not pri-partitioned"); } sql = "SELECT type_name from COLUMNS where tbl_id=" + tblID + " and column_name='" + priPartKey.toLowerCase() + "'"; isColFind = false; ResultSet colSet = stmt.executeQuery(sql); while (colSet.next()) { isColFind = true; priKeyType = colSet.getString(1); break; } colSet.close(); if (!isColFind) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "table " + "can not find partition key information " + priPartKey); throw new MetaException("can not find partition key information " + priPartKey); } pti = new PrimitiveTypeInfo(); pti.setTypeName(priKeyType); StringIO = PrimitiveObjectInspectorFactory .getPrimitiveJavaObjectInspector(PrimitiveCategory.STRING); ValueIO = PrimitiveObjectInspectorFactory .getPrimitiveWritableObjectInspector(pti.getPrimitiveCategory()); converter1 = ObjectInspectorConverters.getConverter(StringIO, ValueIO); converter2 = ObjectInspectorConverters.getConverter(StringIO, ValueIO); if ((addPartitionDesc.getPartType().equalsIgnoreCase("RANGE_PARTITION") && !priPartType.equalsIgnoreCase("range")) || (addPartitionDesc.getPartType().equalsIgnoreCase("LIST_PARTITION") && !priPartType.equalsIgnoreCase("list"))) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "can not add a " + addPartitionDesc.getPartType() + " partition, but the pri-partition type is " + priPartType); throw new MetaException("can not add a " + addPartitionDesc.getPartType() + " partition, but the pri-partition type is " + priPartType); } LinkedHashMap<String, List<String>> partSpaces = new LinkedHashMap<String, List<String>>(); Set<String> subPartNameSet = new TreeSet<String>(); sql = "SELECT level, part_name, part_values from PARTITIONS where" + " tbl_id=" + tblID;// + " order by level asc"; ResultSet partSet = stmt.executeQuery(sql); int partLevel = 0; while (partSet.next()) { partLevel = partSet.getInt(1); if (partLevel == 0) { String partName = partSet.getString(2); List<String> valueList = new ArrayList<String>(); Array spaceArray = partSet.getArray(3); ResultSet priValueSet = spaceArray.getResultSet(); while (priValueSet.next()) { valueList.add(priValueSet.getString(2)); } partSpaces.put(partName, valueList); } else if (partLevel == 1) { String partName = partSet.getString(2); subPartNameSet.add(partName); } } partSet.close(); partToAdd = new ArrayList<String>(); LinkedHashMap<String, List<String>> addPartSpaces = (LinkedHashMap<String, List<String>>) addPartitionDesc .getParSpaces(); Iterator<String> itr = addPartSpaces.keySet().iterator(); while (itr.hasNext()) { String key = itr.next().toLowerCase(); if (partSpaces.containsKey(key)) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "table : " + tblName + " have already contain a pri parititon named: " + key); throw new MetaException( "table : " + tblName + " have already contain a pri parititon named: " + key); } partToAdd.add(key); } Iterator<List<String>> listItr = addPartSpaces.values().iterator(); while (listItr.hasNext()) { Iterator<String> valueItr = listItr.next().iterator(); if (valueItr.hasNext()) { String value = valueItr.next(); if (converter1.convert(value) == null) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "value : " + value + " should be type of " + priKeyType); throw new MetaException("value : " + value + " should be type of " + priKeyType); } Iterator<List<String>> PartValuesItr = partSpaces.values().iterator(); while (PartValuesItr.hasNext()) { if (PartValuesItr.next().contains(value)) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "table : " + tblName + " have already contain a pri partition contain value: " + value); throw new MetaException("table : " + tblName + " have already contain a pri partition contain value: " + value); } } } } ps = con.prepareStatement( "INSERT INTO partitions(level, tbl_id, " + " part_name, part_values) values(?,?,?,?)"); for (Map.Entry<String, List<String>> entry : addPartSpaces.entrySet()) { ps.setInt(1, 0); ps.setLong(2, tblID); Array spaceArray = con.createArrayOf("varchar", entry.getValue().toArray()); ps.setArray(4, spaceArray); ps.setString(3, entry.getKey()); ps.addBatch(); } ps.executeBatch(); if (!tblType.equalsIgnoreCase("EXTERNAL_TABLE")) { for (String partName : partToAdd) { if (tblLocation == null || tblLocation.trim().isEmpty()) { pathToMake.addAll(wh.getPriPartitionPaths(dbName, tblName, partName, subPartNameSet)); } else { pathToMake.addAll(Warehouse.getPriPartitionPaths(new Path(tblLocation), partName, subPartNameSet)); } } } else { for (String partName : partToAdd) { pathToMake.addAll( Warehouse.getPriPartitionPaths(new Path(tblLocation), partName, subPartNameSet)); } } } else if (addPartitionDesc.getLevel() == 1) { sql = "SELECT tbl_id, tbl_type, sub_part_type, sub_part_key, tbl_format, tbl_location" + " from TBLS where db_name='" + dbName.toLowerCase() + "' and tbl_name='" + tblName.toLowerCase() + "'"; tblSet = stmt.executeQuery(sql); isTblFind = false; while (tblSet.next()) { isTblFind = true; tblID = tblSet.getLong(1); tblType = tblSet.getString(2); subPartKey = tblSet.getString(4); subPartType = tblSet.getString(3); tblFormat = tblSet.getString(5); tblLocation = tblSet.getString(6); if (subPartType != null && !subPartType.isEmpty()) { hasSubPart = true; } break; } tblSet.close(); if (!isTblFind) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "can not find table " + dbName + ":" + tblName); throw new MetaException("can not find table " + dbName + ":" + tblName); } if (!tblType.equalsIgnoreCase("MANAGED_TABLE")) { if (tblType.equalsIgnoreCase("EXTERNAL_TABLE") && tblFormat != null && tblFormat.equalsIgnoreCase("pgdata")) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + tblType + ":" + tblFormat + " can not support alter partition"); throw new MetaException(tblType + ":" + tblFormat + " can not support alter partition"); } if (externalPartition && tblType.equalsIgnoreCase("EXTERNAL_TABLE") && (tblFormat == null || !tblFormat.equalsIgnoreCase("pgdata"))) { } else { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + tblType + " can not support alter partition"); throw new MetaException(tblType + " can not support alter partition"); } } if (!hasSubPart) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "table " + dbName + ":" + tblName + " is not sun-partitioned"); throw new MetaException("table " + dbName + ":" + tblName + " is not sun-partitioned"); } sql = "SELECT type_name from COLUMNS where tbl_id=" + tblID + " and column_name='" + subPartKey.toLowerCase() + "'"; isColFind = false; ResultSet colSet = stmt.executeQuery(sql); while (colSet.next()) { isColFind = true; subKeyType = colSet.getString(1); break; } colSet.close(); if (!isColFind) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "can not find partition key information " + priPartKey); throw new MetaException("can not find partition key information " + priPartKey); } pti = new PrimitiveTypeInfo(); pti.setTypeName(subKeyType); StringIO = PrimitiveObjectInspectorFactory .getPrimitiveJavaObjectInspector(PrimitiveCategory.STRING); ValueIO = PrimitiveObjectInspectorFactory .getPrimitiveWritableObjectInspector(pti.getPrimitiveCategory()); converter1 = ObjectInspectorConverters.getConverter(StringIO, ValueIO); converter2 = ObjectInspectorConverters.getConverter(StringIO, ValueIO); if ((addPartitionDesc.getPartType().equalsIgnoreCase("RANGE_PARTITION") && !subPartType.equalsIgnoreCase("range")) || (addPartitionDesc.getPartType().equalsIgnoreCase("LIST_PARTITION") && !subPartType.equalsIgnoreCase("list"))) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "you can not add a " + addPartitionDesc.getPartType() + " partition, but the sub-partition type is " + subPartType); throw new MetaException("you can not add a " + addPartitionDesc.getPartType() + " partition, but the sub-partition type is " + subPartType); } LinkedHashMap<String, List<String>> partSpaces = new LinkedHashMap<String, List<String>>(); Set<String> partNameSet = new TreeSet<String>(); sql = "SELECT level, part_name, part_values from PARTITIONS where" + " tbl_id=" + tblID;// + " order by level asc"; ResultSet partSet = stmt.executeQuery(sql); int partLevel = 0; while (partSet.next()) { partLevel = partSet.getInt(1); if (partLevel == 1) { String partName = partSet.getString(2); List<String> valueList = new ArrayList<String>(); Array spaceArray = partSet.getArray(3); ResultSet priValueSet = spaceArray.getResultSet(); while (priValueSet.next()) { valueList.add(priValueSet.getString(2)); } partSpaces.put(partName, valueList); } else if (partLevel == 0) { String partName = partSet.getString(2); partNameSet.add(partName); } } partToAdd = new ArrayList<String>(); LinkedHashMap<String, List<String>> addPartSpaces = (LinkedHashMap<String, List<String>>) addPartitionDesc .getParSpaces(); Iterator<String> itr = addPartSpaces.keySet().iterator(); while (itr.hasNext()) { String key = itr.next().toLowerCase(); if (partSpaces.containsKey(key)) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "table : " + tblName + " have already contain a sub parititon named: " + key); throw new MetaException( "table : " + tblName + " have already contain a sub parititon named: " + key); } if (key.equalsIgnoreCase("default")) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "use : 'alter table tblname add default subpartition' to add default subpartition!"); throw new MetaException( "use : 'alter table tblname add default subpartition' to add default subpartition!"); } partToAdd.add(key); } Iterator<List<String>> listItr = addPartSpaces.values().iterator(); while (listItr.hasNext()) { Iterator<String> valueItr = listItr.next().iterator(); if (valueItr.hasNext()) { String value = valueItr.next(); if (converter1.convert(value) == null) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "value : " + value + " should be type of " + priKeyType); throw new MetaException("value : " + value + " should be type of " + priKeyType); } Iterator<List<String>> PartValuesItr = partSpaces.values().iterator(); while (PartValuesItr.hasNext()) { if (PartValuesItr.next().contains(value)) { LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + "table : " + tblName + " have already contain a sub partition contain value: " + value); throw new MetaException("table : " + tblName + " have already contain a sub partition contain value: " + value); } } } } ps = con.prepareStatement( "INSERT INTO partitions(level, tbl_id, " + " part_name, part_values) values(?,?,?,?)"); for (Map.Entry<String, List<String>> entry : addPartSpaces.entrySet()) { ps.setInt(1, 1); ps.setLong(2, tblID); Array spaceArray = con.createArrayOf("varchar", entry.getValue().toArray()); ps.setArray(4, spaceArray); ps.setString(3, entry.getKey()); ps.addBatch(); } ps.executeBatch(); if (!tblType.equalsIgnoreCase("EXTERNAL_TABLE")) { for (String partName : partToAdd) { if (tblLocation == null || tblLocation.trim().isEmpty()) { pathToMake.addAll(wh.getSubPartitionPaths(dbName, tblName, partNameSet, partName)); } else { pathToMake.addAll( Warehouse.getSubPartitionPaths(new Path(tblLocation), partNameSet, partName)); } } } else { for (String partName : partToAdd) { pathToMake.addAll( Warehouse.getSubPartitionPaths(new Path(tblLocation), partNameSet, partName)); } } } con.commit(); success = true; } catch (SQLException ex) { ex.printStackTrace(); LOG.error("add partition error, db=" + dbName + ", tbl=" + tblName + ", level=" + addPartitionDesc.getLevel() + ", msg=" + ex.getMessage()); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } if (isPathMaked) { for (Path path : pathToMake) { wh.deleteDir(path, false); } } } closeStatement(ps); closeConnection(con); } if (success) { boolean mkDirOK = false; List<Path> createdPath = new ArrayList<Path>(); try { for (Path path : pathToMake) { mkDirOK = wh.mkdirs(path); if (!mkDirOK) { break; } createdPath.add(path); } } catch (Exception x) { mkDirOK = false; } if (!mkDirOK) { dropPartitionMeta(dbName, tblID, addPartitionDesc); if (!createdPath.isEmpty()) { for (Path path : createdPath) { wh.deleteDir(path, true); } } throw new MetaException("can not create hdfs path, add partition failed"); } } }
From source file:org.entrystore.rowstore.store.impl.PgDataset.java
/** * @see Dataset#populate(File)/*from w w w . j a va 2s. c om*/ */ @Override public boolean populate(File csvFile) throws IOException { if (csvFile == null) { throw new IllegalArgumentException("Argument must not be null"); } String dataTable = getDataTable(); if (dataTable == null) { log.error("Dataset has no data table assigned"); return false; } setStatus(EtlStatus.PROCESSING); Connection conn = null; PreparedStatement stmt = null; CSVReader cr = null; try { conn = rowstore.getConnection(); cr = new CSVReader(new FileReader(csvFile), ',', '"'); int lineCount = 0; String[] labels = null; String[] line; conn.setAutoCommit(false); stmt = conn.prepareStatement("INSERT INTO " + dataTable + " (rownr, data) VALUES (?, ?)"); while ((line = cr.readNext()) != null) { if (lineCount == 0) { labels = line; } else { JSONObject jsonLine = null; try { jsonLine = csvLineToJsonObject(line, labels); } catch (Exception e) { log.error(e.getMessage()); log.info("Rolling back transaction"); conn.rollback(); setStatus(EtlStatus.ERROR); return false; } stmt.setInt(1, lineCount); PGobject jsonb = new PGobject(); jsonb.setType("jsonb"); jsonb.setValue(jsonLine.toString()); stmt.setObject(2, jsonb); log.debug("Adding to batch: " + stmt); stmt.addBatch(); // we execute the batch every 100th line if ((lineCount % 100) == 0) { log.debug("Executing: " + stmt); stmt.executeBatch(); } } lineCount++; } // in case there are some inserts left to be sent (i.e. // batch size above was smaller than 100 when loop ended) log.debug("Executing: " + stmt); stmt.executeBatch(); // we create an index over the data createIndex(conn, dataTable, labels); // we commit the transaction and free the resources of the statement conn.commit(); setStatus(EtlStatus.AVAILABLE); return true; } catch (SQLException e) { SqlExceptionLogUtil.error(log, e); try { log.info("Rolling back transaction"); conn.rollback(); } catch (SQLException e1) { SqlExceptionLogUtil.error(log, e1); } setStatus(EtlStatus.ERROR); return false; } finally { if (cr != null) { try { cr.close(); } catch (IOException e) { log.error(e.getMessage()); } } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { SqlExceptionLogUtil.error(log, e); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { SqlExceptionLogUtil.error(log, e); } } } }