JDBCUtils.java

/*
 * JDBCUtils
 *
 * $Id$
 * $HeadURL$
 */
package gov.usgs.util;

import java.lang.reflect.InvocationTargetException;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * JDBC Connection and Statement utility functions.
 *
 * @author jmfee
 *
 */
public class JDBCUtils {

	/** Mysql Driver. */
	public static final String MYSQL_DRIVER_CLASSNAME = "com.mysql.jdbc.Driver";

	/** SQLite Driver. */
	public static final String SQLITE_DRIVER_CLASSNAME = "org.sqlite.JDBC";

	/**
	 * Create a new JDBC Connection.
	 *
	 * @param driver
	 *            driver class name.
	 * @param url
	 *            driver specific url.
	 * @return Connection to database.
	 * @throws ClassNotFoundException
	 *             if driver class is not found.
	 * @throws IllegalAccessException
	 *             if driver empty constructor is not public.
	 * @throws InstantiationException
	 *             if an exception occurs while instantiating driver.
	 * @throws InvocationTargetException
	 *             if an exception occurs with invoked method
	 * @throws NoSuchMethodException
	 *             if method cannot be found
	 * @throws SQLException
	 *             if an error occurs while making connection.
	 */
	public static Connection getConnection(final String driver, final String url)
			throws ClassNotFoundException, IllegalAccessException,
			InstantiationException, InvocationTargetException,
			NoSuchMethodException, SQLException {
		// create driver class, which registers with DriverManager
		Class.forName(driver).getConstructor().newInstance();

		// request connection from DriverManager
		return DriverManager.getConnection(url);
	}

	/**
	 * Set a JDBC prepared statement parameter.
	 *
	 * Either calls statement.setNull if object is null, or sets the appropriate
	 * type based on the object. If the object is not null, type is ignored.
	 *
	 * @param statement
	 *            statement with parameters to set.
	 * @param index
	 *            index of parameter being set.
	 * @param object
	 *            value of parameter being set.
	 * @param type
	 *            java.sql.Types constant for column type.
	 * @throws SQLException
	 *             if an error occurs while making connection.
	 */
	public static void setParameter(final PreparedStatement statement,
			final int index, final Object object, final int type)
			throws SQLException {

		if (object == null) {
			statement.setNull(index, type);
		} else if (object instanceof Boolean) {
			statement.setBoolean(index, (Boolean) object);
		} else if (object instanceof Byte) {
			statement.setByte(index, (Byte) object);
		} else if (object instanceof Character) {
			statement.setString(index, ((Character) object).toString());
		} else if (object instanceof Double) {
			statement.setDouble(index, (Double) object);
		} else if (object instanceof Float) {
			statement.setFloat(index, (Float) object);
		} else if (object instanceof Integer) {
			statement.setInt(index, (Integer) object);
		} else if (object instanceof Long) {
			statement.setLong(index, (Long) object);
		} else if (object instanceof Short) {
			statement.setShort(index, (Short) object);
		} else if (object instanceof String) {
			statement.setString(index, (String) object);
		} else {
			statement.setObject(index, object, type);
			System.err.printf(
					"Unsupported object type (%s): index=%d, value=%s\n",
					object.getClass().getName(), index, object.toString());
		}
	}

	/**
	 * Get a mysql connection from a URL.
	 *
	 * Calls getConnection(MYSQL_DRIVER_CLASSNAME, url).
	 *
	 * @param url
	 *            a Mysql URL.
	 * @return a Connection to a Mysql database.
	 * @throws SQLException
	 *             if an error occurs while making connection.
	 * @throws ClassNotFoundException
	 *             if driver class is not found.
	 * @throws IllegalAccessException
	 *             if driver empty constructor is not public.
	 * @throws InstantiationException
	 *             if an exception occurs while instantiating driver.
	 * @throws InvocationTargetException
	 *             if an exception occurs with invoked method
	 * @throws NoSuchMethodException
	 *             if method cannot be found
	 */
	public static Connection getMysqlConnection(final String url)
			throws SQLException, ClassNotFoundException,
			IllegalAccessException, InstantiationException,
			InvocationTargetException, NoSuchMethodException {
		return getConnection(MYSQL_DRIVER_CLASSNAME, url);
	}

	/**
	 * Get a sqlite connection from a file.
	 *
	 * Builds a sqlite file url and calls getSqliteConnection(url).
	 *
	 * @param file
	 *            sqlite database file.
	 * @return connection to sqlite database file.
	 * @throws SQLException
	 *             if an error occurs while making connection.
	 * @throws ClassNotFoundException
	 *             if driver class is not found.
	 * @throws IllegalAccessException
	 *             if driver empty constructor is not public.
	 * @throws InstantiationException
	 *             if an exception occurs while instantiating driver.
	 * @throws InvocationTargetException
	 *             if an exception occurs with invoked method
	 * @throws NoSuchMethodException
	 *             if method cannot be found
	 */
	public static Connection getSqliteConnection(final File file)
			throws SQLException, ClassNotFoundException,
			IllegalAccessException, InstantiationException,
			InvocationTargetException, NoSuchMethodException {
		String sqliteFileURL = "jdbc:sqlite:" + file.getAbsolutePath();
		return getSqliteConnection(sqliteFileURL);
	}

	/**
	 * Get a sqlite connection from a URL.
	 *
	 * Calls getConnection(SQLITE_DRIVER_CLASSNAME, url).
	 *
	 * @param url
	 *            sqlite database URL.
	 * @return a Connection to a sqlite database.
	 * @throws SQLException
	 *             if an error occurs while making connection.
	 * @throws ClassNotFoundException
	 *             if driver class is not found.
	 * @throws IllegalAccessException
	 *             if driver empty constructor is not public.
	 * @throws InstantiationException
	 *             if an exception occurs while instantiating driver.
	 * @throws InvocationTargetException
	 *             if an exception occurs with invoked method
	 * @throws NoSuchMethodException
	 *             if method cannot be found
	 */
	public static Connection getSqliteConnection(final String url)
			throws SQLException, ClassNotFoundException,
			IllegalAccessException, InstantiationException,
			InvocationTargetException, NoSuchMethodException {
		return getConnection(SQLITE_DRIVER_CLASSNAME, url);
	}

}