Example usage for java.sql DatabaseMetaData getPrimaryKeys

List of usage examples for java.sql DatabaseMetaData getPrimaryKeys

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

Introduction

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

Prototype

ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException;

Source Link

Document

Retrieves a description of the given table's primary key columns.

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, PRIMARY KEY (id) );");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    DatabaseMetaData meta = conn.getMetaData();
    ResultSet rs = meta.getPrimaryKeys(null, null, "survey");

    java.util.List list = new java.util.ArrayList();
    while (rs.next()) {
        String columnName = rs.getString("COLUMN_NAME");
        System.out.println("getPrimaryKeys(): columnName=" + columnName);
    }/*from  w  w w. j  av a  2 s  . c om*/

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

From source file:PrimaryKeysSuppliers.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;//from   ww w .java  2 s . c o m
    String createString = "create table SUPPLIERSPK " + "(SUP_ID INTEGER NOT NULL, " + "SUP_NAME VARCHAR(40), "
            + "STREET VARCHAR(40), " + "CITY VARCHAR(20), " + "STATE CHAR(2), " + "ZIP CHAR(5), "
            + "primary key(SUP_ID))";
    Statement stmt;

    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");

        stmt = con.createStatement();
        stmt.executeUpdate(createString);

        DatabaseMetaData dbmd = con.getMetaData();

        ResultSet rs = dbmd.getPrimaryKeys(null, null, "SUPPLIERSPK");
        while (rs.next()) {
            String name = rs.getString("TABLE_NAME");
            String columnName = rs.getString("COLUMN_NAME");
            String keySeq = rs.getString("KEY_SEQ");
            String pkName = rs.getString("PK_NAME");
            System.out.println("table name :  " + name);
            System.out.println("column name:  " + columnName);
            System.out.println("sequence in key:  " + keySeq);
            System.out.println("primary key name:  " + pkName);
            System.out.println("");
        }

        rs.close();
        con.close();

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

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, PRIMARY KEY (id) );");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet rs = null;/*  w w  w  .  j  ava 2s . c  om*/
    DatabaseMetaData meta = conn.getMetaData();
    // The Oracle database stores its table names as Upper-Case,
    // if you pass a table name in lowercase characters, it will not work.
    // MySQL database does not care if table name is uppercase/lowercase.
    //
    rs = meta.getPrimaryKeys(null, null, "survey");

    java.util.List list = new java.util.ArrayList();
    while (rs.next()) {
        String columnName = rs.getString("COLUMN_NAME");
        System.out.println("getPrimaryKeys(): columnName=" + columnName);
    }

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

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 {/*from  w  ww.j a  va  2  s. c om*/
            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:org.apache.phoenix.hive.util.PhoenixUtil.java

public static List<String> getPrimaryKeyColumnList(Connection conn, String tableName) throws SQLException {
    Map<Short, String> primaryKeyColumnInfoMap = Maps.newHashMap();
    DatabaseMetaData dbMeta = conn.getMetaData();

    String[] schemaInfo = getTableSchema(tableName.toUpperCase());
    try (ResultSet rs = dbMeta.getPrimaryKeys(null, schemaInfo[0], schemaInfo[1])) {
        while (rs.next()) {
            primaryKeyColumnInfoMap.put(rs.getShort("KEY_SEQ"), rs.getString("COLUMN_NAME"));
        }/* w w w . j a  v  a 2s  .  c o  m*/

        if (LOG.isDebugEnabled()) {
            LOG.debug("PK-columns : " + primaryKeyColumnInfoMap);
        }
    }

    return Lists.newArrayList(primaryKeyColumnInfoMap.values());
}

From source file:net.ontopia.topicmaps.cmdlineutils.rdbms.RDBMSIndexTool.java

protected static Map getPrimaryKeys(String table_name, DatabaseMetaData dbm) throws SQLException {
    // returns { table_name(colname,...) : index_name }
    Map result = new HashMap(5);
    ResultSet rs = dbm.getPrimaryKeys(null, null, table_name);
    String prev_index_name = null;
    String columns = null;/*from w w w  .  ja va  2  s  .  com*/

    while (rs.next()) {
        String index_name = rs.getString(6);
        if (prev_index_name != null && !prev_index_name.equals(index_name)) {
            result.put(table_name + '(' + columns + ')', prev_index_name);
            columns = null;
        }
        // column_name might be quoted, so unquote it before proceeding
        String column_name = unquote(rs.getString(4), dbm.getIdentifierQuoteString());
        if (columns == null)
            columns = column_name;
        else
            columns = columns + "," + column_name;

        prev_index_name = index_name;
    }
    rs.close();

    if (prev_index_name != null)
        result.put(table_name + '(' + columns + ')', prev_index_name);

    return result;
}

From source file:org.eclipse.dirigible.repository.ext.db.DBUtils.java

public static ResultSet getPrimaryKeys(Connection connection, String name) throws SQLException {

    DatabaseMetaData meta = connection.getMetaData();

    if (name == null) {
        meta.getPrimaryKeys(null, null, name);
    }/*from w  w  w  . j  a  v  a 2  s  . co m*/

    ResultSet columns = meta.getPrimaryKeys(null, null, name);
    if (columns.next()) {
        return meta.getPrimaryKeys(null, null, name);
    } else {
        columns = meta.getPrimaryKeys(null, null, name.toLowerCase());
        if (columns.next()) {
            return meta.getPrimaryKeys(null, null, name.toLowerCase());
        } else {
            columns = meta.getPrimaryKeys(null, null, name.toUpperCase());
            // if (columns.next()) {
            // return meta.getColumns(null, null, name.toUpperCase(), null);
            // }
        }
    }

    return columns;
}

From source file:org.ralasafe.db.DBView.java

private static TableView getTable(Connection conn, String dsName, String schema, String tableName)
        throws SQLException {
    String mySchema = "";
    if (!StringUtil.isEmpty(schema)) {
        mySchema = schema + ".";
    }/*www  .  j  ava  2  s  .com*/

    if (log.isDebugEnabled()) {
        log.debug("Get table/view definition: dsName=" + dsName + ", table/view Name=" + mySchema + tableName);
    }

    Statement stmt = null;
    ResultSet rs = null;
    ResultSet primaryKeys = null;

    try {
        stmt = conn.createStatement();

        rs = stmt.executeQuery("select * from " + mySchema + tableName + " where 1=2");
        ResultSetMetaData metaData = rs.getMetaData();

        TableView table = new TableView();
        table.setSchema(schema);
        table.setName(tableName);

        DatabaseMetaData metaData2 = conn.getMetaData();
        String databaseProductName = DBUtil.getDatabaseProductName(conn);

        if (databaseProductName == DBUtil.MYSQL) {
            primaryKeys = metaData2.getPrimaryKeys(schema, null, tableName);
        } else {
            primaryKeys = metaData2.getPrimaryKeys(null, null, tableName);
        }

        Map pkColumnViewMap = new HashMap();
        while (primaryKeys.next()) {
            pkColumnViewMap.put(primaryKeys.getString("COLUMN_NAME"), null);
        }

        List columnList = new ArrayList(metaData.getColumnCount());
        for (int i = 1, columnCount = metaData.getColumnCount(); i <= columnCount; i++) {
            ColumnView column = new ColumnView();
            String columnName = metaData.getColumnName(i);
            column.setName(columnName);
            String sqlType = metaData.getColumnTypeName(i);

            if (sqlType.equalsIgnoreCase("blob") || sqlType.equalsIgnoreCase("clob")
                    || sqlType.equalsIgnoreCase("text")) {
                // DO NOTHING
            } else {
                int precision = metaData.getPrecision(i);
                int scale = metaData.getScale(i);

                if (precision != 0) {
                    if (scale == 0) {
                        sqlType = sqlType + "(" + precision + ")";
                    } else {
                        sqlType = sqlType + "(" + precision + "," + scale + ")";
                    }
                }
            }
            column.setSqlType(sqlType);
            columnList.add(column);

            // it's a primary key?
            if (pkColumnViewMap.containsKey(columnName)) {
                pkColumnViewMap.put(columnName, column);
            }
        }

        table.setColumnViews(columnList);

        // sometimes, oracle jdbc driver returns pk info is redundance, 
        // actually the column does exist at all.  Clear them.
        clearInvalidPK(pkColumnViewMap);

        if (pkColumnViewMap.size() > 0) {
            table.setPkColumnViews(pkColumnViewMap.values());
        }
        return table;
    } finally {
        DBUtil.close(primaryKeys);
        DBUtil.close(rs);
        DBUtil.close(stmt);
    }
}

From source file:gridool.db.helpers.GridDbUtils.java

@Nullable
public static PrimaryKey getPrimaryKey(@Nonnull final Connection conn, @CheckForNull final String pkTableName,
        final boolean reserveAdditionalInfo) throws SQLException {
    if (pkTableName == null) {
        throw new IllegalArgumentException();
    }/* www  . j  a v a 2  s .c  o m*/
    DatabaseMetaData metadata = conn.getMetaData();
    String catalog = conn.getCatalog();
    final ResultSet rs = metadata.getPrimaryKeys(catalog, null, pkTableName);
    final PrimaryKey pkey;
    try {
        if (!rs.next()) {
            return null;
        }
        String pkName = rs.getString("PK_NAME");
        pkey = new PrimaryKey(pkName, pkTableName);
        do {
            pkey.addColumn(rs);
        } while (rs.next());
    } finally {
        rs.close();
    }

    if (reserveAdditionalInfo) {
        // set foreign key column positions
        pkey.setColumnPositions(metadata);
        // set exported keys
        final Collection<ForeignKey> exportedKeys = getExportedKeys(conn, pkTableName, false);
        if (!exportedKeys.isEmpty()) {
            final List<String> pkColumnsProbe = pkey.getColumnNames();
            final int numPkColumnsProbe = pkColumnsProbe.size();
            final List<ForeignKey> exportedKeyList = new ArrayList<ForeignKey>(4);
            outer: for (ForeignKey fk : exportedKeys) {
                List<String> names = fk.getPkColumnNames();
                if (names.size() != numPkColumnsProbe) {
                    continue;
                }
                for (String name : names) {
                    if (!pkColumnsProbe.contains(name)) {
                        continue outer;
                    }
                }
                exportedKeyList.add(fk);
            }
            if (!exportedKeyList.isEmpty()) {
                pkey.setExportedKeys(exportedKeyList);
            }
        }
    }
    return pkey;
}

From source file:org.openflexo.technologyadapter.jdbc.util.SQLHelper.java

private static Set<String> getKeys(final JDBCTable table) throws SQLException {
    // TODO : maybe resource leak, cannot use lexical scope for auto-closing
    Connection connection = table.getResourceData().getConnection();

    DatabaseMetaData metadata = connection.getMetaData();

    try (ResultSet foundKeys = metadata.getPrimaryKeys(connection.getCatalog(), "PUBLIC",
            sqlName(table.getName()))) {

        Set<String> keys = new HashSet<>();
        while (foundKeys.next()) {
            keys.add(foundKeys.getString("COLUMN_NAME"));
        }/*from  ww  w  .j ava2  s  .c  o m*/
        return keys;
    }
}