Java SimpleJdbcInsert类代码示例

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
@Autowired
public JdbcOwnerDAO(DataSource dataSource) {
  setDataSource(dataSource);
  insertActor = new SimpleJdbcInsert(dataSource).withTableName("owners").usingGeneratedKeyColumns("id");

  ownerRowMapper = (rs, rowNum) -> {
    Owner owner = new Owner();
    owner.setId(rs.getInt("id"));
    owner.setFirstName(rs.getString("first_name"));
    owner.setLastName(rs.getString("last_name"));
    owner.setAddress(rs.getString("address"));
    owner.setCity(rs.getString("city"));
    owner.setTelephone(rs.getString("telephone"));
    return owner;
  };
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
/**
 * Insert a new Book into the database.
 * 
 * @throws RuntimeException
 *             if id is not null
 */
@Override
public Book save(Book book) {
	if (book.getId() == null) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("title", book.getTitle());
		map.put("author", book.getAuthor());
		SimpleJdbcInsert sji = new SimpleJdbcInsert(getJdbcTemplate());
		sji.setTableName("book");
		sji.setGeneratedKeyName("id");
		Long id = (Long) sji.executeAndReturnKey(map);
		book.setId(id);
		return book;
	} else {
		throw new RuntimeException("Book exist");
	}
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
@Override
public void afterPropertiesSet() throws Exception {
	rowMapper = new BeanPropertyRowMapper<User>(User.class);
	jdbcInsert = new SimpleJdbcInsert(getDataSource()).withTableName(tableName);

	sqlSelectUserByUuid = String.format("SELECT * FROM %s u WHERE u.uuid = :userUuid", tableName);
	sqlSelectUserByEmail = String.format("SELECT * FROM %s u WHERE u.email = :userEmail", tableName);
	sqlDeleteUserByUuid = String.format("DELETE FROM %s WHERE uuid = :userUuid", tableName);
	sqlUpdateUser = String.format(
			"UPDATE %s SET display_name = :displayName, email = :email, time_zone = :timeZone, locale = :locale, is_blocked = :isBlocked, integration_data = :integrationData WHERE uuid = :uuid",
			tableName);

	sqlSearchUsersByDisplayName = String.format(
			"SELECT SQL_CALC_FOUND_ROWS u.*, INSTR(u.display_name, :displayNameAsIs) as ddd FROM %s u WHERE u.display_name LIKE :displayName ORDER BY ddd ASC, u.display_name LIMIT :offset,:max",
			tableName);
	sqlSearchUsersByDisplayNameGetCount = "SELECT FOUND_ROWS()";
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
/**
 * {@inheritDoc}
 */
@Override
public int insertInitialTestSuiteData() {
    LOGGER.debug("Build SQL query for new primary key in table 'sakuli_suites'");

    testSuite.refreshState();
    MapSqlParameterSource tcParameters = getInitialDataParameters();
    LOGGER.debug("write the following values to 'sakuli_suites': "
            + tcParameters.getValues()
            + " ==>  now execute ....");
    SimpleJdbcInsert insertInitialSuiteData = new SimpleJdbcInsert(getDataSource())
            .withTableName("sakuli_suites")
            .usingGeneratedKeyColumns("id");

    int dbPrimaryKey = insertInitialSuiteData.executeAndReturnKey(tcParameters).intValue();

    LOGGER.info("test suite \"" + testSuite.getId()
            + "\" has been written to 'sakuli_suites' with  primaryKey=" + dbPrimaryKey);
    return dbPrimaryKey;
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
@Override
public int saveTestSuiteToSahiJobs() {
    LOGGER.debug("save the guid to the table 'sakuli_jobs'");
    //build up the statement
    MapSqlParameterSource tcParameters = getGuidParameter();
    LOGGER.debug("write the following values to 'sakuli_jobs': "
            + tcParameters.getValues()
            + " ==>  now execute ....");
    SimpleJdbcInsert insertTS = new SimpleJdbcInsert(getDataSource())
            .withTableName("sakuli_jobs")
            .usingGeneratedKeyColumns("id");
    testSuite.setDbJobPrimaryKey(insertTS.executeAndReturnKey(tcParameters).intValue());
    LOGGER.info("the test suite \"" + testSuite.getId() + "\""
            + "with the guid \"" + testSuite.getGuid()
            + "\" has been written to 'sakuli_jobs' with  primaryKey=" + testSuite.getDbJobPrimaryKey());
    return testSuite.getDbJobPrimaryKey();
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
@Override
public void saveSpectrum(final SpectrumDetail spectrum) {
    logger.debug("Insert a spectrum into database: {}", spectrum.getReferenceId());

    SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(template);

    simpleJdbcInsert.withTableName("spectrum").usingGeneratedKeyColumns("spectrum_pk");

    HashMap<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("assay_fk", spectrum.getAssayId());
    parameters.put("spectrum_ref", spectrum.getReferenceId());
    parameters.put("precursor_mz", spectrum.getPrecursorMz());
    parameters.put("precursor_charge", spectrum.getPrecursorCharge());
    parameters.put("is_identified", spectrum.isIdentified());

    Number key = simpleJdbcInsert.executeAndReturnKey(new MapSqlParameterSource(parameters));
    spectrum.setId(key.longValue());
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
@Override
public void saveSpectrumLibrary(SpectrumLibraryDetail spectrumLibraryDetail) {
    logger.debug("Inserting a spectral library detail record");

    SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(template);

    simpleJdbcInsert.withTableName("spectral_library");

    Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("release_version", spectrumLibraryDetail.getVersion());
    parameters.put("release_date", spectrumLibraryDetail.getReleaseDate());
    parameters.put("taxonomy_id", spectrumLibraryDetail.getTaxonomyId());
    parameters.put("species_scientific_name", spectrumLibraryDetail.getSpeciesScientificName());
    parameters.put("species_name", spectrumLibraryDetail.getSpeciesName());
    parameters.put("number_of_spectra", spectrumLibraryDetail.getNumberOfSpectra());
    parameters.put("number_of_peptides", spectrumLibraryDetail.getNumberOfPeptides());
    parameters.put("file_size", spectrumLibraryDetail.getFileSize());
    parameters.put("file_name", spectrumLibraryDetail.getFileName());

    MapSqlParameterSource parameterSource = new MapSqlParameterSource(parameters);
    parameterSource.registerSqlType("release_date", Types.DATE);

    simpleJdbcInsert.execute(parameterSource);
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    LOG.info("Set up initial values");

    LOG.debug("Insert default billing plan");
    SimpleJdbcInsert billingJdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("s_billing_plan")
            .usingColumns("billingType", "numUsers", "volume", "numProjects", "pricing").usingGeneratedKeyColumns("id");

    Map<String, Object> billingParameters = new HashMap<>();
    billingParameters.put("billingType", "Community");
    billingParameters.put("numUsers", 99999999);
    billingParameters.put("volume", 999999999999L);
    billingParameters.put("numProjects", 999999);
    billingParameters.put("pricing", 0);

    Number billingPlanId = billingJdbcInsert.executeAndReturnKey(billingParameters);

    LOG.debug("Insert default account");
    SimpleJdbcInsert accountJdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("s_account")
            .usingColumns("status", "billingPlanId", "paymentMethod", "subdomain").usingGeneratedKeyColumns("id");
    Map<String, Object> accountParameters = new HashMap<>();
    accountParameters.put("status", "Active");
    accountParameters.put("billingPlanId", billingPlanId);
    accountParameters.put("paymentMethod", "None");
    accountParameters.put("subdomain", "");
    accountJdbcInsert.executeAndReturnKey(accountParameters);
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
public static final void grantJdbcAccess(JdbcTemplate jdbcTemplate,
        Group group, Role role) {
    long count = jdbcTemplate.queryForObject(
            "select count(*) from "
                    + TableUtilities.getTableName(RoleGroup.class)
                    + " where " + RoleGroup.ROLE_ID + " = ? and "
                    + RoleGroup.GROUP_ID + " = ?", Long.class,
            role.getId(), group.getId());
    if (count <= 0) {
        SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate);
        insert.withTableName(TableUtilities.getTableName(RoleGroup.class));
        Map<String, Object> fields = new HashMap<String, Object>();
        fields.put(RoleGroup.ROLE_ID, role.getId());
        fields.put(RoleGroup.GROUP_ID, group.getId());
        insert.execute(fields);
    }
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
public static final void grantAccess(JdbcTemplate jdbcTemplate,
        Long userId, Group group) {
    long count = jdbcTemplate.queryForObject(
            "select count(*) from "
                    + TableUtilities.getTableName(UserGroup.class)
                    + " where " + UserGroup.USER_ID + " = ? and "
                    + UserGroup.GROUP_ID + " = ?", Long.class, userId,
            group.getId());
    if (count <= 0) {
        SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate);
        insert.withTableName(TableUtilities.getTableName(UserGroup.class));
        Map<String, Object> fields = new HashMap<String, Object>();
        fields.put(UserGroup.GROUP_ID, group.getId());
        fields.put(UserGroup.USER_ID, userId);
        insert.execute(fields);
    }
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
public static final void update(Gson gson, JdbcTemplate jdbcTemplate, String identity, String name, Object value) {
    PluginSetting pluginSetting = null;
    try {
        pluginSetting = jdbcTemplate.queryForObject("select * from " + TableUtilities.getTableName(PluginSetting.class) + " where " + PluginSetting.IDENTITY + " = ? and " + PluginSetting.NAME + " = ?", new PluginSettingMapper(), identity, name);
    } catch (EmptyResultDataAccessException e) {
    }
    if (pluginSetting != null) {
        jdbcTemplate.update("update " + TableUtilities.getTableName(PluginSetting.class) + " set value = ? where " + PluginSetting.IDENTITY + " = ? and " + PluginSetting.NAME + " = ?", gson.toJson(value), identity, name);
    } else {
        SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate);
        insert.withTableName(TableUtilities.getTableName(PluginSetting.class));
        Map<String, Object> fields = new HashMap<String, Object>();
        fields.put(PluginSetting.IDENTITY, identity);
        fields.put(PluginSetting.NAME, name);
        fields.put(PluginSetting.VALUE, gson.toJson(value));
        insert.execute(fields);
    }
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
private SimpleJdbcInsert createJdbcInsert() {
    JdbcTemplate jdbcTemplate = (JdbcTemplate) operations().getJdbcOperations();
    SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate);

    jdbcInsert.withTableName(EntityUtils.tableName(domainClass));
    Set<String> usingColumns = EntityUtils.columnNamesExceptGeneratedValues(domainClass);
    jdbcInsert.usingColumns(usingColumns.toArray(new String[usingColumns.size()]));

    return jdbcInsert;
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
@Override
public void insert(T entity) {
    callBeforeInsert(entity);

    SimpleJdbcInsert jdbcInsert = createJdbcInsert();
    Map<String, Object> values = EntityUtils.values(entity, domainClass, false);

    jdbcInsert.execute(values);
    callAfterInsert(entity);
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
@Override
@Autowired
public void setDataSource(DataSource dataSource) {
    super.setDataSource(dataSource);
    this.insertPhotographer = new SimpleJdbcInsert(dataSource)
            .withTableName("Photographer")
            .usingGeneratedKeyColumns("id");
}
 

import org.springframework.jdbc.core.simple.SimpleJdbcInsert; //导入依赖的package包/类
@Override
@Autowired
public void setDataSource(DataSource dataSource) {
    super.setDataSource(dataSource);
    this.insertPhoto = new SimpleJdbcInsert(dataSource)
            .withTableName("Photo")
            .usingGeneratedKeyColumns("id");
}