List of usage examples for java.sql PreparedStatement getGeneratedKeys
ResultSet getGeneratedKeys() throws SQLException;
Statement object. From source file:uk.co.swlines.cifreader.cif.CIFDatabase.java
public void insertScheduleBulk(ArrayList<CIFSchedule> r) throws LogicException { if (r.size() == 0) return;/*from w w w . j a v a 2 s .c om*/ try { final StringBuilder schedulesStatement = new StringBuilder( "INSERT INTO schedules_t (train_uid, date_from, date_to," + "runs_mo, runs_tu, runs_we, runs_th, runs_fr, runs_sa, runs_su, bank_hol, status, category, train_identity, headcode, " + "service_code, portion_id, power_type, timing_load, speed, train_class, sleepers, reservations, catering_code, " + "service_branding, stp_indicator, uic_code, atoc_code, ats_code, rsid, bus, train, ship, passenger, " + "oc_b, oc_c, oc_d, oc_e, oc_g, oc_m, oc_p, oc_q, oc_r, oc_s, oc_y, oc_z) VALUES "); final StringBuilder locationsStatement = new StringBuilder( "INSERT INTO locations_t (id, location_order, location_type, " + "tiploc_code, tiploc_instance, arrival, arrival_aftermidnight, public_arrival, public_arrival_aftermidnight, " + "pass, pass_aftermidnight, departure, departure_aftermidnight, public_departure, public_departure_aftermidnight, " + "platform, line, path, engineering_allowance, pathing_allowance, performance_allowance, public_call, " + "actual_call, order_time, public_time, act_a, act_ae, act_bl, act_c, act_d, act_minusd, act_e, act_g, act_h, act_hh, act_k, act_kc, " + "act_ke, act_kf, act_ks, act_l, act_n, act_op, act_or, act_pr, act_r, act_rm, act_rr, act_s, act_t, act_minust, act_tb, " + "act_tf, act_ts, act_tw, act_u, act_minusu, act_w, act_x) VALUES "); final StringBuilder locationsChange = new StringBuilder( "INSERT INTO locations_change_t (schedule_id, location_id, category, " + "train_identity, headcode, service_code, portion_id, power_type, timing_load, speed, train_class, sleepers, " + "reservations, catering_code, service_branding, uic_code, rsid, oc_b, oc_c, oc_d, oc_e, oc_g, oc_m, oc_p, " + "oc_q, oc_r, oc_s, oc_y, oc_z) VALUES " + createInsertPlaceholders(29)); PreparedStatement stmtSchedules = getConnection().prepareStatement( schedulesStatement.append(createInsertPlaceholders(46)).toString(), Statement.RETURN_GENERATED_KEYS); PreparedStatement stmtLocChange = getConnection().prepareStatement(locationsChange.toString()); int locationTotal = 0; for (CIFSchedule schedule : r) { int parameterIndex = 1; stmtSchedules.clearParameters(); stmtSchedules.setString(parameterIndex++, schedule.getUid()); stmtSchedules.setString(parameterIndex++, schedule.getDate_from()); stmtSchedules.setString(parameterIndex++, schedule.getDate_to()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_mo()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_tu()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_we()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_th()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_fr()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_sa()); stmtSchedules.setBoolean(parameterIndex++, schedule.isRuns_su()); if (schedule.getBank_holiday() != ' ') stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getBank_holiday())); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.CHAR); stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getStatus()).trim()); stmtSchedules.setString(parameterIndex++, schedule.getCategory()); stmtSchedules.setString(parameterIndex++, schedule.getTrain_identity()); stmtSchedules.setString(parameterIndex++, schedule.getHeadcode()); if (schedule.getService_code() != null) stmtSchedules.setInt(parameterIndex++, schedule.getService_code()); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.INTEGER); if (schedule.getPortion_id() != ' ') stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getPortion_id())); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.CHAR); stmtSchedules.setString(parameterIndex++, schedule.getPower_type()); stmtSchedules.setString(parameterIndex++, schedule.getTiming_load()); stmtSchedules.setString(parameterIndex++, schedule.getSpeed()); if (schedule.getTrain_class() != ' ') stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getTrain_class())); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.CHAR); if (schedule.getSleepers() != ' ') stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getSleepers())); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.CHAR); if (schedule.getReservations() != ' ') stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getReservations())); else stmtSchedules.setNull(parameterIndex++, java.sql.Types.CHAR); stmtSchedules.setString(parameterIndex++, schedule.getCatering_code()); stmtSchedules.setString(parameterIndex++, schedule.getService_branding()); stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getStp_indicator()).trim()); stmtSchedules.setString(parameterIndex++, schedule.getUic_code()); stmtSchedules.setString(parameterIndex++, schedule.getAtoc_code()); stmtSchedules.setString(parameterIndex++, String.valueOf(schedule.getAts()).trim()); stmtSchedules.setString(parameterIndex++, schedule.getRsid()); stmtSchedules.setBoolean(parameterIndex++, schedule.isBus()); stmtSchedules.setBoolean(parameterIndex++, schedule.isTrain()); stmtSchedules.setBoolean(parameterIndex++, schedule.isShip()); stmtSchedules.setBoolean(parameterIndex++, schedule.isPassenger()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_b()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_c()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_d()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_e()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_g()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_m()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_p()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_q()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_r()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_s()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_y()); stmtSchedules.setBoolean(parameterIndex++, schedule.isOc_z()); stmtSchedules.execute(); locationTotal += schedule.getLocations().size(); ResultSet rs = stmtSchedules.getGeneratedKeys(); while (rs.next()) { schedule.setDatabaseId(rs.getInt(1)); } rs.close(); } stmtSchedules.close(); int parameterIndex = 1; PreparedStatement stmtLocations = getConnection().prepareStatement( locationsStatement.append(createInsertPlaceholdersList(locationTotal, 59)).toString()); for (CIFSchedule schedule : r) { int locationOrder = -1; Integer originDeparture = null, originPublicDeparture = null; CIFLocation origin = schedule.getLocations().get(0); if (origin instanceof CIFLocationOrigin) { originDeparture = ((CIFLocationOrigin) origin).getDeparture(); originPublicDeparture = ((CIFLocationOrigin) origin).getPublic_departure(); } if (originDeparture == null) throw new LogicException(schedule); for (CIFLocation location : schedule.getLocations()) { stmtLocations.setInt(parameterIndex++, schedule.getDatabaseId()); stmtLocations.setInt(parameterIndex++, ++locationOrder); stmtLocations.setString(parameterIndex++, location.getLocationType()); stmtLocations.setString(parameterIndex++, location.getTiploc()); stmtLocations.setString(parameterIndex++, String.valueOf(location.getTiploc_instance()).trim()); Integer orderTime = null; Integer publicTime = null; if (location instanceof CIFLocationArrival && ((CIFLocationArrival) location).getArrival() != null) { CIFLocationArrival locationArrival = (CIFLocationArrival) location; stmtLocations.setInt(parameterIndex++, locationArrival.getArrival()); stmtLocations.setBoolean(parameterIndex++, locationArrival.getArrival() < originDeparture); orderTime = locationArrival.getArrival(); if (locationArrival.getPublic_arrival() != null) { stmtLocations.setInt(parameterIndex++, locationArrival.getPublic_arrival()); stmtLocations.setBoolean(parameterIndex++, originPublicDeparture != null ? locationArrival.getPublic_arrival() < originPublicDeparture : null); publicTime = locationArrival.getPublic_arrival(); } else { stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); } } else { stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); } if (location instanceof CIFLocationIntermediate && ((CIFLocationIntermediate) location).getPass() != null) { CIFLocationIntermediate locationIntermediate = (CIFLocationIntermediate) location; stmtLocations.setInt(parameterIndex++, locationIntermediate.getPass()); stmtLocations.setBoolean(parameterIndex++, locationIntermediate.getPass() < originDeparture); orderTime = locationIntermediate.getPass(); } else { stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); } if (location instanceof CIFLocationDepart && ((CIFLocationDepart) location).getDeparture() != null) { CIFLocationDepart locationDeparture = (CIFLocationDepart) location; stmtLocations.setInt(parameterIndex++, locationDeparture.getDeparture()); stmtLocations.setBoolean(parameterIndex++, locationDeparture.getDeparture() < originDeparture); orderTime = locationDeparture.getDeparture(); if (locationDeparture.getPublic_departure() != null) { stmtLocations.setInt(parameterIndex++, locationDeparture.getPublic_departure()); stmtLocations.setBoolean(parameterIndex++, originPublicDeparture != null ? locationDeparture.getPublic_departure() < originPublicDeparture : null); publicTime = locationDeparture.getPublic_departure(); } else { stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); } } else { stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); } if (location.getPlatform().length() != 0) stmtLocations.setString(parameterIndex++, location.getPlatform()); else stmtLocations.setNull(parameterIndex++, java.sql.Types.VARCHAR); if (location instanceof CIFLocationDepart && ((CIFLocationDepart) location).getLine().length() != 0) stmtLocations.setString(parameterIndex++, ((CIFLocationDepart) location).getLine()); else stmtLocations.setNull(parameterIndex++, java.sql.Types.VARCHAR); if (location instanceof CIFLocationArrival && ((CIFLocationArrival) location).getPath().length() != 0) stmtLocations.setString(parameterIndex++, ((CIFLocationArrival) location).getPath()); else stmtLocations.setNull(parameterIndex++, java.sql.Types.VARCHAR); if (location instanceof CIFLocationDepart) { CIFLocationDepart locationDeparture = (CIFLocationDepart) location; stmtLocations.setInt(parameterIndex++, locationDeparture.getAllowance_engineering()); stmtLocations.setInt(parameterIndex++, locationDeparture.getAllowance_pathing()); stmtLocations.setInt(parameterIndex++, locationDeparture.getAllowance_performance()); } else { stmtLocations.setInt(parameterIndex++, 0); stmtLocations.setInt(parameterIndex++, 0); stmtLocations.setInt(parameterIndex++, 0); } stmtLocations.setBoolean(parameterIndex++, location.isPublic_call()); stmtLocations.setBoolean(parameterIndex++, location.isActual_call()); // stmtLocations.setInt(parameterIndex++, location.isActual_call() ? ( ? : ) : ((CIFLocationIntermediate) location).getPass()); stmtLocations.setInt(parameterIndex++, orderTime); if (publicTime != null) stmtLocations.setInt(parameterIndex++, publicTime); else stmtLocations.setNull(parameterIndex++, java.sql.Types.INTEGER); stmtLocations.setBoolean(parameterIndex++, location.isAc_a()); stmtLocations.setBoolean(parameterIndex++, location.isAc_ae()); stmtLocations.setBoolean(parameterIndex++, location.isAc_bl()); stmtLocations.setBoolean(parameterIndex++, location.isAc_c()); stmtLocations.setBoolean(parameterIndex++, location.isAc_d()); stmtLocations.setBoolean(parameterIndex++, location.isAc__d()); stmtLocations.setBoolean(parameterIndex++, location.isAc_e()); stmtLocations.setBoolean(parameterIndex++, location.isAc_g()); stmtLocations.setBoolean(parameterIndex++, location.isAc_h()); stmtLocations.setBoolean(parameterIndex++, location.isAc_hh()); stmtLocations.setBoolean(parameterIndex++, location.isAc_k()); stmtLocations.setBoolean(parameterIndex++, location.isAc_kc()); stmtLocations.setBoolean(parameterIndex++, location.isAc_ke()); stmtLocations.setBoolean(parameterIndex++, location.isAc_kf()); stmtLocations.setBoolean(parameterIndex++, location.isAc_ks()); stmtLocations.setBoolean(parameterIndex++, location.isAc_l()); stmtLocations.setBoolean(parameterIndex++, location.isAc_n()); stmtLocations.setBoolean(parameterIndex++, location.isAc_op()); stmtLocations.setBoolean(parameterIndex++, location.isAc_or()); stmtLocations.setBoolean(parameterIndex++, location.isAc_pr()); stmtLocations.setBoolean(parameterIndex++, location.isAc_r()); stmtLocations.setBoolean(parameterIndex++, location.isAc_rm()); stmtLocations.setBoolean(parameterIndex++, location.isAc_rr()); stmtLocations.setBoolean(parameterIndex++, location.isAc_s()); stmtLocations.setBoolean(parameterIndex++, location.isAc_t()); stmtLocations.setBoolean(parameterIndex++, location.isAc__t()); stmtLocations.setBoolean(parameterIndex++, location.isAc_tb()); stmtLocations.setBoolean(parameterIndex++, location.isAc_tf()); stmtLocations.setBoolean(parameterIndex++, location.isAc_ts()); stmtLocations.setBoolean(parameterIndex++, location.isAc_tw()); stmtLocations.setBoolean(parameterIndex++, location.isAc_u()); stmtLocations.setBoolean(parameterIndex++, location.isAc__u()); stmtLocations.setBoolean(parameterIndex++, location.isAc_w()); stmtLocations.setBoolean(parameterIndex++, location.isAc_x()); if (location instanceof CIFLocationIntermediate) { CIFLocationEnRouteChange change = ((CIFLocationIntermediate) location).getChangeRecord(); if (change != null) { stmtLocChange.setInt(1, schedule.getDatabaseId()); stmtLocChange.setInt(2, locationOrder); stmtLocChange.setString(3, change.getCategory()); stmtLocChange.setString(4, change.getTrain_identity()); stmtLocChange.setString(5, change.getHeadcode()); stmtLocChange.setString(6, change.getService_code()); if (change.getPortion_id() != ' ') stmtLocChange.setString(7, String.valueOf(change.getPortion_id())); else stmtLocChange.setNull(7, java.sql.Types.CHAR); stmtLocChange.setString(8, change.getPower_type()); stmtLocChange.setString(9, change.getTiming_load()); stmtLocChange.setString(10, change.getSpeed()); if (change.getTrain_class() != ' ') stmtLocChange.setString(11, String.valueOf(change.getTrain_class())); else stmtLocChange.setNull(11, java.sql.Types.CHAR); if (change.getSleeper() != ' ') stmtLocChange.setString(12, String.valueOf(change.getSleeper())); else stmtLocChange.setNull(12, java.sql.Types.CHAR); if (change.getReservations() != ' ') stmtLocChange.setString(13, String.valueOf(change.getReservations())); else stmtLocChange.setNull(13, java.sql.Types.CHAR); stmtLocChange.setString(14, change.getCatering_code()); stmtLocChange.setString(15, change.getService_branding()); stmtLocChange.setString(16, change.getUic()); stmtLocChange.setString(17, change.getRsid()); stmtLocChange.setBoolean(18, change.isOc_b()); stmtLocChange.setBoolean(19, change.isOc_c()); stmtLocChange.setBoolean(20, change.isOc_d()); stmtLocChange.setBoolean(21, change.isOc_e()); stmtLocChange.setBoolean(22, change.isOc_g()); stmtLocChange.setBoolean(23, change.isOc_m()); stmtLocChange.setBoolean(24, change.isOc_p()); stmtLocChange.setBoolean(25, change.isOc_q()); stmtLocChange.setBoolean(26, change.isOc_r()); stmtLocChange.setBoolean(27, change.isOc_s()); stmtLocChange.setBoolean(28, change.isOc_y()); stmtLocChange.setBoolean(29, change.isOc_z()); stmtLocChange.addBatch(); } } } } stmtLocChange.executeBatch(); stmtLocations.execute(); stmtLocations.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); System.exit(1); } }
From source file:uk.ac.ebi.sail.server.data.DataManager.java
public ClassifierShadow addClassifier(ClassifierShadow cs) { Connection conn = null;/*w ww .j av a 2s . com*/ ResultSet rst = null; try { conn = dSrc.getConnection(); PreparedStatement pstmt = conn.prepareStatement(insertClassifierSQL, Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, cs.getName()); pstmt.setString(2, cs.getDesc()); pstmt.setBoolean(3, cs.isAllowMulty()); pstmt.setBoolean(4, cs.isMandatory()); pstmt.setString(5, cs.getTarget().name()); pstmt.executeUpdate(); rst = pstmt.getGeneratedKeys(); int id = -1; if (rst.next()) id = rst.getInt(1); rst.close(); pstmt.close(); if (cs.getTags() != null) { pstmt = conn.prepareStatement(insertTagSQL, Statement.RETURN_GENERATED_KEYS); for (Tag t : cs.getTags()) { pstmt.setString(1, t.getName()); pstmt.setString(2, t.getDescription()); pstmt.setInt(3, id); pstmt.executeUpdate(); rst = pstmt.getGeneratedKeys(); if (rst.next()) t.setId(rst.getInt(1)); rst.close(); } pstmt.close(); } if (cs.getClassificationTags() != null) { pstmt = conn.prepareStatement(insertClassifierClassificationSQL); for (int tid : cs.getClassificationTags()) { Tag t = tags.get(tid); if (t == null) { logger.warn("Invalid tag ID=" + tid); continue; } pstmt.setInt(1, id); pstmt.setInt(2, tid); pstmt.executeUpdate(); } } Classifier nc = cs.createClassifier(); nc.setId(id); cs.setId(id); nc.setTags(cs.getTags()); if (nc.getTags() != null) { for (Tag t : nc.getTags()) tags.put(t.getId(), t); } classifiers.put(nc.getId(), nc); classifiersList.add(nc); return cs; } 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:uk.ac.ebi.sail.server.data.DataManager.java
public void importRelations(String txt) throws ParseException { String SEP = null;//from w w w .j a va 2 s .c om int cpos = 0; int len = txt.length(); String lineSep = "\n"; cpos = txt.indexOf(lineSep); if (cpos == -1) throw new ParseException(1, "File must contain at least one line"); if (cpos > 0 && txt.charAt(cpos - 1) == '\r') lineSep = "\r\n"; cpos = 0; List<Relation> newRels = new ArrayList<Relation>(); int ln = 0; while (cpos < len) { ln++; int pos = txt.indexOf(lineSep, cpos); if (pos == -1) break; String str = txt.substring(cpos, pos); cpos = pos + lineSep.length(); if (str.length() == 0) continue; if (SEP == null) { if (str.indexOf('\t') > 0) SEP = "\t"; else SEP = ","; } String[] parts = str.split(SEP); if (parts.length != 4) throw new ParseException(ln, "Invalid syntax. Must be <Host> <Classifier> <Tag> <Target>"); Parameter hp = paramCodeMap.get(parts[0]); if (hp == null) throw new ParseException(ln, "Invalid or unknown parameter code: '" + parts[0] + "'"); Parameter tp = paramCodeMap.get(parts[3]); if (tp == null) throw new ParseException(ln, "Invalid or unknown parameter code: '" + parts[1] + "'"); Classifier cl = null; for (Classifier c : classifiersList) { if (c.getName().equals(parts[1])) { cl = c; break; } } if (cl == null) throw new ParseException(ln, "Unknown classifier: '" + parts[1] + "'"); Tag tg = null; for (Tag t : cl.getTags()) { if (t.getName().equals(parts[2])) { tg = t; break; } } if (tg == null) throw new ParseException(ln, "Unknown tag: '" + parts[1] + ":" + parts[2] + "'"); boolean has = false; if (hp.getRelations() != null) { for (Relation r : hp.getRelations()) { if (r.getTag() == tg && r.getTargetParameter() == tp) { has = true; // throw new ParseException(ln,"This relation already exists"); } } } if (!has) { Relation rl = new Relation(); rl.setTargetParameter(tp); rl.setTag(tg); rl.setHostParameter(hp); newRels.add(rl); } } if (newRels.size() == 0) return; Connection conn = null; ResultSet rst = null; try { conn = dSrc.getConnection(); PreparedStatement stmt = conn.prepareStatement(insertRelationSQL, PreparedStatement.RETURN_GENERATED_KEYS); for (Relation r : newRels) { stmt.setInt(1, r.getHostParameter().getId()); stmt.setInt(2, r.getTargetParameter().getId()); stmt.setInt(3, r.getTag().getId()); stmt.executeUpdate(); rst = stmt.getGeneratedKeys(); if (rst.next()) { r.setId(rst.getInt(1)); } rst.close(); r.getHostParameter().addRelation(r); ParameterShadow ps = ((SSParameterInfo) r.getHostParameter().getAuxInfo()).getShadow(); int[][] rels = ps.getRelations(); if (rels == null) ps.setRelations(new int[][] { new int[] { r.getId(), r.getTargetParameter().getId(), r.getTag().getId() } }); else { int[][] nrels = new int[rels.length + 1][]; for (int i = 0; i < rels.length; i++) nrels[i] = rels[i]; nrels[rels.length] = new int[] { r.getId(), r.getTargetParameter().getId(), r.getTag().getId() }; ps.setRelations(nrels); } } } catch (SQLException e) { Log.error("SQL error", e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { Log.error("Connection closing error", e); } } } }
From source file:at.alladin.rmbt.controlServer.RegistrationResource.java
@Post("json") public String request(final String entity) { long startTime = System.currentTimeMillis(); final String secret = getContext().getParameters().getFirstValue("RMBT_SECRETKEY"); addAllowOrigin();// ww w. ja v a2 s.co m JSONObject request = null; final ErrorList errorList = new ErrorList(); final JSONObject answer = new JSONObject(); String answerString; final String clientIpRaw = getIP(); final InetAddress clientAddress = InetAddresses.forString(clientIpRaw); final String clientIpString = InetAddresses.toAddrString(clientAddress); System.out.println(MessageFormat.format(labels.getString("NEW_REQUEST"), clientIpRaw)); final String geoIpCountry = GeoIPHelper.lookupCountry(clientAddress); // public_ip_asn final Long asn = Helperfunctions.getASN(clientAddress); // public_ip_as_name // country_asn (2 digit country code of AS, eg. AT or EU) final String asName; final String asCountry; if (asn == null) { asName = null; asCountry = null; } else { asName = Helperfunctions.getASName(asn); asCountry = Helperfunctions.getAScountry(asn); } if (entity != null && !entity.isEmpty()) // try parse the string to a JSON object try { request = new JSONObject(entity); int typeId = 0; final String lang = request.optString("language"); // Load Language Files for Client final List<String> langs = Arrays .asList(settings.getString("RMBT_SUPPORTED_LANGUAGES").split(",\\s*")); if (langs.contains(lang)) { errorList.setLanguage(lang); labels = ResourceManager.getSysMsgBundle(new Locale(lang)); } // System.out.println(request.toString(4)); if (conn != null) { final Client clientDb = new Client(conn); if (!request.optString("type").isEmpty()) { typeId = clientDb.getTypeId(request.getString("type")); if (clientDb.hasError()) errorList.addError(clientDb.getError()); } final List<String> clientNames = Arrays .asList(settings.getString("RMBT_CLIENT_NAME").split(",\\s*")); final List<String> clientVersions = Arrays .asList(settings.getString("RMBT_VERSION_NUMBER").split(",\\s*")); if (clientNames.contains(request.optString("client")) && clientVersions.contains(request.optString("version")) && typeId > 0) { UUID uuid = null; final String uuidString = request.optString("uuid", ""); if (uuidString.length() != 0) uuid = UUID.fromString(uuidString); final String clientName = request.getString("client"); final String clientVersion = request.getString("version"); String timeZoneId = request.getString("timezone"); // String tmpTimeZoneId = timeZoneId; final long clientTime = request.getLong("time"); final Timestamp clientTstamp = java.sql.Timestamp .valueOf(new Timestamp(clientTime).toString()); final JSONObject location = request.optJSONObject("location"); long geotime = 0; double geolat = 0; double geolong = 0; float geoaccuracy = 0; double geoaltitude = 0; float geobearing = 0; float geospeed = 0; String geoprovider = ""; if (!request.isNull("location")) { geotime = location.optLong("time", 0); geolat = location.optDouble("lat", 0); geolong = location.optDouble("long", 0); geoaccuracy = (float) location.optDouble("accuracy", 0); geoaltitude = location.optDouble("altitude", 0); geobearing = (float) location.optDouble("bearing", 0); geospeed = (float) location.optDouble("speed", 0); geoprovider = location.optString("provider", ""); } Calendar timeWithZone = null; if (timeZoneId.isEmpty()) { timeZoneId = Helperfunctions.getTimezoneId(); timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); } else timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); long clientUid = 0; /* * if (uuid == null) { * clientDb.setTimeZone(timeWithZone); * clientDb.setTime(tstamp); * clientDb.setClient_type_id(typeId); uuid = * clientDb.storeClient(); if (clientDb.hasError()) { * errorList.addError(clientDb.getError()); } else { * answer.put("uuid", uuid.toString()); } } */ if (errorList.getLength() == 0 && uuid != null) { clientUid = clientDb.getClientByUuid(uuid); if (clientDb.hasError()) errorList.addError(clientDb.getError()); } if (clientUid > 0) { final String testUuid = UUID.randomUUID().toString(); final String testOpenUuid = UUID.randomUUID().toString(); boolean testServerEncryption = true; // default is // true // hack for android api <= 10 (2.3.x) // using encryption with test doesn't work if (request.has("plattform") && request.optString("plattform").equals("Android")) if (request.has("api_level")) { final String apiLevelString = request.optString("api_level"); try { final int apiLevel = Integer.parseInt(apiLevelString); if (apiLevel <= 10) testServerEncryption = false; } catch (final NumberFormatException e) { } } final String serverType; if (request.optString("client").equals("RMBTws")) serverType = "RMBTws"; else serverType = "RMBT"; final Boolean ipv6; if (clientAddress instanceof Inet6Address) ipv6 = true; else if (clientAddress instanceof Inet4Address) ipv6 = false; else // should never happen, unless ipv > 6 is available ipv6 = null; final TestServer server = getNearestServer(errorList, geolat, geolong, geotime, clientIpString, asCountry, geoIpCountry, serverType, testServerEncryption, ipv6); try { if (server == null) throw new JSONException("could not find server"); if (timeZoneId.isEmpty()) { timeZoneId = Helperfunctions.getTimezoneId(); timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); } else timeWithZone = Helperfunctions.getTimeWithTimeZone(timeZoneId); answer.put("test_server_address", server.address); answer.put("test_server_port", server.port); answer.put("test_server_name", server.name); answer.put("test_server_encryption", testServerEncryption); answer.put("test_duration", getSetting("rmbt_duration")); answer.put("test_numthreads", getSetting("rmbt_num_threads")); answer.put("test_numpings", getSetting("rmbt_num_pings")); answer.put("client_remote_ip", clientIpString); final String resultUrl = new Reference(getURL(), settings.getString("RMBT_RESULT_PATH")).getTargetRef().toString(); // System.out.println(resultUrl); answer.put("result_url", resultUrl); final String resultQoSUrl = new Reference(getURL(), settings.getString("RMBT_QOS_RESULT_PATH")).getTargetRef().toString(); // System.out.println(resultUrl); answer.put("result_qos_url", resultQoSUrl); } catch (final JSONException e) { System.out.println("Error generating Answer " + e.toString()); errorList.addError("ERROR_RESPONSE_JSON"); } if (errorList.getLength() == 0) try { PreparedStatement st; st = conn.prepareStatement( "INSERT INTO test(time, uuid, open_test_uuid, client_id, client_name, client_version, client_software_version, client_language, client_public_ip, client_public_ip_anonymized, country_geoip, server_id, port, use_ssl, timezone, client_time, duration, num_threads_requested, status, software_revision, client_test_counter, client_previous_test_status, public_ip_asn, public_ip_as_name, country_asn, public_ip_rdns, run_ndt)" + "VALUES(NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS); int i = 1; // uuid st.setObject(i++, UUID.fromString(testUuid)); // open_test_uuid st.setObject(i++, UUID.fromString(testOpenUuid)); // client_id st.setLong(i++, clientUid); // client_name st.setString(i++, clientName); // client_version st.setString(i++, clientVersion); // client_software_version st.setString(i++, request.optString("softwareVersion", null)); // client_language st.setString(i++, lang); // client_public_ip st.setString(i++, clientIpString); // client_public_ip_anonymized st.setString(i++, Helperfunctions.anonymizeIp(clientAddress)); // country_geoip (2digit country code derived from public IP of client) st.setString(i++, geoIpCountry); // server_id st.setInt(i++, server.id); // port st.setInt(i++, server.port); // use_ssl st.setBoolean(i++, testServerEncryption); // timezone (of client) st.setString(i++, timeZoneId); // client_time (local time of client) st.setTimestamp(i++, clientTstamp, timeWithZone); // duration (requested) st.setInt(i++, Integer.parseInt(getSetting("rmbt_duration"))); // num_threads_requested st.setInt(i++, Integer.parseInt(getSetting("rmbt_num_threads"))); // status (of test) st.setString(i++, "STARTED"); //was "RUNNING" before // software_revision (of client) st.setString(i++, request.optString("softwareRevision", null)); // client_test_counter (number of tests the client has performed) final int testCounter = request.optInt("testCounter", -1); if (testCounter == -1) // older clients did not support testCounter st.setNull(i++, Types.INTEGER); else st.setLong(i++, testCounter); // client_previous_test_status (outcome of previous test) st.setString(i++, request.optString("previousTestStatus", null)); // AS name if (asn == null) st.setNull(i++, Types.BIGINT); else st.setLong(i++, asn); if (asName == null) st.setNull(i++, Types.VARCHAR); else st.setString(i++, asName); // AS country if (asCountry == null) st.setNull(i++, Types.VARCHAR); else st.setString(i++, asCountry); //public_ip_rdns String reverseDNS = Helperfunctions.reverseDNSLookup(clientAddress); if (reverseDNS == null || reverseDNS.isEmpty()) st.setNull(i++, Types.VARCHAR); else { reverseDNS = reverseDNS.replaceFirst("\\.$", ""); st.setString(i++, reverseDNS); // cut off last dot (#332) } // run_ndt if (request.has("ndt")) st.setBoolean(i++, request.getBoolean("ndt")); else st.setNull(i++, Types.BOOLEAN); final int affectedRows = st.executeUpdate(); if (affectedRows == 0) errorList.addError("ERROR_DB_STORE_TEST"); else { long key = 0; final ResultSet rs = st.getGeneratedKeys(); if (rs.next()) // Retrieve the auto generated // key(s). key = rs.getLong(1); rs.close(); final PreparedStatement getProviderSt = conn .prepareStatement("SELECT rmbt_set_provider_from_as(?)"); getProviderSt.setLong(1, key); String provider = null; if (getProviderSt.execute()) { final ResultSet rs2 = getProviderSt.getResultSet(); if (rs2.next()) provider = rs2.getString(1); } if (provider != null) answer.put("provider", provider); final PreparedStatement testSlotStatement = conn .prepareStatement("SELECT rmbt_get_next_test_slot(?)"); testSlotStatement.setLong(1, key); int testSlot = -1; if (testSlotStatement.execute()) { final ResultSet rs2 = testSlotStatement.getResultSet(); if (rs2.next()) testSlot = rs2.getInt(1); } if (testSlot < 0) errorList.addError("ERROR_DB_STORE_GENERAL"); else { final String data = testUuid + "_" + testSlot; final String hmac = Helperfunctions.calculateHMAC(secret, data); if (hmac.length() == 0) errorList.addError("ERROR_TEST_TOKEN"); final String token = data + "_" + hmac; final PreparedStatement updateSt = conn .prepareStatement("UPDATE test SET token = ? WHERE uid = ?"); updateSt.setString(1, token); updateSt.setLong(2, key); updateSt.executeUpdate(); answer.put("test_token", token); answer.put("test_uuid", testUuid); answer.put("test_id", key); final long now = System.currentTimeMillis(); int wait = testSlot - (int) (now / 1000); if (wait < 0) wait = 0; answer.put("test_wait", wait); if (geotime != 0 && geolat != 0 && geolong != 0) { final GeoLocation clientLocation = new GeoLocation(conn); clientLocation.setTest_id(key); final Timestamp geotstamp = java.sql.Timestamp .valueOf(new Timestamp(geotime).toString()); clientLocation.setTime(geotstamp, timeZoneId); clientLocation.setAccuracy(geoaccuracy); clientLocation.setAltitude(geoaltitude); clientLocation.setBearing(geobearing); clientLocation.setSpeed(geospeed); clientLocation.setProvider(geoprovider); clientLocation.setGeo_lat(geolat); clientLocation.setGeo_long(geolong); clientLocation.storeLocation(); if (clientLocation.hasError()) errorList.addError(clientLocation.getError()); } } } st.close(); } catch (final SQLException e) { errorList.addError("ERROR_DB_STORE_GENERAL"); e.printStackTrace(); } } else errorList.addError("ERROR_CLIENT_UUID"); } else errorList.addError("ERROR_CLIENT_VERSION"); } else errorList.addError("ERROR_DB_CONNECTION"); // System.out.println(answer.toString(4)); } catch (final JSONException e) { errorList.addError("ERROR_REQUEST_JSON"); System.out.println("Error parsing JSDON Data " + e.toString()); } else errorList.addErrorString("Expected request is missing."); try { answer.putOpt("error", errorList.getList()); } catch (final JSONException e) { System.out.println("Error saving ErrorList: " + e.toString()); } answerString = answer.toString(); long elapsedTime = System.currentTimeMillis() - startTime; System.out.println(MessageFormat.format(labels.getString("NEW_REQUEST_SUCCESS"), clientIpRaw, Long.toString(elapsedTime))); return answerString; }
From source file:uk.ac.ebi.sail.server.data.DataManager.java
public ParameterShadow addParameter(ParameterShadow sp) throws ParameterManagementException { Connection conn = null;//from ww w. j a v a 2 s .c o m ResultSet rst = null; Parameter pr = sp.createParameter(); if (sp.getInheritedParameters() != null) { for (int ipid : sp.getInheritedParameters()) { Parameter inhP = params.get(ipid); if (inhP == null) throw new ParameterManagementException("Invalid inherited parameter ID=" + ipid, ParameterManagementException.INV_INH_PARAMETER_ID); pr.addInheritedParameter(inhP); } } if (sp.getTags() != null) { for (int tid : sp.getTags()) { Tag t = tags.get(tid); if (t == null) throw new ParameterManagementException("Invalid tag ID=" + tid, ParameterManagementException.INV_TAG_ID); pr.addClassificationTag(t); } } if (sp.getAnnotations() != null) { for (AnnotationShadow ans : sp.getAnnotations()) { Tag t = tags.get(ans.getTag()); if (t == null) throw new ParameterManagementException("Invalid annotation tag ID=" + ans.getTag(), ParameterManagementException.INV_TAG_ID); Annotation an = ans.createAnnotation(); an.setTag(t); pr.addAnnotation(an); } } if (sp.getRelations() != null) { int i = 1; for (int[] rl : sp.getRelations()) { Parameter rP = params.get(rl[1]); if (rP == null) throw new ParameterManagementException("Invalid inherited parameter ID=" + rl[1], ParameterManagementException.INV_INH_PARAMETER_ID); Tag t = tags.get(rl[2]); if (t == null) throw new ParameterManagementException("Invalid tag ID=" + rl[2], ParameterManagementException.INV_TAG_ID); rl[0] = i; Relation r = new Relation(); r.setId(i); r.setHostParameter(pr); r.setTargetParameter(rP); r.setTag(t); pr.addRelation(r); i++; } } try { conn = dSrc.getConnection(); PreparedStatement pstmt = conn.prepareStatement("INSERT INTO " + TBL_PARAMETER + " (" + FLD_CODE + ',' + FLD_NAME + ',' + FLD_DESCRIPTION + ") VALUES (?,?,?)", Statement.RETURN_GENERATED_KEYS); pstmt.setString(1, sp.getCode()); pstmt.setString(2, sp.getName()); pstmt.setString(3, sp.getDesc()); try { pstmt.executeUpdate(); } catch (SQLIntegrityConstraintViolationException ex) { throw new ParameterManagementException("Parameter with code: '" + sp.getCode() + "' already exists", ex, ParameterManagementException.CODE_EXISTS); } rst = pstmt.getGeneratedKeys(); int id = -1; if (rst.next()) id = rst.getInt(1); pstmt.close(); if (sp.getVariables() != null || sp.getQualifiers() != null) { PreparedStatement vStmt = null; pstmt = conn.prepareStatement(insertPartSQL, Statement.RETURN_GENERATED_KEYS); if (sp.getVariables() != null) { for (Variable v : sp.getVariables()) { pstmt.setInt(1, id); pstmt.setString(2, v.getName()); pstmt.setString(3, v.getDescription()); pstmt.setString(4, v.getType().name()); pstmt.setBoolean(5, v.isPredefined()); pstmt.setBoolean(6, v.isMandatory()); pstmt.executeUpdate(); rst = pstmt.getGeneratedKeys(); if (rst.next()) v.setId(rst.getInt(1)); rst.close(); if (v.getType() == Type.ENUM && v.getVariants() != null) { if (vStmt == null) vStmt = conn.prepareStatement(insertVariantSQL, Statement.RETURN_GENERATED_KEYS); for (Variant vr : v.getVariants()) { if (!vr.isPredefined()) continue; vStmt.setInt(1, v.getId()); vStmt.setString(2, vr.getName()); vStmt.setInt(3, vr.getCoding()); vStmt.setBoolean(4, vr.isPredefined()); vStmt.executeUpdate(); rst = vStmt.getGeneratedKeys(); if (rst.next()) vr.setId(rst.getInt(1)); rst.close(); } } } } if (sp.getQualifiers() != null) { for (Qualifier q : sp.getQualifiers()) { pstmt.setInt(1, id); pstmt.setString(2, q.getName()); pstmt.setString(3, q.getDescription()); pstmt.setString(4, QUALIFIER_TYPE); pstmt.setBoolean(5, q.isPredefined()); pstmt.setBoolean(6, q.isMandatory()); pstmt.executeUpdate(); rst = pstmt.getGeneratedKeys(); if (rst.next()) q.setId(rst.getInt(1)); rst.close(); if (q.getVariants() != null) { if (vStmt == null) vStmt = conn.prepareStatement(insertVariantSQL, Statement.RETURN_GENERATED_KEYS); for (Variant vr : q.getVariants()) { if (!vr.isPredefined()) continue; vStmt.setInt(1, q.getId()); vStmt.setString(2, vr.getName()); vStmt.setInt(3, vr.getCoding()); vStmt.setBoolean(4, vr.isPredefined()); vStmt.executeUpdate(); rst = vStmt.getGeneratedKeys(); if (rst.next()) vr.setId(rst.getInt(1)); rst.close(); } } } } if (vStmt != null) vStmt.close(); pstmt.close(); } if (sp.getInheritedParameters() != null) { pstmt = conn.prepareStatement("INSERT INTO " + TBL_INHERITED + " (" + FLD_HOST_PARAM_ID + ',' + FLD_TARGET_PARAM_ID + ") VALUES (" + id + ",?)"); for (int ip : sp.getInheritedParameters()) { pstmt.setInt(1, ip); pstmt.executeUpdate(); } pstmt.close(); } if (sp.getAnnotations() != null) { pstmt = conn.prepareStatement(insertParameterAnnotationsSQL); for (AnnotationShadow ans : sp.getAnnotations()) { pstmt.setInt(1, id); pstmt.setInt(2, ans.getTag()); pstmt.setString(3, ans.getText()); pstmt.executeUpdate(); } pstmt.close(); } if (sp.getTags() != null) { pstmt = conn.prepareStatement("INSERT INTO " + TBL_PARAMETER_CLASSIFICATION + " (" + FLD_PARAMETER_ID + ',' + FLD_TAG_ID + ") VALUES (" + id + ",?)"); for (int t : sp.getTags()) { pstmt.setInt(1, t); pstmt.executeUpdate(); } pstmt.close(); } if (sp.getRelations() != null) { pstmt = conn.prepareStatement( "INSERT INTO " + TBL_RELATION + " (" + FLD_HOST_PARAM_ID + ',' + FLD_TARGET_PARAM_ID + ',' + FLD_TAG_ID + ") VALUES (" + id + ",?,?)", PreparedStatement.RETURN_GENERATED_KEYS); for (int[] r : sp.getRelations()) { pstmt.setInt(1, r[1]); pstmt.setInt(2, r[2]); pstmt.executeUpdate(); rst = pstmt.getGeneratedKeys(); int rlid = -1; if (rst.next()) rlid = rst.getInt(1); for (Relation rl : pr.getRelations()) { if (rl.getId() == r[0]) { rl.setId(rlid); break; } } r[0] = rlid; } pstmt.close(); } SSParameterInfo ssp = new SSParameterInfo(); ssp.setShadow(sp); pr.setAuxInfo(ssp); pr.setId(id); sp.setId(id); params.put(id, pr); paramCodeMap.put(pr.getCode(), pr); paramList.add(sp); return sp; } catch (SQLException e) { logger.error("SQL error", e); throw new ParameterManagementException("SQL error: " + e.getMessage(), e, ParameterManagementException.SQL_ERROR); } 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); } } } }
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()); /*/* w w w . j av a2 s. c o m*/ 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:org.wso2.carbon.appmgt.impl.dao.AppMDAO.java
/** * Save policy groups//from w w w . j a v a 2 s . com * * @param policyGroupName :policy group name * @param throttlingTier : throttling Tier * @param userRoles : user roles * @param isAnonymousAllowed : is anonymous access allowed to URL pattern * @param objPartialMappings : Object which contains XACML policy partial details arrays * @param policyGroupDesc :Policy group Desciption * @return : last saved policy group id * @throws AppManagementException if any an error found while saving data to DB */ public static Integer savePolicyGroup(String policyGroupName, String throttlingTier, String userRoles, String isAnonymousAllowed, Object[] objPartialMappings, String policyGroupDesc) throws AppManagementException { PreparedStatement ps = null; Connection conn = null; ResultSet rs = null; String query = "INSERT INTO APM_POLICY_GROUP(NAME,THROTTLING_TIER,USER_ROLES,URL_ALLOW_ANONYMOUS,DESCRIPTION) " + "VALUES(?,?,?,?,?) "; int policyGroupId = -1; try { conn = APIMgtDBUtil.getConnection(); conn.setAutoCommit(false); ps = conn.prepareStatement(query, new String[] { "POLICY_GRP_ID" }); ps.setString(1, policyGroupName); ps.setString(2, throttlingTier); ps.setString(3, userRoles); ps.setBoolean(4, Boolean.parseBoolean(isAnonymousAllowed)); ps.setString(5, policyGroupDesc); ps.executeUpdate(); rs = ps.getGeneratedKeys(); if (rs.next()) { policyGroupId = Integer.parseInt(rs.getString(1)); } // save partials mapped to policy group if (objPartialMappings != null) { if (objPartialMappings.length > 0) { savePolicyPartialMappings(policyGroupId, objPartialMappings, conn); } } conn.commit(); if (log.isDebugEnabled()) { StringBuilder strDataContext = new StringBuilder(); strDataContext.append("(policyGroupName:").append(policyGroupName).append(", throttlingTier:") .append(throttlingTier).append(", userRoles:").append(userRoles) .append(", isAnonymousAllowed:").append(isAnonymousAllowed).append(", Partial Mappings:") .append(objPartialMappings).append(")"); log.debug("Record saved successfully." + strDataContext.toString()); } } catch (SQLException e) { if (conn != null) { try { conn.rollback(); } catch (SQLException e1) { log.error("Failed to rollback while saving the policy group - " + policyGroupId, e); } } StringBuilder strDataContext = new StringBuilder(); strDataContext.append("(policyGroupName:").append(policyGroupName).append(", throttlingTier:") .append(throttlingTier).append(", userRoles:").append(userRoles).append(", isAnonymousAllowed:") .append(isAnonymousAllowed).append(", Partial Mappings:").append(objPartialMappings) .append(")"); handleException("SQL Error while executing the query to save Policy Group : " + query + " : " + strDataContext.toString(), e); } finally { APIMgtDBUtil.closeAllConnections(ps, conn, rs); } return policyGroupId; }
From source file:uk.ac.ebi.sail.server.data.DataManager.java
public void importData(String txt, int collectionID) throws ParseException { studySummaryCache.clear();/*from w ww. j a v a 2s . co m*/ collectionSummaryCache.clear(); Collection<Record> rcs = new ArrayList<Record>(); int cpos = 0; int len = txt.length(); int ln = 0; char colSeparator = '\0'; if (!txt.startsWith(SAMPLE_ID_COL)) { if (!txt.startsWith("\"" + SAMPLE_ID_COL + "\"")) throw new ParseException(1, "The first column must be " + SAMPLE_ID_COL); colSeparator = txt.charAt(SAMPLE_ID_COL.length() + 2); } else colSeparator = txt.charAt(SAMPLE_ID_COL.length()); if (colSeparator != '\t' && colSeparator != ',') throw new ParseException(1, "Column separator must be either tab or comma"); String sep = "" + colSeparator; String lineSep = "\r\n"; if (txt.indexOf(lineSep) == -1) lineSep = "\n"; if (txt.indexOf(lineSep) == -1) throw new ParseException(1, "File must contains at least 2 lines separated by either \\n or \\r\\n "); Map<ParameterPart, List<Variant>> tmpVarisMap = new TreeMap<ParameterPart, List<Variant>>(); // String qSep = "\""+sep; List<String> parts = new ArrayList<String>(100); FullPartRef[] pparts = null; while (cpos < len) { // ln++; // System.out.println("Line: "+ln); int pos = txt.indexOf(lineSep, cpos); if (pos == -1) break; parts.clear(); StringUtil.splitExcelString(txt.substring(cpos, pos), sep, parts); // String[] parts = txt.substring(cpos, pos).split(sep,-2); if (ln == 0) { ln++; pparts = analyzeHeader(parts); } else { ln++; // String[] row = new String[header.length]; if (parts.size() == 0) break; Record rc = new Record(); rc.setCollectionId(collectionID); for (int i = -1; i < pparts.length; i++) { if (i == -1) rc.setCollectionRecordIDs(parts.get(0)); else { String val = ""; if (i + 1 < parts.size()) val = parts.get(i + 1).trim(); ParameterPart pp = pparts[i].getParameterPart(); if (val.length() != 0) { if (pp.isEnum()) { short vid = pp.getVariantIndexByValue(val); // short vid = pparts[i].getParameterPart().getVariantID(val); if (vid == -1) { if (pp.isPredefined()) throw new ParseException(ln, "Variant '" + val + "' is not allowed for column: '" + pparts[i].getParameter().getCode() + '.' + pp.getName() + "'"); List<Variant> tmpVaris = tmpVarisMap.get(pp); short n = -1; if (tmpVaris == null) { tmpVaris = new ArrayList<Variant>(5); tmpVarisMap.put(pp, tmpVaris); } else { n = 1; for (Variant v : tmpVaris) { if (v.getName().equals(val)) { v.incCount(); break; } n++; } if (n > tmpVaris.size()) n = -1; } if (n < 0) { Variant nV = new Variant(val, 0, false); nV.setId(0); nV.incCount(); tmpVaris.add(nV); n = (short) tmpVaris.size(); } vid = (short) -n; } VariantPartValue pv = new VariantPartValue(pp); pv.setVariant(vid); rc.addPartValue(pv); } else { if (ParameterPart.SECURED_VARIANT_SIGN.equals(val)) { PartValue pv = new PartValue(pp); rc.addPartValue(pv); } else { Variable vrbl = (Variable) pp; if (vrbl.getType() == Type.REAL) { float realValue; try { realValue = Float.parseFloat(val); } catch (Exception e) { throw new ParseException(ln, "Invalid value for REAL type column " + pparts[i].getParameter().getCode() + '.' + pp.getName()); } RealPartValue rpv = new RealPartValue(pp); rpv.setRealValue(realValue); rc.addPartValue(rpv); } else if (vrbl.getType() == Type.INTEGER || vrbl.getType() == Type.DATE) { int intValue; try { intValue = Integer.parseInt(val); } catch (Exception e) { throw new ParseException(ln, "Invalid value for " + vrbl.getType().name() + " type column " + pparts[i].getParameter().getCode() + '.' + pp.getName()); } IntPartValue ipv = new IntPartValue(pp); ipv.setIntValue(intValue); rc.addPartValue(ipv); } else if (vrbl.getType() == Type.BOOLEAN) { boolean boolValue; try { boolValue = Boolean.parseBoolean(val); } catch (Exception e) { throw new ParseException(ln, "Invalid value for " + vrbl.getType().name() + " type column " + pparts[i].getParameter().getCode() + '.' + pp.getName()); } if ((!boolValue) && "1".equals(val)) boolValue = true; IntPartValue ipv = new IntPartValue(pp); ipv.setIntValue(boolValue ? 1 : 0); rc.addPartValue(ipv); } else throw new ParseException(ln, "Invalid value for " + vrbl.getType().name() + " type column " + pparts[i].getParameter().getCode() + '.' + pp.getName()); } } } else { PartValue pv = new EmptyPartValue(pp); rc.addPartValue(pv); } } } rcs.add(rc); } cpos = pos + lineSep.length(); } Connection conn = null; ResultSet rst = null; try { conn = dSrc.getConnection(); PreparedStatement recstmt = conn.prepareStatement("INSERT INTO " + TBL_RECORD + " (" + FLD_COLLECTION_RECORD_ID + "," + FLD_COUNT + "," + FLD_COLLECTION_ID + ") VALUES (?,1,?)", Statement.RETURN_GENERATED_KEYS); PreparedStatement contstmt = conn .prepareStatement("INSERT INTO " + TBL_RECORD_CONTENT + " (" + FLD_INT_VALUE + "," + FLD_REAL_VALUE + "," + FLD_RECORD_ID + "," + FLD_PART_ID + ") VALUES (?,?,?,?)"); PreparedStatement updcontstmt = conn .prepareStatement("UPDATE " + TBL_RECORD_CONTENT + " SET " + FLD_INT_VALUE + "=?," + FLD_REAL_VALUE + "=? WHERE " + FLD_RECORD_ID + "=? AND " + FLD_PART_ID + "=?"); PreparedStatement rmcontstmt = conn.prepareStatement("DELETE FROM " + TBL_RECORD_CONTENT + " WHERE " + FLD_RECORD_ID + "=? AND " + FLD_PART_ID + "=?"); PreparedStatement insvarstmt = conn .prepareStatement( "INSERT INTO " + TBL_VARIANT + " (" + FLD_PART_ID + ',' + FLD_NAME + ',' + FLD_VARI_CODING + ',' + FLD_PREDEFINED + ") VALUES (?,?,0,0)", Statement.RETURN_GENERATED_KEYS); PreparedStatement delRecStmt = null; PreparedStatement delRecContStmt = null; for (Record r : rcs) { Record exR = findRecord(r); int rid = 0; if (exR == null) { if (r.getPartValues().size() > 0) { recstmt.setString(1, r.getCollectionRecordIDs()); recstmt.setInt(2, collectionID); recstmt.executeUpdate(); rst = recstmt.getGeneratedKeys(); if (rst.next()) rid = rst.getInt(1); r.setId(rid); data.add(r); } } else { rid = exR.getId(); if (r.getPartValues().size() == 0) { if (delRecStmt == null) { delRecStmt = conn .prepareStatement("DELETE FROM " + TBL_RECORD + " WHERE " + FLD_ID + "=?"); delRecContStmt = conn.prepareStatement( "DELETE FROM " + TBL_RECORD_CONTENT + " WHERE " + FLD_RECORD_ID + "=?"); } delRecStmt.setInt(1, rid); delRecStmt.executeUpdate(); delRecContStmt.setInt(1, rid); delRecContStmt.executeUpdate(); data.remove(exR); for (PartValue pv : exR.getPartValues()) { if (pv instanceof VariantPartValue) pv.getPart().uncountVariantByIndex(((VariantPartValue) pv).getVariant()); } continue; } } for (PartValue pv : r.getPartValues()) { PartValue opv = exR != null ? exR.getPartValue(pv.getPartID()) : null; if (pv instanceof EmptyPartValue) { if (opv != null) { exR.removePartValue(opv); rmcontstmt.setInt(1, rid); rmcontstmt.setInt(2, pv.getPartID()); rmcontstmt.executeUpdate(); if (opv instanceof VariantPartValue) opv.getPart().uncountVariantByIndex(((VariantPartValue) opv).getVariant()); if (exR.getPartValues().size() == 0) { if (delRecStmt == null) { delRecStmt = conn.prepareStatement( "DELETE FROM " + TBL_RECORD + " WHERE " + FLD_ID + "=?"); delRecContStmt = conn.prepareStatement( "DELETE FROM " + TBL_RECORD_CONTENT + " WHERE " + FLD_RECORD_ID + "=?"); } delRecStmt.setInt(1, exR.getId()); delRecStmt.executeUpdate(); delRecContStmt.setInt(1, exR.getId()); delRecContStmt.executeUpdate(); data.remove(exR); exR = null; } } } else { PreparedStatement stmt = null; if (opv != null) stmt = updcontstmt; else stmt = contstmt; stmt.setInt(3, rid); stmt.setInt(4, pv.getPartID()); if (pv instanceof IntPartValue) { stmt.setInt(1, ((IntPartValue) pv).getIntValue()); stmt.setNull(2, java.sql.Types.FLOAT); } else if (pv instanceof RealPartValue) { stmt.setNull(1, java.sql.Types.INTEGER); stmt.setFloat(2, ((RealPartValue) pv).getRealValue()); } else if (pv instanceof VariantPartValue) { short vidx = ((VariantPartValue) pv).getVariant(); Variant tv = null; if (vidx < 0) { List<Variant> tmpVaris = tmpVarisMap.get(pv.getPart()); tv = tmpVaris.get((-vidx) - 1); if (tv.getId() == 0) { insvarstmt.setInt(1, pv.getPartID()); insvarstmt.setString(2, tv.getName()); insvarstmt.executeUpdate(); ResultSet vrst = insvarstmt.getGeneratedKeys(); vrst.next(); tv.setId(vrst.getInt(1)); vrst.close(); short nidx = pv.getPart().addVariant(tv); tv.setCoding(nidx); ((VariantPartValue) pv).setVariant(nidx); } else ((VariantPartValue) pv).setVariant((short) tv.getCoding()); } else { tv = pv.getPart().getVariant(vidx); tv.incCount(); } stmt.setInt(1, tv.getId()); stmt.setNull(2, java.sql.Types.FLOAT); } else { stmt.setNull(1, java.sql.Types.INTEGER); stmt.setNull(2, java.sql.Types.FLOAT); } stmt.executeUpdate(); if (opv != null) exR.removePartValue(opv); if (exR != null) exR.addPartValue(pv); } } if (exR != null) exR.completeRecord(); else r.completeRecord(); } recstmt.close(); contstmt.close(); updcontstmt.close(); rmcontstmt.close(); insvarstmt.close(); for (List<Variant> tvl : tmpVarisMap.values()) for (Variant tv : tvl) tv.setCoding(0); Statement stmt = conn.createStatement(); stmt.executeUpdate("UPDATE " + TBL_COLLECTION + " SET " + FLD_LAST_UPDATE + "=" + System.currentTimeMillis() + " WHERE " + FLD_ID + "=" + collectionID); stmt.close(); Collections.sort(data, RecordComparator.getIntstance()); prepareCounts(); // TODO count only parameter with new data } catch (SQLException e) { Log.error("SQL error", e); throw new ParseException(0, "SQL error"); } catch (Exception e1) { logger.error("Data import error", e1); throw new ParseException(0, "Unknown error: " + e1.getMessage()); } 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); } } }
From source file:uk.ac.ebi.sail.server.data.DataManager.java
public String upgradeDB_20090720() { StringBuilder log = new StringBuilder(); Connection conn = null;// ww w. j av a2s . com ResultSet rst = null; try { conn = dSrc.getConnection(); Statement stmt = conn.createStatement(); // stmt.executeUpdate("UPDATE "+TBL_VARIANT+" SET "+FLD_PREDEFINED+"=1"); // log.append("Predefined flag is set for variants").append(logEOL); stmt.executeUpdate("DELETE FROM " + TBL_VARIANT + " WHERE " + FLD_NAME + "='@'"); log.append("Anonymous variants have been removed").append(logEOL); rst = stmt.executeQuery("SELECT * FROM " + TBL_VARIANT + " ORDER BY " + FLD_PART_ID); IntMap<PartVariSet> variMap = new IntTreeMap<PartVariSet>(); List<Var> varis = new ArrayList<Var>(5); PartVariSet partLst = null; while (rst.next()) { Var v = new Var(); int ptid = rst.getInt(FLD_PART_ID); v.setId(rst.getInt(FLD_ID)); v.setPtid(ptid); v.setCod(rst.getInt(FLD_VARI_CODING)); v.setName(rst.getString(FLD_NAME)); log.append("----- Loading variant (ID,Part,Name,Code) (" + v.getId() + ',' + ptid + "," + v.getName() + ',' + v.getCod() + ") -----").append(logEOL); partLst = variMap.get(ptid); if (partLst == null) { partLst = new PartVariSet(); variMap.put(ptid, partLst); } partLst.add(v); int cptid = 0; if (varis.size() > 0) cptid = varis.get(0).getPtid(); if (varis.size() == 0 || cptid == ptid) { varis.add(v); } else { partLst.setMax(updateBlock(conn, varis)); varis.clear(); varis.add(v); log.append("Variants for Part=" + cptid + " have been precessed. Max code=" + partLst.getMax()) .append(logEOL); } } rst.close(); if (varis.size() > 0) { partLst.setMax(updateBlock(conn, varis)); log.append("Variants for Part=" + varis.get(0).getPtid() + " have been precessed. Max code=" + partLst.getMax()).append(logEOL); } rst = stmt.executeQuery("SELECT * FROM " + TBL_RECORD_CONTENT + " ORDER BY " + FLD_PART_ID); ResultSet trst = null; PreparedStatement selPartType = conn .prepareStatement("SELECT " + FLD_TYPE + " FROM " + TBL_PART + " WHERE " + FLD_ID + "=?"); PreparedStatement updPartVal = conn.prepareStatement("UPDATE " + TBL_RECORD_CONTENT + " SET " + FLD_INT_VALUE + "=? WHERE " + FLD_PART_ID + "=? AND " + FLD_RECORD_ID + "=?"); PreparedStatement insVariant = conn .prepareStatement( "INSERT INTO " + TBL_VARIANT + " (" + FLD_PART_ID + ',' + FLD_NAME + ',' + FLD_VARI_CODING + ',' + FLD_PREDEFINED + ") VALUES (?,?,?,0)", Statement.RETURN_GENERATED_KEYS); int cptID = -1; String partType = null; while (rst.next()) { int ptID = rst.getInt(FLD_PART_ID); if (cptID != ptID) { selPartType.setInt(1, ptID); trst = selPartType.executeQuery(); if (!trst.next()) { log.append("Can't find part with ID=" + ptID + " in database").append(logEOL); return log.toString(); } partType = trst.getString(1); trst.close(); } if (!("QUALIFIER".equals(partType) || "ENUM".equals(partType))) continue; String value = rst.getString(FLD_ENUM_VALUE); log.append("Processing value for Part=" + ptID + " Value=" + value).append(logEOL); if (ParameterPart.SECURED_VARIANT_SIGN.equals(value)) { updPartVal.setInt(1, 0); updPartVal.setInt(2, ptID); updPartVal.setInt(3, rst.getInt(FLD_RECORD_ID)); updPartVal.executeUpdate(); log.append("Part value updated. RecordID=" + rst.getInt(FLD_RECORD_ID) + " Part=" + ptID + " Variant=0").append(logEOL); continue; } partLst = variMap.get(ptID); if (partLst == null) { partLst = new PartVariSet(); partLst.setMax(0); variMap.put(ptID, partLst); } Var matchV = null; for (Var v : partLst) { if (v.getName().equals(value)) { matchV = v; break; } } if (matchV == null) { log.append("There is no matching variant. Part=" + ptID + " Value=" + value).append(logEOL); int ncod = partLst.getNextCoding(); insVariant.setInt(1, ptID); insVariant.setString(2, value); insVariant.setInt(3, ncod); insVariant.executeUpdate(); ResultSet krst = insVariant.getGeneratedKeys(); if (!krst.next()) { log.append("Can't get generated key. Variant name' " + value + "' PartID=" + ptID) .append(logEOL); return log.toString(); } matchV = new Var(); matchV.setId(krst.getInt(1)); matchV.setCod(ncod); matchV.setName(value); matchV.setPtid(ptID); krst.close(); partLst.add(matchV); log.append("New variant added. ID=" + matchV.getId() + " Part=" + ptID + " Value=" + value + " Code=" + matchV.getCod()).append(logEOL); } updPartVal.setInt(1, matchV.getId()); updPartVal.setInt(2, ptID); updPartVal.setInt(3, rst.getInt(FLD_RECORD_ID)); updPartVal.executeUpdate(); log.append("Part value updated. RecordID=" + rst.getInt(FLD_RECORD_ID) + " Part=" + ptID + " Variant=" + matchV.getId()).append(logEOL); } } catch (SQLException e) { log.append("SQL error" + e).append(logEOL); } 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 log.toString(); }
From source file:uk.ac.ebi.sail.server.data.DataManager.java
public ClassifierShadow updateClassifier(ClassifierShadow scl) throws ClassifierManagementException { int id = scl.getId(); Classifier orig = classifiers.get(id); if (orig == null) throw new ClassifierManagementException("Classifier doesn't exist ID=" + id, ClassifierManagementException.INV_CLASSIFIER_ID); SetComparator<Tag> res = SetComparator.compare(orig.getTags(), scl.getTags()); Connection conn = null;//www.jav a 2s .co m ResultSet rst = null; try { conn = dSrc.getConnection(); PreparedStatement pstmt = conn.prepareStatement(updateClassifierSQL); pstmt.setString(1, scl.getName()); pstmt.setString(2, scl.getDesc()); pstmt.setBoolean(3, scl.isAllowMulty()); pstmt.setBoolean(4, scl.isMandatory()); pstmt.setString(5, scl.getTarget().name()); pstmt.setInt(6, id); pstmt.executeUpdate(); pstmt.close(); if (res.getItemsToDelete() != null) { pstmt = conn.prepareStatement(deleteTagSQL); PreparedStatement delParamTag = conn.prepareStatement( "DELETE FROM " + TBL_PARAMETER_CLASSIFICATION + " WHERE " + FLD_TAG_ID + "=?"); PreparedStatement delClsTag = conn.prepareStatement( "DELETE FROM " + TBL_CLASSIFIER_CLASSIFICATION + " WHERE " + FLD_TAG_ID + "=?"); PreparedStatement delRelTag = conn .prepareStatement("DELETE FROM " + TBL_RELATION + " WHERE " + FLD_TAG_ID + "=?"); PreparedStatement delRepAnntTag = conn.prepareStatement( "DELETE FROM " + TBL_COLLECTION_ANNOTATION + " WHERE " + FLD_TAG_ID + "=?"); PreparedStatement delParamAnntTag = conn.prepareStatement( "DELETE FROM " + TBL_PARAMETER_ANNOTATION + " WHERE " + FLD_TAG_ID + "=?"); for (Tag t : res.getItemsToDelete()) { pstmt.setInt(1, t.getId()); pstmt.executeUpdate(); delParamTag.setInt(1, t.getId()); delParamTag.executeUpdate(); delClsTag.setInt(1, t.getId()); delClsTag.executeUpdate(); delRelTag.setInt(1, t.getId()); delRelTag.executeUpdate(); delRepAnntTag.setInt(1, t.getId()); delRepAnntTag.executeUpdate(); delParamAnntTag.setInt(1, t.getId()); delParamAnntTag.executeUpdate(); tags.remove(t.getId()); } delParamTag.close(); delClsTag.close(); delRelTag.close(); delRepAnntTag.close(); pstmt.close(); } if (res.getNewItems() != null) { pstmt = conn.prepareStatement(insertTagSQL, Statement.RETURN_GENERATED_KEYS); for (Tag t : res.getNewItems()) { pstmt.setString(1, t.getName()); pstmt.setString(2, t.getDescription()); pstmt.setInt(3, id); pstmt.executeUpdate(); rst = pstmt.getGeneratedKeys(); if (rst.next()) t.setId(rst.getInt(1)); rst.close(); tags.put(t.getId(), t); } pstmt.close(); } if (res.getUpdateItems() != null && res.getUpdateItems().size() > 0) { pstmt = conn.prepareStatement(updateTagSQL); for (Tag t : res.getUpdateItems()) { pstmt.setString(1, t.getName()); pstmt.setString(2, t.getDescription()); pstmt.setInt(3, t.getId()); pstmt.executeUpdate(); } pstmt.close(); } pstmt = conn.prepareStatement(deleteAllClassifierClassificationSQL); pstmt.setInt(1, id); pstmt.executeUpdate(); pstmt.close(); if (scl.getClassificationTags() != null) // TODO check tag existance { pstmt = conn.prepareStatement(insertClassifierClassificationSQL); for (int tid : scl.getClassificationTags()) { pstmt.setInt(1, id); pstmt.setInt(2, tid); pstmt.executeUpdate(); } } if (res.getItemsToDelete() != null) updateStructure(); else { orig.setName(scl.getName()); orig.setDescription(scl.getDesc()); if (orig.getTags() != null) { for (Tag ot : orig.getTags()) { for (Tag nt : scl.getTags()) { if (ot.getId() == nt.getId()) { ot.setName(nt.getName()); ot.setDescription(nt.getDescription()); break; } } } } if (res.getNewItems() != null) { for (Tag t : res.getNewItems()) orig.addTag(t); } orig.setClassificationTags(null); if (scl.getClassificationTags() != null) { for (int tid : scl.getClassificationTags()) { Tag t = tags.get(tid); if (t == null) { logger.warn("Invalid tag ID=" + tid); } else orig.addTag(t); } } } return scl; } 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; }