Package com.jk.data.dataaccess.core

Class JKDataAccessImpl

java.lang.Object
com.jk.data.dataaccess.core.JKDataAccessImpl
All Implemented Interfaces:
JKDataAccessService
Direct Known Subclasses:
H2DataAccess, JKOracleDataAccess, MysqlDataAccess
public class JKDataAccessImpl extends Object implements JKDataAccessService
This class provides a comprehensive set of methods for interacting with a database.

It includes features for executing SQL queries, managing transactions, and caching query results.

This class is designed to simplify database operations and enhance maintainability and performance in database-driven applications.

Version:
1.0
Author:
Dr. Jalal H. Kiswani

  • Field Details

    • acceptedTimeInMillis

      public static final int acceptedTimeInMillis
      Represents the time duration, in milliseconds, for query execution acceptance.

      This constant represents accepted execution time for a query.

      The value of this constant is retrieved from the application's configuration using the JKConfig class, specifically by calling the get() method to obtain an instance of the configuration, and then calling JKConfig.getPropertyAsInteger(String, int) to retrieve the configured value as an integer.

      If the configured property is not found or cannot be converted to an integer, a default value defined in JKConstants.Database with the name QUERY_EXEC_ACCEPTED_TIME_DEFAULT will be used.

      See Also:

    • maxAcceptedTimeInMillis

      public static final int maxAcceptedTimeInMillis
      Represents the maximum time duration, in milliseconds, allowed for query execution acceptance.

      This constant represents the upper limit of time within which a query execution is considered accepted.

      The value of this constant is retrieved from the application's configuration using the JKConfig class, specifically by calling the get() method to obtain an instance of the configuration, and then calling JKConfig.getPropertyAsInteger(String, int) to retrieve the configured value as an integer.

      If the configured property is not found or cannot be converted to an integer, a default value defined in JKConstants.Database with the name QUERY_EXEC_MAX_TIME_DEFAULT will be used.

      See Also:

    • bufferSize

      public static final int bufferSize
      Represents the size of the buffer used for query log data storage.

      This constant represents the size of the buffer used to store query log data.

      It is retrieved from the application's configuration using the JKConfig class. Specifically, it is obtained by calling the get() method to obtain an instance of the configuration, and then calling JKConfig.getPropertyAsInteger(String, int) to retrieve the configured value as an integer.

      If the configured property is not found or cannot be converted to an integer, a default value defined in JKConstants.Database with the name QUERY_LOG_BUFFER_SIZE_DEFAULT will be used.

      See Also:

    • logger

      protected JKLogger logger
      Represents the logger for this class.

      This field holds an instance of the logger obtained from the JKLoggerFactory. It is used for logging various messages and information related to the class.

      See Also:

  • Constructor Details

    • JKDataAccessImpl

      public JKDataAccessImpl()
      This method constructs a new JKDataAccessImpl using the default data source.

    • JKDataAccessImpl

      public JKDataAccessImpl(String dataSourcePrefix)
      This method constructs a new JKDataAccessImpl using the data source associated with the provided prefix.
      Parameters:
      dataSourcePrefix - Specifies the prefix associated with the data source.

    • JKDataAccessImpl

      public JKDataAccessImpl(JKDataSource dataSource)
      This method constructs a new JKDataAccessImpl using the provided data source.
      Parameters:
      dataSource - Specifies the data source of this instance.

  • Method Details

    • close

      protected void close(Connection connection)
      This method is responsible for closing a JDBC Connection.
      Parameters:
      connection - Specifies the JDBC Connection to be closed.

    • close

      protected void close(PreparedStatement ps)
      This method is responsible for closing a PreparedStatement.
      Parameters:
      ps - Specifies the PreparedStatement to be closed.

    • close

      protected void close(PreparedStatement ps, Connection c)
      This method is responsible for closing both a PreparedStatement and a Connection.
      Parameters:
      ps - Specifies the PreparedStatement to be closed.
      c - Specifies the JDBC Connection to be closed.

    • close

      protected void close(ResultSet rs)
      This method is responsible for closing a ResultSet.
      Parameters:
      rs - Specifies the ResultSet to be closed.

    • close

      protected void close(ResultSet rs, PreparedStatement ps, Connection c)
      This method is responsible for closing a PreparedStatement, ResultSet, and a Connection.
      Parameters:
      rs - Specifies the ResultSet to be closed.
      ps - Specifies the PreparedStatement to be closed.
      c - Specifies the JDBC Connection to be closed.

    • close

      protected void close(Statement ps)
      This method is responsible for closing a Statement.
      Parameters:
      ps - Specifies the Statement to be closed.

    • executeQueryAsCachedRowSet

      public CachedRowSet executeQueryAsCachedRowSet(String query, Object... params)
      This method executes a database query and returns the result as a CachedRowSet.
      Specified by:
      executeQueryAsCachedRowSet in interface JKDataAccessService
      Parameters:
      query - Specifies the SQL query to execute.
      params - Specifies the optional parameters to bind to the query.
      Returns:
      the CachedRowSet containing the query results.

    • executeQueryAsIdValue

      public List<JKDbIdValue> executeQueryAsIdValue(String query, Object... params)
      This method executes a database query and returns the result as a list of JKDbIdValue objects.
      Specified by:
      executeQueryAsIdValue in interface JKDataAccessService
      Parameters:
      query - Specifies the SQL query to execute.
      params - Specifies the optional parameters to bind to the query.
      Returns:
      a list of JKDbIdValue objects representing the query results

    • executeQueryAsString

      public String executeQueryAsString(String query, Object... params)
      This method executes an SQL query and returns the result as a formatted string.
      Specified by:
      executeQueryAsString in interface JKDataAccessService
      Parameters:
      query - Specifies the SQL query to execute.
      params - Specifies the optional parameters to be used in the SQL query.
      Returns:
      the formatted string representation of the query result.

    • executeQueryAsString

      public String executeQueryAsString(String query, String fieldSeparator, String recordsSepartor, Object... params)
      This method executes an SQL query and returns the result as a formatted string with custom separators.
      Specified by:
      executeQueryAsString in interface JKDataAccessService
      Parameters:
      query - Specifies the SQL query to execute.
      fieldSeparator - Specifies the separator used to separate fields in the result.
      recordsSepartor - Specifies the separator used to separate records in the result.
      params - Specifies the optional parameters to be used in the SQL query.
      Returns:
      the formatted string representation of the query result with custom separators.

    • executeUpdate

      public Object executeUpdate(JKUpdater updater) throws JKDataAccessException
      This method executes a database update operation using a JKUpdater and returns the result.
      Specified by:
      executeUpdate in interface JKDataAccessService
      Parameters:
      updater - Specifies the JKUpdater object that defines the update operation.
      Returns:
      the result of the update operation, which can be a generated key or another relevant value.
      Throws:
      JKDataAccessException

    • prepareStatement

      protected PreparedStatement prepareStatement(Connection connection, boolean returnGeneratedKeys, JKUpdater updater) throws SQLException
      This method prepares a PreparedStatement for executing SQL commands on the provided database connection. It offers the option to specify whether generated keys should be returned after execution.
      Parameters:
      connection - Specifies the database connection on which the PreparedStatement will be prepared.
      returnGeneratedKeys - Indicates whether to return generated keys after executing the SQL command (true) or not (false).
      updater - Specifies the updater.
      Returns:
      a PreparedStatement ready for executing SQL commands.
      Throws:
      SQLException - if a database access error occurs or the SQL command is invalid.

    • prepareQueryStatement

      protected PreparedStatement prepareQueryStatement(Connection connection, JKFinder finder) throws SQLException
      This method prepares a PreparedStatement for executing a database query based on the criteria specified in the given JKFinder instance.
      Parameters:
      connection - Specifies the database connection on which the PreparedStatement will be prepared.
      finder - Specifies the JKFinder instance representing the query criteria.
      Returns:
      a PreparedStatement ready for executing the database query.
      Throws:
      SQLException - if a database access error occurs or the query is invalid.

    • executeUpdate

      public Object executeUpdate(JKUpdater updater, boolean ignoreRecordNotFoundException)
      This method executes an update operation using the provided JKUpdater and allows for ignoring the JKRecordNotFoundException.
      Specified by:
      executeUpdate in interface JKDataAccessService
      Parameters:
      updater - Specifies the JKUpdater object responsible for specifying the update operation
      ignoreRecordNotFoundException - Indicates whether to ignore the JKRecordNotFoundException if no records are affected by the update
      Returns:
      the generated key if applicable, or null.

    • execute

      public int execute(String statement, Object... params) throws JKDataAccessException
      This method executes a database operation with the provided SQL statement and optional parameters.
      Specified by:
      execute in interface JKDataAccessService
      Parameters:
      statement - Specifies the SQL statement to execute.
      params - Specifies the parameters to be bound to the SQL statement.
      Returns:
      the number of rows affected by the operation.
      Throws:
      JKDataAccessException

    • executeQueryAsArray

      public Object[] executeQueryAsArray(String query, Object... params)
      This method executes a SQL query and returns the result as an array of objects.
      Specified by:
      executeQueryAsArray in interface JKDataAccessService
      Parameters:
      query - Specifies the SQL query to execute.
      params - Specifies the optional parameters to be used in the query.
      Returns:
      an array of objects representing the result of the query.

    • executeQueryAsList

      public List<List<Object>> executeQueryAsList(String query, Object... params)
      This method executes a query and returns the result as a List of rows, where each row is represented as a List of objects.
      Specified by:
      executeQueryAsList in interface JKDataAccessService
      Parameters:
      query - Specifies the SQL query to be executed.
      params - Specifies the optional parameters to be used in the query.
      Returns:
      the list of rows, where each row is represented as a List of Objects.

    • executeSingleOutputQuery

      public Object executeSingleOutputQuery(String query, Object... params)
      This method executes a database query that is expected to return a single value and retrieves that value.
      Specified by:
      executeSingleOutputQuery in interface JKDataAccessService
      Parameters:
      query - Specifies the SQL query to execute.
      params - Specifies the parameters to be bound to the SQL query.
      Returns:
      the single value returned by the query, or null if no value is found.

    • findRecord

      public <T> T findRecord(JKFinder finder)
      This method finds and retrieves a record from the database table based on the provided JKFinder criteria.
      Specified by:
      findRecord in interface JKDataAccessService
      Type Parameters:
      T - Specifies the type of the record object.
      Parameters:
      finder - Specifies the JKFinder instance specifying the criteria for the record retrieval.
      Returns:
      the retrieved record as an object of type T, or null if the record is not found.

    • findRecord

      public <T> T findRecord(JKFinder finder, String tableName, Object recordId)
      This method finds and retrieves a specific record from the specified database table using the provided JKFinder and record ID.
      Specified by:
      findRecord in interface JKDataAccessService
      Type Parameters:
      T - Specifies the type of the record object.
      Parameters:
      finder - Specifies the JKFinder instance specifying the criteria for the record retrieval.
      tableName - Specifies the name of the database table from which to retrieve the record.
      recordId - Specifies the unique identifier of the record to be retrieved.
      Returns:
      the retrieved record as an object of type T, or null if the record is not found.

    • getConnection

      protected Connection getConnection()
      This method is used to obtain a database connection from the configured data source.
      Returns:
      the database connection from the data source.

    • getConnection

      protected Connection getConnection(boolean query)
      This method is used to obtain a database connection from the configured data source.
      Parameters:
      query - Indicates whether the connection will be used for executing a query (true) or not (false).
      Returns:
      the database connection from the data source.

    • getDataSource

      protected JKDataSource getDataSource()
      This method returns the JKDataSource instance that is used for managing database connections in the context of this class.
      Returns:
      the JKDataSource associated with this class.

    • getGeneratedKey

      protected Object getGeneratedKey(PreparedStatement ps) throws SQLException
      This method retrieves the generated key from the given PreparedStatement.
      Parameters:
      ps - Specifies the PreparedStatement from which to retrieve the generated key.
      Returns:
      the generated key value obtained from the executed PreparedStatement
      Throws:
      SQLException - if a database access error occurs or this method is called on a closed {PreparedStatement}

    • getNextId

      protected Long getNextId(Connection connectoin, String tableName, String fieldName) throws JKDataAccessException
      This method is used to obtain the next available ID for a specific table and field in the database.
      Parameters:
      connectoin - Specifies the database Connection to use for the query.
      tableName - Specifies the name of the database table from which to retrieve the next ID.
      fieldName - Specifies the name of the field in the table to which the ID belongs.
      Returns:
      the next available ID for the specified table and field, subject to the given condition.
      Throws:
      JKDataAccessException - if data access operations fail, encounter issues, or are denied due to various reasons.

    • getNextId

      protected Long getNextId(Connection con, String tableName, String fieldName, String condition)
      This method is used to obtain the next available ID for a specific table and field in the database, based on the given condition.
      Parameters:
      con - Specifies the database Connection to use for the query.
      tableName - Specifies the name of the database table from which to retrieve the next ID.
      fieldName - Specifies the name of the field in the table to which the ID belongs.
      condition - Specifies the condition to apply when determining the next available ID.
      Returns:
      the next available ID for the specified table and field, subject to the given condition.

    • getNextId

      public Long getNextId(String tableName, String fieldName)
      This method retrieves the next available ID for a specified table and field.
      Specified by:
      getNextId in interface JKDataAccessService
      Parameters:
      tableName - Specifies the name of the database table.
      fieldName - Specifies the name of the field for which the next ID is needed.
      Returns:
      the next available ID for the specified table and field.

    • getNextId

      public Long getNextId(String tableName, String fieldName, String condition)
      This method retrieves the next available ID for a specified table and field based on a given condition.
      Specified by:
      getNextId in interface JKDataAccessService
      Parameters:
      tableName - Specifies the name of the database table.
      fieldName - Specifies the name of the field for which the next ID is needed.
      condition - Specifies the condition that filters the records to determine the next ID.
      Returns:
      the next available ID for the specified table, field, and condition.

    • getRowsCount

      public int getRowsCount(String query, Object... params)
      This method retrieves the number of rows returned by a specified SQL query with optional parameters.
      Specified by:
      getRowsCount in interface JKDataAccessService
      Parameters:
      query - Specifies the SQL query for which row count is to be determined.
      params - Specifies the optional parameters to be used in the SQL query.
      Returns:
      the number of rows returned by the specified SQL query.

    • getSystemDate

      public Date getSystemDate()
      This method retrieves the current system date and time from the database.
      Specified by:
      getSystemDate in interface JKDataAccessService
      Returns:
      a Date object representing the current system date and time

    • getList

      public <T> List<T> getList(JKFinder finder)
      This method retrieves a list of objects based on the provided JKFinder criteria.
      Specified by:
      getList in interface JKDataAccessService
      Type Parameters:
      T - Specifies the type of the record object.
      Parameters:
      finder - Specifies the JKFinder instance specifying the criteria for the record retrieval.
      Returns:
      a list of objects that match the criteria defined in the JKFinder.

    • getList

      public <T> List<T> getList(JKFinder finder, String key)
      Gets the list.
      Specified by:
      getList in interface JKDataAccessService
      Type Parameters:
      T - the generic type
      Parameters:
      finder - the finder
      key - the key
      Returns:
      the list

    • prepareQueryStatement

      protected PreparedStatement prepareQueryStatement(Connection connection, String sql, Object... params) throws SQLException
      This method is used to create a PreparedStatement for executing a parametric query with the provided SQL statement and parameters.
      Parameters:
      connection - Specifies the database Connection to use for preparing the statement.
      sql - Specifies the SQL statement to prepare, with place-holders for parameters.
      params - Specifies the parameters to set in the prepared statement.
      Returns:
      a PreparedStatement ready for executing the parametric query.
      Throws:
      SQLException - if a database access error occurs or this method is called on a closed Connection.

    • prepareStatement

      protected PreparedStatement prepareStatement(Connection connection, String sql, Object... params) throws SQLException
      This method prepares a PreparedStatement for executing a parametric SQL statement with the given SQL and parameters.
      Parameters:
      connection - Specifies the database Connection to use for preparing the statement.
      sql - Specifies the SQL statement to prepare, with place-holders for parameters.
      params - Specifies the parameters to set in the prepared statement.
      Returns:
      a PreparedStatement ready for executing the parametric SQL statement.
      Throws:
      SQLException - if a database access error occurs or this method is called on a closed Connection.

    • prepareStatement

      protected PreparedStatement prepareStatement(Connection connection, boolean returnGeneratedKeys, String sql, Object... params) throws SQLException
      This method prepares a PreparedStatement for executing a parametric SQL statement with the given SQL and parameters, optionally returning generated keys.
      Parameters:
      connection - Specifies the database Connection to use for preparing the statement.
      returnGeneratedKeys - Indicates whether to return any auto-generated keys produced by the statement execution (true) or not (false).
      sql - Specifies the SQL statement to prepare, with place-holders for parameters.
      params - Specifies the parameters to set in the prepared statement.
      Returns:
      a PreparedStatement ready for executing the parametric SQL statement.
      Throws:
      SQLException - if a database access error occurs or this method is called on a closed Connection.

    • setParams

      protected PreparedStatement setParams(PreparedStatement prepareStatement, Object... params) throws SQLException
      This method sets the parameters in a PreparedStatement based on the provided parameter values.
      Parameters:
      prepareStatement - Specifies the PreparedStatement in which to set the parameter values.
      params - Specifies the parameter values to set in the PreparedStatement.
      Returns:
      the same PreparedStatement with parameters set.
      Throws:
      SQLException - if parameterIndex does not correspond to a parameter marker in the SQL statement; if a database access error occurs;this method is called on a closed PreparedStatementor the type of the given object is ambiguous.

    • printRecordResultSet

      protected void printRecordResultSet(ResultSet rs)
      This method prints the records from a ResultSet to the standard output stream (console).
      Parameters:
      rs - Specifies the ResultSet containing the records to be printed.

    • printRecordResultSet

      protected void printRecordResultSet(ResultSet rs, boolean all, PrintStream out)
      This method prints records from a ResultSet to a specified PrintStream.
      Parameters:
      rs - Specifies the ResultSet containing the records to be printed.
      all - Indicates whether to print all records or not.
      out - Specifies the PrintStream to which records should be printed.

    • resetCache

      public static void resetCache()
      This method provides a way to clear the cache, removing all cached data and starting with an empty cache.

    • runScript

      public void runScript(String fileName)
      This method runs a SQL script from a specified file.
      Specified by:
      runScript in interface JKDataAccessService
      Parameters:
      fileName - Specifies the name of the file containing the SQL script to execute.

    • isTableExists

      public boolean isTableExists(String tableName)
      This method checks whether the table with the provided name exists in the database or not.
      Specified by:
      isTableExists in interface JKDataAccessService
      Parameters:
      tableName - Specifies the table name in the database.
      Returns:
      true, if the table exists, false otherwise.

    • setSession

      public void setSession(JKSession session)
      This method is yet to be implemented (TBI).
      Parameters:
      session - Specifies the method parameter (TBI).

    • describeTable

      public Map<String,String> describeTable(String name)
      This method retrieves data about a database table and returns it as a map of column names to their corresponding data types.
      Specified by:
      describeTable in interface JKDataAccessService
      Parameters:
      name - Specifies the name of the table to describe.
      Returns:
      the map containing column names as keys and their corresponding data types as values.

    • dropTable

      public void dropTable(String tableName)
      This method drops a database table specified by its name.
      Specified by:
      dropTable in interface JKDataAccessService
      Parameters:
      tableName - Specifies the name of the table to drop.

    • executeUpdate

      public Object executeUpdate(String sql, Object... objects)
      This method executes an SQL update statement with optional parameters and returns the result.
      Specified by:
      executeUpdate in interface JKDataAccessService
      Parameters:
      sql - Specifies the SQL update statement to execute.
      objects - Specifies an optional array of parameters to be used in the SQL statement.
      Returns:
      the object representing the result of the update operation.

    • find

      public <T> T find(String query, JKPopulator<T> populator, Object... params)
      This method executes an SQL query and maps the result to an object using a provided JKPopulator.
      Specified by:
      find in interface JKDataAccessService
      Type Parameters:
      T - Specifies the type of objects.
      Parameters:
      query - Specifies the SQL query to execute.
      populator - Specifies the JKPopulator instance responsible for mapping the query result to an object
      params - Specifies the optional parameters to be used in the SQL query.
      Returns:
      an object resulting from the query execution and mapping.

    • getList

      public <T> List<T> getList(String query, JKPopulator<T> populator, Object... params)
      This method executes an SQL query and maps the result to a list of objects using a provided populator.
      Specified by:
      getList in interface JKDataAccessService
      Type Parameters:
      T - Specifies the type of objects.
      Parameters:
      query - Specifies the SQL query to execute.
      populator - Specifies the JKPopulator instance responsible for mapping the query result to an object.
      params - Specifies the optional parameters to be used in the SQL query.
      Returns:
      the list of objects resulting from the query execution and mapping.

    • findAndCacheRecord

      public <T> T findAndCacheRecord(String key, String query, JKPopulator<T> populator, Object... params)
      This method executes an SQL query, maps the result to an object using a provided populator, and caches the mapped object using the specified table name.
      Specified by:
      findAndCacheRecord in interface JKDataAccessService
      Type Parameters:
      T - Specifies the type of objects.
      Parameters:
      key - Specifies the name of the table associated with the cached record.
      query - Specifies the SQL query to execute.
      populator - Specifies the JKPopulator instance responsible for mapping the query result to an object.
      params - Specifies the optional parameters to be used in the SQL query.
      Returns:
      an object resulting from the query execution and mapping, cached under the specified table name.

    • getListAndCache

      public <T> List<T> getListAndCache(String key, String query, JKPopulator<T> populator, Object... params)
      This method executes an SQL query, maps the result to a list of objects using a provided populator, and caches the result for future use.
      Specified by:
      getListAndCache in interface JKDataAccessService
      Type Parameters:
      T - Specifies the type of objects.
      Parameters:
      key - Specifies the name of the table associated with the query.
      query - Specifies the SQL query to execute.
      populator - Specifies the JKPopulator instance responsible for mapping the query result to an object.
      params - Specifies the optional parameters to be used in the SQL query.
      Returns:
      the list of objects resulting from the query execution and mapping.

    • beginTransaction

      public void beginTransaction()
      This method begins a new database transaction.
      Specified by:
      beginTransaction in interface JKDataAccessService

    • isInTransaction

      public boolean isInTransaction()
      This method checks whether the current instance is in an active transaction.
      Returns:
      true, if the current instance is in an active transaction, false otherwise.

    • closeTransaction

      public void closeTransaction(boolean commit)
      This method closes the current database transaction, either committing or rolling it back based on the specified parameter.
      Specified by:
      closeTransaction in interface JKDataAccessService
      Parameters:
      commit - Indicates whether the transaction will be committed (true), or if it will be rolled back (false).

    • addQueryLog

      protected void addQueryLog(String query, Instant startTimer, Instant endTimer)
      This method logs information about the execution of a database query.
      Parameters:
      query - Specifies the SQL query being executed.
      startTimer - Specifies the time stamp when the query execution started.
      endTimer - Specifies the time stamp when the query execution finished.

    • getCaller

      public static String getCaller()
      This method retrieves the name of the caller method or class.
      Returns:
      the string representing the name of the caller method or class.

    • writeLog

      protected void writeLog(String query, long duration, String caller)
      This method writes a log entry with query information and execution duration.
      Parameters:
      query - Specifies the SQL query or operation to log.
      duration - Specifies the duration of query execution in milliseconds.
      caller - Specifies the name or context of the caller method or class.

    • call

      public List call(String call, List<Object> params, List<Class<?>> types)
      This method executes a stored procedure or callable statement and returns a list of results.
      Specified by:
      call in interface JKDataAccessService
      Parameters:
      call - Specifies the name or identifier of the stored procedure to execute.
      params - Specifies the list of parameters to pass to the stored procedure.
      types - Specifies the list of parameter types corresponding to the parameters.
      Returns:
      a list of results returned by the stored procedure, or an empty list if no results are returned.

    • call

      public <T> T call(JKCaller caller)
      This method executes a database operation defined by the provided JKCaller.
      Specified by:
      call in interface JKDataAccessService
      Type Parameters:
      T - Specifies the type of the result returned by the database operation.
      Parameters:
      caller - Specifies the JKCaller that defines the database operation.
      Returns:
      the result of the database operation.

    • loadSql

      protected String loadSql(String sql)
      This method loads and retrieves an SQL query or statement from a resource based on the provided key.
      Parameters:
      sql - Specifies the key or identifier of the SQL query or statement to retrieve.
      Returns:
      the SQL query or statement as a string.