List of usage examples for java.sql PreparedStatement clearParameters
void clearParameters() throws SQLException;
From source file:org.linqs.psl.database.rdbms.RDBMSInserter.java
private void insertInternal(List<Double> values, List<List<Object>> data) { assert (values.size() == data.size()); int partitionID = partition.getID(); if (partitionID < 0) { throw new IllegalArgumentException("Partition IDs must be non-negative."); }// w w w . j av a 2s. c o m for (int rowIndex = 0; rowIndex < data.size(); rowIndex++) { List<Object> row = data.get(rowIndex); assert (row != null); if (row.size() != predicateInfo.argumentColumns().size()) { throw new IllegalArgumentException( String.format("Data on row %d length does not match for %s: Expecting: %d, Got: %d", rowIndex, partition.getName(), predicateInfo.argumentColumns().size(), row.size())); } } try (Connection connection = dataStore.getConnection(); PreparedStatement multiInsertStatement = connection.prepareStatement(multiInsertSQL); PreparedStatement singleInsertStatement = connection.prepareStatement(singleInsertSQL);) { int batchSize = 0; // We will go from the multi-insert to the single-insert when we don't have enough data to fill the multi-insert. PreparedStatement activeStatement = multiInsertStatement; int insertSize = DEFAULT_MULTIROW_COUNT; int rowIndex = 0; while (rowIndex < data.size()) { // Index for the current index. int paramIndex = 1; if (activeStatement == multiInsertStatement && data.size() - rowIndex < DEFAULT_MULTIROW_COUNT) { // Commit any records left in the multi-insert batch. if (batchSize > 0) { activeStatement.executeBatch(); activeStatement.clearBatch(); batchSize = 0; } activeStatement = singleInsertStatement; insertSize = 1; } for (int i = 0; i < insertSize; i++) { List<Object> row = data.get(rowIndex); Double value = values.get(rowIndex); // Partition activeStatement.setInt(paramIndex++, partitionID); // Value if (value == null || value.isNaN()) { activeStatement.setNull(paramIndex++, java.sql.Types.DOUBLE); } else { activeStatement.setDouble(paramIndex++, value); } for (int argIndex = 0; argIndex < predicateInfo.argumentColumns().size(); argIndex++) { Object argValue = row.get(argIndex); assert (argValue != null); if (argValue instanceof Integer) { activeStatement.setInt(paramIndex++, (Integer) argValue); } else if (argValue instanceof Double) { // The standard JDBC way to insert NaN is using setNull if (Double.isNaN((Double) argValue)) { activeStatement.setNull(paramIndex++, java.sql.Types.DOUBLE); } else { activeStatement.setDouble(paramIndex++, (Double) argValue); } } else if (argValue instanceof String) { // This is the most common value we get when someone is using InsertUtils. // The value may need to be convered from a string. activeStatement.setObject(paramIndex++, convertString((String) argValue, argIndex)); } else if (argValue instanceof UniqueIntID) { activeStatement.setInt(paramIndex++, ((UniqueIntID) argValue).getID()); } else if (argValue instanceof UniqueStringID) { activeStatement.setString(paramIndex++, ((UniqueStringID) argValue).getID()); } else { throw new IllegalArgumentException("Unknown data type for :" + argValue); } } rowIndex++; } activeStatement.addBatch(); batchSize++; if (batchSize >= DEFAULT_PAGE_SIZE) { activeStatement.executeBatch(); activeStatement.clearBatch(); batchSize = 0; } } if (batchSize > 0) { activeStatement.executeBatch(); activeStatement.clearBatch(); batchSize = 0; } activeStatement.clearParameters(); activeStatement = null; } catch (SQLException ex) { log.error(ex.getMessage()); throw new RuntimeException("Error inserting into RDBMS.", ex); } }
From source file:org.wso2.carbon.idp.mgt.dao.IdPManagementDAO.java
/** * @param federatedAuthenticatorConfig//from w ww . j a v a 2s . c om * @param dbConnection * @param idpId * @throws IdentityProviderManagementException * @throws SQLException */ private void updateFederatedAuthenticatorConfig(FederatedAuthenticatorConfig federatedAuthenticatorConfig, Connection dbConnection, int idpId, int tenantId) throws IdentityProviderManagementException, SQLException { PreparedStatement prepStmt = null; ResultSet rs = null; try { String sqlStmt = IdPManagementConstants.SQLQueries.UPDATE_IDP_AUTH_SQL; prepStmt = dbConnection.prepareStatement(sqlStmt); if (federatedAuthenticatorConfig.isEnabled()) { prepStmt.setString(1, "1"); } else { prepStmt.setString(1, "0"); } prepStmt.setInt(2, idpId); prepStmt.setString(3, CharacterEncoder.getSafeText(federatedAuthenticatorConfig.getName())); prepStmt.executeUpdate(); int authnId = getAuthenticatorIdentifier(dbConnection, idpId, federatedAuthenticatorConfig.getName()); for (Property property : federatedAuthenticatorConfig.getProperties()) { sqlStmt = IdPManagementConstants.SQLQueries.UPDATE_IDP_AUTH_PROP_SQL; prepStmt = dbConnection.prepareStatement(sqlStmt); prepStmt.setString(1, CharacterEncoder.getSafeText(property.getValue())); if (property.isConfidential()) { prepStmt.setString(2, "1"); } else { prepStmt.setString(2, "0"); } prepStmt.setInt(3, authnId); prepStmt.setString(4, CharacterEncoder.getSafeText(property.getName())); int rows = prepStmt.executeUpdate(); if (rows == 0) { // this should be an insert. prepStmt.clearParameters(); sqlStmt = IdPManagementConstants.SQLQueries.ADD_IDP_AUTH_PROP_SQL; prepStmt = dbConnection.prepareStatement(sqlStmt); prepStmt.setInt(1, authnId); prepStmt.setInt(2, tenantId); prepStmt.setString(3, CharacterEncoder.getSafeText(property.getName())); prepStmt.setString(4, CharacterEncoder.getSafeText(property.getValue())); if (property.isConfidential()) { prepStmt.setString(5, "1"); } else { prepStmt.setString(5, "0"); } prepStmt.executeUpdate(); } } } finally { IdentityApplicationManagementUtil.closeResultSet(rs); IdentityApplicationManagementUtil.closeStatement(prepStmt); } }
From source file:org.wso2.carbon.idp.mgt.dao.IdPManagementDAO.java
/** * @param dbConnection/*w ww . jav a 2 s.co m*/ * @param idPName * @param tenantId * @return * @throws IdentityProviderManagementException * @throws SQLException */ private FederatedAuthenticatorConfig[] getFederatedAuthenticatorConfigs(Connection dbConnection, String idPName, IdentityProvider federatedIdp, int tenantId) throws IdentityProviderManagementException, SQLException { int idPId = getIdentityProviderIdentifier(dbConnection, idPName, tenantId); PreparedStatement prepStmt = null; ResultSet rs = null; ResultSet proprs = null; String defaultAuthName = null; if (federatedIdp != null && federatedIdp.getDefaultAuthenticatorConfig() != null) { defaultAuthName = federatedIdp.getDefaultAuthenticatorConfig().getName(); } String sqlStmt = IdPManagementConstants.SQLQueries.GET_ALL_IDP_AUTH_SQL; Set<FederatedAuthenticatorConfig> federatedAuthenticatorConfigs = new HashSet<FederatedAuthenticatorConfig>(); try { prepStmt = dbConnection.prepareStatement(sqlStmt); prepStmt.setInt(1, idPId); rs = prepStmt.executeQuery(); while (rs.next()) { FederatedAuthenticatorConfig authnConfig = new FederatedAuthenticatorConfig(); int authnId = rs.getInt("ID"); authnConfig.setName(rs.getString("NAME")); if (("1").equals(rs.getString("IS_ENABLED"))) { authnConfig.setEnabled(true); } else { authnConfig.setEnabled(false); } authnConfig.setDisplayName(rs.getString("DISPLAY_NAME")); if (defaultAuthName != null && authnConfig.getName().equals(defaultAuthName)) { federatedIdp.getDefaultAuthenticatorConfig().setDisplayName(authnConfig.getDisplayName()); } sqlStmt = IdPManagementConstants.SQLQueries.GET_IDP_AUTH_PROPS_SQL; prepStmt.clearParameters(); prepStmt = dbConnection.prepareStatement(sqlStmt); prepStmt.setInt(1, authnId); proprs = prepStmt.executeQuery(); Set<Property> properties = new HashSet<Property>(); while (proprs.next()) { Property property = new Property(); property.setName(proprs.getString("PROPERTY_KEY")); property.setValue(proprs.getString("PROPERTY_VALUE")); if (("1").equals(proprs.getString("IS_SECRET"))) { property.setConfidential(true); } properties.add(property); } IdentityApplicationManagementUtil.closeResultSet(proprs); authnConfig.setProperties(properties.toArray(new Property[properties.size()])); federatedAuthenticatorConfigs.add(authnConfig); } return federatedAuthenticatorConfigs .toArray(new FederatedAuthenticatorConfig[federatedAuthenticatorConfigs.size()]); } finally { IdentityApplicationManagementUtil.closeResultSet(rs); IdentityApplicationManagementUtil.closeResultSet(proprs); IdentityApplicationManagementUtil.closeStatement(prepStmt); } }
From source file:org.jasig.portal.layout.dlm.RDBMDistributedLayoutStore.java
@Override protected int saveStructure(Node node, PreparedStatement structStmt, PreparedStatement parmStmt) throws SQLException { if (node == null) { // No more return 0; }/*from w ww. j a va 2 s. c om*/ if (node.getNodeName().equals("parameter")) { //parameter, skip it and go on to the next node return this.saveStructure(node.getNextSibling(), structStmt, parmStmt); } if (!(node instanceof Element)) { return 0; } final Element structure = (Element) node; if (LOG.isDebugEnabled()) { LOG.debug("saveStructure XML content: " + XmlUtilitiesImpl.toString(node)); } // determine the struct_id for storing in the db. For incorporated nodes in // the plf their ID is a system-wide unique ID while their struct_id for // storing in the db is cached in a dlm:plfID attribute. int saveStructId = -1; final String plfID = structure.getAttribute(Constants.ATT_PLF_ID); if (!plfID.equals("")) { saveStructId = Integer.parseInt(plfID.substring(1)); } else { final String id = structure.getAttribute("ID"); saveStructId = Integer.parseInt(id.substring(1)); } int nextStructId = 0; int childStructId = 0; int chanId = -1; IPortletDefinition portletDef = null; final boolean isChannel = node.getNodeName().equals("channel"); if (isChannel) { chanId = Integer.parseInt(node.getAttributes().getNamedItem("chanID").getNodeValue()); portletDef = this.portletDefinitionRegistry.getPortletDefinition(String.valueOf(chanId)); if (portletDef == null) { //Portlet doesn't exist any more, drop the layout node return 0; } } if (node.hasChildNodes()) { childStructId = this.saveStructure(node.getFirstChild(), structStmt, parmStmt); } nextStructId = this.saveStructure(node.getNextSibling(), structStmt, parmStmt); structStmt.clearParameters(); structStmt.setInt(1, saveStructId); structStmt.setInt(2, nextStructId); structStmt.setInt(3, childStructId); final String externalId = structure.getAttribute("external_id"); if (externalId != null && externalId.trim().length() > 0) { final Integer eID = new Integer(externalId); structStmt.setInt(4, eID.intValue()); } else { structStmt.setNull(4, java.sql.Types.NUMERIC); } if (isChannel) { structStmt.setInt(5, chanId); structStmt.setNull(6, java.sql.Types.VARCHAR); } else { structStmt.setNull(5, java.sql.Types.NUMERIC); structStmt.setString(6, structure.getAttribute("name")); } final String structType = structure.getAttribute("type"); structStmt.setString(7, structType); structStmt.setString(8, RDBMServices.dbFlag(xmlBool(structure.getAttribute("hidden")))); structStmt.setString(9, RDBMServices.dbFlag(xmlBool(structure.getAttribute("immutable")))); structStmt.setString(10, RDBMServices.dbFlag(xmlBool(structure.getAttribute("unremovable")))); if (LOG.isDebugEnabled()) { LOG.debug(structStmt.toString()); } structStmt.executeUpdate(); // code to persist extension attributes for dlm final NamedNodeMap attribs = node.getAttributes(); for (int i = 0; i < attribs.getLength(); i++) { final Node attrib = attribs.item(i); final String name = attrib.getNodeName(); if (name.startsWith(Constants.NS) && !name.equals(Constants.ATT_PLF_ID) && !name.equals(Constants.ATT_FRAGMENT) && !name.equals(Constants.ATT_PRECEDENCE)) { // a cp extension attribute. Push into param table. parmStmt.clearParameters(); parmStmt.setInt(1, saveStructId); parmStmt.setString(2, name); parmStmt.setString(3, attrib.getNodeValue()); if (LOG.isDebugEnabled()) { LOG.debug(parmStmt.toString()); } parmStmt.executeUpdate(); } } final NodeList parameters = node.getChildNodes(); if (parameters != null && isChannel) { for (int i = 0; i < parameters.getLength(); i++) { if (parameters.item(i).getNodeName().equals("parameter")) { final Element parmElement = (Element) parameters.item(i); final NamedNodeMap nm = parmElement.getAttributes(); final String parmName = nm.getNamedItem("name").getNodeValue(); final String parmValue = nm.getNamedItem("value").getNodeValue(); final Node override = nm.getNamedItem("override"); // if no override specified then default to allowed if (override != null && !override.getNodeValue().equals("yes")) { // can't override } else { // override only for adhoc or if diff from chan def final IPortletDefinitionParameter cp = portletDef.getParameter(parmName); if (cp == null || !cp.getValue().equals(parmValue)) { parmStmt.clearParameters(); parmStmt.setInt(1, saveStructId); parmStmt.setString(2, parmName); parmStmt.setString(3, parmValue); if (LOG.isDebugEnabled()) { LOG.debug(parmStmt); } parmStmt.executeUpdate(); } } } } } return saveStructId; }
From source file:sh.isaac.convert.rxnorm.standard.RxNormMojo.java
/** * Load meta data./*from w ww . j a v a 2 s. co m*/ * * @throws Exception the exception */ private void loadMetaData() throws Exception { this.ptRefsets = new PT_Refsets(rxNormName); this.ptRefsets.addProperty(cpcRefsetConceptKey); final PropertyType sourceMetadata = new PT_SAB_Metadata(); this.ptRelationshipMetadata = new PT_Relationship_Metadata(); this.ptUMLSAttributes = new PT_Annotations(); this.importUtil.loadMetaDataItems( Arrays.asList(this.ptRefsets, sourceMetadata, this.ptRelationshipMetadata, this.ptUMLSAttributes), this.metaDataRoot.getPrimordialUuid()); // Attributes from MRDoc // dynamically add more attributes from *DOC { ConsoleUtil.println("Creating attribute types"); this.ptTermAttributes = new BPT_Annotations(rxNormName) { }; this.ptTermAttributes.indexByAltNames(); // extra logic at the end to keep NDC's from any sab when processing RXNorm try (Statement s = this.db.getConnection().createStatement(); // extra logic at the end to keep NDC's from any sab when processing RXNorm ResultSet rs = s.executeQuery("SELECT VALUE, TYPE, EXPL from " + this.tablePrefix + "DOC where DOCKEY = 'ATN' and VALUE in (select distinct ATN from " + this.tablePrefix + "SAT" + " where " + createSabQueryPart("", false) + " or ATN='NDC')")) { while (rs.next()) { final String abbreviation = rs.getString("VALUE"); final String type = rs.getString("TYPE"); final String expansion = rs.getString("EXPL"); if (!type.equals("expanded_form")) { throw new RuntimeException( "Unexpected type in the attribute data within DOC: '" + type + "'"); } String altName = null; String description = null; if (expansion.length() > 30) { description = expansion; } else { altName = expansion; } final AbbreviationExpansion ae = this.abbreviationExpansions.get(abbreviation); if (ae == null) { ConsoleUtil.printErrorln("No Abbreviation Expansion found for " + abbreviation); this.ptTermAttributes.addProperty(abbreviation, altName, description); } else { this.ptTermAttributes.addProperty(ae.getExpansion(), ae.getAbbreviation(), ae.getDescription()); } } } if (this.ptTermAttributes.getProperties().size() > 0) { this.importUtil.loadMetaDataItems(this.ptTermAttributes, this.metaDataRoot.getPrimordialUuid()); } } // description types { ConsoleUtil.println("Creating description_ types"); this.ptDescriptions = new PT_Descriptions(rxNormName); this.ptDescriptions.indexByAltNames(); final PreparedStatement ps; try (Statement s = this.db.getConnection().createStatement()) { ResultSet usedDescTypes; usedDescTypes = s .executeQuery("select distinct TTY from RXNCONSO WHERE " + createSabQueryPart("", false)); ps = this.db.getConnection().prepareStatement( "select TYPE, EXPL from " + this.tablePrefix + "DOC where DOCKEY='TTY' and VALUE=?"); while (usedDescTypes.next()) { final String tty = usedDescTypes.getString(1); ps.setString(1, tty); String expandedForm; final HashSet<String> classes; try (ResultSet descInfo = ps.executeQuery()) { expandedForm = null; classes = new HashSet<>(); while (descInfo.next()) { final String type = descInfo.getString("TYPE"); final String expl = descInfo.getString("EXPL"); switch (type) { case "expanded_form": if (expandedForm != null) { throw new RuntimeException("Expected name to be null!"); } expandedForm = expl; break; case "tty_class": classes.add(expl); break; default: throw new RuntimeException("Unexpected type in DOC for '" + tty + "'"); } } } ps.clearParameters(); Property p = null; final AbbreviationExpansion ae = this.abbreviationExpansions.get(tty); if (ae == null) { ConsoleUtil.printErrorln("No Abbreviation Expansion found for " + tty); p = makeDescriptionType(tty, expandedForm, null, classes); } else { p = makeDescriptionType(ae.getExpansion(), ae.getAbbreviation(), ae.getDescription(), classes); } this.ptDescriptions.addProperty(p); for (final String tty_class : classes) { this.importUtil.addStringAnnotation(ComponentReference.fromConcept(p.getUUID()), tty_class, this.ptUMLSAttributes.getProperty("tty_class").getUUID(), Status.ACTIVE); } } usedDescTypes.close(); } ps.close(); if (this.ptDescriptions.getProperties().size() > 0) { this.importUtil.loadMetaDataItems(this.ptDescriptions, this.metaDataRoot.getPrimordialUuid()); } } loadRelationshipMetadata(); // STYPE values this.sTypes = new HashMap<>(); { ConsoleUtil.println("Creating STYPE types"); try (Statement s = this.db.getConnection().createStatement(); ResultSet rs = s.executeQuery("SELECT DISTINCT VALUE, TYPE, EXPL FROM " + this.tablePrefix + "DOC where DOCKEY like 'STYPE%'")) { while (rs.next()) { final String sType = rs.getString("VALUE"); final String type = rs.getString("TYPE"); final String name = rs.getString("EXPL"); if (!type.equals("expanded_form")) { throw new RuntimeException( "Unexpected type in the attribute data within DOC: '" + type + "'"); } final ComponentReference c = ComponentReference.fromConcept(this.importUtil.createConcept( ConverterUUID.createNamespaceUUIDFromString( this.ptUMLSAttributes.getProperty("STYPE").getUUID() + ":" + name), name, null, null, sType, this.ptUMLSAttributes.getProperty("STYPE").getUUID(), null)); this.sTypes.put(name, c.getPrimordialUuid()); this.sTypes.put(sType, c.getPrimordialUuid()); } } } this.suppress = xDocLoaderHelper("SUPPRESS", "Suppress", false, this.ptUMLSAttributes.getProperty("SUPPRESS").getUUID()); // Not yet loading co-occurrence data yet, so don't need these yet. // xDocLoaderHelper("COA", "Attributes of co-occurrence", false); // xDocLoaderHelper("COT", "Type of co-occurrence", true); final HashMap<String, UUID> contextTypes = xDocLoaderHelper("CXTY", "Context Type", false, sourceMetadata.getProperty("CXTY").getUUID()); // not yet loading mappings - so don't need this yet // xDocLoaderHelper("FROMTYPE", "Mapping From Type", false); // xDocLoaderHelper("TOTYPE", "Mapping To Type", false); // MAPATN - not yet used in UMLS // Handle the languages // Not actually doing anythign with these at the moment, we just map to metadata languages. { try (Statement s = this.db.getConnection().createStatement(); ResultSet rs = s.executeQuery("SELECT * from " + this.tablePrefix + "DOC where DOCKEY = 'LAT' and VALUE in (select distinct LAT from " + this.tablePrefix + "CONSO where " + createSabQueryPart("", false) + ")")) { while (rs.next()) { final String abbreviation = rs.getString("VALUE"); final String type = rs.getString("TYPE"); // String expansion = rs.getString("EXPL"); if (!type.equals("expanded_form")) { throw new RuntimeException( "Unexpected type in the language data within DOC: '" + type + "'"); } if (abbreviation.equals("ENG") || abbreviation.equals("SPA")) { // use official ISAAC languages if (abbreviation.equals("ENG") || abbreviation.equals("SPA")) { // We can map these onto metadata types. } else { throw new RuntimeException("unsupported language"); } } } } } // And Source Restriction Levels { ConsoleUtil.println("Creating Source Restriction Level types"); this.sourceRestrictionLevels = new HashMap<>(); try (PreparedStatement ps = this.db.getConnection().prepareStatement( "SELECT VALUE, TYPE, EXPL from " + this.tablePrefix + "DOC where DOCKEY=? ORDER BY VALUE")) { ps.setString(1, "SRL"); final ResultSet rs = ps.executeQuery(); String value = null; String description = null; String uri = null; // Two entries per SRL, read two rows, create an entry. while (rs.next()) { String type = rs.getString("TYPE"); String expl = rs.getString("EXPL"); switch (type) { case "expanded_form": description = expl; break; case "uri": uri = expl; break; default: throw new RuntimeException("oops"); } if (value == null) { value = rs.getString("VALUE"); } else { if (!value.equals(rs.getString("VALUE"))) { throw new RuntimeException("oops"); } if ((description == null) || (uri == null)) { throw new RuntimeException("oops"); } final ComponentReference c = ComponentReference.fromConcept(this.importUtil.createConcept( ConverterUUID.createNamespaceUUIDFromString( sourceMetadata.getProperty("SRL").getUUID() + ":" + value), value, null, null, description, sourceMetadata.getProperty("SRL").getUUID(), null)); this.sourceRestrictionLevels.put(value, c.getPrimordialUuid()); this.importUtil.addStringAnnotation(c, uri, this.ptUMLSAttributes.getProperty("URI").getUUID(), Status.ACTIVE); type = null; expl = null; value = null; } } rs.close(); } } // And Source vocabularies final PreparedStatement getSABMetadata = this.db.getConnection().prepareStatement( "Select * from " + this.tablePrefix + "SAB where (VSAB = ? or (RSAB = ? and CURVER='Y' ))"); { ConsoleUtil.println("Creating Source Vocabulary types"); this.ptSABs = new PropertyType("Source Vocabularies", true, DynamicDataType.STRING) { }; this.ptSABs.indexByAltNames(); final HashSet<String> sabList = new HashSet<>(); sabList.addAll(this.sabsToInclude); Statement s = this.db.getConnection().createStatement(); ResultSet rs = s.executeQuery("select distinct SAB from RXNSAT where ATN='NDC'"); while (rs.next()) { sabList.add(rs.getString("SAB")); } rs.close(); s.close(); for (final String currentSab : sabList) { s = this.db.getConnection().createStatement(); rs = s.executeQuery("SELECT SON from " + this.tablePrefix + "SAB WHERE (VSAB='" + currentSab + "' or (RSAB='" + currentSab + "' and CURVER='Y'))"); if (rs.next()) { final String son = rs.getString("SON"); final Property p = this.ptSABs.addProperty(son, currentSab, null); final ComponentReference cr = ComponentReference.fromConcept(p.getUUID()); try { // lookup the other columns for the row with this newly added RSAB terminology getSABMetadata.setString(1, (p.getSourcePropertyAltName() == null) ? p.getSourcePropertyNameFQN() : p.getSourcePropertyAltName()); getSABMetadata.setString(2, (p.getSourcePropertyAltName() == null) ? p.getSourcePropertyNameFQN() : p.getSourcePropertyAltName()); try (ResultSet rs2 = getSABMetadata.executeQuery()) { if (rs2.next()) { for (final Property metadataProperty : sourceMetadata.getProperties()) { final String columnName = (metadataProperty.getSourcePropertyAltName() == null) ? metadataProperty.getSourcePropertyNameFQN() : metadataProperty.getSourcePropertyAltName(); final String columnValue = rs2.getString(columnName); if (columnValue == null) { continue; } switch (columnName) { case "SRL": this.importUtil.addUUIDAnnotation(cr, this.sourceRestrictionLevels.get(columnValue), metadataProperty.getUUID()); break; case "CXTY": this.importUtil.addUUIDAnnotation(cr, contextTypes.get(columnValue), sourceMetadata.getProperty("CXTY").getUUID()); break; default: this.importUtil.addStringAnnotation(cr, columnValue, metadataProperty.getUUID(), Status.ACTIVE); break; } } } if (rs2.next()) { throw new RuntimeException( "Too many sabs. Perhaps you should be using versioned sabs!"); } } } catch (final SQLException e) { throw new RuntimeException("Error loading *SAB", e); } } else { throw new RuntimeException("Too few? SABs - perhaps you need to use versioned SABs."); } if (rs.next()) { throw new RuntimeException( "Too many SABs for '" + currentSab + "' - perhaps you need to use versioned SABs."); } rs.close(); s.close(); } this.importUtil.loadMetaDataItems(this.ptSABs, this.metaDataRoot.getPrimordialUuid()); getSABMetadata.close(); } // And semantic types { ConsoleUtil.println("Creating semantic types"); try (Statement s = this.db.getConnection().createStatement(); ResultSet rs = s .executeQuery("SELECT distinct TUI, STN, STY from " + this.tablePrefix + "STY")) { while (rs.next()) { final String tui = rs.getString("TUI"); final String stn = rs.getString("STN"); final String sty = rs.getString("STY"); final ComponentReference c = ComponentReference.fromConcept(this.importUtil.createConcept( ConverterUUID.createNamespaceUUIDFromString( this.ptUMLSAttributes.getProperty("STY").getUUID() + ":" + sty), sty, null, null, null, this.ptUMLSAttributes.getProperty("STY").getUUID(), null)); this.semanticTypes.put(tui, c.getPrimordialUuid()); this.importUtil.addStringAnnotation(c, tui, this.ptUMLSAttributes.getProperty("TUI").getUUID(), Status.ACTIVE); this.importUtil.addStringAnnotation(c, stn, this.ptUMLSAttributes.getProperty("STN").getUUID(), Status.ACTIVE); } } } }
From source file:org.apache.tajo.catalog.store.AbstractDBStore.java
@Override public void addPartitions(String databaseName, String tableName, List<CatalogProtos.PartitionDescProto> partitions, boolean ifNotExists) throws UndefinedDatabaseException, UndefinedTableException, UndefinedPartitionMethodException { final int databaseId = getDatabaseId(databaseName); final int tableId = getTableId(databaseId, databaseName, tableName); ensurePartitionTable(tableName, tableId); Connection conn = null;// w ww .jav a2 s .co m // To delete existing partition keys PreparedStatement pstmt1 = null; // To delete existing partition; PreparedStatement pstmt2 = null; // To insert a partition PreparedStatement pstmt3 = null; // To insert partition keys PreparedStatement pstmt4 = null; PartitionDescProto partitionDesc = null; try { conn = getConnection(); conn.setAutoCommit(false); int currentIndex = 0, lastIndex = 0; pstmt1 = conn.prepareStatement(deletePartitionKeysSql); pstmt2 = conn.prepareStatement(deletePartitionSql); pstmt3 = conn.prepareStatement(insertPartitionSql); pstmt4 = conn.prepareStatement(insertPartitionKeysSql); // Set a batch size like 1000. This avoids SQL injection and also takes care of out of memory issue. int batchSize = conf.getInt(TajoConf.ConfVars.PARTITION_DYNAMIC_BULK_INSERT_BATCH_SIZE.varname, 1000); for (currentIndex = 0; currentIndex < partitions.size(); currentIndex++) { PartitionDescProto partition = partitions.get(currentIndex); try { partitionDesc = getPartition(databaseName, tableName, partition.getPartitionName()); // Delete existing partition and partition keys if (ifNotExists) { pstmt1.setInt(1, partitionDesc.getId()); pstmt1.addBatch(); pstmt1.clearParameters(); pstmt2.setInt(1, partitionDesc.getId()); pstmt2.addBatch(); pstmt2.clearParameters(); } } catch (UndefinedPartitionException e) { } // Insert partition pstmt3.setInt(1, tableId); pstmt3.setString(2, partition.getPartitionName()); pstmt3.setString(3, partition.getPath()); pstmt3.setLong(4, partition.getNumBytes()); pstmt3.addBatch(); pstmt3.clearParameters(); // Insert partition keys for (int i = 0; i < partition.getPartitionKeysCount(); i++) { PartitionKeyProto partitionKey = partition.getPartitionKeys(i); pstmt4.setInt(1, tableId); pstmt4.setString(2, partition.getPartitionName()); pstmt4.setInt(3, tableId); pstmt4.setString(4, partitionKey.getColumnName()); pstmt4.setString(5, partitionKey.getPartitionValue()); pstmt4.addBatch(); pstmt4.clearParameters(); } // Execute batch if (currentIndex >= lastIndex + batchSize && lastIndex != currentIndex) { pstmt1.executeBatch(); pstmt1.clearBatch(); pstmt2.executeBatch(); pstmt2.clearBatch(); pstmt3.executeBatch(); pstmt3.clearBatch(); pstmt4.executeBatch(); pstmt4.clearBatch(); lastIndex = currentIndex; } } // Execute existing batch queries if (lastIndex != currentIndex) { pstmt1.executeBatch(); pstmt2.executeBatch(); pstmt3.executeBatch(); pstmt4.executeBatch(); } if (conn != null) { conn.commit(); } } catch (SQLException se) { if (conn != null) { try { conn.rollback(); } catch (SQLException e) { LOG.error(e, e); } } throw new TajoInternalError(se); } finally { CatalogUtil.closeQuietly(pstmt1); CatalogUtil.closeQuietly(pstmt2); CatalogUtil.closeQuietly(pstmt3); CatalogUtil.closeQuietly(pstmt4); } }
From source file:com.mirth.connect.donkey.server.data.jdbc.JdbcDao.java
@Override public void insertMessageAttachment(String channelId, long messageId, Attachment attachment) { logger.debug(channelId + "/" + messageId + ": inserting message attachment"); try {/*from w w w.j av a2s.c o m*/ PreparedStatement statement = prepareStatement("insertMessageAttachment", channelId); statement.setString(1, attachment.getId()); statement.setLong(2, messageId); statement.setString(3, attachment.getType()); // The size of each segment of the attachment. int chunkSize = 10000000; if (attachment.getContent().length <= chunkSize) { // If there is only one segment, just store it statement.setInt(4, 1); statement.setInt(5, attachment.getContent().length); statement.setBytes(6, attachment.getContent()); statement.executeUpdate(); } else { // Use an input stream on the attachment content to segment the data. ByteArrayInputStream inputStream = new ByteArrayInputStream(attachment.getContent()); // The order of the segment int segmentIndex = 1; // As long as there are bytes left while (inputStream.available() > 0) { // Set the segment number statement.setInt(4, segmentIndex++); // Determine the segment size. If there are more bytes left than the chunk size, the size is the chunk size. Otherwise it is the number of remaining bytes int segmentSize = Math.min(chunkSize, inputStream.available()); // Create a byte array to store the chunk byte[] segment = new byte[segmentSize]; // Read the chunk from the input stream to the byte array inputStream.read(segment, 0, segmentSize); // Set the segment size statement.setInt(5, segmentSize); // Set the byte data statement.setBytes(6, segment); // Perform the insert statement.executeUpdate(); } } // Clear the parameters because the data held in memory could be quite large. statement.clearParameters(); } catch (SQLException e) { throw new DonkeyDaoException(e); } }
From source file:i5.las2peer.services.mobsos.SurveyService.java
/** * Stores a new questionnaire described with JSON into the MobSOS database. * The MobSOS database thereby generates a new id returned by this method. * @throws UnsupportedEncodingException * @throws ParseException //from w w w .ja v a2s . c o m */ private int storeNewQuestionnaire(JSONObject questionnaire) throws IllegalArgumentException, SQLException, UnsupportedEncodingException, ParseException { String sub = (String) getActiveUserInfo().get("sub"); Connection conn = null; PreparedStatement stmt = null; ResultSet rset = null; try { conn = dataSource.getConnection(); stmt = conn.prepareStatement("insert into " + jdbcSchema + ".questionnaire(owner, organization, logo, name, description, lang) values (?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS); stmt.clearParameters(); stmt.setString(1, sub); // active agent becomes owner automatically stmt.setString(2, (String) questionnaire.get("organization")); stmt.setString(3, (String) questionnaire.get("logo")); stmt.setString(4, (String) questionnaire.get("name")); stmt.setString(5, (String) questionnaire.get("description")); stmt.setString(6, (String) questionnaire.get("lang")); stmt.executeUpdate(); ResultSet rs = stmt.getGeneratedKeys(); if (rs.next()) { return rs.getInt(1); } else { throw new NoSuchElementException("No new questionnaire was created!"); } } catch (UnsupportedOperationException e) { e.printStackTrace(); } finally { try { if (rset != null) rset.close(); } catch (Exception e) { e.printStackTrace(); } try { if (stmt != null) stmt.close(); } catch (Exception e) { e.printStackTrace(); } try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } throw new NoSuchElementException("No new questionnaire was created!"); }
From source file:i5.las2peer.services.mobsos.SurveyService.java
/** * Stores a new survey described with JSON into the MobSOS database. * The MobSOS database thereby generates a new id returned by this method. * @throws ParseException //from w w w. j a v a2s. c o m */ private int storeNewSurvey(JSONObject survey) throws IllegalArgumentException, SQLException, ParseException { Connection conn = null; PreparedStatement stmt = null; ResultSet rset = null; String sub = (String) getActiveUserInfo().get("sub"); try { conn = dataSource.getConnection(); stmt = conn.prepareStatement("insert into " + jdbcSchema + ".survey(owner, organization, logo, name, description, resource, start, end, lang ) values (?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS); stmt.clearParameters(); stmt.setString(1, sub); // active agent becomes owner automatically stmt.setString(2, (String) survey.get("organization")); stmt.setString(3, (String) survey.get("logo")); stmt.setString(4, (String) survey.get("name")); stmt.setString(5, (String) survey.get("description")); stmt.setString(6, (String) survey.get("resource")); stmt.setTimestamp(7, new Timestamp(DatatypeConverter.parseDateTime((String) survey.get("start")).getTimeInMillis())); stmt.setTimestamp(8, new Timestamp(DatatypeConverter.parseDateTime((String) survey.get("end")).getTimeInMillis())); stmt.setString(9, (String) survey.get("lang")); stmt.executeUpdate(); ResultSet rs = stmt.getGeneratedKeys(); if (rs.next()) { return rs.getInt(1); } else { throw new NoSuchElementException("No new survey was created!"); } } catch (UnsupportedOperationException e) { e.printStackTrace(); } finally { try { if (rset != null) rset.close(); } catch (Exception e) { e.printStackTrace(); } try { if (stmt != null) stmt.close(); } catch (Exception e) { e.printStackTrace(); } try { if (conn != null) conn.close(); } catch (Exception e) { e.printStackTrace(); } } throw new NoSuchElementException("No new survey was created!"); }
From source file:org.apache.tajo.catalog.store.AbstractDBStore.java
@Override public void createTable(final CatalogProtos.TableDescProto table) throws UndefinedDatabaseException, DuplicateTableException { Connection conn = null;// w ww . j av a2 s . c o m PreparedStatement pstmt = null; ResultSet res = null; final String[] splitted = IdentifierUtil.splitTableName(table.getTableName()); if (splitted.length == 1) { throw new TajoInternalError( "createTable() requires a qualified table name, but it is '" + table.getTableName() + "'"); } final String databaseName = splitted[0]; final String tableName = splitted[1]; if (existTable(databaseName, tableName)) { throw new DuplicateTableException(tableName); } final int dbid = getDatabaseId(databaseName); try { conn = getConnection(); conn.setAutoCommit(false); String sql = "INSERT INTO TABLES (DB_ID, " + COL_TABLES_NAME + ", TABLE_TYPE, PATH, DATA_FORMAT, HAS_SELF_DESCRIBE_SCHEMA) VALUES(?, ?, ?, ?, ?, ?) "; if (LOG.isDebugEnabled()) { LOG.debug(sql); } pstmt = conn.prepareStatement(sql); pstmt.setInt(1, dbid); pstmt.setString(2, tableName); if (table.getIsExternal()) { pstmt.setString(3, TableType.EXTERNAL.name()); } else { pstmt.setString(3, TableType.MANAGED.name()); } pstmt.setString(4, table.getPath()); pstmt.setString(5, table.getMeta().getDataFormat()); pstmt.setBoolean(6, table.getSchema() == null); pstmt.executeUpdate(); pstmt.close(); String tidSql = "SELECT TID from " + TB_TABLES + " WHERE " + COL_DATABASES_PK + "=? AND " + COL_TABLES_NAME + "=?"; pstmt = conn.prepareStatement(tidSql); pstmt.setInt(1, dbid); pstmt.setString(2, tableName); res = pstmt.executeQuery(); if (!res.next()) { throw new TajoInternalError("There is no TID matched to '" + table.getTableName() + '"'); } int tableId = res.getInt("TID"); res.close(); pstmt.close(); String colSql = "INSERT INTO " + TB_COLUMNS + // 1 2 3 4 " (TID, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE)" + " VALUES(?, ?, ?, ?) "; if (LOG.isDebugEnabled()) { LOG.debug(colSql); } pstmt = conn.prepareStatement(colSql); for (int i = 0; i < table.getSchema().getFieldsCount(); i++) { ColumnProto col = table.getSchema().getFields(i); org.apache.tajo.type.Type type = TypeProtobufEncoder.decode(col.getType()); pstmt.setInt(1, tableId); pstmt.setString(2, extractSimpleName(col.getName())); pstmt.setInt(3, i); pstmt.setString(4, TypeStringEncoder.encode(type)); pstmt.addBatch(); pstmt.clearParameters(); } pstmt.executeBatch(); pstmt.close(); if (table.getMeta().hasParams()) { String propSQL = "INSERT INTO " + TB_OPTIONS + "(TID, KEY_, VALUE_) VALUES(?, ?, ?)"; if (LOG.isDebugEnabled()) { LOG.debug(propSQL); } pstmt = conn.prepareStatement(propSQL); for (KeyValueProto entry : table.getMeta().getParams().getKeyvalList()) { pstmt.setInt(1, tableId); pstmt.setString(2, entry.getKey()); pstmt.setString(3, entry.getValue()); pstmt.addBatch(); pstmt.clearParameters(); } pstmt.executeBatch(); pstmt.close(); } if (table.hasStats()) { String statSql = "INSERT INTO " + TB_STATISTICS + " (TID, NUM_ROWS, NUM_BYTES) VALUES(?, ?, ?)"; if (LOG.isDebugEnabled()) { LOG.debug(statSql); } pstmt = conn.prepareStatement(statSql); pstmt.setInt(1, tableId); pstmt.setLong(2, table.getStats().getNumRows()); pstmt.setLong(3, table.getStats().getNumBytes()); pstmt.executeUpdate(); pstmt.close(); } if (table.hasPartition()) { String partSql = "INSERT INTO PARTITION_METHODS (TID, PARTITION_TYPE, EXPRESSION, EXPRESSION_SCHEMA) VALUES(?, ?, ?, ?)"; if (LOG.isDebugEnabled()) { LOG.debug(partSql); } pstmt = conn.prepareStatement(partSql); pstmt.setInt(1, tableId); pstmt.setString(2, table.getPartition().getPartitionType().name()); pstmt.setString(3, table.getPartition().getExpression()); pstmt.setBytes(4, table.getPartition().getExpressionSchema().toByteArray()); pstmt.executeUpdate(); } // If there is no error, commit the changes. conn.commit(); } catch (SQLException se) { if (conn != null) { try { conn.rollback(); } catch (SQLException e) { LOG.error(e, e); } } throw new TajoInternalError(se); } finally { CatalogUtil.closeQuietly(pstmt, res); } }