Example usage for java.sql DatabaseMetaData getColumns

List of usage examples for java.sql DatabaseMetaData getColumns

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. 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:com.seer.datacruncher.profiler.spring.ProfilerInfoUpdateController.java

public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

    ServletOutputStream out = null;//  w  w w.j  a  v a 2 s.  co  m
    response.setContentType("application/json");
    out = response.getOutputStream();

    @SuppressWarnings("unchecked")
    Hashtable<String, String> dbParams = (Hashtable<String, String>) request.getSession(true)
            .getAttribute("dbConnectionData");
    if (dbParams != null) {
        request.setAttribute("serverName", CommonUtil.notNullValue(dbParams.get("Database_DSN")));
    }
    String selectedValue = CommonUtil.notNullValue(request.getParameter("selectedValue"));

    request.setAttribute("selectedValue", selectedValue);
    String tableName = CommonUtil.notNullValue(request.getParameter("parent"));

    request.setAttribute("parentValue", tableName);

    ObjectMapper mapper = new ObjectMapper();

    Vector vector = RdbmsConnection.getTable();

    int i = vector.indexOf(tableName);

    Vector avector[] = (Vector[]) null;
    avector = TableMetaInfo.populateTable(5, i, i + 1, avector);

    QueryDialog querydialog = new QueryDialog(1, tableName, avector);
    try {
        querydialog.executeAction("");
    } catch (Exception e) {
        e.printStackTrace();
    }

    String strColumnName = "";

    List<String> listPrimaryKeys = new ArrayList<String>();
    Map<String, Integer> mapColumnNames = new HashMap<String, Integer>();

    try {

        RdbmsConnection.openConn();
        DatabaseMetaData dbmd = RdbmsConnection.getMetaData();

        ResultSet resultset = dbmd.getPrimaryKeys(null, null, tableName);
        while (resultset.next()) {
            listPrimaryKeys.add(resultset.getString("COLUMN_NAME"));
        }

        resultset = dbmd.getColumns(null, null, tableName, null);

        while (resultset.next()) {
            strColumnName = resultset.getString(4);
            mapColumnNames.put(strColumnName, resultset.getInt(5));
        }

        RdbmsConnection.closeConn();
    } catch (Exception ex) {
        ex.printStackTrace();
    }

    Map<String, Integer> mapPrimaryKeys = new HashMap<String, Integer>();

    if (strColumnName.trim().length() > 0) {
        try {
            JSONArray array = new JSONArray(request.getParameter("data"));

            for (int count = 0; count < array.length(); count++) {
                JSONObject jsonObject = new JSONObject(array.get(count).toString());

                StringBuilder queryString = new StringBuilder();
                Iterator<String> keyIterator = jsonObject.keys();

                while (keyIterator.hasNext()) {
                    String strKey = keyIterator.next();

                    if (listPrimaryKeys.contains(strKey)) {
                        mapPrimaryKeys.put(strKey,
                                ((int) Double.parseDouble(jsonObject.get(strKey).toString())));
                        continue;
                    }
                    if (jsonObject.get(strKey) != null) {

                        if (mapColumnNames.get(strKey) == 4 || mapColumnNames.get(strKey) == 5
                                || mapColumnNames.get(strKey) == -6) {
                            queryString.append(
                                    strKey + "=" + Integer.parseInt(jsonObject.get(strKey).toString()) + ",");
                        } else if (mapColumnNames.get(strKey) == 2 || mapColumnNames.get(strKey) == 3
                                || mapColumnNames.get(strKey) == 7 || mapColumnNames.get(strKey) == 6
                                || mapColumnNames.get(strKey) == -5) {
                            queryString.append(strKey + "=" + jsonObject.get(strKey) + ",");
                        } else if (mapColumnNames.get(strKey) == 91 || mapColumnNames.get(strKey) == 92
                                || mapColumnNames.get(strKey) == 93) {
                            queryString.append(strKey + "=" + jsonObject.get(strKey) + ",");
                        } else if (mapColumnNames.get(strKey) == -7 || mapColumnNames.get(strKey) == 16
                                || mapColumnNames.get(strKey) == -3 || mapColumnNames.get(strKey) == -4) {
                            queryString.append(strKey + "=" + jsonObject.get(strKey) + ",");
                        } else if (mapColumnNames.get(strKey) == -1 || mapColumnNames.get(strKey) == 1
                                || mapColumnNames.get(strKey) == 12) {
                            queryString.append(strKey + "=\"" + jsonObject.get(strKey) + "\",");
                        }
                    }
                }
                StringBuilder whereClause = new StringBuilder(" where ");

                for (String primaryKey : listPrimaryKeys) {
                    whereClause.append(primaryKey + "=" + mapPrimaryKeys.get(primaryKey).intValue());
                    whereClause.append(" and ");
                }
                String strWhereClause = whereClause.toString();
                strWhereClause = strWhereClause.substring(0, strWhereClause.lastIndexOf("and"));

                queryString = new StringBuilder("UPDATE " + tableName + " SET ")
                        .append(queryString.toString().substring(0, queryString.toString().length() - 1));
                queryString.append(strWhereClause);

                RdbmsConnection.openConn();
                RdbmsConnection.executeUpdate(queryString.toString());
                RdbmsConnection.closeConn();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    Update update = new Update();
    update.setSuccess(true);

    GridUtil gridUtil = new GridUtil();
    gridUtil.generateGridData(querydialog.getTableGridDTO(), false, null);
    update.setResults(gridUtil.getData());

    out.write(mapper.writeValueAsBytes(update));
    out.flush();
    out.close();

    return null;
}

From source file:com.seer.datacruncher.spring.SchemaFieldsPopupUpdateController.java

private void deleteLinkedTableFields(SchemaFieldEntity fieldEntity, String tableName) {
    try {//  w  w w  .j av a 2s  .  c  o  m
        Connection connection = getConnection(String.valueOf(fieldEntity.getIdSchema()), true);
        DatabaseMetaData md = connection.getMetaData();
        ResultSet rs = md.getColumns(null, null, tableName, null);

        List<SchemaFieldEntity> listSchemaFields = schemaFieldsDao
                .findAllByParentId(fieldEntity.getIdSchemaField());

        while (rs.next()) {
            String colName = rs.getString("COLUMN_NAME");
            String linkToDb = tableName + "." + colName;
            if (listSchemaFields != null) {
                for (SchemaFieldEntity instance : listSchemaFields) {
                    if (colName.equals(instance.getName()) && instance.getLinkToDb().equals(linkToDb)) {
                        delete(instance.getIdSchemaField());
                        break;
                    }
                }
            }
        }
    } catch (SQLException sqex) {
        sqex.printStackTrace();
    }
}

From source file:com.emr.schemas.TableRelationsForm.java

/**
 * Method to populate a table's columns to a {@link ListModel}
 * @param tableName {@link String} Table name
 *///from  w  w w  .  ja  va 2  s  . c o  m
private void populateTableColumnsToList(String tableName) {
    try {
        DatabaseMetaData dbmd = emrConn.getMetaData();
        ResultSet rs = dbmd.getColumns(null, null, tableName, "%");
        while (rs.next()) {
            String colName = rs.getString(4);
            listModel.addElement(tableName + "." + colName);
        }
    } catch (SQLException e) {
        String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e);
        JOptionPane.showMessageDialog(this,
                "Could not fetch Tables for the KenyaEMR Database. Error Details: " + stacktrace,
                "Table Names Error", JOptionPane.ERROR_MESSAGE);
    }
}

From source file:com.emr.schemas.TableRelationsForm.java

/**
 * Method to get all the columns in a table
 * @param tableName {@link String} Table name
 * @return {@link List} List of the table's columns
 */// www  .j  a  v a2  s. c  om
private List getTableColumns(String tableName) {
    List columns = new ArrayList();
    try {
        DatabaseMetaData dbmd = emrConn.getMetaData();
        ResultSet rs = dbmd.getColumns(null, null, tableName, "%");
        while (rs.next()) {
            String colName = rs.getString(4);
            columns.add(colName);
        }
    } catch (SQLException e) {
        String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e);
        JOptionPane.showMessageDialog(this,
                "Could not fetch Tables for the KenyaEMR Database. Error Details: " + stacktrace,
                "Table Names Error", JOptionPane.ERROR_MESSAGE);
    }
    return columns;
}

From source file:org.apache.torque.generator.source.jdbc.JdbcMetadataSource.java

/**
 * Retrieves all the column names and types for a given table from
 * JDBC metadata.//from  w ww .j  a  va2s.  c o m
 *
 * @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;
}

From source file:org.seasar.dbflute.logic.jdbc.metadata.basic.DfColumnExtractor.java

protected ResultSet extractColumnMetaData(DatabaseMetaData metaData, UnifiedSchema unifiedSchema,
        String tableName, boolean retry) throws SQLException {
    final String catalogName = unifiedSchema.getPureCatalog();
    final String schemaName = unifiedSchema.getPureSchema();
    try {/*  w w w.j  av a  2s  .  co m*/
        return metaData.getColumns(catalogName, schemaName, tableName, null);
    } catch (SQLException e) {
        if (retry) {
            // because the exception may be thrown when the table is not found
            return null;
        } else {
            throw e;
        }
    }
}

From source file:com.jaxio.celerio.configuration.database.support.MetadataExtractor.java

private void loadColumns(JdbcConnectivity configuration, DatabaseMetaData databaseMetaData, Table table)
        throws SQLException {
    log.info("Extracting columns for table: " + table.getName());
    ResultSet resultSet = databaseMetaData.getColumns(configuration.getCatalog(), configuration.getSchemaName(),
            table.getName(), "%");
    ResultSetWrapper rsw = new ResultSetColumns(resultSet, useLabel);

    while (resultSet.next()) {
        Column c = new Column();

        // fill it
        c.setName(getString(rsw, "COLUMN_NAME"));
        c.setType(JdbcType.fromJdbcType(rsw.getInt("DATA_TYPE")));
        c.setSize(rsw.getInt("COLUMN_SIZE"));
        c.setDecimalDigits(rsw.getInt("DECIMAL_DIGITS"));
        c.setNullable(isNullable(rsw.getInt("NULLABLE")));
        String remarks = getString(rsw, "REMARKS");
        if (notEmpty(remarks)) {
            c.setRemarks(remarks);/*from w  w w  .  j  ava2s. c  o m*/
        }
        String columnDef = getString(rsw, "COLUMN_DEF");
        if (notEmpty(columnDef)) {
            c.setColumnDef(columnDef);
        }
        c.setOrdinalPosition(rsw.getInt("ORDINAL_POSITION"));

        try {
            // not all driver may support it, it was added post jdk 1.4.2
            String autoIncrement = getString(rsw, "IS_AUTOINCREMENT");
            if ("YES".equalsIgnoreCase(autoIncrement)) {
                c.setAutoIncrement(Boolean.TRUE);
            } else if ("NO".equalsIgnoreCase(autoIncrement)) {
                c.setAutoIncrement(Boolean.FALSE);
            } else {
                c.setAutoIncrement(null);
            }
        } catch (SQLException sqle) {
            c.setAutoIncrement(null);
        }

        // add it
        table.addColumn(c);
    }

    resultSet.close();
}

From source file:com.emr.schemas.EditMappingsForm.java

/**
 * Method for getting a tables' columns//from ww w  . j a  va2 s  .  c om
 * @param tableName {@link String} The table name
 * @return {@link List} List of the table's columns
 */
private List getTableColumns(String tableName) {
    List tableColumns = new ArrayList();
    try {
        DatabaseMetaData dbmd = mpiConn.getMetaData();
        ResultSet rs = dbmd.getColumns(null, null, tableName, "%");
        while (rs.next()) {
            String colName = rs.getString(4);
            tableColumns.add(colName);
        }
    } catch (SQLException e) {

        String stacktrace = org.apache.commons.lang3.exception.ExceptionUtils.getStackTrace(e);
        JOptionPane.showMessageDialog(this,
                "Could not fetch Tables for the KenyaEMR Database. Error Details: " + stacktrace,
                "Table Names Error", JOptionPane.ERROR_MESSAGE);
    }
    return tableColumns;
}

From source file:com.uber.hoodie.hive.HoodieHiveClient.java

/**
 * Get the table schema//from   w w w . j ava 2s .  co  m
 */
Map<String, String> getTableSchema() {
    if (!doesTableExist()) {
        throw new IllegalArgumentException(
                "Failed to get schema for table " + syncConfig.tableName + " does not exist");
    }
    Map<String, String> schema = Maps.newHashMap();
    ResultSet result = null;
    try {
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        result = databaseMetaData.getColumns(null, syncConfig.databaseName, syncConfig.tableName, null);
        while (result.next()) {
            String columnName = result.getString(4);
            String columnType = result.getString(6);
            schema.put(columnName, columnType);
        }
        return schema;
    } catch (SQLException e) {
        throw new HoodieHiveSyncException("Failed to get table schema for " + syncConfig.tableName, e);
    } finally {
        closeQuietly(result, null);
    }
}

From source file:org.jboss.dashboard.ui.panel.dataSourceManagement.DataSourceManagementHandler.java

private List getTableColumns(String tableName) throws Exception {
    List result = new ArrayList();
    Connection connection = getConnection();
    ResultSet columns = null;/*  w  ww  .  ja  va 2  s . c om*/
    try {
        DatabaseMetaData metadata = connection.getMetaData();
        columns = metadata.getColumns(null, "%", tableName, "%");

        while (columns.next()) {
            DataSourceColumnEntry columnEntry = new DataSourceColumnEntry();
            columnEntry.setDatasource(getName());
            columnEntry.setTableName(tableName);
            columnEntry.setSqltype(columns.getShort(COLUMN_DATA_TYPE));
            columnEntry.setName(columns.getString(COLUMN_NAME));
            columnEntry.setIdentity("false");
            columnEntry.setPrimaryKey("false");
            result.add(columnEntry);
        }
        return result;
    } finally {
        try {
            if (columns != null)
                columns.close();
            if (connection != null)
                connection.close();
        } catch (SQLException ignore) {
        }
    }
}