Example usage for java.sql DatabaseMetaData getTables

List of usage examples for java.sql DatabaseMetaData getTables

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. getTables

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData getTables.

Prototype

ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String types[])
        throws SQLException;

Source Link

Document

Retrieves a description of the tables available in the given catalog.

Usage

From source file:com.alifi.jgenerator.spring.SpringGeneratorFactory.java

public static void main(String[] args) {
    @SuppressWarnings("unused")
    ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("META-INF/SpringContext.xml");

    for (DataSourceMap m : DataSourceUtil.getDataSourceMap()) {

        DataSource ds = m.getDataSource();
        try {/*w w w .j  a  va2 s  .co  m*/
            Connection con = ds.getConnection();
            DatabaseMetaData dmd = con.getMetaData();
            String catalog = con.getCatalog();
            String schemaPattern = dmd.getSchemaTerm();
            System.out.println("Catalog:" + con.getCatalog() + " schemaPattern:" + schemaPattern);
            ResultSet rs = dmd.getTables(catalog, schemaPattern, "time_zone_transition", null);

            Statement s = null;
            ResultSet xrs = null;
            try {
                s = con.createStatement();
                xrs = s.executeQuery("select * from time_zone_transition");
                if (xrs.next()) {
                    p("xxxxxxxxxxxxxxxxxxxxxxxx:" + xrs.getString(1));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
            }

            while (rs.next()) {
                p("-----------------------");
                p("TABLE_CAT  :" + rs.getString("TABLE_CAT"));
                p("TABLE_SCHEM:" + rs.getString("TABLE_SCHEM"));
                p("TABLE_NAME :" + rs.getString("TABLE_NAME"));
                p("TABLE_TYPE :" + rs.getString("TABLE_TYPE"));
                p("REMARKS:   " + rs.getString("REMARKS"));

                p("11111111111111111:" + rs.getMetaData().getColumnClassName(1));
                // 
                ResultSet pkeyRs = dmd.getPrimaryKeys(catalog, schemaPattern, rs.getString("TABLE_NAME"));
                while (pkeyRs.next()) {
                    p("M-------------M");
                    p("------COLUMN_NAME:" + pkeyRs.getString("COLUMN_NAME"));
                    p("------KEY_SEQ:" + pkeyRs.getString("KEY_SEQ"));
                    p("------PK_NAME:" + pkeyRs.getString("PK_NAME"));
                }
                // 
                ResultSet rss = dmd.getColumns(catalog, schemaPattern, rs.getString("TABLE_NAME"), null);
                int cCount = rss.getMetaData().getColumnCount();
                for (int i = 1; i <= cCount; i++) {
                    p("-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=");
                    p("       getColumnClassName:" + rss.getMetaData().getColumnClassName(i));
                    p("       getColumnLabel:" + rss.getMetaData().getColumnLabel(i));
                    p("       getColumnName:" + rss.getMetaData().getColumnName(i));
                    p("       getColumnTypeName:" + rss.getMetaData().getColumnTypeName(i));
                    p("       getColumnType:" + ColumnTypes.getType(rss.getMetaData().getColumnType(i)));
                }
            }
            rs = dmd.getTableTypes();

            while (rs.next()) {
                p("=========================");
                p("TABLE_TYPE: " + rs.getString("TABLE_TYPE"));
            }
            // ResultSetMetaData rsmd = rs.getMetaData();

            // int numberOfColumns = rsmd.getColumnCount();
            // for (int i = 1; i <= numberOfColumns; i++)
            // p(rsmd.getColumnName(i));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        p(m.toString());
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    String url = "jdbc:odbc:databaseName";
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String user = "guest";
    String password = "guest";

    try {//  w ww . ja  va2s  .  c o  m
        Class.forName(driver);
        Connection conn = DriverManager.getConnection(url, user, password);

        // Get the MetaData
        DatabaseMetaData metaData = conn.getMetaData();

        // Get driver information
        System.out.println("Driver Informaion");
        System.out.println(metaData.getDriverName());
        System.out.println(metaData.getDriverVersion());
        // Get schema information
        System.out.println("Schemas");
        ResultSet schemas = metaData.getSchemas();
        while (schemas.next()) {
            System.out.println(schemas.getString(1));
        }
        // Get table information
        System.out.println("Tables");
        ResultSet tables = metaData.getTables("", "", "", null);
        while (tables.next()) {
            System.out.println(tables.getString(3));
        }
        conn.close();
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}

From source file:GetDBInfo.java

public static void main(String[] args) {
    Connection c = null; // The JDBC connection to the database server
    try {// w  ww. j  a  v  a2  s  .  c  o  m
        // Look for the properties file DB.props in the same directory as
        // this program. It will contain default values for the various
        // parameters needed to connect to a database
        Properties p = new Properties();
        try {
            p.load(GetDBInfo.class.getResourceAsStream("DB.props"));
        } catch (Exception e) {
        }

        // Get default values from the properties file
        String driver = p.getProperty("driver"); // Driver class name
        String server = p.getProperty("server", ""); // JDBC URL for server
        String user = p.getProperty("user", ""); // db user name
        String password = p.getProperty("password", ""); // db password

        // These variables don't have defaults
        String database = null; // The db name (appended to server URL)
        String table = null; // The optional name of a table in the db

        // Parse the command-line args to override the default values above
        for (int i = 0; i < args.length; i++) {
            if (args[i].equals("-d"))
                driver = args[++i]; //-d <driver>
            else if (args[i].equals("-s"))
                server = args[++i];//-s <server>
            else if (args[i].equals("-u"))
                user = args[++i]; //-u <user>
            else if (args[i].equals("-p"))
                password = args[++i];
            else if (database == null)
                database = args[i]; // <dbname>
            else if (table == null)
                table = args[i]; // <table>
            else
                throw new IllegalArgumentException("Unknown argument: " + args[i]);
        }

        // Make sure that at least a server or a database were specified.
        // If not, we have no idea what to connect to, and cannot continue.
        if ((server.length() == 0) && (database.length() == 0))
            throw new IllegalArgumentException("No database specified.");

        // Load the db driver, if any was specified.
        if (driver != null)
            Class.forName(driver);

        // Now attempt to open a connection to the specified database on
        // the specified server, using the specified name and password
        c = DriverManager.getConnection(server + database, user, password);

        // Get the DatabaseMetaData object for the connection. This is the
        // object that will return us all the data we're interested in here
        DatabaseMetaData md = c.getMetaData();

        // Display information about the server, the driver, etc.
        System.out.println("DBMS: " + md.getDatabaseProductName() + " " + md.getDatabaseProductVersion());
        System.out.println("JDBC Driver: " + md.getDriverName() + " " + md.getDriverVersion());
        System.out.println("Database: " + md.getURL());
        System.out.println("User: " + md.getUserName());

        // Now, if the user did not specify a table, then display a list of
        // all tables defined in the named database. Note that tables are
        // returned in a ResultSet, just like query results are.
        if (table == null) {
            System.out.println("Tables:");
            ResultSet r = md.getTables("", "", "%", null);
            while (r.next())
                System.out.println("\t" + r.getString(3));
        }

        // Otherwise, list all columns of the specified table.
        // Again, information about the columns is returned in a ResultSet
        else {
            System.out.println("Columns of " + table + ": ");
            ResultSet r = md.getColumns("", "", table, "%");
            while (r.next())
                System.out.println("\t" + r.getString(4) + " : " + r.getString(6));
        }
    }
    // Print an error message if anything goes wrong.
    catch (Exception e) {
        System.err.println(e);
        if (e instanceof SQLException)
            System.err.println(((SQLException) e).getSQLState());
        System.err.println("Usage: java GetDBInfo [-d <driver] " + "[-s <dbserver>]\n"
                + "\t[-u <username>] [-p <password>] <dbname>");
    }
    // Always remember to close the Connection object when we're done!
    finally {
        try {
            c.close();
        } catch (Exception e) {
        }
    }
}

From source file:Main.java

public static void main(String[] argv) {
    Connection connection = null;
    Statement statement;//  w  w  w. j av a  2  s  . com
    ResultSet rs;
    try {
        Context ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/AccountsDB");
        connection = ds.getConnection();

        DatabaseMetaData md = connection.getMetaData();
        statement = connection.createStatement();

        System.out.println("getURL() - " + md.getURL());
        System.out.println("getUserName() - " + md.getUserName());
        System.out.println("getDatabaseProductVersion - " + md.getDatabaseProductVersion());
        System.out.println("getDriverMajorVersion - " + md.getDriverMajorVersion());
        System.out.println("getDriverMinorVersion - " + md.getDriverMinorVersion());
        System.out.println("nullAreSortedHigh - " + md.nullsAreSortedHigh());

        System.out.println("<H1>Feature Support</H1>");
        System.out.println(
                "supportsAlterTableWithDropColumn - " + md.supportsAlterTableWithDropColumn() + "<BR>");
        System.out.println("supportsBatchUpdates - " + md.supportsBatchUpdates());
        System.out.println("supportsTableCorrelationNames - " + md.supportsTableCorrelationNames());
        System.out.println("supportsPositionedDelete - " + md.supportsPositionedDelete());
        System.out.println("supportsFullOuterJoins - " + md.supportsFullOuterJoins());
        System.out.println("supportsStoredProcedures - " + md.supportsStoredProcedures());
        System.out.println("supportsMixedCaseQuotedIdentifiers - " + md.supportsMixedCaseQuotedIdentifiers());
        System.out.println("supportsANSI92EntryLevelSQL - " + md.supportsANSI92EntryLevelSQL());
        System.out.println("supportsCoreSQLGrammar - " + md.supportsCoreSQLGrammar());
        System.out.println("getMaxRowSize - " + md.getMaxRowSize());
        System.out.println("getMaxStatementLength - " + md.getMaxStatementLength());
        System.out.println("getMaxTablesInSelect - " + md.getMaxTablesInSelect());
        System.out.println("getMaxConnections - " + md.getMaxConnections());
        System.out.println("getMaxCharLiteralLength - " + md.getMaxCharLiteralLength());

        System.out.println("getTableTypes()");
        rs = md.getTableTypes();
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
        System.out.println("getTables()");
        rs = md.getTables("accounts", "", "%", new String[0]);
        while (rs.next()) {
            System.out.println(rs.getString("TABLE_NAME"));
        }
        System.out.println("Transaction Support");
        System.out.println("getDefaultTransactionIsolation() - " + md.getDefaultTransactionIsolation());
        System.out
                .println("dataDefinitionIgnoredInTransactions() - " + md.dataDefinitionIgnoredInTransactions());

        System.out.println("General Source Information");
        System.out.println("getMaxTablesInSelect - " + md.getMaxTablesInSelect());
        System.out.println("getMaxColumnsInTable - " + md.getMaxColumnsInTable());
        System.out.println("getTimeDateFunctions - " + md.getTimeDateFunctions());
        System.out.println("supportsCoreSQLGrammar - " + md.supportsCoreSQLGrammar());

        System.out.println("getTypeInfo()");
        rs = md.getTypeInfo();
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:Main.java

public static void getTables(Connection conn) throws Exception {
    String TABLE_NAME = "TABLE_NAME";
    String TABLE_SCHEMA = "TABLE_SCHEM";
    String[] TABLE_AND_VIEW_TYPES = { "TABLE", "VIEW" };
    DatabaseMetaData dbmd = conn.getMetaData();

    ResultSet tables = dbmd.getTables(null, null, null, TABLE_AND_VIEW_TYPES);
    while (tables.next()) {
        System.out.println(tables.getString(TABLE_NAME));
        System.out.println(tables.getString(TABLE_SCHEMA));
    }/*from  ww  w. j a v  a  2s. com*/
}

From source file:Main.java

public static void getTables(Connection conn) throws Exception {
    String TABLE_NAME = "TABLE_NAME";
    String TABLE_SCHEMA = "TABLE_SCHEM";
    String[] TABLE_TYPES = { "TABLE" };
    DatabaseMetaData dbmd = conn.getMetaData();

    ResultSet tables = dbmd.getTables(null, null, null, TABLE_TYPES);
    while (tables.next()) {
        System.out.println(tables.getString(TABLE_NAME));
        System.out.println(tables.getString(TABLE_SCHEMA));
    }//from w  ww . j av a  2s  . c  o  m
}

From source file:org.magdaaproject.utils.DatabaseUtils.java

/**
 * check to see if a table already exists in the database
 * //from  w  ww .j a va2s.  c om
 * @param connection a valid connection to the database
 * @param tableName the name of the table to check
 * @return true if the table exists, false if it does not
 * @throws SQLException if something bad happens
 */
public static boolean doesTableExist(Connection connection, String tableName) throws SQLException {

    boolean tableExists = false;

    DatabaseMetaData metaData = connection.getMetaData();
    ResultSet tablesList = metaData.getTables(null, null, tableName, null);

    if (tablesList.next()) {
        tableExists = true;
    }

    tablesList.close();
    tablesList = null;

    metaData = null;

    return tableExists;
}

From source file:com.digitalgeneralists.assurance.Application.java

static void installDb(InputStream propertiesFileStream, InputStream dbScriptStream)
        throws IOException, SQLException {
    Logger logger = Logger.getLogger(Application.class);

    Connection dbConnection = null;
    ResultSet rs = null;/* w w  w .ja va  2s  .  c o  m*/
    try {
        Properties properties = new Properties();

        if (propertiesFileStream != null) {
            properties.load(propertiesFileStream);
        } else {
            throw new FileNotFoundException("The database properties file could not be loaded.");
        }
        String dbUrl = (String) properties.get("jdbc.url");
        String dbUser = (String) properties.get("jdbc.username");
        String dbPassword = (String) properties.get("jdbc.password");

        dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);

        ArrayList<String> listOfDatabases = new ArrayList<String>();
        DatabaseMetaData meta = dbConnection.getMetaData();
        String[] tableTypes = { "TABLE" };
        rs = meta.getTables(null, null, Application.verificationTableName, tableTypes);
        while (rs.next()) {
            String databaseName = rs.getString("TABLE_NAME");
            listOfDatabases.add(databaseName.toUpperCase());
        }
        if (listOfDatabases.contains(Application.verificationTableName)) {
            logger.info("Database already exists");
        } else {
            ScriptRunner runner = new ScriptRunner(dbConnection, true, true);

            Reader dbScript = new InputStreamReader(dbScriptStream);
            runner.runScript(dbScript);

            logger.info("Database is created");
        }
    } finally {
        if (rs != null) {
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
                // The ship is going down. Not much we can do.
                logger.fatal(e);
            }
        }
        if (dbConnection != null) {
            dbConnection.close();
            dbConnection = null;
        }
    }

    logger = null;
}

From source file:org.culturegraph.mf.sql.util.JdbcUtil.java

public static ResultSet getTableMetadata(Connection jdbcConnection, String tableNamePattern, String schema,
        String catalog, boolean isQuoted) {
    ResultSet rs = null;/*  ww  w .j  a v  a 2  s .c  o  m*/
    try {
        DatabaseMetaData meta = jdbcConnection.getMetaData();
        if ((isQuoted && meta.storesMixedCaseQuotedIdentifiers())) {
            rs = meta.getTables(catalog, schema, tableNamePattern, TYPES);
        } else if ((isQuoted && meta.storesUpperCaseQuotedIdentifiers())
                || (!isQuoted && meta.storesUpperCaseIdentifiers())) {
            rs = meta.getTables(StringHelper.toUpperCase(catalog), StringHelper.toUpperCase(schema),
                    StringHelper.toUpperCase(tableNamePattern), TYPES);
        } else if ((isQuoted && meta.storesLowerCaseQuotedIdentifiers())
                || (!isQuoted && meta.storesLowerCaseIdentifiers())) {
            rs = meta.getTables(StringHelper.toLowerCase(catalog), StringHelper.toLowerCase(schema),
                    StringHelper.toLowerCase(tableNamePattern), TYPES);
        } else {
            rs = meta.getTables(catalog, schema, tableNamePattern, TYPES);
        }
        return rs;
        // while (rs.next()) {
        // String tableName = rs.getString("TABLE_NAME");
        // System.out.println("table = " + tableName);
        // }

    } catch (SQLException sqlException) {
        sqlException.printStackTrace();
    } finally {
        try {
            DbUtils.close(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return null;
}

From source file:com.netradius.hibernate.support.HibernateUtil.java

private static List<String[]> getTables() {
    final List<String[]> rows = new ArrayList<String[]>();
    rows.add(new String[] { "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE", "REMARKS", "TYPE_CAT",
            "TYPE_SCHEM", "TYPE_NAME", "SELF_REFERENCING_COL_NAME", "REF_GENERATION " });
    Connection con = null;//w  ww . ja v  a2 s .  c  o  m
    ResultSet rs = null;
    try {
        con = getConnection();
        final DatabaseMetaData md = con.getMetaData();
        rs = md.getTables(null, null, "%", null);
        while (rs.next()) {
            List<String> s = new ArrayList<String>(10);
            for (int i = 10; i > 0; i--)
                s.add(rs.getString(i));
            rows.add(s.toArray(new String[10]));
        }
    } catch (SQLException x) {
        log.error("Error listing tables: " + x.getMessage(), x);
    } finally {
        close(con, null, rs);
    }
    return rows;
}