Package org.springframework.jdbc.core.namedparam

Examples of org.springframework.jdbc.core.namedparam.MapSqlParameterSource


  public long save(Sample sample) throws IOException {
    Long securityProfileId = sample.getSecurityProfile().getProfileId();
    if (this.cascadeType != null){// && this.cascadeType.equals(CascadeType.PERSIST) || this.cascadeType.equals(CascadeType.REMOVE)) {
      securityProfileId = securityProfileDAO.save(sample.getSecurityProfile());
    }
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("alias", sample.getAlias())
            .addValue("accession", sample.getAccession())
            .addValue("description", sample.getDescription())
            .addValue("scientificName", sample.getScientificName())
            .addValue("taxonIdentifier", sample.getTaxonIdentifier())
            //.addValue("identificationBarcode", sample.getIdentificationBarcode())
            .addValue("locationBarcode", sample.getLocationBarcode())
            .addValue("sampleType", sample.getSampleType())
            .addValue("receivedDate", sample.getReceivedDate())
            .addValue("project_projectId", sample.getProject().getProjectId())
            .addValue("securityProfile_profileId", securityProfileId);

    if (sample.getQcPassed() != null) {
      params.addValue("qcPassed", sample.getQcPassed().toString());
    }
    else {
      params.addValue("qcPassed", sample.getQcPassed());
    }

    if (sample.getId() == AbstractSample.UNSAVED_ID) {
      if (!listByAlias(sample.getAlias()).isEmpty()) {
        throw new IOException("NEW: A sample with this alias already exists in the database");
      }
      else {
        SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                                .withTableName(TABLE_NAME)
                                .usingGeneratedKeyColumns("sampleId");
        try {
          sample.setId(DbUtils.getAutoIncrement(template, TABLE_NAME));

          String name = sampleNamingScheme.generateNameFor("name", sample);
          sample.setName(name);

          if (sampleNamingScheme.validateField("name", sample.getName()) && sampleNamingScheme.validateField("alias", sample.getAlias())) {
            String barcode = name + "::" + sample.getAlias();
            params.addValue("name", name);

            params.addValue("identificationBarcode", barcode);

            Number newId = insert.executeAndReturnKey(params);
            if (newId.longValue() != sample.getId()) {
              log.error("Expected Sample ID doesn't match returned value from database insert: rolling back...");
              new NamedParameterJdbcTemplate(template).update(SAMPLE_DELETE, new MapSqlParameterSource().addValue("sampleId", newId.longValue()));
              throw new IOException("Something bad happened. Expected Sample ID doesn't match returned value from DB insert");
            }
          }
          else {
            throw new IOException("Cannot save sample - invalid field:" + sample.toString());
          }
        }
        catch (MisoNamingException e) {
          throw new IOException("Cannot save sample - issue with naming scheme", e);
        }
      }
    }
    else {
      SqlRowSet ss = template.queryForRowSet(SAMPLE_SELECT_BY_ALIAS, new Object[]{sample.getAlias()});
      if (ss.next() && ss.getLong("sampleId") != sample.getId()) {
        throw new IOException("UPD: A sample with this alias already exists in the database");
      }
      else {
        try {
          if (sampleNamingScheme.validateField("name", sample.getName()) && sampleNamingScheme.validateField("alias", sample.getAlias())) {
            params.addValue("sampleId", sample.getId())
                  .addValue("name", sample.getName())
                  .addValue("identificationBarcode", sample.getName() + "::" + sample.getAlias());
            NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
            namedTemplate.update(SAMPLE_UPDATE, params);
          }
View Full Code Here


  )
  public boolean remove(Sample sample) throws IOException {
    NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
    if (sample.isDeletable() &&
           (namedTemplate.update(SAMPLE_DELETE,
                                 new MapSqlParameterSource().addValue("sampleId", sample.getId())) == 1)) {
      Project p = sample.getProject();
      if (this.cascadeType.equals(CascadeType.PERSIST)) {
        if (p!=null) projectDAO.save(p);
      }
      else if (this.cascadeType.equals(CascadeType.REMOVE)) {
View Full Code Here

  @Transactional(readOnly = false, rollbackFor = IOException.class)
  public long save(Submission submission) throws IOException {
    SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
            .withTableName("Submission");

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("alias", submission.getAlias())
            .addValue("accession", submission.getAccession())
            .addValue("description", submission.getDescription())
            .addValue("title", submission.getTitle())
            .addValue("creationDate", submission.getCreationDate())
            .addValue("submittedDate", submission.getSubmissionDate())
            .addValue("verified", submission.isVerified())
            .addValue("completed", submission.isCompleted());

    //if a submission already exists then delete all the old rows first, and repopulate.
    //easier than trying to work out which rows need to be updated and which don't
    if (submission.getId() != Submission.UNSAVED_ID) {
      try {
        if (namingScheme.validateField("name", submission.getName())) {
          MapSqlParameterSource delparams = new MapSqlParameterSource();
          delparams.addValue("submissionId", submission.getId());
          NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
          log.debug("Deleting Submission elements for " + submission.getId());
          namedTemplate.update(SUBMISSION_ELEMENTS_DELETE, delparams);

          params.addValue("submissionId", submission.getId())
                .addValue("name", submission.getName());
          namedTemplate.update(SUBMISSION_UPDATE, params);
        }
        else {
          throw new IOException("Cannot save Submission - invalid field:" + submission.toString());
        }
      }
      catch (MisoNamingException e) {
        throw new IOException("Cannot save Submission - issue with naming scheme", e);
      }
      /*
      params.addValue("submissionId", submission.getSubmissionId())
              .addValue("name", submission.getName());
      namedTemplate.update(SUBMISSION_UPDATE, params);
      */
    }
    else {
      insert.usingGeneratedKeyColumns("submissionId");
      try {
        submission.setId(DbUtils.getAutoIncrement(template, TABLE_NAME));

        String name = namingScheme.generateNameFor("name", submission);
        submission.setName(name);

        if (namingScheme.validateField("name", submission.getName())) {
          params.addValue("name", name)
                .addValue("creationDate", new Date());

          Number newId = insert.executeAndReturnKey(params);
          if (newId.longValue() != submission.getId()) {
            log.error("Expected Submission ID doesn't match returned value from database insert: rolling back...");
            new NamedParameterJdbcTemplate(template).update(SUBMISSION_DELETE, new MapSqlParameterSource().addValue("submissionId", newId.longValue()));
            throw new IOException("Something bad happened. Expected Submission ID doesn't match returned value from DB insert");
          }
        }
        else {
          throw new IOException("Cannot save Submission - invalid field:" + submission.toString());
        }
      }
      catch (MisoNamingException e) {
        throw new IOException("Cannot save Submission - issue with naming scheme", e);
      }
      /*
      String name = "SUB" + DbUtils.getAutoIncrement(template, TABLE_NAME);
      params.addValue("creationDate", new Date());
      params.addValue("name", name);
      Number newId = insert.executeAndReturnKey(params);
      submission.setSubmissionId(newId.longValue());
      submission.setName(name);
      */
    }

    if (submission.getSubmissionElements() != null) {
      Collection<Submittable<Document>> docs = submission.getSubmissionElements();
      for (Submittable s : docs) {
        String tableName = "Submission_";
        String priKey = null;
        Long priValue = null;
        boolean process = true;

        if (s instanceof Sample) {
          tableName += "Sample";
          priKey = "samples_sampleId";
          priValue = ((Sample) s).getId();
        }
        else if (s instanceof Study) {
          tableName += "Study";
          priKey = "studies_studyId";
          priValue = ((Study) s).getId();
        }
        else if (s instanceof Experiment) {
          tableName += "Experiment";
          priKey = "experiments_experimentId";
          priValue = ((Experiment) s).getId();
        }
        else if (s instanceof SequencerPoolPartition) {
          SequencerPoolPartition l = (SequencerPoolPartition) s;
          tableName += "Partition_Dilution";
          priKey = "partitions_partitionId";
          priValue = l.getId();
          process = false;

          if (l.getPool() != null) {
            Collection<Experiment> exps = l.getPool().getExperiments();
            for (Experiment experiment : exps) {
              SimpleJdbcInsert pInsert = new SimpleJdbcInsert(template)
                      .withTableName("Submission_Experiment");
              try {
                MapSqlParameterSource poParams = new MapSqlParameterSource();
                poParams.addValue("submission_submissionId", submission.getId())
                        .addValue("experiments_experimentId", experiment.getId());
                pInsert.execute(poParams);
              }
              catch (DuplicateKeyException dke) {
                log.warn("This Submission_Experiment combination already exists - not inserting: " + dke.getMessage());
              }

              Study study = experiment.getStudy();
              SimpleJdbcInsert sInsert = new SimpleJdbcInsert(template)
                      .withTableName("Submission_Study");
              try {
                MapSqlParameterSource poParams = new MapSqlParameterSource();
                poParams.addValue("submission_submissionId", submission.getId())
                        .addValue("studies_studyId", study.getId());
                sInsert.execute(poParams);
              }
              catch (DuplicateKeyException dke) {
                log.warn("This Submission_Study combination already exists - not inserting: " + dke.getMessage());
              }
            }

            Collection<? extends Dilution> dils = l.getPool().getDilutions();
            for (Dilution dil : dils) {
              Sample sample = dil.getLibrary().getSample();
              SimpleJdbcInsert sInsert = new SimpleJdbcInsert(template)
                      .withTableName("Submission_Sample");
              try {
                MapSqlParameterSource poParams = new MapSqlParameterSource();
                poParams.addValue("submission_submissionId", submission.getId())
                        .addValue("samples_sampleId", sample.getId());
                sInsert.execute(poParams);
              }
              catch (DuplicateKeyException dke) {
                log.warn("This Submission_Sample combination already exists - not inserting: " + dke.getMessage());
              }

              //Adds Submission_Partition_Dilution info to DB table.

              sInsert = new SimpleJdbcInsert(template).withTableName("Submission_Partition_Dilution");
              try {
                MapSqlParameterSource poParams = new MapSqlParameterSource();
                poParams.addValue("submission_submissionId", submission.getId())
                        .addValue("partition_partitionId", l.getId())
                        .addValue("dilution_dilutionId", dil.getId());
                sInsert.execute(poParams);

              }
              catch (DuplicateKeyException dke) {
                log.warn("This Submission_Partition_Dilution combination already exists - not inserting: " + dke.getMessage());
              }
            }
          }
        }

        if (process) {
          if (priKey != null && priValue != null) {
            SimpleJdbcInsert pInsert = new SimpleJdbcInsert(template)
                    .withTableName(tableName);
            try {
              MapSqlParameterSource poParams = new MapSqlParameterSource();
              poParams.addValue("submission_submissionId", submission.getId())
                      .addValue(priKey, priValue);
              pInsert.execute(poParams);
            }
            catch (DuplicateKeyException dke) {
              log.warn("This " + tableName + " combination already exists - not inserting: " + dke.getMessage());
View Full Code Here

  }

  @Override
  public List<Plate<? extends List<? extends Plateable>, ? extends Plateable>> listBySearch(String query) throws IOException {
    String squery = "%" + query + "%";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("search", squery);
    NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
    return namedTemplate.query(PLATE_SELECT_BY_SEARCH, params, new PlateMapper(true));
  }
View Full Code Here

    if (securityProfileId == SecurityProfile.UNSAVED_ID  ||
        (this.cascadeType != null)) { // && this.cascadeType.equals(CascadeType.PERSIST))) {
      securityProfileId = securityProfileDAO.save(plate.getSecurityProfile());
    }

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("description", plate.getDescription())
          .addValue("creationDate", plate.getCreationDate())
          .addValue("plateMaterialType", plate.getPlateMaterialType().getKey())
          .addValue("locationBarcode", plate.getLocationBarcode())
          .addValue("size", plate.getSize())
          .addValue("securityProfile_profileId", securityProfileId);

    if (plate.getTagBarcode() != null) {
      params.addValue("tagBarcodeId", plate.getTagBarcode().getId());
    }

    if (plate.getId() == AbstractPlate.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                            .withTableName(TABLE_NAME)
                            .usingGeneratedKeyColumns("plateId");
      try {
        plate.setId(DbUtils.getAutoIncrement(template, TABLE_NAME));

        String name = namingScheme.generateNameFor("name", plate);
        plate.setName(name);

        if (namingScheme.validateField("name", plate.getName())) {
          String barcode = "";
          if (plate.getTagBarcode() != null) {
            barcode = plate.getName() + "::" + plate.getTagBarcode();
          }
          else {
            //TODO this should be alias
            barcode = plate.getName() + "::" + plate.getDescription();
          }         
          params.addValue("name", name);

          params.addValue("identificationBarcode", barcode);

          Number newId = insert.executeAndReturnKey(params);
          if (newId.longValue() != plate.getId()) {
            log.error("Expected Plate ID doesn't match returned value from database insert: rolling back...");
            new NamedParameterJdbcTemplate(template).update(PLATE_DELETE, new MapSqlParameterSource().addValue("plateId", newId.longValue()));
            throw new IOException("Something bad happened. Expected Plate ID doesn't match returned value from DB insert");
          }
        }
        else {
          throw new IOException("Cannot save Plate - invalid field:" + plate.toString());
        }
      }
      catch (MisoNamingException e) {
        throw new IOException("Cannot save Plate - issue with naming scheme", e);
      }
      /*
      String name = "PLA"+ DbUtils.getAutoIncrement(template, TABLE_NAME);
      params.addValue("name", name);
      params.addValue("identificationBarcode", name + "::" + plate.getTagBarcode());
      Number newId = insert.executeAndReturnKey(params);
      plate.setPlateId(newId.longValue());
      plate.setName(name);
      */
    }
    else {
      try {
        String plateBarcode = "";
        if (plate.getTagBarcode() != null) {
          plateBarcode = plate.getName() + "::" + plate.getTagBarcode();
        }
        else {
          //TODO this should be alias
          plateBarcode = plate.getName() + "::" + plate.getDescription();
        }
        if (namingScheme.validateField("name", plate.getName())) {
          params.addValue("plateId", plate.getId())
                .addValue("name", plate.getName())
                .addValue("identificationBarcode", plateBarcode);
          NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
          namedTemplate.update(PLATE_UPDATE, params);
        }
        else {
          throw new IOException("Cannot save Plate - invalid field:" + plate.toString());
        }
      }
      catch (MisoNamingException e) {
        throw new IOException("Cannot save Plate - issue with naming scheme", e);
      }
      /*
      params.addValue("plateId", plate.getPlateId());
      params.addValue("name", plate.getName());
      params.addValue("identificationBarcode", plate.getName() + "::" + plate.getTagBarcode());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(PLATE_UPDATE, params);
      */
    }

    if (this.cascadeType != null && this.cascadeType.equals(CascadeType.PERSIST)) {
      if (!plate.getElements().isEmpty()) {
        String eType = plate.getElementType().getName();
        MapSqlParameterSource eparams = new MapSqlParameterSource();
        eparams.addValue("plate_plateId", plate.getId());
        NamedParameterJdbcTemplate nt = new NamedParameterJdbcTemplate(template);
        nt.update(PLATE_ELEMENT_DELETE_BY_PLATE_ID, eparams);

        SimpleJdbcInsert eInsert = new SimpleJdbcInsert(template)
                .withTableName("Plate_Elements");

        int pos = 1;
        for (Plateable n : plate.getElements()) {
          if (n.getId() == 0) {
            Store<? super Plateable> dao = daoLookup.lookup(n.getClass());
            if (dao != null) {
              dao.save(n);
            }
            else {
              log.error("No dao class found for " + n.getClass().getName());
            }
          }
          MapSqlParameterSource ltParams = new MapSqlParameterSource();
          ltParams.addValue("plate_plateId", plate.getId())
                  .addValue("elementType", eType)
                  .addValue("elementPosition", pos)
                  .addValue("elementId", n.getId());

          eInsert.execute(ltParams);
View Full Code Here

  )
  public boolean remove(Plate plate) throws IOException {
    NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
    if (plate.isDeletable() &&
           (namedTemplate.update(PLATE_DELETE,
                                 new MapSqlParameterSource().addValue("plateId", plate.getId())) == 1)) {
      MapSqlParameterSource eparams = new MapSqlParameterSource();
      eparams.addValue("plate_plateId", plate.getId());
      namedTemplate.update(PLATE_ELEMENT_DELETE_BY_PLATE_ID, eparams);
      return true;
    }
    return false;
  }
View Full Code Here

  public List<Kit> listKitsByType(KitType kitType) throws IOException {
    return template.query(KITS_SELECT_BY_TYPE, new Object[]{kitType.getKey()}, new KitMapper());
  }

  public long save(Kit kit) throws IOException {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("identificationBarcode", kit.getIdentificationBarcode())
            .addValue("locationBarcode", kit.getLocationBarcode())
            .addValue("lotNumber", kit.getLotNumber())
            .addValue("kitDate", kit.getKitDate())
            .addValue("kitDescriptorId", kit.getKitDescriptor().getKitDescriptorId());

    if (kit.getId() == AbstractKit.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                            .withTableName(TABLE_NAME)
                            .usingGeneratedKeyColumns("kitId");
      Number newId = insert.executeAndReturnKey(params);
      kit.setId(newId.longValue());
    }
    else {
      params.addValue("kitId", kit.getId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(KIT_UPDATE, params);
    }

    if (this.cascadeType != null && this.cascadeType.equals(CascadeType.PERSIST)) {
View Full Code Here

    return template.query(KIT_DESCRIPTORS_SELECT_BY_PLATFORM, new Object[]{platformType}, new KitDescriptorMapper());
  }
 
  public long saveKitDescriptor(KitDescriptor kd) throws IOException {
    //log.info("Saving " + kd.toString() + " : " + kd.getKitType() + " : " + kd.getPlatformType());
    MapSqlParameterSource params = new MapSqlParameterSource();

    params.addValue("name", kd.getName())
            .addValue("version", kd.getVersion())
            .addValue("manufacturer", kd.getManufacturer())
            .addValue("partNumber", kd.getPartNumber())
            .addValue("stockLevel", kd.getStockLevel())
            .addValue("kitType", kd.getKitType().getKey())
            .addValue("platformType", kd.getPlatformType().getKey());

    if (kd.getKitDescriptorId() == KitDescriptor.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                            .withTableName("KitDescriptor")
                            .usingGeneratedKeyColumns("kitDescriptorId");
      Number newId = insert.executeAndReturnKey(params);
      kd.setKitDescriptorId(newId.longValue());
    }
    else {
      params.addValue("kitDescriptorId", kd.getKitDescriptorId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(KIT_DESCRIPTOR_UPDATE, params);
    }

    return kd.getKitDescriptorId();
View Full Code Here

    this.template = template;
  }

  @Override
  public long save(MisoPrintService printService) throws IOException {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("serviceName", printService.getName())
        .addValue("contextName", printService.getPrintContext().getName())
        .addValue("enabled", printService.isEnabled())
        .addValue("printServiceFor", printService.getPrintServiceFor().getName())
        .addValue("printSchema", printService.getBarcodableSchema().getName());
    try {
      JSONObject contextFields = PrintServiceUtils.mapContextFieldsToJSON(printService.getPrintContext());
      String contextFieldJSON = contextFields.toString();
      params.addValue("contextFields", contextFieldJSON);
    }
    catch (IllegalAccessException e) {
      e.printStackTrace();
    }

    if (printService.getServiceId() == -1) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
          .withTableName(TABLE_NAME)
          .usingGeneratedKeyColumns("serviceId");
      Number newId = insert.executeAndReturnKey(params);
      printService.setServiceId(newId.longValue());
    }
    else {
      params.addValue("serviceId", printService.getServiceId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(PRINT_SERVICE_UPDATE, params);
    }
    return printService.getServiceId();
  }
View Full Code Here

  public void setJdbcTemplate(JdbcTemplate template) {
    this.template = template;
  }

  public long save(Status status) throws IOException {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("runName", status.getRunName())
            .addValue("health", status.getHealth().getKey())
            .addValue("startDate", status.getStartDate())
            .addValue("completionDate", status.getCompletionDate())
            .addValue("instrumentName", status.getInstrumentName());

    Blob xmlblob = null;
    try {
      if (status.getXml() != null) {
        byte[] rbytes = status.getXml().getBytes();
        xmlblob = new SerialBlob(rbytes);
        params.addValue("xml", xmlblob);
      }
      else {
        params.addValue("xml", null);
      }
    }
    catch (SerialException e) {
      e.printStackTrace();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }

    if (status.getStatusId() == 0L) {
      Status savedStatus = getByRunName(status.getRunName());
      if (savedStatus == null) {
        SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                .withTableName(TABLE_NAME)
                .usingGeneratedKeyColumns("statusId");

        if (status.getHealth().equals(HealthType.Running) && status.getStartDate() == null) {
          //run freshly started
          params.addValue("startDate", new Date());
        }

        Number newId = insert.executeAndReturnKey(params);
        status.setStatusId(newId.longValue());
      }
      else {
        status.setStatusId(savedStatus.getStatusId());
        params.addValue("statusId", status.getStatusId());
        NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
        namedTemplate.update(STATUS_UPDATE, params);       
      }
    }
    else {
      params.addValue("statusId", status.getStatusId());
      params.addValue("startDate", new SimpleDateFormat("yyyy-MM-dd").format(status.getStartDate()));
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(STATUS_UPDATE, params);
    }

    return status.getStatusId();
View Full Code Here

TOP

Related Classes of org.springframework.jdbc.core.namedparam.MapSqlParameterSource

Copyright © 2018 www.massapicom. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.