@Test public void testBindBatch() { PreparedBatch b = h.prepareBatch("insert into something (id, name) values (:id, :name)"); b.bind("id", 1).bind("name", "Eric").add(); b.bind("id", 2).bind("name", "Brian").add(); b.bind("id", 3).bind("name", "Keith").add(); b.execute(); List<Something> r = h.createQuery("select * from something order by id").mapToBean(Something.class).list(); assertThat(r).hasSize(3); assertThat(r.get(2).getName()).isEqualTo("Keith"); }
private ExecutedBatch internalBatchExecute() { if (!getBinding().isEmpty()) { add(); beforeTemplating(); String renderedSql = getConfig(SqlStatements.class) .getTemplateEngine() .render(getSql(), getContext()); getContext().setRenderedSql(renderedSql); ParsedSql parsedSql = getConfig(SqlStatements.class) .getSqlParser() .parse(renderedSql, getContext()); String sql = parsedSql.getSql(); ParsedParameters parsedParameters = parsedSql.getParameters(); getContext().setParsedSql(parsedSql); StatementBuilder statementBuilder = getHandle().getStatementBuilder(); @SuppressWarnings("PMD.CloseResource") Connection connection = getHandle().getConnection(); stmt = statementBuilder.create(connection, sql, getContext()); addCleanable(() -> statementBuilder.close(connection, sql, stmt)); getConfig(SqlStatements.class).customize(stmt); } catch (SQLException e) { throw new UnableToCreateStatementException(e, getContext()); beforeBinding();
for (int row = 0; row < 1000; row++) { if (!cursor.advanceNextPosition()) { if (batch.size() > 0) { batch.execute(); Type type = columns.get(column).getType(); if (BOOLEAN.equals(type)) { batch.bind(column, cursor.getBoolean(column)); batch.bind(column, cursor.getLong(column)); batch.bind(column, (int) cursor.getLong(column)); batch.bind(column, cursor.getDouble(column)); batch.bind(column, cursor.getSlice(column).toStringUtf8()); batch.bind(column, new Date(localMillis)); batch.add(); batch.execute();
public ResultIterator<Integer> executeAndGetModCount() { StatementContext ctx = getContext(); final int[] modCount = execute(); return new ResultIterator<Integer>() { int pos = 0; @Override public boolean hasNext() { return pos < modCount.length; } @Override public Integer next() { if (!hasNext()) { throw new NoSuchElementException(); } return modCount[pos++]; } @Override public StatementContext getContext() { return ctx; } @Override public void close() { ctx.close(); } }; }
@Test public void testBindMaps() { PreparedBatch b = h.prepareBatch("insert into something (id, name) values (:id, :name)"); b.add(ImmutableMap.of("id", 0, "name", "Keith")); b.add(ImmutableMap.of("id", 1, "name", "Eric")); b.add(ImmutableMap.of("id", 2, "name", "Brian")); b.execute(); List<Something> r = h.createQuery("select * from something order by id").mapToBean(Something.class).list(); assertThat(r).hasSize(3); assertThat(r.get(2).getName()).isEqualTo("Brian"); }
@Test public void testOnPreparedBatch() { Handle h = dbRule.getSharedHandle(); PreparedBatch batch = h.prepareBatch("insert into something (id, name) values (:id, :name)"); batch.registerArgument(new NameAF()); batch.bind("id", 1).bind("name", new Name("Brian", "McCallister")).add(); batch.bind("id", 2).bind("name", new Name("Henning", "S")).add(); batch.execute(); List<String> rs = h.createQuery("select name from something order by id") .mapTo(String.class) .list(); assertThat(rs).containsExactly("Brian McCallister", "Henning S"); }
@Test public void testMultipleExecuteBindBean() { final PreparedBatch b = h.prepareBatch("insert into something (id, name) values (:id, :name)"); b.bindBean(new Something(1, "Eric")).add(); b.bindBean(new Something(2, "Brian")).add(); b.execute(); // bindings should be cleared after execute() b.bindBean(new Something(3, "Keith")).add(); b.execute(); final List<Something> r = h.createQuery("select * from something order by id").mapToBean(Something.class).list(); assertThat(r).extracting(Something::getId, Something::getName) .containsExactly(tuple(1, "Eric"), tuple(2, "Brian"), tuple(3, "Keith")); }
@Test public void testMultipleExecuteBindFields() { h.registerRowMapper(ConstructorMapper.factory(PublicSomething.class)); final PreparedBatch b = h.prepareBatch("insert into something (id, name) values (:id, :name)"); b.bindFields(new PublicSomething(1, "Eric")).add(); b.bindFields(new PublicSomething(2, "Brian")).add(); b.execute(); // bindings should be cleared after execute() b.bindFields(new PublicSomething(3, "Keith")).add(); b.execute(); final List<PublicSomething> r = h.createQuery("select * from something order by id").mapTo(PublicSomething.class).list(); assertThat(r).extracting(s -> s.id, s -> s.name).containsExactly(tuple(1, "Eric"), tuple(2, "Brian"), tuple(3, "Keith")); }
@Test public void testPreparedBatch() { h.execute(CREATE); h.prepareBatch(INSERT_PREPARED).bind(0, 1).execute(); assertThat(logger.getRawSql()).containsExactly(CREATE, CREATE, INSERT_PREPARED, INSERT_PREPARED); assertThat(logger.getTimings()).hasSize(2).allMatch(IS_POSITIVE); assertThat(logger.getExceptions()).isEmpty(); }
@Test public void testPreparedBatch() { h.configure(SqlStatements.class, c -> c.setSqlLogger(SqlLogger.NOP_SQL_LOGGER)); h.createUpdate(CREATE).define("x", "foo").execute(); h.configure(SqlStatements.class, c -> c.setSqlLogger(logger)); int id = 0; h.prepareBatch("insert into <x>(bar) values(?)") .define("x", "foo") .bind(0, new Argument() { @Override public void apply(int position, PreparedStatement statement, StatementContext ctx) throws SQLException { statement.setInt(1, id); } @Override public String toString() { return Objects.toString(id); } }) .execute(); assertThat(logger.getAttributes()) .hasSize(2) .allMatch(x -> x.get("x").equals("foo")) .allMatch(x -> x.size() == 1); assertThat(logger.getBindings()).containsExactly(String.valueOf(id), String.valueOf(id)); }
@Test public void testBatchInsertWithKeyGenerationAndExplicitSeveralColumnNames() { PreparedBatch batch = h.prepareBatch("insert into something (name) values (?) "); batch.add("Brian"); batch.add("Thom"); List<IdCreateTime> ids = batch.executeAndReturnGeneratedKeys("id", "create_time") .map((r, ctx) -> new IdCreateTime(r.getInt("id"), r.getDate("create_time"))) .list(); assertThat(ids).hasSize(2); assertThat(ids).extracting(ic -> ic.id).containsExactly(1, 2); assertThat(ids).extracting(ic -> ic.createTime).doesNotContainNull(); }
public ResultBearing executeAndReturnGeneratedKeys(String... columnNames) { return execute(returningGeneratedKeys(columnNames)); }
@Test public void testPreparedBatch() { PreparedBatch b = dbRule.openHandle().prepareBatch("insert into something (id, name) values (?,?)"); b.add(0, "a"); b.add(0, "a"); assertThatExceptionOfType(UnableToExecuteStatementException.class) .isThrownBy(b::execute) .satisfies(e -> assertSuppressions(e.getCause())); }
/** * Executes the batch, returning the result obtained from the given {@link ResultProducer}. * * @param <R> the type of the result * @param producer the result producer. * @return value returned by the result producer. */ public <R> R execute(ResultProducer<R> producer) { try { return producer.produce(() -> internalBatchExecute().stmt, getContext()); } catch (SQLException e) { try { close(); } catch (Exception e1) { e.addSuppressed(e1); } throw new UnableToProduceResultException("Exception producing batch result", e, getContext()); } }
/** * Add the current binding as a saved batch and clear the binding. * @return this */ public PreparedBatch add() { final Binding currentBinding = getBinding(); if (currentBinding.isEmpty()) { throw new IllegalStateException("Attempt to add() an empty batch, you probably didn't mean to do this " + "- call add() *after* setting batch parameters"); } bindings.add(currentBinding); getContext().setBinding(new Binding()); return this; }
batchIntermediate = batch -> batch.executeAndReturnGeneratedKeys(columnNames) .map(mapper) .iterator(); } else { batchIntermediate = batch -> batch.executeAndReturnGeneratedKeys(columnNames) .mapTo(magic.elementType(batch.getContext())) .iterator();
/** * Prepare a batch to execute. This is for efficiently executing more than one * of the same statements with different parameters bound. * * @param sql the batch SQL * @return a batch which can have "statements" added */ public PreparedBatch prepareBatch(String sql) { return new PreparedBatch(this, sql); }
@Test public void testBindProperties() { PreparedBatch b = h.prepareBatch("insert into something (id, name) values (?, ?)"); b.add(0, "Keith"); b.add(1, "Eric"); b.add(2, "Brian"); b.execute(); List<Something> r = h.createQuery("select * from something order by id").mapToBean(Something.class).list(); assertThat(r).hasSize(3); assertThat(r.get(2).getName()).isEqualTo("Brian"); }