ArrayCountOperation

[Douglas Surber]

Here’s another example using POJOs.

  public void insertListIndependent(List<Item> list, DataSource ds) {
    String sql = "insert into tab values "
            + "(:elem_id, :elem_name, :elem_answer)";
    try (Connection conn = ds.getConnection()) {
      conn.arrayCountOperation(sql)
              .set("elem_", list)
              .submit();
    }
  }


Douglas

> On Oct 20, 2017, at 10:27 AM, Douglas Surber <DOUGLAS.SURBER at oracle.com> wrote:
> 
>  public void insert(Connection conn, List<Item> items) {
>    int n = items.size();
>    Integer[] ids = new Integer[n];
>    String[] names = new String[n];
>    Integer[] answers = new Integer[n];
>    int j = 0;
>    for (Item i : items) {
>      ids[j] = i.getId();
>      names[j] = i.getName();
>      answers[j++] = i.getAnswer();
>    }
>    String sql = "INSERT INTO TAB (@id, @name, @answer)";
>    conn.<List<Long>>arrayCountOperation(sql)
>            .set("id", ids)
>            .set("name", names)
>            .set("answer", answers)
>            .initialValue( () -> new ArrayList<Long>(n) )
>            .countAggregator( (l, c) -> {
>              l.add(c.getCount());
>              return l;
>            })
>            .submit();
>  }
> 
> Construction of the intermediate arrays is no additional work. The implementation will have to store the sets of parameter values in some fashion and so will have to allocate some kind of structure and copy the references to the values into those structures. If the number of items is known in advance the user code can take advantage of that. If the data is already in this shape the user code does not have to transpose it. In most cases this will be more efficient than BatchCountOperation and the same in the worst case.
> 
> Douglas
> 
>> On Oct 20, 2017, at 9:51 AM, Lance Andersen <lance.andersen at oracle.com> wrote:
>> 
>> Can you pull together a revised example and send it out?
>> 
>> 
>>> On Oct 20, 2017, at 11:40 AM, Douglas Surber <douglas.surber at oracle.com <mailto:douglas.surber at oracle.com>> wrote:
>>> 
>>> Based on a suggestion by Lukas Eder <lukas.eder at gmail.com <mailto:lukas.eder at gmail.com>> I intend to replace BatchCountOperation with ArrayCountOperation, appended. This is a much better representation of the desired capability. This is a replacement rather than an addition as one way to do something is enough.
>>> 
>>> Thoughts?
>>> 
>>> Douglas
>>> 
>>> 
>>> 
>>> 
>>> 
>>> /*
>>> * Copyright (c)  2017, Oracle and/or its affiliates. All rights reserved.
>>> * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
>>> *
>>> * This code is free software; you can redistribute it and/or modify it
>>> * under the terms of the GNU General Public License version 2 only, as
>>> * published by the Free Software Foundation.  Oracle designates this
>>> * particular file as subject to the "Classpath" exception as provided
>>> * by Oracle in the LICENSE file that accompanied this code.
>>> *
>>> * This code is distributed in the hope that it will be useful, but WITHOUT
>>> * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
>>> * FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License
>>> * version 2 for more details (a copy is included in the LICENSE file that
>>> * accompanied this code).
>>> *
>>> * You should have received a copy of the GNU General Public License version
>>> * 2 along with this work; if not, write to the Free Software Foundation,
>>> * Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
>>> *
>>> * Please contact Oracle, 500 Oracle Parkway, Redwood Shores, CA 94065 USA
>>> * or visit www.oracle.com <http://www.oracle.com/> if you need additional information or have any
>>> * questions.
>>> */
>>> package java.sql2;
>>> 
>>> import java.time.Duration;
>>> import java.util.List;
>>> import java.util.concurrent.CompletionStage;
>>> import java.util.function.BiFunction;
>>> import java.util.function.Consumer;
>>> import java.util.function.Supplier;
>>> 
>>> /**
>>> * A database operation that returns a count that is executed multiple times
>>> * with multiple sets of parameter values in one database operation. The
>>> * parameters are submitted to the database in the same order as in the
>>> * sequences passed to the set methods. The count results are passed to the
>>> * count aggregator in the same order they are produced by the database. The
>>> * value of the Operation is the last result produced by the count aggregator.
>>> *
>>> * @param <T> the type of the result of aggregating the count results
>>> */
>>> public interface ArrayCountOperation<T> extends Operation<T> {
>>> 
>>> /**
>>>  * Set a sequence of parameter values. The value is captured and should not be
>>>  * modified before the {@link Operation} is completed.
>>>  *
>>>  * The Operation is completed exceptionally with ClassCastException if any of
>>>  * the values cannot be converted to the specified SQL type.
>>>  *
>>>  * @param id the identifier of the parameter marker to be set
>>>  * @param values the sequence of values the parameter is to be set to
>>>  * @param type the SQL type of the values to send to the database
>>>  * @return this Operation
>>>  * @throws IllegalArgumentException if the length of values is not the same as
>>>  * the length of the previously set parameter sequences or if the same id was
>>>  * passed in a previous call.
>>>  * @throws IllegalStateException if the {@link Operation} has been submitted
>>>  */
>>> public ArrayCountOperation<T> set(String id, List<?> values, SqlType type);
>>> 
>>> /**
>>>  * Set a sequence of parameter values. Use a default SQL type determined by
>>>  * the type of the value argument. The value is captured and should not be
>>>  * modified before the {@link Operation} is completed.
>>>  *
>>>  * The Operation is completed exceptionally with ClassCastException if any of
>>>  * the values cannot be converted to the specified SQL type.
>>>  *
>>>  * @param id the identifier of the parameter marker to be set
>>>  * @param values the value the parameter is to be set to
>>>  * @return this {@link Operation}
>>>  * @throws IllegalArgumentException if the length of value is not the same as
>>>  * the length of the previously set parameter sequences or if the same id was
>>>  * passed in a previous call.
>>>  * @throws IllegalStateException if the {@link Operation} has been submitted
>>>  */
>>> public ArrayCountOperation<T> set(String id, List<?> values);
>>> 
>>> /**
>>>  * Set a sequence of parameter values. The first parameter is captured and
>>>  * should not be modified before the {@link Operation} is completed.
>>>  *
>>>  * The Operation is completed exceptionally with ClassCastException if any of
>>>  * the values cannot be converted to the specified SQL type.
>>>  *
>>>  * @param <S> the Java type of the individual parameter values
>>>  * @param id the identifier of the parameter marker to be set
>>>  * @param values the value the parameter is to be set to
>>>  * @param type the SQL type of the value to send to the database
>>>  * @return this Operation
>>>  * @throws IllegalArgumentException if the length of value is not the same as
>>>  * the length of the previously set parameter sequences or if the same id was
>>>  * passed in a previous call.
>>>  * @throws IllegalStateException if the {@link Operation} has been submitted
>>>  */
>>> public <S> ArrayCountOperation<T> set(String id, S[] values, SqlType type);
>>> 
>>> /**
>>>  * Set a sequence of parameter values. Use a default SQL type determined by
>>>  * the type of the value argument. The parameter is captured and should not be
>>>  * modified before the {@link Operation} is completed.
>>>  *
>>>  * The Operation is completed exceptionally with ClassCastException if any of
>>>  * the values cannot be converted to the specified SQL type.
>>>  *
>>>  * @param <S> the Java type of the individual parameter values
>>>  * @param id the identifier of the parameter marker to be set
>>>  * @param values the value the parameter is to be set to
>>>  * @return this Operation
>>>  * @throws IllegalArgumentException if the length of value is not the same as
>>>  * the length of the previously set parameter sequences or if the same id was
>>>  * passed in a previous call.
>>>  * @throws IllegalStateException if the {@link Operation} has been submitted
>>>  */
>>> public <S> ArrayCountOperation<T> set(String id, S[] values);
>>> 
>>> /**
>>>  * Provide a source for a sequence of parameter values.
>>>  *
>>>  * This Operation is not executed until source is completed normally. If
>>>  * source completes exceptionally this Operation completes exceptionally with
>>>  * an IllegealArgumentException with the source's exception as the cause.
>>>  *
>>>  * The Operation is completed exceptionally with ClassCastException if any of
>>>  * the values of the source cannot be converted to the specified SQL type.
>>>  *
>>>  * If the length of the value of source is not the same as the length of all
>>>  * other parameter sequences this Operation is completed exceptionally with
>>>  * IllegalArgumentException.
>>>  *
>>>  * @param id the identifier of the parameter marker to be set
>>>  * @param source supplies the values the parameter is to be set to
>>>  * @param type the SQL type of the value to send to the database
>>>  * @return this Operation
>>>  * @throws IllegalArgumentException if the same id was passed in a previous
>>>  * call.
>>>  * @throws IllegalStateException if the {@link Operation} has been submitted
>>>  */
>>> public ArrayCountOperation<T> set(String id, CompletionStage<?> source, SqlType type);
>>> 
>>> /**
>>>  * Provide a source for a sequence of parameter values. Use a default SQL type
>>>  * determined by the element type of the value of the source.
>>>  *
>>>  * This Operation is not executed until source is completed normally. If
>>>  * source completes exceptionally this Operation completes exceptionally with
>>>  * an IllegealArgumentException with the source's exception as the cause.
>>>  *
>>>  * The Operation is completed exceptionally with ClassCastException if any of
>>>  * the values of the source cannot be converted to the specified SQL type.
>>>  *
>>>  * If the length of the value of source is not the same as the length of all
>>>  * other parameter sequences this Operation is completed exceptionally with
>>>  * IllegalArgumentException.
>>>  *
>>>  * @param id the identifier of the parameter marker to be set
>>>  * @param source supplies the values the parameter is to be set to
>>>  * @return this {@link Operation}
>>>  * @throws IllegalArgumentException if the same id was passed in a previous
>>>  * call.
>>>  * @throws IllegalStateException if the {@link Operation} has been submitted
>>>  */
>>> public ArrayCountOperation<T> set(String id, CompletionStage<?> source);
>>> 
>>> /**
>>>  * A supplier for the value passed to the {@link countAggregator} for the
>>>  * first count processed.
>>>  *
>>>  * The default initial value is {@code () -< null}.
>>>  *
>>>  * @param supplier supplies the initial value for the count aggregator
>>>  * @return this {@link BatchCountOperation}
>>>  * @throws IllegalStateException if the {@link Operation} has been submitted
>>>  * or this method has been called previously.
>>>  */
>>> public ArrayCountOperation<T> initialValue(Supplier<? extends T> supplier);
>>> 
>>> /**
>>>  * A binary function that is applied to each count. The first argument is the
>>>  * result of the previous call or the result of calling initialValue supplier.
>>>  * The second argument is the count. Counts are used in the same order as they
>>>  * are produced by the database.
>>>  *
>>>  * The default value is {@code (t,c) -< null}.
>>>  *
>>>  * @param aggregator a binary function that aggregates the member results
>>>  * @return this {@link BatchCountOperation}
>>>  * @throws IllegalStateException if the {@link Operation} has been submitted
>>>  * or this method has been called previously.
>>>  */
>>> public ArrayCountOperation<T> countAggregator(BiFunction<? super T, Result.Count, ? extends T> aggregator);
>>> 
>>> @Override
>>> public Submission<T> submit();
>>> 
>>> @Override
>>> public ArrayCountOperation<T> onError(Consumer<Throwable> handler);
>>> 
>>> @Override
>>> public ArrayCountOperation<T> timeout(Duration minTime);
>>> 
>>> }
>>> 
>> 
>> <oracle_sig_logo.gif> <http://oracle.com/us/design/oracle-email-sig-198324.gif>
>> <http://oracle.com/us/design/oracle-email-sig-198324.gif> <http://oracle.com/us/design/oracle-email-sig-198324.gif>
>> <http://oracle.com/us/design/oracle-email-sig-198324.gif>Lance Andersen| Principal Member of Technical Staff | +1.781.442.2037
>> Oracle Java Engineering 
>> 1 Network Drive 
>> Burlington, MA 01803
>> Lance.Andersen at oracle.com <mailto:Lance.Andersen at oracle.com>
>> 
>> 
>> 
>