Java Code Examples for java.sql.Connection#getCatalog()

@Test
@Ignore
public void test300ConnectionsWithSingleClientInstance() throws SQLException
{
  // concurrent testing
  int size = 300;
  Connection con = getConnection();
  String database = con.getCatalog();
  String schema = con.getSchema();
  con.createStatement().execute("create or replace table bigTable(rowNum number,rando " +
                                "number) as (select seq4()," +
                                "uniform(1, 10, random()) from table(generator(rowcount=>10000000)) v)");
  con.createStatement().execute("create or replace table conTable(colA number)");

  ExecutorService taskRunner = Executors.newFixedThreadPool(size);
  for (int i = 0; i < size; i++)
  {
    ConcurrentConnections newTask = new ConcurrentConnections();
    taskRunner.submit(newTask);
  }
  assertEquals(null, errorMessage);
  taskRunner.shutdownNow();
}
 

/**
 * Wrapper for {@link Connection#getCatalog()} to solve problems with RDBMs for which catalog and schema are the same
 * thing. For these RDBMs, it returns the schema name when it is not-null and not-empty; otherwise, it returns the
 * value of java.sql.Connection.getCatalog(). For other RDBMs, it returns always the value of
 * java.sql.Connection.getCatalog().
 *
 * @param connection An open JDBC connection
 * @param schema The schema name we want to use
 * @return The current catalog or the schema name passed as argument
 * @throws SQLException
 */
private String getCatalog(Connection connection, String schema) throws SQLException {
  if (Strings.isNullOrEmpty(schema)) {
    return connection.getCatalog();
  }

  String name = connection.getMetaData().getDatabaseProductName().toLowerCase();

  for (String d : RDBMS_WITHOUT_SCHEMAS) {
    if (name.contains(d)) {
      return schema;
    }
  }

  return connection.getCatalog();
}
 

public TesterPreparedStatement(final Connection conn, final String sql, final String columnNames[]) {
    super(conn);
    _sql = sql;
    _columnNames = columnNames;
    try {
        _catalog = conn.getCatalog();
    } catch (final SQLException e) {
        // Ignored
    }
}
 

@Test public void testRemoteConnectionProperties() throws Exception {
  ConnectionSpec.getDatabaseLock().lock();
  try (AvaticaConnection conn = (AvaticaConnection) DriverManager.getConnection(url)) {
    String id = conn.id;
    final Map<String, ConnectionPropertiesImpl> m = ((RemoteMeta) getMeta(conn)).propsMap;
    assertFalse("remote connection map should start ignorant", m.containsKey(id));
    // force creating a connection object on the remote side.
    try (final Statement stmt = conn.createStatement()) {
      assertTrue("creating a statement starts a local object.", m.containsKey(id));
      assertTrue(stmt.execute("select count(1) from EMP"));
    }
    Connection remoteConn = getConnection(FullyRemoteJdbcMetaFactory.getInstance(), id);
    final boolean defaultRO = remoteConn.isReadOnly();
    final boolean defaultAutoCommit = remoteConn.getAutoCommit();
    final String defaultCatalog = remoteConn.getCatalog();
    final String defaultSchema = remoteConn.getSchema();
    conn.setReadOnly(!defaultRO);
    assertTrue("local changes dirty local state", m.get(id).isDirty());
    assertEquals("remote connection has not been touched", defaultRO, remoteConn.isReadOnly());
    conn.setAutoCommit(!defaultAutoCommit);
    assertEquals("remote connection has not been touched",
        defaultAutoCommit, remoteConn.getAutoCommit());

    // further interaction with the connection will force a sync
    try (final Statement stmt = conn.createStatement()) {
      assertEquals(!defaultAutoCommit, remoteConn.getAutoCommit());
      assertFalse("local values should be clean", m.get(id).isDirty());
    }
  } finally {
    ConnectionSpec.getDatabaseLock().unlock();
  }
}
 

public TesterPreparedStatement(final Connection conn, final String sql, final int columnIndexes[]) {
    super(conn);
    _sql = sql;
    _columnIndexes = columnIndexes;
    try {
        _catalog = conn.getCatalog();
    } catch (final SQLException e) {
        // Ignored
    }
}
 

/** Returns a pair of (catalog, schema) for the current connection. */
private Pair<String, String> getCatalogSchema(Connection connection)
    throws SQLException {
  final DatabaseMetaData metaData = connection.getMetaData();
  final List<Integer> version41 = ImmutableList.of(4, 1); // JDBC 4.1
  String catalog = this.catalog;
  String schema = this.schema;
  final boolean jdbc41OrAbove =
      VERSION_ORDERING.compare(version(metaData), version41) >= 0;
  if (catalog == null && jdbc41OrAbove) {
    // From JDBC 4.1, catalog and schema can be retrieved from the connection
    // object, hence try to get it from there if it was not specified by user
    catalog = connection.getCatalog();
  }
  if (schema == null && jdbc41OrAbove) {
    schema = connection.getSchema();
    if ("".equals(schema)) {
      schema = null; // PostgreSQL returns useless "" sometimes
    }
  }
  if ((catalog == null || schema == null)
      && metaData.getDatabaseProductName().equals("PostgreSQL")) {
    final String sql = "select current_database(), current_schema()";
    try (Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery(sql)) {
      if (resultSet.next()) {
        catalog = resultSet.getString(1);
        schema = resultSet.getString(2);
      }
    }
  }
  return Pair.of(catalog, schema);
}
 

/**
 * Helper method to return the current catalog for a connection.
 * 
 * @param conn  The connection
 * @return The current catalog or null if there is none.
 */
protected String getCurrentCatalog(Connection conn) {
    
    try {
        
        return conn.getCatalog();
    }
    catch (SQLException e) {
        
        /* IGNORED */
    }
    
    return null;
}
 

TransferDb(Connection c, Traceable t) throws DataAccessPointException {

        super(t);

        conn = c;

        if (c != null) {
            String productLowerName;

            try {
                meta              = c.getMetaData();
                databaseToConvert = c.getCatalog();
                productLowerName  = meta.getDatabaseProductName();

                if (productLowerName == null) {
                    productLowerName = "";
                } else {
                    productLowerName = productLowerName.toLowerCase();
                }

                helper = HelperFactory.getHelper(productLowerName);

                helper.set(this, t, meta.getIdentifierQuoteString());
            } catch (SQLException e) {
                throw new DataAccessPointException(e.toString());
            }
        }
    }
 

private Subsegment createSubsegment() {
    try {
        Connection connection = delegate.getConnection();
        DatabaseMetaData metadata = connection.getMetaData();
        String subsegmentName = DEFAULT_DATABASE_NAME;
        try {
            URI normalizedUri = new URI(new URI(metadata.getURL()).getSchemeSpecificPart());
            subsegmentName = connection.getCatalog() + "@" + normalizedUri.getHost();
        } catch (URISyntaxException e) {
            logger.warn("Unable to parse database URI. Falling back to default '" + DEFAULT_DATABASE_NAME
                        + "' for subsegment name.", e);
        }

        Subsegment subsegment = AWSXRay.beginSubsegment(subsegmentName);
        if (subsegment == null) {
            return null;
        }

        subsegment.setNamespace(Namespace.REMOTE.toString());
        Map<String, Object> sqlParams = new HashMap<>();
        sqlParams.put(URL, metadata.getURL());
        sqlParams.put(USER, metadata.getUserName());
        sqlParams.put(DRIVER_VERSION, metadata.getDriverVersion());
        sqlParams.put(DATABASE_TYPE, metadata.getDatabaseProductName());
        sqlParams.put(DATABASE_VERSION, metadata.getDatabaseProductVersion());
        subsegment.putAllSql(sqlParams);

        return subsegment;
    } catch (SQLException exception) {
        logger.warn("Failed to create X-Ray subsegment for the statement execution.", exception);
        return null;
    }
}
 

/**
 * 获得数据库的表名
 * 
 * @param config
 * @return
 * @throws Exception
 */
public static List<String> getTableNames(DatabaseConfig config) throws Exception {
	Connection conn = getConnection(config);
	List<String> tables = new ArrayList<>();
	ResultSet rs;
	if (config.getDbType().equalsIgnoreCase(Constant.SQL_SERVER)) {
		// 如果是sqlserver数据库通过查询获得所有表跟视图
		String sql = "select name from sysobjects where UPPER(xtype)='U' or UPPER(xtype)='V'";
		rs = conn.createStatement().executeQuery(sql);
		while (rs.next()) {
			tables.add(rs.getString("name"));
		}

	} else {
		// 如果非sqlserver类型的数据库通过JDBC获得所有表跟视图
		DatabaseMetaData md = conn.getMetaData();
		String[] types = {"TABLE", "VIEW"};
		if (config.getDbType().equalsIgnoreCase(Constant.POSTGRE_SQL)) {
			rs = md.getTables(null, null, null, types);
		} else {
			String catalog = conn.getCatalog() == null ? null : conn.getCatalog();
			rs = md.getTables(catalog, config.getUserName().toUpperCase(), "%%", types);
		}
		while (rs.next()) {
			tables.add(rs.getString(3));
		}
	}
	return tables;
}
 

private void extractMetadata(Transaction transaction) {
    try {
        Connection connection = transaction.getConnection();
        metadata = connection.getMetaData();
        catalog = connection.getCatalog();
    } catch (SQLException exception) {
        throw new SchemaInspectionException("METADATA_EXTRACTION_ERROR", "Failed to obtain metadata from connection.", exception);
    }
}
 

public static List<String> getTablePK(String table) throws SQLException {
    List<String> res = new ArrayList();
    Connection conn = getConnection();
    String catalog = conn.getCatalog();
    DatabaseMetaData metaData = conn.getMetaData();
    ResultSet rs = null;
    rs = metaData.getPrimaryKeys(catalog, null, table);

    while (rs.next()) {
        res.add(rs.getString("COLUMN_NAME"));
    }

    closeAll(conn, null, rs);
    return res;
}
 

public TesterPreparedStatement(final Connection conn, final String sql) {
    super(conn);
    _sql = sql;
    try {
        _catalog = conn.getCatalog();
    } catch (final SQLException e) {
        // Ignored
    }
}
 

/**
 * 获得指定表的属性
 * 
 * @param config
 * @param tableName
 * @return
 * @throws Exception
 */
public static TableContent getTableAttribute(DatabaseConfig config, String tableName) throws Exception {
	Connection conn = getConnection(config);
	TableContent content = new TableContent();
	ResultSet rs;
	DatabaseMetaData md = conn.getMetaData();
	String[] types = {"TABLE", "VIEW"};
	if (config.getDbType().equalsIgnoreCase(Constant.POSTGRE_SQL)) {
		rs = md.getTables(null, null, tableName, types);
	} else {
		String catalog = conn.getCatalog() == null ? null : conn.getCatalog();
		rs = md.getTables(catalog, config.getUserName().toUpperCase(), tableName, types);
	}
	if (rs.next()) {
		try {
			content.setTableCat(rs.getString("TABLE_CAT"));
			content.setTableSchem(rs.getString("TABLE_SCHEM"));
			content.setTableName(rs.getString("TABLE_NAME"));
			content.setTableType(rs.getString("TABLE_TYPE"));
			content.setRemarks(rs.getString("REMARKS"));
			content.setTypeCat(rs.getString("TYPE_CAT"));
			content.setTypeSchem(rs.getString("TYPE_SCHEM"));
			content.setTypeName(rs.getString("TYPE_NAME"));
			content.setSelfReferencingColName(rs.getString("SELF_REFERENCING_COL_NAME"));
			content.setRefGeneration(rs.getString("REF_GENERATION"));
		} catch (Exception e) {
			LOG.error("部分属性获取失败:", e);
		}
	}
	return content;
}
 

@Test
public void testGetStringValueFromColumnDef() throws SQLException
{
  Map<String, String> params = getConnectionParameters();
  Properties properties = new Properties();
  for (Map.Entry<?, ?> entry : params.entrySet())
  {
    if (entry.getValue() != null)
    {
      properties.put(entry.getKey(), entry.getValue());
    }
  }
  // test out connection parameter stringsQuoted to remove strings from quotes
  properties.put("stringsQuotedForColumnDef", "true");
  Connection connection = DriverManager.getConnection(params.get("uri"), properties);
  String database = connection.getCatalog();
  String schema = connection.getSchema();
  final String targetTable = "T0";

  connection.createStatement().execute(
      "create or replace table " + targetTable +
      "(C1 string, C2 string default '', C3 string default 'apples', C4 string default '\"apples\"', C5 int, C6 " +
      "int default 5, C7 string default '''', C8 string default '''apples''''', C9  string default '%')");

  DatabaseMetaData metaData = connection.getMetaData();

  ResultSet resultSet = metaData.getColumns(database, schema, targetTable, "%");
  assertTrue(resultSet.next());
  assertEquals(null, resultSet.getString("COLUMN_DEF"));
  assertTrue(resultSet.next());
  assertEquals("''", resultSet.getString("COLUMN_DEF"));
  assertTrue(resultSet.next());
  assertEquals("'apples'", resultSet.getString("COLUMN_DEF"));
  assertTrue(resultSet.next());
  assertEquals("'\"apples\"'", resultSet.getString("COLUMN_DEF"));
  assertTrue(resultSet.next());
  assertEquals(null, resultSet.getString("COLUMN_DEF"));
  assertTrue(resultSet.next());
  assertEquals("5", resultSet.getString("COLUMN_DEF"));
  assertTrue(resultSet.next());
  assertEquals("''''", resultSet.getString("COLUMN_DEF"));
  assertTrue(resultSet.next());
  assertEquals("'''apples'''''", resultSet.getString("COLUMN_DEF"));
  assertTrue(resultSet.next());
  assertEquals("'%'", resultSet.getString("COLUMN_DEF"));


}
 

@Override
public String getCatalog(Connection conn) throws SQLException {
	return conn.getCatalog();
}
 

@Override
public String getCatalog(Connection conn) throws SQLException {
	return conn.getCatalog();
}
 

@Override
public String getCatalog(Connection conn) throws SQLException {
	return conn.getCatalog();
}
 

public static List<ForeignKey> loadForeignKeys(AccessConfiguration accessConfiguration) {
    Map<String, ForeignKey> foreignKeyMap = new HashMap<String, ForeignKey>();
    String schemaName = accessConfiguration.getSchemaName();
    Connection connection = null;
    ResultSet tableResultSet = null;
    try {
        if (logger.isDebugEnabled()) logger.debug("Loading foreign keys: " + accessConfiguration);
        connection = QueryManager.getConnection(accessConfiguration);
        String catalog = connection.getCatalog();
        if (catalog == null) {
            catalog = accessConfiguration.getUri();
            if (logger.isDebugEnabled()) logger.debug("Catalog is null. Catalog name will be: " + catalog);
        }
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        tableResultSet = databaseMetaData.getTables(catalog, schemaName, null, new String[]{"TABLE"});
        while (tableResultSet.next()) {
            String tableName = tableResultSet.getString("TABLE_NAME");
            if (logger.isDebugEnabled()) logger.debug("Searching foreign keys. ANALYZING TABLE  = " + tableName);
            ResultSet resultSet = databaseMetaData.getImportedKeys(catalog, null, tableName);
            while (resultSet.next()) {
                String fkeyName = resultSet.getString("FK_NAME");
                String pkTableName = resultSet.getString("PKTABLE_NAME");
                String pkColumnName = resultSet.getString("PKCOLUMN_NAME");
                String keyPrimaryKey = pkTableName + "." + pkColumnName;
                String fkTableName = resultSet.getString("FKTABLE_NAME");
                String fkColumnName = resultSet.getString("FKCOLUMN_NAME");
                String keyForeignKey = fkTableName + "." + fkColumnName;
                if (logger.isDebugEnabled()) logger.debug("Analyzing Primary Key: " + keyPrimaryKey + " Found a Foreign Key: " + fkColumnName + " in table " + fkTableName);
                if (logger.isDebugEnabled()) logger.debug("Analyzing foreign key: " + keyForeignKey + " references " + keyPrimaryKey);
                addFKToMap(foreignKeyMap, fkeyName, new AttributeRef(pkTableName, pkColumnName), new AttributeRef(fkTableName, fkColumnName));
            }
        }
    } catch (DAOException daoe) {
        throw new DBMSException("Error connecting to database.\n" + accessConfiguration + "\n" + daoe.getLocalizedMessage());
    } catch (SQLException sqle) {
        throw new DBMSException("Error connecting to database.\n" + accessConfiguration + "\n" + sqle.getLocalizedMessage());
    } finally {
        QueryManager.closeResultSet(tableResultSet);
        QueryManager.closeConnection(connection);
    }
    return new ArrayList<ForeignKey>(foreignKeyMap.values());
}
 

public ConnectionPropertiesImpl(Connection conn) throws SQLException {
  this(conn.getAutoCommit(), conn.isReadOnly(), conn.getTransactionIsolation(),
      conn.getCatalog(), conn.getSchema());
}