Java Code Examples for java.sql.DatabaseMetaData#getSearchStringEscape()

@Override
protected String getEscapedPattern(DatabaseMetaData aMetaData, String aValue) throws SQLException {
	String thePrefix = aMetaData.getSearchStringEscape();

	//related to a bug in some PostgreSQL JDBC driver versions
	//see: http://archives.postgresql.org/pgsql-bugs/2007-03/msg00035.php
	if (thePrefix.length() > 1) {
		thePrefix = StringEscapeUtils.unescapeJava(thePrefix);
	}

	if (!StringUtils.isEmpty(thePrefix) && !StringUtils.isEmpty(aValue)) {
		aValue = aValue.replace("_", thePrefix + "_");
		aValue = aValue.replace("%", thePrefix + "%");
	}

	return aValue;
}
 

private ResultSet getTables(final Connection connection, final String schemaName)
        throws SQLException
{
    DatabaseMetaData metadata = connection.getMetaData();
    String escape = metadata.getSearchStringEscape();
    return metadata.getTables(
            connection.getCatalog(),
            escapeNamePattern(schemaName, escape),
            null,
            new String[] {"TABLE", "VIEW"});
}
 

private ResultSet getColumns(final String catalogName, final TableName tableHandle, final DatabaseMetaData metadata)
        throws SQLException
{
    String escape = metadata.getSearchStringEscape();
    return metadata.getColumns(
            catalogName,
            escapeNamePattern(tableHandle.getSchemaName(), escape),
            escapeNamePattern(tableHandle.getTableName(), escape),
            null);
}
 

@Override
protected ResultSet getTables(Connection connection, Optional<String> schemaName, Optional<String> tableName)
        throws SQLException
{
    DatabaseMetaData metadata = connection.getMetaData();
    String escape = metadata.getSearchStringEscape();
    return metadata.getTables(
            connection.getCatalog(),
            escapeNamePattern(schemaName, escape).orElse(null),
            escapeNamePattern(tableName, escape).orElse(null),
            getTableTypes());
}
 

protected String getEscapedPattern(DatabaseMetaData aMetaData, String aValue) throws SQLException {
	String thePrefix = aMetaData.getSearchStringEscape();
	if (!StringUtils.isEmpty(thePrefix) && !StringUtils.isEmpty(aValue)) {
		aValue = aValue.replace("_", thePrefix + "_");
		aValue = aValue.replace("%", thePrefix + "%");
	}

	return aValue;
}
 

protected String getTableNameFilter(DatabaseMetaData databaseMetaData, String databaseTablePrefix, String flowableTablePrefix) throws SQLException {
    String tableNameFilter = databaseTablePrefix + flowableTablePrefix + "_%";
    if ("postgres".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())
            || "cockroachdb".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
        tableNameFilter = databaseTablePrefix + flowableTablePrefix.toLowerCase(Locale.ROOT) + "_%";
    }
    if ("oracle".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
        tableNameFilter = databaseTablePrefix + flowableTablePrefix + databaseMetaData.getSearchStringEscape() + "_%";
    }

    return tableNameFilter;
}
 

public List<String> getTablesPresentInDatabase() {
    List<String> tableNames = new ArrayList<>();
    try {
        Connection connection = getDbSqlSession().getSqlSession().getConnection();
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        LOGGER.debug("retrieving activiti tables from jdbc metadata");
        String databaseTablePrefix = getDbSqlSession().getDbSqlSessionFactory().getDatabaseTablePrefix();
        String tableNameFilter = databaseTablePrefix + "ACT_%";
        if ("postgres".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
            tableNameFilter = databaseTablePrefix + "act_%";
        }
        if ("oracle".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
            tableNameFilter = databaseTablePrefix + "ACT" + databaseMetaData.getSearchStringEscape() + "_%";
        }

        String catalog = null;
        if (getProcessEngineConfiguration().getDatabaseCatalog() != null && getProcessEngineConfiguration().getDatabaseCatalog().length() > 0) {
            catalog = getProcessEngineConfiguration().getDatabaseCatalog();
        }

        String schema = null;
        if (getProcessEngineConfiguration().getDatabaseSchema() != null && getProcessEngineConfiguration().getDatabaseSchema().length() > 0) {
            schema = getProcessEngineConfiguration().getDatabaseSchema();
        }

        try (ResultSet tables = databaseMetaData.getTables(catalog, schema, tableNameFilter, getDbSqlSession().JDBC_METADATA_TABLE_TYPES)) {
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                tableName = tableName.toUpperCase();
                tableNames.add(tableName);
                LOGGER.debug("retrieved activiti table name {}", tableName);
            }
        }
    } catch (Exception e) {
        throw new ActivitiException("couldn't get activiti table names using metadata: " + e.getMessage(), e);
    }
    return tableNames;
}
 

/**
 * Creates a new analyzer for the database described by given metadata.
 *
 * @param  source     the data source, usually given by user at {@code SQLStore} creation time.
 * @param  metadata   Value of {@code source.getConnection().getMetaData()}.
 * @param  listeners  Value of {@code SQLStore.listeners}.
 * @param  locale     Value of {@code SQLStore.getLocale()}.
 */
Analyzer(final DataSource source, final DatabaseMetaData metadata, final StoreListeners listeners,
         final Locale locale) throws SQLException
{
    this.source      = source;
    this.metadata    = metadata;
    this.listeners   = listeners;
    this.locale      = locale;
    this.strings     = new HashMap<>();
    this.escape      = metadata.getSearchStringEscape();
    this.functions   = new SpatialFunctions(metadata);
    this.nameFactory = DefaultFactories.forBuildin(NameFactory.class);
    /*
     * The following tables are defined by ISO 19125 / OGC Simple feature access part 2.
     * Note that the standard specified those names in upper-case letters, which is also
     * the default case specified by the SQL standard.  However some databases use lower
     * cases instead.
     */
    String crs  = "SPATIAL_REF_SYS";
    String geom = "GEOMETRY_COLUMNS";
    if (metadata.storesLowerCaseIdentifiers()) {
        crs  = crs .toLowerCase(Locale.US).intern();
        geom = geom.toLowerCase(Locale.US).intern();
    }
    ignoredTables = new HashSet<>(4);
    ignoredTables.add(crs);
    ignoredTables.add(geom);
    final Dialect dialect = Dialect.guess(metadata);
    if (dialect == Dialect.POSTGRESQL) {
        ignoredTables.add("geography_columns");     // Postgis 1+
        ignoredTables.add("raster_columns");        // Postgis 2
        ignoredTables.add("raster_overviews");
    }
    /*
     * Information to be collected during table analysis.
     */
    tables   = new HashMap<>();
    warnings = new LinkedHashSet<>();
}
 

public List<String> getTablesPresentInDatabase() {
  List<String> tableNames = new ArrayList<String>();
  Connection connection = null;
  try {
    connection = getDbSqlSession().getSqlSession().getConnection();
    DatabaseMetaData databaseMetaData = connection.getMetaData();
    ResultSet tables = null;
    try {
      log.debug("retrieving activiti tables from jdbc metadata");
      String databaseTablePrefix = getDbSqlSession().getDbSqlSessionFactory().getDatabaseTablePrefix();
      String tableNameFilter = databaseTablePrefix+"ACT_%";
      if ("postgres".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
        tableNameFilter = databaseTablePrefix+"act_%";
      }
      if ("oracle".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
        tableNameFilter = databaseTablePrefix+"ACT" + databaseMetaData.getSearchStringEscape() + "_%";
      }
      
      String catalog = null;
      if (getProcessEngineConfiguration().getDatabaseCatalog() != null && getProcessEngineConfiguration().getDatabaseCatalog().length() > 0) {
        catalog = getProcessEngineConfiguration().getDatabaseCatalog();
      }
      
      String schema = null;
      if (getProcessEngineConfiguration().getDatabaseSchema() != null && getProcessEngineConfiguration().getDatabaseSchema().length() > 0) {
        schema = getProcessEngineConfiguration().getDatabaseSchema();
      }
      
      tables = databaseMetaData.getTables(catalog, schema, tableNameFilter, getDbSqlSession().JDBC_METADATA_TABLE_TYPES);
      while (tables.next()) {
        String tableName = tables.getString("TABLE_NAME");
        tableName = tableName.toUpperCase();
        tableNames.add(tableName);
        log.debug("  retrieved activiti table name {}", tableName);
      }
    } finally {
      tables.close();
    }
  } catch (Exception e) {
    throw new ActivitiException("couldn't get activiti table names using metadata: "+e.getMessage(), e); 
  }
  return tableNames;
}
 

@Override
public List<String> getTablesPresentInDatabase() {
  List<String> tableNames = new ArrayList<String>();
  Connection connection = null;
  try {
    connection = getDbSqlSession().getSqlSession().getConnection();
    DatabaseMetaData databaseMetaData = connection.getMetaData();
    ResultSet tables = null;
    try {
      log.debug("retrieving activiti tables from jdbc metadata");
      String databaseTablePrefix = getDbSqlSession().getDbSqlSessionFactory().getDatabaseTablePrefix();
      String tableNameFilter = databaseTablePrefix + "ACT_%";
      if ("postgres".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
        tableNameFilter = databaseTablePrefix + "act_%";
      }
      if ("oracle".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
        tableNameFilter = databaseTablePrefix + "ACT" + databaseMetaData.getSearchStringEscape() + "_%";
      }
      
      String catalog = null;
      if (getProcessEngineConfiguration().getDatabaseCatalog() != null && getProcessEngineConfiguration().getDatabaseCatalog().length() > 0) {
        catalog = getProcessEngineConfiguration().getDatabaseCatalog();
      }
      
      String schema = null;
      if (getProcessEngineConfiguration().getDatabaseSchema() != null && getProcessEngineConfiguration().getDatabaseSchema().length() > 0) {
        if ("oracle".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
          schema = getProcessEngineConfiguration().getDatabaseSchema().toUpperCase();
        } else {
          schema = getProcessEngineConfiguration().getDatabaseSchema();
        }
      }
      
      tables = databaseMetaData.getTables(catalog, schema, tableNameFilter, DbSqlSession.JDBC_METADATA_TABLE_TYPES);
      while (tables.next()) {
        String tableName = tables.getString("TABLE_NAME");
        tableName = tableName.toUpperCase();
        tableNames.add(tableName);
        log.debug("  retrieved activiti table name {}", tableName);
      }
    } finally {
      tables.close();
    }
  } catch (Exception e) {
    throw new ActivitiException("couldn't get activiti table names using metadata: " + e.getMessage(), e);
  }
  return tableNames;
}
 

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

/**
 * Creates a new {@code SQLBuilder} initialized from the given database metadata.
 *
 * @param  metadata     the database metadata.
 * @param  quoteSchema  whether the schema name should be written between quotes.
 * @throws SQLException if an error occurred while fetching the database metadata.
 */
public SQLBuilder(final DatabaseMetaData metadata, final boolean quoteSchema) throws SQLException {
    dialect = Dialect.guess(metadata);
    quote   = metadata.getIdentifierQuoteString();
    escape  = metadata.getSearchStringEscape();
    this.quoteSchema = quoteSchema;
}