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

The following examples show how to use java.sql.DatabaseMetaData#getPrimaryKeys() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: DatabaseMetaDataTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
/**
 * Helper method for testing getPrimaryKeys - calls dmd.getPrimaryKeys for
 * the JDBC call, and getPrimaryKeysODBC for the ODBC procedure
 * @throws SQLException
 */
private ResultSet[] getPrimaryKeys(
        String catalog, String schema, String table) throws SQLException
{
    ResultSet[] rss = new ResultSet[2];
    DatabaseMetaData dmd = getDMD();
    rss[0]= dmd.getPrimaryKeys(catalog, schema, table);
    rss[1]= getPrimaryKeysODBC(catalog, schema, table);

    String [] columnNames = {"TABLE_CAT","TABLE_SCHEM","TABLE_NAME",
            "COLUMN_NAME","KEY_SEQ","PK_NAME"};
    int [] columnTypes = {
            Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,
            Types.VARCHAR,Types.SMALLINT,Types.VARCHAR};
    boolean [] nullability = {false,false,false,false,true,false};
    assertMetaDataResultSet(rss[0], columnNames, columnTypes, nullability);
    assertMetaDataResultSet(rss[1], columnNames, columnTypes, nullability);

    return rss;
}
 
Example 2
Source File: JdbcThinMetadataPrimaryKeysSelfTest.java    From ignite with Apache License 2.0 6 votes vote down vote up
/**
 * Checks that field names in the metadata matches specified expected fields.
 *
 * @param tabName part of the sql query after CREATE TABLE TESTER.
 * @param expPKFields Expected primary key fields.
 */
private void checkPKFields(String tabName, String... expPKFields) throws Exception {
    try (Connection conn = DriverManager.getConnection(URL)) {
        DatabaseMetaData md = conn.getMetaData();

        ResultSet rs = md.getPrimaryKeys(conn.getCatalog(), null, tabName);

        List<String> colNames = new ArrayList<>();

        while (rs.next())
            colNames.add(rs.getString(COL_NAME_IDX));

        assertEquals("Field names in the primary key are not correct",
            Arrays.asList(expPKFields), colNames);
    }
}
 
Example 3
Source File: JdbcRegressionUnitTest.java    From cassandra-jdbc-wrapper with Apache License 2.0 6 votes vote down vote up
@Test
public void testIssue77() throws Exception
{
    DatabaseMetaData md = con.getMetaData();
    System.out.println();
    System.out.println("Test Issue #77");
    System.out.println("--------------");

    // test various retrieval methods
    ResultSet result = md.getPrimaryKeys(con.getCatalog(), KEYSPACE, TABLE);
    AssertJUnit.assertTrue("Make sure we have found an pk", result.next());

    // check the column name from index
    String cn = result.getString("COLUMN_NAME");
    AssertJUnit.assertEquals("Column name match for pk", "keyname", cn);
    System.out.println("Found pk via dmd :   " + cn);
}
 
Example 4
Source File: JDBCAdapter.java    From hana-native-adapters with Apache License 2.0 6 votes vote down vote up
private List<UniqueKey> getUniqueKeys(String tableName) throws SQLException{
	ArrayList<UniqueKey> uniqueKeys = new ArrayList<UniqueKey>();
	DatabaseMetaData meta = conn.getMetaData();
	ResultSet set = meta.getPrimaryKeys(conn.getCatalog(), null, tableName);
	HashMap<String,List<String>> map = new HashMap<String,List<String>>();
    while (set.next()) {
    	String indexName = set.getString("PK_NAME");
    	if (indexName==null)
    		continue;
    	String fieldName = set.getString("COLUMN_NAME");
    	if (!map.containsKey(indexName))
   			map.put(indexName, new ArrayList<String>());
    	map.get(indexName).add(fieldName);
    }
    for(String key: map.keySet())
    {
    	UniqueKey uniqueKey = new UniqueKey(key, map.get(key));
    	uniqueKey.setPrimary(true);
    	uniqueKeys.add(uniqueKey);
    }
    return uniqueKeys;
}
 
Example 5
Source File: metaDatabase.java    From openbd-core with GNU General Public License v3.0 6 votes vote down vote up
private static Map<String, metaColumn> getPrimaryKeys( Map<String, metaColumn> HT, DatabaseMetaData metaData, String _dbInst, String _tableName ){
	ResultSet RES;
	
	try{
		RES	= metaData.getPrimaryKeys( null, null, _tableName ); 
		while ( RES.next() ){
			String columnName = RES.getString(4);
			if ( HT.containsKey( columnName ) ){
				metaColumn mC	=	HT.get( columnName );
				mC.PRIMARYKEY	= true;
			}
		}
		RES.close();
					
		return HT;
	}catch(Exception E){
		return null;
	}
}
 
Example 6
Source File: MySQLTableRepository.java    From mybatis-dalgen with Apache License 2.0 6 votes vote down vote up
/**
 * Fill primary keys.
 *
 * @param connection the connection
 * @param tableName the table name
 * @param databaseMetaData the database meta data
 * @param table the table
 * @throws SQLException the sql exception
 */
private void fillPrimaryKeys(Connection connection, String tableName,
                             DatabaseMetaData databaseMetaData, Table table)
        throws SQLException {
    PrimaryKeys primaryKeys = null;

    ResultSet resultSet = databaseMetaData.getPrimaryKeys(connection.getCatalog(),
            connection.getSchema(), tableName);

    while (resultSet.next()) {
        for (Column column : table.getColumnList()) {
            if (StringUtils.equals(column.getSqlName(), Str(resultSet, "COLUMN_NAME"))) {
                primaryKeys = primaryKeys == null ? new PrimaryKeys() : primaryKeys;
                primaryKeys.addColumn(column);
                String pkName = resultSet.getString("PK_NAME");
                pkName = StringUtils.isBlank(pkName) ? column.getSqlName() : pkName;
                primaryKeys.setPkName(CamelCaseUtils.toCapitalizeCamelCase(pkName));
            }
        }
    }
    table.setPrimaryKeys(primaryKeys);
}
 
Example 7
Source File: Show.java    From jsqsh with Apache License 2.0 6 votes vote down vote up
private ResultSet doPrimaryKeys(Session session, Connection con, Options options)
    throws SQLException {
    
    if (options.arguments.size() != 3
        || !options.arguments.get(1).equalsIgnoreCase("keys")) {
        
        session.err.println("Use: \\show primary keys [[catalog.]schema.]table");
        return null;
    }
    
    SQLConnectionContext ctx = (SQLConnectionContext) session.getConnectionContext();
    SQLObjectName name = new SQLObjectName(ctx, options.arguments.get(2));
    
    DatabaseMetaData meta = con.getMetaData();
    return meta.getPrimaryKeys(
        (options.catalog != null ? options.catalog : name.getCatalog()),
        (options.schemaPattern != null ? options.schemaPattern : name.getSchema()),
        (options.tablePattern != null ? options.tablePattern : name.getName()));
}
 
Example 8
Source File: DbUtilities.java    From openemm with GNU Affero General Public License v3.0 6 votes vote down vote up
public static CaseInsensitiveSet getPrimaryKeyColumns(Connection connection, String tableName) throws SQLException {
	if (StringUtils.isBlank(tableName)) {
		return null;
	} else {
		try {
			if (checkDbVendorIsOracle(connection)) {
				tableName = tableName.toUpperCase();
			}

			DatabaseMetaData metaData = connection.getMetaData();
			try (ResultSet resultSet = metaData.getPrimaryKeys(connection.getCatalog(), null, tableName)) {
				CaseInsensitiveSet returnList = new CaseInsensitiveSet();
				while (resultSet.next()) {
					returnList.add(resultSet.getString("COLUMN_NAME"));
				}
				return returnList;
			}
		} catch (Exception e) {
			throw new RuntimeException("Cannot read primarykey columns for table " + tableName + ": " + e.getMessage(), e);
		}
	}
}
 
Example 9
Source File: PutDatabaseRecord.java    From nifi with Apache License 2.0 6 votes vote down vote up
public static TableSchema from(final Connection conn, final String catalog, final String schema, final String tableName,
                               final boolean translateColumnNames, final boolean includePrimaryKeys) throws SQLException {
    final DatabaseMetaData dmd = conn.getMetaData();

    try (final ResultSet colrs = dmd.getColumns(catalog, schema, tableName, "%")) {
        final List<ColumnDescription> cols = new ArrayList<>();
        while (colrs.next()) {
            final ColumnDescription col = ColumnDescription.from(colrs);
            cols.add(col);
        }

        final Set<String> primaryKeyColumns = new HashSet<>();
        if (includePrimaryKeys) {
            try (final ResultSet pkrs = dmd.getPrimaryKeys(catalog, null, tableName)) {

                while (pkrs.next()) {
                    final String colName = pkrs.getString("COLUMN_NAME");
                    primaryKeyColumns.add(normalizeColumnName(colName, translateColumnNames));
                }
            }
        }

        return new TableSchema(cols, translateColumnNames, primaryKeyColumns, dmd.getIdentifierQuoteString());
    }
}
 
Example 10
Source File: DbHelper.java    From crud-intellij-plugin with Apache License 2.0 6 votes vote down vote up
private List<Column> getAllColumn(String tableName) {
    Connection conn = getConnection(db);
    try {
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableName);
        String primaryKey = null;
        while (primaryKeys.next()) {
            primaryKey = primaryKeys.getString("COLUMN_NAME");
        }
        ResultSet rs = metaData.getColumns(null, null, tableName, null);
        List<Column> ls = new ArrayList<>();
        while (rs.next()) {
            String columnName = rs.getString("COLUMN_NAME");
            Column column = new Column(rs.getString("REMARKS"), columnName, rs.getInt("DATA_TYPE"));
            if (!StringUtils.isNullOrEmpty(primaryKey) && columnName.equals(primaryKey)) {
                column.setId(true);
            }
            ls.add(column);
        }
        return ls;
    } catch (SQLException e) {
        throw new RuntimeException(e.getMessage(), e);
    } finally {
        closeConnection(conn);
    }
}
 
Example 11
Source File: PhysicalModelInitializer.java    From Knowage-Server with GNU Affero General Public License v3.0 5 votes vote down vote up
private void addPrimaryKey(DatabaseMetaData dbMeta, PhysicalModel model, PhysicalTable table) {
	PhysicalColumn column;
	PhysicalPrimaryKey primaryKey;
	ResultSet rs;

	primaryKey = null;

	try {
		rs = dbMeta.getPrimaryKeys(model.getCatalog(), model.getSchema(), table.getName());
		/*
		 * 1. TABLE_CAT String => table catalog (may be null) 2. TABLE_SCHEM String => table schema (may be null) 3. TABLE_NAME String => table name 4.
		 * COLUMN_NAME String => column name 5. KEY_SEQ short => sequence number within primary key 6. PK_NAME String => primary key name (may be null)
		 */

		while (rs.next()) {
			if (primaryKey == null) {
				primaryKey = FACTORY.createPhysicalPrimaryKey();
				primaryKey.setName(rs.getString("PK_NAME"));

				primaryKey.setTable(table);
				model.getPrimaryKeys().add(primaryKey);

				getPropertiesInitializer().addProperties(primaryKey);
			}

			column = table.getColumn(rs.getString("COLUMN_NAME"));
			if (column != null) {
				primaryKey.getColumns().add(column);
			}

		}
		rs.close();

	} catch (Throwable t) {
		throw new RuntimeException("Impossible to retrive primaryKeys metadata", t);
	}
}
 
Example 12
Source File: JDBCAvroRegistry.java    From components with Apache License 2.0 5 votes vote down vote up
protected Set<String> getPrimaryKeys(DatabaseMetaData databaseMetdata, String catalogName, String schemaName, String tableName)
        throws SQLException {
    Set<String> result = new HashSet<>();

    try (ResultSet resultSet = databaseMetdata.getPrimaryKeys(catalogName, schemaName, tableName)) {
        if (resultSet != null) {
            while (resultSet.next()) {
                result.add(resultSet.getString("COLUMN_NAME"));
            }
        }
    }

    return result;
}
 
Example 13
Source File: TableMetaReader.java    From doma-gen with Apache License 2.0 5 votes vote down vote up
protected Set<String> getPrimaryKeys(DatabaseMetaData metaData, TableMeta tableMeta)
    throws SQLException {
  Set<String> results = new HashSet<String>();
  ResultSet rs =
      metaData.getPrimaryKeys(
          tableMeta.getCatalogName(), tableMeta.getSchemaName(), tableMeta.getName());
  try {
    while (rs.next()) {
      results.add(rs.getString("COLUMN_NAME"));
    }
  } finally {
    JdbcUtil.close(rs);
  }
  return results;
}
 
Example 14
Source File: MetadataUtilities.java    From netbeans with Apache License 2.0 5 votes vote down vote up
/**
 * Call {@link DatabaseMetaData#getPrimaryKeys(String, String, String)},
 * wrapping any internal runtime exeption into an {@link SQLException}.
 */
public static ResultSet getPrimaryKeys(DatabaseMetaData dmd,
        String catalog, String schema, String table) throws SQLException {
    try {
        return dmd.getPrimaryKeys(catalog, schema, table);
    } catch (SQLException e) {
        throw e;
    } catch (Throwable t) {
        throw new SQLException(t);
    }
}
 
Example 15
Source File: SchemaExtractorBase.java    From DKO with GNU Lesser General Public License v2.1 5 votes vote down vote up
private Map<String, Map<String, Set<String>>> getPrimaryKeysJDBC(
		final Connection conn) throws SQLException {
	final Map<String, Map<String, Set<String>>> pks = new LinkedHashMap<String, Map<String, Set<String>>>();
	final DatabaseMetaData metadata = conn.getMetaData();
	final ResultSet rs = metadata.getPrimaryKeys(null, null, null);
	while (rs.next()) {
		final String catalog = rs.getString("TABLE_CAT");
		final String schema = rs.getString("TABLE_SCHEM");
		final String tableName = rs.getString("TABLE_NAME");
		final String columnName = rs.getString("COLUMN_NAME");
		final String pkName = rs.getString("PK_NAME");
		if ("pg_toast".equals(schema)) continue;
		if (includeSchemas!=null && !includeSchemas.contains(schema)) continue;
		Map<String, Set<String>> tables = pks.get(schema);
		if (tables == null) {
			tables = new LinkedHashMap<String, Set<String>>();
			pks.put(schema, tables);
		}
		Set<String> pk = tables.get(tableName);
		if (pk == null) {
			pk = new LinkedHashSet<String>();
			tables.put(tableName, pk);
		}
		pk.add(columnName);
	}
	return pks;
}
 
Example 16
Source File: MetaDataJavaPrinter.java    From aceql-http with GNU Lesser General Public License v2.1 5 votes vote down vote up
private void printGetPrimaryKeys(String tableName) throws SQLException {

	if (tableName == null) {
	    throw new NullPointerException("tableName is null!");
	}
	/**
	<pre><code>
            1.TABLE_CAT String => table catalog (may be null)
            2.TABLE_SCHEM String => table schema (may be null)
            3.TABLE_NAME String => table name
            4.COLUMN_NAME String => Column name
            5.KEY_SEQ short => sequence number within primary key( a valueof 1 represents the first Column of the primary key, a value of 2 wouldrepresent the second Column within the primary key).
            6.PK_NAME String => primary key name (may be null)
	</code></pre>
	 */

	DatabaseMetaData databaseMetaData = connection.getMetaData();
	ResultSet rs = databaseMetaData.getPrimaryKeys(catalog, null, tableName);

	while (rs.next()) {
	    System.out.println();
	    for (int i = 1; i < 7; i++) {
		System.out.println("databaseMetaData.getPrimaryKeys( " + tableName + ") " + i + ": " + rs.getString(i));
	    }
	}

    }
 
Example 17
Source File: MetadataTest.java    From Komondor with GNU General Public License v3.0 4 votes vote down vote up
/**
 * WL#411 - Generated columns.
 * 
 * Test for new syntax and support in DatabaseMetaData.getColumns().
 * 
 * New syntax for CREATE TABLE, introduced in MySQL 5.7.6:
 * -col_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment] [[NOT] NULL] [[PRIMARY] KEY]
 */
public void testGeneratedColumns() throws Exception {
    if (!versionMeetsMinimum(5, 7, 6)) {
        return;
    }

    // Test GENERATED columns syntax.
    createTable("pythagorean_triple",
            "(side_a DOUBLE NULL, side_b DOUBLE NULL, "
                    + "side_c_vir DOUBLE AS (SQRT(side_a * side_a + side_b * side_b)) VIRTUAL UNIQUE KEY COMMENT 'hypotenuse - virtual', "
                    + "side_c_sto DOUBLE GENERATED ALWAYS AS (SQRT(POW(side_a, 2) + POW(side_b, 2))) STORED UNIQUE KEY COMMENT 'hypotenuse - stored' NOT NULL "
                    + "PRIMARY KEY)");

    // Test data for generated columns.
    assertEquals(1, this.stmt.executeUpdate("INSERT INTO pythagorean_triple (side_a, side_b) VALUES (3, 4)"));
    this.rs = this.stmt.executeQuery("SELECT * FROM pythagorean_triple");
    assertTrue(this.rs.next());
    assertEquals(3d, this.rs.getDouble(1));
    assertEquals(4d, this.rs.getDouble(2));
    assertEquals(5d, this.rs.getDouble(3));
    assertEquals(5d, this.rs.getDouble(4));
    assertEquals(3d, this.rs.getDouble("side_a"));
    assertEquals(4d, this.rs.getDouble("side_b"));
    assertEquals(5d, this.rs.getDouble("side_c_sto"));
    assertEquals(5d, this.rs.getDouble("side_c_vir"));
    assertFalse(this.rs.next());

    for (String connProps : new String[] { "useInformationSchema=false", "useInformationSchema=true" }) {
        Connection testConn = null;
        testConn = getConnectionWithProps(connProps);
        DatabaseMetaData dbmd = testConn.getMetaData();

        String test = "Case [" + connProps + "]";

        // Test columns metadata.
        this.rs = dbmd.getColumns(null, null, "pythagorean_triple", "%");
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_a", this.rs.getString("COLUMN_NAME"));
        assertEquals(test, "YES", this.rs.getString("IS_NULLABLE"));
        assertEquals(test, "NO", this.rs.getString("IS_AUTOINCREMENT"));
        assertEquals(test, "NO", this.rs.getString("IS_GENERATEDCOLUMN"));
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_b", this.rs.getString("COLUMN_NAME"));
        assertEquals(test, "YES", this.rs.getString("IS_NULLABLE"));
        assertEquals(test, "NO", this.rs.getString("IS_AUTOINCREMENT"));
        assertEquals(test, "NO", this.rs.getString("IS_GENERATEDCOLUMN"));
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_c_vir", this.rs.getString("COLUMN_NAME"));
        assertEquals(test, "YES", this.rs.getString("IS_NULLABLE"));
        assertEquals(test, "NO", this.rs.getString("IS_AUTOINCREMENT"));
        assertEquals(test, "YES", this.rs.getString("IS_GENERATEDCOLUMN"));
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_c_sto", this.rs.getString("COLUMN_NAME"));
        assertEquals(test, "NO", this.rs.getString("IS_NULLABLE"));
        assertEquals(test, "NO", this.rs.getString("IS_AUTOINCREMENT"));
        assertEquals(test, "YES", this.rs.getString("IS_GENERATEDCOLUMN"));
        assertFalse(test, this.rs.next());

        // Test primary keys metadata.
        this.rs = dbmd.getPrimaryKeys(null, null, "pythagorean_triple");
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_c_sto", this.rs.getString("COLUMN_NAME"));
        assertEquals(test, "PRIMARY", this.rs.getString("PK_NAME"));
        assertFalse(test, this.rs.next());

        // Test indexes metadata.
        this.rs = dbmd.getIndexInfo(null, null, "pythagorean_triple", false, true);
        assertTrue(test, this.rs.next());
        assertEquals(test, "PRIMARY", this.rs.getString("INDEX_NAME"));
        assertEquals(test, "side_c_sto", this.rs.getString("COLUMN_NAME"));
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_c_sto", this.rs.getString("INDEX_NAME"));
        assertEquals(test, "side_c_sto", this.rs.getString("COLUMN_NAME"));
        assertTrue(test, this.rs.next());
        assertEquals(test, "side_c_vir", this.rs.getString("INDEX_NAME"));
        assertEquals(test, "side_c_vir", this.rs.getString("COLUMN_NAME"));
        assertFalse(test, this.rs.next());

        testConn.close();
    }
}
 
Example 18
Source File: QueryDatabaseMetaDataTest.java    From phoenix with BSD 3-Clause "New" or "Revised" License 4 votes vote down vote up
@Test
public void testPrimaryKeyMetadataScan() throws SQLException {
    long ts = nextTimestamp();
    ensureTableCreated(getUrl(), MDTEST_NAME, null, ts);
    ensureTableCreated(getUrl(), CUSTOM_ENTITY_DATA_FULL_NAME, null, ts);
    Properties props = new Properties();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet rs;
    rs = dbmd.getPrimaryKeys(null, "", MDTEST_NAME);
    assertTrue(rs.next());
    assertEquals(rs.getString("TABLE_SCHEM"),null);
    assertEquals(MDTEST_NAME, rs.getString("TABLE_NAME"));
    assertEquals(null, rs.getString("TABLE_CAT"));
    assertEquals(SchemaUtil.normalizeIdentifier("id"), rs.getString("COLUMN_NAME"));
    assertEquals(1, rs.getInt("KEY_SEQ"));
    assertEquals(null, rs.getString("PK_NAME"));
    assertFalse(rs.next());
    
    rs = dbmd.getPrimaryKeys(null, CUSTOM_ENTITY_DATA_SCHEMA_NAME, CUSTOM_ENTITY_DATA_NAME);
    assertTrue(rs.next());
    assertEquals(CUSTOM_ENTITY_DATA_SCHEMA_NAME, rs.getString("TABLE_SCHEM"));
    assertEquals(CUSTOM_ENTITY_DATA_NAME, rs.getString("TABLE_NAME"));
    assertEquals(null, rs.getString("TABLE_CAT"));
    assertEquals(SchemaUtil.normalizeIdentifier("organization_id"), rs.getString("COLUMN_NAME"));
    assertEquals(1, rs.getInt("KEY_SEQ"));
    assertEquals(SchemaUtil.normalizeIdentifier("pk"), rs.getString("PK_NAME")); // TODO: this is on the table row
    
    assertTrue(rs.next());
    assertEquals(CUSTOM_ENTITY_DATA_SCHEMA_NAME, rs.getString("TABLE_SCHEM"));
    assertEquals(CUSTOM_ENTITY_DATA_NAME, rs.getString("TABLE_NAME"));
    assertEquals(null, rs.getString("TABLE_CAT"));
    assertEquals(SchemaUtil.normalizeIdentifier("key_prefix"), rs.getString("COLUMN_NAME"));
    assertEquals(2, rs.getInt("KEY_SEQ"));
    assertEquals(SchemaUtil.normalizeIdentifier("pk"), rs.getString("PK_NAME"));
    
    assertTrue(rs.next());
    assertEquals(CUSTOM_ENTITY_DATA_SCHEMA_NAME, rs.getString("TABLE_SCHEM"));
    assertEquals(CUSTOM_ENTITY_DATA_NAME, rs.getString("TABLE_NAME"));
    assertEquals(null, rs.getString("TABLE_CAT"));
    assertEquals(SchemaUtil.normalizeIdentifier("custom_entity_data_id"), rs.getString("COLUMN_NAME"));
    assertEquals(3, rs.getInt("KEY_SEQ"));
    assertEquals(SchemaUtil.normalizeIdentifier("pk"), rs.getString("PK_NAME"));

    assertFalse(rs.next());

    rs = dbmd.getColumns("", CUSTOM_ENTITY_DATA_SCHEMA_NAME, CUSTOM_ENTITY_DATA_NAME, null);
    assertTrue(rs.next());
    assertEquals(CUSTOM_ENTITY_DATA_SCHEMA_NAME, rs.getString("TABLE_SCHEM"));
    assertEquals(CUSTOM_ENTITY_DATA_NAME, rs.getString("TABLE_NAME"));
    assertEquals(null, rs.getString("TABLE_CAT"));
    assertEquals(SchemaUtil.normalizeIdentifier("organization_id"), rs.getString("COLUMN_NAME"));
    
    assertTrue(rs.next());
    assertEquals(CUSTOM_ENTITY_DATA_SCHEMA_NAME, rs.getString("TABLE_SCHEM"));
    assertEquals(CUSTOM_ENTITY_DATA_NAME, rs.getString("TABLE_NAME"));
    assertEquals(null, rs.getString("TABLE_CAT"));
    assertEquals(SchemaUtil.normalizeIdentifier("key_prefix"), rs.getString("COLUMN_NAME"));
    
    assertTrue(rs.next());
    assertEquals(CUSTOM_ENTITY_DATA_SCHEMA_NAME, rs.getString("TABLE_SCHEM"));
    assertEquals(CUSTOM_ENTITY_DATA_NAME, rs.getString("TABLE_NAME"));
    assertEquals(null, rs.getString("TABLE_CAT"));
    assertEquals(SchemaUtil.normalizeIdentifier("custom_entity_data_id"), rs.getString("COLUMN_NAME"));
    
    // The above returns all columns, starting with the PK columns
    assertTrue(rs.next());
    
    rs = dbmd.getColumns("", CUSTOM_ENTITY_DATA_SCHEMA_NAME.toLowerCase(), CUSTOM_ENTITY_DATA_NAME.toLowerCase(), "key_prefix");
    assertTrue(rs.next());
    assertEquals(CUSTOM_ENTITY_DATA_SCHEMA_NAME, rs.getString("TABLE_SCHEM"));
    assertEquals(CUSTOM_ENTITY_DATA_NAME, rs.getString("TABLE_NAME"));
    assertEquals(null, rs.getString("TABLE_CAT"));
    assertEquals(SchemaUtil.normalizeIdentifier("key_prefix"), rs.getString("COLUMN_NAME"));
    
    rs = dbmd.getColumns("", CUSTOM_ENTITY_DATA_SCHEMA_NAME.toLowerCase(), CUSTOM_ENTITY_DATA_NAME.toLowerCase(), "key_prefix".toUpperCase());
    assertTrue(rs.next());
    assertEquals(CUSTOM_ENTITY_DATA_SCHEMA_NAME, rs.getString("TABLE_SCHEM"));
    assertEquals(CUSTOM_ENTITY_DATA_NAME, rs.getString("TABLE_NAME"));
    assertEquals(null, rs.getString("TABLE_CAT"));
    assertEquals(SchemaUtil.normalizeIdentifier("key_prefix"), rs.getString("COLUMN_NAME"));
    
    assertFalse(rs.next());
    
}
 
Example 19
Source File: DatabaseMetaDataIT.java    From snowflake-jdbc with Apache License 2.0 4 votes vote down vote up
@Test
public void testUseConnectionCtx() throws SQLException
{
  try (Connection connection = getConnection())
  {
    connection.createStatement().execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true");
    String schema = connection.getSchema();
    DatabaseMetaData databaseMetaData = connection.getMetaData();

    // create tables within current schema.
    connection.createStatement().execute("create or replace schema TEST_CTX");
    connection.createStatement().execute("create or replace table CTX_TBL_A (colA string, colB decimal, " +
                                         "colC number PRIMARY KEY);");
    connection.createStatement().execute("create or replace table CTX_TBL_B (colA string, colB decimal, " +
                                         "colC number FOREIGN KEY REFERENCES CTX_TBL_A (colC));");
    connection.createStatement().execute("create or replace table CTX_TBL_C (colA string, colB decimal, " +
                                         "colC number, colD int, colE timestamp, colF string, colG number);");
    // now create more tables under current schema
    connection.createStatement().execute("use schema " + schema);
    connection.createStatement().execute("create or replace table CTX_TBL_D (colA string, colB decimal, " +
                                         "colC number PRIMARY KEY);");
    connection.createStatement().execute("create or replace table CTX_TBL_E (colA string, colB decimal, " +
                                         "colC number FOREIGN KEY REFERENCES CTX_TBL_D (colC));");
    connection.createStatement().execute("create or replace table CTX_TBL_F (colA string, colB decimal, " +
                                         "colC number, colD int, colE timestamp, colF string, colG number);");

    // this should only return TEST_CTX schema and tables
    connection.createStatement().execute("use schema TEST_CTX");

    ResultSet resultSet = databaseMetaData.getSchemas(null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getTables(null, null, null, null);
    assertEquals(3, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getColumns(null, null, null, null);
    assertEquals(13, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getPrimaryKeys(null, null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getImportedKeys(null, null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getExportedKeys(null, null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    resultSet = databaseMetaData.getCrossReference(null, null, null, null,
                                                   null, null);
    assertEquals(1, getSizeOfResultSet(resultSet));

    // Now compare results to setting client metadata to false.
    connection.createStatement().execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=false");
    databaseMetaData = connection.getMetaData();

    resultSet = databaseMetaData.getSchemas(null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getTables(null, null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(6));

    resultSet = databaseMetaData.getColumns(null, null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(26));

    resultSet = databaseMetaData.getPrimaryKeys(null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getImportedKeys(null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getExportedKeys(null, null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

    resultSet = databaseMetaData.getCrossReference(null, null, null, null,
                                                   null, null);
    assertThat(getSizeOfResultSet(resultSet), greaterThanOrEqualTo(2));

  }
}
 
Example 20
Source File: DataSelectLogic.java    From JspMyAdmin2 with GNU General Public License v2.0 4 votes vote down vote up
/**
 * 
 * @param bean
 * @return
 * @throws ClassNotFoundException
 * @throws SQLException
 * @throws Exception
 */
public String update(Bean bean) throws SQLException {
	DataUpdateBean dataUpdateBean = (DataUpdateBean) bean;

	ApiConnection apiConnection = null;
	PreparedStatement statement = null;
	ResultSet resultSet = null;

	StringBuilder builder = null;
	String primaryKey = null;
	try {
		apiConnection = getConnection(bean.getRequest_db());
		DatabaseMetaData databaseMetaData = apiConnection.getDatabaseMetaData();
		resultSet = databaseMetaData.getPrimaryKeys(null, null, _table);
		if (resultSet.next()) {
			primaryKey = resultSet.getString(Constants.COLUMN_NAME);
		}
		close(resultSet);

		if (dataUpdateBean.getColumn_name() != null && dataUpdateBean.getColumn_value() != null) {
			builder = new StringBuilder();
			builder.append("UPDATE `");
			builder.append(_table);
			builder.append(Constants.SYMBOL_TEN);
			builder.append(" SET `");
			builder.append(dataUpdateBean.getColumn_name().trim());
			builder.append(Constants.SYMBOL_TEN);
			builder.append(" = ? WHERE `");
			builder.append(primaryKey);
			builder.append(Constants.SYMBOL_TEN);
			builder.append(" = ?");
			statement = apiConnection.getStmt(builder.toString());
			if (Constants.DATABASE_NULL.equals(dataUpdateBean.getColumn_value())) {
				statement.setObject(1, null);
			} else {
				statement.setString(1, dataUpdateBean.getColumn_value());
			}
			statement.setString(2, dataUpdateBean.getPrimary_key());
			int count = statement.executeUpdate();
			String query = new QueryExtracter(statement).toString();
			apiConnection.commit();
			return query + "; // " + count;
		}
	} finally {
		close(resultSet);
		close(statement);
		close(apiConnection);
	}
	return null;
}