List of usage examples for java.sql PreparedStatement addBatch
void addBatch() throws SQLException;
PreparedStatement object's batch of commands. 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 ww . ja va2 s .co 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 boolean addUserGroup(group newGroup, String user) throws MetaException { Connection con = null;//w w w. jav a 2 s. c o m ; PreparedStatement ps = null; boolean success = false; newGroup.setGroupName(newGroup.getGroupName().toLowerCase()); user = user.toLowerCase(); try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("add user group error, creator=" + newGroup.getCreator() + ", group=" + newGroup.getGroupName() + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("add user group error, creator=" + newGroup.getCreator() + ", group=" + newGroup.getGroupName() + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement("insert into usergroup(creator, group_name)" + " values(?,?)"); ps.setString(1, newGroup.getCreator().toLowerCase()); ps.setString(2, newGroup.getGroupName()); ps.executeUpdate(); ps.close(); String addList = newGroup.getUserList(); if (addList != null && !addList.isEmpty()) { ps = con.prepareStatement("update tdwuser set group_name=? where user_name=?"); String[] addArray = addList.split(","); for (int i = 0; i < addArray.length; i++) { ps.setString(1, newGroup.getGroupName()); ps.setString(2, addArray[i].toLowerCase()); ps.addBatch(); } ps.executeBatch(); } con.commit(); success = true; } catch (SQLException sqlex) { LOG.error("add user group error, creator=" + newGroup.getCreator() + ", group=" + newGroup.getGroupName() + ", msg=" + sqlex.getMessage()); sqlex.printStackTrace(); throw new MetaException(sqlex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return success; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean revokeRoleFromRole(String roleName, List<String> roles) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;/*from w ww . ja v a 2s . c o m*/ ; PreparedStatement ps = null; boolean success = false; roleName = roleName.toLowerCase(); List<String> roleLowerCase = new ArrayList<String>(roles.size()); for (String role : roles) { roleLowerCase.add(role.toLowerCase()); } try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("revoke role error, role=" + roleName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("revoke role error, role=" + roleName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement("select role_name from tdwrole where role_name=?"); ps.setString(1, roleName.toLowerCase()); boolean isRoleFind = false; ResultSet roleSet = ps.executeQuery(); while (roleSet.next()) { isRoleFind = true; break; } roleSet.close(); ps.close(); if (!isRoleFind) { throw new NoSuchObjectException("can not find role:" + roleName); } ps = con.prepareStatement("delete from tdwsonrole where role_name=? and sonrole_name=?"); for (String role : roles) { ps.setString(1, roleName.toLowerCase()); ps.setString(2, role.toLowerCase()); ps.addBatch(); } ps.executeBatch(); con.commit(); success = true; } catch (SQLException sqlex) { LOG.error("revoke role error, role=" + roleName + ", msg=" + sqlex.getMessage()); sqlex.printStackTrace(); throw new MetaException(sqlex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return success; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean grantRoleToUser(String userName, List<String> roles) throws NoSuchObjectException, InvalidObjectException, MetaException { Connection con = null;//from ww w. ja v a 2s. c om ; PreparedStatement ps = null; boolean success = false; userName = userName.toLowerCase(); List<String> roleLowerCase = new ArrayList<String>(roles.size()); for (String role : roles) { roleLowerCase.add(role.toLowerCase()); } roles = roleLowerCase; try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("grant role to user error , user=" + userName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("grant role to user error , user=" + userName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement("select user_name from tdwuser where user_name=?"); ps.setString(1, userName); boolean isPrivFind = false; ResultSet userSet = ps.executeQuery(); while (userSet.next()) { isPrivFind = true; break; } userSet.close(); ps.close(); if (!isPrivFind) { throw new NoSuchObjectException("can not find user:" + userName); } ps = con.prepareStatement("select role_name from tdwuserrole where user_name=?"); ps.setString(1, userName); List<String> roleSet = new ArrayList<String>(); ResultSet roleRetSet = ps.executeQuery(); while (roleRetSet.next()) { roleSet.add(roleRetSet.getString(1)); } roleRetSet.close(); ps.close(); roles.removeAll(roleSet); if (!roles.isEmpty()) { ps = con.prepareStatement("insert into tdwuserrole(user_name, role_name) values(?,?)"); for (String role : roles) { ps.setString(1, userName); ps.setString(2, role); ps.addBatch(); } ps.executeBatch(); } con.commit(); success = true; } catch (SQLException ex) { LOG.error("grant auth sys error , user=" + userName + ", msg=" + ex.getMessage()); ex.printStackTrace(); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return success; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public boolean updatePBInfo(String dbName, String tableName, String modifiedTime) throws InvalidOperationException, MetaException { Connection con = null;/*from w ww . j a v a2 s . c o m*/ PreparedStatement ps = null; boolean success = false; dbName = dbName.toLowerCase(); tableName = tableName.toLowerCase(); String jarName = "./auxlib/" + dbName + "_" + tableName + "_" + modifiedTime + ".jar"; String className = dbName + "_" + tableName + "_" + modifiedTime; try { con = getSegmentConnection(dbName); } catch (MetaStoreConnectException e1) { LOG.error("updatePBInfo, db=" + dbName + ", table=" + tableName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("replace column error, db=" + dbName + ", table=" + tableName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement("select tbl_id, serde_lib" + " from tbls where db_name=? and tbl_name=?"); ps.setString(1, dbName); ps.setString(2, tableName); String serdeLib = null; boolean isTblFind = false; long tblID = 0; ResultSet tblSet = ps.executeQuery(); while (tblSet.next()) { isTblFind = true; tblID = tblSet.getLong(1); serdeLib = tblSet.getString(2); } tblSet.close(); ps.close(); if (!isTblFind) { throw new MetaException("can not find table " + dbName + ":" + tableName); } if (!serdeLib.equals(ProtobufSerDe.class.getName())) { throw new MetaException("sorry, can only update jar info for a pb table "); } 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 containJar = false; boolean containClass = false; if (tblParamMap.containsKey("pb.jar")) containJar = true; if (tblParamMap.containsKey("pb.outer.class.name")) containClass = true; if (containJar && containClass) { ps = con.prepareStatement( "update table_params set param_value=? where tbl_id=? and param_type='TBL' and param_key=?"); ps.setString(1, jarName); ps.setLong(2, tblID); ps.setString(3, "pb.jar"); ps.addBatch(); ps.setString(1, className); ps.setLong(2, tblID); ps.setString(3, "pb.outer.class.name"); ps.addBatch(); ps.executeBatch(); ps.close(); } con.commit(); success = true; } catch (SQLException ex) { ex.printStackTrace(); LOG.error("updatePBInfo, db=" + dbName + ", tbl=" + tableName + ", msg=" + ex.getMessage()); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeConnection(con); } return true; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
@Override public void addTblProps(String dbName, String tblName, String modifyUser, Map<String, String> props) throws InvalidOperationException, MetaException { Connection con;//from www . j a va 2 s .com PreparedStatement ps = null; boolean success = false; dbName = dbName.toLowerCase(); tblName = tblName.toLowerCase(); try { con = getSegmentConnection(dbName); } catch (MetaStoreConnectException e1) { LOG.error("add table props error, db=" + dbName + ", tbl=" + tblName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("add table props 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 tbls.tbl_id from tbls where " + "tbls.db_name=? and tbls.tbl_name=?"); ps.setString(1, dbName); ps.setString(2, tblName); boolean isTblFind = false; long tblID = 0; ResultSet tblSet = ps.executeQuery(); while (tblSet.next()) { isTblFind = true; tblID = tblSet.getLong(1); } tblSet.close(); ps.close(); if (!isTblFind) { throw new MetaException("can not find table " + dbName + ":" + tblName); } 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(); Map<String, String> oldParamMap = new HashMap<String, String>(); while (paramSet.next()) { oldParamMap.put(paramSet.getString(1), paramSet.getString(2)); } paramSet.close(); ps.close(); Map<String, String> needUpdateMap = new HashMap<String, String>(); Map<String, String> needAddMap = new HashMap<String, String>(); for (Entry<String, String> entry : props.entrySet()) { if (oldParamMap.containsKey(entry.getKey())) { needUpdateMap.put(entry.getKey(), entry.getValue()); } else { needAddMap.put(entry.getKey(), entry.getValue()); } } if (oldParamMap.containsKey("last_modified_time")) { needUpdateMap.put("last_modified_time", String.valueOf(System.currentTimeMillis() / 1000)); } else { needAddMap.put("last_modified_time", String.valueOf(System.currentTimeMillis() / 1000)); } if (oldParamMap.containsKey("last_modified_by")) { needUpdateMap.put("last_modified_by", modifyUser); } else { needAddMap.put("last_modified_by", modifyUser); } if (!needUpdateMap.isEmpty()) { ps = con.prepareStatement("update table_params set param_value=? where " + " tbl_id=? and param_type='TBL' and param_key=?"); for (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(); } if (!needAddMap.isEmpty()) { ps = con.prepareStatement("insert into table_params(tbl_id, param_type, " + "param_key, param_value) values(?,?,?,?)"); for (Map.Entry<String, String> entry : needAddMap.entrySet()) { ps.setLong(1, tblID); ps.setString(2, "TBL"); ps.setString(3, entry.getKey()); ps.setString(4, entry.getValue()); ps.addBatch(); } ps.executeBatch(); ps.close(); } con.commit(); success = true; } catch (SQLException ex) { LOG.error("add table props error, db=" + dbName + ", tbl=" + tblName + ", msg=" + ex.getMessage()); 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
@Override public void addSerdeProps(String dbName, String tblName, String modifyUser, Map<String, String> props) throws InvalidOperationException, MetaException { Connection con;/*from w w w . j ava 2s . co m*/ PreparedStatement ps = null; Statement stmt = null; boolean success = false; dbName = dbName.toLowerCase(); tblName = tblName.toLowerCase(); try { con = getSegmentConnection(dbName); } catch (MetaStoreConnectException e1) { LOG.error("add serde props error, db=" + dbName + ", tbl=" + tblName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("add serde props error, db=" + dbName + ", tbl=" + tblName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); stmt = con.createStatement(); String sql = "select tbls.tbl_id from tbls where " + "tbls.db_name='" + dbName + "' and tbls.tbl_name='" + tblName + "'"; boolean isTblFind = false; long tblID = 0; ResultSet tblSet = stmt.executeQuery(sql); while (tblSet.next()) { isTblFind = true; tblID = tblSet.getLong(1); } tblSet.close(); if (!isTblFind) { throw new MetaException("can not find table " + dbName + ":" + tblName); } Map<String, String> tblParamMap = new HashMap<String, String>(); Map<String, String> serdeParamMap = new HashMap<String, String>(); sql = "select param_type, param_key, param_value from table_params where tbl_id=" + tblID + " and (param_type='SERDE' or param_type='TBL')"; ResultSet paramSet = stmt.executeQuery(sql); String type = null; while (paramSet.next()) { type = paramSet.getString(1); if (type.equalsIgnoreCase("TBL")) { tblParamMap.put(paramSet.getString(2), paramSet.getString(3)); } else { serdeParamMap.put(paramSet.getString(2), paramSet.getString(3)); } } paramSet.close(); boolean containTime = false; boolean contailUser = false; if (tblParamMap.containsKey("last_modified_time")) containTime = true; if (tblParamMap.containsKey("last_modified_by")) contailUser = true; Map<String, String> needUpdateMap = new HashMap<String, String>(); Map<String, String> needAddMap = new HashMap<String, String>(); for (Entry<String, String> entry : props.entrySet()) { if (serdeParamMap.containsKey(entry.getKey())) { needUpdateMap.put(entry.getKey(), entry.getValue()); } else { needAddMap.put(entry.getKey(), entry.getValue()); } } if (!needUpdateMap.isEmpty() || containTime || contailUser) { ps = con.prepareStatement("update table_params set param_value=? where " + " tbl_id=? and param_type=? and param_key=?"); for (Entry<String, String> entry : needUpdateMap.entrySet()) { ps.setString(1, entry.getValue()); ps.setLong(2, tblID); ps.setString(3, "SERDE"); ps.setString(4, entry.getKey()); ps.addBatch(); } if (containTime) { ps.setString(1, String.valueOf(System.currentTimeMillis() / 1000)); ps.setLong(2, tblID); ps.setString(3, "TBL"); ps.setString(4, "last_modified_time"); ps.addBatch(); } if (contailUser) { ps.setString(1, modifyUser); ps.setLong(2, tblID); ps.setString(3, "TBL"); ps.setString(4, "last_modified_by"); ps.addBatch(); } ps.executeBatch(); ps.close(); } if (!needAddMap.isEmpty() || !containTime || !contailUser) { ps = con.prepareStatement("insert into table_params(tbl_id, param_type, " + "param_key, param_value) values(?,?,?,?)"); for (Map.Entry<String, String> entry : needAddMap.entrySet()) { ps.setLong(1, tblID); ps.setString(2, "SERDE"); ps.setString(3, entry.getKey()); ps.setString(4, entry.getValue()); ps.addBatch(); } if (!containTime) { ps.setLong(1, tblID); ps.setString(2, "TBL"); ps.setString(3, "last_modified_time"); ps.setString(4, String.valueOf(System.currentTimeMillis() / 1000)); ps.addBatch(); } if (!contailUser) { ps.setLong(1, tblID); ps.setString(2, "TBL"); ps.setString(3, "last_modified_by"); ps.setString(4, modifyUser); ps.addBatch(); } ps.executeBatch(); ps.close(); } con.commit(); success = true; } catch (SQLException ex) { ex.printStackTrace(); LOG.error("add serde props error, db=" + dbName + ", tbl=" + tblName + ", msg=" + ex.getMessage()); throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(ps); closeStatement(stmt); closeConnection(con); } return; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
public void renameTableNoDistributeTrans(String dbName, String tblName, String modifyUser, String newName) throws InvalidOperationException, MetaException { if (!MetaStoreUtils.validateName(newName)) { throw new InvalidOperationException(newName + " is not a valid object name"); }/*from w w w . j av a 2 s . c o m*/ if (tblName.equals(newName)) { return; } dbName = dbName.toLowerCase(); tblName = tblName.toLowerCase(); modifyUser = modifyUser.toLowerCase(); newName = newName.toLowerCase(); Connection con = null; PreparedStatement ps = null; String newLocation = null; String oldLocation = null; String serdeLib = null; String tblType = null; boolean isMoved = false; Path newPath = null; Path oldPath = null; FileSystem oldFs = null; FileSystem newFs = null; boolean success = false; try { con = getSegmentConnection(dbName); } catch (MetaStoreConnectException e1) { LOG.error("rename table error, db=" + dbName + ", tbl=" + tblName + ", newName=" + newName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("rename table error, db=" + dbName + ", tbl=" + tblName + ", newName=" + newName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement( "select tbl_id, tbl_type, tbl_location, serde_lib from TBLS where db_name=? and tbl_name=?"); ps.setString(1, dbName); ps.setString(2, tblName); boolean isTblFind = false; long tblID = 0; ResultSet tblSet = ps.executeQuery(); while (tblSet.next()) { isTblFind = true; tblID = tblSet.getLong(1); tblType = tblSet.getString(2); oldLocation = tblSet.getString(3); serdeLib = tblSet.getString(4); break; } tblSet.close(); ps.close(); if (!isTblFind) { throw new MetaException("can not find table " + dbName + ":" + tblName); } if (!tblType.equalsIgnoreCase("MANAGED_TABLE")) { throw new MetaException("only manage table can rename "); } if (serdeLib.equals(ProtobufSerDe.class.getName())) { throw new MetaException( "Renaming table is not supported for protobuf table. SerDe may be incompatible"); } 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(); } Warehouse wh = new Warehouse(hiveConf); // newLocation = wh.getDefaultTablePath(dbName, newName).toString(); newLocation = oldLocation.substring(0, oldLocation.length() - tblName.length()) + newName; ps = con.prepareStatement("update tbls set tbl_name=?, tbl_location=? where tbl_id=?"); ps.setString(1, newName.toLowerCase()); ps.setString(2, newLocation); ps.setLong(3, tblID); ps.executeUpdate(); ps.close(); oldPath = new Path(oldLocation); oldFs = wh.getFs(oldPath); newPath = new Path(newLocation); newFs = wh.getFs(newPath); if (oldFs != newFs) { throw new InvalidOperationException( "table new location " + oldFs + " is on a different file system than the old location " + newFs + ". This operation is not supported"); } try { oldFs.exists(oldPath); if (newFs.exists(newPath)) { throw new InvalidOperationException("New location for this table " + dbName + "." + tblName + " already exists : " + newPath); } } catch (IOException e) { throw new InvalidOperationException( "Unable to access new location " + newPath + " for table " + dbName + "." + tblName); } try { if (oldFs.exists(oldPath)) { oldFs.rename(oldPath, newPath); } isMoved = true; } catch (IOException e) { throw new InvalidOperationException( "Unable to access old location " + oldPath + " for table " + dbName + "." + tblName); } if (isMoved) { try { if (oldFs.exists(oldPath)) { oldFs.rename(newPath, oldPath); } } catch (IOException e) { throw new InvalidOperationException( "Unable to access old location " + oldPath + " for table " + dbName + "." + tblName); } } con.commit(); success = true; } catch (SQLException ex) { throw new MetaException(ex.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } //if (isMoved) { // try { // if (oldFs.exists(oldPath)) { // oldFs.rename(newPath, oldPath); // } // } catch (IOException e) { // throw new InvalidOperationException( // "Unable to access old location " + oldPath + " for table " // + dbName + "." + tblName); // } //} } closeStatement(ps); closeConnection(con); } success = false; Statement stmt = null; try { con = getGlobalConnection(); } catch (MetaStoreConnectException e1) { LOG.error("rename table error, db=" + dbName + ", tbl=" + tblName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("rename table error, db=" + dbName + ", tbl=" + tblName + ", msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); stmt = con.createStatement(); String sql = "update tblpriv set tbl_name='" + newName + "' where db_name='" + dbName + "' and tbl_name='" + tblName + "'"; stmt.executeUpdate(sql); try { sql = "update tblsensitivity set tbl_name='" + newName + "' where db_name='" + dbName + "' and tbl_name='" + tblName + "'"; stmt.executeUpdate(sql); } catch (Exception x) { } con.commit(); success = true; } catch (SQLException x) { LOG.error("rename table error, db=" + dbName + ", tbl=" + tblName + ", msg=" + x.getMessage()); throw new MetaException(x.getMessage()); } finally { if (!success) { try { con.rollback(); } catch (SQLException e) { } } closeStatement(stmt); closeConnection(con); } return; }
From source file:org.apache.hadoop.hive.metastore.MyXid.java
public void jdbcCreateView(Table tbl) throws AlreadyExistsException, InvalidObjectException, MetaException { LOG.debug("first, check the name is valid or not"); if (!MetaStoreUtils.validateName(tbl.getTableName()) || !MetaStoreUtils.validateColNames(tbl.getSd().getCols()) || (tbl.getPriPartition() != null && !MetaStoreUtils.validateName(tbl.getPriPartition().getParKey().getName())) || (tbl.getSubPartition() != null && !MetaStoreUtils.validateName(tbl.getSubPartition().getParKey().getName()))) { LOG.error("create view error db=" + tbl.getDbName() + ", view=" + tbl.getTableName()); throw new InvalidObjectException(tbl.getTableName() + " is not a valid object name"); }/*from w ww . j av a2 s .c o m*/ boolean success = false; Connection con; PreparedStatement ps = null; tbl.setDbName(tbl.getDbName().toLowerCase()); tbl.setTableName(tbl.getTableName().toLowerCase()); long tblID = genTblID(tbl.getDbName(), tbl.getTableName()); try { con = getSegmentConnection(tbl.getDbName()); } catch (MetaStoreConnectException e1) { LOG.error("create view error db=" + tbl.getDbName() + ", view=" + tbl.getTableName() + ",msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } catch (SQLException e1) { LOG.error("create view error db=" + tbl.getDbName() + ", view=" + tbl.getTableName() + ",msg=" + e1.getMessage()); throw new MetaException(e1.getMessage()); } try { con.setAutoCommit(false); con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ); ps = con.prepareStatement("select tbl_id, tbl_type from TBLS where db_name=? and tbl_name=?"); ps.setString(1, tbl.getDbName()); ps.setString(2, tbl.getTableName()); boolean isViewFind = false; String tblType = null; ResultSet tblSet = ps.executeQuery(); while (tblSet.next()) { isViewFind = true; tblID = tblSet.getLong(1); tblType = tblSet.getString(2); break; } tblSet.close(); ps.close(); if (isViewFind && !tbl.isIsReplaceOnExit()) { LOG.error("view " + tbl.getDbName() + ":" + tbl.getTableName() + " has exist"); throw new AlreadyExistsException( "view " + tbl.getDbName() + ":" + tbl.getTableName() + " has exist"); } if (isViewFind && tbl.isIsReplaceOnExit()) { if (tblType != null && !tblType.equalsIgnoreCase("VIRTUAL_VIEW")) { LOG.error("name conflict " + tbl.getDbName() + ":" + tbl.getTableName() + " already exist, and it is not a view"); throw new MetaException("name conflict " + tbl.getDbName() + ":" + tbl.getTableName() + " already exist, and it is not a view"); } ps = con.prepareStatement("update TBLS set tbl_comment=? where tbl_id=? "); ps.setString(1, tbl.getParameters().get("comment")); ps.setLong(2, tblID); ps.executeUpdate(); ps.close(); ps = con.prepareStatement("update tdwview set view_original_text=?, view_expanded_text=?, " + "vtables=? where tbl_id=? "); ps.setString(1, tbl.getViewOriginalText()); ps.setString(2, tbl.getViewExpandedText()); ps.setString(3, tbl.getVtables().toLowerCase()); ps.setLong(4, tblID); ps.executeUpdate(); ps.close(); 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(?,?,?,?,?)"); List<FieldSchema> fieldSchemas = tbl.getSd().getCols(); int size = fieldSchemas.size(); for (int i = 0; i < size; i++) { ps.setLong(1, i); ps.setLong(2, tblID); ps.setString(3, fieldSchemas.get(i).getName().toLowerCase()); ps.setString(4, fieldSchemas.get(i).getType()); ps.setString(5, fieldSchemas.get(i).getComment()); ps.addBatch(); } ps.executeBatch(); ps.close(); Map<String, String> tblPram = 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()) { tblPram.put(paramSet.getString(1), paramSet.getString(2)); } paramSet.close(); ps.close(); ps = con.prepareStatement("delete from TABLE_PARAMS where tbl_id=? and param_type='TBL'"); ps.setLong(1, tblID); ps.executeUpdate(); ps.close(); tblPram.putAll(tbl.getParameters()); ps = con.prepareStatement( "insert into TABLE_PARAMS(tbl_id, param_type, param_key, param_value) values(?,?,?,?)"); for (Entry<String, String> entry : tblPram.entrySet()) { ps.setLong(1, tblID); ps.setString(2, "TBL"); ps.setString(3, entry.getKey()); ps.setString(4, entry.getValue()); ps.addBatch(); } ps.executeBatch(); ps.close(); con.commit(); success = true; } else { ps = con.prepareStatement("INSERT INTO TBLS(tbl_id, retention, tbl_type, db_name, " + "tbl_name, tbl_owner, tbl_comment)" + " values(?,?,?,?,?,?,?)"); StorageDescriptor sd = tbl.getSd(); if (sd == null || sd.getSerdeInfo() == null) { throw new MetaException("storage descriptor of table " + tbl.getTableName() + " is null"); } ps.setLong(1, tblID); ps.setLong(2, tbl.getRetention()); ps.setString(3, tbl.getTableType()); ps.setString(4, tbl.getDbName()); ps.setString(5, tbl.getTableName()); ps.setString(6, tbl.getOwner().toLowerCase()); ps.setString(7, tbl.getParameters().get("comment")); ps.executeUpdate(); ps.close(); ps = con.prepareStatement( "INSERT INTO COLUMNS(column_index, tbl_id, column_name, type_name, comment) " + " values(?,?,?,?,?)"); List<FieldSchema> fieldList = sd.getCols(); int fieldSize = fieldList.size(); for (int i = 0; i < fieldSize; i++) { FieldSchema field = fieldList.get(i); ps.setInt(1, i); ps.setLong(2, tblID); ps.setString(3, field.getName()); ps.setString(4, field.getType()); ps.setString(5, field.getComment()); ps.addBatch(); } ps.executeBatch(); ps.close(); if (tbl.getParametersSize() > 0 || sd.getParametersSize() > 0 || sd.getSerdeInfo().getParametersSize() > 0) { ps = con.prepareStatement( "insert into table_params(tbl_id, param_type, param_key, param_value) " + " values(?,?,?,?)"); if (tbl.getParametersSize() > 0) { for (Map.Entry<String, String> entry : tbl.getParameters().entrySet()) { if (entry.getKey().equalsIgnoreCase("type") || entry.getKey().equalsIgnoreCase("comment")) break; ps.setLong(1, tblID); ps.setString(2, "TBL"); ps.setString(3, entry.getKey()); ps.setString(4, entry.getValue()); ps.addBatch(); } } if (sd.getParametersSize() > 0) { for (Map.Entry<String, String> entry : sd.getParameters().entrySet()) { ps.setLong(1, tblID); ps.setString(2, "SD"); ps.setString(3, entry.getKey()); ps.setString(4, entry.getValue()); ps.addBatch(); } } if (sd.getSerdeInfo().getParametersSize() > 0) { for (Map.Entry<String, String> entry : sd.getSerdeInfo().getParameters().entrySet()) { ps.setLong(1, tblID); ps.setString(2, "SERDE"); ps.setString(3, entry.getKey()); ps.setString(4, entry.getValue()); ps.addBatch(); } } ps.executeBatch(); ps.close(); } ps = con.prepareStatement( "insert into tdwview(tbl_id, view_original_text, view_expanded_text, vtables) values(?,?,?,?)"); ps.setLong(1, tblID); ps.setString(2, tbl.getViewOriginalText()); ps.setString(3, tbl.getViewExpandedText()); ps.setString(4, tbl.getVtables().toLowerCase()); ps.executeUpdate(); ps.close(); con.commit(); success = true; } } catch (SQLException sqlex) { sqlex.printStackTrace(); LOG.error("create view error db=" + tbl.getDbName() + ", view=" + tbl.getTableName() + ", msg=" + sqlex.getMessage()); throw new MetaException(sqlex.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
@Override public void addSerde(String dbName, String tblName, AddSerdeDesc addSerdeDesc) throws InvalidOperationException, MetaException { Connection con;// w w w .j a v a 2 s .c o m PreparedStatement ps = null; 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; }