package com.kitten.database;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
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.util.logging.Logger;
import com.kitten.constants.KittenConstants;
import com.kitten.dao.KittenLobDao;
import com.kitten.dao.KittenResultSetSize;
import com.kitten.exceptions.KittenCannotRunStatementException;
import com.kitten.utilities.KittenUtilities;
public class KittenExecuteSQLCommand implements IKittenExecuteDatabaseCommand {
private static Logger log = Logger.getLogger(KittenExecuteSQLCommand.class.getName());
private Connection connection;
private KittenDatabaseExecutionData executionData;
private IKittenSQLCommandListener listener;
private boolean typeForwardOnly=false;
public KittenExecuteSQLCommand(KittenDatabaseExecutionData executionData, IKittenDatabaseListener databaseListener) {
super();
this.executionData = executionData;
this.listener = databaseListener;
try{log.addHandler(this.executionData.getKittenProperties().getLogFileHandler());} catch (Exception e){}
}
public KittenDatabaseExecutionData getKittenDatabaseExecutionData() {
return this.executionData;
}
private void executeCommands() throws KittenCannotRunStatementException {
boolean resultSetUpdatable = false;
String[] statements = this.executionData.getStatements();
if (statements != null) {
//null should not be possible, has already been checked
if (executionData.getKittenConnectionDao().getConnection() != null) {
//null should not be possible, has already been checked
connection = executionData.getKittenConnectionDao().getConnection();
try {
// System.out.println("execute sql1:"+connection.getAutoCommit()+" "+executionData.getKittenDao().isAutoCommit());
connection.setAutoCommit(executionData.getKittenDao().isAutoCommit());
// System.out.println("execute sql2:"+this.connection.getAutoCommit()+" "+executionData.getKittenDao().isAutoCommit());
for (int i = 0; i < statements.length; i++) {
if (statements[i] != null || "".equals(statements[i])) {
//statements[i]=statements[i].replaceAll("\\/","\\\\\\/");
PreparedStatement preparedStatement = getPreparedStatement(statements[i]);
if (preparedStatement != null) {
statements[i] = KittenDatabaseHelper.performPreExecute(preparedStatement, statements[i], executionData.getKittenDao().getMaxRows(), executionData.getKittenDao().getMaxSecs());
log.info("Executing statement: " + statements[i] + ", timeout: " + (preparedStatement.getQueryTimeout() == 0 ? "No time out..." : preparedStatement.getQueryTimeout() + " second(s)..."));
readTables();
ResultSet resultSet = executeStatement(preparedStatement, statements[i]);
try {
if (resultSet != null) {
if (resultSet.getConcurrency() == ResultSet.CONCUR_UPDATABLE) {
resultSetUpdatable = true;
}
// check4PrimaryKeys() ;
performPostExecute(resultSet, preparedStatement, resultSetUpdatable);
//executionData.getKittenDao().getKittenResultDao().setKittenTableModel(new KittenTableModel(resultSet,resultSetUpdatable));
}
} catch (Exception e) {
rollback(connection);
KittenDatabaseHelper.closeOpenThings(resultSet, preparedStatement);
throw new KittenCannotRunStatementException("Cannot execute SQL statement... \n" + e);
} finally {
if (resultSet != null) {
} else {
KittenDatabaseHelper.closeOpenThings(null, preparedStatement);
}
}
} //preparedStatement ==null
} else {
throw new KittenCannotRunStatementException("Empty(null) statement! ");
}
}
} catch (Exception sql) {
rollback(connection);
throw new KittenCannotRunStatementException("Cannot execute SQL statement.... \n" + sql.getMessage());
}
}
}
}
private ResultSet executeStatement(PreparedStatement preparedStatement, String statement) throws Exception,KittenCannotRunStatementException {
int numOfRowsAffected = 0;
ResultSet resultSet = null;
if (statement.indexOf(KittenConstants.KITTEN_BLOB_INSERT) > 0 || statement.indexOf(KittenConstants.KITTEN_BLOB_UPDATE) > 0) {
FileInputStream fis=null;
KittenLobDao kittenLobDao=null;
try {
if (KittenUtilities.retrieveNthWordInString(statement.trim(), 1, " ").equalsIgnoreCase(KittenConstants.INSERT)) {
kittenLobDao=KittenDatabaseHelper.getStatement4LobInsert(statement,KittenConstants.KITTEN_BLOB_INSERT);
} else if (KittenUtilities.retrieveNthWordInString(statement.trim(), 1, " ").equalsIgnoreCase(KittenConstants.UPDATE)) {
kittenLobDao=KittenDatabaseHelper.getStatement4LobUpdate(statement,KittenConstants.KITTEN_BLOB_UPDATE);
}
preparedStatement =connection.prepareStatement(kittenLobDao.getStatement());
for (int i=0;i<kittenLobDao.getPaths().size();i++){
File file = new File((String)kittenLobDao.getPaths().get(i));
int fileLength = (int) file.length();
fis = new FileInputStream(file);
preparedStatement.setBinaryStream(i+1, fis, (int)file.length());
}
preparedStatement.execute();
} catch (Exception e) {
throw new KittenCannotRunStatementException("Cannot insert/update BLOB..."+e);
}
} else if (statement.indexOf(KittenConstants.KITTEN_CLOB_INSERT) > 0 || statement.indexOf(KittenConstants.KITTEN_CLOB_UPDATE) > 0) {
//System.out.println("execute clob "+KittenUtilities.retrieveNthWordInString(statement.trim(), 1, " "));
InputStream fin=null;
KittenLobDao kittenLobDao=null;
try {
if (KittenUtilities.retrieveNthWordInString(statement.trim(), 1, " ").equalsIgnoreCase(KittenConstants.INSERT)) {
kittenLobDao=KittenDatabaseHelper.getStatement4LobInsert(statement,KittenConstants.KITTEN_CLOB_INSERT);
} else if (KittenUtilities.retrieveNthWordInString(statement.trim(), 1, " ").equalsIgnoreCase(KittenConstants.UPDATE)) {
kittenLobDao=KittenDatabaseHelper.getStatement4LobUpdate(statement,KittenConstants.KITTEN_CLOB_UPDATE);
}
preparedStatement =connection.prepareStatement(kittenLobDao.getStatement());
for (int i=0;i<kittenLobDao.getPaths().size();i++){
File file = new File((String)kittenLobDao.getPaths().get(i));
int fileLength = (int) file.length();
fin = new FileInputStream(file);
preparedStatement.setAsciiStream(i+1, fin, fileLength);
}
preparedStatement.execute();
} catch (Exception e) {
throw new KittenCannotRunStatementException("Cannot insert/update CLOB..."+e);
} finally {
try {
fin.close();
} catch (Exception e) {log.warning("Problems closing inputstream..."+e);}
}
} else {
if (KittenUtilities.retrieveNthWordInString(statement.trim(), 1, " ")==null){
throw new KittenCannotRunStatementException("Cannot run 'null' statement");
}
if (KittenUtilities.retrieveNthWordInString(statement.trim(), 1, " ").equalsIgnoreCase(KittenConstants.UPDATE)) {
numOfRowsAffected = preparedStatement.executeUpdate();
if (numOfRowsAffected == 1) {
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText(numOfRowsAffected + " row updated!");
} else {
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText(numOfRowsAffected + " rows updated!");
}
} else if (KittenUtilities.retrieveNthWordInString(statement.trim(), 1, " ").equalsIgnoreCase(KittenConstants.INSERT)) {
numOfRowsAffected = preparedStatement.executeUpdate();
if (numOfRowsAffected == 1) {
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText(numOfRowsAffected + " row inserted!");
} else {
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText(numOfRowsAffected + " rows inserted!");
}
} else if (KittenUtilities.retrieveNthWordInString(statement.trim(), 1, " ").equalsIgnoreCase(KittenConstants.DELETE)) {
numOfRowsAffected = preparedStatement.executeUpdate();
if (numOfRowsAffected == 1) {
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText(numOfRowsAffected + " row deleted!");
} else {
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText(numOfRowsAffected + " rows deleted!");
}
} else if (KittenUtilities.retrieveNthWordInString(statement, 1, " ").equalsIgnoreCase(KittenConstants.CREATE)) {
preparedStatement.execute();
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText(KittenUtilities.retrieveNthWordInString(statement.trim(), 2, " ") + " created!");
} else if (KittenUtilities.retrieveNthWordInString(statement, 1, " ").equalsIgnoreCase(KittenConstants.ALTER)) {
preparedStatement.execute();
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText(KittenUtilities.retrieveNthWordInString(statement.trim(), 2, " ") + " altered!");
} else if (KittenUtilities.retrieveNthWordInString(statement, 1, " ").equalsIgnoreCase(KittenConstants.DROP)) {
preparedStatement.execute();
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText(KittenUtilities.retrieveNthWordInString(statement.trim(), 2, " ") + " dropped!");
} else if (statement.trim().equalsIgnoreCase(KittenConstants.COMMIT) || statement.trim().equalsIgnoreCase(KittenConstants.ROLLBACK)) {
preparedStatement.execute();
executionData.getKittenConnectionDao().setCommitedRollbacked(true);
} else {
try {
resultSet = preparedStatement.executeQuery();
} catch (SQLException s) {
preparedStatement.execute();
}
}
}
return resultSet;
}
private PreparedStatement getPreparedStatement(String statement) throws SQLException,KittenCannotRunStatementException {
boolean isResultSetUpdatableAfterCommitOrRollback = executionData.getKittenConnectionDao().isResultSetUpdatableAfterCommitOrRollback();
boolean isResultSetUpdatableBeforeCommitOrRollback = executionData.getKittenConnectionDao().isResultSetUpdatableBeforeCommitOrRollback();
PreparedStatement preparedStatement = null;
boolean scrollable =checkScrollableResultSet(statement);
if (scrollable) {
if (isResultSetUpdatableBeforeCommitOrRollback && isResultSetUpdatableAfterCommitOrRollback) {
log.fine("ResultSet: updatable: true, holdable after commit/rollback: true....");
preparedStatement = connection.prepareStatement(statement, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE, ResultSet.HOLD_CURSORS_OVER_COMMIT);
} else if (isResultSetUpdatableBeforeCommitOrRollback && !isResultSetUpdatableAfterCommitOrRollback) {
log.fine("ResultSet: updatable: true, holdable after commit/rollback: false....");
preparedStatement = connection.prepareStatement(statement, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
} else {
preparedStatement = connection.prepareStatement(statement, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
log.fine("ResultSet: updatable: false, holdable after commit/rollback: false....");
}
} else {
log.fine("ResultSet: updatable: false, holdable after commit/rollback: false, and not scrollable.....");
preparedStatement = connection.prepareStatement(statement, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
typeForwardOnly=true;
}
return preparedStatement;
}
private void performPostExecute(ResultSet resultSet, PreparedStatement preparedStatement, boolean resultSetUpdatable) throws SQLException {
if (resultSet != null) {
KittenResultSetSize kittenResultSetSize =defineResultSetSize(resultSet);
// Object[] resultArray = KittenDatabaseHelper.makeResultReady(resultSet);
executionData.getKittenConnectionDao().setCommitedRollbacked(false);
executionData.getKittenDao().getKittenResultDao().setCellEditable(resultSetUpdatable);
executionData.getKittenDao().getKittenResultDao().setKittenResultSetSize(kittenResultSetSize);
// executionData.getKittenDao().getKittenResultDao().setDataAndColumn((Object[][]) resultArray[1], (String[]) resultArray[0], resultSet);
executionData.getKittenDao().getKittenResultDao().setDataAndColumn(resultSet,kittenResultSetSize);
KittenDatabaseHelper.addResultSet(resultSet);
//System.out.println("execute sql3:"+resultSetUpdatable+" "+executionData.getKittenDao().isAutoCommit());
}
}
private boolean checkScrollableResultSet(String statement) throws KittenCannotRunStatementException{
boolean scrollable=true;
PreparedStatement testStatement=null;
try {
testStatement = connection.prepareStatement(statement, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
} catch (Exception e) {
try {
testStatement = connection.prepareStatement(statement, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
scrollable=false;
} catch (SQLException sql) {
log.warning("Error encountered while determining resultset type! "+sql);
throw new KittenCannotRunStatementException("Error encountered while determining resultset type! "+sql);
}
typeForwardOnly=true;
}
return scrollable;
}
public void run() {
try { //System.out.println("execute sql: "+Thread. activeCount() );
executeCommands();
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText("SQL statement successfully executed...");
} catch (KittenCannotRunStatementException k) {
log.warning(KittenExecuteSQLCommand.class.getName() + ": " + k.getMessage());
executionData.getKittenDao().getKittenOutputTextDao().appendOutputText(k.getMessage());
}
listener.onResult(new IKittenSQLCommandListener.ResultEvent(this));
}
private void rollback(Connection c) {
try {
c.rollback();
} catch (Exception e) {
}
}
private KittenResultSetSize defineResultSetSize(ResultSet resultSet) throws SQLException{
//Define the size here so it only needs to be done once
ResultSetMetaData meta= resultSet.getMetaData();
KittenResultSetSize resultSetSize = new KittenResultSetSize();
if (!typeForwardOnly) {
resultSetSize.setNumOfCols(meta.getColumnCount()+1);
resultSet.last();
resultSetSize.setNumOfRows(resultSet.getRow());
resultSet.first();
} else {
resultSetSize.setNumOfCols(meta.getColumnCount()+1);
resultSetSize.setNumOfRows(-999);
}
return resultSetSize;
}
private void check4PrimaryKeys() throws SQLException{
DatabaseMetaData metadata = null;
metadata = connection.getMetaData();
ResultSet rs= metadata.getPrimaryKeys(null,null,"public.customers");
while (rs.next()){
System.out.println("1: "+rs.getObject("COLUMN_NAME"));
}
ResultSet rs2= metadata.getPrimaryKeys(null,null,"customers");
while (rs2.next()){
System.out.println("2: "+rs2.getObject("COLUMN_NAME"));
}
ResultSet rs3= metadata.getPrimaryKeys(null,"public","customers");
while (rs3.next()){
System.out.println("3: "+rs3.getObject("COLUMN_NAME"));
}
}
public void readTables() throws SQLException {
// PreparedStatement stmt =connection.prepareStatement("select * from TARWE where 1=-1");
// stmt.execute();
// for (int i=1;i<=stmt.getMetaData().getColumnCount();i++){
// //System.out.println(stmt.getMetaData().getColumnCount());
// System.out.println(stmt.getMetaData().getColumnClassName(i));
// }
// DatabaseMetaData metadata = null;
//
// metadata = connection.getMetaData();
// String[] names = {"TABLE"};
// ResultSet tableNames = metadata.getTables(null,"%", "%EMAIL_LOG%", names);
//
// while (tableNames.next()) {
// String table = new String(tableNames.getString("TABLE_NAME"));
// ResultSet columns = metadata.getColumns(null, "%", table, "%");
// while (columns.next()) {
// String columnName = columns.getString("COLUMN_NAME");
// String datatype = columns.getString("TYPE_NAME");
// int datasize = columns.getInt("COLUMN_SIZE");
// int digits = columns.getInt("DECIMAL_DIGITS");
// int nullable = columns.getInt("NULLABLE");
// System.out.println("Tabel: "+columnName+" "+datatype+" "+datasize+" ");
// }
// }
}
}
