package com.quantum.sql; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.InputStream; import java.io.Reader; import java.io.UnsupportedEncodingException; import java.net.MalformedURLException; import java.net.URL; import java.net.URLClassLoader; 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.Hashtable; import java.util.Properties; import java.util.Vector; import com.quantum.model.Bookmark; import com.quantum.model.ConnectionException; import com.quantum.model.Entity; import com.quantum.model.PasswordFinder; import com.quantum.sql.metadata.MetaDataJDBCInterface; import com.quantum.sql.metadata.ObjectMetaData; import com.quantum.view.LogProxy; import com.quantum.view.bookmark.EntityNode; import com.quantum.view.bookmark.TreeNode; /** * MultiSQLServer is a Singleton, used as a interface with the sql drivers. * 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; private Hashtable classLoaderCache = new Hashtable(); boolean running = true; public MultiSQLServer() { //start(); } public synchronized static MultiSQLServer getInstance() { if (instance == null) { instance = new MultiSQLServer(); } return instance; } public void commit(Connection con) { LogProxy log = LogProxy.getInstance(); try { con.commit(); } catch (SQLException e) { log.addText(LogProxy.ERROR, "Error commiting: " + e, e); //$NON-NLS-1$ } } public void rollback(Connection con) { LogProxy log = LogProxy.getInstance(); try { con.rollback(); } catch (SQLException e) { log.addText(LogProxy.ERROR, "Error rolling back: " + e, e); //$NON-NLS-1$ } } public void setAutoCommit(Connection con, boolean enabled) { LogProxy log = LogProxy.getInstance(); try { if (con != null) { con.setAutoCommit(enabled); } else { log.addText(LogProxy.ERROR, "Please connect before setting autocommit"); //$NON-NLS-1$ } } catch (SQLException e) { log.addText(LogProxy.ERROR, "Error setting autocommit: " + e, e); //$NON-NLS-1$ } } public void disconnect(Connection connection) throws ConnectionException { try { if (connection != null) { connection.close(); } } catch (SQLException e) { throw new ConnectionException(e); } } 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 - * The Bookmark with the data needed to make the connection * @param passwordFinder - * A utility class that can be invoked if the bookmark does not * include a password * @return The Connection object if everything went OK */ public Connection connect(Bookmark bookmark, PasswordFinder passwordFinder) throws ConnectionException { String password = bookmark.getPassword(); if (bookmark.getPromptForPassword()) { password = passwordFinder.getPassword(); if (passwordFinder.isPasswordMeantToBeSaved()) { bookmark.setPassword(password); } } Connection con; if (password != null) { con = connect(bookmark, password); } 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) throws ConnectionException { LogProxy log = LogProxy.getInstance(); log.addText(LogProxy.QUERY, "Connecting to: " + bookmark.getName()); //$NON-NLS-1$ URL urls[] = new URL[1]; try { String driverFile = bookmark.getDriverFile(); URLClassLoader loader = (URLClassLoader) classLoaderCache.get(driverFile); if (loader == null) { urls[0] = new File(driverFile).toURL(); loader = new URLClassLoader(urls); classLoaderCache.put(driverFile, loader); System.out.println("Creating new classloader"); //$NON-NLS-1$ } else { System.out.println("Using classloader in cache"); //$NON-NLS-1$ } Class driverClass = loader.loadClass(bookmark.getDriver()); Driver driver = (Driver) driverClass.newInstance(); Properties props = new Properties(); props.put(USERNAME, bookmark.getUsername()); props.put(PASSWORD, password); Connection connection = driver.connect(bookmark.getConnect(), props); if (connection == null) { throw new ConnectionException("Error: Driver returned a null connection: " + bookmark.toString()); //$NON-NLS-1$ } log.addText(LogProxy.RESULTS, "Connected to: " + bookmark.getName()); //$NON-NLS-1$ System.out.println("Connected"); //$NON-NLS-1$ return connection; } catch (SQLException e) { throw new ConnectionException(e); } catch (MalformedURLException e) { throw new ConnectionException(e); } catch (ClassNotFoundException e) { throw new ConnectionException(e); } catch (InstantiationException e) { throw new ConnectionException(e); } catch (IllegalAccessException 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(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( 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(""); //$NON-NLS-1$ } else { row.addElement(value); } } results.addRow(row); } rowCount++; if (!disable && (rowCount > endRow)) { exitEarly = true; break; } } if (exitEarly) { results.setHasMore(set.next()); } else { results.setMaxSize(rowCount); results.setHasMore(false); } 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)); } else { row.addElement(textSize + ", " + precision + ", " + scale); //$NON-NLS-1$ //$NON-NLS-2$ } 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(""); //$NON-NLS-1$ } 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 metadata; } }