org.apache.empire.dbms.DBMSHandlerBase Maven / Gradle / Ivy
The newest version!
/*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you under the Apache License, Version 2.0 (the
* "License"); you may not use this file except in compliance
* with the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing,
* software distributed under the License is distributed on an
* "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
* KIND, either express or implied. See the License for the
* specific language governing permissions and limitations
* under the License.
*/
package org.apache.empire.dbms;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.UUID;
import org.apache.empire.commons.DateUtils;
import org.apache.empire.commons.ObjectUtils;
import org.apache.empire.commons.StringUtils;
import org.apache.empire.data.DataType;
import org.apache.empire.db.DBBlobData;
import org.apache.empire.db.DBClobData;
import org.apache.empire.db.DBCmdParam;
import org.apache.empire.db.DBColumn;
import org.apache.empire.db.DBColumnExpr;
import org.apache.empire.db.DBCombinedCmd;
import org.apache.empire.db.DBCommand;
import org.apache.empire.db.DBCommandExpr;
import org.apache.empire.db.DBDDLGenerator.DDLActionType;
import org.apache.empire.db.DBDatabase;
import org.apache.empire.db.DBRelation;
import org.apache.empire.db.DBRowSet;
import org.apache.empire.db.DBSQLBuilder;
import org.apache.empire.db.DBSQLScript;
import org.apache.empire.db.DBTable;
import org.apache.empire.db.DBTableColumn;
import org.apache.empire.db.exceptions.EmpireSQLException;
import org.apache.empire.db.exceptions.QueryFailedException;
import org.apache.empire.db.validation.DBModelChecker;
import org.apache.empire.db.validation.DBModelParser;
import org.apache.empire.exceptions.InvalidArgumentException;
import org.apache.empire.exceptions.NotSupportedException;
import org.apache.empire.exceptions.UnexpectedReturnValueException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* The DBMSHandler class is an abstract base class for all database handler.
* Its purpose is to handle everything that is - or might be - database vendor specific.
*/
public abstract class DBMSHandlerBase implements DBMSHandler
{
private static final Logger log = LoggerFactory.getLogger(DBMSHandler.class);
// Illegal name chars and reserved SQL keywords
protected static final char[] ILLEGAL_NAME_CHARS = new char[] { '@', '?', '>', '=', '<', ';', ':',
'/', '.', '-', ',', '+', '*', ')', '(',
'\'', '&', '%', '!', ' '
};
protected static final String[] GENERAL_SQL_KEYWORDS = new String[] { "user", "group",
"table", "column", "view", "index", "constraint",
"select", "udpate", "insert", "alter", "delete",
"order" };
protected final Set reservedSQLKeywords;
// Postfix for auto-generated Sequence names
protected String SEQUENCE_NAME_SUFFIX = "_SEQ";
/**
* DBMSBuilder
* A Default DBSQLBuilder implementation with no additional features
*/
public static final class DBMSBuilder extends DBSQLBuilder
{
protected DBMSBuilder(DBMSHandler dbms)
{
super(dbms);
}
}
/**
* DBMSCommand
* A Default DBCommand implementation with no additional features
*/
public static final class DBMSCommand extends DBCommand
{
protected DBMSCommand(DBMSHandler dbms, boolean autoPrepareStmt)
{
super(dbms, autoPrepareStmt);
}
}
/**
* This class is used to emulate sequences by using a sequence table.
* It is used with the executeSQL function and only required for insert statements
*/
public static class DBSeqTable extends DBTable
{
// *Deprecated* private static final long serialVersionUID = 1L;
public DBColumn C_SEQNAME;
public DBColumn C_SEQVALUE;
public DBColumn C_TIMESTAMP;
/**
* Constructor
*
* @param tableName the table name
* @param db the database object
*/
public DBSeqTable(String tableName, DBDatabase db)
{
super(tableName, db);
// Add all Colums
C_SEQNAME = addColumn("SeqName", DataType.VARCHAR, 40, true);
C_SEQVALUE = addColumn("SeqValue", DataType.INTEGER, 0, true);
C_TIMESTAMP = addColumn("SeqTime", DataType.DATETIME, 0, true);
// Primary Key
setPrimaryKey(new DBColumn[] { C_SEQNAME });
}
// Overrideable
public Object getNextValue(String SeqName, long minValue, Connection conn)
{
DBMSHandler dbms = db.getDbms();
// Create a Command
PreparedStatement stmt = null;
try
{ // The select Statement
DBCommand cmd = dbms.createCommand(db.isPreparedStatementsEnabled());
DBCmdParam nameParam = cmd.addParam(SeqName);
cmd.select(C_SEQVALUE);
cmd.select(C_TIMESTAMP);
cmd.where (C_SEQNAME.is(nameParam));
String selectCmd = cmd.getSelect();
// Get the next Value
long seqValue = 0;
while (seqValue == 0)
{
// stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt = conn.prepareStatement(selectCmd, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
stmt.setString(1, SeqName);
// Query existing value
ResultSet rs = stmt.executeQuery();
if (rs.next())
{ // Read the Sequence Value
seqValue = Math.max(rs.getLong(1) + 1, minValue);
java.sql.Timestamp current = rs.getTimestamp(2);
dbms.closeResultSet(rs);
// Update existing Record
cmd.clear();
DBCmdParam name = cmd.addParam(SeqName);
DBCmdParam time = cmd.addParam(current);
cmd.set(C_SEQVALUE.to(seqValue));
cmd.set(C_TIMESTAMP.to(DBDatabase.SYSDATE));
cmd.where(C_SEQNAME.is(name));
cmd.where(C_TIMESTAMP.is(time));
if (dbms.executeSQL(cmd.getUpdate(), cmd.getParamValues(), conn, null) < 1)
seqValue = 0; // Try again
}
else
{ // Close Reader
dbms.closeResultSet(rs);
// sequence does not exist
seqValue = minValue;
log.warn("Sequence {} does not exist! Creating sequence with start-value of {}", SeqName, seqValue);
// create a new sequence entry
cmd.clear();
cmd.set(C_SEQNAME.to(SeqName));
cmd.set(C_SEQVALUE.to(seqValue));
cmd.set(C_TIMESTAMP.to(DBDatabase.SYSDATE));
if (dbms.executeSQL(cmd.getInsert(), cmd.getParamValues(), conn, null) < 1)
seqValue = 0; // Try again
}
// check for concurrency problem
if (seqValue == 0)
log.warn("Failed to increment sequence {}. Trying again!", SeqName);
// close
closeStatement(stmt);
cmd.clear();
rs = null;
}
if (log.isInfoEnabled())
log.info("Sequence {} incremented to {}.", SeqName, seqValue);
return new Long(seqValue);
} catch (SQLException e) {
// throw exception
throw new EmpireSQLException(this, e);
} finally
{ // Cleanup
closeStatement(stmt);
}
}
/*
* cleanup
*/
private void closeStatement(Statement stmt)
{
try
{ // Statement close
if (stmt != null)
stmt.close();
// done
return;
} catch (SQLException sqle) {
// Commit failed!
throw new EmpireSQLException(this, sqle);
}
}
}
/**
* Constructor
*/
protected DBMSHandlerBase()
{
// Initialize List of reserved Keywords
reservedSQLKeywords = new HashSet(GENERAL_SQL_KEYWORDS.length);
for (String keyWord:GENERAL_SQL_KEYWORDS){
reservedSQLKeywords.add(keyWord);
}
}
/**
* checks if the database exists
* The default implementation performs a simple count query on the first table or view
* SELECT count(*) FROM table
* @return true if the database exists or false otherwise
*/
@Override
public boolean checkExists(DBDatabase db, Connection conn)
{
// Default implementation:
// Select the count from ANY table or view
List tables = db.getTables();
DBRowSet any = (tables.isEmpty() ? db.getViews().get(0) : tables.get(0));
String schema = db.getSchema();
String linkName = db.getLinkName();
// build the statement
DBSQLBuilder sql = createSQLBuilder();
sql.append("SELECT count(*) from ");
if (schema != null)
{ // Add Schema
sql.append(schema);
sql.append(".");
}
// Append the name
appendObjectName(sql, any.getName(), null);
if (linkName!=null)
{ // Database Link
sql.append(getSQLPhrase(DBSqlPhrase.SQL_DATABASE_LINK));
sql.append(linkName);
}
// Select now
try {
querySingleValue(sql.toString(), null, DataType.INTEGER, conn);
return true;
} catch(QueryFailedException e) {
// Database does not exist
return false;
}
}
/**
* Called when a database is opened
*/
@Override
public void attachDatabase(DBDatabase db, Connection conn)
{
/* Nothing here */
}
/**
* Called when a database is closed
*/
@Override
public void detachDatabase(DBDatabase db, Connection conn)
{
/* Nothing here */
}
/**
* This function creates a DBSQLBuilder for this DBMS
* @return a DBMS specific DBSQLBuilder object
*/
@Override
public DBSQLBuilder createSQLBuilder()
{
return new DBMSBuilder(this);
}
/**
* This function creates a DBCommand derived object this database
* @param autoPrepareStmt flag whether to automatically provide literal values as prepared statement params
* @return a DBCommand object
*/
@Override
public DBCommand createCommand(boolean autoPrepareStmt)
{
return new DBMSCommand(this, autoPrepareStmt);
}
/**
* This function gives the dbms a chance to provide a custom implementation
* for a combined command such as UNION or INTERSECT
* @param left the left command
* @param keyWord the key word (either "UNION" or "INTERSECT")
* @param left the right command
* @return a DBCommandExpr object
*/
@Override
public DBCommandExpr createCombinedCommand(DBCommandExpr left, String keyWord, DBCommandExpr right)
{
return new DBCombinedCmd(left, keyWord, right);
}
/**
* Returns whether or not a particular feature is supported by this dbms
* @param type type of requested feature. @see DBMSFeature
* @return true if the features is supported or false otherwise
*/
@Override
public abstract boolean isSupported(DBMSFeature type);
/**
* Detects whether a table or column name needs to be quoted or not
* By default all reserved SQL keywords as well as names
* containing a "-", "/", "+" or " " require quoting.
* Overrides this function to add database specific keywords like "user" or "count"
*/
public boolean detectQuoteName(String name)
{
// Check for reserved names
if (reservedSQLKeywords.contains(name.toLowerCase()))
return true;
// Check for illegalNameChars
int len = name.length();
for (int i=0; iic)
break;
if (c==ic)
return true;
}
}
// Quoting not necessary
return false;
}
/**
* Appends a table, view or column name to an SQL phrase.
*
* @param sql the StringBuilder containing the SQL phrase.
* @param name the name of the object (table, view or column)
* @param useQuotes use quotes or not
*/
@Override
public void appendObjectName(DBSQLBuilder sql, String name, Boolean useQuotes)
{
if (useQuotes==null)
useQuotes = detectQuoteName(name);
// Check whether to use quotes or not
if (useQuotes)
sql.append(getSQLPhrase(DBSqlPhrase.SQL_QUOTES_OPEN));
// Append Name
sql.append(name);
// End Quotes
if (useQuotes)
sql.append(getSQLPhrase(DBSqlPhrase.SQL_QUOTES_CLOSE));
}
/**
* Returns a timestamp that is used for record updates.
* @param conn the connection that might be used
* @return the current date and time.
*/
@Override
public Timestamp getUpdateTimestamp(Connection conn)
{
// Default implementation
java.util.Date date = new java.util.Date();
return new java.sql.Timestamp(date.getTime());
}
/**
* Returns the next value of a named sequence The numbers are used for fields of type DBExpr.DT_AUTOINC.
* If a dbms supports this function it must return true for isSupported(DBMSFeature.SEQUENCES).
*
* @param db the database
* @param SeqName the name of the sequence
* @param minValue the minimum value of the sequence
* @param conn a valid database connection
* @return a new unique sequence value or null if an error occurred
*/
public abstract Object getNextSequenceValue(DBDatabase db, String SeqName, int minValue, Connection conn);
/**
* Returns an expression for creating a sequence value.
* This is intended for the use with INSERT INTO statements where many records are affected.
* @param column the column for which to obtain an expression providing the next sequence value
* @return an expression for the next sequence value
*/
public abstract DBColumnExpr getNextSequenceValueExpr(DBTableColumn column);
/**
* Returns the sequence name of for a column of type AUTOINC
* The sequence name is usually provided as the default value
* If no Default value is provided the sequence name is generated from the table and the column name
*/
public String getColumnSequenceName(DBTableColumn column)
{
if (column.getDataType()!=DataType.AUTOINC)
throw new InvalidArgumentException("column", column);
// return the sequence name
Object seqName = column.getDefaultValue();
if (seqName!=null)
return seqName.toString();
// Auto-generate the sequence name
StringBuilder b = new StringBuilder(column.getRowSet().getName());
b.append("_");
b.append(column.getName());
b.append(SEQUENCE_NAME_SUFFIX);
seqName = b.toString();
// Store as default for later use
column.setDefaultValue(seqName);
return (String)seqName;
}
/**
* Returns an auto-generated value for a particular column
*
* @param db the database
* @param column the column for which a value is required
* @param conn a valid database connection
* @return the auto-generated value
*/
@Override
public Object getColumnAutoValue(DBDatabase db, DBTableColumn column, Connection conn)
{
// Supports sequences?
DataType type = column.getDataType();
if (type == DataType.AUTOINC)
{ // Use a numeric sequence
if (isSupported(DBMSFeature.SEQUENCES)==false)
return null; // Create Later
String sequenceName = getColumnSequenceName(column);
return getNextSequenceValue(db, sequenceName, 1, conn);
}
else if (type== DataType.UNIQUEID)
{ // emulate using java.util.UUID
return UUID.randomUUID();
}
else if (type==DataType.DATE || type==DataType.TIME || type==DataType.DATETIME || type==DataType.TIMESTAMP)
{ if (conn==null)
return null; // No connection
// Get database system's date and time
Date ts = getUpdateTimestamp(conn);
if (type==DataType.DATE)
return DateUtils.getDateOnly(ts);
if (type==DataType.TIME)
return DateUtils.getTimeOnly(ts);
return ts;
}
// Other types
throw new NotSupportedException(this, "getColumnAutoValue() for "+type);
}
/**
* Reads a single column value from the given JDBC ResultSet and returns a value object of desired data type.
*
* This gives the dbms the opportunity to change the value
* i.e. to simulate missing data types with other types.
*
* @param rset the sql Resultset with the current data row
* @param columnIndex one based column Index of the desired column
* @param dataType the required data type
*
* @return the value of the Column
*
* @throws SQLException if a database access error occurs
*/
@Override
public Object getResultValue(ResultSet rset, int columnIndex, DataType dataType)
throws SQLException
{
if (dataType == DataType.DATETIME || dataType == DataType.TIMESTAMP)
{ // Get Timestamp (do not use getObject()!)
return rset.getTimestamp(columnIndex);
}
else if (dataType == DataType.CLOB)
{
java.sql.Clob clob = rset.getClob(columnIndex);
return ((clob != null) ? clob.getSubString(1, (int) clob.length()) : null);
}
else if (dataType == DataType.BLOB)
{ // Get bytes of a binary large object
java.sql.Blob blob = rset.getBlob(columnIndex);
return ((blob != null) ? blob.getBytes(1, (int) blob.length()) : null);
}
else
{
return rset.getObject(columnIndex);
}
}
/**
* Executes the select, update or delete SQL-Command with a Statement object.
*
* @param sqlCmd the SQL-Command
* @param sqlParams array of sql command parameters used for prepared statements (Optional).
* @param conn a valid connection to the database.
* @param genKeys allows to set the auto generated key of a record (INSERT statements only)
*
* @return the row count for insert, update or delete or 0 for SQL statements that return nothing
*
* @throws SQLException if a database access error occurs
*/
@Override
public int executeSQL(String sqlCmd, Object[] sqlParams, Connection conn, DBSetGenKeys genKeys)
throws SQLException
{ // Execute the Statement
Statement stmt = null;
try
{
int count = 0;
if (sqlParams!=null)
{ // Use a prepared statement
PreparedStatement pstmt = (genKeys!=null)
? conn.prepareStatement(sqlCmd, Statement.RETURN_GENERATED_KEYS)
: conn.prepareStatement(sqlCmd);
stmt = pstmt;
prepareStatement(pstmt, sqlParams);
count = pstmt.executeUpdate();
}
else
{ // Execute a simple statement
stmt = conn.createStatement();
count = (genKeys!=null)
? stmt.executeUpdate(sqlCmd, Statement.RETURN_GENERATED_KEYS)
: stmt.executeUpdate(sqlCmd);
}
// Retrieve any auto-generated keys
if (genKeys!=null && count>0)
{ // Return Keys
ResultSet rs = stmt.getGeneratedKeys();
try {
int rownum = 0;
while(rs.next())
{
genKeys.set(rownum++, rs.getObject(1));
}
} finally {
rs.close();
}
}
// done
return count;
} finally {
closeStatement(stmt);
}
}
/**
* Executes a list of sql statements as batch
* @param sqlCmd an array of sql statements
* @param sqlCmdParams and array of statement parameters
* @param conn a JDBC connection
* @return an array containing the number of records affected by each statement
* @throws SQLException
*/
@Override
public int[] executeBatch(String[] sqlCmd, Object[][] sqlCmdParams, Connection conn)
throws SQLException
{ // Execute the Statement
if (sqlCmdParams!=null)
{ // Use a prepared statement
PreparedStatement pstmt = null;
try
{
int pos=0;
String lastCmd = null;
int[] result = new int[sqlCmd.length];
for (int i=0; i<=sqlCmd.length; i++)
{ // get cmd
String cmd = (i "+e.getMessage(), e);
closeStatement(stmt);
throw e;
}
}
/**
* Query a single value
* @return the value of the first column in the first row of the query
*/
@Override
public Object querySingleValue(String sqlCmd, Object[] sqlParams, DataType dataType, Connection conn)
{
ResultSet rs = null;
try
{ // Get the next Value
rs = executeQuery(sqlCmd, sqlParams, false, conn);
if (rs == null)
throw new UnexpectedReturnValueException(rs, "dbms.executeQuery()");
// Check Result
if (rs.next() == false)
{ // no result
log.trace("querySingleValue for {} returned no result", sqlCmd);
return ObjectUtils.NO_VALUE;
}
// Read value
return getResultValue(rs, 1, dataType);
} catch (SQLException sqle)
{ // Error
throw new QueryFailedException(this, sqlCmd, sqle);
} finally {
// Cleanup
closeResultSet(rs);
}
}
/**
* Appends a statement to enable or disable a foreign key relation.
* The default is to drop or create the relation
* Override this method to provide different behavior for your database.
* @param r the foreign key relation which should be enabled or disabled
* @param enable true to enable the relation or false to disable
* @param script the script to which to add the DDL command(s)
*/
@Override
public void appendEnableRelationStmt(DBRelation r, boolean enable, DBSQLScript script)
{
if (enable)
getDDLScript(DDLActionType.CREATE, r, script);
else
getDDLScript(DDLActionType.DROP, r, script);
}
/**
* Creates a DataModelParser instance of this DBMSHandler
* @return
*/
@Override
public DBModelParser createModelParser(String catalog, String schema)
{
return new DBModelParser(catalog, schema);
}
/**
* Creates a DataModelChecker instance of this DBMSHandler
* @return
*/
@Override
public DBModelChecker createModelChecker(DBDatabase db)
{
log.warn("A general and possibly untested DBModelChecker is used for DBMSHandler {}. Please override to inklude DBMS specific features.", getClass().getSimpleName());
// the default model checker
DBModelParser modelParser = createModelParser(null, db.getSchema());
return new DBModelChecker(modelParser);
}
/**
* Extracts native error message of an sqlExeption.
*
* @param e the SQLException
* @return the error message of the database
*/
@Override
public String extractErrorMessage(SQLException e)
{
return e.getMessage();
}
/**
* Convenience function for closing a JDBC Resultset
* Use it instead of rset.close() and stmt.close()
*
* @param rset a ResultSet object
*/
@Override
public void closeResultSet(ResultSet rset)
{
try
{ // check ResultSet
if (rset == null)
return; // nothing to do
// close Resultset
Statement stmt = rset.getStatement();
rset.close();
// check Statement
if (stmt == null)
return;
// close Statement
stmt.close();
// done
return;
} catch (SQLException sqle) {
// Commit failed!
throw new EmpireSQLException(this, sqle);
}
}
/**
* Convenience function for closing a JDBC Resultset
* Use it instead of stmt.close()
*
* @param stmt a Statement object
*/
protected void closeStatement(Statement stmt)
{
try
{ // Statement close
if (stmt != null)
stmt.close();
// done
return;
} catch (SQLException sqle) {
// Commit failed!
throw new EmpireSQLException(this, sqle);
}
}
/**
* Prepares an sql statement by setting the supplied objects as parameters.
*
* @param pstmt the prepared statement
* @param sqlParams list of objects
*/
protected void prepareStatement(PreparedStatement pstmt, Object[] sqlParams)
throws SQLException
{
for (int i=0; i))
{
// Objects that need String conversion
String strval = value.toString();
pstmt.setObject(paramIndex, strval);
// log
if (log.isTraceEnabled())
log.trace("Statement param {} set to '{}'", paramIndex, strval);
}
else
{ // simple parameter value
pstmt.setObject(paramIndex, value);
// log
if (log.isTraceEnabled())
log.trace("Statement param {} set to '{}'", paramIndex, value);
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy