package hirondelle.web4j.database;
import static hirondelle.web4j.util.Consts.NEW_LINE;
import hirondelle.web4j.BuildImpl;
import hirondelle.web4j.model.AppException;
import hirondelle.web4j.model.DateTime;
import hirondelle.web4j.model.Decimal;
import hirondelle.web4j.model.Id;
import hirondelle.web4j.model.DateTime.Unit;
import hirondelle.web4j.readconfig.ConfigReader;
import hirondelle.web4j.security.SafeText;
import hirondelle.web4j.util.Util;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Enumeration;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.TimeZone;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
Encapsulates an SQL statement used in the application.
<P><span class='highlight'>See package overview for important information</span>.
<P>This class hides details regarding all SQL statements used by the application. These items
are hidden from the caller of this class :
<ul>
<li>the retrieval of SQL statements from an underlying textual <tt>.sql</tt> file or
files
<li>the textual content of SQL statements
<li>the details of placing parameters into a {@link PreparedStatement}
</ul>
<P> Only {@link PreparedStatement} objects are used here, since they <a
href="http://www.javapractices.com/Topic212.cjp">are usually preferable</a> to
{@link Statement} objects.
*/
final class SqlStatement {
/**
Called by the framework upon startup, to read and validate all SQL statements from the
underlying <tt>*.sql</tt> text file(s).
<P>Verifies that there is no mismatch
whatsoever between the <tt>public static final</tt> {@link SqlId} fields used in the
application, and the keys of the corresponding <tt>*.sql</tt> file(s). If there is a
mismatch, then an exception is thrown when this class loads, to ensure that errors are
reported as soon as possible.
<P> Upon startup, the framework can optionally
attempt a test precompile of each SQL statement, by calling
{@link Connection#prepareStatement(String)}. If the SQL text is not syntactically
correct, then a call to <tt>Connection.prepareStatement()</tt>
<em>might</em> throw
an {@link SQLException}, according to the implementation of the driver/database. (For
example, JavaDB/Derby will throw an {@link SQLException}, while MySql and Oracle will not.)
If such an error is detected, then it is logged as <tt>SEVERE</tt>.
<P>A setting in <tt>web.xml</tt> can disable this pre-compilation, if desired.
*/
static void readSqlFile() {
readSqlText();
checkStoredProcedures();
checkSqlFilesVersusSqlIdFields();
precompileAll();
}
/**
SQL statement which takes parameters.
<P>This class supports the same classes as parameters as {@link ConvertColumnImpl}.
That is, only objects of the those classes can be present in the <tt>aSqlParams</tt>
list. A parameter may also be <tt>null</tt>.
<P> For <tt>Id</tt> objects, in particular, the underlying column must modeled as text,
not a number. If the underlying column is numeric, then the caller must convert an
{@link Id} into a numeric form using {@link Id#asInteger} or {@link Id#asLong}.
@param aSqlId corresponds to a key in the underlying <tt>.sql</tt> file
@param aSearchCriteria is possibly <tt>null</tt>, and represents the criteria input
by the user during a search operation for a particular record (or records). If present,
then {@link DynamicCriteria#toString()} is appended to the text of the underlying SQL
statement from the <tt>.sql</tt> files.
@param aSqlParams contains at least one object of the supported classes noted above;
<span class="highlight">the number and order of these parameter objects matches the
number and order of "?" parameters in the underlying SQL</span>.
*/
SqlStatement(SqlId aSqlId, DynamicCriteria aSearchCriteria, Object... aSqlParams) {
fSqlId = aSqlId;
fSqlText = getSqlTextFromId(aSqlId);
if (aSearchCriteria != null) {
fSqlText = fSqlText + aSearchCriteria.toString();
}
checkNumParamsMatches(aSqlParams);
checkParamsOfSupportedType(aSqlParams);
fParams = aSqlParams;
fLogger.finest(this.toString());
}
/**
Return a {@link PreparedStatement} whose parameters, if any, have been populated using
the <tt>aSqlParams</tt> passed to the constructor.
<P>If the underlying database auto-generates any keys by executing the returned
<tt>PreparedStatement</tt>, they will be available from the returned value using
{@link Statement#getGeneratedKeys}.
<P>If the returned statement is a <tt>SELECT</tt>, then a limit, as configured in
<tt>web.xml</tt>, is placed on the maximum number of rows which can be returned.
This is meant as a defensive safety measure, to avoid returning an excessively large
number of rows.
*/
PreparedStatement getPreparedStatement(Connection aConnection) throws SQLException {
PreparedStatement result = null;
result = getPS(fSqlText, aConnection, fSqlId);
populateParamsUsingPS(result);
result.setMaxRows(DbConfig.getMaxRows(fSqlId.getDatabaseName()).intValue());
result.setFetchSize(DbConfig.getFetchSize(fSqlId.getDatabaseName()).intValue());
return result;
}
/** Return the {@link SqlId} passed to the constructor. */
public SqlId getSqlId() {
return fSqlId;
}
/**
Return the number of <tt>'?'</tt> placeholders appearing in the underlying SQL
statement.
*/
static int getNumParameters(SqlId aSqlId) {
int result = 0;
String sqlText = getSqlTextFromId(aSqlId);
result = getNumParams(fQUESTION_MARK, sqlText);
return result;
}
/** Intended for debugging only. */
@Override public String toString() {
StringBuilder result = new StringBuilder();
result.append(fSqlId);
result.append(" {");
result.append(NEW_LINE);
result.append(" fSqlText = ").append(fSqlText).append(NEW_LINE);
List<Object> params = Arrays.asList(fParams);
result.append(" Params = ").append(params).append(NEW_LINE);
result.append("}");
result.append(NEW_LINE);
return result.toString();
}
// PRIVATE
/** The id of the SQL statement, as named in the underlying .sql file. */
private final SqlId fSqlId;
/** Parameter values to be placed into a SQL statement. */
private final Object[] fParams;
/**
The raw text of the SQL statement, as retrieved from the underlying *.sql file(s) (for
example "SELECT Name FROM Blah").
*/
private String fSqlText;
/** Contents of the underlying *.sql file(s). */
private static Properties fSqlProperties;
private static final Pattern fQUESTION_MARK = Pattern.compile("\\?");
private static final Pattern fSQL_PROPERTIES_FILE_NAME_PATTERN = Pattern.compile("(?:.)*\\.sql");
private static final String fTESTING_SQL_PROPERTIES = "C:\\johanley\\Projects\\webappskeleton\\WEB-INF\\mysql.sql";
private static final String fSTORED_PROC = "{call";
private static final Pattern fSELECT_PATTERN = Pattern.compile("^SELECT", Pattern.CASE_INSENSITIVE);
private static final String fUNSUPPORTED_STORED_PROC = "{?=";
private static final Logger fLogger = Util.getLogger(SqlStatement.class);
private static PreparedStatement getPS(String aSqlText, Connection aConnection, SqlId aSqlId) throws SQLException {
PreparedStatement result = null;
if (isStoredProcedure(aSqlText)) {
result = aConnection.prepareCall(aSqlText);
}
else {
if (isSelect(aSqlText)) {
// allow scrolling of SELECT result sets
result = aConnection.prepareStatement(aSqlText, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
}
else {
if (DbConfig.hasAutogeneratedKeys(aSqlId.getDatabaseName())) {
result = aConnection.prepareStatement(aSqlText, Statement.RETURN_GENERATED_KEYS);
}
else {
result = aConnection.prepareStatement(aSqlText);
}
}
}
return result;
}
private void populateParamsUsingPS(PreparedStatement aStatement) throws SQLException {
// parameter indexes are 1-based, not 0-based.
for (int idx = 1; idx <= fParams.length; ++idx) {
Object param = fParams[idx - 1];
if (param == null) {
fLogger.finest("Param" + idx + ": null");
// is there a better way of doing this?
// setNull needs the type of the underlying column, which is not available
aStatement.setString(idx, null);
}
else if (param instanceof String) {
fLogger.finest("Param" + idx + ": String");
aStatement.setString(idx, (String)param);
}
else if (param instanceof Integer) {
fLogger.finest("Param" + idx + ": Integer");
Integer paramVal = (Integer)param;
aStatement.setInt(idx, paramVal.intValue());
}
else if (param instanceof Boolean) {
fLogger.finest("Param" + idx + ": Boolean");
Boolean paramVal = (Boolean)param;
aStatement.setBoolean(idx, paramVal.booleanValue());
}
else if (param instanceof hirondelle.web4j.model.DateTime) {
fLogger.finest("Param" + idx + ": hirondelle.web4j.model.DateTime");
setDateTime(param, aStatement, idx);
}
else if (param instanceof java.util.Date) {
fLogger.finest("Param" + idx + ": Date");
setDate(param, aStatement, idx);
}
else if (param instanceof java.math.BigDecimal) {
fLogger.finest("Param" + idx + ": BigDecimal");
aStatement.setBigDecimal(idx, (BigDecimal)param);
}
else if (param instanceof Decimal) {
fLogger.finest("Param" + idx + ": Decimal");
Decimal value = (Decimal)param;
aStatement.setBigDecimal(idx, value.getAmount());
}
else if (param instanceof Long) {
fLogger.finest("Param" + idx + ": Long");
Long paramVal = (Long)param;
aStatement.setLong(idx, paramVal.longValue());
}
else if (param instanceof Id) {
fLogger.finest("Param" + idx + ": Id");
Id paramId = (Id)param;
aStatement.setString(idx, paramId.getRawString());
}
else if (param instanceof SafeText) {
fLogger.finest("Param" + idx + ": SafeText");
SafeText paramText = (SafeText)param;
aStatement.setString(idx, paramText.getRawString());
}
else if (param instanceof Locale){
fLogger.finest("Param" + idx + ": Locale");
Locale locale = (Locale)param;
String nonLocalizedId = locale.toString(); //en_US_south; independent of any JRE locale
aStatement.setString(idx, nonLocalizedId);
}
else if (param instanceof TimeZone){
fLogger.finest("Param" + idx + ": TimeZone");
TimeZone timeZone = (TimeZone)param;
String nonLocalizedId = timeZone.getID(); //America/Montreal
aStatement.setString(idx, nonLocalizedId);
}
else {
throw new IllegalArgumentException("Unsupported type of parameter: " + param.getClass());
}
}
}
private void setDate(Object aParam, PreparedStatement aStatement, int aIdx) throws SQLException {
// java.sql.Date has date only, and java.sql.Time has time only
java.util.Date dateUtil = (java.util.Date)aParam;
java.sql.Timestamp timestampSql = new java.sql.Timestamp(dateUtil.getTime());
if (DbConfig.hasTimeZoneHint()) {
aStatement.setTimestamp(aIdx, timestampSql, DbConfig.getTimeZoneHint());
}
else {
aStatement.setTimestamp(aIdx, timestampSql);
}
}
private void setDateTime(Object aParam, PreparedStatement aStatement, int aIdx) throws SQLException {
DateTime dateTime = (DateTime)aParam;
String formattedDateTime = "";
if (
dateTime.unitsAllPresent(Unit.YEAR, Unit.MONTH, Unit.DAY) &&
dateTime.unitsAllAbsent(Unit.HOUR, Unit.MINUTE, Unit.SECOND)
){
fLogger.finest("Treating DateTime as a date (year-month-day).");
formattedDateTime = dateTime.format(DbConfig.getDateFormat(fSqlId.getDatabaseName()));
}
else if (
dateTime.unitsAllAbsent(Unit.YEAR, Unit.MONTH, Unit.DAY) &&
dateTime.unitsAllPresent(Unit.HOUR, Unit.MINUTE, Unit.SECOND)
){
fLogger.finest("Treating DateTime as a time (hour-minute-second).");
formattedDateTime = dateTime.format(DbConfig.getTimeFormat(fSqlId.getDatabaseName()));
}
else if (
dateTime.unitsAllPresent(Unit.YEAR, Unit.MONTH, Unit.DAY) &&
dateTime.unitsAllPresent(Unit.HOUR, Unit.MINUTE, Unit.SECOND)
) {
fLogger.finest("Treating DateTime as a date+time (year-month-day-hour-minute-second).");
formattedDateTime = dateTime.format(DbConfig.getDateTimeFormat(fSqlId.getDatabaseName()));
}
else {
String message =
"Unable to format DateTime using the DateTimeFormatForPassingParamsToDb setting in web.xml." +
" The units present in the DateTime object do not match any of the expected combinations. " +
"If needed, you can always format the DateTime manually in your DAO, and pass a String to the database instead of a DateTime."
;
fLogger.severe(message);
throw new IllegalArgumentException(message);
}
aStatement.setString(aIdx, formattedDateTime);
}
private static String getSqlTextFromId(SqlId aSqlId) {
return fSqlProperties.getProperty(aSqlId.toString());
}
private void checkNumParamsMatches(Object[] aSqlParams) {
checkNumParams(fQUESTION_MARK, aSqlParams);
}
private static boolean isStoredProcedure(String aSqlText) {
return aSqlText.startsWith(fSTORED_PROC);
}
private static boolean isSelect(String aSqlText) {
return Util.contains(fSELECT_PATTERN, aSqlText);
}
private void checkNumParams(Pattern aPattern, Object[] aParams) {
Matcher matcher = aPattern.matcher(fSqlText);
int numParams = 0;
while (matcher.find()) {
++numParams;
}
if (numParams != aParams.length) {
throw new IllegalArgumentException(aParams.length + " params should be " + numParams);
}
}
private static int getNumParams(Pattern aPlaceholderPattern, String aSqlText) {
int result = 0;
Matcher matcher = aPlaceholderPattern.matcher(aSqlText);
while (matcher.find()) {
++result;
}
return result;
}
private void checkParamsOfSupportedType(Object[] aSqlParams) {
for (Object param : aSqlParams) {
if (!isSupportedType(param)) {
throw new IllegalArgumentException("Unsupported type of SQL parameter: " + param.getClass());
}
}
}
private boolean isSupportedType(Object aParam) {
return aParam == null || BuildImpl.forConvertParam().isSupported(aParam.getClass());
}
private static void readSqlText() {
if (fSqlProperties != null) {
fSqlProperties.clear();
}
if (!DbConfig.isTestingMode()) {
fSqlProperties = ConfigReader.fetchMany(fSQL_PROPERTIES_FILE_NAME_PATTERN, ConfigReader.FileType.TEXT_BLOCK);
}
else {
fSqlProperties = ConfigReader.fetchForTesting(fTESTING_SQL_PROPERTIES, ConfigReader.FileType.TEXT_BLOCK);
}
}
private static void checkSqlFilesVersusSqlIdFields() {
Map sqlIdFields = ConfigReader.fetchPublicStaticFinalFields(SqlId.class);
Set<String> sqlIdStrings = convertToSetOfStrings(sqlIdFields);
fLogger.config("SqlId fields " + Util.logOnePerLine(sqlIdStrings));
AppException mismatches = getMismatches(sqlIdStrings, fSqlProperties.keySet());
if (mismatches.isNotEmpty()) {
fLogger.severe("MISMATCH found between .sql files and SqlId fields. " + Util.logOnePerLine(mismatches.getMessages()));
throw new IllegalStateException(Util.logOnePerLine(mismatches.getMessages()));
}
fLogger.config("No mismatches found between .sql files and SqlId fields.");
}
/**
Map <tt>aSqlIdFields</tt> contains KEY - containing Class VALUE - Set of SqlId Fields
<P>
In this case, we are interested only in the "global" set of SqlId fields, unrelated to
any particular class. This method will doubly iterate through its argument, and return
a Set of Strings extracted from the SqlId.toString() method. This is to allow
comparison with the identifiers in the .sql files.
*/
private static Set<String> convertToSetOfStrings(Map<Class<?>, Set<SqlId>> aSqlIdFields) {
Set<String> result = new LinkedHashSet<String>();
Set classes = aSqlIdFields.keySet();
Iterator classesIter = classes.iterator();
while (classesIter.hasNext()) {
Class containingClass = (Class)classesIter.next();
Set<SqlId> fields = aSqlIdFields.get(containingClass);
result.addAll(getSqlIdFieldsAsStrings(fields));
}
return result;
}
private static Set<String> getSqlIdFieldsAsStrings(Set<SqlId> aSqlIds) {
Set<String> result = new LinkedHashSet<String>();
for (SqlId sqlId : aSqlIds) {
result.add(sqlId.toString());
}
return result;
}
private static AppException getMismatches(Set<String> aSqlIdStrings,
Collection<Object> aSqlTextFileKeys) {
AppException result = new AppException();
for (String fieldValue : aSqlIdStrings) {
if (!aSqlTextFileKeys.contains(fieldValue)) {
result.add("SqlId field " + fieldValue + " is not present in any underlying .sql file.");
}
}
for (Object sqlFileKey : aSqlTextFileKeys) {
if (!aSqlIdStrings.contains(sqlFileKey)) {
result.add("The key " + sqlFileKey + " in a .sql file does not match any corresponding public static final SqlId field in any class.");
}
}
return result;
}
private static void checkStoredProcedures() {
AppException errors = new AppException();
Enumeration allSqlIds = fSqlProperties.propertyNames();
while (allSqlIds.hasMoreElements()) {
String sqlId = (String)allSqlIds.nextElement();
String sql = (String)fSqlProperties.get(sqlId);
if (sql.startsWith(fUNSUPPORTED_STORED_PROC)) {
errors.add(
"The stored procedured called " + Util.quote(sqlId) + " has an explict return "
+ "value since it begins with " + fUNSUPPORTED_STORED_PROC + ". "
+ "A *.sql file can contain stored procedures, but only if they do not "
+ "have any OUT or INOUT parameters, including *explicit* return values (which "
+ "would need registration as an OUT parameter). See hirondelle.web4j.database "
+ "package overview for more information."
);
}
}
if (errors.isNotEmpty()) { throw new IllegalStateException(errors.getMessages().toString()); }
}
/**
Attempt a precompile of all statements.
<P>Precompilation is not supported by some drivers/databases.
*/
private static void precompileAll() {
fLogger.config("Attempting precompile of all SQL statements by calling Connection.prepareStatement(String). Precompilation is not supported by all drivers/databases. If not supported, then this checking is not useful. See web.xml.");
ConnectionSource connSrc = BuildImpl.forConnectionSource();
Connection connection = null;
PreparedStatement preparedStatement = null;
String sqlText = null;
SqlId sqlId = null;
String sqlIdString = null;
List<String> successIds = new ArrayList<String>();
List<String> failIds = new ArrayList<String>();
Set<Object> statementIds = fSqlProperties.keySet();
Iterator<Object> iter = statementIds.iterator();
while (iter.hasNext()) {
sqlId = SqlId.fromStringId((String) iter.next());
sqlIdString = sqlId.toString();
sqlText = fSqlProperties.getProperty(sqlIdString);
String dbName = sqlId.getDatabaseName();
if(DbConfig.isSqlPrecompilationAttempted(dbName)){
try {
connection = (Util.textHasContent(dbName)) ? connSrc.getConnection(dbName) : connSrc.getConnection();
preparedStatement = getPS(sqlText, connection, sqlId); // SQLException depends on driver/db
successIds.add(sqlIdString);
}
catch (SQLException ex) {
failIds.add(sqlIdString);
fLogger.severe("SQLException occurs for attempted precompile of " + sqlId + " " + ex.getMessage() + NEW_LINE + sqlText);
}
catch (DAOException ex) {
fLogger.severe("Error encountered during attempts to precompile SQL statements : " + ex);
}
finally {
try {
DbUtil.close(preparedStatement, connection);
}
catch (DAOException ex) {
fLogger.severe("Cannot close connection and/or statement : " + ex);
}
}
}
}
fLogger.config("Attempted SQL precompile, and found no failure for : " + Util.logOnePerLine(successIds));
if (!failIds.isEmpty()) {
fLogger.config("Attempted SQL precompile, and found *** FAILURE *** for : " + Util.logOnePerLine(failIds));
}
}
}