Example usage for java.sql DatabaseMetaData getColumns

Introduction

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

Prototype

ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
        throws SQLException;

Source Link

Document

Retrieves a description of table columns available in the specified catalog.

Usage

From source file:org.bibsonomy.database.common.util.AbstractDatabaseSchemaInformation.java

/**
 * returns meta informations of the database
 * //from   w w w.  jav a 2 s  .co m
 * @param <R>
 * @param resultClass
 * @param tableNamePattern
 * @param columnNamePattern
 * @param columnLabel
 * 
 * @return the schema information of the column of the table
 */
@SuppressWarnings("unchecked")
protected static <R> R getSchemaInformation(/* only used for the cast */final Class<R> resultClass,
        final String tableNamePattern, final String columnNamePattern, final String columnLabel,
        final SqlMapSession sqlMapSession) {
    final DataSource dataSource = sqlMapSession.getDataSource();

    Connection connection = null;

    try {
        connection = dataSource.getConnection();
        final DatabaseMetaData metaData = connection.getMetaData();
        final ResultSet columns = metaData.getColumns(null, null, tableNamePattern, columnNamePattern);
        while (columns.next()) {
            return (R) columns.getObject(columnLabel);
        }
    } catch (final SQLException ex) {
        log.warn("can't get schema informations for column '" + columnNamePattern + "' of table '"
                + tableNamePattern + "'", ex);
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (final SQLException ex) {
                log.warn("can't close connection", ex);
            }
        }
    }

    return null;
}

---

From source file:nl.b3p.gis.FeatureSchemaFactory.java

/**Creates a FeatureSchema from a table.
 * @param conn The connection to the database.
 * @param table The name of the table in the given database. From this table a FeatureSchema is created
 * @return a FeatureSchema//from  w  w  w  . ja  v a 2  s.  c  o  m
 */
static public FeatureSchema createFeatureSchemaFromDbTable(Connection conn, String table,
        String[] dontAddColumns) throws SQLException, Exception {
    FeatureSchema fs = new FeatureSchema();
    List tableNames = SqlMetaDataUtils.getTableAndViewNames(conn);
    DatabaseMetaData dbmd = conn.getMetaData();
    if (tableNames.contains(table)) {
        ResultSet rs = dbmd.getColumns(null, null, table, null);
        for (int i = 0; rs.next(); i++) {
            String columnName = rs.getString("COLUMN_NAME");
            boolean add = true;
            if (dontAddColumns != null) {
                for (int d = 0; d < dontAddColumns.length && add; d++) {
                    if (dontAddColumns[d].equalsIgnoreCase(columnName)) {
                        add = false;
                    }
                }
            }
            if (add) {
                int dataType = rs.getInt("DATA_TYPE");
                AttributeType at = SqlMetaDataUtils.getAttributeTypeFromSqlType(dataType);
                //probably a Geom object, otherwise unknown type so don't add the attribute.
                if (at.equals(AttributeType.OBJECT)) {
                    if (dbmd.getDatabaseProductName().equalsIgnoreCase(SqlMetaDataUtils.PRODUCT_POSTGRES)) {
                        //the geom columns in postgis are stored in the "geometry_columns" table
                        PreparedStatement statement = null;
                        statement = conn.prepareStatement(
                                "SELECT * FROM geometry_columns g WHERE g.f_table_name = '" + table + "';");
                        ResultSet rsgeom = statement.executeQuery();
                        //if there is a geometry_columns record then this is a geometry
                        if (rsgeom.next()) {
                            int epsgCode = rsgeom.getInt("srid");
                            CoordinateSystem cs = new CoordinateSystem("EPSG:" + epsgCode, epsgCode, null);
                            fs.setCoordinateSystem(cs);

                            String type = rsgeom.getString("type");

                            fs.addAttribute(columnName, AttributeType.GEOMETRY);
                        }
                        statement.close();
                    } else {
                        log.info("create geometry feature not supported for " + dbmd.getDatabaseProductName());
                    }
                } else {
                    fs.addAttribute(columnName, at);
                }
            }
        }
        //set the coordinateSystem without the projection. Don't need it (yet)

    } else {
        log.error("Table " + table + " not found");
        throw new Exception("Table " + table + " not found");
    }
    return fs;
}

---

From source file:org.apache.zeppelin.mysql.SqlCompleter.java

private static void getColumnNames(DatabaseMetaData meta, Set<String> names) {

    try {//from  w  w w.  j  a  v  a 2  s  .co m
        try (ResultSet columns = meta.getColumns(meta.getConnection().getCatalog(), null, "%", "%")) {

            while (columns.next()) {
                // Add the following strings: (1) column name, (2) table name
                String name = columns.getString("TABLE_NAME");
                if (!isBlank(name)) {
                    names.add(name);
                    names.add(columns.getString("COLUMN_NAME"));
                    // names.add(columns.getString("TABLE_NAME") + "." + columns.getString("COLUMN_NAME"));
                }
            }
        }

        logger.debug(Joiner.on(',').join(names));
    } catch (Throwable t) {
        logger.error("Failed to retrieve the column name", t);
    }
}

---

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

private static List<String[]> getColumns(final String table) {
    final List<String[]> rows = new ArrayList<String[]>();
    rows.add(new String[] { "TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE", "TYPE_NAME",
            "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE", "REMARKS",
            "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH", "ORDINAL_POSITION",
            "IS_NULLABLE", "SCOPE_CATLOG", "SCOPE_SCHEMA", "SCOPE_TABLE", "SOURCE_DATA_TYPE",
            "IS_AUTOINCREMENT" });
    Connection con = null;//from www  . j  a  v a2s  .  c  om
    ResultSet rs = null;
    try {
        con = getConnection();
        final DatabaseMetaData md = con.getMetaData();
        rs = md.getColumns(null, null, table, "%");
        while (rs.next()) {
            List<String> s = new ArrayList<String>(23);
            for (int i = 23; i > 0; i--)
                s.add(rs.getString(i));
            rows.add(s.toArray(new String[23]));
        }
    } catch (SQLException x) {
        log.error("Error describing table: " + x.getMessage(), x);
    } finally {
        close(con, null, rs);
    }
    return rows;
}

---

From source file:org.apache.zeppelin.impala.SqlCompleter.java

private static void getColumnNames(DatabaseMetaData meta, Set<String> names) throws SQLException {

    try {//from  w  ww. ja  va  2s . co m
        ResultSet columns = meta.getColumns(meta.getConnection().getCatalog(), null, "%", "%");
        try {

            while (columns.next()) {
                // Add the following strings: (1) column name, (2) table name
                String name = columns.getString("TABLE_NAME");
                if (!isBlank(name)) {
                    names.add(name);
                    names.add(columns.getString("COLUMN_NAME"));
                    // names.add(columns.getString("TABLE_NAME") + "." + columns.getString("COLUMN_NAME"));
                }
            }
        } finally {
            columns.close();
        }

        logger.debug(Joiner.on(',').join(names));
    } catch (Exception e) {
        logger.error("Failed to retrieve the column name", e);
    }
}

---

From source file:org.apache.zeppelin.postgresql.SqlCompleter.java

private static void getColumnNames(DatabaseMetaData meta, Set<String> names) throws SQLException {

    try {//w w  w . ja v a 2  s.c o  m
        ResultSet columns = meta.getColumns(meta.getConnection().getCatalog(), null, "%", "%");
        try {

            while (columns.next()) {
                // Add the following strings: (1) column name, (2) table name
                String name = columns.getString("TABLE_NAME");
                if (!isBlank(name)) {
                    names.add(name);
                    names.add(columns.getString("COLUMN_NAME"));
                    // names.add(columns.getString("TABLE_NAME") + "." + columns.getString("COLUMN_NAME"));
                }
            }
        } finally {
            columns.close();
        }

        logger.debug(Joiner.on(',').join(names));
    } catch (Throwable t) {
        logger.error("Failed to retrieve the column name", t);
    }
}

---

From source file:org.kawanfw.sql.servlet.sql.PostgreSqlUtil.java

/**
 * Says if the database is PostgreSQL AND there is an OID column for large
 * file storage/*from ww  w.j a  v a 2s  .c o m*/
 * 
 * @param connection
 *            the JDBC Connection
 * @param sql
 *            the sql order
 * @return true if the database is PostgreSQL AND there is a OID column for
 *         large file storage
 */
public static boolean isPostgreSqlStatementWithOID(Connection connection, String sql)
        throws SQLException, IOException {

    debug("before new SqlUtil(connection).isPostgreSQL()");
    if (!new SqlUtil(connection).isPostgreSQL()) {
        return false;
    }

    String catalog = null;
    String schema = null;
    ResultSet rs = null;

    StatementAnalyser statementAnalyser = new StatementAnalyser(sql, new Vector<Object>());
    String table = statementAnalyser.getTableNameFromDmlStatement();

    table = table.toLowerCase();

    debug("table: " + table);

    DatabaseMetaData databaseMetaData = connection.getMetaData();

    try {
        rs = databaseMetaData.getColumns(catalog, schema, table, null);
        debug("Before rs.next");
        while (rs.next()) {
            int columnType = rs.getInt(5);

            if (columnType == Types.BIGINT) {
                return true;
            }
        }
    } finally {
        if (rs != null) {
            rs.close();
        }
    }

    return false;
}

---

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

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

    DatabaseMetaData meta = connection.getMetaData();

    if (name == null) {
        meta.getColumns(null, null, name, null);
    }// w  w w . j  av  a2  s  .  com

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

    return columns;
}

---

From source file:com.example.querybuilder.server.Jdbc.java

public static ResultSet getColumns(DatabaseMetaData databaseMetaData, String catalog, String schemaPattern,
        String tableNamePattern, String columnNamePattern) {
    try {//  ww  w  .j a  v  a2  s.  co m
        return databaseMetaData.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);
    } catch (SQLException e) {
        throw new SqlRuntimeException(e);
    }
}

---

From source file:org.kawanfw.sql.servlet.sql.PostgreSqlUtil.java

/**
 * Returns all the column names that are Types.BIGINT
 * @param connection//from  www . j  a  va 2s . c o  m
 * @return the column names that are Types.BIGINT
 * @throws SQLException
 */
public static Set<String> getTypeBigIntColumnNames(Connection connection) throws SQLException {

    if (connection == null) {
        throw new IllegalArgumentException("connection is null!");
    }

    DatabaseMetaData databaseMetaData = connection.getMetaData();

    String catalog = null;
    String schema = "public";
    String table = null;

    Set<String> typeBigIntColumnNames = new TreeSet<String>();
    ResultSet rs = null;
    try {
        rs = databaseMetaData.getColumns(catalog, schema, table, null);
        debug("Before rs.next");
        while (rs.next()) {
            int columnType = rs.getInt(5);

            if (columnType == Types.BIGINT) {

                if (DEBUG) {
                    System.out.println();
                    System.out.println(rs.getString(1));
                    System.out.println(rs.getString(2));
                    System.out.println(rs.getString(4));
                }

                String columnName = rs.getString(4).toLowerCase();
                typeBigIntColumnNames.add(columnName);
            }
        }
    } finally {
        if (rs != null) {
            rs.close();
        }
    }

    return typeBigIntColumnNames;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• »