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:com.prosnav.oms.dao.CustDao.java
public int insertAndGetKey(final String sql, final Object[] o, String id) { KeyHolder keyHolder = new GeneratedKeyHolder(); jt.update(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { // String sql_sms = "insert into // sms(title,content,date_s,form,sffs,by1,by2,by3) values // (?,?,'"+dates+"',?,?,?,?,?)"; PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); for (int i = 0; i < o.length; i++) { ps.setObject(i + 1, o[i]); }//ww w . j a va 2 s. c o m return ps; } }, keyHolder); Map<String, Object> generatedId = keyHolder.getKeyList().get(0); Integer com_id = (Integer) generatedId.get(id); // Long generatedId = keyHolder.getKey().longValue(); // int a =Integer.parseInt(o_id); return com_id; }
From source file:edu.pitt.apollo.db.ApolloDbUtils.java
private BigInteger addDataServiceRunForAllMessageTypes(Object message, int md5CollisionId, Authentication authentication, SoftwareIdentification dataServiceSoftwareId, int sourceSoftwareId) throws ApolloDatabaseException, Md5UtilsException { String userName = authentication.getRequesterId(); String password = authentication.getRequesterPassword(); String[] userIdTokens = parseUserId(userName); userName = userIdTokens[0];//from w ww. jav a2s.co m int softwareKey = getSoftwareIdentificationKey(dataServiceSoftwareId); int userKey = getUserKey(userName, password); try (Connection conn = datasource.getConnection()) { BigInteger simulationGroupId = getNewSimulationGroupId(); String query = "INSERT INTO run (md5_hash_of_run_message, software_id, requester_id, last_service_to_be_called, simulation_group_id, md5_collision_id) VALUES (?, ?, ?, ?, ?, ?)"; PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, md5Utils.getMd5(message)); pstmt.setInt(2, softwareKey); pstmt.setInt(3, userKey); pstmt.setInt(4, 1); pstmt.setInt(5, simulationGroupId.intValue()); pstmt.setInt(6, md5CollisionId); pstmt.execute(); ResultSet rs = pstmt.getGeneratedKeys(); BigInteger runId; if (rs.next()) { runId = new BigInteger(rs.getString(1)); } else { throw new ApolloDatabaseRecordNotInsertedException("Record not inserted!"); } // ALSO NEED TO ADD serialized run data service message (JSON) to // run_data_content table... // use insertDataContentForRun for this int dataContentKey = addTextDataContent(jsonUtils.getJSONString(message)); int runDataDescriptionId = getRunDataDescriptionId(ContentDataFormatEnum.TEXT, "data_retrieval_request_message.json", ContentDataTypeEnum.RUN_MESSAGE, sourceSoftwareId, getSoftwareIdentificationKey(dataServiceSoftwareId)); // int runDataId = the following line returns the runDataId, but // it's not used at this point. associateContentWithRunId(new BigInteger(String.valueOf(runId)), dataContentKey, runDataDescriptionId); List<BigInteger> runIdsForDataService = new ArrayList<>(); runIdsForDataService.add(runId); addRunIdsToSimulationGroup(simulationGroupId, runIdsForDataService); updateStatusOfRun(runId, MethodCallStatusEnum.LOADED_RUN_CONFIG_INTO_DATABASE, "Adding config information to the database for runId: " + runId.toString()); return runId; } catch (SQLException ex) { throw new ApolloDatabaseException("SQLException attempting to add simulation run: " + ex.getMessage()); } }
From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java
/** * {@inheritDoc}/*from ww w .ja v a 2 s. c o m*/ */ @Override public void createJob(MJob job, Connection conn) { PreparedStatement stmt = null; int result; try { stmt = conn.prepareStatement(STMT_INSERT_JOB, Statement.RETURN_GENERATED_KEYS); stmt.setString(1, job.getName()); stmt.setLong(2, job.getLinkId(Direction.FROM)); stmt.setLong(3, job.getLinkId(Direction.TO)); stmt.setBoolean(4, job.getEnabled()); stmt.setString(5, job.getCreationUser()); stmt.setTimestamp(6, new Timestamp(job.getCreationDate().getTime())); stmt.setString(7, job.getLastUpdateUser()); stmt.setTimestamp(8, new Timestamp(job.getLastUpdateDate().getTime())); result = stmt.executeUpdate(); if (result != 1) { throw new SqoopException(DerbyRepoError.DERBYREPO_0012, Integer.toString(result)); } ResultSet rsetJobId = stmt.getGeneratedKeys(); if (!rsetJobId.next()) { throw new SqoopException(DerbyRepoError.DERBYREPO_0013); } long jobId = rsetJobId.getLong(1); // from config for the job createInputValues(STMT_INSERT_JOB_INPUT, jobId, job.getJobConfig(Direction.FROM).getConfigs(), conn); // to config for the job createInputValues(STMT_INSERT_JOB_INPUT, jobId, job.getJobConfig(Direction.TO).getConfigs(), conn); // driver config per job createInputValues(STMT_INSERT_JOB_INPUT, jobId, job.getDriverConfig().getConfigs(), conn); job.setPersistenceId(jobId); } catch (SQLException ex) { logException(ex, job); throw new SqoopException(DerbyRepoError.DERBYREPO_0026, ex); } finally { closeStatements(stmt); } }
From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java
@Override public Feature addFeature(Enabler enabler, String name, String type) { Feature feature = null;/* w w w . jav a 2 s . c o m*/ if (enabler == null || name == null || type == null) { Log.e(TAG, "One cannot create a feature where the enable is null, name is null or with no type"); return null; } try { checkOpenness(); connection.setAutoCommit(false); String sqlFeatureInsert = "INSERT INTO " + IotHubDataHandler.TABLE_FEATURE + "(" + IotHubDataHandler.KEY_FEATURE_ENABLER_ID + "," + IotHubDataHandler.KEY_FEATURE_NAME + "," + IotHubDataHandler.KEY_FEATURE_TYPE + "," + IotHubDataHandler.KEY_FEATURE_IS_FEED + ") VALUES (?,?,?,?)"; PreparedStatement psFeatureInsert = connection.prepareStatement(sqlFeatureInsert, Statement.RETURN_GENERATED_KEYS); psFeatureInsert.setLong(1, enabler.getId()); psFeatureInsert.setString(2, name); psFeatureInsert.setString(3, type); psFeatureInsert.setBoolean(4, false); //An added feature is never a feed psFeatureInsert.executeUpdate(); ResultSet genKeysFeature = psFeatureInsert.getGeneratedKeys(); if (genKeysFeature.next()) { long insertIdFeature = genKeysFeature.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 feature that was just added to the db"); feature = getFeature(insertIdFeature); if (feature == null) { Log.e(TAG, "The feature should not be null"); } } else { Log.e(TAG, "The insert of feature " + name + " did not work"); } genKeysFeature.close(); psFeatureInsert.close(); } catch (SQLException | IotHubDatabaseException e) { e.printStackTrace(); feature = null; } try { if (feature == null) { connection.rollback(); } connection.commit(); connection.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); } return feature; }
From source file:edu.pitt.apollo.db.ApolloDbUtils.java
public BigInteger[] addSimulationRun(RunMessage runMessage, int md5CollisionId, SoftwareIdentification identificationOfSoftwareToRun, int sourceSoftwareIdKey, SoftwareIdentification destinationSoftwareForRunSimulationMessage, Authentication authentication) throws ApolloDatabaseException, Md5UtilsException { String userName = authentication.getRequesterId(); String password = authentication.getRequesterPassword(); runMessage.setAuthentication(new Authentication()); String[] userIdTokens = parseUserId(userName); userName = userIdTokens[0];// w w w . j a v a 2 s . com Integer softwareKey = null; if (identificationOfSoftwareToRun != null) { softwareKey = getSoftwareIdentificationKey(identificationOfSoftwareToRun); } int userKey = getUserKey(userName, password); BigInteger simulationGroupId = null; String additionalInsertField = ""; String additionalParamHolder = ""; BigInteger[] runIdSimulationGroupId = new BigInteger[2]; String md5 = md5Utils.getMd5(runMessage); try (Connection conn = datasource.getConnection()) { simulationGroupId = getNewSimulationGroupId(); runIdSimulationGroupId[1] = simulationGroupId; additionalInsertField = ", simulation_group_id"; additionalParamHolder = ",?"; String query = "INSERT IGNORE INTO run (md5_hash_of_run_message, software_id, requester_id, last_service_to_be_called, md5_collision_id " + additionalInsertField + ") VALUES (?, ?, ?, ?, ? " + additionalParamHolder + ")"; PreparedStatement pstmt = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, md5); if (softwareKey != null) { pstmt.setInt(2, softwareKey); } else { pstmt.setNull(2, Types.INTEGER); } pstmt.setInt(3, userKey); pstmt.setInt(4, 1); pstmt.setInt(5, md5CollisionId); pstmt.setLong(6, simulationGroupId.longValue()); ResultSet rs; int rowsAffected = pstmt.executeUpdate(); if (rowsAffected > 0) { rs = pstmt.getGeneratedKeys(); } else { query = "SELECT id FROM run WHERE md5_hash_of_run_message = ? and md5_collision_id = ?"; pstmt = conn.prepareStatement(query); pstmt.setString(1, md5); pstmt.setInt(2, md5CollisionId); rs = pstmt.executeQuery(); } BigInteger runId; if (rs.next()) { runId = new BigInteger(rs.getString(1)); } else { throw new ApolloDatabaseRecordNotInsertedException("Record not inserted!"); } List<BigInteger> runIds = new ArrayList<>(); runIds.add(runId); if (!(runMessage instanceof RunSimulationsMessage)) { addRunIdsToSimulationGroup(simulationGroupId, runIds); } // ALSO NEED TO ADD serialized runSimulationMessage(JSON) to // run_data_content table... // use insertDataContentForRun for this int dataContentKey = addTextDataContent(jsonUtils.getJSONString(runMessage)); int runDataDescriptionId = getRunDataDescriptionId(ContentDataFormatEnum.TEXT, "run_message.json", ContentDataTypeEnum.RUN_MESSAGE, sourceSoftwareIdKey, getSoftwareIdentificationKey(destinationSoftwareForRunSimulationMessage)); // int runDataId = the following line returns the runDataId, but // it's not used at this point. associateContentWithRunId(new BigInteger(String.valueOf(runId)), dataContentKey, runDataDescriptionId); runIdSimulationGroupId[0] = runId; if (runIdSimulationGroupId.length == 2) { runIdSimulationGroupId[1] = simulationGroupId; } updateStatusOfRun(runId, MethodCallStatusEnum.LOADED_RUN_CONFIG_INTO_DATABASE, "Adding config information to the database for runId: " + runId.toString()); return runIdSimulationGroupId; // } catch (ClassNotFoundException ex) { // throw new ApolloDatabaseException( // "ClassNotFoundException attempting to add simulation run: " // + ex.getMessage()); } catch (SQLException ex) { throw new ApolloDatabaseException("SQLException attempting to add simulation run: " + ex.getMessage()); } }
From source file:org.etudes.component.app.jforum.JforumDataServiceImpl.java
/** * process post attachments/*from w w w . ja v a2s.c o m*/ * @param connection connection * @param fromPostId from postid * @param toPostId to post id * @param jforumUserId jforum user id */ private void processAttachments(Connection connection, int fromPostId, int toPostId, int jforumUserId) { //get attachments from fromPostId and make copy for toPostId String getAttachementsSql = "SELECT attach_id, post_id, privmsgs_id, user_id " + "FROM jforum_attach WHERE post_id = ?"; try { PreparedStatement p = connection.prepareStatement(getAttachementsSql); p.setInt(1, fromPostId); ResultSet rs = p.executeQuery(); while (rs.next()) { int attachId = rs.getInt("attach_id"); //int postId = rs.getInt("post_id"); //create attachment for toPostId String addAttachmentSql = null; PreparedStatement createAttachmentStmnt = null; ResultSet rsAttachment = null; int createdAttachId = -1; if (sqlService.getVendor().equals("oracle")) { addAttachmentSql = "INSERT INTO jforum_attach (attach_id, post_id, privmsgs_id, user_id) " + "VALUES (jforum_attach_seq.nextval, ?, ?, ?)"; createAttachmentStmnt = connection.prepareStatement(addAttachmentSql); createAttachmentStmnt.setInt(1, toPostId); createAttachmentStmnt.setInt(2, 0); createAttachmentStmnt.setInt(3, jforumUserId); createAttachmentStmnt.executeUpdate(); createAttachmentStmnt.close(); String forumLastGeneratedTopicId = "SELECT jforum_attach_seq.currval FROM DUAL"; createAttachmentStmnt = connection.prepareStatement(addAttachmentSql); rsAttachment = createAttachmentStmnt.executeQuery(); if (rsAttachment.next()) { createdAttachId = rsAttachment.getInt(1); } rsAttachment.close(); createAttachmentStmnt.close(); } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) { addAttachmentSql = "INSERT INTO jforum_attach (post_id, privmsgs_id, user_id) " + "VALUES (?, ?, ?)"; createAttachmentStmnt = connection.prepareStatement(addAttachmentSql, Statement.RETURN_GENERATED_KEYS); createAttachmentStmnt.setInt(1, toPostId); createAttachmentStmnt.setInt(2, 0); createAttachmentStmnt.setInt(3, jforumUserId); createAttachmentStmnt.executeUpdate(); rsAttachment = createAttachmentStmnt.getGeneratedKeys(); if (rsAttachment.next()) { createdAttachId = rsAttachment.getInt(1); } rsAttachment.close(); createAttachmentStmnt.close(); } String attachmentDetailSql = "SELECT attach_desc_id, attach_id, physical_filename, " + "real_filename, description, mimetype, filesize, thumb, extension_id " + " FROM jforum_attach_desc WHERE attach_id = ?"; PreparedStatement p1 = connection.prepareStatement(attachmentDetailSql); p1.setInt(1, attachId); ResultSet rs1 = p1.executeQuery(); if (rs1.next()) { int attachDescId = rs1.getInt("attach_desc_id"); //int attachId = rs1.getInt("attach_id"); String physicalFilename = rs1.getString("physical_filename"); String realFilename = rs1.getString("real_filename"); String description = rs1.getString("description"); String mimetype = rs1.getString("mimetype"); int filesize = rs1.getInt("filesize"); int thumb = rs1.getInt("thumb"); int extensionId = rs1.getInt("extension_id"); try { String attachmentsStoreDir = ServerConfigurationService.getString(ATTACHMENTS_STORE_DIR); if (attachmentsStoreDir == null || attachmentsStoreDir.trim().length() == 0) { //to get the file path is needed String tomcatPath = getCatalina(); //if (logger.isInfoEnabled()) logger.info("Tomcat path is : "+ tomcatPath); //load jforums SystemGlobal.properties and get attachments store directory String propFile = tomcatPath + "/webapps/etudes-jforum-tool/WEB-INF/config/SystemGlobals.properties"; Properties props = new Properties(); props.load(new FileInputStream(propFile)); /*attachment file path is the value of System global variable attachments.store.dir + physical_filename*/ attachmentsStoreDir = (String) props.get(ATTACHMENTS_STORE_DIR); //String attachmentsUploadDir = (String)props.get("attachments.upload.dir"); File attSrorePath = new File(attachmentsStoreDir); if (!attSrorePath.exists()) { //assuming default path attachmentsStoreDir = tomcatPath + "/webapps/etudes-jforum-tool/upload/"; } } String addAttachmentInfo = null; //create attachment file Calendar c = new GregorianCalendar(); c.setTimeInMillis(System.currentTimeMillis()); c.get(Calendar.YEAR); int year = Calendar.getInstance().get(Calendar.YEAR); int month = Calendar.getInstance().get(Calendar.MONTH) + 1; int day = Calendar.getInstance().get(Calendar.DAY_OF_MONTH); String dir = "" + year + "/" + month + "/" + day + "/"; new File(attachmentsStoreDir + "/" + dir).mkdirs(); String fileext = realFilename.trim().substring(realFilename.trim().lastIndexOf('.') + 1); String physicalFilenameActpath = dir + IdManager.createUuid() + "_" + jforumUserId + "." + fileext; try { saveAttachmentFile(attachmentsStoreDir + File.separator + physicalFilenameActpath, new File(attachmentsStoreDir + File.separator + physicalFilename)); } catch (Exception e) { if (logger.isErrorEnabled()) logger.error("processAttachments(): Error while saving attachemnt file"); e.printStackTrace(); } PreparedStatement createAttachmentInfoStmnt = null; ResultSet rsAttachmentInfo = null; int createdAttachInfoId = -1; if (sqlService.getVendor().equals("oracle")) { addAttachmentInfo = "INSERT INTO jforum_attach_desc (attach_desc_id, attach_id, physical_filename, " + "real_filename, description, mimetype, filesize, upload_time, thumb, extension_id ) " + "VALUES (jforum_attach_desc_seq.nextval, ?, ?, ?, ?, ?, ?, SYSDATE, ?, ?)"; createAttachmentInfoStmnt = connection.prepareStatement(addAttachmentSql); createAttachmentInfoStmnt.setInt(1, createdAttachId); createAttachmentInfoStmnt.setString(2, physicalFilenameActpath); createAttachmentInfoStmnt.setString(3, realFilename); createAttachmentInfoStmnt.setString(4, description); createAttachmentInfoStmnt.setString(5, mimetype); createAttachmentInfoStmnt.setInt(6, filesize); createAttachmentInfoStmnt.setInt(7, thumb); createAttachmentInfoStmnt.setInt(8, extensionId); createAttachmentInfoStmnt.close(); String lastGeneratedAttachInfoId = "SELECT jforum_attach_desc_seq.currval FROM DUAL"; createAttachmentInfoStmnt = connection.prepareStatement(lastGeneratedAttachInfoId); rsAttachmentInfo = createAttachmentInfoStmnt.executeQuery(); if (rsAttachmentInfo.next()) { createdAttachInfoId = rsAttachmentInfo.getInt(1); } rsAttachmentInfo.close(); createAttachmentInfoStmnt.close(); } else if (sqlService.getVendor().equalsIgnoreCase("mysql")) { addAttachmentInfo = "INSERT INTO jforum_attach_desc (attach_id, physical_filename, " + "real_filename, description, mimetype, filesize, upload_time, thumb, extension_id ) " + "VALUES (?, ?, ?, ?, ?, ?, NOW(), ?, ?)"; createAttachmentInfoStmnt = connection.prepareStatement(addAttachmentInfo, Statement.RETURN_GENERATED_KEYS); createAttachmentInfoStmnt.setInt(1, createdAttachId); createAttachmentInfoStmnt.setString(2, physicalFilenameActpath); createAttachmentInfoStmnt.setString(3, realFilename); createAttachmentInfoStmnt.setString(4, description); createAttachmentInfoStmnt.setString(5, mimetype); createAttachmentInfoStmnt.setInt(6, filesize); createAttachmentInfoStmnt.setInt(7, thumb); createAttachmentInfoStmnt.setInt(8, extensionId); createAttachmentInfoStmnt.executeUpdate(); rsAttachmentInfo = createAttachmentInfoStmnt.getGeneratedKeys(); if (rsAttachmentInfo.next()) { createdAttachInfoId = rsAttachmentInfo.getInt(1); } rsAttachmentInfo.close(); createAttachmentInfoStmnt.close(); } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } rs1.close(); p1.close(); } rs.close(); p.close(); } catch (SQLException e) { if (logger.isErrorEnabled()) logger.error("processAttachments():Error while processing attachments : " + e.toString()); e.printStackTrace(); } }
From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java
@Override public Service addService(ServiceInfo serviceInfo, String name, String metadata, String config, boolean bootAtStartup) { Service service = null;/*www . j a v a 2 s . c om*/ if (serviceInfo == null || name == null) { Log.e(TAG, "One cannot create a service where the serviceInfo is null, or name is null"); return null; } try { checkOpenness(); connection.setAutoCommit(false); String sqlInsert = "INSERT INTO " + IotHubDataHandler.TABLE_SERVICE + "(" + IotHubDataHandler.KEY_SERVICE_NAME + "," + IotHubDataHandler.KEY_SERVICE_METADATA + "," + IotHubDataHandler.KEY_SERVICE_SERVICE_INFO + "," + IotHubDataHandler.KEY_SERVICE_CONFIG + "," + IotHubDataHandler.KEY_SERVICE_BOOT_AT_STARTUP + ") VALUES (?,?,?,?,?)"; PreparedStatement psInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); psInsert.setString(1, name); psInsert.setString(2, metadata); psInsert.setLong(3, serviceInfo.getId()); psInsert.setString(4, config); psInsert.setBoolean(5, bootAtStartup); psInsert.executeUpdate(); ResultSet genKeys = psInsert.getGeneratedKeys(); if (genKeys.next()) { long insertId = genKeys.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 service " + insertId + " that was just added to the db"); service = getService(insertId); if (service == null) { Log.e(TAG, "The service " + name + " should not be null"); } } else { Log.e(TAG, "The insert of service " + name + " did not work"); } genKeys.close(); psInsert.close(); } catch (SQLException | IotHubDatabaseException e) { e.printStackTrace(); service = null; } try { if (service == null) { connection.rollback(); } connection.commit(); connection.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); } return service; }
From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java
/** * {@inheritDoc}/*from www . j av a 2 s. co m*/ */ @Override public void createSubmission(MSubmission submission, Connection conn) { PreparedStatement stmt = null; int result; try { stmt = conn.prepareStatement(STMT_INSERT_SUBMISSION, Statement.RETURN_GENERATED_KEYS); stmt.setLong(1, submission.getJobId()); stmt.setString(2, submission.getStatus().name()); stmt.setString(3, submission.getCreationUser()); stmt.setTimestamp(4, new Timestamp(submission.getCreationDate().getTime())); stmt.setString(5, submission.getLastUpdateUser()); stmt.setTimestamp(6, new Timestamp(submission.getLastUpdateDate().getTime())); stmt.setString(7, submission.getExternalId()); stmt.setString(8, submission.getExternalLink()); stmt.setString(9, submission.getExceptionInfo()); stmt.setString(10, submission.getExceptionStackTrace()); result = stmt.executeUpdate(); if (result != 1) { throw new SqoopException(DerbyRepoError.DERBYREPO_0012, Integer.toString(result)); } ResultSet rsetSubmissionId = stmt.getGeneratedKeys(); if (!rsetSubmissionId.next()) { throw new SqoopException(DerbyRepoError.DERBYREPO_0013); } long submissionId = rsetSubmissionId.getLong(1); if (submission.getCounters() != null) { createSubmissionCounters(submissionId, submission.getCounters(), conn); } // Save created persistence id submission.setPersistenceId(submissionId); } catch (SQLException ex) { logException(ex, submission); throw new SqoopException(DerbyRepoError.DERBYREPO_0034, ex); } finally { closeStatements(stmt); } }
From source file:uk.ac.ebi.sail.server.data.DataManager.java
public ParameterShadow updateParameter(ParameterShadow p, boolean holdData) throws ParameterManagementException { int id = p.getId(); Parameter origP = params.get(id); if (origP == null) throw new ParameterManagementException("Parameter doesn't exist ID=" + id, ParameterManagementException.INV_PARAMETER_ID); SetComparator<Variable> variablesCmp = SetComparator.compare(origP.getVariables(), p.getVariables()); SetComparator<Qualifier> qualifiersCmp = SetComparator.compare(origP.getQualifiers(), p.getQualifiers()); /*//from ww w .ja v a 2 s .com Collection<Variable> newVars=null; if( p.getVariables() != null ) { if( origP.getVariables() == null ) newVars = p.getVariables(); else { newVars = new ArrayList<Variable>(5); for( Variable nv : p.getVariables() ) { boolean found = false; for( Variable ev : origP.getVariables() ) { if( nv.getId() == ev.getId() ) { found=true; break; } } if( ! found ) { newVars.add(nv); } } } } Collection<Variable> delVars=null; if( origP.getVariables() != null ) { if( p.getVariables() == null ) delVars = origP.getVariables(); else { delVars = new ArrayList<Variable>(5); for( Variable ov : origP.getVariables() ) { boolean found = false; for( Variable nv : p.getVariables() ) { if( nv.getId() == ov.getId() ) { found=true; break; } } if( ! found ) { delVars.add(ov); } } } } */ /* Collection<Qualifier> newQual=null; if( p.getQualifiers() != null ) { if( origP.getQualifiers() == null ) newQual = p.getQualifiers(); else { newQual = new ArrayList<Qualifier>(5); for( Qualifier nv : p.getQualifiers() ) { boolean found = false; for( Qualifier ev : origP.getQualifiers() ) { if( nv.getId() == ev.getId() ) { found=true; break; } } if( ! found ) { newQual.add(nv); } } } } Collection<Qualifier> delQuals=null; if( origP.getQualifiers() != null ) { if( p.getQualifiers() == null ) delQuals = origP.getQualifiers(); else { delQuals = new ArrayList<Qualifier>(5); for( Qualifier ov : origP.getQualifiers() ) { boolean found = false; for( Qualifier nv : p.getQualifiers() ) { if( nv.getId() == ov.getId() ) { found=true; break; } } if( ! found ) { delQuals.add(ov); } } } } */ Collection<Parameter> newInh = null; if (p.getInheritedParameters() != null) { if (origP.getInheritedParameters() == null) { newInh = new ArrayList<Parameter>(p.getInheritedParameters().length); for (int pid : p.getInheritedParameters()) { Parameter ip = params.get(pid); if (ip == null) throw new ParameterManagementException("Invalid inherited parameter ID=" + pid, ParameterManagementException.INV_INH_PARAMETER_ID); newInh.add(ip); } } else { newInh = new ArrayList<Parameter>(5); for (int nvid : p.getInheritedParameters()) { boolean found = false; for (Parameter ev : origP.getInheritedParameters()) { if (nvid == ev.getId()) { found = true; break; } } if (!found) { Parameter ip = params.get(nvid); if (ip == null) throw new ParameterManagementException("Invalid inherited parameter ID=" + nvid, ParameterManagementException.INV_INH_PARAMETER_ID); newInh.add(ip); } } } } Collection<Parameter> delInh = null; if (origP.getInheritedParameters() != null) { if (p.getInheritedParameters() == null) delInh = origP.getInheritedParameters(); else { delInh = new ArrayList<Parameter>(5); for (Parameter ov : origP.getInheritedParameters()) { boolean found = false; for (int nvid : p.getInheritedParameters()) { if (nvid == ov.getId()) { found = true; break; } } if (!found) { delInh.add(ov); } } } } Collection<Tag> newTag = null; if (p.getTags() != null) { if (origP.getClassificationTags() == null) { newTag = new ArrayList<Tag>(p.getTags().length); for (int tid : p.getTags()) { Tag t = tags.get(tid); if (t == null) throw new ParameterManagementException("Invalid tag ID=" + tid, ParameterManagementException.INV_TAG_ID); newTag.add(t); } } else { newTag = new ArrayList<Tag>(5); for (int tid : p.getTags()) { boolean found = false; for (Tag ev : origP.getClassificationTags()) { if (tid == ev.getId()) { found = true; break; } } if (!found) { Tag t = tags.get(tid); if (t == null) throw new ParameterManagementException("Invalid tag ID=" + tid, ParameterManagementException.INV_TAG_ID); newTag.add(t); } } } } Collection<Tag> delTag = null; if (origP.getClassificationTags() != null) { if (p.getTags() == null) delTag = origP.getClassificationTags(); else { for (Tag ov : origP.getClassificationTags()) { boolean found = false; for (int tid : p.getTags()) { if (tid == ov.getId()) { found = true; break; } } if (!found) { if (delTag == null) delTag = new ArrayList<Tag>(5); delTag.add(ov); } } } } Collection<Relation> fullRels = new ArrayList<Relation>(); Collection<Relation> newRel = null; if (p.getRelations() != null) { if (origP.getRelations() == null) { newRel = new ArrayList<Relation>(p.getRelations().length); for (int[] irel : p.getRelations()) { Tag t = tags.get(irel[2]); if (t == null) throw new ParameterManagementException("Invalid relation tag ID=" + irel[2], ParameterManagementException.INV_TAG_ID); Parameter tp = params.get(irel[1]); if (tp == null) throw new ParameterManagementException("Invalid relation target parameter ID=" + irel[1], ParameterManagementException.INV_RERLAGET_PARAMETER_ID); Relation nr = new Relation(); nr.setHostParameter(origP); nr.setTargetParameter(tp); nr.setTag(t); newRel.add(nr); fullRels.add(nr); } } else { newRel = new ArrayList<Relation>(5); for (int[] rl : p.getRelations()) { boolean found = false; for (Relation er : origP.getRelations()) { if (rl[0] == er.getId()) { found = true; break; } } if (!found) { Tag t = tags.get(rl[2]); if (t == null) throw new ParameterManagementException("Invalid relation tag ID=" + rl[2], ParameterManagementException.INV_TAG_ID); Parameter tp = params.get(rl[1]); if (tp == null) throw new ParameterManagementException("Invalid relation target parameter ID=" + rl[1], ParameterManagementException.INV_RERLAGET_PARAMETER_ID); Relation nr = new Relation(); nr.setHostParameter(origP); nr.setTargetParameter(tp); nr.setTag(t); newRel.add(nr); fullRels.add(nr); } } } } Collection<Relation> delRel = null; if (origP.getRelations() != null) { if (p.getRelations() == null) delRel = origP.getRelations(); else { delRel = new ArrayList<Relation>(5); for (Relation ov : origP.getRelations()) { boolean found = false; for (int[] relid : p.getRelations()) { if (relid[0] == ov.getId()) { found = true; break; } } if (!found) { delRel.add(ov); } else fullRels.add(ov); } } } Connection conn = null; ResultSet rst = null; try { conn = dSrc.getConnection(); Statement stmt = conn.createStatement(); StringBuilder sb = new StringBuilder(200); if (variablesCmp.getItemsToDelete() != null) { for (Variable v : variablesCmp.getItemsToDelete()) sb.append(v.getId()).append(','); } if (qualifiersCmp.getItemsToDelete() != null) { for (Qualifier q : qualifiersCmp.getItemsToDelete()) sb.append(q.getId()).append(','); } if (sb.length() > 0) { sb.setCharAt(sb.length() - 1, ')'); String ids = sb.toString(); if (holdData) { rst = stmt.executeQuery( "SELECT COUNT(*) FROM " + TBL_RECORD_CONTENT + " WHERE " + FLD_PART_ID + " IN (" + ids); rst.next(); int nRec = rst.getInt(1); if (nRec > 0) throw new ParameterManagementException( "There are " + nRec + " data records annotated by parts ID=(" + ids, ParameterManagementException.DATA_ANNOTATED_BY_PART); rst.close(); } stmt.executeUpdate("DELETE FROM " + TBL_RECORD_CONTENT + " WHERE " + FLD_PART_ID + " IN (" + ids); stmt.executeUpdate("DELETE FROM " + TBL_PART + " WHERE " + FLD_PARAMETER_ID + "=" + p.getId() + " AND " + FLD_ID + " IN (" + ids); stmt.executeUpdate("DELETE FROM " + TBL_VARIANT + " WHERE " + FLD_PART_ID + " IN (" + ids); } PreparedStatement pstmt = conn.prepareStatement("UPDATE " + TBL_PARAMETER + " SET " + FLD_NAME + "=?," + FLD_DESCRIPTION + "=?," + FLD_CODE + "=? WHERE ID=" + p.getId()); pstmt.setString(1, p.getName()); pstmt.setString(2, p.getDesc()); pstmt.setString(3, p.getCode()); pstmt.executeUpdate(); pstmt.close(); pstmt = null; Helper hlp = new Helper(conn); hlp.insertParts(variablesCmp.getNewItems(), p.getId()); hlp.insertParts(qualifiersCmp.getNewItems(), p.getId()); hlp.updateParts(p.getVariables(), origP.getVariables()); hlp.updateParts(p.getQualifiers(), origP.getQualifiers()); hlp.destroy(); if (newInh != null) { PreparedStatement insertInhStmt = conn.prepareStatement(insertInheritedSQL); for (Parameter ip : newInh) { insertInhStmt.setInt(1, p.getId()); insertInhStmt.setInt(2, ip.getId()); insertInhStmt.executeUpdate(); ((SSParameterInfo) ip.getAuxInfo()).addChildren(origP); } insertInhStmt.close(); } if (delInh != null) { PreparedStatement deleteInhStmt = conn.prepareStatement(deleteInheritedSQL); for (Parameter ip : newInh) { deleteInhStmt.setInt(1, p.getId()); deleteInhStmt.setInt(2, ip.getId()); deleteInhStmt.executeUpdate(); ((SSParameterInfo) ip.getAuxInfo()).removeChildren(origP); } deleteInhStmt.close(); } if (newTag != null) { PreparedStatement insertParamTagStmt = conn.prepareStatement(insertParameterTagSQL); for (Tag t : newTag) { insertParamTagStmt.setInt(1, p.getId()); insertParamTagStmt.setInt(2, t.getId()); insertParamTagStmt.executeUpdate(); } insertParamTagStmt.close(); } if (delTag != null) { PreparedStatement deleteParamTagStmt = conn.prepareStatement(deleteParameterTagSQL); for (Tag t : delTag) { deleteParamTagStmt.setInt(1, p.getId()); deleteParamTagStmt.setInt(2, t.getId()); deleteParamTagStmt.executeUpdate(); } deleteParamTagStmt.close(); } if (newRel != null) { PreparedStatement insertRelationStmt = conn.prepareStatement(insertRelationSQL, Statement.RETURN_GENERATED_KEYS); for (Relation r : newRel) { insertRelationStmt.setInt(1, p.getId()); insertRelationStmt.setInt(2, r.getTargetParameter().getId()); insertRelationStmt.setInt(3, r.getTag().getId()); insertRelationStmt.executeUpdate(); rst = insertRelationStmt.getGeneratedKeys(); if (rst.next()) r.setId(rst.getInt(1)); else throw new ParameterManagementException("Can't get generated IDs", ParameterManagementException.SYSTEM_ERROR); rst.close(); } insertRelationStmt.close(); } if (delRel != null) { PreparedStatement deleteRelationStmt = conn.prepareStatement(deleteRelationSQL); for (Relation r : delRel) { deleteRelationStmt.setInt(1, r.getId()); deleteRelationStmt.executeUpdate(); } deleteRelationStmt.close(); } pstmt = conn.prepareStatement(deleteParameterAnnotationsSQL); pstmt.setInt(1, origP.getId()); pstmt.executeUpdate(); pstmt.close(); if (p.getAnnotations() != null) { pstmt = conn.prepareStatement(insertParameterAnnotationsSQL); for (AnnotationShadow ans : p.getAnnotations()) { pstmt.setInt(1, origP.getId()); pstmt.setInt(2, ans.getTag()); pstmt.setString(3, ans.getText()); pstmt.executeUpdate(); } pstmt.close(); } origP.setName(p.getName()); origP.setCode(p.getCode()); origP.setDescription(p.getDesc()); origP.setVariables(p.getVariables()); origP.setQualifiers(p.getQualifiers()); origP.clearInherited(); if (p.getInheritedParameters() != null) { for (int pid : p.getInheritedParameters()) origP.addInheritedParameter(params.get(pid)); } origP.clearTags(); if (p.getTags() != null) { for (int tid : p.getTags()) origP.addClassificationTag(tags.get(tid)); } origP.setRelations(fullRels); ParameterShadow ps = ((SSParameterInfo) origP.getAuxInfo()).getShadow(); ps.update(origP); return ps; } catch (SQLException e) { logger.error("SQL error", e); } finally { if (rst != null) { try { rst.close(); } catch (SQLException e) { } } if (conn != null) { try { conn.close(); } catch (SQLException e) { Log.error("Connection closing error", e); } } } return null; }
From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java
@Override public ServiceInfo addServiceInfo(String name, File file) { ServiceInfo service = null;/*www. j av a 2s . c om*/ if (file == null || name == null) { Log.e(TAG, "One cannot create a serviceInfo where the file is null, or name is null"); return null; } try { checkOpenness(); connection.setAutoCommit(false); String sqlInsert = "INSERT INTO " + IotHubDataHandler.TABLE_SERVICE_INFO + "(" + IotHubDataHandler.KEY_SERVICE_INFO_SERVICE_NAME + "," + IotHubDataHandler.KEY_SERVICE_INFO_FILENAME + ") VALUES (?,?)"; PreparedStatement psInsert = connection.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS); psInsert.setString(1, name); psInsert.setString(2, file.getName()); psInsert.executeUpdate(); ResultSet genKeys = psInsert.getGeneratedKeys(); if (genKeys.next()) { long insertId = genKeys.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 service info " + name + " that was just added to the db"); service = getServiceInfo(insertId); if (service == null) { Log.e(TAG, "The service info " + name + " should not be null"); } } else { Log.e(TAG, "The insert of service info " + name + " did not work"); } genKeys.close(); psInsert.close(); } catch (SQLException | IotHubDatabaseException e) { e.printStackTrace(); service = null; } try { if (service == null) { connection.rollback(); } connection.commit(); connection.setAutoCommit(true); } catch (SQLException e) { e.printStackTrace(); } return service; }