Example usage for java.sql Connection getMetaData

List of usage examples for java.sql Connection getMetaData

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. getMetaData

Introduction

In this page you can find the example usage for java.sql Connection getMetaData.

Prototype

DatabaseMetaData getMetaData() throws SQLException;

Source Link

Document

Retrieves a DatabaseMetaData object that contains metadata about the database to which this Connection object represents a connection.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getHSQLConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar);");
    st.executeUpdate("create view surveyView as (select * from survey);");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet rs = null;//from   ww w . j  a v  a2  s  .c  om
    DatabaseMetaData meta = conn.getMetaData();
    rs = meta.getTables(null, null, null, new String[] { "VIEW" });

    while (rs.next()) {
        String tableName = rs.getString("TABLE_NAME");
        System.out.println("tableName=" + tableName);
    }

    st.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet rsColumns = null;/*from   w w  w  . j a v a  2s.  com*/
    DatabaseMetaData meta = conn.getMetaData();
    rsColumns = meta.getColumns(null, null, "survey", null);
    while (rsColumns.next()) {
        String columnName = rsColumns.getString("COLUMN_NAME");
        System.out.println("column name=" + columnName);
        String columnType = rsColumns.getString("TYPE_NAME");
        System.out.println("type:" + columnType);
        int size = rsColumns.getInt("COLUMN_SIZE");
        System.out.println("size:" + size);
        int nullable = rsColumns.getInt("NULLABLE");
        if (nullable == DatabaseMetaData.columnNullable) {
            System.out.println("nullable true");
        } else {
            System.out.println("nullable false");
        }
        int position = rsColumns.getInt("ORDINAL_POSITION");
        System.out.println("position:" + position);

    }

    st.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    DatabaseMetaData dbMetaData = conn.getMetaData();
    ResultSet rs = dbMetaData.getProcedureColumns(conn.getCatalog(), null, "procedureNamePattern",
            "columnNamePattern");

    while (rs.next()) {
        // get stored procedure metadata
        String procedureCatalog = rs.getString(1);
        String procedureSchema = rs.getString(2);
        String procedureName = rs.getString(3);
        String columnName = rs.getString(4);
        short columnReturn = rs.getShort(5);
        int columnDataType = rs.getInt(6);
        String columnReturnTypeName = rs.getString(7);
        int columnPrecision = rs.getInt(8);
        int columnByteLength = rs.getInt(9);
        short columnScale = rs.getShort(10);
        short columnRadix = rs.getShort(11);
        short columnNullable = rs.getShort(12);
        String columnRemarks = rs.getString(13);

        System.out.println("stored Procedure name=" + procedureName);
        System.out.println("procedureCatalog=" + procedureCatalog);
        System.out.println("procedureSchema=" + procedureSchema);
        System.out.println("procedureName=" + procedureName);
        System.out.println("columnName=" + columnName);
        System.out.println("columnReturn=" + columnReturn);
        System.out.println("columnDataType=" + columnDataType);
        System.out.println("columnReturnTypeName=" + columnReturnTypeName);
        System.out.println("columnPrecision=" + columnPrecision);
        System.out.println("columnByteLength=" + columnByteLength);
        System.out.println("columnScale=" + columnScale);
        System.out.println("columnRadix=" + columnRadix);
        System.out.println("columnNullable=" + columnNullable);
        System.out.println("columnRemarks=" + columnRemarks);
    }/* ww w.ja v  a 2  s.  co  m*/

    st.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet privileges = null;//ww w  . j  a  v a 2  s .com
    DatabaseMetaData meta = conn.getMetaData();
    // The '_' character represents any single character.
    // The '%' character represents any sequence of zero
    // or more characters.
    privileges = meta.getTablePrivileges(conn.getCatalog(), "%", "survey");
    while (privileges.next()) {
        String catalog = privileges.getString("TABLE_CAT");
        String schema = privileges.getString("TABLE_SCHEM");
        String tableName = privileges.getString("TABLE_NAME");
        String privilege = privileges.getString("PRIVILEGE");
        String grantor = privileges.getString("GRANTOR");
        String grantee = privileges.getString("GRANTEE");
        String isGrantable = privileges.getString("IS_GRANTABLE");

        System.out.println("table name:" + tableName);
        System.out.println("catalog:" + catalog);
        System.out.println("schema:" + schema);
        System.out.println("privilege:" + privilege);
        System.out.println("grantor:" + grantor);
        System.out.println("isGrantable:" + isGrantable);
        System.out.println("grantee:" + grantee);
    }

    st.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet indexInformation = null;
    DatabaseMetaData meta = conn.getMetaData();

    // The '_' character represents any single character.
    // The '%' character represents any sequence of zero
    // or more characters.
    indexInformation = meta.getIndexInfo(conn.getCatalog(), null, "survey", true, true);
    while (indexInformation.next()) {
        short type = indexInformation.getShort("TYPE");
        switch (type) {
        case DatabaseMetaData.tableIndexClustered:
            System.out.println("tableIndexClustered");
        case DatabaseMetaData.tableIndexHashed:
            System.out.println("tableIndexHashed");
        case DatabaseMetaData.tableIndexOther:
            System.out.println("tableIndexOther");
        case DatabaseMetaData.tableIndexStatistic:
            System.out.println("tableIndexStatistic");
        default:/*from  www .j  av  a 2s .  co m*/
            System.out.println("tableIndexOther");
        }

    }

    st.close();
    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    //    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet privileges = null;//from   w ww.  j a  v a  2 s .  com
    DatabaseMetaData meta = conn.getMetaData();
    // The '_' character represents any single character.
    // The '%' character represents any sequence of zero
    // or more characters.
    privileges = meta.getColumnPrivileges(conn.getCatalog(), null, "survey", "%");
    while (privileges.next()) {

        String catalog = privileges.getString("TABLE_CAT");
        String schema = privileges.getString("TABLE_SCHEM");
        String tableName = privileges.getString("TABLE_NAME");
        String dbColumn = privileges.getString("COLUMN_NAME");
        String privilege = privileges.getString("PRIVILEGE");
        String grantor = privileges.getString("GRANTOR");
        String grantee = privileges.getString("GRANTEE");
        String isGrantable = privileges.getString("IS_GRANTABLE");

        System.out.println("table name:" + tableName);
        System.out.println("catalog:" + catalog);
        System.out.println("column:" + dbColumn);
        System.out.println("schema:" + schema);
        System.out.println("privilege:" + privilege);
        System.out.println("grantor:" + grantor);
        System.out.println("isGrantable:" + isGrantable);
        System.out.println("grantee:" + grantee);
    }

    st.close();
    conn.close();
}

From source file:TypeInfo.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;
    DatabaseMetaData dbmd;//from w  w  w .  ja  v a2 s .  c om

    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {
        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        dbmd = con.getMetaData();

        ResultSet rs = dbmd.getTypeInfo();
        while (rs.next()) {
            String typeName = rs.getString("TYPE_NAME");
            short dataType = rs.getShort("DATA_TYPE");
            String createParams = rs.getString("CREATE_PARAMS");
            int nullable = rs.getInt("NULLABLE");
            boolean caseSensitive = rs.getBoolean("CASE_SENSITIVE");
            System.out.println("DBMS type " + typeName + ":");
            System.out.println("     java.sql.Types:  " + dataType);
            System.out.print("     parameters used to create: ");
            System.out.println(createParams);
            System.out.println("     nullable?:  " + nullable);
            System.out.print("     case sensitive?:  ");
            System.out.println(caseSensitive);
            System.out.println("");

        }

        con.close();

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
    }
}

From source file:Main.java

public static void main(String[] argv) throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", "root", "root");

    Statement st = con.createStatement();
    ResultSet rs = st.executeQuery("DESCRIBE myTable");
    ResultSetMetaData md = rs.getMetaData();
    int col = md.getColumnCount();
    for (int i = 1; i <= col; i++) {
        String col_name = md.getColumnName(i);
        System.out.println(col_name);
    }// www  .ja v  a 2 s  .  c om
    DatabaseMetaData dbm = con.getMetaData();
    ResultSet rs1 = dbm.getColumns(null, "%", "myTable", "%");
    while (rs1.next()) {
        String col_name = rs1.getString("COLUMN_NAME");
        String data_type = rs1.getString("TYPE_NAME");
        int data_size = rs1.getInt("COLUMN_SIZE");
        int nullable = rs1.getInt("NULLABLE");
        System.out.println(col_name + " " + data_type + "(" + data_size + ")");
        if (nullable == 1) {
            System.out.println("YES");
        } else {
            System.out.println("NO");
        }
    }
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet indexInformation = null;
    DatabaseMetaData meta = conn.getMetaData();

    // The '_' character represents any single character.
    // The '%' character represents any sequence of zero
    // or more characters.
    indexInformation = meta.getIndexInfo(conn.getCatalog(), null, "survey", true, true);
    while (indexInformation.next()) {
        String dbCatalog = indexInformation.getString("TABLE_CATALOG");
        String dbSchema = indexInformation.getString("TABLE_SCHEMA");
        String dbTableName = indexInformation.getString("TABLE_NAME");
        boolean dbNoneUnique = indexInformation.getBoolean("NON_UNIQUE");
        String dbIndexQualifier = indexInformation.getString("INDEX_QUALIFIER");
        String dbIndexName = indexInformation.getString("INDEX_NAME");
        short dbType = indexInformation.getShort("TYPE");
        short dbOrdinalPosition = indexInformation.getShort("ORDINAL_POSITION");
        String dbColumnName = indexInformation.getString("COLUMN_NAME");
        String dbAscOrDesc = indexInformation.getString("ASC_OR_DESC");
        int dbCardinality = indexInformation.getInt("CARDINALITY");
        int dbPages = indexInformation.getInt("PAGES");
        String dbFilterCondition = indexInformation.getString("FILTER_CONDITION");

        System.out.println("index name=" + dbIndexName);
        System.out.println("table=" + dbTableName);
        System.out.println("column=" + dbColumnName);
        System.out.println("catalog=" + dbCatalog);
        System.out.println("schema=" + dbSchema);
        System.out.println("nonUnique=" + dbNoneUnique);
        System.out.println("indexQualifier=" + dbIndexQualifier);
        System.out.println("type=" + dbType);
        System.out.println("ordinalPosition=" + dbOrdinalPosition);
        System.out.println("ascendingOrDescending=" + dbAscOrDesc);
        System.out.println("cardinality=" + dbCardinality);
        System.out.println("pages=" + dbPages);
        System.out.println("filterCondition=" + dbFilterCondition);
    }//from ww  w . j a  v  a 2s .co  m

    st.close();
    conn.close();
}

From source file:com.ingby.socbox.bischeck.test.JDBCtest.java

static public void main(String[] args)
        throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {

    CommandLineParser parser = new GnuParser();
    CommandLine line = null;//from   ww w  .jav  a 2 s .co m

    // create the Options
    Options options = new Options();
    options.addOption("u", "usage", false, "show usage.");
    options.addOption("c", "connection", true, "the connection url");
    options.addOption("s", "sql", true, "the sql statement to run");
    options.addOption("m", "meta", true, "get the table meta data");
    options.addOption("C", "columns", true, "the number of columns to display, default 1");
    options.addOption("d", "driver", true, "the driver class");
    options.addOption("v", "verbose", false, "verbose outbout");

    try {
        // parse the command line arguments
        line = parser.parse(options, args);
    } catch (org.apache.commons.cli.ParseException e) {
        System.out.println("Command parse error:" + e.getMessage());
        HelpFormatter formatter = new HelpFormatter();
        formatter.printHelp("JDBCtest", options);
        Util.ShellExit(1);
    }

    if (line.hasOption("verbose")) {
        verbose = true;
    }

    if (line.hasOption("usage")) {
        HelpFormatter formatter = new HelpFormatter();
        formatter.printHelp("Bischeck", options);
        Util.ShellExit(0);
    }

    String driverclassname = null;
    if (!line.hasOption("driver")) {
        System.out.println("Driver class must be set");
        Util.ShellExit(1);
    } else {
        driverclassname = line.getOptionValue("driver");
        outputln("DriverClass: " + driverclassname);
    }

    String connectionname = null;
    if (!line.hasOption("connection")) {
        System.out.println("Connection url must be set");
        Util.ShellExit(1);
    } else {
        connectionname = line.getOptionValue("connection");
        outputln("Connection: " + connectionname);
    }

    String sql = null;
    String tablename = null;

    if (line.hasOption("sql")) {
        sql = line.getOptionValue("sql");
        outputln("SQL: " + sql);

    }

    if (line.hasOption("meta")) {
        tablename = line.getOptionValue("meta");
        outputln("Table: " + tablename);
    }

    int nrColumns = 1;
    if (line.hasOption("columns")) {
        nrColumns = new Integer(line.getOptionValue("columns"));
    }

    long execStart = 0l;
    long execEnd = 0l;
    long openStart = 0l;
    long openEnd = 0l;
    long metaStart = 0l;
    long metaEnd = 0l;

    Class.forName(driverclassname).newInstance();
    openStart = System.currentTimeMillis();
    Connection conn = DriverManager.getConnection(connectionname);
    openEnd = System.currentTimeMillis();

    if (tablename != null) {
        ResultSet rsCol = null;
        metaStart = System.currentTimeMillis();
        DatabaseMetaData md = conn.getMetaData();
        metaEnd = System.currentTimeMillis();

        rsCol = md.getColumns(null, null, tablename, null);
        if (verbose) {
            tabular("COLUMN_NAME");
            tabular("TYPE_NAME");
            tabular("COLUMN_SIZE");
            tabularlast("DATA_TYPE");
            outputln("");
        }

        while (rsCol.next()) {
            tabular(rsCol.getString("COLUMN_NAME"));
            tabular(rsCol.getString("TYPE_NAME"));
            tabular(rsCol.getString("COLUMN_SIZE"));
            tabularlast(rsCol.getString("DATA_TYPE"));
            outputln("", true);
        }
    }

    if (sql != null) {
        Statement stat = conn.createStatement();
        stat.setQueryTimeout(10);

        execStart = System.currentTimeMillis();
        ResultSet res = stat.executeQuery(sql);
        ResultSetMetaData rsmd = res.getMetaData();
        execEnd = System.currentTimeMillis();

        if (verbose) {
            for (int i = 1; i < nrColumns + 1; i++) {
                if (i != nrColumns)
                    tabular(rsmd.getColumnName(i));
                else
                    tabularlast(rsmd.getColumnName(i));
            }
            outputln("");
        }
        while (res.next()) {
            for (int i = 1; i < nrColumns + 1; i++) {
                if (i != nrColumns)
                    tabular(res.getString(i));
                else
                    tabularlast(res.getString(i));
            }
            outputln("", true);
        }

        stat.close();
        res.close();
    }

    conn.close();

    // Print the execution times
    outputln("Open time: " + (openEnd - openStart) + " ms");

    if (line.hasOption("meta")) {
        outputln("Meta time: " + (metaEnd - metaStart) + " ms");
    }

    if (line.hasOption("sql")) {
        outputln("Exec time: " + (execEnd - execStart) + " ms");
    }
}