impl.H2DatabaseConnection Maven / Gradle / Ivy
package impl;
import api.Column;
import api.DataChangeListener;
import api.sql.SQLDatabaseConnection;
import api.sql.SQLSchema;
import api.sql.SQLTable;
import api.sql.constraint.Constraint;
import api.sql.constraint.NotNull;
import api.sql.constraint.PrimaryKey;
import impl.schema.SchemaDescriptionTable;
import java.sql.*;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicBoolean;
import java.util.function.Consumer;
import java.util.function.Predicate;
import java.util.stream.Collectors;
import java.util.stream.Stream;
public abstract class H2DatabaseConnection implements SQLDatabaseConnection {
private static class H2DatabaseColumn implements Column{
private final String name;
private final String dataType;
private H2DatabaseColumn(String name, String dataType) {
this.name = name;
this.dataType = dataType;
}
@Override
public String getName() {
return name;
}
@Override
public String getDatatype() {
return dataType;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Column)) return false;
Column that = (Column) o;
return name.equalsIgnoreCase(that.getName()) &&
dataType.equalsIgnoreCase(that.getDatatype());
}
@Override
public int hashCode() {
return Objects.hash(name, dataType);
}
@Override
public String toString() {
return "H2DatabaseColumn{" +
"name='" + name + '\'' +
", dataType='" + dataType + '\'' +
'}';
}
}
private static class H2DatabaseTable implements SQLTable{
private final Set columns = new HashSet<>();
private final Set constraints = new HashSet<>();
private final String name;
private H2DatabaseTable(String name) {
assert name != null;
this.name = name;
}
@Override
public Set getConstraints() {
return constraints;
}
@Override
public String getName() {
return name;
}
@Override
public Set getColumns() {
return columns;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
H2DatabaseTable that = (H2DatabaseTable) o;
return Objects.equals(columns, that.columns) &&
Objects.equals(constraints, that.constraints) &&
Objects.equals(name, that.name);
}
@Override
public int hashCode() {
return Objects.hash(columns, constraints, name);
}
@Override
public String toString() {
return "H2DatabaseTable{" +
"columns=" + columns +
", constraints=" + constraints +
", name='" + name + '\'' +
'}';
}
}
private final Set> dataChangeListeners = ConcurrentHashMap.newKeySet();
protected abstract String buildDatabaseUrl();
/**
* Initializes the database according to the given schema. Tables which are mentioned in the
* schema will be dropped if they already exist. All other tables will not be touched by this
* commmand.
* @param schema The schema that the database have to fulfill
*/
@Override
public void init(SQLSchema schema) {
try(Connection connection = DriverManager.getConnection(buildDatabaseUrl())) {
List tableNames = new LinkedList<>();
try(Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
ResultSet constraintQueryResult = statement.executeQuery("SHOW TABLES");
if (constraintQueryResult.last()) {
constraintQueryResult.beforeFirst();
while (constraintQueryResult.next()) {
tableNames.add(constraintQueryResult.getString("TABLE_NAME"));
}
}
}
tableNames.forEach(new Consumer() {
@Override
public void accept(String tableName) {
try(Statement statement = connection.createStatement()){
statement.execute(String.format("DROP TABLE %s CASCADE", tableName));
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
});
initDatabase(schema, connection);
SQLTable table = new SchemaDescriptionTable();
StringBuilder queryString = new StringBuilder(String.format("CREATE TABLE IF NOT EXISTS %s (", table.getName()));
List columnList = new ArrayList<>(table.getColumns());
for (int i = 0; i < columnList.size(); i++){
if (i > 0){
queryString.append(", ");
}
queryString.append(String.format("%s %s", columnList.get(i).getName(), columnList.get(i).getDatatype()));
appendConstraints(table, queryString, columnList.get(i));
}
queryString.append(")");
try (Statement statement = connection.createStatement()){
statement.execute(queryString.toString());
}
saveDatabaseStructure(schema);
dataChangeListeners.forEach(listener -> listener.initiated(schema));
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
private void saveDatabaseStructure(SQLSchema schema) {
SchemaDescriptionTable schemaDescriptionTable = new SchemaDescriptionTable();
schema.getTables().forEach(sqlTable -> {
String tableName = sqlTable.getName();
List columns = new ArrayList<>(sqlTable.getColumns());
columns.forEach(column -> {
Map columnValueMap = new HashMap<>();
columnValueMap.put(SchemaDescriptionTable.ID, new Value(tableName+column.getName()));
columnValueMap.put(SchemaDescriptionTable.TABLE, new Value(tableName));
columnValueMap.put(SchemaDescriptionTable.COLUMN, new Value(column.getName()));
columnValueMap.put(SchemaDescriptionTable.DATATYPE, new Value(column.getDatatype()));
Row row = new Row(columnValueMap, schemaDescriptionTable);
insert(row);
});
});
}
private void initDatabase(SQLSchema schema, Connection connection) {
schema.getTables().forEach(table -> {
try(Statement statement = connection.createStatement()){
statement.execute(String.format("DROP TABLE IF EXISTS %s CASCADE", table.getName()));
} catch (SQLException e) {
throw new RuntimeException(e);
}
StringBuilder columnDefinition = buildColumnDefinitionString(table);
try(Statement statement = connection.createStatement()){
String queryString = String.format("CREATE TABLE %s (%s)", table.getName(), columnDefinition);
statement.execute(queryString);
} catch (SQLException e) {
throw new RuntimeException(e);
}
});
}
private StringBuilder buildColumnDefinitionString(SQLTable table) {
List columns = new ArrayList<>(table.getColumns());
StringBuilder columnDefinition = new StringBuilder();
for (int i = 0; i < columns.size(); i++){
if (i > 0){
columnDefinition.append(",");
}
columnDefinition.append(String.format("`%s` %s", columns.get(i).getName(), columns.get(i).getDatatype()));
appendConstraints(table, columnDefinition, columns.get(i));
}
return columnDefinition;
}
@Override
public void insert(Row data) {
try(Connection connection = DriverManager.getConnection(buildDatabaseUrl())){
StringBuilder columnNames = new StringBuilder();
StringBuilder valuesHolder = new StringBuilder();
List values = new ArrayList<>(data.getColumns().size());
final boolean[] isFirstRow = {true};
data.getColumns().forEach(column -> {
if (!isFirstRow[0]){
columnNames.append(",");
valuesHolder.append(",");
}
columnNames.append(column.getName());
valuesHolder.append("?");
Optional value = data.get(column);
assert value.isPresent();
values.add(value.get());
isFirstRow[0] = false;
});
String query = String.format("INSERT INTO %s (%s) VALUES (%s)", data.getTable().getName(), columnNames, valuesHolder);
try(PreparedStatement statement = connection.prepareStatement(query)){
for (int i = 0; i < values.size(); i++){
statement.setString(i+1, values.get(i).get());
}
statement.execute();
}
dataChangeListeners.forEach(listener -> listener.inserted(data));
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public void update(Row data, Predicate predicate) {
if (data.getColumns().size() < 1){
return;
}
Stream rowsToUpdate = read(predicate);
try(Connection connection = DriverManager.getConnection(buildDatabaseUrl())){
rowsToUpdate.forEach(row -> {
List columnValues = new ArrayList<>(data.getColumns().size());
List whereValues = new ArrayList<>(row.getColumns().size());
StringBuilder queryString = new StringBuilder();
queryString.append(String.format("UPDATE %s SET", row.getTable().getName()));
StringBuilder setClause = new StringBuilder();
data.getColumns().forEach(column -> {
Optional value = data.get(column);
if (!value.isPresent()){
return; //ignore columns for which no value is given
}
if (setClause.length() > 0){
setClause.append(" ,");
}
setClause.append(String.format(" %s=?", column.getName()));
columnValues.add(value.get());
});
queryString.append(setClause.toString());
if (row.getColumns().size() > 0){
queryString.append(" WHERE");
StringBuilder whereClause = new StringBuilder();
row.getColumns().forEach(column -> {
if (!row.get(column).isPresent()){
return;
}
if (whereClause.length() > 0){
whereClause.append(" AND");
}
whereClause.append(String.format(" %s=?", column.getName()));
whereValues.add(row.get(column).get());
});
queryString.append(whereClause.toString());
}
try(PreparedStatement statement = connection.prepareStatement(queryString.toString())){
for (int i = 0; i < columnValues.size(); i++){
statement.setString(i+1, columnValues.get(i).get());
}
for (int i = 0; i < whereValues.size(); i++){
statement.setString(i+columnValues.size()+1, whereValues.get(i).get());
}
statement.execute();
} catch (SQLException e) {
throw new RuntimeException(e);
}
dataChangeListeners.forEach(listener -> listener.updated(row, data));
});
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public void delete(Predicate predicate) {
Stream rowsToDelete = read(predicate);
try(Connection connection = DriverManager.getConnection(buildDatabaseUrl())){
rowsToDelete.forEach(row -> {
List values = new ArrayList<>(row.getColumns().size());
StringBuilder queryString = new StringBuilder();
queryString.append(String.format("DELETE FROM %s", row.getTable().getName()));
if (row.getColumns().size() > 0){
queryString.append(" WHERE");
StringBuilder conditions = new StringBuilder();
row.getColumns().forEach(new Consumer() {
@Override
public void accept(Column column) {
if (!row.get(column).isPresent()){
return;
}
Value value = row.get(column).get();
if (conditions.length() > 0){
conditions.append(" AND");
}
conditions.append(String.format(" %s=?", column.getName()));
values.add(value);
}
});
queryString.append(conditions.toString());
}
try(PreparedStatement statement = connection.prepareStatement(queryString.toString())){
for (int i = 0; i < values.size(); i++){
statement.setString(i+1, values.get(i).get());
}
statement.execute();
} catch (SQLException e) {
throw new RuntimeException(e);
}
dataChangeListeners.forEach(listener -> listener.deleted(row));
});
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public boolean isInitiated(SQLSchema schema) {
class TableFilter implements Predicate{
private final SchemaDescriptionTable schemaDescriptionTable = new SchemaDescriptionTable();
private final SQLSchema schema;
TableFilter(SQLSchema schema) {
assert schema != null;
this.schema = schema;
}
@Override
public boolean test(Row row) {
if (!row.getTable().getName().equalsIgnoreCase(schemaDescriptionTable.getName())) {
return false;
}
Optional column = row.getColumns().stream().filter(
column1 -> column1.getName().equalsIgnoreCase(SchemaDescriptionTable.TABLE.getName())
).findAny();
//noinspection OptionalIsPresent
if (!column.isPresent()){
return false;
}
return schema.getTables().parallelStream().anyMatch(sqlTable -> {
Optional columnValue = row.get(column.get());
assert columnValue.isPresent();
return columnValue.get().get().equalsIgnoreCase(sqlTable.getName());
});
}
}
List rows = read(new TableFilter(schema)).collect(Collectors.toList());
AtomicBoolean allMatch = new AtomicBoolean(true);
schema.getTables().forEach(sqlTable -> {
List tableRows = rows.parallelStream().filter(row -> {
Optional tableName = row.get(SchemaDescriptionTable.TABLE);
assert tableName.isPresent();
return tableName.get().get().equalsIgnoreCase(sqlTable.getName());
}).collect(Collectors.toList());
boolean match = sqlTable.getColumns().stream().allMatch(column -> tableRows.parallelStream().anyMatch(row -> {
Optional columnName = row.get(SchemaDescriptionTable.COLUMN);
assert columnName.isPresent();
if (!columnName.get().get().equalsIgnoreCase(column.getName())) {
return false;
}
Optional columnDataType = row.get(SchemaDescriptionTable.DATATYPE);
assert columnDataType.isPresent();
return columnDataType.get().get().equalsIgnoreCase(column.getDatatype());
}));
if (!match){
allMatch.set(false);
}
});
return allMatch.get();
}
@Override
public Stream read(Predicate predicate) {
try(Connection connection = DriverManager.getConnection(buildDatabaseUrl())) {
List tables = queryTableNames(connection);
if (tables.isEmpty()){
return Stream.empty();
}
List rows = new LinkedList<>();
tables.parallelStream().forEach(tableName -> {
H2DatabaseTable table;
try{
table = queryTableDefinition(connection, tableName);
} catch (SQLException e){
throw new RuntimeException(e);
}
try(Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
ResultSet constraintQueryResult = statement.executeQuery(String.format("SELECT * FROM %s", tableName));
if (constraintQueryResult.last()){
constraintQueryResult.beforeFirst();
while (constraintQueryResult.next()){
Map columnValues = new HashMap<>();
table.columns.forEach(column -> {
try {
String valueString = constraintQueryResult.getString(column.getName());
Value value = new Value("");
if (valueString != null){
value = new Value(valueString);
}
columnValues.put(column, value);
} catch (SQLException e) {
throw new RuntimeException(e);
}
});
Row row = new Row(columnValues, table);
if (predicate.test(row)){
rows.add(row);
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
});
return rows.stream();
} catch (SQLException e){
throw new RuntimeException(e);
}
}
private H2DatabaseTable queryTableDefinition(Connection connection, String tableName) throws SQLException{
H2DatabaseTable table = new H2DatabaseTable(tableName);
try(Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
ResultSet constraintQueryResult = statement.executeQuery(String.format(
"select * from information_schema.columns where table_name = '%s';", tableName));
if (constraintQueryResult.last()){
//columns have been found for the current table
constraintQueryResult.beforeFirst();
while (constraintQueryResult.next()){
String columnName = constraintQueryResult.getString("COLUMN_NAME");
String[] dataTypeParts = constraintQueryResult.getString("COLUMN_TYPE").split(" ");
String dataType = dataTypeParts[0];
table.columns.add(new H2DatabaseColumn(columnName, dataType));
}
}
}
return table;
}
private List queryTableNames(Connection connection) throws SQLException {
List tables = new LinkedList<>();
try (Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) {
ResultSet tablesQueryResult = statement.executeQuery("SHOW TABLES");
if (!tablesQueryResult.last()) {
return Collections.emptyList();
}
tablesQueryResult.beforeFirst();
while (tablesQueryResult.next()) {
tables.add(tablesQueryResult.getString(1));
}
}
return tables;
}
/**
* Appends the constraint string to the given column definition string
* @param table The table containing the constraints
* @param columnDefinition The column definition string the constraints shall be appended to
* @param column The column the constraints refer to
*/
private void appendConstraints(SQLTable table, StringBuilder columnDefinition, Column column) {
Set constraints = table.getConstraints().stream().filter(
constraint -> Objects.equals(constraint.getConstrainedColumn(), column)
).collect(Collectors.toSet());
constraints.forEach(constraint -> {
if (constraint instanceof PrimaryKey){
columnDefinition.append(" PRIMARY KEY");
}
if (constraint instanceof NotNull){
columnDefinition.append(" NOT NULL");
}
});
}
@Override
public void registerDataChangeListener(DataChangeListener dataChangeListener) {
this.dataChangeListeners.add(dataChangeListener);
}
@Override
public void unregisterDataChangeListener(DataChangeListener dataChangeListener) {
this.dataChangeListeners.remove(dataChangeListener);
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy