src/org/mxchange/jcore/database/backend/mysql/MySqlDatabaseBackend.java

   1 /*
   2  * Copyright (C) 2015 Roland Haeder
   3  *
   4  * This program is free software: you can redistribute it and/or modify
   5  * it under the terms of the GNU General Public License as published by
   6  * the Free Software Foundation, either version 3 of the License, or
   7  * (at your option) any later version.
   8  *
   9  * This program is distributed in the hope that it will be useful,
  10  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  * GNU General Public License for more details.
  13  *
  14  * You should have received a copy of the GNU General Public License
  15  * along with this program.  If not, see <http://www.gnu.org/licenses/>.
  16  */
  17 package org.mxchange.jcore.database.backend.mysql;
  18
  19 import java.io.IOException;
  20 import java.sql.Connection;
  21 import java.sql.DriverManager;
  22 import java.sql.PreparedStatement;
  23 import java.sql.ResultSet;
  24 import java.sql.SQLException;
  25 import java.text.MessageFormat;
  26 import java.util.Iterator;
  27 import java.util.LinkedHashSet;
  28 import java.util.Map;
  29 import java.util.Set;
  30 import org.mxchange.jcore.criteria.searchable.SearchableCriteria;
  31 import org.mxchange.jcore.database.backend.BaseDatabaseBackend;
  32 import org.mxchange.jcore.database.backend.DatabaseBackend;
  33 import org.mxchange.jcore.database.frontend.DatabaseFrontend;
  34 import org.mxchange.jcore.database.result.DatabaseResult;
  35 import org.mxchange.jcore.database.result.Result;
  36 import org.mxchange.jcore.database.storage.Storeable;
  37 import org.mxchange.jcore.exceptions.UnsupportedDatabaseDriverException;
  38
  39 /**
  40  * A backend class for MySQL connections
  41  *
  42  * @author Roland Haeder
  43  */
  44 public class MySqlDatabaseBackend extends BaseDatabaseBackend implements DatabaseBackend {
  45         /**
  46          * An instance of a datbase connection
  47          */
  48         private static Connection connection;
  49
  50         /**
  51          * Prepared statement for total row count
  52          */
  53         private PreparedStatement totalRows;
  54
  55         /**
  56          * Constructor with table name
  57          *
  58          * @param frontend An instance of the frontend
  59          * @throws org.mxchange.jcore.exceptions.UnsupportedDatabaseDriverException If the requested driver is not supported
  60          */
  61         public MySqlDatabaseBackend (final DatabaseFrontend frontend) throws UnsupportedDatabaseDriverException {
  62                 // Trace message
  63                 this.getLogger().trace(MessageFormat.format("frontend={0} - CALLED!", frontend)); //NOI18N
  64
  65                 // Validate driver
  66                 this.validateDriver("mysql"); //NOI18N
  67
  68                 // Get table name
  69                 String tableName = frontend.getTableName();
  70
  71                 // Debug message
  72                 this.getLogger().debug(MessageFormat.format("tableName={0}", tableName)); //NOI18N
  73
  74                 // Now that the driver is there, set the table name
  75                 this.setTableName(tableName);
  76
  77                 // Set frontend
  78                 this.setFrontend(frontend);
  79         }
  80
  81         @Override
  82         public void connectToDatabase () throws SQLException {
  83                 // Trace message
  84                 this.getLogger().trace("CALLED!"); //NOI18N
  85
  86                 // Is the connection already there?
  87                 if (connection instanceof Connection) {
  88                         // Is the connection really up?
  89                         if (connection.isClosed()) {
  90                                 // Connection is closed again
  91                                 throw new SQLException("Connection is closed."); //NOI18N
  92                         }
  93
  94                         // Already connected
  95                         this.getLogger().debug("Connection is already established."); //NOI18N
  96
  97                         // No need to connect
  98                         return;
  99                 }
 100
 101                 // Generate connection string
 102                 String connect = String.format("jdbc:mysql://%s/%s", //NOI18N
 103                                 this.getProperty("database.mysql.host"), //NOI18N
 104                                 this.getProperty("database.mysql.dbname") //NOI18N
 105                 );
 106
 107                 // Debug message
 108                 this.getLogger().debug(MessageFormat.format("Attempting to connect to {0} ...", connect)); //NOI18N
 109
 110                 // Now get a connection instance back
 111                 connection = DriverManager.getConnection(
 112                                 connect,
 113                                 this.getProperty("database.mysql.login"), //NOI18N
 114                                 this.getProperty("database.mysql.password") //NOI18N
 115                 );
 116
 117                 // Debug message
 118                 this.getLogger().debug("Connection is up, preparing some statements ..."); //NOI18N
 119
 120                 // Set prepared statement
 121                 this.totalRows = connection.prepareStatement(String.format("SELECT COUNT(`%s`) AS `cnt` FROM `%s` LIMIT 1", this.getFrontend().getIdName(), this.getTableName())); //NOI18N
 122
 123                 // Trace message
 124                 this.getLogger().trace("EXIT!"); //NOI18N
 125         }
 126
 127         /**
 128          * Inserts given dataset instance and returns a Result instance on success.
 129          * Please note that this method can insert only a single record into
 130          * database. Multiple inserts are not yet supported.
 131          *
 132          * @param dataset A dataset instance
 133          * @return An instance of Result
 134          * @todo Support more than one record being inserted in a separate method
 135          */
 136         @Override
 137         public Result<? extends Storeable> doInsertDataSet (final Map<String, Object> dataset) throws SQLException {
 138                 // Trace message
 139                 this.getLogger().trace(MessageFormat.format("dataset={0} - CALLED!", dataset)); //NOI18N
 140
 141                 // dataset should not be null and not empty
 142                 if (dataset == null) {
 143                         // It is null, so abort here
 144                         throw new NullPointerException("dataset is null"); //NOI18N
 145                 } else if (dataset.isEmpty()) {
 146                         // It is empty, also abort here
 147                         throw new IllegalArgumentException("dataset is empty"); //NOI18N
 148                 }
 149
 150                 // Debug message
 151                 this.getLogger().debug(MessageFormat.format("Need to parse {0} values ...", dataset.size())); //NOI18N
 152
 153                 // Init values
 154                 Set<Object> values = new LinkedHashSet<>(dataset.size());
 155
 156                 // Start with INSERT INTO
 157                 StringBuilder query = new StringBuilder(String.format("INSERT INTO `%s` (", this.getTableName())); //NOI18N
 158                 StringBuilder valueQuery = new StringBuilder("("); //NOI18N
 159
 160                 // Get iterator from it
 161                 Iterator<Map.Entry<String, Object>> iterator = dataset.entrySet().iterator();
 162
 163                 // "Walk" over all entries
 164                 while (iterator.hasNext()) {
 165                         // Get next entry
 166                         Map.Entry<String, Object> entry = iterator.next();
 167
 168                         // Add key as database column to query
 169                         query.append(String.format("`%s`,", entry.getKey())); //NOI18N
 170
 171                         // Get value
 172                         Object value = entry.getValue();
 173
 174                         // Debug message
 175                         this.getLogger().debug(MessageFormat.format("value={0} - BEFORE!", value)); //NOI18N
 176
 177                         // Handle possible empty->null convertion
 178                         value = this.getFrontend().emptyStringToNull(entry.getKey(), value);
 179
 180                         // Debug message
 181                         this.getLogger().debug(MessageFormat.format("value={0} - AFTER!", value)); //NOI18N
 182
 183                         // Is the value null?
 184                         if (value == null) {
 185                                 // Add null
 186                                 valueQuery.append("NULL,"); //NOI18N
 187                         } else {
 188                                 // Add value
 189                                 valueQuery.append("?,"); //NOI18N
 190                                 values.add(value);
 191                         }
 192                 }
 193
 194                 // Now put all together
 195                 query.replace(query.length() - 1, query.length(), ") VALUES "); //NOI18N
 196                 query.append(valueQuery.substring(0, valueQuery.length() - 1));
 197                 query.append(")"); //NOI18N
 198
 199                 // Full statement is complete here, better log it
 200                 this.getLogger().debug(MessageFormat.format("query={0} is complete.", query)); //NOI18N
 201
 202                 // Prepare statement instance
 203                 PreparedStatement statement = this.getPreparedStatement(query, values);
 204
 205                 // Debug message
 206                 this.getLogger().debug(MessageFormat.format("statement={0}", statement));
 207
 208                 // Run it
 209                 int status = statement.executeUpdate();
 210
 211                 // Debug message
 212                 this.getLogger().debug(MessageFormat.format("status={0}", status)); //NOI18N
 213
 214                 // The result set needs to be transformed into Result, so initialize a result instance here
 215                 Result<? extends Storeable> result = new DatabaseResult(status, statement);
 216
 217                 // Trace message
 218                 this.getLogger().trace(MessageFormat.format("result={0} - EXIT!", result)); //NOI18N
 219
 220                 // Return it
 221                 return result;
 222         }
 223
 224         @Override
 225         public Result<? extends Storeable> doSelectByCriteria (final SearchableCriteria critera) throws SQLException {
 226                 // Trace message
 227                 this.getLogger().trace(MessageFormat.format("criteria={0} - CALLED!", critera)); //NOI18N
 228
 229                 // criteria must not be null
 230                 if (critera == null) {
 231                         // Abort here
 232                         throw new NullPointerException("criteria is null"); //NOI18N
 233                 }
 234
 235                 // Start SELECT query
 236                 StringBuilder query = new StringBuilder(String.format("SELECT * FROM `%s`", this.getTableName())); //NOI18N
 237
 238                 // Get entry set
 239                 Set<Map.Entry<String, Object>> set = critera.entrySet();
 240
 241                 // Debug message
 242                 this.getLogger().debug(MessageFormat.format("set.isEmpty()={0}", set.isEmpty())); //NOI18N
 243
 244                 // Init values
 245                 Set<Object> values = new LinkedHashSet<>(set.size());
 246
 247                 // Are there conditions?
 248                 if (!set.isEmpty()) {
 249                         // Continue with WHERE
 250                         query.append(" WHERE "); //NOI18N
 251
 252                         // No more than 1 value currently
 253                         if (set.size() > 1) {
 254                                 // Not supported yet
 255                                 throw new IllegalArgumentException("More than one criteria is not supported yet."); //NOI18N
 256                         }
 257
 258                         // Get iterator
 259                         Iterator<Map.Entry<String, Object>> iterator = set.iterator();
 260
 261                         // "Walk through all
 262                         while (iterator.hasNext()) {
 263                                 // Get element
 264                                 Map.Entry<String, Object> entry = iterator.next();
 265
 266                                 // Add key as column name
 267                                 query.append(String.format("`%s`", entry.getKey())); //NOI18N
 268
 269                                 // Get value
 270                                 Object value = entry.getValue();
 271
 272                                 // Debug message
 273                                 this.getLogger().debug(MessageFormat.format("value={0}", value)); //NOI18N
 274
 275                                 // Add value
 276                                 query.append("=?"); //NOI18N
 277                                 values.add(value);
 278                         }
 279                 }
 280
 281                 // Is limit set?
 282                 if (critera.getLimit() > 0) {
 283                         // Is skip set?
 284                         if (critera.getSkip() > 0) {
 285                                 // Limit with skip
 286                                 query.append(String.format(" LIMIT %d,%d", critera.getSkip(), critera.getLimit())); //NOI18N
 287                         } else {
 288                                 // Only limit
 289                                 query.append(String.format(" LIMIT %d", critera.getLimit())); //NOI18N
 290                         }
 291                 }
 292
 293                 // Full statement is complete here, better log it
 294                 this.getLogger().debug(MessageFormat.format("query={0} is complete.", query)); //NOI18N
 295
 296                 // Get a prepared instance
 297                 PreparedStatement statement = this.getPreparedStatement(query, values);
 298
 299                 // Debug message
 300                 this.getLogger().debug(MessageFormat.format("statement={0}", statement));
 301
 302                 // Run it
 303                 ResultSet resultSet = statement.executeQuery();
 304
 305                 // Debug message
 306                 this.getLogger().debug(MessageFormat.format("resultSet={0}", resultSet));
 307
 308                 // The result set needs to be transformed into Result, so initialize a result instance here
 309                 Result<? extends Storeable> result = this.getFrontend().getResultFromSet(resultSet);
 310
 311                 // Trace message
 312                 this.getLogger().trace(MessageFormat.format("result={0} - EXIT!", result)); //NOI18N
 313
 314                 // Return it
 315                 return result;
 316         }
 317
 318         @Override
 319         public void doShutdown () throws SQLException, IOException {
 320                 // Trace message
 321                 this.getLogger().trace("CALLED!"); //NOI18N
 322
 323                 // Is the connection still there?
 324                 if (!connection.isClosed()) {
 325                         // Close down database connection
 326                         connection.close();
 327                 }
 328
 329                 // Trace message
 330                 this.getLogger().trace("EXIT!"); //NOI18N
 331         }
 332
 333         @Override
 334         public int getTotalRows () throws IOException, SQLException {
 335                 // Trace message
 336                 this.getLogger().trace("CALLED!"); //NOI18N
 337
 338                 // Execute query
 339                 ResultSet set = this.totalRows.executeQuery();
 340
 341                 // Rewind to beginning
 342                 set.beforeFirst();
 343
 344                 // Get long
 345                 int count = set.getInt("cnt"); //NOI18N
 346
 347                 // Trace message
 348                 this.getLogger().trace(MessageFormat.format("count={0} - EXIT!", count)); //NOI18N
 349
 350                 // Return it
 351                 return count;
 352         }
 353
 354         /**
 355          * Some "getter" for a prepared statement with inserted values
 356          *
 357          * @param query SQL query string
 358          * @param values Values set
 359          * @return A fully prepared statement
 360          */
 361         private PreparedStatement getPreparedStatement (final StringBuilder query, final Set<Object> values) throws SQLException {
 362                 // Trace message
 363                 this.getLogger().trace(MessageFormat.format("query={0},values={1} - CALLED!", query, values)); //NOI18N
 364
 365                 // Init prepared statement
 366                 PreparedStatement statement = connection.prepareStatement(query.toString());
 367
 368                 // Debug message
 369                 this.getLogger().debug(MessageFormat.format("statement={0}", statement)); //NOI18N
 370
 371                 // Get iterator on values
 372                 Iterator<Object> valueIterator = values.iterator();
 373
 374                 // Init index with 1
 375                 int index = 1;
 376
 377                 // Set all values
 378                 while (valueIterator.hasNext()) {
 379                         // Get next value
 380                         Object value = valueIterator.next();
 381
 382                         //Debug message
 383                         this.getLogger().debug(MessageFormat.format("index={1} has value={0}", value, index)); //NOI18N
 384
 385                         // Detect type again
 386                         if (value instanceof Boolean) {
 387                                 // Debug log
 388                                 this.getLogger().debug(MessageFormat.format("Setting boolean value={0} for index={1}", value, index)); //NOI18N
 389
 390                                 // Found boolean
 391                                 statement.setBoolean(index, (boolean) value);
 392                         } else if (value instanceof String) {
 393                                 // Debug message
 394                                 this.getLogger().debug(MessageFormat.format("Setting string value={0} for index={1}", value, index)); //NOI18N
 395
 396                                 // Found string
 397                                 statement.setString(index, (String) value);
 398                         } else if (value instanceof Integer) {
 399                                 // Debug message
 400                                 this.getLogger().debug(MessageFormat.format("Setting integer value={0} for index={1}", value, index)); //NOI18N
 401
 402                                 // Found ineteger
 403                                 statement.setInt(index, (int) value);
 404                         } else if (value instanceof Long) {
 405                                 // Debug message
 406                                 this.getLogger().debug(MessageFormat.format("Setting long value={0} for index={1}", value, index)); //NOI18N
 407
 408                                 // Found ineteger
 409                                 statement.setLong(index, (long) value);
 410                         } else if (value instanceof Float) {
 411                                 // Debug message
 412                                 this.getLogger().debug(MessageFormat.format("Setting float value={0} for index={1}", value, index)); //NOI18N
 413
 414                                 // Found ineteger
 415                                 statement.setFloat(index, (float) value);
 416                         } else if (value == null) {
 417                                 // Debug message
 418                                 this.getLogger().warn(MessageFormat.format("Null value in index={0} is not supported (yet)", index)); //NOI18N
 419                         } else {
 420                                 // Not parseable type
 421                                 throw new SQLException(MessageFormat.format("Cannot handle value({0})={1} for index={2} in table {3}", value.getClass().getSimpleName(), value, index, this.getTableName())); //NOI18N
 422                         }
 423
 424                         // Increment index
 425                         index++;
 426                 }
 427
 428                 // Trace message
 429                 this.getLogger().trace(MessageFormat.format("statement={0} - EXIT!", statement)); //NOI18N
 430
 431                 // Return it
 432                 return statement;
 433         }
 434 }