Examples of 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

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

            .addValue("instrumentModel", platform.getInstrumentModel())
            .addValue("description", platform.getDescription())
            .addValue("numContainers", platform.getNumContainers());


    if (platform.getPlatformId() == null) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
              .withTableName(TABLE_NAME)
              .usingGeneratedKeyColumns("platformId");
      Number newId = insert.executeAndReturnKey(params);
      platform.setPlatformId(newId.longValue());
    }
    else {
      params.addValue("platformId", platform.getPlatformId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);

View Full Code Here

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

            .addValue("securityProfile_profileId", securityProfileId)
            .addValue("project_projectId", study.getProject().getProjectId())
            .addValue("studyType", study.getStudyType());


    if (study.getId() == AbstractStudy.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                            .withTableName(TABLE_NAME)
                            .usingGeneratedKeyColumns("studyId");
      try {
        study.setId(DbUtils.getAutoIncrement(template, TABLE_NAME));


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


        if (namingScheme.validateField("name", study.getName())) {
          params.addValue("name", name);


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


      Project p = study.getProject();


      SimpleJdbcInsert pInsert = new SimpleJdbcInsert(template)
                            .withTableName("Project_Study");


      MapSqlParameterSource poParams = new MapSqlParameterSource();
      poParams.addValue("Project_projectId", p.getProjectId())
              .addValue("studies_studyId", study.getId());
      try {
        pInsert.execute(poParams);
      }
      catch(DuplicateKeyException dke) {
        //ignore
      }
    }

View Full Code Here

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

            .addValue("qcDate", poolQC.getQcDate())
            .addValue("qcMethod", poolQC.getQcType().getQcTypeId())
            .addValue("results", poolQC.getResults());


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

View Full Code Here

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

    if (sequencerPartitionContainer.getPlatformType() != null) {
      params.addValue("platformType", sequencerPartitionContainer.getPlatformType().getKey());
    }


    if (sequencerPartitionContainer.getId() == AbstractSequencerPartitionContainer.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
              .withTableName(TABLE_NAME)
              .usingGeneratedKeyColumns("containerId");
      //try {
        sequencerPartitionContainer.setId(DbUtils.getAutoIncrement(template, TABLE_NAME));


        /*
        String name = namingScheme.generateNameFor("name", sequencerPartitionContainer);
        sequencerPartitionContainer.setName(name);


        if (namingScheme.validateField("name", sequencerPartitionContainer.getName())) {
          params.addValue("name", name);


          Number newId = insert.executeAndReturnKey(params);
          if (newId.longValue() != sequencerPartitionContainer.getId()) {
            log.error("Expected SequencerPartitionContainer ID doesn't match returned value from database insert: rolling back...");
            new NamedParameterJdbcTemplate(template).update(SEQUENCER_PARTITION_CONTAINER_DELETE, new MapSqlParameterSource().addValue("containerId", newId.longValue()));
            throw new IOException("Something bad happened. Expected SequencerPartitionContainer ID doesn't match returned value from DB insert");
          }
        }
        else {
          throw new IOException("Cannot save SequencerPartitionContainer - invalid field:" + sequencerPartitionContainer.toString());
        }


      }
      catch (MisoNamingException e) {
        throw new IOException("Cannot save SequencerPartitionContainer - issue with naming scheme", e);
      }
      */
      Number newId = insert.executeAndReturnKey(params);
      sequencerPartitionContainer.setId(newId.longValue());
    }
    else {
      /*
      try {
        if (namingScheme.validateField("name", sequencerPartitionContainer.getName())) {
          params.addValue("containerId", sequencerPartitionContainer.getId())
                .addValue("name", sequencerPartitionContainer.getName());
          NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
          namedTemplate.update(SEQUENCER_PARTITION_CONTAINER_UPDATE, params);
        }
        else {
          throw new IOException("Cannot save SequencerPartitionContainer - invalid field:" + sequencerPartitionContainer.toString());
        }
      }
      catch (MisoNamingException e) {
        throw new IOException("Cannot save SequencerPartitionContainer - issue with naming scheme", e);
      }
      */


      params.addValue("containerId", sequencerPartitionContainer.getId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(SEQUENCER_PARTITION_CONTAINER_UPDATE, params);
    }


    //MapSqlParameterSource delparams = new MapSqlParameterSource();
    //delparams.addValue("container_containerId", sequencerPartitionContainer.getContainerId());
    //NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
    //namedTemplate.update(SEQUENCER_PARTITION_CONTAINER_PARTITION_DELETE_BY_SEQUENCER_PARTITION_CONTAINER_ID, delparams);


    if (sequencerPartitionContainer.getPartitions() != null && !sequencerPartitionContainer.getPartitions().isEmpty()) {
      //log.info(sequencerPartitionContainer.getName()+":: Saving " + sequencerPartitionContainer.getPartitions().size() + " partitions...");


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


      for (SequencerPoolPartition l : sequencerPartitionContainer.getPartitions()) {
        l.setSecurityProfile(sequencerPartitionContainer.getSecurityProfile());
        long partitionId = partitionDAO.save(l);


        //log.info(sequencerPartitionContainer.getName()+":: Saved partition " + l.getPartitionNumber() + " ("+partitionId+")");


        MapSqlParameterSource flParams = new MapSqlParameterSource();
        flParams.addValue("container_containerId", sequencerPartitionContainer.getId())
                .addValue("partitions_partitionId", partitionId);
        try {
          eInsert.execute(flParams);
        }
        catch (DuplicateKeyException dke) {
          log.debug("This Container/Partition combination already exists - not inserting: " + dke.getMessage());
        }
      }

View Full Code Here

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

    if (library.getId() == AbstractLibrary.UNSAVED_ID) {
      if (getByAlias(library.getAlias()) != null) {
        throw new IOException("NEW: A library with this alias already exists in the database");
      }
      else {
        SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                .withTableName(TABLE_NAME)
                .usingGeneratedKeyColumns("libraryId");
        /*
        String name = Library.PREFIX + DbUtils.getAutoIncrement(template, TABLE_NAME);
        params.addValue("name", name);
        params.addValue("identificationBarcode", name + "::" + library.getAlias());
        Number newId = insert.executeAndReturnKey(params);
        library.setLibraryId(newId.longValue());
        library.setName(name);
        */


        try {
          library.setId(DbUtils.getAutoIncrement(template, TABLE_NAME));


          String name = libraryNamingScheme.generateNameFor("name", library);
          library.setName(name);
          if (libraryNamingScheme.validateField("name", library.getName()) && libraryNamingScheme.validateField("alias", library.getAlias())) {
            String barcode = name + "::" + library.getAlias();
            params.addValue("name", name);


            params.addValue("identificationBarcode", barcode);


            Number newId = insert.executeAndReturnKey(params);
            if (newId.longValue() != library.getId()) {
              log.error("Expected library ID doesn't match returned value from database insert: rolling back...");
              new NamedParameterJdbcTemplate(template).update(LIBRARY_DELETE, new MapSqlParameterSource().addValue("libraryId", newId.longValue()));
              throw new IOException("Something bad happened. Expected library ID doesn't match returned value from DB insert");
            }
          }
          else {
            throw new IOException("Cannot save library - invalid field:" + library.toString());
          }
        }
        catch (MisoNamingException e) {
          throw new IOException("Cannot save library - issue with naming scheme", e);
        }
      }
    }
    else {
      try {
        if (libraryNamingScheme.validateField("name", library.getName()) && libraryNamingScheme.validateField("alias", library.getAlias())) {
          params.addValue("libraryId", library.getId())
                .addValue("name", library.getName())
                .addValue("identificationBarcode", library.getName() + "::" + library.getAlias());
          NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
          namedTemplate.update(LIBRARY_UPDATE, params);
        }
        else {
          throw new IOException("Cannot save library - invalid field:" + library.toString());
        }
      }
      catch (MisoNamingException e) {
        throw new IOException("Cannot save library - issue with naming scheme", e);
      }
    }


    MapSqlParameterSource libparams = new MapSqlParameterSource();
    libparams.addValue("library_libraryId", library.getId());
    NamedParameterJdbcTemplate libNamedTemplate = new NamedParameterJdbcTemplate(template);
    libNamedTemplate.update(LIBRARY_TAGBARCODE_DELETE_BY_LIBRARY_ID, libparams);


    /*
    if (library.getTagBarcode() != null) {
      SimpleJdbcInsert eInsert = new SimpleJdbcInsert(template)
              .withTableName("Library_TagBarcode");


      MapSqlParameterSource ltParams = new MapSqlParameterSource();
      ltParams.addValue("library_libraryId", library.getLibraryId())
              .addValue("barcode_barcodeId", library.getTagBarcode().getTagBarcodeId());


      eInsert.execute(ltParams);
    }
    */


    if (library.getTagBarcodes() != null && !library.getTagBarcodes().isEmpty()) {
      SimpleJdbcInsert eInsert = new SimpleJdbcInsert(template)
              .withTableName("Library_TagBarcode");


      for (TagBarcode t : library.getTagBarcodes().values()) {
        MapSqlParameterSource ltParams = new MapSqlParameterSource();
        ltParams.addValue("library_libraryId", library.getId())
              .addValue("barcode_barcodeId", t.getId());
        eInsert.execute(ltParams);
      }
    }


    if (this.cascadeType != null) {
      if (this.cascadeType.equals(CascadeType.PERSIST)) {

View Full Code Here

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

            .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

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

            .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

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

                                  @Property(name = "includeParameterTypes", value = "false")
                          }
                  )
  )
  public long save(SecurityProfile securityProfile) throws IOException {
    SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
            .withTableName(TABLE_NAME);
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("allowAllInternal", securityProfile.isAllowAllInternal());


    if (securityProfile.getOwner() != null) {
      params.addValue("owner_userId", securityProfile.getOwner().getUserId());
    }


    //if a profile 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(securityProfile.getProfileId() != SecurityProfile.UNSAVED_ID) {
      MapSqlParameterSource delparams = new MapSqlParameterSource();
      delparams.addValue("profileId", securityProfile.getProfileId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(PROFILE_USERS_GROUPS_DELETE, delparams);


      List<SecurityProfile> results = template.query(PROFILE_SELECT_BY_ID, new Object[]{securityProfile.getProfileId()}, new SecurityProfileMapper());
      if (results.size() > 0) {
        log.error("SecurityProfile deletion failed!");
      }
      else {
        params.addValue("profileId", securityProfile.getProfileId());
        insert.execute(params);
      }
    }
    else {
      insert.usingGeneratedKeyColumns("profileId");
      Number newId = insert.executeAndReturnKey(params);
      securityProfile.setProfileId(newId.longValue());
    }


    //profile read users
    if (securityProfile.getReadUsers() != null && !securityProfile.getReadUsers().isEmpty()) {
      SimpleJdbcInsert uInsert = new SimpleJdbcInsert(template)
              .withTableName("SecurityProfile_ReadUser");


      for (User u : securityProfile.getReadUsers()) {
        MapSqlParameterSource uParams = new MapSqlParameterSource();
        uParams.addValue("SecurityProfile_profileId", securityProfile.getProfileId())
                .addValue("readUser_userId", u.getUserId());
        uInsert.execute(uParams);
      }
    }


    //profile write users
    if (securityProfile.getWriteUsers() != null && !securityProfile.getWriteUsers().isEmpty()) {
      SimpleJdbcInsert uInsert = new SimpleJdbcInsert(template)
              .withTableName("SecurityProfile_WriteUser");


      for (User u : securityProfile.getWriteUsers()) {
        MapSqlParameterSource uParams = new MapSqlParameterSource();
        uParams.addValue("SecurityProfile_profileId", securityProfile.getProfileId())
                .addValue("writeUser_userId", u.getUserId());
        uInsert.execute(uParams);
      }
    }


    //profile read groups
    if (securityProfile.getReadGroups() != null && !securityProfile.getReadGroups().isEmpty()) {
      SimpleJdbcInsert uInsert = new SimpleJdbcInsert(template)
              .withTableName("SecurityProfile_ReadGroup");


      for (Group g : securityProfile.getReadGroups()) {
        MapSqlParameterSource uParams = new MapSqlParameterSource();
        uParams.addValue("SecurityProfile_profileId", securityProfile.getProfileId())
                .addValue("readGroup_groupId", g.getGroupId());
        uInsert.execute(uParams);
      }
    }


    //profile read groups
    if (securityProfile.getWriteGroups() != null && !securityProfile.getWriteGroups().isEmpty()) {
      SimpleJdbcInsert uInsert = new SimpleJdbcInsert(template)
              .withTableName("SecurityProfile_WriteGroup");


      for (Group g : securityProfile.getWriteGroups()) {
        MapSqlParameterSource uParams = new MapSqlParameterSource();
        uParams.addValue("SecurityProfile_profileId", securityProfile.getProfileId())
                .addValue("writeGroup_groupId", g.getGroupId());
        uInsert.execute(uParams);
      }
    }
    
    return securityProfile.getProfileId();
  }

View Full Code Here

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

    else {
      params.addValue("pool_poolId", null);
    }
    
    if (partition.getId() == AbstractPartition.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
        .withTableName(TABLE_NAME)
        .usingGeneratedKeyColumns("partitionId");
      Number newId = insert.executeAndReturnKey(params);
      partition.setId(newId.longValue());
    }
    else {
      params.addValue("partitionId", partition.getId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);

View Full Code Here

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

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

View Full Code Here

0 1 2 3 4 5

TOP

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.