/*
* Copyright 2008 FatWire Corporation. All Rights Reserved.
*
* Licensed 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 tools.gsf.facade.sql;
import COM.FutureTense.Interfaces.ICS;
import COM.FutureTense.Interfaces.IList;
import com.fatwire.cs.core.db.PreparedStmt;
import com.fatwire.cs.core.db.StatementParam;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.math.BigDecimal;
import java.sql.Array;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Date;
import java.sql.Ref;
import java.sql.Struct;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.Collections;
import java.util.Iterator;
/**
* A helper class over <tt>ICS.SQL</tt>
*
* @author Dolf Dijkstra
* @see ICS#SQL(String, String, String, int, boolean, boolean, StringBuffer)
*/
public class SqlHelper {
private static final Logger LOG = LoggerFactory.getLogger("tools.gsf.facade.sql.SqlHelper");
private SqlHelper() {
}
/**
* facade over ICS.SQL
* <p>
* limit =-1;
* <p>
* bCache=true;
* <p>
* clears errno before ics.SQL
* <p>
* no IList registered in ics variable space
*
* @param ics Content Server context object
* @param table tablename
* @param sql the sql statement, needs to start with 'select'
* @return never null, always an IListIterable
* @throws RuntimeException if errno is not zero or not -101
* @see SqlHelper#select(ICS, String, String, int)
*/
public static final IListIterable select(final ICS ics, final String table, final String sql) {
return select(ics, table, sql, -1);
}
/**
* Executes an ICS.SQL operation with a limit.
*
* @param ics Content Server context object
* @param table tablename
* @param sql the sql statement, needs to start with 'select'
* @param limit maximum number of rows to return
* @return never null, always an IListIterable
* @see ICS#SQL(String, String, String, int, boolean, StringBuffer)
*/
public static final IListIterable select(final ICS ics, final String table, final String sql, final int limit) {
final StringBuffer errstr = new StringBuffer();
ics.ClearErrno();
if (sql == null) {
throw new NullPointerException("sql can not be null");
}
if (!sql.toLowerCase().trim().startsWith("select")) {
throw new IllegalArgumentException("Can only do select statements:" + sql);
}
final IList i = ics.SQL(table, sql, null, limit, true, errstr);
if (ics.GetErrno() == -101) {
ics.ClearErrno();
} else if (ics.GetErrno() != 0) {
throw new RuntimeException("ics.SQL returned " + ics.GetErrno() + " and errstr: '" + errstr.toString()
+ "' for " + sql);
}
return new IListIterable(i);
}
/**
* Executes sql statements, other then SELECT statements.
* <p>
* flushes the table (ics.FlushCatalog()) after the statement execution
*
* @param ics Content Server context object
* @param table tablename
* @param sql the sql statement, can not start with "select"
*/
public static final void execute(final ICS ics, final String table, final String sql) {
final StringBuffer errstr = new StringBuffer();
ics.ClearErrno();
if (sql == null) {
throw new NullPointerException("sql can not be null");
}
if (sql.toLowerCase().trim().startsWith("select")) {
throw new IllegalArgumentException("Can not do select statements:" + sql);
}
ics.SQL(table, sql, null, -1, false, true, errstr);
if (ics.GetErrno() == 0) {
if (ics.FlushCatalog(table)) {
ics.ClearErrno();
} else {
LOG.warn("Flushing failed for table " + table + ". (" + ics.GetErrno() + ")");
ics.ClearErrno();
}
} else if (ics.GetErrno() == -502) { // update statements do not
// return an IList, cs signals
// this via errno -502
if (ics.FlushCatalog(table)) {
ics.ClearErrno();
} else {
// throw exception??
LOG.warn("Flushing failed for table " + table + ". (" + ics.GetErrno() + ")");
ics.ClearErrno();
}
} else {
LOG.warn("ics.SQL returned " + ics.GetErrno() + " and errstr: " + errstr.toString() + " for " + sql);
}
}
/**
* Executes a PreparedStatement
*
* @param ics Content Server context object
* @param stmt the PreparedStatement
* @param param the statement parameters
* @return never null, always an IListIterable
*/
public static final IListIterable select(final ICS ics, final PreparedStmt stmt, final StatementParam param) {
final IList i = ics.SQL(stmt, param, true);
if (ics.GetErrno() != -101) { // no rows if fine
ics.ClearErrno();
} else if (ics.GetErrno() != 0) {
throw new RuntimeException("ics.SQL returned " + ics.GetErrno() + " and errstr: " + " for "
+ stmt.toString());
}
return new IListIterable(i);
}
/**
* Executes a PreparedStatement in a simple form. The values are simply
* mapped based on order and type to prepared statement parameters.
*
* @param ics Content Server context object
* @param table tablename
* @param sql the sql statement
* @param value the values for the prepared statement parameters.
* @return never null, always an IListIterable
*/
public static final IListIterable selectSimplePrepared(final ICS ics, String table, String sql, Object... value) {
final PreparedStmt stmt = new PreparedStmt(sql, Collections.singletonList(table));
for (int i = 0; value != null && i < value.length; i++) {
stmt.setElement(i, toJdbcType(value[i]));
}
final StatementParam param = stmt.newParam();
for (int i = 0; value != null && i < value.length; i++) {
Object o = value[i];
if (o instanceof String) {
param.setString(i, (String) o);
} else if (o instanceof BigDecimal) {
param.setBigDecimal(i, (BigDecimal) o);
} else if (o instanceof Boolean) {
param.setBoolean(i, (Boolean) o);
} else if (o instanceof Integer) {
param.setInt(i, (Integer) o);
} else if (o instanceof Long) {
param.setLong(i, (Long) o);
} else if (o instanceof Float) {
param.setFloat(i, (Float) o);
} else if (o instanceof Double) {
param.setDouble(i, (Double) o);
} else if (o instanceof Byte) {
param.setByte(i, (Byte) o);
} else if (o instanceof java.sql.Date) {
param.setDate(i, (Date) o);
} else if (o instanceof java.sql.Time) {
param.setTime(i, (Time) o);
} else if (o instanceof java.sql.Timestamp) {
param.setTimeStamp(i, (Timestamp) o);
} else if (o instanceof Clob) {
throw new IllegalArgumentException("Can't search for " + o.getClass().getName());
} else if (o instanceof Blob) {
throw new IllegalArgumentException("Can't search for " + o.getClass().getName());
} else if (o.getClass().isArray()) {
throw new IllegalArgumentException("Can't search for " + o.getClass().getName());
} else if (o instanceof Array) {
throw new IllegalArgumentException("Can't search for " + o.getClass().getName());
} else if (o instanceof Struct) {
throw new IllegalArgumentException("Can't search for " + o.getClass().getName());
} else if (o instanceof Ref) {
throw new IllegalArgumentException("Can't search for " + o.getClass().getName());
} else if (o instanceof java.net.URL) {
throw new IllegalArgumentException("Can't search for " + o.getClass().getName());
}
}
return select(ics, stmt, param);
}
private static int toJdbcType(Object o) {
if (o instanceof String) {
return Types.VARCHAR;
} else if (o instanceof java.math.BigDecimal) {
return Types.NUMERIC;
} else if (o instanceof Boolean) {
return Types.BOOLEAN;
} else if (o instanceof Integer) {
return Types.INTEGER;
} else if (o instanceof Long) {
return Types.BIGINT;
} else if (o instanceof Float) {
return Types.REAL;
} else if (o instanceof Double) {
return Types.DOUBLE;
} else if (o instanceof byte[]) {
return Types.LONGVARBINARY;
} else if (o instanceof java.sql.Date) {
return Types.DATE;
} else if (o instanceof java.sql.Time) {
return Types.TIME;
} else if (o instanceof java.sql.Timestamp) {
return Types.TIMESTAMP;
} else if (o instanceof Clob) {
return Types.CLOB;
} else if (o instanceof Blob) {
return Types.BLOB;
} else if (o instanceof Array) {
return Types.ARRAY;
} else if (o instanceof Struct) {
return Types.STRUCT;
} else if (o instanceof Ref) {
return Types.REF;
} else if (o instanceof java.net.URL) {
return Types.DATALINK;
} else {
throw new IllegalArgumentException(o == null ? "o must not be nulll" : "Can't handle type "
+ o.getClass().getName());
}
}
/**
* Executes a PreparedStatement, returning a single row
*
* @param ics the Content Server context
* @param stmt the PreparedStatement
* @param param the statement parameters
* @return Row if resultset is returned, otherwise null
*/
public static final Row selectSingle(final ICS ics, final PreparedStmt stmt, final StatementParam param) {
ics.ClearErrno();
final IList i = ics.SQL(stmt, param, true);
if (ics.GetErrno() == 0) {
return new IListIterable(i).iterator().next();
} else if (ics.GetErrno() == -101) { // no rows is fine
ics.ClearErrno();
return null;
} else {
throw new RuntimeException("ics.SQL returned " + ics.GetErrno() + " for " + stmt.toString());
}
}
/**
* Executes an ICS.SQL operation, returning a single Row, or null if no
* result was returned by ICS.SQL.
*
* @param ics Content Server context object
* @param table tablename
* @param sql the sql statement, needs to start with 'select'
* @return Row if resultset is returned, otherwise null
*/
public static final Row selectSingle(final ICS ics, final String table, String sql) {
Iterator<Row> i = select(ics, table, sql, 1).iterator();
return i.hasNext() ? i.next() : null;
}
/**
* Quote a string for use in a SQL statement.
*
* @param s string to quote
* @return quoted string. Null strings are returned simply as ''.
*/
public static final String quote(final String s) {
if (s == null || s.length() == 0) {
return "''";
}
return "'" + s.replace("'", "''") + "'";
}
public static boolean tableExists(final ICS ics, final String table) {
ics.CatalogDef(table, null, new StringBuffer());
return ics.GetErrno() == 0;
}
}