From f9fc2ec9a73e932e678cf6532225cd228d7cfa95 Mon Sep 17 00:00:00 2001
From: Roland Haeder <roland@mxchange.org>
Date: Thu, 30 Jul 2015 15:32:41 +0200
Subject: [PATCH] =?utf8?q?First=20atttempt=20of=20prepared=20statement=20S?=
=?utf8?q?igned-off-by:Roland=20H=C3=A4der=20<roland@mxchange.org>?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit
---
.../database/backend/DatabaseBackend.java | 6 ++-
.../backend/mysql/MySqlDatabaseBackend.java | 49 +++++++++++++++++--
2 files changed, 49 insertions(+), 6 deletions(-)
diff --git a/Addressbook/src/org/mxchange/addressbook/database/backend/DatabaseBackend.java b/Addressbook/src/org/mxchange/addressbook/database/backend/DatabaseBackend.java
index 9a66e94f..70cfd81d 100644
--- a/Addressbook/src/org/mxchange/addressbook/database/backend/DatabaseBackend.java
+++ b/Addressbook/src/org/mxchange/addressbook/database/backend/DatabaseBackend.java
@@ -47,8 +47,9 @@ public interface DatabaseBackend extends FrameworkInterface {
* Some "getter" for total table row count
*
* @return Total row count
+ * @throws java.sql.SQLException If an SQL error occurs
*/
- public int getTotalCount ();
+ public int getTotalCount () throws SQLException;
/**
* Checks whether at least one row is found with given boolean value.
@@ -56,8 +57,9 @@ public interface DatabaseBackend extends FrameworkInterface {
* @param columnName Column to check for boolean value
* @param bool Boolean value to check
* @return Whether boolean value is found and returns at least one row
+ * @throws java.sql.SQLException If an SQL error occurs
*/
- public boolean isRowFound (final String columnName, final boolean bool);
+ public boolean isRowFound (final String columnName, final boolean bool) throws SQLException;
/**
* Rewinds backend
diff --git a/Addressbook/src/org/mxchange/addressbook/database/backend/mysql/MySqlDatabaseBackend.java b/Addressbook/src/org/mxchange/addressbook/database/backend/mysql/MySqlDatabaseBackend.java
index 8845521e..8b0ad9f3 100644
--- a/Addressbook/src/org/mxchange/addressbook/database/backend/mysql/MySqlDatabaseBackend.java
+++ b/Addressbook/src/org/mxchange/addressbook/database/backend/mysql/MySqlDatabaseBackend.java
@@ -19,6 +19,8 @@ package org.mxchange.addressbook.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.text.MessageFormat;
import java.util.Iterator;
@@ -40,6 +42,11 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas
*/
private static Connection connection;
+ /**
+ * Prepared statement for full row count
+ */
+ private PreparedStatement totalRowCount;
+
/**
* Constructor with table name
*
@@ -84,6 +91,18 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas
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("SELECT COUNT(`id`) AS `cnt` FROM ? LIMIT 1"); //NOI18N
+
// Trace message
this.getLogger().trace("EXIT!"); //NOI18N
}
@@ -97,7 +116,7 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas
@Override
public void doShutdown () {
// This should not happen:
- assert(MySqlDatabaseBackend.connection == null) : "connection is null"; //NOI18N
+ assert(MySqlDatabaseBackend.connection instanceof Connection) : MessageFormat.format("connection is not valid: {0}", MySqlDatabaseBackend.connection); //NOI18N
try {
// Close down database connection
@@ -108,8 +127,30 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas
}
@Override
- public int getTotalCount () {
- throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
+ public int getTotalCount () throws SQLException{
+ // Nothing counted by default
+ int count = 0;
+
+ // Set table name
+ this.totalRowCount.setString(1, this.getTableName());
+
+ // Prepared statements are cool ...
+ if (this.totalRowCount.execute()) {
+ // Did fully work, so get result set
+ ResultSet set = this.totalRowCount.getResultSet();
+
+ // First rewind it
+ assert(set.last()) : ": last() failed"; //NOI18N
+
+ // Get integer from 'cnt' alias (see statement)
+ count = set.getInt("cnt"); //NOI18N
+
+ // Go back to beginning
+ set.beforeFirst();
+ }
+
+ // Return result
+ return count;
}
/**
@@ -120,7 +161,7 @@ public class MySqlDatabaseBackend extends BaseDatabaseBackend implements Databas
* @return Whether boolean value is found and returns at least one row
*/
@Override
- public boolean isRowFound (final String columnName, final boolean bool) {
+ public boolean isRowFound (final String columnName, final boolean bool) throws SQLException {
// Is at least one entry found?
if (this.getTotalCount() == 0) {
// No entry found at all
--
2.39.5