plugins/org.eclipse.app4mc.atdb/src/org/eclipse/app4mc/atdb/DBConnection.java - app4mc/org.eclipse.app4mc - Git at Google

 /**
  ********************************************************************************
  * Copyright (c) 2020 Eclipse APP4MC contributors.
  *
  * This program and the accompanying materials are made
  * available under the terms of the Eclipse Public License 2.0
  * which is available at https://www.eclipse.org/legal/epl-2.0/
  *
  * SPDX-License-Identifier: EPL-2.0
  *
  ********************************************************************************
  */

 package org.eclipse.app4mc.atdb;

 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ParameterMetaData;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 import java.util.Arrays;
 import java.util.Collection;
 import java.util.LinkedHashMap;
 import java.util.LinkedHashSet;
 import java.util.List;
 import java.util.Map;
 import java.util.Properties;
 import java.util.Set;
 import java.util.stream.Stream;
 import java.util.stream.Stream.Builder;

 public class DBConnection<T extends DBConnection<T>> implements AutoCloseable {

 	private static final String TABLE_EXISTS_QUERY = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?;";

 	private final Connection connection;

 	private final Map<String, PreparedStatement> prepStmts;
 	private final Collection<Statement> stmts;
 	private final Set<PreparedStatement> currentBatches;
 	private boolean isInBatchMode = false;

 	/**
 	 * Creates a connection to the given db file in read-only mode.
 	 *
 	 * @param dbFile The path to the db file.
 	 * @throws SQLException
 	 */
 	public DBConnection(final String dbFile) throws SQLException {
 		this(dbFile, false);
 	}

 	/**
 	 * Creates a connection to the given db file.
 	 *
 	 * @param dbFile The path to the db file.
 	 * @param writeAccess Specify <code>TRUE</code> to open the db connection with write access. <code>FALSE</code> for read-only mode.
 	 * @throws SQLException
 	 */
 	public DBConnection(final String dbFile, final boolean writeAccess) throws SQLException {
 		try {
 			Class.forName("org.sqlite.JDBC"); // init JDBC driver
 		} catch (ClassNotFoundException e) {
 			// fail silently. DriverManager will throw an SQL exception complaining about a missing driver
 		}
 		final String dbFileUrl = "jdbc:sqlite:" + dbFile;
 		if (!writeAccess) {
 			final Properties properties = new Properties();
 			properties.put("open_mode", "1"); //$NON-NLS-1$ //$NON-NLS-2$
 			this.connection = DriverManager.getConnection(dbFileUrl, properties);
 		} else {
 			this.connection = DriverManager.getConnection(dbFileUrl);
 			try (final Statement statement = this.connection.createStatement()) {
 				statement.setQueryTimeout(30); // set timeout to 30 sec.
 				statement.executeUpdate("PRAGMA foreign_keys = ON;"); // enable foreign key checks
 			}
 		}
 		this.prepStmts = new LinkedHashMap<>();
 		this.stmts = new LinkedHashSet<>();
 		this.currentBatches = new LinkedHashSet<>();
 	}

 	/**
 	 * Closes all statements created by this connection. Then closes the connection to the data base itself.
 	 */
 	@Override
 	public void close() throws SQLException {
 		for(final PreparedStatement ps:this.prepStmts.values()) {
 			ps.close();
 		}
 		this.prepStmts.clear();
 		this.connection.close();
 	}

 	/**
 	 * Executes the statement (changing the contents of the db) given in the query.
 	 *
 	 * @param query The statement that performs the changes in the db.
 	 * @return <code>TRUE</code> if the statement was executed by the db, <code>FALSE</code> otherwise.
 	 * @throws SQLException
 	 */
 	public void executeUpdate(final String query) throws SQLException {
 		try (final Statement statement = this.connection.createStatement()) {
 			statement.setQueryTimeout(30); // set timeout to 30 sec.
 			statement.executeUpdate(query);
 		}
 	}

 	/**
 	 * Same as {@link java.util.function.Consumer}, but may throw exception of type E.
 	 * @param <T>
 	 * @param <E>
 	 */
 	@FunctionalInterface
 	public static interface ThrowingConsumer<T, E extends Exception> {
 	    void accept(T t) throws E;
 	}

 	/**
 	 * Executes a read-only query and consumes the result set via the given consumer function.
 	 *
 	 * @param query The read-only query to execute.
 	 * @param consumer The function that consumes the result set.
 	 * @throws SQLException
 	 */
 	public void queryAndConsumeResult(final String query, final ThrowingConsumer<ResultSet, SQLException> consumer) throws SQLException {
 		try (final Statement tmpStatement = this.connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
 			final ResultSet resultSet = tmpStatement.executeQuery(query);
 			consumer.accept(resultSet);
 		}
 	}

 	/**
 	 * Same as {@link java.util.function.Function}, but may throw exception of type E.
 	 * @param <T>
 	 * @param <R>
 	 * @param <E>
 	 */
 	@FunctionalInterface
 	public static interface ThrowingFunction<T, R, E extends Exception> {
 	    R apply(T t) throws E;
 	}

 	/**
 	 * Subclasses can use this method to send a prepared statement query to the data base, map each result row via the rowMapper parameter, and
 	 * return the result for further processing.
 	 *
 	 * @param <R> Target type for the row mapper and returned stream.
 	 * @param query The prepared statement query
 	 * @param rowMapper The result row mapping function
 	 * @return Stream of mapped row results.
 	 * @throws SQLException
 	 */
 	protected <R> Stream<R> queryAndMapToStream(final PreparedStatement query, final ThrowingFunction<ResultSet, R, SQLException> rowMapper)
 			throws SQLException {
 		try (final ResultSet resultSet = query.executeQuery()) {
 			final Builder<R> result = Stream.builder();
 			while (resultSet.next()) {
 				result.accept(rowMapper.apply(resultSet));
 			}
 			return result.build();
 		}
 	}

 	/**
 	 * Returns a prepared statement for the the given query. Will return an existing one if the query was already created as a prepared statement.
 	 * Since the prepared statement is internally cached, this DBConnection will also close it, when the connection is closed. Thus, the user must not
 	 * close the prepared statement.
 	 *
 	 * @param query The query to put into the prepared statement.
 	 * @return The prepared statement for the given query.
 	 * @throws SQLException
 	 */
 	public PreparedStatement getPreparedStatementFor(final String query) throws SQLException {
 		if (!this.prepStmts.containsKey(query)) {
 			this.prepStmts.put(query, this.connection.prepareStatement(query));
 		}
 		return this.prepStmts.get(query);
 	}

 	/**
 	 * Returns a prepared query for the given query string. This query is only allowed read access to the data base. Will return an existing one if the query
 	 * was already created as a prepared query. Since the prepared query is internally cached, this DBConnection will also close it, when the connection is
 	 * closed. Thus, the user must not close the prepared query.
 	 *
 	 * @param query The query string to put into the prepared query.
 	 * @return The prepared query for the given query string.
 	 * @throws SQLException
 	 */
 	public PreparedStatement getPrepareQueryFor(final String query) throws SQLException {
 		if (!this.prepStmts.containsKey(query)) {
 			this.prepStmts.put(query, this.connection.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY));
 		}
 		return this.prepStmts.get(query);
 	}

 	/**
 	 * Creates and returns a new statement. This DBConection will also close the statement, when the connection is closed. Thus, the user must not close the
 	 * statement.
 	 *
 	 * @return A newly created statement.
 	 * @throws SQLException
 	 */
 	public Statement createStatement() throws SQLException {
 		final Statement stmt = this.connection.createStatement();
 		this.stmts.add(stmt);
 		return stmt;
 	}

 	/**
 	 * Executes the given statement batches (calls {@link Statement#executeBatch()} on each provided statement).
 	 *
 	 * @param statements Batches of statements to be executed.
 	 * @throws SQLException
 	 */
 	public void executeBatchStatements(final Statement...statements) throws SQLException {
 		executeBatchStatements(Arrays.asList(statements));
 	}

 	/**
 	 * Executes the given statement batches (calls {@link Statement#executeBatch()} on each provided statement).
 	 *
 	 * @param statements Batches of statements to be executed.
 	 * @throws SQLException
 	 */
 	public void executeBatchStatements(final Collection<? extends Statement> statements) throws SQLException {
 		final boolean oldAutoCommit = this.connection.getAutoCommit();
 		if (oldAutoCommit) this.connection.setAutoCommit(false);
 		try {
 			for(final Statement stmt:statements) {
 				stmt.executeBatch();
 			}
 		} finally {
 			this.connection.setAutoCommit(oldAutoCommit);
 		}
 	}

 	/**
 	 * Checks whether a table with the given tableName exists in the data base.
 	 *
 	 * @param tableName The name of the table to check for existence.
 	 * @return <code>TRUE</code> if a table with the specified name exists, <code>FALSE</code> otherwise.
 	 * @throws SQLException
 	 */
 	public boolean tableExists(final String tableName) throws SQLException {
 		final PreparedStatement prepStmt = getPrepareQueryFor(TABLE_EXISTS_QUERY);
 		prepStmt.setString(1, tableName);
 		try (final ResultSet resultSet = prepStmt.executeQuery()) {
 			return resultSet.next();
 		}
 	}

 	/**
 	 * Can be used to execute updating statements (the ones that change the data base) in batches. These batches are only built up by calling
 	 * {@link #executeBatchablePrepStmt(String, List)} (from a subclass, since it is protected).
 	 *
 	 * @param batchConsumer The consumer function in which all updating statements can be called to be batch executed.
 	 * @throws SQLException
 	 */
 	public void executeBatchUpdate(final ThrowingConsumer<T, SQLException> batchConsumer) throws SQLException {
 		this.isInBatchMode = true;
 		// cast is safe because of recursive generic T
 		@SuppressWarnings("unchecked")
 		final T self = (T)this;
 		batchConsumer.accept(self);
 		executeBatchStatements(this.currentBatches);
 		this.currentBatches.clear();
 		this.isInBatchMode = false;
 	}

 	/**
 	 * Subclasses can call this method to update the data base via a prepared statement with parameters.
 	 *
 	 * @param prepStmt The prepared statement used to update the data base.
 	 * @param parameters The parameters for the prepared statement.
 	 * @throws SQLException
 	 */
 	protected void executeBatchablePrepStmt(final String prepStmt, final List<?> parameters) throws SQLException {
 		final PreparedStatement stmt = getPreparedStatementFor(prepStmt);
 		final ParameterMetaData pmd = stmt.getParameterMetaData();
 		if (pmd.getParameterCount() == parameters.size()) {
 			for(int i = 0; i < parameters.size(); i++) {
 				stmt.setObject(i+1, parameters.get(i));
 			}
 		}
 		if (this.isInBatchMode) {
 			stmt.addBatch();
 			this.currentBatches.add(stmt);
 		} else {
 			stmt.executeUpdate();
 		}
 	}

 }
	/**
	********************************************************************************
	* Copyright (c) 2020 Eclipse APP4MC contributors.
	*
	* This program and the accompanying materials are made
	* available under the terms of the Eclipse Public License 2.0
	* which is available at https://www.eclipse.org/legal/epl-2.0/
	*
	* SPDX-License-Identifier: EPL-2.0
	*
	********************************************************************************
	*/

	package org.eclipse.app4mc.atdb;

	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ParameterMetaData;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.Arrays;
	import java.util.Collection;
	import java.util.LinkedHashMap;
	import java.util.LinkedHashSet;
	import java.util.List;
	import java.util.Map;
	import java.util.Properties;
	import java.util.Set;
	import java.util.stream.Stream;
	import java.util.stream.Stream.Builder;

	public class DBConnection<T extends DBConnection<T>> implements AutoCloseable {

	private static final String TABLE_EXISTS_QUERY = "SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?;";

	private final Connection connection;

	private final Map<String, PreparedStatement> prepStmts;
	private final Collection<Statement> stmts;
	private final Set<PreparedStatement> currentBatches;
	private boolean isInBatchMode = false;

	/**
	* Creates a connection to the given db file in read-only mode.
	*
	* @param dbFile The path to the db file.
	* @throws SQLException
	*/
	public DBConnection(final String dbFile) throws SQLException {
	this(dbFile, false);
	}

	/**
	* Creates a connection to the given db file.
	*
	* @param dbFile The path to the db file.
	* @param writeAccess Specify <code>TRUE</code> to open the db connection with write access. <code>FALSE</code> for read-only mode.
	* @throws SQLException
	*/
	public DBConnection(final String dbFile, final boolean writeAccess) throws SQLException {
	try {
	Class.forName("org.sqlite.JDBC"); // init JDBC driver
	} catch (ClassNotFoundException e) {
	// fail silently. DriverManager will throw an SQL exception complaining about a missing driver
	}
	final String dbFileUrl = "jdbc:sqlite:" + dbFile;
	if (!writeAccess) {
	final Properties properties = new Properties();
	properties.put("open_mode", "1"); //$NON-NLS-1$ //$NON-NLS-2$
	this.connection = DriverManager.getConnection(dbFileUrl, properties);
	} else {
	this.connection = DriverManager.getConnection(dbFileUrl);
	try (final Statement statement = this.connection.createStatement()) {
	statement.setQueryTimeout(30); // set timeout to 30 sec.
	statement.executeUpdate("PRAGMA foreign_keys = ON;"); // enable foreign key checks
	}
	}
	this.prepStmts = new LinkedHashMap<>();
	this.stmts = new LinkedHashSet<>();
	this.currentBatches = new LinkedHashSet<>();
	}

	/**
	* Closes all statements created by this connection. Then closes the connection to the data base itself.
	*/
	@Override
	public void close() throws SQLException {
	for(final PreparedStatement ps:this.prepStmts.values()) {
	ps.close();
	}
	this.prepStmts.clear();
	this.connection.close();
	}

	/**
	* Executes the statement (changing the contents of the db) given in the query.
	*
	* @param query The statement that performs the changes in the db.
	* @return <code>TRUE</code> if the statement was executed by the db, <code>FALSE</code> otherwise.
	* @throws SQLException
	*/
	public void executeUpdate(final String query) throws SQLException {
	try (final Statement statement = this.connection.createStatement()) {
	statement.setQueryTimeout(30); // set timeout to 30 sec.
	statement.executeUpdate(query);
	}
	}

	/**
	* Same as {@link java.util.function.Consumer}, but may throw exception of type E.
	* @param <T>
	* @param <E>
	*/
	@FunctionalInterface
	public static interface ThrowingConsumer<T, E extends Exception> {
	void accept(T t) throws E;
	}

	/**
	* Executes a read-only query and consumes the result set via the given consumer function.
	*
	* @param query The read-only query to execute.
	* @param consumer The function that consumes the result set.
	* @throws SQLException
	*/
	public void queryAndConsumeResult(final String query, final ThrowingConsumer<ResultSet, SQLException> consumer) throws SQLException {
	try (final Statement tmpStatement = this.connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {
	final ResultSet resultSet = tmpStatement.executeQuery(query);
	consumer.accept(resultSet);
	}
	}

	/**
	* Same as {@link java.util.function.Function}, but may throw exception of type E.
	* @param <T>
	* @param <R>
	* @param <E>
	*/
	@FunctionalInterface
	public static interface ThrowingFunction<T, R, E extends Exception> {
	R apply(T t) throws E;
	}

	/**
	* Subclasses can use this method to send a prepared statement query to the data base, map each result row via the rowMapper parameter, and
	* return the result for further processing.
	*
	* @param <R> Target type for the row mapper and returned stream.
	* @param query The prepared statement query
	* @param rowMapper The result row mapping function
	* @return Stream of mapped row results.
	* @throws SQLException
	*/
	protected <R> Stream<R> queryAndMapToStream(final PreparedStatement query, final ThrowingFunction<ResultSet, R, SQLException> rowMapper)
	throws SQLException {
	try (final ResultSet resultSet = query.executeQuery()) {
	final Builder<R> result = Stream.builder();
	while (resultSet.next()) {
	result.accept(rowMapper.apply(resultSet));
	}
	return result.build();
	}
	}

	/**
	* Returns a prepared statement for the the given query. Will return an existing one if the query was already created as a prepared statement.
	* Since the prepared statement is internally cached, this DBConnection will also close it, when the connection is closed. Thus, the user must not
	* close the prepared statement.
	*
	* @param query The query to put into the prepared statement.
	* @return The prepared statement for the given query.
	* @throws SQLException
	*/
	public PreparedStatement getPreparedStatementFor(final String query) throws SQLException {
	if (!this.prepStmts.containsKey(query)) {
	this.prepStmts.put(query, this.connection.prepareStatement(query));
	}
	return this.prepStmts.get(query);
	}

	/**
	* Returns a prepared query for the given query string. This query is only allowed read access to the data base. Will return an existing one if the query
	* was already created as a prepared query. Since the prepared query is internally cached, this DBConnection will also close it, when the connection is
	* closed. Thus, the user must not close the prepared query.
	*
	* @param query The query string to put into the prepared query.
	* @return The prepared query for the given query string.
	* @throws SQLException
	*/
	public PreparedStatement getPrepareQueryFor(final String query) throws SQLException {
	if (!this.prepStmts.containsKey(query)) {
	this.prepStmts.put(query, this.connection.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY));
	}
	return this.prepStmts.get(query);
	}

	/**
	* Creates and returns a new statement. This DBConection will also close the statement, when the connection is closed. Thus, the user must not close the
	* statement.
	*
	* @return A newly created statement.
	* @throws SQLException
	*/
	public Statement createStatement() throws SQLException {
	final Statement stmt = this.connection.createStatement();
	this.stmts.add(stmt);
	return stmt;
	}

	/**
	* Executes the given statement batches (calls {@link Statement#executeBatch()} on each provided statement).
	*
	* @param statements Batches of statements to be executed.
	* @throws SQLException
	*/
	public void executeBatchStatements(final Statement...statements) throws SQLException {
	executeBatchStatements(Arrays.asList(statements));
	}

	/**
	* Executes the given statement batches (calls {@link Statement#executeBatch()} on each provided statement).
	*
	* @param statements Batches of statements to be executed.
	* @throws SQLException
	*/
	public void executeBatchStatements(final Collection<? extends Statement> statements) throws SQLException {
	final boolean oldAutoCommit = this.connection.getAutoCommit();
	if (oldAutoCommit) this.connection.setAutoCommit(false);
	try {
	for(final Statement stmt:statements) {
	stmt.executeBatch();
	}
	} finally {
	this.connection.setAutoCommit(oldAutoCommit);
	}
	}

	/**
	* Checks whether a table with the given tableName exists in the data base.
	*
	* @param tableName The name of the table to check for existence.
	* @return <code>TRUE</code> if a table with the specified name exists, <code>FALSE</code> otherwise.
	* @throws SQLException
	*/
	public boolean tableExists(final String tableName) throws SQLException {
	final PreparedStatement prepStmt = getPrepareQueryFor(TABLE_EXISTS_QUERY);
	prepStmt.setString(1, tableName);
	try (final ResultSet resultSet = prepStmt.executeQuery()) {
	return resultSet.next();
	}
	}

	/**
	* Can be used to execute updating statements (the ones that change the data base) in batches. These batches are only built up by calling
	* {@link #executeBatchablePrepStmt(String, List)} (from a subclass, since it is protected).
	*
	* @param batchConsumer The consumer function in which all updating statements can be called to be batch executed.
	* @throws SQLException
	*/
	public void executeBatchUpdate(final ThrowingConsumer<T, SQLException> batchConsumer) throws SQLException {
	this.isInBatchMode = true;
	// cast is safe because of recursive generic T
	@SuppressWarnings("unchecked")
	final T self = (T)this;
	batchConsumer.accept(self);
	executeBatchStatements(this.currentBatches);
	this.currentBatches.clear();
	this.isInBatchMode = false;
	}

	/**
	* Subclasses can call this method to update the data base via a prepared statement with parameters.
	*
	* @param prepStmt The prepared statement used to update the data base.
	* @param parameters The parameters for the prepared statement.
	* @throws SQLException
	*/
	protected void executeBatchablePrepStmt(final String prepStmt, final List<?> parameters) throws SQLException {
	final PreparedStatement stmt = getPreparedStatementFor(prepStmt);
	final ParameterMetaData pmd = stmt.getParameterMetaData();
	if (pmd.getParameterCount() == parameters.size()) {
	for(int i = 0; i < parameters.size(); i++) {
	stmt.setObject(i+1, parameters.get(i));
	}
	}
	if (this.isInBatchMode) {
	stmt.addBatch();
	this.currentBatches.add(stmt);
	} else {
	stmt.executeUpdate();
	}
	}

	}