/**
*
* Licensed to the Apache Software Foundation (ASF) under one
* or more contributor license agreements. See the NOTICE file
* distributed with this work for additional information
* regarding copyright ownership. The ASF licenses this file
* to you 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 org.apache.airavata.common.utils;
import org.apache.airavata.common.exception.ApplicationSettingsException;
import org.apache.commons.dbcp.BasicDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.sql.*;
import java.util.Properties;
/**
* Database lookup. Abstracts out JDBC operations.
*/
public class DBUtil {
private String jdbcUrl;
private String databaseUserName;
private String databasePassword;
private String driverName;
protected static Logger log = LoggerFactory.getLogger(DBUtil.class);
private Properties properties;
public DBUtil(String jdbcUrl, String userName, String password, String driver) throws InstantiationException,
IllegalAccessException, ClassNotFoundException {
this.jdbcUrl = jdbcUrl;
this.databaseUserName = userName;
this.databasePassword = password;
this.driverName = driver;
init();
}
/**
* Initializes and load driver. Must be called this before calling anyother method.
*
* @throws ClassNotFoundException
* If DB driver is not found.
* @throws InstantiationException
* If unable to create driver class.
* @throws IllegalAccessException
* If security does not allow users to instantiate driver object.
*/
private void init() throws ClassNotFoundException, InstantiationException, IllegalAccessException {
properties = new Properties();
properties.put("user", databaseUserName);
properties.put("password", databasePassword);
properties.put("characterEncoding", "ISO-8859-1");
properties.put("useUnicode", "true");
loadDriver();
}
/**
* Generic method to query values in the database.
*
* @param tableName
* Table name to query
* @param selectColumn
* The column selecting
* @param whereValue
* The condition query
* @return The value appropriate to the query.
* @throws SQLException
* If an error occurred while querying
*/
public String getMatchingColumnValue(String tableName, String selectColumn, String whereValue) throws SQLException {
return getMatchingColumnValue(tableName, selectColumn, selectColumn, whereValue);
}
/**
* Generic method to query values in the database.
*
* @param tableName
* Table name to query
* @param selectColumn
* The column selecting
* @param whereColumn
* The column which condition should apply
* @param whereValue
* The condition query
* @return The value appropriate to the query.
* @throws SQLException
* If an error occurred while querying
*/
public String getMatchingColumnValue(String tableName, String selectColumn, String whereColumn, String whereValue)
throws SQLException {
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("SELECT ").append(selectColumn).append(" FROM ").append(tableName).append(" WHERE ")
.append(whereColumn).append(" = ?");
String sql = stringBuilder.toString();
Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = null;
try {
ps.setString(1, whereValue);
rs = ps.executeQuery();
if (rs.next()) {
return rs.getString(1);
}
} finally {
try {
if (rs != null) {
rs.close();
}
ps.close();
connection.close();
} catch (Exception ignore) {
log.error("An error occurred while closing database connections ", ignore);
}
}
return null;
}
/**
* Create table utility method.
*
* @param sql
* SQL to be executed.
* @throws SQLException
* If an error occurred while creating the table.
*/
public void executeSQL(String sql) throws SQLException {
Connection connection = getConnection();
PreparedStatement ps = connection.prepareStatement(sql);
try {
ps.executeUpdate();
connection.commit();
} finally {
try {
if (ps != null) {
ps.close();
}
connection.close();
} catch (Exception ignore) {
log.error("An error occurred while closing database connections ", ignore);
}
}
}
private void loadDriver() throws ClassNotFoundException, IllegalAccessException, InstantiationException {
Class.forName(driverName).newInstance();
}
/**
* Gets a new DBCP data source.
*
* @return A new data source.
*/
public DataSource getDataSource() {
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName(this.driverName);
ds.setUsername(this.databaseUserName);
ds.setPassword(this.databasePassword);
ds.setUrl(this.jdbcUrl);
return ds;
}
/**
* Creates a new JDBC connections based on provided DBCP properties.
*
* @return A new DB connection.
* @throws SQLException
* If an error occurred while creating the connection.
*/
public Connection getConnection() throws SQLException {
Connection connection = DriverManager.getConnection(jdbcUrl, properties);
connection.setAutoCommit(false);
return connection;
}
/**
* Utility method to close statements and connections.
*
* @param preparedStatement
* The prepared statement to close.
* @param connection
* The connection to close.
*/
public static void cleanup(PreparedStatement preparedStatement, Connection connection) {
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
log.error("Error closing prepared statement.", e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
log.error("Error closing database connection.", e);
}
}
}
/**
* Utility method to close statements and connections.
*
* @param preparedStatement
* The prepared statement to close.
*/
public static void cleanup(PreparedStatement preparedStatement) {
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
log.error("Error closing prepared statement.", e);
}
}
}
/**
* Utility method to close statements and connections.
*
* @param preparedStatement
* The prepared statement to close.
*/
public static void cleanup(PreparedStatement preparedStatement, ResultSet resultSet) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
log.error("Error closing prepared statement.", e);
}
}
cleanup(preparedStatement);
}
/**
* Cleanup the connection.
* @param connection The connection to close.
*/
public static void cleanup(Connection connection) {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
log.debug("Error closing connection.", e);
log.warn("Error closing connection.");
}
}
}
/**
* Mainly useful for tests.
*
* @param tableName
* The table name.
* @param connection
* The connection to be used.
*/
public static void truncate(String tableName, Connection connection) throws SQLException {
String sql = "delete from " + tableName;
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.executeUpdate();
connection.commit();
}
/**
* Creates a DBUtil object based on servlet context configurations.
*
* @return DBUtil object.
* @throws Exception
* If an error occurred while reading configurations or while creating database object.
*/
public static DBUtil getCredentialStoreDBUtil() throws ApplicationSettingsException, IllegalAccessException,
ClassNotFoundException, InstantiationException {
String jdbcUrl = ServerSettings.getCredentialStoreDBURL();
String userName = ServerSettings.getCredentialStoreDBUser();
String password = ServerSettings.getCredentialStoreDBPassword();
String driverName = ServerSettings.getCredentialStoreDBDriver();
StringBuilder stringBuilder = new StringBuilder("Starting credential store, connecting to database - ");
stringBuilder.append(jdbcUrl).append(" DB user - ").append(userName).append(" driver name - ")
.append(driverName);
log.debug(stringBuilder.toString());
DBUtil dbUtil = new DBUtil(jdbcUrl, userName, password, driverName);
dbUtil.init();
return dbUtil;
}
}