Java tutorial
/* * 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 ro.nextreports.designer.dbviewer; import ro.nextreports.engine.Report; import ro.nextreports.engine.querybuilder.sql.dialect.CSVDialect; import ro.nextreports.engine.querybuilder.sql.dialect.Dialect; import ro.nextreports.engine.querybuilder.sql.dialect.DialectException; import ro.nextreports.engine.querybuilder.sql.dialect.OracleDialect; import ro.nextreports.engine.util.DialectUtil; import ro.nextreports.engine.util.ParameterUtil; import ro.nextreports.engine.util.ProcUtil; import ro.nextreports.engine.util.ReportUtil; import ro.nextreports.engine.queryexec.IdName; import ro.nextreports.engine.queryexec.QueryParameter; import java.sql.*; import java.util.*; import java.io.Serializable; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import ro.nextreports.designer.Globals; import ro.nextreports.designer.datasource.DataSource; import ro.nextreports.designer.datasource.DefaultDataSourceManager; import ro.nextreports.designer.dbviewer.common.*; import ro.nextreports.designer.util.I18NSupport; public class DefaultDBViewer implements DBViewer { public static final String NO_SCHEMA_NAME = "%"; private static final Log LOG = LogFactory.getLog(DefaultDBViewer.class); public DBInfo getDBInfo(String schemaName, int mask) throws NextSqlException { Connection con; try { con = Globals.getConnection(); } catch (Exception e) { throw new NextSqlException("Could not retrieve connection."); } return getDBInfo(schemaName, mask, con); } public DBInfo getDBInfo(int mask) throws NextSqlException { Connection con; try { con = Globals.getConnection(); } catch (Exception e) { throw new NextSqlException("Could not retrieve connection.", e); } return getDBInfo(mask, con); } public DBInfo getDBInfo(int mask, Connection con) throws NextSqlException { String schemaName; try { schemaName = con.getMetaData().getUserName(); /* * As of version 7 there was no equivalent to the concept of "schema" * in SQL Server. For DatabaseMetaData functions that include * SCHEMA_NAME drivers usually return the user name that owns the table. * * So for all databases in which schema name is different from the user * name, we use the schema name as "%" */ boolean foundSchema = false; try { ResultSet schemas = con.getMetaData().getSchemas(); while (schemas.next()) { String sch = schemas.getString("TABLE_SCHEM"); if (schemaName.equals(sch)) { foundSchema = true; break; } } } catch (SQLException ex) { LOG.error(ex.getMessage(), ex); ex.printStackTrace(); } if (!foundSchema) { schemaName = "%"; } } catch (Exception e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("Could not retrieve connection.", e); } return getDBInfo(schemaName, mask, con); } public DBInfo getDBInfo(String schemaName, int mask, Connection con) throws NextSqlException { String info = ""; List<String> keywords = new ArrayList<String>(); List<DBTable> tables = new ArrayList<DBTable>(); List<DBProcedure> procedures = new ArrayList<DBProcedure>(); Dialect dialect; try { dialect = DialectUtil.getDialect(con); } catch (Exception ex) { ex.printStackTrace(); throw new NextSqlException("Could not get Dialect.", ex); } try { DatabaseMetaData dbmd = con.getMetaData(); if ((mask & DBInfo.INFO) == DBInfo.INFO) { StringBuffer sb = new StringBuffer(); sb.append(I18NSupport.getString("database.product")).append(dbmd.getDatabaseProductName()) .append("\r\n"); sb.append(I18NSupport.getString("database.product.version")) .append(dbmd.getDatabaseProductVersion()).append("\r\n"); sb.append(I18NSupport.getString("database.driver.name")).append(dbmd.getDriverName()) .append("\r\n"); sb.append(I18NSupport.getString("database.driver.version")).append(dbmd.getDriverVersion()) .append("\r\n"); info = sb.toString(); } if ((mask & DBInfo.SUPPORTED_KEYWORDS) == DBInfo.SUPPORTED_KEYWORDS) { StringTokenizer st = new StringTokenizer(dbmd.getSQLKeywords(), ","); while (st.hasMoreTokens()) { keywords.add(st.nextToken()); } } // Get a ResultSet that contains all of the tables in this database // We specify a table_type of "TABLE" to prevent seeing system tables, // views and so forth boolean tableMask = ((mask & DBInfo.TABLES) == DBInfo.TABLES); boolean viewMask = ((mask & DBInfo.VIEWS) == DBInfo.VIEWS); if (tableMask || viewMask) { String[] tableTypes; if (tableMask && viewMask) { tableTypes = new String[] { "TABLE", "VIEW" }; } else if (tableMask) { tableTypes = new String[] { "TABLE" }; } else { tableTypes = new String[] { "VIEW" }; } String pattern = tableMask ? Globals.getTableNamePattern() : Globals.getViewNamePattern(); ResultSet allTables = dbmd.getTables(null, schemaName, pattern, tableTypes); try { while (allTables.next()) { String table_name = allTables.getString("TABLE_NAME"); String table_type = allTables.getString("TABLE_TYPE"); // discard recycle bin tables String ignoreTablePrefix = dialect.getRecycleBinTablePrefix(); if ((table_name == null) || ((ignoreTablePrefix != null) && table_name.startsWith(ignoreTablePrefix))) { continue; } if ((mask & DBInfo.INDEXES) == DBInfo.INDEXES) { ResultSet indexList = null; try { // Get a list of all the indexes for this table indexList = dbmd.getIndexInfo(null, schemaName, table_name, false, false); List<DBIndex> indexes = new ArrayList<DBIndex>(); while (indexList.next()) { String index_name = indexList.getString("INDEX_NAME"); String column_name = indexList.getString("COLUMN_NAME"); if (!index_name.equals("null")) { DBIndex index = new DBIndex(index_name, column_name); indexes.add(index); } } DBTable table = new DBTable(schemaName, table_name, table_type, indexes); tables.add(table); } catch (SQLException e) { throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } finally { closeResultSet(indexList); } } else { DBTable table = new DBTable(schemaName, table_name, table_type); tables.add(table); } } } catch (SQLException e) { throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } finally { closeResultSet(allTables); } } boolean procedureMask = ((mask & DBInfo.PROCEDURES) == DBInfo.PROCEDURES); if (procedureMask) { String pattern = Globals.getProcedureNamePattern(); if (pattern == null) { pattern = "%"; } ResultSet rs = dbmd.getProcedures(null, schemaName, pattern); try { while (rs.next()) { String spName = rs.getString("PROCEDURE_NAME"); int spType = rs.getInt("PROCEDURE_TYPE"); String catalog = rs.getString("PROCEDURE_CAT"); // System.out.println("Stored Procedure Name: " + spName); // if (spType == DatabaseMetaData.procedureReturnsResult) { // System.out.println("procedure Returns Result"); // } else if (spType == DatabaseMetaData.procedureNoResult) { // System.out.println("procedure No Result"); // } else { // System.out.println("procedure Result unknown"); // } procedures.add(new DBProcedure(schemaName, catalog, spName, spType)); } } catch (SQLException e) { throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } finally { closeResultSet(rs); } } } catch (SQLException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } return new DBInfo(info, tables, procedures, keywords); } public List<DBColumn> getColumns(String schema, String table) throws NextSqlException, MalformedTableNameException { Connection con; List<DBColumn> columns = new ArrayList<DBColumn>(); String schemaName; String escapedTableName; try { con = Globals.getConnection(); if (schema == null) { schemaName = Globals.getConnection().getMetaData().getUserName(); } else { schemaName = schema; } Dialect dialect = Globals.getDialect(); if (dialect.isKeyWord(table)) { escapedTableName = dialect.getEscapedKeyWord(table); } else { escapedTableName = table; } } catch (Exception e) { throw new NextSqlException("Could not retrieve connection.", e); } ResultSet rs = null; Statement stmt = null; List<String> keyColumns = new ArrayList<String>(); try { // primary keys DatabaseMetaData dbmd = con.getMetaData(); rs = dbmd.getPrimaryKeys(null, schemaName, table); while (rs.next()) { keyColumns.add(rs.getString("COLUMN_NAME")); } closeResultSet(rs); // foreign keys rs = dbmd.getImportedKeys(null, schemaName, table); List<String> foreignColumns = new ArrayList<String>(); HashMap<String, DBForeignColumnInfo> fkMap = new HashMap<String, DBForeignColumnInfo>(); while (rs.next()) { String fkSchema = rs.getString("FKTABLE_SCHEM"); String fkTable = rs.getString("FKTABLE_NAME"); String fkColumn = rs.getString("FKCOLUMN_NAME"); String pkSchema = rs.getString("PKTABLE_SCHEM"); String pkTable = rs.getString("PKTABLE_NAME"); String pkColumn = rs.getString("PKCOLUMN_NAME"); DBForeignColumnInfo fkInfo = new DBForeignColumnInfo(fkSchema, fkTable, fkColumn, pkSchema, pkTable, pkColumn); //System.out.println("fkInfo : " + fkInfo); foreignColumns.add(fkColumn); fkMap.put(fkColumn, fkInfo); } closeResultSet(rs); // column names with index rs = dbmd.getIndexInfo(null, schemaName, table, false, true); List<String> indexes = new ArrayList<String>(); while (rs.next()) { String indexName = rs.getString(9); if (indexName != null) { indexes.add(indexName); } } closeResultSet(rs); DataSource ds = DefaultDataSourceManager.getInstance().getConnectedDataSource(); String header = ""; stmt = con.createStatement(); try { // workaround if a table name contains spaces if (escapedTableName.indexOf(" ") != -1) { escapedTableName = "\"" + escapedTableName + "\""; } String prefix = ""; if (!NO_SCHEMA_NAME.equals(schemaName)) { prefix = schemaName; } if (prefix.indexOf(" ") != -1) { prefix = "\"" + prefix + "\""; } if (!"".equals(prefix)) { prefix = prefix + "."; } if (ds.getDriver().equals(CSVDialect.DRIVER_CLASS)) { header = (String) ds.getProperties().get("headerline"); if (header == null) { header = ""; } } if (header.isEmpty()) { String s = "SELECT * FROM " + prefix + escapedTableName + " WHERE 1 = 0"; LOG.info("getColumns[ " + s + "]"); rs = stmt.executeQuery(s); } } catch (SQLException e) { e.printStackTrace(); throw new MalformedTableNameException(e); } if (header.isEmpty()) { ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); for (int col = 1; col <= columnCount; col++) { String name = rsmd.getColumnLabel(col); int length = rsmd.getColumnDisplaySize(col); int precision = rsmd.getPrecision(col); int scale = rsmd.getScale(col); boolean isPrimaryKey = false; boolean isForeignKey = false; boolean isIndex = false; if (keyColumns.contains(name)) { isPrimaryKey = true; } DBForeignColumnInfo fkInfo = null; if (foreignColumns.contains(name)) { isForeignKey = true; fkInfo = fkMap.get(name); } if (indexes.contains(name)) { isIndex = true; } DBColumn column = new DBColumn(schemaName, table, name, rsmd.getColumnTypeName(col), isPrimaryKey, isForeignKey, isIndex, fkInfo, length, precision, scale); columns.add(column); } } else { String columnTypes = (String) ds.getProperties().get("columnTypes"); String[] names = header.split(","); String[] types = new String[names.length]; for (int i = 0; i < types.length; i++) { types[i] = "String"; } if ((columnTypes != null) && !columnTypes.isEmpty()) { types = columnTypes.split(","); } for (int i = 0; i < names.length; i++) { DBColumn column = new DBColumn(schemaName, table, names[i], types[i], false, false, false, null, 20, 0, 0); columns.add(column); } } } catch (SQLException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } finally { closeResultSet(rs); closeStatement(stmt); } return columns; } public List<DBProcedureColumn> getProcedureColumns(String schema, String catalog, String procedure) throws NextSqlException { Connection con; List<DBProcedureColumn> columns = new ArrayList<DBProcedureColumn>(); String schemaName; try { con = Globals.getConnection(); if (schema == null) { schemaName = Globals.getConnection().getMetaData().getUserName(); } else { schemaName = schema; } } catch (Exception e) { throw new NextSqlException("Could not retrieve connection.", e); } ResultSet rs = null; Statement stmt = null; try { DatabaseMetaData dbmd = con.getMetaData(); rs = dbmd.getProcedureColumns(catalog, schema, procedure, null); while (rs.next()) { String name = rs.getString("COLUMN_NAME"); int returnType = rs.getShort("COLUMN_TYPE"); String retType; if (DatabaseMetaData.procedureColumnIn == returnType) { retType = ProcUtil.IN; } else if (DatabaseMetaData.procedureColumnOut == returnType) { retType = ProcUtil.OUT; } else if (DatabaseMetaData.procedureColumnInOut == returnType) { retType = ProcUtil.INOUT; } else if (DatabaseMetaData.procedureColumnReturn == returnType) { retType = ProcUtil.VAL; } else { retType = ProcUtil.OTHER; } String dataType = rs.getString("TYPE_NAME"); int length = rs.getInt("LENGTH"); int precision = rs.getInt("PRECISION"); int scale = rs.getInt("SCALE"); DBProcedureColumn col = new DBProcedureColumn(schema, procedure, name, retType, dataType, length, precision, scale); columns.add(col); } return columns; } catch (SQLException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } finally { closeResultSet(rs); closeStatement(stmt); } } public boolean isValidProcedure(DBProcedure proc) { try { List<DBProcedureColumn> columns = getProcedureColumns(proc.getSchema(), proc.getCatalog(), proc.getName()); return isValidProcedure(columns); } catch (Exception e) { e.printStackTrace(); return false; } } public boolean isValidProcedure(List<DBProcedureColumn> columns) { try { Dialect dialect = Globals.getDialect(); int out = 0; for (DBProcedureColumn col : columns) { if (ProcUtil.IN.equals(col.getReturnType()) || ProcUtil.VAL.equals(col.getReturnType())) { } else if (ProcUtil.OUT.equals(col.getReturnType())) { if (dialect instanceof OracleDialect) { if (ProcUtil.REF_CURSOR.equals(col.getDataType())) { out++; } else { return false; } } else { return false; } } else { return false; } } if ((out != 1) && (dialect instanceof OracleDialect)) { return false; } return true; } catch (Exception e) { e.printStackTrace(); return false; } } public List<IdName> getColumnValues(String schema, String table, String columnName, String shownColumnName, byte orderBy) throws NextSqlException { return getColumnValues(Globals.getConnection(), schema, table, columnName, shownColumnName, orderBy); } public List<IdName> getColumnValues(Connection con, String schema, String table, String columnName, String shownColumnName, byte orderBy) throws NextSqlException { List<IdName> values = new ArrayList<IdName>(); try { values = ParameterUtil.getColumnValues(con, schema, table, columnName, shownColumnName, orderBy); } catch (SQLException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } catch (DialectException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("Dialect Exception: " + e.getMessage(), e); } return values; } public List<IdName> getValues(String select, boolean sort, byte orderBy) throws NextSqlException, InvalidSqlException { return getValues(Globals.getConnection(), select, sort, orderBy); } public List<IdName> getValues(Connection con, String select, boolean sort, byte orderBy) throws NextSqlException, InvalidSqlException { List<IdName> values = new ArrayList<IdName>(); try { values = ParameterUtil.getSelectValues(con, select, sort, orderBy); } catch (SQLException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("SQL Exception: " + e.getMessage(), e); } catch (DialectException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("Dialect Exception: " + e.getMessage(), e); } return values; } public ArrayList<Serializable> getDefaultSourceValues(Connection con, QueryParameter qp) throws NextSqlException { ArrayList<Serializable> result = new ArrayList<Serializable>(); try { result = ParameterUtil.getDefaultSourceValues(con, qp); } catch (Exception e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("Exception: " + e.getMessage(), e); } return result; } public DBColumn getColumn(String schema, String tableName, String columnName) throws NextSqlException { List<DBColumn> columns = null; try { columns = getColumns(schema, tableName); } catch (MalformedTableNameException e) { return null; } for (DBColumn column : columns) { if (column.getTable().equals(tableName) && column.getName().equals(columnName)) { return column; } } return null; } public String getUserSchema() throws NextSqlException { try { if (Globals.getConnection() == null) { return null; } return Globals.getConnection().getMetaData().getUserName(); } catch (Exception e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("Could not retrieve schema name.", e); } } public String getUserSchema(Connection connection) throws NextSqlException { try { return connection.getMetaData().getUserName(); } catch (Exception e) { LOG.error(e.getMessage(), e); e.printStackTrace(); throw new NextSqlException("Could not retrieve schema name.", e); } } public List<String> getSchemas() throws NextSqlException { return getSchemas(Globals.getConnection()); } public List<String> getSchemas(Connection connection) throws NextSqlException { List<String> schemas = new ArrayList<String>(); ResultSet rs = null; try { DatabaseMetaData dbmd = connection.getMetaData(); rs = dbmd.getSchemas(); while (rs.next()) { String schemaName = rs.getString("TABLE_SCHEM"); schemas.add(schemaName); } if (schemas.size() == 0) { schemas.add(NO_SCHEMA_NAME); } return schemas; } catch (Exception e) { LOG.error(e.getMessage(), e); e.printStackTrace(); //throw new NextSqlException("Could not retrieve schema names.", e); schemas.add("%"); return schemas; } finally { closeResultSet(rs); } } private void closeStatement(Statement stmt) { if (stmt != null) { try { stmt.close(); } catch (SQLException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); } } } private void closeResultSet(ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { LOG.error(e.getMessage(), e); e.printStackTrace(); } } } public DBColumn getPrimaryKeyColumn(DBColumn foreignKeyColumn) throws NextSqlException { DBColumn column = null; DBInfo info = getDBInfo(foreignKeyColumn.getSchema(), DBInfo.TABLES); List<DBTable> tables = info.getTables(); for (DBTable table : tables) { List<DBColumn> columns = null; try { columns = getColumns(foreignKeyColumn.getSchema(), table.getName()); } catch (MalformedTableNameException e) { // table with special characters LOG.error(e.getMessage(), e); e.printStackTrace(); continue; } for (DBColumn col : columns) { if (col.isForeignKey()) { DBForeignColumnInfo fkInfo = col.getFkInfo(); if (fkInfo.getFkTable().equals(foreignKeyColumn.getTable()) && fkInfo.getFkColumn().equals(foreignKeyColumn.getName())) { column = new DBColumn(foreignKeyColumn.getSchema(), fkInfo.getPkTable(), fkInfo.getPkColumn(), foreignKeyColumn.getType(), true, false, false, fkInfo, foreignKeyColumn.getLength(), foreignKeyColumn.getPrecision(), foreignKeyColumn.getScale()); return column; } } } } return column; } // here we get foreign keys just from the tables from the same schema !!! // getDBInfo(null, DBInfo.TABLES) may take too long (so let it be like this from now) public List<DBColumn> getForeignKeyColumns(DBColumn primaryKeyColumn) throws NextSqlException { List<DBColumn> list = new ArrayList<DBColumn>(); DBInfo info = getDBInfo(primaryKeyColumn.getSchema(), DBInfo.TABLES); List<DBTable> tables = info.getTables(); for (DBTable table : tables) { List<DBColumn> columns = null; try { columns = getColumns(table.getSchema(), table.getName()); } catch (MalformedTableNameException e) { // table with special characters LOG.error(e.getMessage(), e); e.printStackTrace(); continue; } for (DBColumn col : columns) { if (col.isForeignKey()) { DBForeignColumnInfo fkInfo = col.getFkInfo(); if (fkInfo.getPkTable().equals(primaryKeyColumn.getTable()) && fkInfo.getPkColumn().equals(primaryKeyColumn.getName())) { DBColumn column = new DBColumn(primaryKeyColumn.getSchema(), fkInfo.getFkTable(), fkInfo.getFkColumn(), primaryKeyColumn.getType(), true, false, false, fkInfo, primaryKeyColumn.getLength(), primaryKeyColumn.getPrecision(), primaryKeyColumn.getScale()); list.add(column); } } } } return list; } public String isValidSql(Report report) { return ReportUtil.isValidSqlWithMessage(Globals.getConnection(), report); } }