com.hfg.sql.SQLUtil Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of com_hfg Show documentation
Show all versions of com_hfg Show documentation
com.hfg xml, html, svg, and bioinformatics utility library
package com.hfg.sql;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.hfg.datetime.DateUtil;
import com.hfg.sql.jdbc.JDBCConnection;
import com.hfg.sql.jdbc.JDBCException;
import com.hfg.sql.jdbc.JDBCResultSet;
import com.hfg.sql.jdbc.SQLStatementOptions;
import com.hfg.sql.table.DatabaseCol;
import com.hfg.sql.table.DatabaseTable;
import com.hfg.util.StackTraceElementFilter;
import com.hfg.util.StackTraceUtil;
import com.hfg.util.StringBuilderPlus;
import com.hfg.util.StringUtil;
import com.hfg.util.collection.CollectionUtil;
//------------------------------------------------------------------------------
/**
SQL utility functions.
@author J. Alex Taylor, hairyfatguy.com
*/
//------------------------------------------------------------------------------
// com.hfg XML/HTML Coding Library
//
// This library is free software; you can redistribute it and/or
// modify it under the terms of the GNU Lesser General Public
// License as published by the Free Software Foundation; either
// version 2.1 of the License, or (at your option) any later version.
//
// This library 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
// Lesser General Public License for more details.
//
// You should have received a copy of the GNU Lesser General Public
// License along with this library; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
//
// J. Alex Taylor, President, Founder, CEO, COO, CFO, OOPS hairyfatguy.com
// [email protected]
//------------------------------------------------------------------------------
public class SQLUtil
{
public static Level sSQLLoggingLevel = Level.FINE;
private final static Logger LOGGER = Logger.getLogger(SQLUtil.class.getName());
private static List sStackTraceExclusions = new ArrayList<>(4);
static
{
LOGGER.setUseParentHandlers(false);
sStackTraceExclusions.add(new StackTraceElementFilter().setPackage(SQLUtil.class.getPackage()));
sStackTraceExclusions.add(new StackTraceElementFilter().setPackage(DatabaseTable.class.getPackage()));
sStackTraceExclusions.add(new StackTraceElementFilter().setClass(JDBCConnection.class).setMethodName("execute"));
}
//---------------------------------------------------------------------------
public static Logger getLogger()
{
return LOGGER;
}
//---------------------------------------------------------------------------
public static String sqlString(Object inValue)
{
return inValue != null ? sqlString(inValue.toString()) : "null";
}
//---------------------------------------------------------------------------
public static String sqlString(String inValue)
{
return inValue != null ? "'" + StringUtil.replaceAll(inValue, "\'", "\'\'") + "'" : "null";
}
//---------------------------------------------------------------------------
/**
All single and double quotes internal to the string are doubled for escaping.
@param inValue the value string to be escaped for single and double quotes
@return the escaped value
*/
public static String sqlStringWithDoubleQuotes(String inValue)
{
return inValue != null ? "\"" + StringUtil.replaceAll(StringUtil.replaceAll(inValue, "\"", "\"\""), "\'", "\'\'") + "\"" : "null";
}
//---------------------------------------------------------------------------
public static String sqlStringList(Collection inValues)
{
String listString = "";
if (CollectionUtil.hasValues(inValues))
{
List sqlStrings = new ArrayList<>(inValues.size());
for (Object object : inValues)
{
sqlStrings.add(sqlString(object));
}
listString = StringUtil.join(sqlStrings, ", ");
}
return listString;
}
//---------------------------------------------------------------------------
public static boolean execute(Connection inConn, String inSQL)
throws SQLException
{
boolean result;
Statement stmt = null;
try
{
stmt = inConn.createStatement();
result = execute(stmt, inSQL);
}
finally
{
SQLUtil.close(stmt);
}
return result;
}
//---------------------------------------------------------------------------
public static boolean execute(PreparedStatement inPreparedStatement)
throws JDBCException
{
return execute(inPreparedStatement, null);
}
//---------------------------------------------------------------------------
public static boolean execute(Statement inStatement, String inSQL)
throws JDBCException
{
boolean result;
long startTime = System.currentTimeMillis();
try
{
if (inStatement instanceof PreparedStatement)
{
PreparedStatement ps = (PreparedStatement) inStatement;
inSQL = ps.toString();
result = ((PreparedStatement) inStatement).execute();
}
else
{
result = inStatement.execute(inSQL);
}
}
catch (Exception e)
{
throw new JDBCException("Error executing SQL:\n" + inSQL, e);
}
if (LOGGER.isLoggable(sSQLLoggingLevel))
{
// Generate debugging info w/ the SQL run and its execution time
StringBuilderPlus msg = new StringBuilderPlus()
.appendln("[" + StackTraceUtil.getCallingStackTraceElement(sStackTraceExclusions) + "] SQL:\n")
.appendln(inSQL)
.appendln("Execution Time: " + DateUtil.generateElapsedTimeString(startTime));
LOGGER.log(sSQLLoggingLevel, msg.toString());
}
return result;
}
//---------------------------------------------------------------------------
public static int executeUpdate(Connection inConn, String inSQL)
throws SQLException
{
int rowsUpdated;
Statement stmt = null;
try
{
stmt = inConn.createStatement();
rowsUpdated = executeUpdate(stmt, inSQL);
}
finally
{
SQLUtil.close(stmt);
}
return rowsUpdated;
}
//---------------------------------------------------------------------------
public static int executeUpdate(PreparedStatement inPreparedStatement)
throws SQLException
{
return executeUpdate(inPreparedStatement, null);
}
//---------------------------------------------------------------------------
public static int executeUpdate(Statement inStatement, CharSequence inSQL)
throws SQLException
{
int result;
long startTime = System.currentTimeMillis();
try
{
if (inStatement instanceof PreparedStatement)
{
PreparedStatement ps = (PreparedStatement) inStatement;
inSQL = ps.toString();
result = ((PreparedStatement) inStatement).executeUpdate();
}
else
{
result = inStatement.executeUpdate(inSQL.toString());
}
}
catch (Exception e)
{
throw new JDBCException("Error executing SQL:\n" + inSQL, e);
}
if (LOGGER.isLoggable(sSQLLoggingLevel))
{
// Generate debugging info w/ the SQL run and its execution time
StringBuilderPlus msg = new StringBuilderPlus()
.appendln("[" + StackTraceUtil.getCallingStackTraceElement(sStackTraceExclusions) + "] SQL:")
.appendln(inSQL)
.appendln("Rows " + (isDelete(inSQL) ? "Deleted" : "Updated") + ": " + result)
.appendln("Execution Time: " + DateUtil.generateElapsedTimeString(startTime));
LOGGER.log(sSQLLoggingLevel, msg.toString());
}
return result;
}
//---------------------------------------------------------------------------
public static ResultSet executeQuery(Connection inConn, String inSQL)
throws SQLException
{
return executeQuery(inConn, inSQL, null);
}
//---------------------------------------------------------------------------
public static ResultSet executeQuery(Connection inConn, String inSQL, SQLStatementOptions inOptions)
throws SQLException
{
Statement stmt = null;
ResultSet rs;
try
{
stmt = inConn.createStatement();
if (inOptions != null)
{
inOptions.configureStmt(stmt);
}
rs = executeQuery(stmt, inSQL);
}
catch (SQLException e)
{
close(stmt);
throw e;
}
return rs;
}
//---------------------------------------------------------------------------
public static int executeQuery(PreparedStatement inPreparedStatement)
throws SQLException
{
return executeUpdate(inPreparedStatement, null);
}
//---------------------------------------------------------------------------
public static ResultSet executeQuery(Statement inStatement, String inSQL)
throws JDBCException
{
ResultSet rs;
long startTime = System.currentTimeMillis();
try
{
if (inStatement instanceof PreparedStatement
&& null == inSQL)
{
PreparedStatement ps = (PreparedStatement) inStatement;
inSQL = ps.toString();
rs = ((PreparedStatement) inStatement).executeQuery();
}
else
{
rs = inStatement.executeQuery(inSQL);
}
}
catch (Exception e)
{
throw new JDBCException("Error executing SQL:\n" + inSQL, e);
}
if (LOGGER.isLoggable(sSQLLoggingLevel))
{
// Generate debugging info w/ the SQL run and its execution time
StringBuilderPlus msg = new StringBuilderPlus()
.appendln("[" + StackTraceUtil.getCallingStackTraceElement(sStackTraceExclusions) + "] SQL:\n")
.appendln(inSQL)
.appendln("Execution Time: " + DateUtil.generateElapsedTimeString(startTime));
LOGGER.log(sSQLLoggingLevel, msg.toString());
}
return new JDBCResultSet(rs);
}
//---------------------------------------------------------------------------
public static void closeResultSetAndStatement(ResultSet inResultSet)
throws JDBCException
{
try
{
if (inResultSet != null)
{
Statement stmt = inResultSet.getStatement();
stmt.close();
inResultSet.close();
}
} catch (SQLException e)
{
throw new JDBCException(e);
}
}
//---------------------------------------------------------------------------
public static void close(ResultSet inResultSet)
throws JDBCException
{
try
{
if (inResultSet != null)
{
inResultSet.close();
}
} catch (SQLException e)
{
throw new JDBCException(e);
}
}
//---------------------------------------------------------------------------
public static void close(Statement inStmt)
throws JDBCException
{
try
{
if (inStmt != null)
{
inStmt.close();
}
}
catch (SQLException e)
{
throw new JDBCException(e);
}
}
//---------------------------------------------------------------------------
public static void close(Connection inConn)
throws JDBCException
{
try
{
if (inConn != null)
{
inConn.close();
}
}
catch (SQLException e)
{
throw new JDBCException(e);
}
}
//---------------------------------------------------------------------------
/**
* Splits a SQL script into individual commands and strips line and block comments.
* @param inSQL the input SQL script
* @return a List of SQL commands as Strings.
* @throws IOException
*/
public static List splitBatchSQL(Reader inSQL)
throws IOException
{
List sqlCommands = new ArrayList<>();
BufferedReader bufferedReader;
if (inSQL instanceof BufferedReader)
{
bufferedReader = (BufferedReader) inSQL;
}
else
{
bufferedReader = new BufferedReader(inSQL);
}
StringBuilderPlus currentSQLCommand = new StringBuilderPlus();
// In-line SQL comments start with two dashes and continue to the end of the line. Ex: '-- my comment'
// Block SQL comments look like block comments in C or Java. Ex: '/* my comment */'
boolean inInlineComment = false;
boolean inBlockComment = false;
boolean inQuotedContent = false;
boolean firstOpeningDollarEncountered = false;
boolean firstClosingDollarEncountered = false;
boolean inDollarQuotedContent = false;
String line;
while ((line = bufferedReader.readLine()) != null)
{
inInlineComment = false;
char[] lineChars = line.toCharArray();
for (int i = 0; i < line.length(); i++)
{
char theChar = lineChars[i];
if (theChar == ';'
&& ! inInlineComment
&& ! inBlockComment
&& ! inQuotedContent
&& ! inDollarQuotedContent)
{
String sql = currentSQLCommand.toString().trim();
if (StringUtil.isSet(sql))
{
sqlCommands.add(sql);
}
currentSQLCommand.setLength(0);
}
else
{
if (! inBlockComment
&& ! inInlineComment)
{
currentSQLCommand.append(theChar);
}
if (theChar == '$')
{
if (! firstOpeningDollarEncountered)
{
firstOpeningDollarEncountered = true;
}
else if (! inDollarQuotedContent)
{
inDollarQuotedContent = true;
}
else if (! firstClosingDollarEncountered)
{
firstClosingDollarEncountered = true;
}
else
{
// This is the ending '$' for dollar-quoted content
firstOpeningDollarEncountered = false;
inDollarQuotedContent = false;
firstClosingDollarEncountered = false;
}
}
// Is this the beginning of an inline comment?
else if (theChar == '-'
&& i > 0
&& lineChars[i - 1] == '-')
{
// Remove the start of comment characters
if (! inBlockComment)
{
currentSQLCommand.setLength(currentSQLCommand.length() - 2);
}
inInlineComment = true;
}
else if (inBlockComment // Is it the end of a block comment?
&& theChar == '/'
&& i > 0
&& lineChars[i - 1] == '*')
{
inBlockComment = false;
}
else if (! inBlockComment // Is it the beginning of a block comment?
&& theChar == '*'
&& i > 0
&& lineChars[i - 1] == '/')
{
inBlockComment = true;
currentSQLCommand.setLength(currentSQLCommand.length() - 2);
}
else if (inQuotedContent // Is it end of some quoted content?
&& theChar == '\''
&& (0 == i || lineChars[i - 1] != '\\'))
{
inQuotedContent = false;
}
else if (! inQuotedContent // Is it start of some quoted content?
&& theChar == '\''
&& (0 == i || lineChars[i - 1] != '\\'))
{
inQuotedContent = true;
}
}
}
currentSQLCommand.appendln();
}
// Process remaining content
String sql = currentSQLCommand.toString().trim();
if (StringUtil.isSet(sql))
{
sqlCommands.add(sql);
}
bufferedReader.close();
return sqlCommands;
}
//---------------------------------------------------------------------------
public static Integer getInteger(ResultSet inResultSet, String inColumnLabel)
throws SQLException
{
Integer value = inResultSet.getInt(inColumnLabel);
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Integer getInteger(ResultSet inResultSet, DatabaseCol inColumn)
throws SQLException
{
Integer value = inResultSet.getInt(inColumn.name());
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Integer getInteger(ResultSet inResultSet, int inColumnIndex)
throws SQLException
{
Integer value = inResultSet.getInt(inColumnIndex);
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Long getLong(ResultSet inResultSet, String inColumnLabel)
throws SQLException
{
Long value = inResultSet.getLong(inColumnLabel);
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Long getLong(ResultSet inResultSet, DatabaseCol inColumn)
throws SQLException
{
Long value = inResultSet.getLong(inColumn.name());
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Long getLong(ResultSet inResultSet, int inColumnIndex)
throws SQLException
{
Long value = inResultSet.getLong(inColumnIndex);
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Float getFloat(ResultSet inResultSet, String inColumnLabel)
throws SQLException
{
Float value = inResultSet.getFloat(inColumnLabel);
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Float getFloat(ResultSet inResultSet, DatabaseCol inColumn)
throws SQLException
{
Float value = inResultSet.getFloat(inColumn.name());
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Float getFloat(ResultSet inResultSet, int inColumnIndex)
throws SQLException
{
Float value = inResultSet.getFloat(inColumnIndex);
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Double getDouble(ResultSet inResultSet, String inColumnLabel)
throws SQLException
{
Double value = inResultSet.getDouble(inColumnLabel);
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Double getDouble(ResultSet inResultSet, DatabaseCol inColumn)
throws SQLException
{
Double value = inResultSet.getDouble(inColumn.name());
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
public static Double getDouble(ResultSet inResultSet, int inColumnIndex)
throws SQLException
{
Double value = inResultSet.getDouble(inColumnIndex);
return (inResultSet.wasNull() ? null : value);
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setString().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setString(PreparedStatement inPreparedStatement, int inIndex, String inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setString(inIndex, inValue);
}
else
{
inPreparedStatement.setNull(inIndex, Types.VARCHAR);
}
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setFloat().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setFloat(PreparedStatement inPreparedStatement, int inIndex, Float inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setFloat(inIndex, inValue);
}
else
{
inPreparedStatement.setNull(inIndex, Types.FLOAT);
}
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setDouble().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setDouble(PreparedStatement inPreparedStatement, int inIndex, Double inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setDouble(inIndex, inValue);
}
else
{
inPreparedStatement.setNull(inIndex, Types.FLOAT);
}
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setInt().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setInt(PreparedStatement inPreparedStatement, int inIndex, Integer inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setInt(inIndex, inValue);
}
else
{
inPreparedStatement.setNull(inIndex, Types.BIGINT);
}
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setShort().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setShort(PreparedStatement inPreparedStatement, int inIndex, Short inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setShort(inIndex, inValue);
}
else
{
inPreparedStatement.setNull(inIndex, Types.SMALLINT);
}
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setLong().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setLong(PreparedStatement inPreparedStatement, int inIndex, Long inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setLong(inIndex, inValue);
}
else
{
inPreparedStatement.setNull(inIndex, Types.BIGINT);
}
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setDate().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setDate(PreparedStatement inPreparedStatement, int inIndex, java.util.Date inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setDate(inIndex, new Date(inValue.getTime()));
}
else
{
inPreparedStatement.setNull(inIndex, Types.BIGINT);
}
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setDate().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setDate(PreparedStatement inPreparedStatement, int inIndex, Date inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setDate(inIndex, inValue);
}
else
{
inPreparedStatement.setNull(inIndex, Types.BIGINT);
}
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setTimestamp().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setTimestamp(PreparedStatement inPreparedStatement, int inIndex, Timestamp inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setTimestamp(inIndex, inValue);
}
else
{
inPreparedStatement.setNull(inIndex, Types.TIMESTAMP_WITH_TIMEZONE);
}
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setArray().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setArray(PreparedStatement inPreparedStatement, int inIndex, Array inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setArray(inIndex, inValue);
}
else
{
inPreparedStatement.setNull(inIndex, Types.ARRAY);
}
}
//---------------------------------------------------------------------------
/**
A null-tolerant wrapper for PreparedStatement's setBytes().
@param inPreparedStatement the PreparedStatement in which to set the value
@param inIndex the parameter index where the value should be placed
@param inValue the value to insert into the PreparedStatement
@throws SQLException
*/
public static void setBytes(PreparedStatement inPreparedStatement, int inIndex, byte[] inValue)
throws SQLException
{
if (inValue != null)
{
inPreparedStatement.setBytes(inIndex, inValue);
}
else
{
inPreparedStatement.setNull(inIndex, Types.BINARY);
}
}
//---------------------------------------------------------------------------
/**
A convenience method for getting a count of rows that meet a specific criteria.
@param inConn the database connection
@param inTable the database table
@param inWhereClause the where clause to use
*/
public static int getRowCount(Connection inConn, DatabaseTable inTable, WhereClause inWhereClause)
throws SQLException
{
List clauses = new ArrayList<>(1);
clauses.add(inWhereClause);
return getRowCount(inConn, inTable, clauses);
}
//---------------------------------------------------------------------------
/**
A convenience method for getting a count of rows that meet a specific criteria.
@param inConn the database connection
@param inTable the database table
@param inClauses additional clauses to use
*/
public static int getRowCount(Connection inConn, DatabaseTable inTable, Collection inClauses)
throws SQLException
{
int count = 0;
SQLQuery query = new SQLQuery()
.addSelect("count('x')")
.addFrom(inTable);
if (CollectionUtil.hasValues(inClauses))
{
query.addClauses(inClauses);
}
ResultSet rs = null;
try
{
rs = query.execute(inConn);
if (rs.next())
{
count = rs.getInt(1);
}
}
finally
{
SQLUtil.close(rs);
}
return count;
}
//---------------------------------------------------------------------------
private static boolean isDelete(CharSequence inSQL)
{
boolean isDelete = false;
String[] lines = StringUtil.lines(inSQL);
for (String line : lines)
{
line = line.trim();
if (! line.startsWith("-- ")) // Skip any comment lines
{
isDelete = line.toUpperCase().startsWith("DELETE");
break;
}
}
return isDelete;
}
}