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

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

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

/** 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);
}
 

/**
 * Get schema.
 *
 * @param connection connection
 * @param databaseType database type
 * @return schema
 */
public static String getSchema(final Connection connection, final String databaseType) {
    String result = null;
    try {
        if ("Oracle".equals(databaseType)) {
            return null;
        }
        result = connection.getSchema();
    } catch (final SQLException ignore) {
    }
    return result;
}
 

private static String discoverSchema(final Connection connection) {
    try {
        return connection.getSchema();
    } catch (final SQLException e) {
        return null;
    }
}
 

@BeforeClass
public static void setUpClass() throws Throwable
{
  Connection testConnection = AbstractDriverIT.getConnection();
  // NOTE: the stage object must be created right after the connection
  // because the Loader API assumes the stage object exists in the default
  // namespace of the connection.
  testConnection.createStatement().execute(
      String.format("CREATE OR REPLACE STAGE %s", TARGET_STAGE));
  TARGET_SCHEMA = testConnection.getSchema();
  TARGET_DB = testConnection.getCatalog();
}
 

TestDataConfigBuilder(Connection testConnection, Connection putConnection) throws Exception
{
  this.testConnection = testConnection;
  this.putConnection = putConnection;
  this.databaseName = testConnection.getCatalog();
  this.schemaName = testConnection.getSchema();
}
 

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

/** 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);
}
 

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

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

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

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

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

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

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


}
 

@Test
public void testHandlingSpecialChars() throws SQLException
{
  Connection connection = getConnection();
  String database = connection.getCatalog();
  String schema = connection.getSchema();
  DatabaseMetaData metaData = connection.getMetaData();
  Statement statement = connection.createStatement();
  String escapeChar = metaData.getSearchStringEscape();
  // test getColumns with escaped special characters in table name
  statement.execute("create or replace table \"TEST\\1\\_1\" (\"C%1\" integer,\"C\\1\\\\11\" integer)");
  statement.execute("INSERT INTO \"TEST\\1\\_1\" (\"C%1\",\"C\\1\\\\11\") VALUES (0,0)");
  // test getColumns with escaped special characters in schema and table name
  statement.execute("create or replace schema \"SPECIAL%_\\SCHEMA\"");
  statement.execute("create or replace table \"SPECIAL%_\\SCHEMA\".\"TEST_1_1\" ( \"RNUM\" integer not null, " +
                    "\"C21\" integer," +
                    "\"C11\" integer,\"C%1\" integer,\"C\\1\\\\11\" integer , primary key (\"RNUM\"))");
  statement.execute("INSERT INTO \"TEST_1_1\" (RNUM,C21,C11,\"C%1\",\"C\\1\\\\11\") VALUES (0,0,0,0,0)");
  String escapedTable1 = "TEST" + escapeChar + "\\1" + escapeChar + "\\" + escapeChar + "_1";
  ResultSet resultSet = metaData.getColumns(database, schema, escapedTable1, null);
  assertTrue(resultSet.next());
  assertEquals("C%1", resultSet.getString("COLUMN_NAME"));
  assertTrue(resultSet.next());
  assertEquals("C\\1\\\\11", resultSet.getString("COLUMN_NAME"));
  assertFalse(resultSet.next());

  // Underscore can match to any character, so check that table comes back when underscore is not escaped.
  String partiallyEscapedTable1 = "TEST" + escapeChar + "\\1" + escapeChar + "\\_1";
  resultSet = metaData.getColumns(database, schema, partiallyEscapedTable1, null);
  assertTrue(resultSet.next());
  assertEquals("C%1", resultSet.getString("COLUMN_NAME"));
  assertTrue(resultSet.next());
  assertEquals("C\\1\\\\11", resultSet.getString("COLUMN_NAME"));
  assertFalse(resultSet.next());

  String escapedTable2 = "TEST" + escapeChar + "_1" + escapeChar + "_1";
  String escapedSchema = "SPECIAL%" + escapeChar + "_" + escapeChar + "\\SCHEMA";
  resultSet = metaData.getColumns(database, escapedSchema, escapedTable2, null);
  assertTrue(resultSet.next());
  assertEquals("RNUM", resultSet.getString("COLUMN_NAME"));
  assertTrue(resultSet.next());
  assertEquals("C21", resultSet.getString("COLUMN_NAME"));
  assertTrue(resultSet.next());
  assertEquals("C11", resultSet.getString("COLUMN_NAME"));
  assertTrue(resultSet.next());
  assertEquals("C%1", resultSet.getString("COLUMN_NAME"));
  assertTrue(resultSet.next());
  assertEquals("C\\1\\\\11", resultSet.getString("COLUMN_NAME"));
  assertFalse(resultSet.next());

  // test getTables with real special characters and escaped special characters. Unescaped _ should allow both
  // tables to be returned, while escaped _ should match up to the _ in both table names.
  statement.execute("create or replace table " + schema + ".\"TABLE_A\" (colA string)");
  statement.execute("create or replace table " + schema + ".\"TABLE_B\" (colB number)");
  String escapedTable = "TABLE" + escapeChar + "__";
  resultSet = metaData.getColumns(database, schema, escapedTable, null);
  assertTrue(resultSet.next());
  assertEquals("COLA", resultSet.getString("COLUMN_NAME"));
  assertTrue(resultSet.next());
  assertEquals("COLB", resultSet.getString("COLUMN_NAME"));
  assertFalse(resultSet.next());

  resultSet = metaData.getColumns(database, schema, escapedTable, "COLB");
  assertTrue(resultSet.next());
  assertEquals("COLB", resultSet.getString("COLUMN_NAME"));
  assertFalse(resultSet.next());

  statement.execute("create or replace table " + schema + ".\"special%table\" (colA string)");
  resultSet = metaData.getColumns(database, schema, "special" + escapeChar + "%table", null);
  assertTrue(resultSet.next());
  assertEquals("COLA", resultSet.getString("COLUMN_NAME"));
}
 

@Override
public String resolveSchemaName(Connection connection, Dialect dialect) throws SQLException {
	return connection.getSchema();
}
 

/**
 * Delegates to {@link Connection#getSchema()} without throwing a {@link AbstractMethodError}.
 * <p>
 * If the JDBC driver does not implement {@link Connection#getSchema()}, then return null.
 * </p>
 *
 * @param connection
 *            the receiver
 * @return null for a JDBC 4 driver or a value per {@link Connection#getSchema()}.
 * @throws SQLException
 *             See {@link Connection#getSchema()}.
 * @see Connection#getSchema()
 */
public static String getSchema(final Connection connection) throws SQLException {
    try {
        return connection.getSchema();
    } catch (final AbstractMethodError e) {
        // do nothing
        return null;
    }
}
 

/**
 * Delegates to {@link Connection#getSchema()} without throwing an {@link AbstractMethodError}.
 * <p>
 * If the JDBC driver does not implement {@link Connection#getSchema()}, then return null.
 * </p>
 *
 * @param connection
 *            the receiver
 * @return null for a JDBC 4 driver or a value per {@link Connection#getSchema()}.
 * @throws SQLException
 *             See {@link Connection#getSchema()}.
 * @see Connection#getSchema()
 */
public static String getSchema(final Connection connection) throws SQLException {
    try {
        return connection.getSchema();
    } catch (final AbstractMethodError e) {
        // do nothing
        return null;
    }
}