Examples of org.springframework.jdbc.core.simple.SimpleJdbcInsert

Package org.springframework.jdbc.core.simple

Examples of org.springframework.jdbc.core.simple.SimpleJdbcInsert

org.springframework.jdbc.core.simple.SimpleJdbcInsert
A SimpleJdbcInsert is a multi-threaded, reusable object providing easy insert capabilities for a table. It provides meta data processing to simplify the code needed to construct a basic insert statement. All you need to provide is the name of the table and a Map containing the column names and the column values.
The meta data processing is based on the DatabaseMetaData provided by the JDBC driver. As long as the JBDC driver can provide the names of the columns for a specifed table than we can rely on this auto-detection feature. If that is not the case then the column names must be specified explicitly.
The actual insert is being handled using Spring's {@link org.springframework.jdbc.core.JdbcTemplate}.
Many of the configuration methods return the current instance of the SimpleJdbcInsert to provide the ablity to string multiple ones together in a "fluid" interface style. @author Thomas Risberg @since 2.5 @see java.sql.DatabaseMetaData @see org.springframework.jdbc.core.JdbcTemplate

    this.template = template;
  }


  @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

    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);
          pos++;
        }
      }
    }

View Full Code Here

            .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);

View Full Code Here

            .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);

View Full Code Here

    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);

View Full Code Here

    }


    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());

View Full Code Here

            .addValue("creationDate", dilution.getCreationDate())
            .addValue("dilutionUserName", dilution.getDilutionCreator())
            .addValue("securityProfile_profileId", securityProfileId);


    if (dilution.getId() == AbstractDilution.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                              .withTableName("emPCRDilution")
                              .usingGeneratedKeyColumns("dilutionId");


      try {
        dilution.setId(DbUtils.getAutoIncrement(template, "emPCRDilution"));


        String name = namingScheme.generateNameFor("name", dilution);
        dilution.setName(name);
        if (namingScheme.validateField("name", dilution.getName())) {
          String barcode = name + "::" + dilution.getEmPCR().getName();
          params.addValue("name", name);


          params.addValue("identificationBarcode", barcode);


          Number newId = insert.executeAndReturnKey(params);
          if (newId.longValue() != dilution.getId()) {
            log.error("Expected emPCRDilution ID doesn't match returned value from database insert: rolling back...");
            new NamedParameterJdbcTemplate(template).update(EMPCR_DILUTION_DELETE, new MapSqlParameterSource().addValue("dilutionId", newId.longValue()));
            throw new IOException("Something bad happened. Expected emPCRDilution ID doesn't match returned value from DB insert");
          }

View Full Code Here

            .addValue("qcMethod", runQC.getQcType().getQcTypeId())
            .addValue("information", runQC.getInformation())
            .addValue("doNotProcess", runQC.getDoNotProcess());


    if (runQC.getId() == AbstractQC.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                              .withTableName(TABLE_NAME)
                              .usingGeneratedKeyColumns("qcId");
      Number newId = insert.executeAndReturnKey(params);
      runQC.setId(newId.longValue());
    }
    else {
      params.addValue("qcId", runQC.getId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(RUN_QC_UPDATE, params);
    }


    for (Partition p : runQC.getPartitionSelections()) {
      SimpleJdbcInsert pInsert = new SimpleJdbcInsert(template)
              .withTableName("RunQC_Partition");


      MapSqlParameterSource poParams = new MapSqlParameterSource();
      poParams.addValue("runQc_runQcId", runQC.getId())
              .addValue("containers_containerId", p.getSequencerPartitionContainer().getId())
              .addValue("partitionNumber", p.getPartitionNumber());
      try {
        pInsert.execute(poParams);
      }
      catch(DuplicateKeyException se) {
        //ignore
      }
    }

View Full Code Here

            .addValue("creationDate", dilution.getCreationDate())
            .addValue("securityProfile_profileId", securityProfileId)
            .addValue("dilutionUserName", dilution.getDilutionCreator());


    if (dilution.getId() == AbstractDilution.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                              .withTableName("LibraryDilution")
                              .usingGeneratedKeyColumns("dilutionId");
      try {
        dilution.setId(DbUtils.getAutoIncrement(template, "LibraryDilution"));


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


        if (namingScheme.validateField("name", dilution.getName())) {
          String barcode = name + "::" + dilution.getLibrary().getAlias();
          params.addValue("name", name);


          params.addValue("identificationBarcode", barcode);


          Number newId = insert.executeAndReturnKey(params);
          if (newId.longValue() != dilution.getId()) {
            log.error("Expected LibraryDilution ID doesn't match returned value from database insert: rolling back...");
            new NamedParameterJdbcTemplate(template).update(LIBRARY_DILUTION_DELETE, new MapSqlParameterSource().addValue("dilutionId", newId.longValue()));
            throw new IOException("Something bad happened. Expected LibraryDilution ID doesn't match returned value from DB insert");
          }

View Full Code Here

               "to this DAO.");
      params.addValue("password", user.getPassword());
    }


    if (user.getUserId() == UserImpl.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                              .withTableName("User")
                              .usingGeneratedKeyColumns("userId");
      Number newId = insert.executeAndReturnKey(params);
      user.setUserId(newId.longValue());
    }
    else {
      params.addValue("userId", user.getUserId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(USER_UPDATE, params);
    }


    //sort User_Group


    //delete existing joins
    MapSqlParameterSource delparams = new MapSqlParameterSource();
    delparams.addValue("userId", user.getUserId());
    NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
    namedTemplate.update(USER_GROUP_DELETE_BY_USER_ID, delparams);


    if (user.getGroups()!= null && !user.getGroups().isEmpty()) {
      SimpleJdbcInsert eInsert = new SimpleJdbcInsert(template)
                            .withTableName("User_Group");
      for (Group g : user.getGroups()) {
        MapSqlParameterSource ugParams = new MapSqlParameterSource();
        ugParams.addValue("users_userId", user.getUserId())
                .addValue("groups_groupId", g.getGroupId());


        eInsert.execute(ugParams);
      }
    }


    return user.getUserId();
  }

View Full Code Here

0 1 2 3 4 5 6

TOP

Related Classes of org.springframework.jdbc.core.simple.SimpleJdbcInsert

br.jus.tjrn.arq.audit.providers.SpringJdbcAuditService

com.manning.siia.batch.PaymentWriter

com.upgradingdave.persist.hs2sqldb.ResourceDaoImpl

example.generics.persist.hsqldb.EpisodeDaoImpl

org.geekhub.database.ActorDAO

org.geekhub.database.MovieDAO

org.infinispan.spring.provider.sample.JdbcBookDao

org.jeecgframework.core.common.dao.jdbc.SimpleJdbcTemplate

org.sporcic.repository.JdbcContactDao

org.springframework.batch.sample.domain.football.internal.JdbcGameDao

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.