SQLLoggingDatabaseHelper.java
/*
** Module : SQLLoggingDatabaseHelper.java
** Abstract : Helper object for logging database access.
**
** Copyright (c) 2024, Golden Code Development Corporation.
**
** -#- -I- --Date-- --------------------------------Description----------------------------------
** 001 ICP 20240417 Created first version.
*/
/*
** This program is free software: you can redistribute it and/or modify
** it under the terms of the GNU Affero General Public License as
** published by the Free Software Foundation, either version 3 of the
** License, or (at your option) any later version.
**
** This program is distributed in the hope that it will be useful,
** but WITHOUT ANY WARRANTY; without even the implied warranty of
** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
** GNU Affero General Public License for more details.
**
** You may find a copy of the GNU Affero GPL version 3 at the following
** location: https://www.gnu.org/licenses/agpl-3.0.en.html
**
** Additional terms under GNU Affero GPL version 3 section 7:
**
** Under Section 7 of the GNU Affero GPL version 3, the following additional
** terms apply to the works covered under the License. These additional terms
** are non-permissive additional terms allowed under Section 7 of the GNU
** Affero GPL version 3 and may not be removed by you.
**
** 0. Attribution Requirement.
**
** You must preserve all legal notices or author attributions in the covered
** work or Appropriate Legal Notices displayed by works containing the covered
** work. You may not remove from the covered work any author or developer
** credit already included within the covered work.
**
** 1. No License To Use Trademarks.
**
** This license does not grant any license or rights to use the trademarks
** Golden Code, FWD, any Golden Code or FWD logo, or any other trademarks
** of Golden Code Development Corporation. You are not authorized to use the
** name Golden Code, FWD, or the names of any author or contributor, for
** publicity purposes without written authorization.
**
** 2. No Misrepresentation of Affiliation.
**
** You may not represent yourself as Golden Code Development Corporation or FWD.
**
** You may not represent yourself for publicity purposes as associated with
** Golden Code Development Corporation, FWD, or any author or contributor to
** the covered work, without written authorization.
**
** 3. No Misrepresentation of Source or Origin.
**
** You may not represent the covered work as solely your work. All modified
** versions of the covered work must be marked in a reasonable way to make it
** clear that the modified work is not originating from Golden Code Development
** Corporation or FWD. All modified versions must contain the notices of
** attribution required in this license.
*/
package com.goldencode.p2j.persist.orm;
import java.sql.*;
import java.util.*;
import com.goldencode.p2j.cfg.Configuration;
import com.goldencode.p2j.util.logging.CentralLogger;
public class SQLLoggingDatabaseHelper
{
/** Logger. */
private static final CentralLogger LOG = CentralLogger.get(SQLLoggingDatabaseHelper.class);
/** Map that includes a helper for each given database. */
private static final Map<String, SQLLoggingDatabaseHelper> dbHelpers = new HashMap<>();
/** Database connection */
private Connection connection = null;
/**
* Constructor to instantiate a helper
*
* @param filename
* The filename of the h2 database to be created.
* @param user
* The user of the h2 database to be created.
* @param pass
* The password of the h2 database to be created.
*/
public SQLLoggingDatabaseHelper(String filename, String user, String pass)
{
Statement stmt = null;
try
{
String home = Configuration.home();
char sep = Configuration.P2J_FILE_SEP;
String p2jHomePath = home + sep;
String url = "jdbc:h2:"
+ p2jHomePath
+ filename + ";"
+ "DB_CLOSE_DELAY=-1;"
+ "AUTOCOMMIT=OFF;"
+ "DEFAULT_LOCK_TIMEOUT=10000;"
+ "LOCK_MODE=3;"
+ "MV_STORE=FALSE";
Class.forName("org.h2.Driver");
openConnection(url, user, pass);
}
catch (Exception e)
{
LOG.warning("Couldn't initialize the SQL logging database:" + e.getMessage());
}
if (connection != null)
{
try
{
stmt = connection.createStatement();
String create = "create table if not exists SQLQuery_profiling (" +
"id identity primary key, " +
"databaseName varchar, time_stamp timestamp, statement varchar," +
"executionTime double, batchSize int, stackTrace varchar)";
stmt.execute(create);
connection.commit();
}
catch (Exception e)
{
try
{
connection.rollback();
}
catch (SQLException ex)
{
LOG.warning("Couldn't roll back the transaction", ex);
}
LOG.warning("Couldn't initialize the SQL logging database:" + e.getMessage());
}
finally
{
if (stmt != null)
{
try
{
stmt.close();
}
catch (SQLException exc)
{
LOG.warning("Couldn't close the statement", exc);
}
}
}
}
}
/**
* Get the logging database helper of a specific database.
*
* @param databaseName
* The name of the database in use.
* @param fileName
* The filename of the H2 database.
* @param user
* The user of the H2 database.
* @param pass
* The password of the H2 database.
*
* @return If it exists, an already created helper for that database, or a new one otherwise.
*/
public static SQLLoggingDatabaseHelper getDbHelper(String databaseName,
String fileName,
String user,
String pass)
{
SQLLoggingDatabaseHelper dbHelper = dbHelpers.get(databaseName);
if (dbHelper != null)
{
return dbHelper;
}
dbHelper = new SQLLoggingDatabaseHelper(fileName, user, pass);
dbHelpers.put(databaseName, dbHelper);
return dbHelper;
}
/**
* Insert a SQL query profiling record.
*
* @param databaseName
* The FWD database on which profiling is done.
* @param statement
* The SQL statement that is profiled.
* @param executionTime
* The execution time of the profiled SQL Query.
* @param batchSize
* The batch size of the profiled SQL Query.
* @param stackTrace
* The stack trace of the profiled SQL Query.
*
* @return Primary key of the created record.
*/
public Long insertSQLQueryProfiling(String databaseName,
String statement,
Double executionTime,
int batchSize,
String stackTrace)
{
Long id = null;
try
{
// prepare insert statement for SQL query profiling
String insert = "insert into SQLQuery_profiling (" +
"databaseName, time_stamp, statement, executionTime, batchSize, stackTrace) " +
"values(?, ?, ?, ?, ?, ?)";
PreparedStatement stmtsqlQueryInsert = connection.prepareStatement(insert,
Statement.RETURN_GENERATED_KEYS);
stmtsqlQueryInsert.setString(1, databaseName);
stmtsqlQueryInsert.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
stmtsqlQueryInsert.setString(3, statement);
stmtsqlQueryInsert.setDouble(4, executionTime);
stmtsqlQueryInsert.setInt(5, batchSize);
stmtsqlQueryInsert.setString(6, stackTrace);
stmtsqlQueryInsert.execute();
stmtsqlQueryInsert.clearParameters();
// read back the primary key that was autogenerated
ResultSet rs = stmtsqlQueryInsert.getGeneratedKeys();
if (rs.next())
{
id = rs.getLong(1);
}
connection.commit();
}
catch (Exception e)
{
if (connection != null)
{
try
{
connection.rollback();
}
catch (SQLException ex)
{
LOG.warning("Couldn't roll back the transaction", ex);
}
}
LOG.warning("Couldn't insert SQL Query Profiling in database:"+ e.getMessage());
}
if (id == null)
{
LOG.warning("Couldn't insert SQL Query Profiling in database!");
}
return id;
}
/**
* Open a new connection to the database (if one already opened, it will close it first).
*
* @param url
* The connection URL of the database.
* @param user
* The database user configured in directory.xml
* @param pass
* The database password configured in directory.xml
*/
private void openConnection(String url, String user, String pass)
{
if (connection != null)
{
closeConnection();
}
try
{
connection = DriverManager.getConnection(url, user, pass);
}
catch (SQLException e)
{
LOG.warning("Couldn't establish connection to SQL logging database:"+ e.getMessage());
}
}
/**
* Close the current connection.
*/
private void closeConnection()
{
if (connection == null)
{
return;
}
try
{
connection.close();
connection = null;
}
catch (Exception e)
{
LOG.warning("Couldn't close the connection to SQL logging database:"+ e.getMessage());
}
}
}