List of usage examples for java.sql Statement RETURN_GENERATED_KEYS
int RETURN_GENERATED_KEYS
To view the source code for java.sql Statement RETURN_GENERATED_KEYS.
Click Source Link
From source file:nl.nn.adapterframework.jdbc.JdbcTransactionalStorage.java
protected String storeMessageInDatabase(Connection conn, String messageId, String correlationId, Timestamp receivedDateTime, String comments, String label, Serializable message) throws IOException, SQLException, JdbcException, SenderException { PreparedStatement stmt = null; try {//from w w w . j a v a 2 s . c o m IDbmsSupport dbmsSupport = getDbmsSupport(); if (log.isDebugEnabled()) log.debug("preparing insert statement [" + insertQuery + "]"); if (!dbmsSupport.mustInsertEmptyBlobBeforeData()) { stmt = conn.prepareStatement(insertQuery, Statement.RETURN_GENERATED_KEYS); } else { stmt = conn.prepareStatement(insertQuery); } stmt.clearParameters(); int parPos = 0; if (StringUtils.isNotEmpty(getTypeField())) { stmt.setString(++parPos, type); } if (StringUtils.isNotEmpty(getSlotId())) { stmt.setString(++parPos, getSlotId()); } if (StringUtils.isNotEmpty(getHostField())) { stmt.setString(++parPos, host); } if (StringUtils.isNotEmpty(getLabelField())) { stmt.setString(++parPos, label); } stmt.setString(++parPos, messageId); stmt.setString(++parPos, correlationId); stmt.setTimestamp(++parPos, receivedDateTime); stmt.setString(++parPos, comments); if (type.equalsIgnoreCase(TYPE_MESSAGELOG_PIPE) || type.equalsIgnoreCase(TYPE_MESSAGELOG_RECEIVER)) { if (getRetention() < 0) { stmt.setTimestamp(++parPos, null); } else { Date date = new Date(); Calendar cal = Calendar.getInstance(); cal.setTime(date); cal.add(Calendar.DAY_OF_MONTH, getRetention()); stmt.setTimestamp(++parPos, new Timestamp(cal.getTime().getTime())); } } else { stmt.setTimestamp(++parPos, null); } if (!isStoreFullMessage()) { if (isOnlyStoreWhenMessageIdUnique()) { stmt.setString(++parPos, messageId); stmt.setString(++parPos, slotId); } stmt.execute(); return null; } if (!dbmsSupport.mustInsertEmptyBlobBeforeData()) { ByteArrayOutputStream out = new ByteArrayOutputStream(); if (isBlobsCompressed()) { DeflaterOutputStream dos = new DeflaterOutputStream(out); ObjectOutputStream oos = new ObjectOutputStream(dos); oos.writeObject(message); dos.close(); } else { ObjectOutputStream oos = new ObjectOutputStream(out); oos.writeObject(message); } stmt.setBytes(++parPos, out.toByteArray()); if (isOnlyStoreWhenMessageIdUnique()) { stmt.setString(++parPos, messageId); stmt.setString(++parPos, slotId); } stmt.execute(); ResultSet rs = stmt.getGeneratedKeys(); if (rs.next()) { return rs.getString(1); } else { return null; } } if (isOnlyStoreWhenMessageIdUnique()) { stmt.setString(++parPos, messageId); stmt.setString(++parPos, slotId); } stmt.execute(); int updateCount = stmt.getUpdateCount(); if (log.isDebugEnabled()) log.debug("update count for insert statement: " + updateCount); if (updateCount > 0) { if (log.isDebugEnabled()) log.debug("preparing select statement [" + selectKeyQuery + "]"); stmt = conn.prepareStatement(selectKeyQuery); ResultSet rs = null; try { // retrieve the key rs = stmt.executeQuery(); if (!rs.next()) { throw new SenderException("could not retrieve key of stored message"); } String newKey = rs.getString(1); rs.close(); // and update the blob if (log.isDebugEnabled()) log.debug("preparing update statement [" + updateBlobQuery + "]"); stmt = conn.prepareStatement(updateBlobQuery); stmt.clearParameters(); stmt.setString(1, newKey); rs = stmt.executeQuery(); if (!rs.next()) { throw new SenderException("could not retrieve row for stored message [" + messageId + "]"); } // String newKey = rs.getString(1); // BLOB blob = (BLOB)rs.getBlob(2); Object blobHandle = dbmsSupport.getBlobUpdateHandle(rs, 1); OutputStream out = dbmsSupport.getBlobOutputStream(rs, 1, blobHandle); // OutputStream out = JdbcUtil.getBlobUpdateOutputStream(rs,1); if (isBlobsCompressed()) { DeflaterOutputStream dos = new DeflaterOutputStream(out); ObjectOutputStream oos = new ObjectOutputStream(dos); oos.writeObject(message); oos.close(); dos.close(); } else { ObjectOutputStream oos = new ObjectOutputStream(out); oos.writeObject(message); oos.close(); } out.close(); dbmsSupport.updateBlob(rs, 1, blobHandle); return newKey; } finally { if (rs != null) { rs.close(); } } } else { if (isOnlyStoreWhenMessageIdUnique()) { return "already there"; } else { throw new SenderException( "update count for update statement not greater than 0 [" + updateCount + "]"); } } } finally { if (stmt != null) { stmt.close(); } } }
From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java
private int createGrade(Connection connection, String toContextId, int gradeType, int forumId, int topicId, int categoryId, float gradePoints, int addToGradebook, boolean minPostsRequired, int minPosts, String gradebookTitle) throws SQLException { int gradeId = -1; ResultSet rs = null;// w w w . jav a2 s. c om String gradebookToolId = ServerConfigurationService.getString(JForumGradeService.GRADEBOOK_TOOL_ID); if ((gradebookToolId == null) || (gradebookToolId.trim().length() == 0)) { gradebookToolId = "sakai.gradebook.tool"; } Site site = null; try { site = SiteService.getSite(ToolManager.getCurrentPlacement().getContext()); } catch (IdUnusedException e) { if (logger.isWarnEnabled()) { logger.warn(e.toString(), e); } } boolean gradebookExists = false; if ((site != null) && (site.getToolForCommonId(gradebookToolId) != null)) { gradebookExists = true; } if (sqlService.getVendor().equals("oracle")) { String gradeModelAddNewsql = "INSERT INTO jforum_grade(grade_id, context, grade_type, forum_id, topic_id, categories_id, points, add_to_gradebook, min_posts_required, min_posts) " + "VALUES (jforum_grade_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement p = connection.prepareStatement(gradeModelAddNewsql); p.setString(1, toContextId); p.setInt(2, gradeType); p.setInt(3, forumId); p.setInt(4, topicId); p.setInt(5, categoryId); p.setFloat(6, gradePoints); if (gradebookExists) { String gradebookUid = ToolManager.getInstance().getCurrentPlacement().getContext(); JForumGBService jForumGBService = null; jForumGBService = (JForumGBService) ComponentManager .get("org.etudes.api.app.jforum.JForumGBService"); if (!jForumGBService.isAssignmentDefined(gradebookUid, gradebookTitle)) { p.setInt(7, addToGradebook); } else { addToGradebook = 0; p.setInt(7, 0); } } else { p.setInt(7, 0); } if (minPostsRequired) { p.setInt(8, 1); p.setInt(9, minPosts); } else { p.setInt(8, 0); p.setInt(9, 0); } p.executeUpdate(); p.close(); String gradeLastGeneratedGradeId = "SELECT jforum_grade_seq.currval FROM DUAL"; p = connection.prepareStatement(gradeLastGeneratedGradeId); rs = p.executeQuery(); if (rs.next()) { gradeId = rs.getInt(1); } rs.close(); p.close(); } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) { String gradeModelAddNewsql = "INSERT INTO jforum_grade(context, grade_type, forum_id, topic_id, categories_id, points, add_to_gradebook, min_posts_required, min_posts) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; PreparedStatement p = connection.prepareStatement(gradeModelAddNewsql, Statement.RETURN_GENERATED_KEYS); p.setString(1, toContextId); p.setInt(2, gradeType); p.setInt(3, forumId); p.setInt(4, topicId); p.setInt(5, categoryId); p.setFloat(6, gradePoints); if (gradebookExists) { String gradebookUid = ToolManager.getInstance().getCurrentPlacement().getContext(); JForumGBService jForumGBService = null; jForumGBService = (JForumGBService) ComponentManager .get("org.etudes.api.app.jforum.JForumGBService"); if (!jForumGBService.isAssignmentDefined(gradebookUid, gradebookTitle)) { p.setInt(7, addToGradebook); } else { addToGradebook = 0; p.setInt(7, 0); } } else { p.setInt(7, 0); } if (minPostsRequired) { p.setInt(8, 1); p.setInt(9, minPosts); } else { p.setInt(8, 0); p.setInt(9, 0); } p.executeUpdate(); rs = p.getGeneratedKeys(); if (rs.next()) { gradeId = rs.getInt(1); } rs.close(); p.close(); } return gradeId; }
From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java
@Override public PluginInfo addJavascriptPlugin(String serviceName, String packageName, File file) { PluginInfo pluginInfo = null;/* w w w. j a v a2s. c o m*/ if (serviceName == null) { Log.e(TAG, "One cannot create a plugin where service name is null"); return null; } try { checkOpenness(); connection.setAutoCommit(false); //First things first, insert the feed's values to the feed table String sqlPluginInsert = "INSERT INTO " + IotHubDataHandler.TABLE_PLUGIN_INFO + "(" + IotHubDataHandler.KEY_PLUGIN_INFO_TYPE + "," + IotHubDataHandler.KEY_PLUGIN_INFO_SERVICE_NAME + "," + IotHubDataHandler.KEY_PLUGIN_INFO_PACKAGE_NAME + "," + IotHubDataHandler.KEY_PLUGIN_INFO_FILENAME + ") VALUES (?,?,?,?)"; PreparedStatement psPluginInsert = connection.prepareStatement(sqlPluginInsert, Statement.RETURN_GENERATED_KEYS); psPluginInsert.setString(1, IotHubDataHandler.JAVASCRIPT_PLUGIN); psPluginInsert.setString(2, serviceName); psPluginInsert.setString(3, packageName); psPluginInsert.setString(4, file == null ? null : file.getName()); psPluginInsert.executeUpdate(); ResultSet genKeysPlugin = psPluginInsert.getGeneratedKeys(); if (genKeysPlugin.next()) { long insertIdPlugin = genKeysPlugin.getLong(1); //At point we should have everything set so it is time to retrieve the plugin from the database //Log.d(TAG, "Now i will try to collect the plugin that was just added to the db"); pluginInfo = getPluginInfo(insertIdPlugin); if (pluginInfo == null) { Log.e(TAG, "The plugin should not be null"); } //Now I want to make some checks if (!pluginInfo.isJavascript()) { Log.e(TAG, "The plugin " + pluginInfo.getId() + " is not javascript"); pluginInfo = null; } } else { Log.e(TAG, "The insert of javascript plugin " + serviceName + " did not work"); } genKeysPlugin.close(); psPluginInsert.close(); } catch (SQLException | IotHubDatabaseException e) { e.printStackTrace(); pluginInfo = null; } try { if (pluginInfo == null) { connection.rollback(); } connection.commit(); connection.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); } return pluginInfo; }
From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java
/** * Helper method to insert the configs from the MConnector into the * repository. The job and connector configs within <code>mc</code> will get * updated with the id of the configs when this function returns. * @param mc The connector to use for updating configs * @param conn JDBC connection to use for inserting the configs *///from w w w .j a v a 2s. com private void insertConfigsForConnector(MConnector mc, Connection conn) { long connectorId = mc.getPersistenceId(); PreparedStatement baseConfigStmt = null; PreparedStatement baseInputStmt = null; try { baseConfigStmt = conn.prepareStatement(crudQueries.getStmtInsertIntoConfig(), Statement.RETURN_GENERATED_KEYS); baseInputStmt = conn.prepareStatement(crudQueries.getStmtInsertIntoInput(), Statement.RETURN_GENERATED_KEYS); // Register link type config registerConfigs(connectorId, null /* No direction for LINK type config */, mc.getLinkConfig().getConfigs(), MConfigType.LINK.name(), baseConfigStmt, baseInputStmt, conn); // Register both from/to job type config for connector if (mc.getSupportedDirections().isDirectionSupported(Direction.FROM)) { registerConfigs(connectorId, Direction.FROM, mc.getFromConfig().getConfigs(), MConfigType.JOB.name(), baseConfigStmt, baseInputStmt, conn); } if (mc.getSupportedDirections().isDirectionSupported(Direction.TO)) { registerConfigs(connectorId, Direction.TO, mc.getToConfig().getConfigs(), MConfigType.JOB.name(), baseConfigStmt, baseInputStmt, conn); } } catch (SQLException ex) { throw new SqoopException(CommonRepositoryError.COMMON_0011, mc.toString(), ex); } finally { closeStatements(baseConfigStmt, baseInputStmt); } }
From source file:edu.ku.brc.specify.config.FixDBAfterLogin.java
/** * @param disciplineId/*from w ww .j a v a 2s. c om*/ * @return * @throws SQLException */ public static Integer addSymbiotaExportSchema(Integer disciplineId) throws SQLException { Statement stmt = DBConnection.getInstance().getConnection().createStatement(); Integer schemaId = null; try { String sql = "insert into spexportschema(TimestampCreated,Version,Description,SchemaName,SchemaVersion,DisciplineID,CreatedByAgentID) " + "values(now(),0,'http://rs.tdwg.org/dwc/terms','SymbiotaDwc','1.0'," + disciplineId + "," + AppContextMgr.getInstance().getClassObject(SpecifyUser.class).getId() + ")"; stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); ResultSet key = stmt.getGeneratedKeys(); key.next(); schemaId = key.getInt(1); Integer spuId = AppContextMgr.getInstance().getClassObject(SpecifyUser.class).getId(); //XXX This is so stoopid. insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','created'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','description'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','identifier'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','language'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','license'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','modified'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','publisher'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','references'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','title'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','accessRights'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','associatedTaxa'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','basisOfRecord'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','catalogNumber'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','collectionCode'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:double','coordinateUncertaintyInMeters'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','country'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','county'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:dateTimeISO','dateIdentified'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:gDay','day'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:decimalLatitudeDataType','decimalLatitude'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:decimalLongitudeDataType','decimalLongitude'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','disposition'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','dynamicProperties'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:dayOfYearDataType','endDayOfYear'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','establishmentMeans'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:dateTimeISO','eventDate'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','family'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','fieldNotes'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','fieldNumber'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','footprintWKT'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','genus'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','geodeticDatum'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','georeferenceProtocol'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','georeferenceRemarks'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','georeferenceSources'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','georeferenceVerificationStatus'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','georeferencedBy'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','habitat'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:nonEmptyString','identificationID'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','identificationQualifier'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','identificationReferences'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','identificationRemarks'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','identifiedBy'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:positiveInteger','individualCount'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','informationWithheld'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','infraspecificEpithet'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','institutionCode'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','lifeStage'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','locality'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:nonEmptyString','locationID'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:double','maximumElevationInMeters'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:double','minimumElevationInMeters'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:gMonth','month'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','municipality'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','occurrenceRemarks'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','otherCatalogNumbers'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','ownerInstitutionCode'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','preparations'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','recordNumber'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','recordedBy'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','reproductiveCondition'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','rights'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','rightsHolder'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','samplingProtocol'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','scientificName'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','scientificNameAuthorship'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','sex'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','specificEpithet'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'dwc:dayOfYearDataType','startDayOfYear'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','stateProvince'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','taxonRank'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','taxonRemarks'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','typeStatus'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','verbatimCoordinates'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','verbatimElevation'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:string','verbatimEventDate'," + schemaId + "," + spuId + ")"); insertThis( "insert into spexportschemaitem(TimestampCreated,Version,DataType,FieldName,SpExportschemaId,CreatedByAgentID) values(now(),0,'xs:gYear','year'," + schemaId + "," + spuId + ")"); return schemaId; } catch (SQLException e) { if (schemaId != null) { stmt.executeUpdate("DELETE FROM spexportschemaitem WHERE SpExportSchemaID=" + schemaId); stmt.executeUpdate("DELETE FROM spexportschema WHERE SpExportSchemaID=" + schemaId); } throw e; } finally { stmt.close(); } }
From source file:edu.pitt.apollo.db.ApolloDbUtils.java
public int associateContentWithRunId(BigInteger runKey, int dataContentKey, int runDataDescriptionId) throws ApolloDatabaseException, ApolloDatabaseKeyNotFoundException { if (runDataDescriptionId >= 0) { String query = "INSERT IGNORE INTO run_data (run_id, description_id, content_id) values (?,?,?)"; PreparedStatement pstmt;//from ww w .j av a 2s . c om try (Connection conn = datasource.getConnection()) { pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); pstmt.setInt(1, runKey.intValue()); pstmt.setInt(2, runDataDescriptionId); pstmt.setInt(3, dataContentKey); int rowsAffected = pstmt.executeUpdate(); if (rowsAffected > 0) { ResultSet rs = pstmt.getGeneratedKeys(); rs.next(); return rs.getInt(1); } else { pstmt.close(); query = "SELECT id FROM run_data WHERE run_id = ? AND description_id = ? and content_id = ?"; try { pstmt = conn.prepareStatement(query); pstmt.setInt(1, runKey.intValue()); pstmt.setInt(2, runDataDescriptionId); pstmt.setInt(3, dataContentKey); ResultSet rs = pstmt.executeQuery(); if (rs.next()) { return rs.getInt(1); } else { throw new ApolloDatabaseException("Could not get id for apparently existing run_data."); } } finally { pstmt.close(); } } } catch (SQLException ex) { throw new ApolloDatabaseException( "SQLException associating content with run ID " + runKey + ": " + ex.getMessage()); } } else { throw new ApolloDatabaseKeyNotFoundException( "associateContentWithRunId() called with an invalid key: " + runKey); } }
From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java
private void insertConfigsforDriver(MDriver mDriver, Connection conn) { PreparedStatement baseConfigStmt = null; PreparedStatement baseInputStmt = null; try {/* w w w .j a v a2 s .c o m*/ baseConfigStmt = conn.prepareStatement(STMT_INSERT_INTO_CONFIG, Statement.RETURN_GENERATED_KEYS); baseInputStmt = conn.prepareStatement(STMT_INSERT_INTO_INPUT, Statement.RETURN_GENERATED_KEYS); // Register a driver config as a job type with no direction registerConfigs(mDriver.getPersistenceId(), null /* no direction*/, mDriver.getDriverConfig().getConfigs(), MConfigType.JOB.name(), baseConfigStmt, baseInputStmt, conn); } catch (SQLException ex) { logException(ex, mDriver); throw new SqoopException(DerbyRepoError.DERBYREPO_0014, ex); } finally { closeStatements(baseConfigStmt, baseInputStmt); } }
From source file:org.apache.sqoop.repository.common.CommonRepositoryHandler.java
private long insertAndGetDriverId(MDriver mDriver, Connection conn) { PreparedStatement baseDriverStmt = null; try {//from w w w .j a v a 2s. c o m baseDriverStmt = conn.prepareStatement(crudQueries.getStmtInsertIntoConfigurable(), Statement.RETURN_GENERATED_KEYS); baseDriverStmt.setString(1, mDriver.getUniqueName()); baseDriverStmt.setString(2, Driver.getClassName()); baseDriverStmt.setString(3, mDriver.getVersion()); baseDriverStmt.setString(4, mDriver.getType().name()); int baseDriverCount = baseDriverStmt.executeUpdate(); if (baseDriverCount != 1) { throw new SqoopException(CommonRepositoryError.COMMON_0009, Integer.toString(baseDriverCount)); } ResultSet rsetDriverId = baseDriverStmt.getGeneratedKeys(); if (!rsetDriverId.next()) { throw new SqoopException(CommonRepositoryError.COMMON_0010); } return rsetDriverId.getLong(1); } catch (SQLException ex) { throw new SqoopException(CommonRepositoryError.COMMON_0044, mDriver.toString(), ex); } finally { closeStatements(baseDriverStmt); } }
From source file:org.kawanfw.sql.jdbc.ConnectionHttp.java
/** * Creates a default <code>PreparedStatement</code> object that has the * capability to retrieve auto-generated keys. The given constant tells the * driver whether it should make auto-generated keys available for * retrieval. This parameter is ignored if the SQL statement is not an * <code>INSERT</code> statement, or an SQL statement able to return * auto-generated keys (the list of such statements is vendor-specific). * <P>/*from w ww . ja v a2 s.c o m*/ * <B>Note:</B> This method is optimized for handling parametric SQL * statements that benefit from precompilation. If the driver supports * precompilation, the method <code>prepareStatement</code> will send the * statement to the database for precompilation. Some drivers may not * support precompilation. In this case, the statement may not be sent to * the database until the <code>PreparedStatement</code> object is executed. * This has no direct effect on users; however, it does affect which methods * throw certain SQLExceptions. * <P> * Result sets created using the returned <code>PreparedStatement</code> * object will by default be type <code>TYPE_FORWARD_ONLY</code> and have a * concurrency level of <code>CONCUR_READ_ONLY</code>. The holdability of * the created result sets can be determined by calling * {@link #getHoldability}. * * @param sql * an SQL statement that may contain one or more '?' IN parameter * placeholders * @param autoGeneratedKeys * a flag indicating whether auto-generated keys should be * returned; one of <code>Statement.RETURN_GENERATED_KEYS</code> * or <code>Statement.NO_GENERATED_KEYS</code> * @return a new <code>PreparedStatement</code> object, containing the * pre-compiled SQL statement, that will have the capability of * returning auto-generated keys * @exception SQLException * if a database access error occurs, this method is called * on a closed connection or the given parameter is not a * <code>Statement</code> constant indicating whether * auto-generated keys should be returned * @exception SQLFeatureNotSupportedException * if the JDBC driver does not support this method with a * constant of Statement.RETURN_GENERATED_KEYS * @since 1.4 */ public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException { if (autoGeneratedKeys != Statement.RETURN_GENERATED_KEYS && autoGeneratedKeys != Statement.NO_GENERATED_KEYS) { throw new SQLException( "Invalid parameter autoGeneratedKeys value. Must be 1 or 2. Valus is: " + autoGeneratedKeys); } return new PreparedStatementHttp(this, sql, autoGeneratedKeys); }
From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java
@Override public PluginInfo addNativePlugin(String serviceName, String packageName, File file) { PluginInfo pluginInfo = null;//from ww w .j a v a 2s .com if (serviceName == null || packageName == null) { Log.e(TAG, "One cannot create a plugin where service name is null"); return null; } try { checkOpenness(); connection.setAutoCommit(false); //First things first, insert the feed's values to the feed table String sqlPluginInsert = "INSERT INTO " + IotHubDataHandler.TABLE_PLUGIN_INFO + "(" + IotHubDataHandler.KEY_PLUGIN_INFO_TYPE + "," + IotHubDataHandler.KEY_PLUGIN_INFO_SERVICE_NAME + "," + IotHubDataHandler.KEY_PLUGIN_INFO_PACKAGE_NAME + "," + IotHubDataHandler.KEY_PLUGIN_INFO_FILENAME + ") VALUES (?,?,?,?)"; PreparedStatement psPluginInsert = connection.prepareStatement(sqlPluginInsert, Statement.RETURN_GENERATED_KEYS); psPluginInsert.setString(1, IotHubDataHandler.NATIVE_PLUGIN); psPluginInsert.setString(2, serviceName); psPluginInsert.setString(3, packageName); psPluginInsert.setString(4, file == null ? null : file.getName()); psPluginInsert.executeUpdate(); ResultSet genKeysPlugin = psPluginInsert.getGeneratedKeys(); if (genKeysPlugin.next()) { long insertIdPlugin = genKeysPlugin.getLong(1); //At point we should have everything set so it is time to retrieve the plugin from the database //Log.d(TAG, "Now i will try to collect the plugin that was just added to the db"); pluginInfo = getPluginInfo(insertIdPlugin); if (pluginInfo == null) { Log.e(TAG, "The plugin should not be null"); } //Now I want to make some checks if (!pluginInfo.isNative()) { Log.e(TAG, "The plugin " + pluginInfo.getId() + " is not native"); pluginInfo = null; } } else { Log.e(TAG, "The insert of native plugin " + serviceName + " did not work"); } genKeysPlugin.close(); psPluginInsert.close(); } catch (SQLException | IotHubDatabaseException e) { e.printStackTrace(); pluginInfo = null; } try { if (pluginInfo == null) { connection.rollback(); } connection.commit(); connection.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); } return pluginInfo; }