package com.tesora.dve.common;
/*
* #%L
* Tesora Inc.
* Database Virtualization Engine
* %%
* Copyright (C) 2011 - 2014 Tesora Inc.
* %%
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License, version 3,
* 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 Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
* #L%
*/
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import com.tesora.dve.exceptions.PECodingException;
import com.tesora.dve.exceptions.PEException;
public class DBHelper {
public static final String CONN_ROOT = PEConstants.PROP_JPA_JDBC_PREFIX;
public static final String CONN_DRIVER_CLASS = PEConstants.PROP_FULL_JDBC_DRIVER;
public static final String CONN_URL = PEConstants.PROP_FULL_JDBC_URL;
public static final String CONN_USER = PEConstants.PROP_FULL_JDBC_USER;
public static final String CONN_PASSWORD = PEConstants.PROP_FULL_JDBC_PASSWORD;
public static final String CONN_DBNAME = PEConstants.PROP_DBNAME;
private static Logger logger = Logger.getLogger(DBHelper.class);
protected PEUrl connUrl;
protected String connUserName;
protected String connPwd;
protected String connDBName = null;
protected Connection connection = null;
protected Statement stmt = null;
protected PreparedStatement prepStmt = null;
protected String prepQuery;
protected int rowCount = 0;
protected long lastInsertID = 0L;
protected ResultSet resultSet = null;
protected boolean enableResultSetStreaming = false;
protected boolean useBufferedQuery = false;
protected boolean disconnectAfterProcess = true;
@SuppressWarnings("resource")
public static Charset getConnectionCharset(final DBHelper dbHelper) throws SQLException {
final Connection connection = dbHelper.getConnection();
if ((connection != null) && !connection.isClosed()) {
try (final ResultSet rs = connection.createStatement().executeQuery("SHOW VARIABLES WHERE Variable_name = 'character_set_connection'")) {
rs.last();
final String connectionCharsetName = rs.getString(2);
return Charset.forName(PEStringUtils.dequote(connectionCharsetName));
}
}
throw new PECodingException("Database connection is not open.");
}
/**
* @param props
* @throws PEException
*/
public DBHelper(Properties props) throws PEException {
this(props.getProperty(CONN_URL), props.getProperty(CONN_USER), props.getProperty(CONN_PASSWORD), props
.getProperty(CONN_DBNAME));
}
/**
* @param url
* @param userName
* @param password
* @param database
* @throws PEException
*/
public DBHelper(String url, String userName, String password, String database) throws PEException {
this(PEUrl.fromUrlString(url), userName, password, database);
}
/**
* @param url
* @param userName
* @param password
* @throws PEException
*/
public DBHelper(String url, String userName, String password) throws PEException {
this(PEUrl.fromUrlString(url), userName, password);
}
/**
* @param url
* @param userName
* @param password
* @param database
* @throws PEException
*/
public DBHelper(PEUrl url, String userName, String password, String database) throws PEException {
this(url, userName, password);
this.connDBName = database;
}
/**
* @param url
* @param userName
* @param password
* @throws PEException
*/
public DBHelper(PEUrl url, String userName, String password) throws PEException {
this.connUrl = url;
this.connUserName = userName;
this.connPwd = password;
this.connDBName = connUrl.getPath();
}
public static DBType urlToDBType(String url) throws PEException {
return DBType.fromDriverClass(urlToDriverClass(PEUrl.fromUrlString(url)));
}
public static String urlToDriverClass(String url) throws PEException {
return urlToDriverClass(PEUrl.fromUrlString(url));
}
public static String urlToDriverClass(PEUrl url) throws PEException {
if (url.getSubProtocol().equalsIgnoreCase(PEConstants.MYSQL_SUBPROTOCOL))
return PEConstants.MYSQL_DRIVER_CLASS;
throw new PEException("Cannot determine JDBC driver class for '" + url.getSubProtocol() + "'");
}
public static String loadDriverForURL(String url) throws PEException {
return loadDriver(urlToDriverClass(url));
}
public static String loadDriverForURL(PEUrl url) throws PEException {
return loadDriver(urlToDriverClass(url));
}
public static String loadDriver(String driverClass) throws PEException {
try {
Class.forName(driverClass);
} catch (Exception e) {
logger.error("JDBC driver class '" + driverClass + "' not found");
throw new PEException("JDBC driver class '" + driverClass + "' not found");
}
return driverClass;
}
public Connection getConnection() {
return this.connection;
}
public PEUrl getUrl() {
return connUrl;
}
public String getUserName() {
return connUserName;
}
public String getPassword() {
return connPwd;
}
public String getDBName() {
return connDBName;
}
public void setDBName(String connDBName) {
this.connDBName = connDBName;
}
public void disconnect() {
logger.info("Disconnecting from " + this.connUrl + " as " + this.connUserName);
try {
closeStatement();
if (connection != null)
connection.close();
} catch (SQLException e) {
logger.error(e, e);
}
}
public DBHelper connect() throws PEException {
return connect(new Properties());
}
public DBHelper connect(Properties props) throws PEException {
props.put("user", connUserName);
props.put("password", connPwd);
return doConnect(props);
}
private DBHelper doConnect(Properties props) throws PEException {
DBHelper.loadDriverForURL(connUrl);
try {
logger.info("Connecting to " + this.connUrl + " as " + this.connUserName);
connection = DriverManager.getConnection(connUrl.getURL(), props);
} catch (Exception se) {
throw new PEException("Error connecting to database '" + connUrl + "' - " + se.getMessage(), se);
}
try {
if (this.connDBName != null) {
connection.setCatalog(connDBName);
logger.info("Database set to " + connDBName);
}
} catch (Exception se) {
throw new PEException(
"Error using database '" + connDBName + "' on '" + connUrl + "' - " + se.getMessage(), se);
}
return this;
}
public void closeStatement() throws SQLException {
if (resultSet != null) {
resultSet.close();
resultSet = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (prepStmt != null) {
prepStmt.close();
prepStmt = null;
}
}
/**
* @param query
* The query to run
* @return <b>true</b> if the first result is a ResultSet object;
* <b>false</b> if it is an update count or there are no results
* @throws SQLException
*/
public boolean executeQuery(String query) throws SQLException {
closeStatement();
checkConnected();
if (logger.isDebugEnabled())
logger.debug("ExecuteQuery '" + query + "' on " + getUrl());
stmt = connection.createStatement();
lastInsertID = 0L;
if (isEnableResultSetStreaming()) {
stmt.setFetchSize(Integer.MIN_VALUE);
}
boolean ret = stmt.execute(query, Statement.RETURN_GENERATED_KEYS);
if (!ret) {
// when stmt.execute returns false it means no result set is
// expected get the number of rows affected
rowCount = stmt.getUpdateCount();
printLine(rowCount + " rows affected");
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
lastInsertID = rs.getLong(1);
}
} else {
// a stmt returning a result set was run
resultSet = stmt.getResultSet();
if (useBufferedQuery)
resultSet.setFetchSize(Integer.MAX_VALUE);
}
return ret;
}
/**
* @param line
*/
protected void printLine(String line) {
// TODO - implement this?
}
public void executeFromStream(InputStream is) throws SQLException {
final String cmdDelimeter = ";";
final String comment = "--";
String command = "";
String separator = "";
BufferedReader reader = null;
try {
reader = new BufferedReader(new InputStreamReader(is));
String line = null;
while ((line = reader.readLine()) != null) {
if (line.equalsIgnoreCase("quit") || line.equalsIgnoreCase("exit")) {
break;
}
String trimmed = line.trim();
if (trimmed.startsWith(comment))
continue;
if (trimmed.endsWith(cmdDelimeter)) {
command = command + separator + line;
try {
executeQuery(command);
} catch (SQLException se) {
System.out.println(command);
throw se;
}
command = "";
} else {
command = command + separator + line;
separator = " ";
}
}
} catch (IOException e) {
logger.error(e, e);
e.printStackTrace();
} finally {
if (disconnectAfterProcess)
this.disconnect();
if (reader != null)
try {
reader.close();
} catch (Exception e) {
// do nothing
}
}
}
public int getRowCount() {
return rowCount;
}
public long getLastInsertID() {
return lastInsertID;
}
public ResultSet getResultSet() {
return resultSet;
}
public void setResultSet(ResultSet resultSet) {
this.resultSet = resultSet;
}
public void prepare(String query) throws SQLException {
closeStatement();
checkConnected();
prepQuery = query;
prepStmt = connection.prepareStatement(query);
}
public PreparedStatement prepareIndependent(String query) throws SQLException {
if (resultSet != null) {
resultSet.close();
resultSet = null;
}
checkConnected();
return connection.prepareStatement(query);
}
private boolean executePrepared(PreparedStatement ps, String query, List<Object> params) throws SQLException,
PEException {
if (ps == null) {
throw new PEException("A prepared statement is not available to be executed - call prepare first");
}
if (logger.isDebugEnabled() && query != null)
logger.debug("Command: " + query);
for (int i = 0; i < params.size(); i++) {
ps.setObject(i + 1, params.get(i));
}
boolean ret = ps.execute();
if (!ret) {
// when stmt.execute returns false it means no result set is
// expected get the number of rows affected
rowCount = ps.getUpdateCount();
printLine(rowCount + " rows affected");
} else {
// a prepStmt returning a result set was run
resultSet = ps.getResultSet();
if (useBufferedQuery)
resultSet.setFetchSize(Integer.MAX_VALUE);
}
return ret;
}
public boolean executePrepared(List<Object> params) throws SQLException, PEException {
return executePrepared(prepStmt, prepQuery, params);
}
public boolean executePrepared(PreparedStatement ps, List<Object> params) throws SQLException, PEException {
return executePrepared(ps, null, params);
}
public boolean isEnableResultSetStreaming() {
return enableResultSetStreaming;
}
public void setEnableResultSetStreaming(boolean enableResultSetStreaming) {
this.enableResultSetStreaming = enableResultSetStreaming;
}
public boolean useBufferedQuery() {
return useBufferedQuery;
}
public void setBufferedQuery(boolean useBufferedQuery) {
this.useBufferedQuery = useBufferedQuery;
}
public boolean disconnectAfterProcess() {
return disconnectAfterProcess;
}
public void setDisconnectAfterProcess(boolean disconnectAfterProcess) {
this.disconnectAfterProcess = disconnectAfterProcess;
}
public int getNumWarnings() throws SQLException {
int count = 0;
if (stmt != null) {
SQLWarning warning = stmt.getWarnings();
while (warning != null) {
// TODO: filter for our test code until SHOW is completely fixed
if (!StringUtils.equalsIgnoreCase(warning.getMessage(), "SHOW WARNINGS is not supported")) {
count++;
}
warning = warning.getNextWarning();
}
return count;
}
return 0;
}
private void checkConnected() throws SQLException {
if (connection == null)
throw new SQLException("Not connected");
}
}