001package org.itsallcode.jdbc;
002
003import java.sql.Connection;
004import java.sql.PreparedStatement;
005import java.util.Iterator;
006import java.util.List;
007import java.util.stream.Stream;
008
009import org.itsallcode.jdbc.batch.*;
010import org.itsallcode.jdbc.resultset.RowMapper;
011import org.itsallcode.jdbc.resultset.SimpleResultSet;
012import org.itsallcode.jdbc.resultset.generic.Row;
013
014/**
015 * Interface containing various DB operations. Use one of the implementations
016 * {@link SimpleConnection} or {@link Transaction}.
017 */
018public interface DbOperations extends AutoCloseable {
019
020    /**
021     * Execute all commands in a SQL script, separated with {@code ;}.
022     * 
023     * @param sqlScript script to execute.
024     */
025    void executeScript(final String sqlScript);
026
027    /**
028     * Execute a single SQL statement.
029     * 
030     * @param sql SQL statement
031     * @return either the row count for SQL Data Manipulation Language (DML)
032     *         statements or 0 for SQL statements that return nothing
033     */
034    int executeUpdate(final String sql);
035
036    /**
037     * Execute a single SQL statement as a prepared statement with placeholders.
038     * <p>
039     * This will use {@link PreparedStatement#setObject(int, Object)} for setting
040     * parameters. If you need more control, use
041     * {@link #executeUpdate(String, PreparedStatementSetter)}.
042     * 
043     * @param sql        SQL statement
044     * @param parameters parameters to set in the prepared statement
045     * @return either the row count for SQL Data Manipulation Language (DML)
046     *         statements or 0 for SQL statements that return nothing
047     */
048    default int executeUpdate(final String sql, final List<Object> parameters) {
049        return this.executeUpdate(sql, new GenericParameterSetter(parameters));
050    }
051
052    /**
053     * Execute a single SQL statement as a prepared statement with placeholders.
054     * 
055     * @param sql                     SQL statement
056     * @param preparedStatementSetter prepared statement setter
057     * @return either the row count for SQL Data Manipulation Language (DML)
058     *         statements or 0 for SQL statements that return nothing
059     */
060    int executeUpdate(final String sql, PreparedStatementSetter preparedStatementSetter);
061
062    /**
063     * Execute a SQL query and return a {@link SimpleResultSet result set} with
064     * generic {@link Row}s.
065     * 
066     * @param sql SQL query
067     * @return result set
068     */
069    SimpleResultSet<Row> query(final String sql);
070
071    /**
072     * Execute a SQL query and return a {@link SimpleResultSet result set} with rows
073     * converted to a custom type {@link T} using the given {@link RowMapper}.
074     * 
075     * @param <T>       generic row type
076     * @param sql       SQL query
077     * @param rowMapper row mapper
078     * @return the result set
079     */
080    default <T> SimpleResultSet<T> query(final String sql, final RowMapper<T> rowMapper) {
081        return query(sql, ps -> {
082        }, rowMapper);
083    }
084
085    /**
086     * Execute a SQL query, set parameters and return a {@link SimpleResultSet
087     * result set} with rows converted to a custom type {@link T}.
088     * <p>
089     * This will use {@link PreparedStatement#setObject(int, Object)} for setting
090     * parameters. If you need more control, use
091     * {@link #executeUpdate(String, PreparedStatementSetter)}.
092     * 
093     * @param <T>        generic row type
094     * @param sql        SQL query
095     * @param parameters parameters to set in the prepared statement
096     * @param rowMapper  row mapper
097     * @return the result set
098     */
099    default <T> SimpleResultSet<T> query(final String sql, final List<Object> parameters,
100            final RowMapper<T> rowMapper) {
101        return this.query(sql, new GenericParameterSetter(parameters), rowMapper);
102    }
103
104    /**
105     * Execute a SQL query, set parameters and return a {@link SimpleResultSet
106     * result set} with rows converted to a custom type {@link T}.
107     * 
108     * @param <T>                     generic row type
109     * @param sql                     SQL query
110     * @param preparedStatementSetter the prepared statement setter
111     * @param rowMapper               row mapper
112     * @return the result set
113     */
114    <T> SimpleResultSet<T> query(final String sql, final PreparedStatementSetter preparedStatementSetter,
115            final RowMapper<T> rowMapper);
116
117    /**
118     * Create a batch statement builder for executing multiple statements in a
119     * batch.
120     * 
121     * @return batch statement builder
122     */
123    StatementBatchBuilder statementBatch();
124
125    /**
126     * Create a prepared statement batch builder for inserting or updating rows by
127     * directly setting values of a {@link PreparedStatement}.
128     * <p>
129     * If you want to insert rows from an {@link Iterator} or a {@link Stream}, use
130     * {@link #preparedStatementBatch(Class)}.
131     * 
132     * @return batch insert builder
133     */
134    PreparedStatementBatchBuilder preparedStatementBatch();
135
136    /**
137     * Create a row-based prepared statement batch builder for inserting or updating
138     * rows from an {@link Iterator} or a {@link Stream}.
139     * <p>
140     * If you want to insert rows by directly setting values of a
141     * {@link PreparedStatement}, use {@link #preparedStatementBatch()}.
142     * 
143     * @param rowType row type
144     * @param <T>     row type
145     * @return row-based batch insert builder
146     */
147    <T> RowPreparedStatementBatchBuilder<T> preparedStatementBatch(final Class<T> rowType);
148
149    /**
150     * Get the original wrapped connection.
151     * <p>
152     * Use this in case of missing features in {@link DbOperations}.
153     * 
154     * @return original wrapped connection
155     */
156    Connection getOriginalConnection();
157
158    @Override
159    void close();
160}