package com.spun.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import com.spun.util.database.DatabaseObject;
import com.spun.util.database.DatabaseTransactionInfo;
import com.spun.util.database.SqlConnectionException;
import com.spun.util.logger.SimpleLogger;
/**
* A static class of convenience functions for database access
*/
public class DatabaseUtils
{
private static ArrayList<DatabaseTransactionInfo> connections = new ArrayList<DatabaseTransactionInfo>();
public static final int NEW = 1;
public static final int NEW_MODIFIED = 2;
public static final int OLD = 3;
public static final int OLD_MODIFIED = 4;
public static final int ACCESS = 0;
public static final int POSTGRESQL = 1;
public static final int SYBASE = 2;
public static final int ORACLE = 3;
public static final int SQLSERVER = 4;
public static final int SQLSERVER2000 = 5;
public static final int MY_SQL = 6;
public static final int SQLSERVER2005 = 7;
public static final String DATABASE_TYPES[] = {"Access",
"PostgreSQL",
"Sybase",
"Oracle",
"Microsoft SQL Server 7.00",
"Microsoft SQL Server 2000",
"MySQL",
"Microsoft SQL Server"};
/***********************************************************************/
public static String getDatabaseType(int type)
{
return DATABASE_TYPES[type];
}
/***********************************************************************/
public static int getDatabaseType(Statement stmt) throws SQLException
{
return getDatabaseType(stmt.getConnection());
}
/***********************************************************************/
public static int getDatabaseType(Connection con) throws SQLException
{
String dbName = con.getMetaData().getDatabaseProductName();
for (int i = 0; i < DATABASE_TYPES.length; i++)
{
if (DATABASE_TYPES[i].equalsIgnoreCase(dbName.trim())) { return i; }
}
throw new Error("Unrecognized database product name: " + dbName);
}
/***********************************************************************/
/**
* Finds the database type name for a jdbc sql type
*/
public static String findDatabaseName(int i)
{
String name = null;
switch (i)
{
case java.sql.Types.BIT :
name = "boolean";
break;
case java.sql.Types.CHAR :
name = "char";
break;
case java.sql.Types.DECIMAL :
name = "decimal";
break;
case java.sql.Types.DOUBLE :
name = "numeric";
break;
case java.sql.Types.FLOAT :
name = "numeric";
break;
case java.sql.Types.INTEGER :
name = "integer";
break;
case java.sql.Types.NUMERIC :
name = "numeric";
break;
case java.sql.Types.TIMESTAMP :
name = "timestamp";
break;
case java.sql.Types.VARCHAR :
name = "varchar";
break;
default :
SimpleLogger.warning("The Type not found(" + i + ")");
break;
}
return name;
}
/***********************************************************************/
/**
* Finds the java object or primative for a sql type
*/
public static String findSQLName(int i)
{
String name = null;
switch (i)
{
case java.sql.Types.ARRAY :
name = "unknown";
break;
case java.sql.Types.BIGINT :
name = "long";
break;
case java.sql.Types.BINARY :
name = "byte[]";
break;
case java.sql.Types.BIT :
name = "boolean";
break;
case java.sql.Types.BLOB :
name = "BLOB";
break;
case java.sql.Types.CHAR :
name = "java.lang.String";
break;
case java.sql.Types.CLOB :
name = "CLOB";
break;
case java.sql.Types.DATE :
name = "java.sql.Date";
break;
case java.sql.Types.DECIMAL :
name = "java.math.BigDecimal";
break;
case java.sql.Types.DISTINCT :
name = "DISTINCT";
break;
case java.sql.Types.DOUBLE :
name = "double";
break;
case java.sql.Types.FLOAT :
name = "double";
break;
case java.sql.Types.INTEGER :
name = "int";
break;
case java.sql.Types.JAVA_OBJECT :
name = "unknown";
break;
case java.sql.Types.LONGVARBINARY :
name = "byte[]";
break;
case java.sql.Types.LONGVARCHAR :
name = "String";
break;
case java.sql.Types.NULL :
name = "NULL";
break;
case java.sql.Types.NUMERIC :
name = "java.math.BigDecimal";
break;
case java.sql.Types.OTHER :
name = "OTHER";
break;
case java.sql.Types.REAL :
name = "float";
break;
case java.sql.Types.REF :
name = "REF";
break;
case java.sql.Types.SMALLINT :
name = "short";
break;
case java.sql.Types.STRUCT :
name = "STRUCT";
break;
case java.sql.Types.TIME :
name = "java.sql.Time";
break;
case java.sql.Types.TIMESTAMP :
name = "java.sql.Timestamp";
break;
case java.sql.Types.TINYINT :
name = "byte";
break;
case java.sql.Types.VARBINARY :
name = "byte[]";
break;
case java.sql.Types.VARCHAR :
name = "java.lang.String";
break;
case 11 :
name = "java.sql.Date";
break;
case -9 :
name = "java.lang.String";
break;
default :
SimpleLogger.warning("The Type not found(" + i + ")");
printSQLValues();
break;
}
return name;
}
public static boolean isSqlServer(int databaseType)
{
switch (databaseType)
{
case SQLSERVER2000 :
case DatabaseUtils.SQLSERVER2005 :
case DatabaseUtils.SQLSERVER :
return true;
default :
return false;
}
}
/***********************************************************************/
public static String makeSQL2000URL(String protocol, String server, String port, String database)
{
String theURL = "";
protocol = (StringUtils.isNonZero(protocol)) ? (protocol) + "://" : "";
server = (StringUtils.isNonZero(server)) ? (server) : "";
port = (StringUtils.isNonZero(port)) ? (":" + port) : "";
database = (StringUtils.isNonZero(database)) ? (";DatabaseName=" + database) : "";
theURL = protocol + server + port + database + ";SelectMethod=cursor";
return theURL;
}
/***********************************************************************/
/**
* Creates a connection to the Database.
*/
public static String makeMySqlURL(String protocol, String server, String port, String database)
{
String theURL = "";
protocol = (StringUtils.isNonZero(protocol)) ? (protocol) + "://" : "";
server = (StringUtils.isNonZero(server)) ? (server) : "";
port = (StringUtils.isNonZero(port)) ? (":" + port) : "";
database = (StringUtils.isNonZero(database)) ? ("/" + database) : "";
theURL = protocol + server + port + database;
return theURL;
}
/***********************************************************************/
/**
* Creates a connection to the Database, and stores it in the cookies table.
*/
public static String makeURL(String protocol, String server, String port, String database, int type)
{
switch (type)
{
case ACCESS :
return makeJDBCAccessURL(protocol, server, port, database);
case SQLSERVER2000 :
case DatabaseUtils.SQLSERVER2005 :
return makeSQL2000URL(protocol, server, port, database);
case ORACLE :
case SQLSERVER :
case POSTGRESQL :
case SYBASE :
return makeMySqlURL(protocol, server, port, database);
case MY_SQL :
return makeMySqlURL(protocol, server, port, database) + "?useUnicode=true&characterEncoding=UTF-8";
}
throw new Error("Database Type '" + type + "' not supported");
}
/***********************************************************************/
/**
* Creates a connection to the Database. This is for Access, which is crap!
*/
public static String makeJDBCAccessURL(String protocol, String server, String port, String database)
{
String theURL = "";
// port = (StringUtils.isNonZero(port)) ? (":" + port) : "";
// server = (StringUtils.isNonZero(server)) ? (server) : "";
database = (StringUtils.isNonZero(database)) ? ("" + database) : "";
theURL = protocol + database;
return theURL;
}
/***********************************************************************/
/**
* Creates a connection to the Database.
*/
public static Connection makeConnection(String driver, String protocol, String server, String port,
String database, String userName, String password, int type)
{
Connection con = null;
String theURL = null;
try
{
driver = (StringUtils.isNonZero(driver)) ? (driver) : "sun.jdbc.odbc.JdbcOdbcDriver";
theURL = makeURL(protocol, server, port, database, type);
SimpleLogger.variable("URL = " + theURL);
// My_System.variable(driver);
Class.forName(driver).newInstance();
con = DriverManager.getConnection(theURL, userName, password);
}
catch (SQLException e)
{
throw new SqlConnectionException(driver, theURL, protocol, server, port, database, userName, password, type,
e);
}
catch (Exception e)
{
SimpleLogger.warning("URL : " + theURL);
ObjectUtils.throwAsError(e);
}
return con;
}
/***********************************************************************/
public static void printSQLValues()
{
SimpleLogger.variable("java.sql.Types.BIT = " + java.sql.Types.BIT);
SimpleLogger.variable("java.sql.Types.TINYINT = " + java.sql.Types.TINYINT);
SimpleLogger.variable("java.sql.Types.BIGINT = " + java.sql.Types.BIGINT);
SimpleLogger.variable("java.sql.Types.LONGVARBINARY = " + java.sql.Types.LONGVARBINARY);
SimpleLogger.variable("java.sql.Types.VARBINARY = " + java.sql.Types.VARBINARY);
SimpleLogger.variable("java.sql.Types.BINARY = " + java.sql.Types.BINARY);
SimpleLogger.variable("java.sql.Types.LONGVARCHAR = " + java.sql.Types.LONGVARCHAR);
SimpleLogger.variable("java.sql.Types.NULL = " + java.sql.Types.NULL);
SimpleLogger.variable("java.sql.Types.CHAR = " + java.sql.Types.CHAR);
SimpleLogger.variable("java.sql.Types.NUMERIC = " + java.sql.Types.NUMERIC);
SimpleLogger.variable("java.sql.Types.DECIMAL = " + java.sql.Types.DECIMAL);
SimpleLogger.variable("java.sql.Types.INTEGER = " + java.sql.Types.INTEGER);
SimpleLogger.variable("java.sql.Types.SMALLINT = " + java.sql.Types.SMALLINT);
SimpleLogger.variable("java.sql.Types.FLOAT = " + java.sql.Types.FLOAT);
SimpleLogger.variable("java.sql.Types.REAL = " + java.sql.Types.REAL);
SimpleLogger.variable("java.sql.Types.DOUBLE = " + java.sql.Types.DOUBLE);
SimpleLogger.variable("java.sql.Types.VARCHAR = " + java.sql.Types.VARCHAR);
SimpleLogger.variable("java.sql.Types.DATE = " + java.sql.Types.DATE);
SimpleLogger.variable("java.sql.Types.TIME = " + java.sql.Types.TIME);
SimpleLogger.variable("java.sql.Types.TIMESTAMP = " + java.sql.Types.TIMESTAMP);
SimpleLogger.variable("java.sql.Types.OTHER = " + java.sql.Types.OTHER);
SimpleLogger.variable("java.sql.Types.JAVA_OBJECT = " + java.sql.Types.JAVA_OBJECT);
SimpleLogger.variable("java.sql.Types.DISTINCT = " + java.sql.Types.DISTINCT);
SimpleLogger.variable("java.sql.Types.STRUCT = " + java.sql.Types.STRUCT);
SimpleLogger.variable("java.sql.Types.ARRAY = " + java.sql.Types.ARRAY);
SimpleLogger.variable("java.sql.Types.BLOB = " + java.sql.Types.BLOB);
SimpleLogger.variable("java.sql.Types.CLOB = " + java.sql.Types.CLOB);
SimpleLogger.variable("java.sql.Types.REF = " + java.sql.Types.REF);
}
/***********************************************************************/
public static String getMethodName(String databaseName)
{
return getVariableName(databaseName, true);
}
/***********************************************************************/
public static String getVariableName(String databaseName)
{
return getVariableName(databaseName, false);
}
/***********************************************************************/
public static String getVariableName(String databaseName, boolean capFirstLetter)
{
StringBuffer returning = new StringBuffer(databaseName.length());
String upper = databaseName.toUpperCase();
int place = 0;
while (place < databaseName.length())
{
char letter = databaseName.charAt(place);
if (letter == '_')
{
returning.append(upper.charAt(++place));
}
else if ((place == 0) && (capFirstLetter))
{
returning.append(upper.charAt(0));
}
else
{
returning.append(letter);
}
place++;
}
return returning.toString();
}
/***********************************************************************/
public static String formatNullableObject(Object o)
{
return formatNullableObject(o, POSTGRESQL);
}
/***********************************************************************/
public static String formatNullableObject(Object o, int type)
{
if (o == null)
{
return "null";
}
else
{
if (o instanceof Integer)
{
return o.toString();
}
else
{
String text = null;
switch (type)
{
case DatabaseUtils.SQLSERVER2005 :
case SQLSERVER2000 :
case SQLSERVER :
text = "'" + toEscapeMSSQL(o.toString()) + "'";
break;
case ACCESS :
text = "'" + toEscapeACCESS_SQL(o.toString()) + "'";
break;
case ORACLE :
case POSTGRESQL :
case SYBASE :
text = "'" + toEscapeSQL(o.toString()) + "'";
break;
}
return text;
}
}
}
/***********************************************************************/
public static String toEscapeMSSQL(String unformattedString)
{
if (unformattedString.indexOf('\'') == -1) { return unformattedString; }
StringBuffer b = new StringBuffer(unformattedString);
for (int i = 0; i < b.length(); i++)
{
char c = b.charAt(i);
switch (c)
{
case '\'' :
b.insert(i, '\'');
i++;
break;
}
}
return b.toString();
}
/***********************************************************************/
public static String toEscapeACCESS_SQL(String unformattedString)
{
if ((unformattedString.indexOf('\'') == -1) && (unformattedString.indexOf('\"') == -1)
&& (unformattedString.indexOf('\\') == -1)) { return unformattedString; }
StringBuffer b = new StringBuffer(unformattedString);
for (int i = 0; i < b.length(); i++)
{
char c = b.charAt(i);
switch (c)
{
case '\"' :
case '\\' :
b.insert(i, '\\');
i++;
break;
case '\'' :
b.insert(i, '\'');
i++;
break;
}
}
return b.toString();
}
/***********************************************************************/
public static String toEscapeSQL(String unformattedString)
{
if ((unformattedString == null)
|| ((unformattedString.indexOf('\'') == -1) && (unformattedString.indexOf('\"') == -1)
&& (unformattedString.indexOf('\\') == -1))) { return unformattedString; }
StringBuffer b = new StringBuffer(unformattedString);
for (int i = 0; i < b.length(); i++)
{
char c = b.charAt(i);
switch (c)
{
case '\"' :
case '\\' :
case '\'' :
b.insert(i, '\\');
i++;
break;
}
}
return b.toString();
}
/***********************************************************************/
public static void beginTransaction(Statement stmt) throws SQLException
{
beginTransaction(stmt.getConnection(), 2);
}
/***********************************************************************/
public static void beginTransaction(Connection con) throws SQLException
{
beginTransaction(con, 2);
}
/***********************************************************************/
private static void beginTransaction(Connection con, int offset) throws SQLException
{
if (getConnection(connections, con) == null)
{
synchronized (connections)
{
//My_System.event("Starting transaction " + DatabaseTransactionInfo.getOriginatorText(offset + 1));
connections.add(new DatabaseTransactionInfo(con, 1 + offset));
}
con.setAutoCommit(false);
}
else
{
//My_System.event("already exist");
}
}
/***********************************************************************/
private static DatabaseTransactionInfo getConnection(ArrayList<DatabaseTransactionInfo> connections2,
Connection con)
{
for (DatabaseTransactionInfo info : connections2)
{
if (con.equals(info.getConnection())) { return info; }
}
return null;
}
/***********************************************************************/
public static void commit(Statement stmt) throws SQLException
{
commit(stmt.getConnection(), 2);
}
/***********************************************************************/
public static void commit(Connection con) throws SQLException
{
commit(con, 2);
}
/***********************************************************************/
private static void commit(Connection con, int offset) throws SQLException
{
DatabaseTransactionInfo commit = null;
synchronized (connections)
{
Iterator<DatabaseTransactionInfo> i = connections.iterator();
while (i.hasNext())
{
DatabaseTransactionInfo held = (DatabaseTransactionInfo) i.next();
if (held.isFinalizeable())
{
i.remove();
}
else if (held.isOriginator(con, 1 + offset))
{
commit = held;
i.remove();
}
}
}
if (commit != null)
{
con.commit();
commit.cleanConnection();
}
}
/***********************************************************************/
public static void rollback(Statement stmt) throws SQLException
{
rollback(stmt.getConnection());
}
/***********************************************************************/
public static void rollback(Connection con) throws SQLException
{
if (con != null)
{
con.rollback();
}
}
/***********************************************************************/
public static String formatBoolean(boolean b)
{
return (b) ? "'1'" : "'0'"; // added quotes for postgres
}
/***********************************************************************/
/**
* This is for none space single words, such as email, name, handle
*/
public static boolean isAcceptableDatabaseString(String string)
{
if (string == null) { return true; }
for (int i = 0; i < string.length(); i++)
{
char c = string.charAt(i);
boolean bad = false;
bad = (c == ' ') || (c == '\n') || (c == '\t') || (c == '\'') || (c == '"');
if (bad) { return false; }
}
return true;
}
/***********************************************************************/
public static String getLike(int databaseType)
{
return databaseType == POSTGRESQL ? "ILIKE" : "LIKE";
}
/***********************************************************************/
/**
* A convenience function to turn the int's into readable text for debuging.
*
* @param status
* The status to be translated
* @return The Text representation of static variable.
* @see com.spun.utilDatabaseUtils#NEW
* @see com.spun.utilDatabaseUtils#NEW_MODIFIED
* @see com.spun.utilDatabaseUtils#OLD
* @see com.spun.utilDatabaseUtils#OLD_MODIFIED
*/
public static final String getDatabaseStatusString(int status)
{
String value = "UNKNOWN DATABASE STATUS";
switch (status)
{
case DatabaseUtils.NEW :
value = "DatabaseUtils.NEW";
break;
case DatabaseUtils.OLD :
value = "DatabaseUtils.OLD";
break;
case DatabaseUtils.NEW_MODIFIED :
value = "DatabaseUtils.NEWMODIFIED";
break;
case DatabaseUtils.OLD_MODIFIED :
value = "DatabaseUtils.OLDMODIFIED";
break;
}
return value;
}
/***********************************************************************/
public static void saveAll(DatabaseObject[] databaseObjects, Statement stmt) throws SQLException
{
for (int i = 0; i < databaseObjects.length; i++)
{
databaseObjects[i].save(stmt);
}
}
/***********************************************************************/
public static void close(ResultSet rs)
{
if (rs != null)
{
try
{
rs.close();
}
catch (SQLException e)
{
throw ObjectUtils.throwAsError(e);
}
}
}
/***********************************************************************/
public static void close(Statement stmt)
{
if (stmt != null)
{
try
{
stmt.close();
}
catch (SQLException e)
{
throw ObjectUtils.throwAsError(e);
}
}
}
/***********************************************************************/
public static void close(Connection con)
{
if (con != null)
{
try
{
con.close();
}
catch (SQLException e)
{
throw ObjectUtils.throwAsError(e);
}
}
}
}