package com.quantum.sql;
-import java.io.ByteArrayOutputStream;
-import java.io.InputStream;
-import java.io.Reader;
-import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.ResultSet;
-import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
-import java.util.Vector;
+import com.quantum.Messages;
+import com.quantum.adapters.AdapterFactory;
+import com.quantum.adapters.DatabaseAdapter;
import com.quantum.model.Bookmark;
import com.quantum.model.ConnectionException;
import com.quantum.model.Entity;
import com.quantum.model.JDBCDriver;
import com.quantum.model.PasswordFinder;
-import com.quantum.sql.metadata.MetaDataJDBCInterface;
-import com.quantum.sql.metadata.ObjectMetaData;
+import com.quantum.util.sql.SQLInstructionBuilder;
import com.quantum.view.LogProxy;
-import com.quantum.view.bookmark.EntityNode;
-import com.quantum.view.bookmark.TreeNode;
/**
* Use MultiSQLServer.getInstance() to get the object.
*/
public class MultiSQLServer implements ConnectionEstablisher {
- private static final int STREAM = 1024 * 2;
public static final String USERNAME = "user"; //$NON-NLS-1$
public static final String PASSWORD = "password"; //$NON-NLS-1$
private static MultiSQLServer instance = null;
- boolean running = true;
private MultiSQLServer() {
}
return instance;
}
- public void commit(Connection con) {
+ public void commit(Connection con) throws SQLException {
LogProxy log = LogProxy.getInstance();
try {
con.commit();
} catch (SQLException e) {
log.addText(LogProxy.ERROR, "Error commiting: " + e, e); //$NON-NLS-1$
+ throw e;
}
}
- public void rollback(Connection con) {
+ public void rollback(Connection con) throws SQLException {
LogProxy log = LogProxy.getInstance();
try {
con.rollback();
} catch (SQLException e) {
log.addText(LogProxy.ERROR, "Error rolling back: " + e, e); //$NON-NLS-1$
+ throw e;
}
}
}
}
- public void disconnect(Connection connection) throws ConnectionException {
- try {
- if (connection != null) {
- connection.close();
- }
- } catch (SQLException e) {
- throw new ConnectionException(e);
+ public void disconnect(Connection connection) throws SQLException {
+ if (connection != null) {
+ connection.close();
}
}
- public Vector getSchemas(Connection con) {
- ResultSet set;
- Vector schemaList = new Vector();
- try {
- DatabaseMetaData meta = con.getMetaData();
- set = meta.getSchemas();
- while (set.next()) {
- schemaList.add(set.getString("TABLE_SCHEM")); //$NON-NLS-1$
- }
- set.close();
- } catch (SQLException e) {
- LogProxy log = LogProxy.getInstance();
- log.addText(LogProxy.ERROR, e);
- }
- return schemaList;
- }
/**
* Makes a connection to a JDBC driver based on the data from a bookmark
* @param bookmark -
bookmark.setPassword(password);
}
}
- Connection con;
+
if (password != null) {
- con = connect(bookmark, password);
+ Connection connection = connect(bookmark, password);
+ if (connection != null) {
+ // Set the autoCommit state of the bookmark to the default on new connections
+ bookmark.setAutoCommit(bookmark.getDefaultAutoCommit());
+ // Set the autoCommit state of the JDBC connection to the bookmark autoCommit statec
+ setAutoCommit(connection, bookmark.isAutoCommit());
+ }
+ return connection;
} else {
return null;
}
- // Set the autoCommit state of the bookmark to the default on new connections
- bookmark.setAutoCommit(bookmark.getDefaultAutoCommit());
- // Set the autoCommit state of the JDBC connection to the bookmark autoCommit statec
- setAutoCommit(con, bookmark.isAutoCommit());
- return con;
}
private Connection connect(Bookmark bookmark, String password)
log.addText(LogProxy.QUERY, "Connecting to: " + bookmark.getName()); //$NON-NLS-1$
try {
JDBCDriver jdbcDriver = bookmark.getJDBCDriver();
- Driver driver = jdbcDriver.getDriver();
- Connection connection = null;
+ Driver driver = jdbcDriver.getDriver();
if (driver != null) {
Properties props = new Properties();
props.put(USERNAME, bookmark.getUsername());
props.put(PASSWORD, password);
- connection =
+ // TODO: This kind of things should really be made general-purpose
+ if (jdbcDriver.getType().equals(AdapterFactory.ORACLE)){
+ // remarksReporting will make the JDBC driver return the remarks for the tables and
+ // the columns. It'll make getting the tables and columns much slower, so it should
+ // really be made into an option
+ // TODO: Make remark reporting into an option
+ props.put("remarksReporting", "true");
+ // includeSynonyms will make the JDBC driver return the proper columns when querying
+ // about a synonym. If not given, synonyms will appear with no columns, exports of data
+ // containing synonyms will break, etc.n So it's needed from the moment you add the synonyms
+ // with the getSynonymsList() in the Database.getEntities() function. That could also be
+ // made into an option, but more logically when more databases are addedd
+ // TODO: Make including synonyms into an option
+ props.put("includeSynonyms", "true");
+ }
+ Connection connection =
driver.connect(bookmark.getConnect(), props);
if (connection == null) {
throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$
jdbcDriver.setVersion(metaData.getDriverVersion());
log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$
System.out.println("Connected"); //$NON-NLS-1$
+ return connection;
+ } else {
+ throw new ConnectionException(Messages.getString(
+ ConnectionException.class, "couldNotInstantiateDriver",
+ new Object[] { jdbcDriver.getClassName(), bookmark.getName() }));
}
- return connection;
} catch (SQLException e) {
throw new ConnectionException(e);
}
}
- public SQLResults execute(Connection con, String s) throws SQLException {
- return execute(con, s, -1, -1);
- }
- public SQLResults execute(Connection con, String s, int startRow, int endRow) throws SQLException {
- return execute(con, s, -1, -1, Integer.MAX_VALUE);
+ public SQLResults execute(Bookmark bookmark, Connection con, Entity entity, String s)
+ throws SQLException {
+ return execute(bookmark, con, entity, s, 200);
}
- public SQLResults execute(Connection con, String s, int startRow, int endRow, int maxLength) throws SQLException {
- return execute(con, s, startRow, endRow, maxLength, ""); //$NON-NLS-1$
+ public SQLResults execute(Bookmark bookmark, Connection con, String s)
+ throws SQLException {
+ return execute(bookmark, con, null, s, 200);
}
- public SQLResults execute(
- Connection con,
- String s,
- int startRow,
- int endRow,
- int maxLength,
- String encoding)
- throws SQLException {
-
- SQLResults results = new SQLResults();
-
- System.out.println("Executing"); //$NON-NLS-1$
- LogProxy log = LogProxy.getInstance();
- log.addText(LogProxy.QUERY, "Executing Request [" + s + "]"); //$NON-NLS-1$ //$NON-NLS-2$
- boolean metadata = false;
- if (s.startsWith("METADATA")) { //$NON-NLS-1$
- metadata = true;
- }
- if (metadata) {
- results.setQuery(s);
- String table = s.substring(s.indexOf(':') + 1);
- String query = "SELECT * FROM " + table + " WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$
- s = query;
- log.addText(LogProxy.QUERY, "Metadata Request [" + s + "]"); //$NON-NLS-1$ //$NON-NLS-2$
- } else {
- results.setQuery(s);
- }
-
- Statement stmt = con.createStatement();
- boolean flag = stmt.execute(s);
- results.setResultSet(flag);
- if (metadata) {
- genMetadataResultSet(results, stmt);
- return results;
- }
- if (!flag) {
- int updates = stmt.getUpdateCount();
- results.setUpdateCount(updates);
- log.addText(LogProxy.RESULTS, "Success: " + updates + " records updated"); //$NON-NLS-1$ //$NON-NLS-2$
-
- } else {
- ResultSet set = stmt.getResultSet();
- ResultSetMetaData metaData = set.getMetaData();
- int columnCount = metaData.getColumnCount();
- Vector columnNames = new Vector();
- for (int i = 1; i <= columnCount; i++) {
- columnNames.addElement(metaData.getColumnName(i));
- }
- results.setColumnNames(columnNames);
- Vector columnTypes = new Vector();
- for (int i = 1; i <= columnCount; i++) {
- columnTypes.addElement(metaData.getColumnTypeName(i));
- }
- results.setColumnTypes(columnTypes);
- int columnSizes[] = new int[columnCount];
- for (int i = 1; i <= columnCount; i++) {
- columnSizes[i - 1] = metaData.getColumnDisplaySize(i);
- }
- int rowCount = 1;
- boolean exitEarly = false;
- while (set.next()) {
- boolean disable = startRow < 1 || endRow < 1;
- boolean start = rowCount >= startRow;
- boolean end = rowCount <= endRow;
- if (disable || (start && end)) {
- Vector row = new Vector();
- for (int i = 1; i <= columnCount; i++) {
- String value;
- if (columnSizes[i - 1] < STREAM
- && columnSizes[i - 1] < maxLength) {
- if (encoding.equals("")) { //$NON-NLS-1$
- value = set.getString(i);
- } else {
- try {
- value =
- new String(set.getBytes(i), encoding);
- } catch (UnsupportedEncodingException e) {
- log.addText(LogProxy.ERROR, "Error Unsupported encoding " + encoding.toString() + ":" + e.toString(), e); //$NON-NLS-1$ //$NON-NLS-2$
- value = new String(set.getBytes(i));
- }
- }
- } else {
- try {
- if (encoding.equals("")) { //$NON-NLS-1$
- Reader reader = set.getCharacterStream(i);
- StringBuffer buffer = new StringBuffer();
- if (reader != null) {
- int retVal = reader.read();
- int count = 0;
- while (retVal >= 0) {
- buffer.append((char) retVal);
- retVal = reader.read();
- count++;
- if (count > maxLength) {
- buffer.append("...>>>"); //$NON-NLS-1$
- break;
- }
- }
- reader.close();
- }
- value = buffer.toString();
- } else {
- InputStream binaryStream =
- set.getBinaryStream(i);
- ByteArrayOutputStream baos =
- new ByteArrayOutputStream();
- if (binaryStream != null) {
- int retVal = binaryStream.read();
- int count = 0;
- while (retVal >= 0) {
- baos.write(retVal);
- retVal = binaryStream.read();
- count++;
- if (count > maxLength) {
- break;
- }
- }
- binaryStream.close();
- }
- value =
- new String(
- baos.toByteArray(),
- encoding);
- }
- } catch (Throwable e) {
- // hack for mysql which doesn't implement
- // character streams
- value = set.getString(i);
- }
- }
- if (set.wasNull()) {
- row.addElement("<NULL>"); //$NON-NLS-1$
- } else {
- row.addElement(value);
- }
- }
- results.addRow(row);
+ public SQLResultSetResults getMetaData(Entity entity, Connection connection) throws SQLException {
+ String query = SQLInstructionBuilder.buildSelectAllColumnsNoRows(entity);
+ SQLResultSetResults results = null;
+ if (connection != null) {
+ Statement statement = connection.createStatement();
+ try {
+ ResultSet set = statement.executeQuery(query);
+ try {
+ results = SQLMetaDataResults.create(entity.getBookmark(), set, query, entity);
+ } finally {
+ set.close();
}
- rowCount++;
- if (!disable && (rowCount > endRow)) {
- exitEarly = true;
- break;
- }
- }
- if (exitEarly) {
- results.setHasMore(set.next());
- } else {
- results.setMaxSize(rowCount);
- results.setHasMore(false);
+ } finally {
+ statement.close();
}
- set.close();
}
- log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$
- stmt.close();
- System.out.println("Executed"); //$NON-NLS-1$
- System.out.println();
return results;
}
- private void genMetadataResultSet(SQLResults results, Statement stmt)
- throws SQLException {
- ResultSet set = stmt.getResultSet();
- ResultSetMetaData metaData = set.getMetaData();
- int columnCount = metaData.getColumnCount();
- Vector columnNames = new Vector();
- columnNames.addElement("ColumnName"); //$NON-NLS-1$
- columnNames.addElement("Type"); //$NON-NLS-1$
- columnNames.addElement("Size"); //$NON-NLS-1$
- columnNames.addElement("Nullable"); //$NON-NLS-1$
- columnNames.addElement("AutoIncrement"); //$NON-NLS-1$
- results.setColumnNames(columnNames);
- for (int i = 1; i <= columnCount; i++) {
- Vector row = new Vector();
- row.addElement(metaData.getColumnName(i));
- row.addElement(metaData.getColumnTypeName(i));
- int textSize = metaData.getColumnDisplaySize(i);
- int precision = metaData.getPrecision(i);
- int scale = metaData.getScale(i);
- if (scale == 0 && precision == 0) {
- row.addElement(Integer.toString(precision));
+
+ public SQLResults execute(Bookmark bookmark, Connection con, String sql,
+ int numberOfRowsPerPage) throws SQLException {
+ return execute(bookmark, con, null, sql, numberOfRowsPerPage);
+ }
+
+
+ public SQLResults execute(
+ Bookmark bookmark,
+ Connection con,
+ Entity entity,
+ String sql,
+ int numberOfRowsPerPage)
+ throws SQLException {
+
+ long startTime = System.currentTimeMillis();
+ System.out.println("Executing"); //$NON-NLS-1$
+ LogProxy log = LogProxy.getInstance();
+ log.addText(LogProxy.QUERY, "SQL (" + bookmark.getName() + ") [" + sql + "]"); //$NON-NLS-1$ //$NON-NLS-2$
+ Statement statement = con.createStatement();
+ try {
+ SQLResults results;
+ if (statement.execute(sql)) {
+ ResultSet set = statement.getResultSet();
+ try {
+ results = SQLStandardResultSetResults.create(set, bookmark, sql, entity, numberOfRowsPerPage);
+ } finally {
+ set.close();
+ }
} else {
- row.addElement(textSize + ", " + precision + ", " + scale); //$NON-NLS-1$ //$NON-NLS-2$
+ int updates = statement.getUpdateCount();
+ results = new SQLUpdateResults(updates);
}
- int nullable = metaData.isNullable(i);
- if (nullable == ResultSetMetaData.columnNoNulls) {
- row.addElement("Not Null"); //$NON-NLS-1$
- } else if (nullable == ResultSetMetaData.columnNullable) {
- row.addElement("Nullable"); //$NON-NLS-1$
- } else if (
- nullable == ResultSetMetaData.columnNullableUnknown) {
- row.addElement("Nullable"); //$NON-NLS-1$
- } else {
- row.addElement("<Error>"); //$NON-NLS-1$
+ log.addText(LogProxy.RESULTS, "Success: result set displayed"); //$NON-NLS-1$
+ if (results != null) {
+ results.setTime(System.currentTimeMillis() - startTime);
}
- row.addElement(
- (metaData.isAutoIncrement(i)
- ? Boolean.TRUE
- : Boolean.FALSE)
- .toString());
- results.addRow(row);
- }
- results.setHasMore(false);
- set.close();
- }
-
- /**
- * Returns an ObjectMetadata object got from the connection 'con' using the name and schema of the node.
- * @param con
- * @param node
- * @return
- * @throws SQLException
- */
- public ObjectMetaData getObjectMetadata(Connection con, TreeNode node) throws SQLException {
- ObjectMetaData metadata = new ObjectMetaData();
- if (!(node instanceof Entity)) return metadata;
-
- String schema = ((Entity)node).getSchema();
- String tableName = node.getName();
-
- if (schema.length() == 0) schema = null;
- metadata.setColumns(MetaDataJDBCInterface.getColumns(con, schema, tableName));
- if (node instanceof EntityNode && ((EntityNode) node).isTable()) {
- metadata.setPrimaryKeys(MetaDataJDBCInterface.getPrimaryKeys(con, schema, tableName));
- metadata.setForeignKeys(MetaDataJDBCInterface.getForeignKeys(con, schema, tableName, true));
- metadata.setIndexInfo(MetaDataJDBCInterface.getIndexInfo(con, schema, tableName));
- metadata.setBestRowId(MetaDataJDBCInterface.getBestRowId(con, schema, tableName));
+ return results;
+ } finally {
+ statement.close();
}
- return metadata;
}
+ public int getSize(Bookmark bookmark, Connection connection, String tableName, DatabaseAdapter adapter) throws SQLException {
+ SQLResultSetResults results = (SQLResultSetResults) execute(
+ bookmark, connection, adapter.getCountQuery(tableName));
+ if (results.getRowCount() > 0 && results.getColumnCount() > 0) {
+ return Integer.parseInt(results.getElement(1, 1).toString());
+ } else {
+ return -1;
+ }
+ }
}
\ No newline at end of file
git://git.phpeclipse.com / phpeclipse.git / blobdiff