From bad6dce76c7a893168d4d64c53dd4040eda337ef Mon Sep 17 00:00:00 2001 From: Roland Haeder Date: Fri, 14 Aug 2015 10:49:44 +0200 Subject: [PATCH] =?utf8?q?Continued=20with=20jcore:=20-=20Added=20method?= =?utf8?q?=20doInsertDataSet()=20-=20Introduced=20getPreparedStatement()?= =?utf8?q?=20-=20Added=20new=20constructor=20in=20DatabaseResult=20for=20s?= =?utf8?q?tatus=20code=20and=20SQL=20warnings=20from=20previous=20executeQ?= =?utf8?q?uery()=20call=20Signed-off-by:Roland=20H=C3=A4der=20?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../database/backend/DatabaseBackend.java | 10 ++ .../base64/Base64CsvDatabaseBackend.java | 6 + .../backend/mysql/MySqlDatabaseBackend.java | 167 ++++++++++++++---- .../frontend/BaseDatabaseFrontend.java | 98 ++++++++-- .../jcore/database/result/DatabaseResult.java | 46 ++++- 5 files changed, 273 insertions(+), 54 deletions(-) diff --git a/src/org/mxchange/jcore/database/backend/DatabaseBackend.java b/src/org/mxchange/jcore/database/backend/DatabaseBackend.java index 1391a67..a1e7a06 100644 --- a/src/org/mxchange/jcore/database/backend/DatabaseBackend.java +++ b/src/org/mxchange/jcore/database/backend/DatabaseBackend.java @@ -18,6 +18,7 @@ package org.mxchange.jcore.database.backend; import java.io.IOException; import java.sql.SQLException; +import java.util.Map; import org.mxchange.jcore.FrameworkInterface; import org.mxchange.jcore.criteria.searchable.SearchableCritera; import org.mxchange.jcore.database.result.Result; @@ -38,6 +39,15 @@ public interface DatabaseBackend extends FrameworkInterface { */ public void connectToDatabase () throws SQLException; + /** + * Inserts given dataset instance and returns a Result instance on success + * + * @param dataset A dataset instance + * @return An instance of Result + * @throws java.sql.SQLException If any SQL error occurs + */ + public Result doInsertDataSet (final Map dataset) throws SQLException; + /** * Run a "SELECT" statement with given criteria and always return a Result * instance. The result instance then provides methods to iterate over all diff --git a/src/org/mxchange/jcore/database/backend/base64/Base64CsvDatabaseBackend.java b/src/org/mxchange/jcore/database/backend/base64/Base64CsvDatabaseBackend.java index 0913d9c..d754c53 100644 --- a/src/org/mxchange/jcore/database/backend/base64/Base64CsvDatabaseBackend.java +++ b/src/org/mxchange/jcore/database/backend/base64/Base64CsvDatabaseBackend.java @@ -22,6 +22,7 @@ import java.io.RandomAccessFile; import java.text.MessageFormat; import java.util.ArrayList; import java.util.List; +import java.util.Map; import javax.xml.bind.DatatypeConverter; import org.mxchange.jcore.FrameworkInterface; import org.mxchange.jcore.criteria.searchable.SearchableCritera; @@ -94,6 +95,11 @@ public class Base64CsvDatabaseBackend extends BaseDatabaseBackend implements Dat // Empty body } + @Override + public Result doInsertDataSet (final Map dataset) { + throw new UnsupportedOperationException(MessageFormat.format("Not supported yet: dataset={0}", dataset)); + } + /** * Searches for given criteria over a file-based database. This method does * always return a Result instance and never null. diff --git a/src/org/mxchange/jcore/database/backend/mysql/MySqlDatabaseBackend.java b/src/org/mxchange/jcore/database/backend/mysql/MySqlDatabaseBackend.java index c1a38e6..8d27643 100644 --- a/src/org/mxchange/jcore/database/backend/mysql/MySqlDatabaseBackend.java +++ b/src/org/mxchange/jcore/database/backend/mysql/MySqlDatabaseBackend.java @@ -31,6 +31,7 @@ import org.mxchange.jcore.criteria.searchable.SearchableCritera; import org.mxchange.jcore.database.backend.BaseDatabaseBackend; import org.mxchange.jcore.database.backend.DatabaseBackend; import org.mxchange.jcore.database.frontend.DatabaseFrontend; +import org.mxchange.jcore.database.result.DatabaseResult; import org.mxchange.jcore.database.result.Result; import org.mxchange.jcore.database.storage.Storeable; import org.mxchange.jcore.exceptions.UnsupportedDatabaseDriverException; @@ -115,6 +116,88 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas this.getLogger().trace("EXIT!"); //NOI18N } + /** + * Inserts given dataset instance and returns a Result instance on success. + * Please note that this method can insert only a single record into + * database. Multiple inserts are not yet supported. + * + * @param dataset A dataset instance + * @return An instance of Result + * @todo Support more than one record being inserted in a separate method + */ + @Override + public Result doInsertDataSet (final Map dataset) throws SQLException { + // Trace message + this.getLogger().trace(MessageFormat.format("dataset={0} - CALLED!", dataset)); + + // dataset should not be null and not empty + if (dataset == null) { + // It is null, so abort here + throw new NullPointerException("dataset is null"); + } else if (dataset.isEmpty()) { + // It is empty, also abort here + throw new IllegalArgumentException("dataset is empty"); + } + + // Debug message + this.getLogger().debug(MessageFormat.format("Need to parse {0} values ...", dataset.size())); + + // Init values + Set values = new LinkedHashSet<>(dataset.size()); + + // Start with INSERT INTO + StringBuilder query = new StringBuilder(String.format("INSERT INTO `%s` (", this.getTableName())); + StringBuilder valueQuery = new StringBuilder("("); + + // Get iterator from it + Iterator> iterator = dataset.entrySet().iterator(); + + // "Walk" over all entries + while (iterator.hasNext()) { + // Get next entry + Map.Entry entry = iterator.next(); + + // Add key as database column to query + query.append(String.format("`%s`,", entry.getKey())); + + // Get value + Object value = entry.getValue(); + + // Debug message + this.getLogger().debug(MessageFormat.format("value={0}", value)); + + // Add value + valueQuery.append("?,"); + values.add(value); + } + + // Now put all together + query.replace(query.length() - 1, query.length(), ") VALUES "); + query.append(valueQuery.substring(0, valueQuery.length() - 1)); + query.append(")"); + + // Full statement is complete here, better log it + this.getLogger().debug(MessageFormat.format("query={0} is complete.", query)); + + // Prepare statement instance + PreparedStatement statement = this.getPreparedStatement(query, values); + + // Run it + int status = statement.executeUpdate(); + + // Debug message + this.getLogger().debug(MessageFormat.format("status={0}", status)); + + // The result set needs to be transformed into Result, so initialize a result instance here + Result result = new DatabaseResult(status, statement); + + // Trace message + this.getLogger().trace(MessageFormat.format("result={0} - EXIT!", result)); + + // Return it + return result; + } + @Override public Result doSelectByCriteria (final SearchableCritera critera) throws SQLException { // Trace message @@ -160,19 +243,9 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas // Debug message this.getLogger().debug(MessageFormat.format("value={0}", value)); - // Which type has the value? - if (value instanceof Boolean) { - // Boolean value - query.append("=?"); - values.add(value); - } else if (value instanceof String) { - // String value - query.append("=?"); - values.add(value); - } else { - // Cannot handle this - throw new SQLException(MessageFormat.format("Cannot handle value={0} for key={1} in table {2}", value, entry.getKey(), this.getTableName())); - } + // Add value + query.append("=?"); + values.add(value); } } @@ -191,7 +264,49 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas // Full statement is complete here, better log it this.getLogger().debug(MessageFormat.format("query={0} is complete.", query)); - // Prepare statement instance + // Get a prepared instance + PreparedStatement statement = this.getPreparedStatement(query, values); + + // Run it + ResultSet resultSet = statement.executeQuery(); + + // The result set needs to be transformed into Result, so initialize a result instance here + Result result = this.getFrontend().getResultFromSet(resultSet); + + // Trace message + this.getLogger().trace(MessageFormat.format("result={0} - EXIT!", result)); + + // Return it + return result; + } + + @Override + public void doShutdown () throws SQLException, IOException { + // Trace message + this.getLogger().trace("CALLED!"); //NOI18N + + // Is the connection still there? + if (!connection.isClosed()) { + // Close down database connection + connection.close(); + } + + // Trace message + this.getLogger().trace("EXIT!"); //NOI18N + } + + /** + * Some "getter" for a prepared statement with inserted values + * + * @param query SQL query string + * @param values Values set + * @return A fully prepared statement + */ + private PreparedStatement getPreparedStatement (final StringBuilder query, final Set values) throws SQLException { + // Trace message + this.getLogger().trace("query=" + query + ",values=" + values + " - CALLED!"); + + // Init prepared statement PreparedStatement statement = connection.prepareStatement(query.toString()); // Debug message @@ -233,28 +348,10 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas index++; } - // Run it - ResultSet resultSet = statement.executeQuery(); - - // The result set needs to be transformed into Result, so initialize a result instance here - Result result = this.getFrontend().getResultFromSet(resultSet); - - // Return it - return result; - } - - @Override - public void doShutdown () throws SQLException, IOException { // Trace message - this.getLogger().trace("CALLED!"); //NOI18N + this.getLogger().trace(MessageFormat.format("statement={0} - EXIT!", statement)); - // Is the connection still there? - if (!connection.isClosed()) { - // Close down database connection - connection.close(); - } - - // Trace message - this.getLogger().trace("EXIT!"); //NOI18N + // Return it + return statement; } } diff --git a/src/org/mxchange/jcore/database/frontend/BaseDatabaseFrontend.java b/src/org/mxchange/jcore/database/frontend/BaseDatabaseFrontend.java index bf7971d..0554f71 100644 --- a/src/org/mxchange/jcore/database/frontend/BaseDatabaseFrontend.java +++ b/src/org/mxchange/jcore/database/frontend/BaseDatabaseFrontend.java @@ -19,6 +19,9 @@ package org.mxchange.jcore.database.frontend; import java.lang.reflect.InvocationTargetException; import java.sql.ResultSet; import java.sql.SQLException; +import java.text.MessageFormat; +import java.util.HashMap; +import java.util.Map; import org.mxchange.jcore.BaseFrameworkSystem; import org.mxchange.jcore.database.backend.DatabaseBackend; import org.mxchange.jcore.database.result.DatabaseResult; @@ -33,11 +36,75 @@ import org.mxchange.jcore.factory.database.backend.BackendFactory; * @author Roland Haeder */ public abstract class BaseDatabaseFrontend extends BaseFrameworkSystem implements DatabaseFrontend { + /** + * A current map instance for data sets being inseted + */ + private final Map dataset; /** * No instances from this class */ protected BaseDatabaseFrontend () { + // Init dataset instance + this.dataset = new HashMap<>(); + } + + /** + * Gets a Result back from given ResultSet instance + * + * @param resultSet ResultSet instance from SQL driver + * @return A typorized Result instance + * @throws java.sql.SQLException If an SQL error occurs + */ + @Override + public Result getResultFromSet (final ResultSet resultSet) throws SQLException { + // Init result instance + Result result = new DatabaseResult(); + + // Attach all possible warnings + result.setWarnings(resultSet.getWarnings()); + + // And return it + return result; + } + + /** + * Adds given key-value pair to dataset instance. The key must not be null, + * but value can be null. + * + * @param key Key to set + * @param value Value to set or null + */ + protected void addToDataSet (final String key, final String value) { + // Trace message + this.getLogger().trace(MessageFormat.format("key={0},value={1} - CALLED!", key, value)); + + // Is key null? + if (key == null) { + // Key is null + throw new NullPointerException("key is null"); + } + + // Add it to map + this.dataset.put(key, value); + + // Trace message + this.getLogger().trace("EXIT!"); + } + + /** + * Clears dataset instance. You should call this before you use it for + * inserting data into your database. + */ + protected void clearDataSet () { + // Trace message + this.getLogger().trace("CALLED!"); + + // Clear dataset + this.dataset.clear(); + + // Trace message + this.getLogger().trace("EXIT!"); } /** @@ -70,21 +137,30 @@ public abstract class BaseDatabaseFrontend extends BaseFrameworkSystem implement } /** - * Gets a Result back from given ResultSet instance + * Inserts currently filled dataset and returns the result of the operation. + * This may return no rows, but the affectedRows field may have been + * updated. * - * @param resultSet ResultSet instance from SQL driver - * @return A typorized Result instance - * @throws java.sql.SQLException If an SQL error occurs + * @return An instance of Result + * @throws java.sql.SQLException If any SQL error occurs */ - @Override - public Result getResultFromSet (final ResultSet resultSet) throws SQLException { - // Init result instance - Result result = new DatabaseResult(); + protected Result doInsertDataSet () throws SQLException { + // Trace message + this.getLogger().trace("CALLED!"); - // Attach all possible warnings - result.setWarnings(resultSet.getWarnings()); + // Is the dataset instance empty? + if (this.dataset.isEmpty()) { + // Cannot insert an empty dataset + throw new IllegalStateException("The dataset instance is empty."); + } - // And return it + // Call backend method + Result result = this.getBackend().doInsertDataSet(this.dataset); + + // Trace message + this.getLogger().trace(MessageFormat.format("result={0} - EXIT!", result)); + + // Return it return result; } } diff --git a/src/org/mxchange/jcore/database/result/DatabaseResult.java b/src/org/mxchange/jcore/database/result/DatabaseResult.java index efe8748..038ac42 100644 --- a/src/org/mxchange/jcore/database/result/DatabaseResult.java +++ b/src/org/mxchange/jcore/database/result/DatabaseResult.java @@ -16,7 +16,10 @@ */ package org.mxchange.jcore.database.result; +import java.sql.PreparedStatement; +import java.sql.SQLException; import java.sql.SQLWarning; +import java.text.MessageFormat; import java.util.Iterator; import java.util.SortedSet; import java.util.TreeSet; @@ -33,6 +36,11 @@ public class DatabaseResult extends BaseFrameworkSystem implements Result result; + /** + * Status value from previous executeUpdate() call + */ + private int status; + /** * SQLWarning instance */ @@ -49,13 +57,35 @@ public class DatabaseResult extends BaseFrameworkSystem implements Result(); } + /** + * A constructor that accepts a status integer from previous executeUpdate() + * call and a the prepared statement instance. + * + * @param status Status code + * @param statement A PreparedStatement instance + * @throws java.sql.SQLException If any SQL error occurs + */ + public DatabaseResult (final int status, final PreparedStatement statement) throws SQLException { + // Call parent constructor + this(); + + // Trace message + this.getLogger().trace(MessageFormat.format("status={0},statement={1} - CALLED!", status, statement)); + + // Set warnings + this.setWarnings(statement.getWarnings()); + + // Set status + this.status = status; + } + /** * Given Storeable instance as a query result. * * @param storeable An instance of a Storeable class */ @Override - public void add (final Storeable storeable) { + public final void add (final Storeable storeable) { // Add to result this.result.add(storeable); } @@ -66,7 +96,7 @@ public class DatabaseResult extends BaseFrameworkSystem implements Result iterator () { + public final Iterator iterator () { // Return iterator from result set return this.result.iterator(); } @Override - public Storeable next () { + public final Storeable next () { // Call iterator's method return this.iterator().next(); } @Override - public void remove () { + public final void remove () { // Call iterator's method this.iterator().remove(); } @Override - public int size () { + public final int size () { return this.result.size(); } } -- 2.39.5