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:com.netflix.metacat.connector.jdbc.services.JdbcConnectorTableService.java

/**
 * Get the columns for a table. See//from  w w  w .  j  av  a2  s. c o m
 * {@link java.sql.DatabaseMetaData#getColumns(String, String, String, String) getColumns} for format of the
 * ResultSet columns.
 *
 * @param connection The database connection to use
 * @param name       The qualified name of the table to get the column descriptions for
 * @return The result set of information
 * @throws SQLException on query error
 */
protected ResultSet getColumns(@Nonnull @NonNull final Connection connection,
        @Nonnull @NonNull final QualifiedName name) throws SQLException {
    final String database = name.getDatabaseName();
    final DatabaseMetaData metaData = connection.getMetaData();
    return metaData.getColumns(database, database, name.getTableName(),
            JdbcConnectorUtils.MULTI_CHARACTER_SEARCH);
}

From source file:eu.optimis.sm.gui.server.ServiceManagerWebServiceImpl.java

private static void writeDB(String sess_id, String name, String pass)
        throws SQLException, ClassNotFoundException {
    try {/* w  ww  .j  a v  a 2 s. c  om*/
        hsqlServer.start();
        Connection connection = null;
        try {
            Class.forName("org.hsqldb.jdbcDriver");
            connection = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb", "sa", "");

            DatabaseMetaData md = connection.getMetaData();
            String[] types = { "TABLE", "VIEW" };
            ResultSet rs = md.getTables(null, null, null, types);

            Boolean exist = false;
            if (rs.next()) {
                logger.info("rs.getString(3) = " + rs.getString(3));
                if (rs.getString(3).equals(tableName.toUpperCase())) {
                    logger.info("Table " + rs.getString(3) + " already exists!");
                    exist = true;
                } else
                    logger.info("current table: " + rs.getString(3));
                while (rs.next()) {
                    logger.info("rs.getString(3) = " + rs.getString(3));
                    if (rs.getString(3).equals(tableName.toUpperCase())) {
                        logger.info("Table " + rs.getString(3) + " already exists!");
                        exist = true;
                    } else
                        logger.info("current table: " + rs.getString(3));
                }

            }
            if (!exist) {
                logger.info("creating table" + tableName);
                logger.info("Table " + tableName + " is created!");
                connection.prepareStatement("create table " + tableName + " (id VARCHAR(255), "
                        + "name VARCHAR(255), pass VARCHAR(255));").execute();
            }
            logger.info("insert into " + tableName + "(id, name, pass) " + "values ('" + sess_id + "', '" + name
                    + "', '" + pass + "');");

            try {
                connection.prepareStatement("insert into " + tableName + "(id, name, pass) " + "values ('"
                        + sess_id + "', '" + name + "', '" + pass + "');").execute();
                logger.info("insert: success!");

            } catch (Exception ex) {
                logger.info(ex);
            }

        } finally {
            if (connection != null) {
                connection.close();
            }
        }
    } finally {
        if (hsqlServer != null) {
            hsqlServer.stop();
        }
    }
}

From source file:DatabaseInfo.java

public void doGet(HttpServletRequest inRequest, HttpServletResponse outResponse)
        throws ServletException, IOException {

    PrintWriter out = null;/*  w  w w . j  a  va  2 s  .c o  m*/
    Connection connection = null;
    Statement statement;
    ResultSet rs;

    outResponse.setContentType("text/html");
    out = outResponse.getWriter();

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

        out.println("<HTML><HEAD><TITLE>Database Server Information</TITLE></HEAD>");
        out.println("<BODY>");
        out.println("<H1>General Source Information</H1>");
        out.println("getURL() - " + md.getURL() + "<BR>");
        out.println("getUserName() - " + md.getUserName() + "<BR>");
        out.println("getDatabaseProductVersion - " + md.getDatabaseProductVersion() + "<BR>");
        out.println("getDriverMajorVersion - " + md.getDriverMajorVersion() + "<BR>");
        out.println("getDriverMinorVersion - " + md.getDriverMinorVersion() + "<BR>");
        out.println("nullAreSortedHigh - " + md.nullsAreSortedHigh() + "<BR>");

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

        out.println("<H1>Data Source Limits</H1>");
        out.println("getMaxRowSize - " + md.getMaxRowSize() + "<BR>");
        out.println("getMaxStatementLength - " + md.getMaxStatementLength() + "<BR>");
        out.println("getMaxTablesInSelect - " + md.getMaxTablesInSelect() + "<BR>");
        out.println("getMaxConnections - " + md.getMaxConnections() + "<BR>");
        out.println("getMaxCharLiteralLength - " + md.getMaxCharLiteralLength() + "<BR>");

        out.println("<H1>SQL Object Available</H1>");
        out.println("getTableTypes()<BR><UL>");
        rs = md.getTableTypes();
        while (rs.next()) {
            out.println("<LI>" + rs.getString(1));
        }
        out.println("</UL>");

        out.println("getTables()<BR><UL>");
        rs = md.getTables("accounts", "", "%", new String[0]);
        while (rs.next()) {
            out.println("<LI>" + rs.getString("TABLE_NAME"));
        }
        out.println("</UL>");

        out.println("<H1>Transaction Support</H1>");
        out.println("getDefaultTransactionIsolation() - " + md.getDefaultTransactionIsolation() + "<BR>");
        out.println(
                "dataDefinitionIgnoredInTransactions() - " + md.dataDefinitionIgnoredInTransactions() + "<BR>");

        out.println("<H1>General Source Information</H1>");
        out.println("getMaxTablesInSelect - " + md.getMaxTablesInSelect() + "<BR>");
        out.println("getMaxColumnsInTable - " + md.getMaxColumnsInTable() + "<BR>");
        out.println("getTimeDateFunctions - " + md.getTimeDateFunctions() + "<BR>");
        out.println("supportsCoreSQLGrammar - " + md.supportsCoreSQLGrammar() + "<BR>");

        out.println("getTypeInfo()<BR><UL>");
        rs = md.getTypeInfo();
        while (rs.next()) {
            out.println("<LI>" + rs.getString(1));
        }
        out.println("</UL>");

        out.println("</BODY></HTML>");
    } catch (Exception e) {
        e.printStackTrace();
    }
}

From source file:it.unibas.spicy.persistence.csv.ExportCSVInstances.java

public void appendCSVInstances(MappingTask mappingTask, HashMap<String, String> directoryPaths, int scenarioNo)
        throws SQLException, DAOException, IOException {
    //connection to Postgres
    IConnectionFactory connectionFactory = new SimpleDbConnectionFactory();
    Connection connection = getConnectionToPostgres(connectionFactory);
    try {//from  w w  w .  ja v a  2 s  .  com
        Statement statement = connection.createStatement();

        //get table names from target database
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        String[] tableTypes = new String[] { "TABLE" };
        ResultSet tableResultSet = databaseMetaData.getTables(SpicyEngineConstants.MAPPING_TASK_DB_NAME,
                SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo, null, tableTypes);
        //for each table
        while (tableResultSet.next()) {
            String tableName = tableResultSet.getString("TABLE_NAME");
            String filePath = directoryPaths.get(tableName);

            if ((filePath != null) && (!filePath.equals(""))) {
                ResultSet allRows = statement.executeQuery("SELECT * FROM "
                        + SpicyEngineConstants.TARGET_SCHEMA_NAME + scenarioNo + ".\"" + tableName + "\";");
                //no of columns
                int columnCount = allRows.getMetaData().getColumnCount();
                //column names
                String[] columnNames = new String[columnCount];
                for (int i = 1; i <= columnCount; i++) {
                    columnNames[i - 1] = allRows.getMetaData().getColumnName(i);
                }
                if (checkDocForCSVColumns(columnCount, columnNames, filePath))
                    appendToCSVDocument(allRows, columnCount, filePath);
                else {
                    throw new DAOException("Column names do not match those of the csv file");
                }
                allRows.close();
            } else {
                System.out.println("The CSV file " + tableName + " cannot be found!");
                System.exit(-1);
            }
        }
    } finally {
        //close connection
        if (connection != null)
            connectionFactory.close(connection);
    }
}

From source file:com.hangum.tadpole.engine.manager.TadpoleSQLManager.java

/**
 * <pre>//from  w ww  .j  a v  a2 s.co  m
 * DB  ?.
 * 
 * ?     ???    .
 *    ?? " ' ? ?  .
 * 
 * </pre>
 * 
 * @param userDB
 * @return
 * @throws Exception
 */
public static SqlMapClient getInstance(final UserDBDAO userDB) throws TadpoleSQLManagerException {
    SqlMapClient sqlMapClient = null;
    Connection conn = null;

    //      synchronized (dbManager) {
    String searchKey = getKey(userDB);
    try {
        sqlMapClient = dbManager.get(searchKey);
        if (sqlMapClient == null) {

            // oracle ?   
            try {
                if (userDB.getDBDefine() == DBDefine.ORACLE_DEFAULT
                        | userDB.getDBDefine() == DBDefine.TIBERO_DEFAULT) {
                    DriverManager.setLoginTimeout(10);

                    if (userDB.getLocale() != null && !"".equals(userDB.getLocale())) {
                        Locale.setDefault(new Locale(userDB.getLocale()));
                    }
                }
            } catch (Exception e) {
                logger.error("set locale error", e);
            }

            // connection pool ? .
            sqlMapClient = SQLMap.getInstance(userDB);
            dbManager.put(searchKey, sqlMapClient);

            // metadata   .
            conn = sqlMapClient.getDataSource().getConnection();

            // don't belive keyword. --;;
            setMetaData(searchKey, userDB, conn.getMetaData());
        }

    } catch (Exception e) {
        //            String strAddReqInfo = "";
        //            try {
        //               strAddReqInfo = RequestInfoUtils.requestInfo("db connection exception ", SessionManager.getEMAIL());
        //            } catch(Exception ee) {
        //               logger.error("request error", ee);
        //            }
        logger.error("===\n get DB Instance \n seq is " + userDB.getSeq() + "\n", e);

        dbManager.remove(searchKey);

        throw new TadpoleSQLManagerException(e);
    } finally {
        if (conn != null)
            try {
                conn.close();
            } catch (Exception e) {
            }
    }
    //      }

    return sqlMapClient;
}

From source file:com.manydesigns.portofino.actions.admin.ConnectionProvidersAction.java

protected void configureEditSchemas() {
    try {/*from w w  w. ja  va2s.  co m*/
        Connection conn = connectionProvider.acquireConnection();
        logger.debug("Reading database metadata");
        DatabaseMetaData metadata = conn.getMetaData();
        List<String> schemaNamesFromDb = connectionProvider.getDatabasePlatform().getSchemaNames(metadata);
        connectionProvider.releaseConnection(conn);

        List<Schema> selectedSchemas = connectionProvider.getDatabase().getSchemas();

        selectableSchemas = new ArrayList<SelectableSchema>(schemaNamesFromDb.size());
        for (String schemaName : schemaNamesFromDb) {
            boolean selected = false;
            for (Schema schema : selectedSchemas) {
                if (schemaName.equalsIgnoreCase(schema.getSchemaName())) {
                    selected = true;
                    break;
                }
            }
            SelectableSchema schema = new SelectableSchema(schemaName, selected);
            selectableSchemas.add(schema);
        }
        schemasForm = new TableFormBuilder(SelectableSchema.class).configFields("selected", "schemaName")
                .configMode(Mode.EDIT).configNRows(selectableSchemas.size()).build();
        schemasForm.readFromObject(selectableSchemas);
    } catch (Exception e) {
        logger.error("Coulnd't read schema names from db", e);
    }
}

From source file:com.l2jfree.sql.L2DataSource.java

/**
 * Obtains <TT>CURRENT_DATABASE</TT> and <TT>CURRENT_SCHEMA</TT> on an arbitrary DBMS.
 * /*from www  .ja  va2 s .c om*/
 * @throws SQLException if a SQL error occurs
 */
public final void initSQLContext() throws SQLException {
    Connection con = null;

    // remove leftover tables
    try {
        con = getConnection();

        DatabaseMetaData dmd = con.getMetaData();

        final List<String> tables = new ArrayList<String>();
        {
            final ResultSet rs = dmd.getTables(null, null, "_zzz%", BASE_TABLE);
            while (rs.next())
                tables.add(rs.getString(3));
            rs.close();
        }

        int removed = 0;
        final Statement s = con.createStatement();
        for (String table : tables) {
            try {
                s.executeUpdate("DROP TABLE " + table);
                removed++;
            } catch (SQLException e) {
                // table is owned by another user
            }
        }
        s.close();

        if (removed > 0)
            _log.info("Removed " + removed + " temporary tables.");
    } finally {
        L2Database.close(con);
    }

    try {
        con = getConnection();

        // generate a random table name
        final String table = "_zzz" + Rnd.getString(5, Rnd.LOWER_CASE_LETTERS);
        // DO NOT LOOK IT UP NOW

        // attempt to create in current schema instead
        {
            final Statement s = con.createStatement();
            s.executeUpdate("CREATE TABLE " + table + " (x INT)");
            s.close();
        }

        // table did not exist in current schema, we can look it up now
        {
            DatabaseMetaData dmd = con.getMetaData();

            final ResultSet rs = dmd.getTables(null, null, table, BASE_TABLE);
            if (rs.next()) {
                _database = rs.getString(1);
                _schema = rs.getString(2);
            } else
                throw new SQLException("Anomaly/Malfunction."); // should never happen

            if (rs.next())
                throw new SQLException("Please try again later."); // should never happen

            rs.close();
        }

        // remove table
        {
            final Statement s = con.createStatement();
            s.executeUpdate("DROP TABLE " + table);
            s.close();
        }
    } finally {
        L2Database.close(con);
    }
}

From source file:com.norconex.collector.core.data.store.impl.jdbc.JDBCCrawlDataStore.java

private boolean ensureTablesExist() throws SQLException {
    ArrayListHandler arrayListHandler = new ArrayListHandler();
    Connection conn = null;
    try {/*from ww w . j a  va 2  s .  co  m*/
        conn = datasource.getConnection();
        List<Object[]> tables = arrayListHandler
                .handle(conn.getMetaData().getTables(null, null, null, new String[] { "TABLE" }));
        if (tables.size() == NUMBER_OF_TABLES) {
            LOG.debug("    Re-using existing tables.");
            return true;
        }
    } finally {
        DbUtils.closeQuietly(conn);
    }
    LOG.debug("    Creating new crawl tables...");
    sqlCreateTable(TABLE_QUEUE);
    sqlCreateTable(TABLE_ACTIVE);
    sqlCreateTable(TABLE_PROCESSED_VALID);
    sqlCreateTable(TABLE_PROCESSED_INVALID);
    sqlCreateTable(TABLE_CACHE);
    return false;
}

From source file:de.ufinke.cubaja.sql.Database.java

/**
 * Uses existing connection with specific configuration attributes.
 * @param connection/* w  w  w.ja  va  2  s . c om*/
 * @param config
 * @throws SQLException
 */
public Database(Connection connection, DatabaseConfig config) throws SQLException {

    if (config.isLog()) {

        myId = getId();
        logger = LogFactory.getLog(Database.class);

        String url = connection.getMetaData().getURL();
        String user = connection.getMetaData().getUserName();
        if (user == null) {
            logger.debug(text.get("connectedNoUser", myId, url));
        } else {
            logger.debug(text.get("connectedUser", myId, url, user));
        }
    }

    this.connection = connection;
    this.config = config;

    connection.setAutoCommit(config.isAutoCommit());
    if (config.getTransactionIsolation() != null) {
        connection.setTransactionIsolation(config.getTransactionIsolation().getLevel());
    }

    if (config.getExecute() != null) {
        execute(config.getExecute());
    }
}

From source file:jp.co.tis.gsp.tools.dba.dialect.OracleDialect.java

@Override
public int guessType(Connection conn, String schema, String tableName, String colName) throws SQLException {
    if (metaData == null) {
        metaData = conn.getMetaData();
    }//w w w  .ja  v a2  s.c om

    ResultSet rs = null;
    try {
        rs = metaData.getColumns(null, normalizeSchemaName(schema), normalizeTableName(tableName),
                normalizeColumnName(colName));

        if (!rs.next()) {
            throw new SQLException(tableName + "?" + colName + "????");
        }
        String type = rs.getString("TYPE_NAME");
        if (!isUsableType(type)) {
            System.err.println("[WARN] " + tableName + "." + colName + "  " + type
                    + "???????");
            return UN_USABLE_TYPE;
        } else if ("VARCHAR2".equals(type) || "NVARCHAR2".equals(type) || "NCHAR".equals(type)) {
            return Types.VARCHAR;
        } else if ("DATE".equals(type)) { // Types.TIMESTAMP????
            return Types.DATE;
        } else if (type.startsWith("TIMESTAMP")) { // TIMESTAMP(6)?Types.OTHER????
            return Types.TIMESTAMP;
        }
        return rs.getInt("DATA_TYPE");
    } finally {
        if (rs != null) {
            rs.close();
        }
    }
}