Java tutorial
package org.apache.torque.generator.source.jdbc; /* * 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. */ import java.io.File; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Set; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.apache.torque.generator.jdbc.SchemaType; import org.apache.torque.generator.source.SourceElement; import org.apache.torque.generator.source.SourceException; import org.apache.torque.generator.source.SourceImpl; /** * A source which reads the data from JDBC Metadata. * * @version $Id: JdbcMetadataSource.java 1331190 2012-04-27 02:41:35Z tfischer $ */ public class JdbcMetadataSource extends SourceImpl { /** * The position in table metadata containing the table name. */ private static final int TABLE_NAME_POS_IN_TABLE_METADATA = 3; /** * The position in column metadata containing the column name. */ private static final int COLUMN_NAME_POS_IN_COLUMN_METADATA = 4; /** * The position in column metadata containing the data type * (as SQL type from java.sql.Types). */ private static final int DATA_TYPE_POS_COLUMN_METADATA = 5; /** * The position in column metadata containing the column size. */ private static final int COLUMN_SIZE_POS_IN_COLUMN_METADATA = 7; /** * The position in column metadata containing the number * of fractional digits. */ private static final int DECIMAL_DIGITS_POS_IN_COLUMN_METADATA = 9; /** * The position in column metadata telling whether null is allowed as value * for that column. */ private static final int NULLABLE_POS_IN_COLUMN_METADATA = 11; /** * The position in column metadata containing the column's default value. */ private static final int DEFAULT_VALUE_POS_IN_COLUMN_METADATA = 13; /** * The position in primary key metadata containing the column name. */ private static final int COLUMN_NAME_POS_IN_PRIMARY_KEY_METADATA = 4; /** * The position in foreign key metadata containing the column name. */ private static final int TABLE_NAME_POS_IN_FOREIGN_KEY_METADATA = 3; /** * The position in foreign key metadata containing the foreign column name. */ private static final int FOREIGN_COLUMN_NAME_POS_IN_FOREIGN_KEY_METADATA = 4; /** * The position in foreign key metadata containing the localcolumn name. */ private static final int LOCAL_COLUMN_NAME_POS_IN_FOREIGN_KEY_METADATA = 8; /** * The position in foreign key metadata containing the foreign key name. */ private static final int FOREIGN_KEY_NAME_POS_IN_FOREIGN_KEY_METADATA = 12; /** The class log. */ private static Log log = LogFactory.getLog(JdbcMetadataSource.class); /** The fully qualified class name of the database driver. */ private String driver; /** The connection url to the database, */ private String url; /** The username to connect to the database. */ private String username; /** The password to connect to the database. */ private String password; /** Which database(mysql) or schema (oracle) should be read. */ private String schema; /** * Constructor. * * @param driver the database driver class, not null. * @param url the connection url, not null. * @param username the username of the database user. * @param password the password of the database user. * @param schema the schema to read. */ public JdbcMetadataSource(String driver, String url, String username, String password, String schema) { this.driver = driver; this.url = url; this.username = username; this.password = password; this.schema = schema; } @Override protected SourceElement createRootElement() throws SourceException { SourceElement rootElement = new SourceElement("database"); { try { Class.forName(driver); } catch (ClassNotFoundException e) { throw new SourceException("Could not find database driver class " + driver, e); } log.debug("DB driver " + driver + " loaded"); } Connection con = null; try { con = DriverManager.getConnection(url, username, password); log.debug("DB connection to database " + url + " established"); DatabaseMetaData dbMetaData = con.getMetaData(); List<String> tableList = getTableNames(dbMetaData, schema); for (int i = 0; i < tableList.size(); i++) { // Add Table. String tableName = (String) tableList.get(i); log.debug("Processing table: " + tableName); SourceElement table = new SourceElement("table"); rootElement.getChildren().add(table); table.setAttribute("name", tableName); List<ColumnMetadata> columns = getColumns(dbMetaData, tableName, schema); Set<String> primaryKeys = getPrimaryKeys(dbMetaData, tableName, schema); for (ColumnMetadata col : columns) { String name = col.getName(); Integer type = col.getSqlType(); int size = col.getSize().intValue(); int scale = col.getDecimalDigits().intValue(); Integer nullType = col.getNullType(); String defValue = col.getDefValue(); SourceElement column = new SourceElement("column"); column.setAttribute("name", name); column.setAttribute("type", SchemaType.getByJdbcType(type).toString()); if (size > 0 && (type.intValue() == Types.CHAR || type.intValue() == Types.VARCHAR || type.intValue() == Types.LONGVARCHAR || type.intValue() == Types.DECIMAL || type.intValue() == Types.NUMERIC)) { column.setAttribute("size", String.valueOf(size)); } if (scale > 0 && (type.intValue() == Types.DECIMAL || type.intValue() == Types.NUMERIC)) { column.setAttribute("scale", String.valueOf(scale)); } if (primaryKeys.contains(name)) { column.setAttribute("primaryKey", "true"); } else if (nullType.intValue() == 0) { column.setAttribute("required", "true"); } if (StringUtils.isNotEmpty(defValue)) { // trim out parens & quotes out of def value. // makes sense for MSSQL. not sure about others. if (defValue.startsWith("(") && defValue.endsWith(")")) { defValue = defValue.substring(1, defValue.length() - 1); } if (defValue.startsWith("'") && defValue.endsWith("'")) { defValue = defValue.substring(1, defValue.length() - 1); } column.setAttribute("default", defValue); } table.getChildren().add(column); } // Foreign keys for this table. Collection<ForeignKeyMetadata> forgnKeys = getForeignKeys(dbMetaData, tableName, schema); for (ForeignKeyMetadata foreignKeyMetadata : forgnKeys) { SourceElement fk = new SourceElement("foreign-key"); fk.setAttribute("foreignTable", foreignKeyMetadata.getReferencedTable()); for (int m = 0; m < foreignKeyMetadata.getLocalColumns().size(); m++) { SourceElement ref = new SourceElement("reference"); ref.setAttribute("local", foreignKeyMetadata.getLocalColumns().get(m)); ref.setAttribute("foreign", foreignKeyMetadata.getForeignColumns().get(m)); fk.getChildren().add(ref); } table.getChildren().add(fk); } } } catch (SQLException e) { throw new SourceException("Could not retrieve JDBC Metadata from url " + url, e); } finally { if (con != null) { try { con.close(); } catch (SQLException e) { log.warn("Could not close database connection", e); } con = null; } } return rootElement; } public String getDescription() { return "JdbcMetadataSource using url " + url; } public File getSourceFile() { return null; } /** * Get all the table names in the current database that are not * system tables. * * @param dbMeta JDBC database metadata. * @return The list of all the tables in a database. * @throws SQLException */ List<String> getTableNames(DatabaseMetaData dbMeta, String dbSchema) throws SQLException { log.debug("Getting table list..."); List<String> tables = new ArrayList<String>(); ResultSet tableNames = null; // these are the entity types we want from the database String[] types = { "TABLE", "VIEW" }; try { tableNames = dbMeta.getTables(null, dbSchema, "%", types); while (tableNames.next()) { String name = tableNames.getString(TABLE_NAME_POS_IN_TABLE_METADATA); tables.add(name); } } finally { if (tableNames != null) { tableNames.close(); } } return tables; } /** * Retrieves all the column names and types for a given table from * JDBC metadata. * * @param dbMeta JDBC metadata. * @param tableName Table from which to retrieve column information. * * @return The list of columns in <code>tableName</code>. * * @throws SQLException if an sql error occurs during information retrieval. */ List<ColumnMetadata> getColumns(DatabaseMetaData dbMeta, String tableName, String dbSchema) throws SQLException { List<ColumnMetadata> columns = new ArrayList<ColumnMetadata>(); ResultSet columnSet = null; try { columnSet = dbMeta.getColumns(null, dbSchema, tableName, null); while (columnSet.next()) { String name = columnSet.getString(COLUMN_NAME_POS_IN_COLUMN_METADATA); Integer sqlType = Integer.valueOf(columnSet.getString(DATA_TYPE_POS_COLUMN_METADATA)); Integer size = Integer.valueOf(columnSet.getInt(COLUMN_SIZE_POS_IN_COLUMN_METADATA)); Integer decimalDigits = Integer.valueOf(columnSet.getInt(DECIMAL_DIGITS_POS_IN_COLUMN_METADATA)); Integer nullType = Integer.valueOf(columnSet.getInt(NULLABLE_POS_IN_COLUMN_METADATA)); String defValue = columnSet.getString(DEFAULT_VALUE_POS_IN_COLUMN_METADATA); ColumnMetadata column = new ColumnMetadata(name, sqlType, size, nullType, defValue, decimalDigits); columns.add(column); } } finally { if (columnSet != null) { columnSet.close(); } } return columns; } /** * Retrieves a list of the columns composing the primary key for a given * table. * * @param dbMeta JDBC metadata. * @param tableName Table from which to retrieve PK information. * @return A list of the primary key parts for <code>tableName</code>. * @throws SQLException */ Set<String> getPrimaryKeys(DatabaseMetaData dbMeta, String tableName, String schemaName) throws SQLException { Set<String> pk = new HashSet<String>(); ResultSet parts = null; try { parts = dbMeta.getPrimaryKeys(null, schemaName, tableName); while (parts.next()) { pk.add(parts.getString(COLUMN_NAME_POS_IN_PRIMARY_KEY_METADATA)); } } finally { if (parts != null) { parts.close(); } } return pk; } /** * Retrieves a list of foreign key columns for a given table. * * @param dbMeta JDBC metadata. * @param tableName Table from which to retrieve FK information. * @return A list of foreign keys in <code>tableName</code>. * @throws SQLException */ Collection<ForeignKeyMetadata> getForeignKeys(DatabaseMetaData dbMeta, String tableName, String schemaName) throws SQLException { Map<String, ForeignKeyMetadata> foreignKeys = new HashMap<String, ForeignKeyMetadata>(); ResultSet resultSet = null; try { resultSet = dbMeta.getImportedKeys(null, schemaName, tableName); while (resultSet.next()) { String refTableName = resultSet.getString(TABLE_NAME_POS_IN_FOREIGN_KEY_METADATA); String fkName = resultSet.getString(FOREIGN_KEY_NAME_POS_IN_FOREIGN_KEY_METADATA); // if FK has no name - make it up (use tablename instead) if (fkName == null) { fkName = refTableName; } ForeignKeyMetadata fk = foreignKeys.get(fkName); if (fk == null) { fk = new ForeignKeyMetadata(); fk.setReferencedTable(refTableName); fk.setForeignKeyName(fkName); foreignKeys.put(fkName, fk); } fk.getLocalColumns().add(resultSet.getString(LOCAL_COLUMN_NAME_POS_IN_FOREIGN_KEY_METADATA)); fk.getForeignColumns().add(resultSet.getString(FOREIGN_COLUMN_NAME_POS_IN_FOREIGN_KEY_METADATA)); } } catch (SQLException e) { // this seems to be happening in some db drivers (sybase) // when retrieving foreign keys from views. log.warn("WARN: Could not read foreign keys for Table " + tableName + " : " + e.getMessage()); } finally { if (resultSet != null) { resultSet.close(); } } return foreignKeys.values(); } }