java.sql.DatabaseMetaData#getPrimaryKeys

Source File: DatabaseMetaDataTest.java From gemfirexd-oss with Apache License 2.0

6 votes

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

Source File: JdbcThinMetadataPrimaryKeysSelfTest.java From ignite with Apache License 2.0

6 votes

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

Source File: JdbcRegressionUnitTest.java From cassandra-jdbc-wrapper with Apache License 2.0

6 votes

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

Source File: JDBCAdapter.java From hana-native-adapters with Apache License 2.0

6 votes

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;
}

Source File: metaDatabase.java From openbd-core with GNU General Public License v3.0

6 votes

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;
	}
}

Source File: MySQLTableRepository.java From mybatis-dalgen with Apache License 2.0

6 votes

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

Source File: Show.java From jsqsh with Apache License 2.0

6 votes

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

Source File: DbUtilities.java From openemm with GNU Affero General Public License v3.0

6 votes

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

Source File: PutDatabaseRecord.java From nifi with Apache License 2.0

6 votes

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

Source File: DbHelper.java From crud-intellij-plugin with Apache License 2.0

6 votes

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

Source File: PhysicalModelInitializer.java From Knowage-Server with GNU Affero General Public License v3.0

5 votes

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

Source File: JDBCAvroRegistry.java From components with Apache License 2.0

5 votes

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;
}

Source File: TableMetaReader.java From doma-gen with Apache License 2.0

5 votes

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;
}

Source File: MetadataUtilities.java From netbeans with Apache License 2.0

5 votes

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

Source File: SchemaExtractorBase.java From DKO with GNU Lesser General Public License v2.1

5 votes

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;
}

Source File: MetaDataJavaPrinter.java From aceql-http with GNU Lesser General Public License v2.1

5 votes

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

    }

Source File: MetadataTest.java From Komondor with GNU General Public License v3.0

4 votes

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

Source File: QueryDatabaseMetaDataTest.java From phoenix with BSD 3-Clause "New" or "Revised" License

4 votes

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

Source File: DatabaseMetaDataIT.java From snowflake-jdbc with Apache License 2.0

4 votes

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

  }
}

Source File: DataSelectLogic.java From JspMyAdmin2 with GNU General Public License v2.0

4 votes

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

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