Java Code Examples for org.springframework.jdbc.core.PreparedStatementCreatorFactory#newPreparedStatementCreator()

/**
 * Demonstrate how to insert and retrieve a generated key.
 * <p>
 * The {@code id} column of the {@code short_message} table can generated ids.
 * In order to retrieve the newly generated id, we need an update that returns
 * some data (a JDBC 3 feature). Spring supports this with a keyholder.
 * <p>
 * Unfortunately, {@link JdbcTemplate#update(PreparedStatementCreator, KeyHolder)}
 * needs a {@link PreparedStatementCreator} which is a bit overwhelming
 * here.
 */
public void insertMessage(Author author, String text) {
    String sql = "INSERT INTO short_message(author_id, posted_time, message_text)" +
            " VALUES(?, ?, ?)";
    Timestamp currentTimestamp = Timestamp.valueOf(LocalDateTime.now());

    PreparedStatementCreatorFactory statementCreatorFactory =
            new PreparedStatementCreatorFactory(sql,
                    Types.INTEGER, Types.TIMESTAMP, Types.VARCHAR);
    statementCreatorFactory.setReturnGeneratedKeys(true);
    statementCreatorFactory.setGeneratedKeysColumnNames("id");
    PreparedStatementCreator preparedStatementCreator =
            statementCreatorFactory.newPreparedStatementCreator(
                    new Object[]{author.getId(), currentTimestamp, text});

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    int update = jdbcTemplate.update(preparedStatementCreator, keyHolder);
    log.info("auto-insert created {} row with key {}", update, keyHolder.getKey());
}
 

/**
 * Demonstrate how to insert and retrieve a generated key.
 * <p>
 * The {@code id} column of the {@code short_message} table can generated ids.
 * In order to retrieve the newly generated id, we need an update that returns
 * some data (a JDBC 3 feature). Spring supports this with a keyholder.
 * <p>
 * Unfortunately, {@link JdbcTemplate#update(PreparedStatementCreator, KeyHolder)}
 * needs a {@link PreparedStatementCreator} which is a bit overwhelming
 * here.
 */
public void insertMessage(Author author, String text) {
    String sql = "INSERT INTO short_message(author_id, posted_time, message_text)" +
            " VALUES(?, ?, ?)";
    Timestamp currentTimestamp = Timestamp.valueOf(LocalDateTime.now());

    PreparedStatementCreatorFactory statementCreatorFactory =
            new PreparedStatementCreatorFactory(sql,
                    Types.INTEGER, Types.TIMESTAMP, Types.VARCHAR);
    statementCreatorFactory.setReturnGeneratedKeys(true);
    statementCreatorFactory.setGeneratedKeysColumnNames("id");
    PreparedStatementCreator preparedStatementCreator =
            statementCreatorFactory.newPreparedStatementCreator(
                    new Object[]{author.getId(), currentTimestamp, text});

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    KeyHolder keyHolder = new GeneratedKeyHolder();
    int update = jdbcTemplate.update(preparedStatementCreator, keyHolder);
    log.info("auto-insert created {} row with key {}", update, keyHolder.getKey());
}
 

@Test
public void updateWithPreparedStatementCreator() {
  reporter = AsyncReporter.builder(new JdbcTemplateSender(underlying))
      .pendingMaxMessages(2)
      .bufferedMaxMessages(2)
      .messageTimeout(0, TimeUnit.MILLISECONDS)
      .build();
  batchJdbcTemplate = new BatchJdbcTemplate(underlying, reporter);

  PreparedStatementCreatorFactory creatorFactory = new PreparedStatementCreatorFactory(INSERTION);
  creatorFactory.addParameter(new SqlParameter(Types.VARCHAR));
  creatorFactory.addParameter(new SqlParameter(Types.TIMESTAMP));

  PreparedStatementCreator creator = creatorFactory
      .newPreparedStatementCreator(new Object[]{randomString(), new Date()});

  batchJdbcTemplate.update(creator);
  batchJdbcTemplate.update(creator);

  reporter.flush();
  int rowCount = batchJdbcTemplate.queryForObject("SELECT COUNT(1) FROM test;", Integer.class);
  assertEquals(2, rowCount);
}
 

private PreparedStatement getPreparedStatement(String sql, @Nullable Map<String, ?> parameters, boolean returnKeys) throws SQLException {
    SqlParameterSource paramSource = new MapSqlParameterSource(parameters);
    ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
    String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
    List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
    Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
    PreparedStatementCreatorFactory psCreatorFactory = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
    psCreatorFactory.setReturnGeneratedKeys(returnKeys);
    PreparedStatementCreator psCreator = psCreatorFactory.newPreparedStatementCreator(params);
    PreparedStatement preparedStatement = psCreator.createPreparedStatement(connection);
    logger.debug(preparedStatement.unwrap(PreparedStatement.class).toString());
    return preparedStatement;
}
 

public Spittle save(Spittle spittle) {
  
  System.out.println("--> " + spittle.getMessage());
  
  
  String sql = "insert into Spittle (message, created_at, latitude, longitude)" +
      " values (?, ?, ?, ?)";
  PreparedStatementCreatorFactory statementCreatorFactory = new PreparedStatementCreatorFactory(sql, Types.VARCHAR, Types.TIMESTAMP, Types.DOUBLE, Types.DOUBLE);
  statementCreatorFactory.setReturnGeneratedKeys(true);
  PreparedStatementCreator creator = statementCreatorFactory.newPreparedStatementCreator(new Object[] {
      spittle.getMessage(),
      spittle.getTime(),
      spittle.getLatitude(),
      spittle.getLongitude()
  });

  
  GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
  jdbc.update(creator, keyHolder);
  return new Spittle(
      keyHolder.getKey().longValue(), 
      spittle.getMessage(), 
      spittle.getTime(), 
      spittle.getLongitude(), 
      spittle.getLatitude());
  
  
}
 

public void update(Spittle spittle) {
 String sql = "update Spittle set message=?, created_at=?, latitude=?, longitude=? "
 		+ "where id=?";
 PreparedStatementCreatorFactory creatorFactory = 
	  new PreparedStatementCreatorFactory(sql, Types.VARCHAR, Types.TIMESTAMP, Types.DOUBLE, Types.DOUBLE, Types.INTEGER);
 PreparedStatementCreator creator = creatorFactory.newPreparedStatementCreator(new Object[] {
	  spittle.getMessage(),
	  spittle.getTime(),
	  spittle.getLatitude(),
	  spittle.getLongitude(),
	  spittle.getId()
 });
 jdbc.update(creator);
}
 

/**
 * Build a PreparedStatementCreator based on the given SQL and named parameters.
 * <p>Note: Not used for the {@code update} variant with generated key handling.
 * @param sql SQL to execute
 * @param paramSource container of arguments to bind
 * @return the corresponding PreparedStatementCreator
 */
protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) {
	ParsedSql parsedSql = getParsedSql(sql);
	String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
	Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
	List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
	PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
	return pscf.newPreparedStatementCreator(params);
}
 

protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) {
	ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
	String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
	Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
	List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
	PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
	return pscf.newPreparedStatementCreator(params);
}
 

/**
 * Build a PreparedStatementCreator based on the given SQL and named parameters.
 * <p>Note: Not used for the {@code update} variant with generated key handling.
 * @param sql SQL to execute
 * @param paramSource container of arguments to bind
 * @return the corresponding PreparedStatementCreator
 */
protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) {
	ParsedSql parsedSql = getParsedSql(sql);
	String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
	Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
	List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
	PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
	return pscf.newPreparedStatementCreator(params);
}
 

/**
 * Build a PreparedStatementCreator based on the given SQL and named parameters.
 * <p>Note: Not used for the {@code update} variant with generated key handling.
 * @param sql SQL to execute
 * @param paramSource container of arguments to bind
 * @return the corresponding PreparedStatementCreator
 */
protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) {
	ParsedSql parsedSql = getParsedSql(sql);
	String sqlToUse = NamedParameterUtils.substituteNamedParameters(parsedSql, paramSource);
	Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
	List<SqlParameter> declaredParameters = NamedParameterUtils.buildSqlParameterList(parsedSql, paramSource);
	PreparedStatementCreatorFactory pscf = new PreparedStatementCreatorFactory(sqlToUse, declaredParameters);
	return pscf.newPreparedStatementCreator(params);
}
 

/**
 * Build a {@link PreparedStatementCreator} based on the given SQL and named parameters.
 * <p>Note: Directly called from all {@code query} variants.
 * Not used for the {@code update} variant with generated key handling.
 *
 * @param sql         the SQL statement to execute
 * @param paramSource container of arguments to bind
 * @return the corresponding {@link PreparedStatementCreator}
 */
@Override
protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource) {
    ParsedSql parsedSql = getParsedSql(sql);
    PreparedStatementCreatorFactory pscf = getPreparedStatementCreatorFactory(parsedSql, paramSource);
    Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
    return pscf.newPreparedStatementCreator(params);
}
 

/**
 * Build a {@link PreparedStatementCreator} based on the given SQL and named parameters.
 * <p>Note: Used for the {@code update} variant with generated key handling, and also
 * delegated from {@link #getPreparedStatementCreator(String, SqlParameterSource)}.
 * @param sql the SQL statement to execute
 * @param paramSource container of arguments to bind
 * @param customizer callback for setting further properties on the
 * {@link PreparedStatementCreatorFactory} in use), applied before the
 * actual {@code newPreparedStatementCreator} call
 * @return the corresponding {@link PreparedStatementCreator}
 * @since 5.0.5
 * @see #getParsedSql(String)
 * @see PreparedStatementCreatorFactory#PreparedStatementCreatorFactory(String, List)
 * @see PreparedStatementCreatorFactory#newPreparedStatementCreator(Object[])
 */
protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource,
		@Nullable Consumer<PreparedStatementCreatorFactory> customizer) {

	ParsedSql parsedSql = getParsedSql(sql);
	PreparedStatementCreatorFactory pscf = getPreparedStatementCreatorFactory(parsedSql, paramSource);
	if (customizer != null) {
		customizer.accept(pscf);
	}
	Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
	return pscf.newPreparedStatementCreator(params);
}
 

/**
 * Build a {@link PreparedStatementCreator} based on the given SQL and named parameters.
 * <p>Note: Used for the {@code update} variant with generated key handling, and also
 * delegated from {@link #getPreparedStatementCreator(String, SqlParameterSource)}.
 * @param sql the SQL statement to execute
 * @param paramSource container of arguments to bind
 * @param customizer callback for setting further properties on the
 * {@link PreparedStatementCreatorFactory} in use), applied before the
 * actual {@code newPreparedStatementCreator} call
 * @return the corresponding {@link PreparedStatementCreator}
 * @since 5.0.5
 * @see #getParsedSql(String)
 * @see PreparedStatementCreatorFactory#PreparedStatementCreatorFactory(String, List)
 * @see PreparedStatementCreatorFactory#newPreparedStatementCreator(Object[])
 */
protected PreparedStatementCreator getPreparedStatementCreator(String sql, SqlParameterSource paramSource,
		@Nullable Consumer<PreparedStatementCreatorFactory> customizer) {

	ParsedSql parsedSql = getParsedSql(sql);
	PreparedStatementCreatorFactory pscf = getPreparedStatementCreatorFactory(parsedSql, paramSource);
	if (customizer != null) {
		customizer.accept(pscf);
	}
	Object[] params = NamedParameterUtils.buildValueArray(parsedSql, paramSource, null);
	return pscf.newPreparedStatementCreator(params);
}