下面列出了java.sql.Connection#getMetaData ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
public static void main(final String[] args) throws InputException, InstantiationException, IllegalAccessException, SQLException {
new ERDiagramActivator();
final DBSetting setting = new DBSetting("Oracle", "localhost", 1521, "XE", "nakajima", "nakajima", true, null, null);
Connection con = null;
try {
con = setting.connect();
final DatabaseMetaData metaData = con.getMetaData();
metaData.getIndexInfo(null, "SYS", "ALERT_QT", false, false);
} finally {
if (con != null) {
con.close();
}
}
}
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()));
}
public static boolean columnExists(Connection connection, String tableName, String columnName)
throws SQLException
{
boolean exists = false;
DatabaseMetaData metaData = connection.getMetaData();
// Fetch all columns in order to do a case-insensitive search
ResultSet res = metaData.getColumns(null, DbConstants.DATABASE_SCHEMA_NAME, null, null);
while (res.next())
{
String resTableName = res.getString(META_COL_TABLE_NAME);
String resColumnName = res.getString(META_COL_COLUMN_NAME);
if (tableName.equalsIgnoreCase(resTableName) && columnName.equalsIgnoreCase(resColumnName))
{
exists = true;
}
}
return exists;
}
/**
* テーブルメタデータを読み取ります。
*
* @return テーブルメタデータ
*/
public List<TableMeta> read() {
Connection con = JdbcUtil.getConnection(dataSource);
try {
DatabaseMetaData metaData = con.getMetaData();
List<TableMeta> tableMetas =
getTableMetas(metaData, schemaName != null ? schemaName : getDefaultSchemaName(metaData));
for (TableMeta tableMeta : tableMetas) {
Set<String> primaryKeySet = getPrimaryKeys(metaData, tableMeta);
handleColumnMeta(metaData, tableMeta, primaryKeySet);
}
if (dialect.isJdbcCommentUnavailable()) {
readCommentFromDictinary(con, tableMetas);
}
return tableMetas;
} catch (SQLException e) {
throw new GenException(Message.DOMAGEN9001, e, e);
} finally {
JdbcUtil.close(con);
}
}
private static void checkConn( Connection conn, String label)
{
try
{
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getSchemas();
while( rs.next());
rs.close();
}
catch( SQLException sqle)
{
passed = false;
System.out.println( "Could not use the " + label + " connection:");
System.out.println( " " + sqle.getMessage());
}
}
public static SQLDialect investigateDialect(Connection connection) {
SQLDialect dialect = null;
try {
DatabaseMetaData metaData = connection.getMetaData();
String dbProduct = metaData.getDatabaseProductName();
dialect = identifyDialect(dbProduct);
if (dialect == null) {
logger.debug("Attempting to guess on driver name.");
dialect = identifyDialect(metaData.getDriverName());
}
if (dialect == null) {
logger.warnf("Unable to detect database dialect from connection metadata or JDBC driver name.");
} else {
logger.debugf("Detect database dialect as '%s'.", dialect);
}
} catch (Exception e) {
logger.debug("Unable to read JDBC metadata.", e);
}
return dialect;
}
public String checkDBProductName() throws Exception {
Connection conn = null;
String dbProductName = null;
try {
if (Log.doTrace())
Log.traceEnter("TradeDirect:checkDBProductName");
conn = getConn();
DatabaseMetaData dbmd = conn.getMetaData();
dbProductName = dbmd.getDatabaseProductName();
} catch (SQLException e) {
Log.error(e, "TradeDirect:checkDBProductName() -- Error checking the Daytrader Database Product Name");
} finally {
releaseConn(conn);
}
return dbProductName;
}
private cfData typeIndex(cfSession _session, cfDataSource datasource, String dbname, String table ) throws cfmRunTimeException{
Connection c = null;
try {
c = datasource.getPooledConnection();
DatabaseMetaData metaData = c.getMetaData();
cfQueryResultData queryResult = new cfQueryResultData(new String[] { "index_name", "column_name", "ordinal_position", "cardinality", "type", "pages", "non_unique" }, "DBINFO");
ResultSet rset = metaData.getIndexInfo(dbname, null, table, false, false);
int row=1;
while ( rset.next() ){
queryResult.addRow(1);
queryResult.setCell(row, 1, new cfStringData(rset.getString(6)) );
queryResult.setCell(row, 2, new cfStringData(rset.getString(9)) );
queryResult.setCell(row, 3, new cfStringData(rset.getString(8)) );
queryResult.setCell(row, 3, new cfStringData(rset.getString(11)) );
queryResult.setCell(row, 3, new cfStringData(rset.getString(7)) );
queryResult.setCell(row, 3, new cfStringData(rset.getString(12)) );
row++;
}
rset.close();
return queryResult;
} catch (SQLException e) {
throwException(_session, e.getMessage() );
} finally {
datasource.close(c);
}
return null;
}
private void createIfNotExists(final String tableName, final String createTableStatement) throws SQLException {
final Connection conn = getConnection();
final DatabaseMetaData dbmd = conn.getMetaData();
final ResultSet rs = dbmd.getTables(null, schema, tableName, null);
PreparedStatement ps = null;
if (!rs.next()) {
ps = conn.prepareStatement(createTableStatement);
ps.executeUpdate();
}
cleanupConnection(conn, rs, ps);
}
private void checkDatabaseProperty(Connection connection)
throws SQLException {
final DatabaseMetaData metaData = connection.getMetaData();
assertThat(metaData.getSQLKeywords(), equalTo(""));
assertThat(metaData.getStringFunctions(),
equalTo("ASCII,CHAR,CONCAT,DIFFERENCE,HEXTORAW,INSERT,LCASE,LEFT,"
+ "LENGTH,LOCATE,LTRIM,RAWTOHEX,REPEAT,REPLACE,RIGHT,RTRIM,SOUNDEX,"
+ "SPACE,SUBSTR,UCASE"));
assertThat(metaData.getDefaultTransactionIsolation(),
equalTo(Connection.TRANSACTION_READ_COMMITTED));
}
public WrappedConnection( Connection con ){
this.con = con;
activeListener = null;
creationTime = System.currentTimeMillis();
validationQuery = null;
if ( !validateConnections ) {
return;
}
try {
// create validation query
DatabaseMetaData dbmd = con.getMetaData();
String dbProductName = dbmd.getDatabaseProductName().toLowerCase();
if ( ( dbProductName.indexOf( "microsoft" ) >= 0 ) ||
( dbProductName.indexOf( "sql server" ) >= 0 ) || // this value is also returned by the BEA driver for sybase
( dbProductName.indexOf( "access" ) >= 0 ) ||
( dbProductName.indexOf( "adaptive server enterprise" ) >= 0 ) || // sybase using jConnect 5.5 driver
( dbProductName.indexOf( "sybase" ) >= 0 ) ) { // this check may not be needed
validationQuery = "select 1";
} else if ( dbProductName.indexOf( "oracle" ) >= 0 ) {
validationQuery = "select sysdate from dual";
} else if ( ( dbProductName.indexOf( "mysql" ) >= 0 ) ||
( dbProductName.indexOf( "postgres" ) >= 0 ) ) {
validationQuery = "select now()";
} else if ( dbProductName.indexOf( "informix" ) >= 0 ) {
validationQuery = "select distinct current timestamp from informix.systables";
} else if ( ( dbProductName.indexOf( "db2" ) >= 0 ) ||
( dbProductName.indexOf( "ibm" ) >= 0 ) ) { // this check may not be needed
validationQuery = "select distinct(current timestamp) from sysibm.systables";
} else if ( dbProductName.indexOf( "pointbase" ) >= 0 ) {
validationQuery = "select count(*) from systables";
} else {
cfEngine.log( "Can't validate connections for " + dbProductName );
}
} catch ( SQLException ignore ) {}
}
public Status check() {
boolean ok;
try {
Connection connection = dataSource.getConnection();
try {
DatabaseMetaData metaData = connection.getMetaData();
ResultSet resultSet = metaData.getTypeInfo();
try {
ok = resultSet.next();
} finally {
resultSet.close();
}
if (message == null) {
message = metaData.getURL()
+ " (" + metaData.getDatabaseProductName()
+ " " + metaData.getDatabaseProductVersion()
+ ", " + getIsolation(metaData.getDefaultTransactionIsolation()) + ")";
}
if (version == 0) {
version = metaData.getDatabaseMajorVersion();
}
} finally {
connection.close();
}
} catch (Throwable e) {
logger.error(e.getMessage(), e);
ok = false;
}
return new Status(!ok ? Status.Level.ERROR : (version < 5 ? Status.Level.WARN : Status.Level.OK), message);
}
/**
* 获取数据库类型
* @param dataSource
* @return
* @throws SQLException
*/
private String getDatabaseTypeByDataSource(DataSource dataSource) throws SQLException{
if("".equals(DB_TYPE)) {
Connection connection = dataSource.getConnection();
try {
DatabaseMetaData md = connection.getMetaData();
String dbType = md.getDatabaseProductName().toLowerCase();
if(dbType.indexOf("mysql")>=0) {
DB_TYPE = DataBaseConstant.DB_TYPE_MYSQL;
}else if(dbType.indexOf("oracle")>=0) {
DB_TYPE = DataBaseConstant.DB_TYPE_ORACLE;
}else if(dbType.indexOf("sqlserver")>=0||dbType.indexOf("sql server")>=0) {
DB_TYPE = DataBaseConstant.DB_TYPE_SQLSERVER;
}else if(dbType.indexOf("postgresql")>=0) {
DB_TYPE = DataBaseConstant.DB_TYPE_POSTGRESQL;
}else {
throw new JeecgBootException("数据库类型:["+dbType+"]不识别!");
}
} catch (Exception e) {
log.error(e.getMessage(), e);
}finally {
connection.close();
}
}
return DB_TYPE;
}
public Status check() {
boolean ok;
try {
Connection connection = dataSource.getConnection();
try {
DatabaseMetaData metaData = connection.getMetaData();
ResultSet resultSet = metaData.getTypeInfo();
try {
ok = resultSet.next();
} finally {
resultSet.close();
}
if (message == null) {
message = metaData.getURL()
+ " (" + metaData.getDatabaseProductName()
+ " " + metaData.getDatabaseProductVersion()
+ ", " + getIsolation(metaData.getDefaultTransactionIsolation()) + ")";
}
if (version == 0) {
version = metaData.getDatabaseMajorVersion();
}
} finally {
connection.close();
}
} catch (Throwable e) {
logger.error(e.getMessage(), e);
ok = false;
}
return new Status(! ok ? Status.Level.ERROR : (version < 5 ? Status.Level.WARN : Status.Level.OK), message);
}
/**
* 获取数据库类型
* @param dataSource
* @return
* @throws SQLException
*/
private String getDatabaseTypeByDataSource(DataSource dataSource) throws SQLException{
if("".equals(DB_TYPE)) {
Connection connection = dataSource.getConnection();
try {
DatabaseMetaData md = connection.getMetaData();
String dbType = md.getDatabaseProductName().toLowerCase();
if(dbType.indexOf("mysql")>=0) {
DB_TYPE = DataBaseConstant.DB_TYPE_MYSQL;
}else if(dbType.indexOf("oracle")>=0) {
DB_TYPE = DataBaseConstant.DB_TYPE_ORACLE;
}else if(dbType.indexOf("sqlserver")>=0||dbType.indexOf("sql server")>=0) {
DB_TYPE = DataBaseConstant.DB_TYPE_SQLSERVER;
}else if(dbType.indexOf("postgresql")>=0) {
DB_TYPE = DataBaseConstant.DB_TYPE_POSTGRESQL;
}else {
throw new JeecgBootException("数据库类型:["+dbType+"]不识别!");
}
} catch (Exception e) {
log.error(e.getMessage(), e);
}finally {
connection.close();
}
}
return DB_TYPE;
}
public static void metadataCalls(Connection conn)
throws Exception
{
System.out.println("A new connection is doing metadata calls, but never commit...");
DatabaseMetaData dmd = conn.getMetaData();
getTypeInfo(dmd,System.out);
getTables(dmd,System.out);
getColumnInfo(dmd, "%",System.out);
getPrimaryKeys(dmd, "%",System.out);
getExportedKeys(dmd, "%",System.out);
}
private void createJobExecutionTableAndIndexIfNeeded(final Connection connection) throws SQLException {
DatabaseMetaData dbMetaData = connection.getMetaData();
try (ResultSet resultSet = dbMetaData.getTables(connection.getCatalog(), null, TABLE_JOB_EXECUTION_LOG, new String[]{"TABLE"})) {
if (!resultSet.next()) {
createJobExecutionTable(connection);
}
}
}
@Test
public void testReconTaskStatusCRUDOperations() throws Exception {
// Verify table exists
Connection connection = getConnection();
DatabaseMetaData metaData = connection.getMetaData();
ResultSet resultSet = metaData.getTables(null, null,
RECON_TASK_STATUS_TABLE_NAME, null);
while (resultSet.next()) {
Assert.assertEquals(RECON_TASK_STATUS_TABLE_NAME,
resultSet.getString("TABLE_NAME"));
}
ReconTaskStatusDao dao = getDao(ReconTaskStatusDao.class);
long now = System.currentTimeMillis();
ReconTaskStatus newRecord = new ReconTaskStatus();
newRecord.setTaskName("HelloWorldTask");
newRecord.setLastUpdatedTimestamp(now);
newRecord.setLastUpdatedSeqNumber(100L);
// Create
dao.insert(newRecord);
ReconTaskStatus newRecord2 = new ReconTaskStatus();
newRecord2.setTaskName("GoodbyeWorldTask");
newRecord2.setLastUpdatedTimestamp(now);
newRecord2.setLastUpdatedSeqNumber(200L);
// Create
dao.insert(newRecord2);
// Read
ReconTaskStatus dbRecord = dao.findById("HelloWorldTask");
Assert.assertEquals("HelloWorldTask", dbRecord.getTaskName());
Assert.assertEquals(Long.valueOf(now), dbRecord.getLastUpdatedTimestamp());
Assert.assertEquals(Long.valueOf(100), dbRecord.getLastUpdatedSeqNumber());
// Update
dbRecord.setLastUpdatedSeqNumber(150L);
dao.update(dbRecord);
// Read updated
dbRecord = dao.findById("HelloWorldTask");
Assert.assertEquals(Long.valueOf(150), dbRecord.getLastUpdatedSeqNumber());
// Delete
dao.deleteById("GoodbyeWorldTask");
// Verify
dbRecord = dao.findById("GoodbyeWorldTask");
Assert.assertNull(dbRecord);
}
@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(getUrl(), 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("custom_entity_data_id"), rs.getString("COLUMN_NAME"));
assertEquals(3, 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("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("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
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"));
assertEquals(rs.getInt("COLUMN_SIZE"), 15);
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(rs.getInt("COLUMN_SIZE"), 3);
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, CUSTOM_ENTITY_DATA_NAME, "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, CUSTOM_ENTITY_DATA_NAME, "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"));
assertFalse(rs.next());
}
public String getDriverVersion() throws Exception {
final Connection con = ds.getConnection();
final DatabaseMetaData md = con.getMetaData();
return md.getDriverVersion();
}