/*
* NOTE: This copyright does *not* cover user programs that use Hyperic
* program services by normal system calls through the application
* program interfaces provided as part of the Hyperic Plug-in Development
* Kit or the Hyperic Client Development Kit - this is merely considered
* normal use of the program, and does *not* fall under the heading of
* "derived work".
*
* Copyright (C) [2004-2010], VMware, Inc.
* This file is part of Hyperic.
*
* Hyperic is free software; you can redistribute it and/or modify
* it under the terms version 2 of the GNU General Public License as
* published by the Free Software Foundation. This program 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 General Public License for more
* details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
* USA.
*/
package org.hyperic.util.jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hyperic.util.pager.PageControl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
@Component
public class DBUtil {
protected static final Log log = LogFactory.getLog(DBUtil.class);
// Constants for DB Errors that we want to catch in other classes
public static final int ORACLE_ERROR_DIVIDE_BY_ZERO = 1476;
public static final int ORACLE_ERROR_NOT_AVAILABLE = 1034;
// Constants for PostgreSQL errors
// May be found at:
// http://www.postgresql.org/docs/8.0//errcodes-appendix.html
public static final int POSTGRES_ERROR_DIVIDE_BY_ZERO = 22012;
public static final int POSTGRES_CONNECTION_EXCEPTION = 8000;
public static final int POSTGRES_CONNECTION_FAILURE = 8006;
public static final int POSTGRES_UNABLE_TO_CONNECT = 8001;
// Constants for MySQL errors
// May be found at:
// http://dev.mysql.com/doc/refman/5.0/en/error-messages-client.html
public static final int MYSQL_LOCAL_CONN_ERROR = 2002;
public static final int MYSQL_REMOTE_CONN_ERROR = 2003;
// Constants for supported databases.
public static final int DATABASE_UNKNOWN = 0;
public static final int DATABASE_POSTGRESQL_7 = 1;
public static final int DATABASE_POSTGRESQL_8 = 2;
public static final int DATABASE_ORACLE_8 = 3;
public static final int DATABASE_ORACLE_9 = 4;
public static final int DATABASE_ORACLE_10 = 8;
public static final int DATABASE_MYSQL5 = 9;
public static final int DATABASE_ORACLE_11 = 10;
public static final int DATABASE_POSTGRESQL_9 = 11;
// Built-in DB constants
private static String BUILTIN_DB_JDBC_URL = "jdbc:postgresql://127.0.0.1:9432";
private static String BUILTIN_DB_NAME = "hqdb";
public static final int IN_CHUNK_SIZE = 200;
private static Map _dbTypes = new HashMap();
private DataSource dataSource;
@Autowired
public DBUtil(DataSource dataSource) {
this.dataSource = dataSource;
}
/**
* Constructor is private because this class should never be instantiated.
*/
// private DBUtil() {}
/**
* Close a database connection. No exception is thrown if it fails, but a
* warning is logged.
*
* @param ctx The logging context to use if a warning should be issued.
* @param c The connection to close.
*/
public static void closeConnection(Object ctx, Connection c) {
if (c == null)
return;
try {
c.close();
} catch (Exception e) {
log.warn(ctx.toString() + ": Error closing connection.", e);
}
}
/**
* Close a database statement. No exception is thrown if it fails, but a
* warning is logged.
*
* @param ctx The logging context to use if a warning should be issued.
* @param s The statement to close.
*/
public static void closeStatement(Object ctx, Statement s) {
if (s == null)
return;
try {
s.close();
} catch (Exception e) {
log.warn(ctx.toString() + ": Error closing statement.", e);
}
}
/**
* Close a database result set. No exception is thrown if it fails, but a
* warning is logged.
*
* @param ctx The logging context to use if a warning should be issued.
* @param rs The result set to close.
*/
public static void closeResultSet(Object ctx, ResultSet rs) {
if (rs == null)
return;
try {
rs.close();
} catch (Exception e) {
log.warn(ctx.toString() + ": Error closing result set.", e);
}
}
/**
* Close a connection, statement, and result set in one fell swoop. You can
* pass null for any argument and all will be OK :) No exception is thrown
* if any close fails, but warnings will be logged.
*
* @param ctx The logging context to use if warnings should be issued.
* @param c The connection to close.
* @param s The statement set to close.
* @param rs The result set to close.
*/
public static void closeJDBCObjects(Object ctx, Connection c, Statement s, ResultSet rs) {
closeResultSet(ctx, rs);
closeStatement(ctx, s);
closeConnection(ctx, c);
}
/**
* Get the next value of a sequence
*/
public static int getNextSequenceValue(String ctx, Connection conn, String table, String key) throws SQLException {
String query = null;
// What database is this connection hitting?
int dbType = getDBType(conn);
switch (dbType) {
case DATABASE_POSTGRESQL_7:
case DATABASE_POSTGRESQL_8:
case DATABASE_POSTGRESQL_9:
query = "SELECT nextval('" + table + "_" + key + "_seq'::text)";
break;
case DATABASE_ORACLE_8:
case DATABASE_ORACLE_9:
case DATABASE_ORACLE_10:
case DATABASE_ORACLE_11:
query = "SELECT " + table + "_" + key + "_seq.nextval " + "FROM DUAL";
break;
case DATABASE_MYSQL5:
query = "SELECT MAX(" + key + ") + 1 FROM " + table;
break;
default:
throw new SequencesNotSupportedException();
}
PreparedStatement selectPS = null;
ResultSet rs = null;
try {
selectPS = conn.prepareStatement(query);
rs = selectPS.executeQuery();
if (rs.next()) {
return rs.getInt(1);
} else {
throw new SequenceRetrievalException();
}
} finally {
closeResultSet(ctx, rs);
closeStatement(ctx, selectPS);
}
}
/**
* Given a Connection object, this method returns a constant indicating what
* type of database the Connection is connected to.
*
* @param conn The connection whose database type the caller wished to
* ascertain.
* @return One of the DATABASE_XXX constants defined in this class.
*/
public static int getDBType(Connection conn) throws SQLException {
Class connClass = conn.getClass();
Integer dbTypeInteger = (Integer) _dbTypes.get(connClass);
int dbType = DATABASE_UNKNOWN;
if (dbTypeInteger == null) {
DatabaseMetaData dbMetaData = conn.getMetaData();
String dbName = dbMetaData.getDatabaseProductName().toLowerCase();
String dbVersion = dbMetaData.getDatabaseProductVersion().toLowerCase();
log.debug("getDBType: dbName='" + dbName + "', version='" + dbVersion + "'");
if (dbName.indexOf("postgresql") != -1) {
if (dbVersion.startsWith("7.")) {
dbType = DATABASE_POSTGRESQL_7;
} else if (dbVersion.startsWith("8.")) {
dbType = DATABASE_POSTGRESQL_8;
} else if (dbVersion.startsWith("9.")) {
dbType = DATABASE_POSTGRESQL_9;
}
} else if (dbName.indexOf("oracle") != -1) {
if (dbVersion.startsWith("oracle8")) {
dbType = DATABASE_ORACLE_8;
} else if (dbVersion.startsWith("oracle9")) {
dbType = DATABASE_ORACLE_9;
} else if (dbVersion.startsWith("oracle database 10g")) {
dbType = DATABASE_ORACLE_10;
} else if (dbVersion.startsWith("oracle database 11g")) {
dbType = DATABASE_ORACLE_11;
}
} else if (dbName.indexOf("mysql") != -1) {
dbType = DATABASE_MYSQL5;
}
_dbTypes.put(connClass, new Integer(dbType));
} else {
dbType = dbTypeInteger.intValue();
}
return dbType;
}
/**
* Is the database PostgreSQL?
*/
public static boolean isPostgreSQL(Connection c) throws SQLException {
int type = getDBType(c);
return isPostgreSQL(type);
}
public static boolean isPostgreSQL(int type) {
return (type == DATABASE_POSTGRESQL_7 ||
type == DATABASE_POSTGRESQL_8 ||
type == DATABASE_POSTGRESQL_9);
}
/**
* Is the database Oracle?
*/
public static boolean isOracle(Connection c) throws SQLException {
int type = getDBType(c);
return isOracle(type);
}
public static boolean isOracle(int type) {
return (type == DATABASE_ORACLE_8 || type == DATABASE_ORACLE_9 || type == DATABASE_ORACLE_10 || type == DATABASE_ORACLE_11);
}
/**
* Is the database MySQL?
*/
public static boolean isMySQL(Connection c) throws SQLException {
int type = getDBType(c);
return isMySQL(type);
}
public static boolean isMySQL(int type) {
return type == DATABASE_MYSQL5;
}
public boolean isBuiltinDB() {
boolean isBuiltin = false;
try {
Connection conn = getConnection();
DatabaseMetaData dbMetaData = conn.getMetaData();
String url = dbMetaData.getURL();
closeConnection(log, conn);
if (url != null) {
// built-in db url in the format of:
// jdbc:postgresql://127.0.0.1:9432/hqdb?protocolVersion=2
url = url.toLowerCase();
isBuiltin = url.startsWith(BUILTIN_DB_JDBC_URL) &&
url.indexOf(BUILTIN_DB_NAME) > BUILTIN_DB_JDBC_URL.length();
}
} catch (SQLException e) {
log.warn("Error retrieving database meta data.", e);
} finally {
if (log.isDebugEnabled()) {
log.debug("isBuiltinDB=" + isBuiltin);
}
}
return isBuiltin;
}
/**
* get a connection for a datasource
*/
public Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
/**
* Get the type for a boolean as a string for the required database. This
* should use the same XML file as DBSetup uses, but this will work for now.
*
* @param conn - a connection
* @return booleanStr - the appropriate boolean type for the db you're using
*/
public static String getBooleanType(Connection conn) throws SQLException {
int type = getDBType(conn);
switch (type) {
case DATABASE_ORACLE_8:
case DATABASE_ORACLE_9:
case DATABASE_ORACLE_10:
case DATABASE_ORACLE_11:
return "NUMBER(1)";
case DATABASE_MYSQL5:
return "BIT";
default:
return "BOOLEAN";
}
}
/**
* Get the value for a boolean as a string for the required database
*
* @param bool - the boolean you want
* @param conn - a connection
* @return booleanStr - the appropriate boolean string for the db you're
* using
*/
public static String getBooleanValue(boolean bool, Connection conn) throws SQLException {
int type = getDBType(conn);
switch (type) {
case DATABASE_ORACLE_8:
case DATABASE_ORACLE_9:
case DATABASE_ORACLE_10:
case DATABASE_ORACLE_11:
case DATABASE_MYSQL5:
return bool ? "1" : "0";
default:
return bool ? "'1'" : "'0'";
}
}
/**
* Fill out a PreparedStatement correctly with a boolean.
*
* @param bool - the boolean you want
* @param conn - a connection
* @return booleanStr - the appropriate boolean string for the db you're
* using
*/
public static void setBooleanValue(boolean bool, Connection conn, PreparedStatement ps, int idx)
throws SQLException {
int type = getDBType(conn);
switch (type) {
case DATABASE_ORACLE_8:
case DATABASE_ORACLE_9:
case DATABASE_ORACLE_10:
case DATABASE_ORACLE_11:
case DATABASE_MYSQL5:
ps.setInt(idx, (bool) ? 1 : 0);
return;
default:
ps.setBoolean(idx, bool);
return;
}
}
/**
* Seek through the specified ResultSet to the beginning of the page
* specified by the PageControl
*
* @param rs The result set to seek through.
* @param pc The page control that indicates how far to seek
* @return The number of records actually skipped over in the seek.
*/
public static int seek(ResultSet rs, PageControl pc) throws SQLException {
int stop = pc.getPagenum() * pc.getPagesize();
int i = 0;
while (i < stop && rs.next())
i++;
return i;
}
/**
* Check to see if a column exists in a table.
*
* @param ctx The logging context to use.
* @param c The DB connection to use.
* @param table The table to check.
* @param column The column to look for. This is done in a case-insensitive
* manner.
* @return true if the column exists in the table, false otherwise
* @throws SQLException If any kind of DB error occurs.
*/
public static boolean checkColumnExists(String ctx, Connection c, String table, String column) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
ResultSetMetaData rsmd;
String checkColumnSql = "SELECT * FROM " + table + " WHERE 1=0";
try {
ps = c.prepareStatement(checkColumnSql);
rs = ps.executeQuery();
rsmd = rs.getMetaData();
int numCols = rsmd.getColumnCount();
for (int i = 0; i < numCols; i++) {
if (rsmd.getColumnName(i + 1).equalsIgnoreCase(column)) {
return true;
}
}
return false;
} finally {
closeJDBCObjects(ctx, null, ps, rs);
}
}
/**
* Creates a string that consists of the clause repeated a number
* (iterations) of times, joined by the conjunction string
*
* @param iterations the number of times to repeat the clause
* @param conjunction the string used to join the clause
* @param clause the clause to repeat
* @return the resulting String
*/
private static String composeConjunctions(int iterations, String conjunction, String clause) {
StringBuffer strBuf = new StringBuffer();
for (int i = 0; i < iterations; i++) {
if (i > 0)
strBuf.append(conjunction);
strBuf.append(clause);
}
return strBuf.toString();
}
/**
* Creates the SQL query that is used in PreparedStatement for 0 or more
* values for a given column. For example:
* <p/>
* SELECT id FROM TABLE WHERE id IN (?, ?, ?) SELECT id FROM TABLE WHERE id
* = ?
*
* @param column the name of the column to query against
* @param iterations the number of variables
* @return the WHERE clause (without the WHERE keyword)
*/
public static String composeConjunctions(String column, int iterations) {
if (iterations > 1) {
StringBuffer strBuf = new StringBuffer(column).append(" IN (").append(
composeConjunctions(iterations, ",", "?")).append(") ");
return strBuf.toString();
} else if (iterations == 1) {
return " " + column + "=? ";
} else {
// No conditions at all
return " 1=1 ";
}
}
public static void replacePlaceHolder(StringBuffer buf, String repl) {
int index = buf.indexOf("?");
if (index >= 0)
buf.replace(index, index + 1, repl);
}
public static void replacePlaceHolders(StringBuffer buf, Object[] objs) {
for (int i = 0; i < objs.length; i++)
replacePlaceHolder(buf, objs[i].toString());
}
}