From c4ff0263b23ffc6d8e3294eec73f128b89ea6c4d Mon Sep 17 00:00:00 2001 From: Roland Haeder Date: Thu, 13 Aug 2015 12:07:33 +0200 Subject: [PATCH] =?utf8?q?A=20lot=20changes=20on=20jcore:=20-=20Implemente?= =?utf8?q?d=20doSelectByCriteria()=20in=20MySQL=20backend=20-=20Close=20on?= =?utf8?q?ly=20connection=20in=20same=20backend=20if=20it=20is=20still=20o?= =?utf8?q?pen=20-=20Added=20method=20size()=20to=20Result/DatabaseResult?= =?utf8?q?=20-=20Moved=20member=20entrySet()=20in=20BaseCriteria=20to=20pr?= =?utf8?q?oper=20location=20-=20Fixed=20comments=20of=20interface=20Criter?= =?utf8?q?ia=20and=20SearchableCriteria=20-=20Some=20trace/debug=20message?= =?utf8?q?s=20added=20-=20Now=20database=20frontends=20have=20a=20new=20me?= =?utf8?q?thod=20getResultFromSet()=20which=20transforms=20a=20ResultSet?= =?utf8?q?=20(SQL)=20to=20a=20Result=20(jcore)=20-=20Sorted=20class/interf?= =?utf8?q?ace=20imports=20Signed-off-by:Roland=20H=C3=A4der=20?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../mxchange/jcore/BaseFrameworkSystem.java | 6 + .../mxchange/jcore/criteria/BaseCriteria.java | 20 +-- src/org/mxchange/jcore/criteria/Criteria.java | 4 +- .../criteria/searchable/SearchCriteria.java | 2 +- .../searchable/SearchableCritera.java | 14 +-- .../database/backend/DatabaseBackend.java | 3 +- .../base64/Base64CsvDatabaseBackend.java | 4 +- .../backend/mysql/MySqlDatabaseBackend.java | 115 ++++++++++++++---- .../database/frontend/DatabaseFrontend.java | 11 ++ .../jcore/database/result/DatabaseResult.java | 8 ++ .../jcore/database/result/Result.java | 7 ++ 11 files changed, 143 insertions(+), 51 deletions(-) diff --git a/src/org/mxchange/jcore/BaseFrameworkSystem.java b/src/org/mxchange/jcore/BaseFrameworkSystem.java index 88f598a..515981d 100644 --- a/src/org/mxchange/jcore/BaseFrameworkSystem.java +++ b/src/org/mxchange/jcore/BaseFrameworkSystem.java @@ -735,6 +735,9 @@ public class BaseFrameworkSystem implements FrameworkInterface { * Initializes i18n bundles */ protected void initBundle () { + // Trace message + this.getLogger().trace("CALLED!"); + // Is the bundle set? if (bundle instanceof ResourceBundle) { // Is already set @@ -743,6 +746,9 @@ public class BaseFrameworkSystem implements FrameworkInterface { // Set instance bundle = ResourceBundle.getBundle(FrameworkInterface.I18N_BUNDLE_FILE); // NOI18N + + // Trace message + this.getLogger().trace("EXIT!"); } /** diff --git a/src/org/mxchange/jcore/criteria/BaseCriteria.java b/src/org/mxchange/jcore/criteria/BaseCriteria.java index 395faba..c95311b 100644 --- a/src/org/mxchange/jcore/criteria/BaseCriteria.java +++ b/src/org/mxchange/jcore/criteria/BaseCriteria.java @@ -47,24 +47,24 @@ public class BaseCriteria extends BaseFrameworkSystem implements Criteria { } /** - * Gets all values from underlaying map in an iterator. + * Gets all entries as a key-value pair * - * @return Values iteratable + * @return Key-value paira of all entries */ @Override - public Iterable values () { - // Call map's method - return this.criteria.values(); - + public Set> entrySet () { + return this.criteria.entrySet(); } /** - * Gets all entries as a key-value pair + * Gets all values from underlaying map in an iterator. * - * @return Key-value paira of all entries + * @return Values iteratable */ @Override - public Set> entrySet () { - return this.criteria.entrySet(); + public Iterable values () { + // Call map's method + return this.criteria.values(); + } } diff --git a/src/org/mxchange/jcore/criteria/Criteria.java b/src/org/mxchange/jcore/criteria/Criteria.java index 0016a50..74a36b6 100644 --- a/src/org/mxchange/jcore/criteria/Criteria.java +++ b/src/org/mxchange/jcore/criteria/Criteria.java @@ -1,5 +1,3 @@ -package org.mxchange.jcore.criteria; - /* * Copyright (C) 2015 Roland Haeder * @@ -16,7 +14,7 @@ package org.mxchange.jcore.criteria; * You should have received a copy of the GNU General Public License * along with this program. If not, see . */ - +package org.mxchange.jcore.criteria; import java.util.Map; import java.util.Set; diff --git a/src/org/mxchange/jcore/criteria/searchable/SearchCriteria.java b/src/org/mxchange/jcore/criteria/searchable/SearchCriteria.java index 96a637f..ee26956 100644 --- a/src/org/mxchange/jcore/criteria/searchable/SearchCriteria.java +++ b/src/org/mxchange/jcore/criteria/searchable/SearchCriteria.java @@ -34,7 +34,7 @@ public class SearchCriteria extends BaseCriteria implements SearchableCritera { } @Override - public boolean matchesAnd (final Storeable storeable) { + public boolean matches (final Storeable storeable) { // Trace message this.getLogger().trace(MessageFormat.format("storeable={0} - CALLED!", storeable)); diff --git a/src/org/mxchange/jcore/criteria/searchable/SearchableCritera.java b/src/org/mxchange/jcore/criteria/searchable/SearchableCritera.java index 2447e2b..bc8992e 100644 --- a/src/org/mxchange/jcore/criteria/searchable/SearchableCritera.java +++ b/src/org/mxchange/jcore/criteria/searchable/SearchableCritera.java @@ -1,8 +1,3 @@ -package org.mxchange.jcore.criteria.searchable; - -import org.mxchange.jcore.criteria.Criteria; -import org.mxchange.jcore.database.storage.Storeable; - /* * Copyright (C) 2015 Roland Haeder * @@ -19,7 +14,10 @@ import org.mxchange.jcore.database.storage.Storeable; * You should have received a copy of the GNU General Public License * along with this program. If not, see . */ +package org.mxchange.jcore.criteria.searchable; +import org.mxchange.jcore.criteria.Criteria; +import org.mxchange.jcore.database.storage.Storeable; /** * A searchable criteria @@ -29,10 +27,10 @@ import org.mxchange.jcore.database.storage.Storeable; public interface SearchableCritera extends Criteria { /** - * Checks if the given instance of a Storeable class matchesAnd all criteria + * Checks if the given instance of a Storeable class matches * * @param storeable A Storeable instance to check - * @return Whether the Storeable instance matchesAnd all criteria + * @return Whether the Storeable instance matches */ - public boolean matchesAnd (final Storeable storeable); + public boolean matches (final Storeable storeable); } diff --git a/src/org/mxchange/jcore/database/backend/DatabaseBackend.java b/src/org/mxchange/jcore/database/backend/DatabaseBackend.java index 0dd7dac..1391a67 100644 --- a/src/org/mxchange/jcore/database/backend/DatabaseBackend.java +++ b/src/org/mxchange/jcore/database/backend/DatabaseBackend.java @@ -47,8 +47,9 @@ public interface DatabaseBackend extends FrameworkInterface { * @return A result instance * @throws java.io.IOException If any IO error occurs * @throws org.mxchange.jcore.exceptions.BadTokenException If a bad token was found + * @throws java.sql.SQLException If any SQL error occurs */ - public Result doSelectByCriteria (final SearchableCritera critera) throws IOException, BadTokenException; + public Result doSelectByCriteria (final SearchableCritera critera) throws IOException, BadTokenException, SQLException; /** * Shuts down this backend diff --git a/src/org/mxchange/jcore/database/backend/base64/Base64CsvDatabaseBackend.java b/src/org/mxchange/jcore/database/backend/base64/Base64CsvDatabaseBackend.java index 1d3bdc7..0913d9c 100644 --- a/src/org/mxchange/jcore/database/backend/base64/Base64CsvDatabaseBackend.java +++ b/src/org/mxchange/jcore/database/backend/base64/Base64CsvDatabaseBackend.java @@ -128,8 +128,8 @@ public class Base64CsvDatabaseBackend extends BaseDatabaseBackend implements Dat // Debug message this.getLogger().debug(MessageFormat.format("storeable={0}", storeable)); - // Now matchesAnd the found instance - if (critera.matchesAnd(storeable)) { + // Now matches the found instance + if (critera.matches(storeable)) { // Then add it to result result.add(storeable); } diff --git a/src/org/mxchange/jcore/database/backend/mysql/MySqlDatabaseBackend.java b/src/org/mxchange/jcore/database/backend/mysql/MySqlDatabaseBackend.java index c87f46f..7f19012 100644 --- a/src/org/mxchange/jcore/database/backend/mysql/MySqlDatabaseBackend.java +++ b/src/org/mxchange/jcore/database/backend/mysql/MySqlDatabaseBackend.java @@ -19,9 +19,13 @@ package org.mxchange.jcore.database.backend.mysql; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; -import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.Statement; import java.text.MessageFormat; +import java.util.Iterator; +import java.util.Map; +import java.util.Set; import org.mxchange.jcore.criteria.searchable.SearchableCritera; import org.mxchange.jcore.database.backend.BaseDatabaseBackend; import org.mxchange.jcore.database.backend.DatabaseBackend; @@ -41,11 +45,6 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas */ private static Connection connection; - /** - * Prepared statement for full row count - */ - private PreparedStatement totalRowCount; - /** * Constructor with table name * @@ -54,7 +53,7 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas */ public MySqlDatabaseBackend (final DatabaseFrontend frontend) throws UnsupportedDatabaseDriverException { // Trace message - this.getLogger().trace(MessageFormat.format("frontend={0} - CALLED!", frontend)); + this.getLogger().trace(MessageFormat.format("frontend={0} - CALLED!", frontend)); //NOI18N // Validate driver this.validateDriver("mysql"); //NOI18N @@ -63,10 +62,13 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas String tableName = frontend.getTableName(); // Debug message - this.getLogger().debug(MessageFormat.format("tableName={0}", tableName)); + this.getLogger().debug(MessageFormat.format("tableName={0}", tableName)); //NOI18N // Now that the driver is there, set the table name this.setTableName(tableName); + + // Set frontend + this.setFrontend(frontend); } @Override @@ -75,8 +77,14 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas this.getLogger().trace("CALLED!"); //NOI18N // Is the connection already there? - if (MySqlDatabaseBackend.connection instanceof Connection) { - // Already connected + if (connection instanceof Connection) { + // Is the connection really up? + if (connection.isClosed()) { + // Connection is closed again + throw new SQLException("Connection is closed."); //NOI18N + } + + // Already connected this.getLogger().debug("Connection is already established."); //NOI18N // No need to connect @@ -93,39 +101,94 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas this.getLogger().debug(MessageFormat.format("Attempting to connect to {0} ...", connect)); //NOI18N // Now get a connection instance back - MySqlDatabaseBackend.connection = DriverManager.getConnection( + connection = DriverManager.getConnection( connect, this.getProperty("database.mysql.login"), //NOI18N this.getProperty("database.mysql.password") //NOI18N ); - // Is the connection really up? - if (MySqlDatabaseBackend.connection.isClosed()) { - // Connection is closed again - throw new SQLException("Connection is closed."); //NOI18N - } - // Debug message this.getLogger().debug("Connection is up, preparing some statements ..."); //NOI18N - // Here, the connection is established, so prepare some statements - this.totalRowCount = MySqlDatabaseBackend.connection.prepareStatement(String.format("SELECT COUNT(`id`) AS `cnt` FROM `%s` LIMIT 1", this.getTableName())); //NOI18N - // Trace message this.getLogger().trace("EXIT!"); //NOI18N } @Override - public Result doSelectByCriteria (final SearchableCritera critera) { - throw new UnsupportedOperationException(MessageFormat.format("Not supported yet: criteria={0}", critera)); + public Result doSelectByCriteria (final SearchableCritera critera) throws SQLException { + // Trace message + this.getLogger().trace(MessageFormat.format("criteria={0} - CALLED!", critera)); + + // Start SELECT query + StringBuilder query = new StringBuilder(String.format("SELECT * FROM `%s`", this.getTableName())); + + // Get entry set + Set> set = critera.entrySet(); + + // Debug message + this.getLogger().debug(MessageFormat.format("set.isEmpty()={0}", set.isEmpty())); + + // Are there conditions? + if (!set.isEmpty()) { + // Continue with WHERE + query.append(" WHERE "); + + // Get iterator + Iterator> iterator = set.iterator(); + + // "Walk through all + while (iterator.hasNext()) { + // Get element + Map.Entry entry = iterator.next(); + + // Add key as column name + query.append(String.format("`%s`", entry.getKey())); + + // Get value + Object value = entry.getValue(); + + // Debug message + this.getLogger().debug(MessageFormat.format("value={0}", value)); + + // Which type has the value? + if (value instanceof Boolean) { + // Boolean value + query.append(String.format("=%s", value.toString())); + } else { + // Cannot handle this + throw new SQLException(MessageFormat.format("Cannot handle value={0} for key={1} in table {2}", value, entry.getKey(), this.getTableName())); + } + } + } + + // Full statement is complete here, better log it + this.getLogger().debug(MessageFormat.format("query={0} is complete.", query)); + + // Prepare statement instance + Statement statement = connection.createStatement(); + + // Run it + ResultSet resultSet = statement.executeQuery(query.toString()); + + // 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 { - // This should not happen: - assert(MySqlDatabaseBackend.connection instanceof Connection) : MessageFormat.format("connection is not valid: {0}", MySqlDatabaseBackend.connection); //NOI18N + // Trace message + this.getLogger().trace("CALLED!"); //NOI18N + + // Is the connection still there? + if (!connection.isClosed()) { + // Close down database connection + connection.close(); + } - // Close down database connection - MySqlDatabaseBackend.connection.close(); + // Trace message + this.getLogger().trace("EXIT!"); //NOI18N } } diff --git a/src/org/mxchange/jcore/database/frontend/DatabaseFrontend.java b/src/org/mxchange/jcore/database/frontend/DatabaseFrontend.java index 2d2e645..5286215 100644 --- a/src/org/mxchange/jcore/database/frontend/DatabaseFrontend.java +++ b/src/org/mxchange/jcore/database/frontend/DatabaseFrontend.java @@ -17,8 +17,10 @@ package org.mxchange.jcore.database.frontend; import java.io.IOException; +import java.sql.ResultSet; import java.sql.SQLException; import org.mxchange.jcore.FrameworkInterface; +import org.mxchange.jcore.database.result.Result; import org.mxchange.jcore.database.storage.Storeable; import org.mxchange.jcore.exceptions.BadTokenException; @@ -28,6 +30,15 @@ import org.mxchange.jcore.exceptions.BadTokenException; * @author Roland Haeder */ public interface DatabaseFrontend extends FrameworkInterface { + /** + * 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 + */ + public Result getResultFromSet (final ResultSet resultSet) throws SQLException; + /** * Parses given line from database backend into a Storeable instance. Please * note that not all backends need this. diff --git a/src/org/mxchange/jcore/database/result/DatabaseResult.java b/src/org/mxchange/jcore/database/result/DatabaseResult.java index abddc78..c8dca70 100644 --- a/src/org/mxchange/jcore/database/result/DatabaseResult.java +++ b/src/org/mxchange/jcore/database/result/DatabaseResult.java @@ -36,6 +36,9 @@ public class DatabaseResult extends BaseFrameworkSystem implements Result(); } @@ -51,6 +54,11 @@ public class DatabaseResult extends BaseFrameworkSystem implements Result extends FrameworkInterface, Iterato * @param storeable An instance of a Storeable class */ public void add (final Storeable storeable); + + /** + * Returns size of result + * + * @return Size of result + */ + public int size (); } -- 2.39.5