Java DatabaseMetaData.getPrimaryKeys方法代码示例

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
protected final boolean columnInPrimaryKey(String tablename, String colname)
    throws Exception {
    tablename = fixIdentifier(tablename);
    colname = fixIdentifier(colname);
    DatabaseMetaData md = getConnection().getMetaData();
    ResultSet rs = md.getPrimaryKeys(null, getSchema(), tablename);
    
    // printResults(rs, "columnInPrimaryKey(" + viewname + ", " +
    //        colname + ")");
            
    while ( rs.next() ) {
        String pkCol = rs.getString(4);
        if ( pkCol.equals(colname)) {
            return true;
        }
    }
    
    return false;
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Tests the implementation of Information Schema for primary keys.
 */
public void testGetPrimaryKeysUsingInfoShcema() throws Exception {
    if (versionMeetsMinimum(5, 0, 7)) {
        createTable("t1", "(c1 int(1) primary key)");
        Properties props = new Properties();
        props.put("useInformationSchema", "true");
        Connection conn1 = null;
        try {
            conn1 = getConnectionWithProps(props);
            DatabaseMetaData metaData = conn1.getMetaData();
            this.rs = metaData.getPrimaryKeys(null, null, "t1");
            this.rs.next();
            assertEquals("t1", this.rs.getString("TABLE_NAME"));
            assertEquals("c1", this.rs.getString("COLUMN_NAME"));
        } finally {
            if (conn1 != null) {
                conn1.close();
            }
        }
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
public List<String> findTablePrimaryKeys(String dataSourceName, String tableName) throws Exception {
	List<String> primaryKeys = new ArrayList<String>();
	Connection conn = null;
	ResultSet rs = null;
	try {
		conn = connectionHelper.getConnection(dataSourceName);
		DatabaseMetaData metaData = conn.getMetaData();
		rs = metaData.getPrimaryKeys(null, null, tableName.toUpperCase());
		while (rs.next()) {
			primaryKeys.add(rs.getString("COLUMN_NAME").toLowerCase());
		}
		return primaryKeys;
	} finally {
		if (conn != null) {
			conn.close();
		}
		if (rs != null) {
			rs.close();
		}
	}
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
public List<String> findTablePrimaryKeys(String dbInfoId, String tableName) throws Exception {
	List<String> primaryKeys = new ArrayList<String>();
	Connection con = null;
	ResultSet rs = null;
	DataSource ds = this.getDataSourceByDbInfoId(dbInfoId);
	try {
		con = ds.getConnection();
		DatabaseMetaData metaData = con.getMetaData();
		rs = metaData.getPrimaryKeys(null, null, tableName.toUpperCase());
		while (rs.next()) {
			primaryKeys.add(rs.getString("COLUMN_NAME").toUpperCase());
		}
		return primaryKeys;
	} finally {
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeConnection(con);
	}
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
private List<String> loadTablePrimaryKeys(String tableName)throws Exception{
	DataSource ds=this.getJdbcTemplate().getDataSource();
	Connection con = DataSourceUtils.getConnection(ds);
	List<String> primaryKeyList=new ArrayList<String>();
	Statement stmt = null;
	ResultSet rs=null;
	try{
		DatabaseMetaData metaData = con.getMetaData();
		rs = metaData.getPrimaryKeys(null, null, tableName.toUpperCase());
		while (rs.next()) {
			primaryKeyList.add(rs.getString("COLUMN_NAME"));
		}
	}finally{
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeStatement(stmt);
		JdbcUtils.closeConnection(con);
	}
	return primaryKeyList;
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
private String loadTablePrimaryKeys(String tableName,DatabaseMetaData metaData) throws Exception{
	String keys="";
	ResultSet rs = null;
	try {
		rs = metaData.getPrimaryKeys(null, null, tableName.toUpperCase());
		int i=0;
		while (rs.next()) {
			if(i>0){
				keys+=",";
			}
			i++;
			keys+=rs.getString("COLUMN_NAME");
		}
	} finally {
		JdbcUtils.closeResultSet(rs);
	}
	return keys;
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
public static List<Map<Integer, Object>> getTablePrimaryKeys(Connection conn, String tableSchema, String tableName) throws Exception {
	List<Map<Integer, Object>> primaryKeys = new ArrayList<Map<Integer, Object>>();
	Map<Integer, Object> primaryKey;
	DatabaseMetaData databaseMetaData = conn.getMetaData();
	if (tableSchema == null) {
		tableSchema = getDefaultTableSchema(conn);
	}
	ResultSet rs = null;
	try {
		rs = databaseMetaData.getPrimaryKeys(null, tableSchema, tableName.toUpperCase());
		while (rs.next()) {
			primaryKey = new HashMap<Integer, Object>();
			int i = 1;
			while (i <= 6) {
				primaryKey.put(i, rs.getObject(i));
				i++;
			}
			primaryKeys.add(primaryKey);
		}
		return primaryKeys;
	} finally {
		DbJdbcUtils.closeResultSet(rs);
	}
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
public void testGetPrimaryKeys() throws SQLException {
    try {
        createTable("multikey", "(d INT NOT NULL, b INT NOT NULL, a INT NOT NULL, c INT NOT NULL, PRIMARY KEY (d, b, a, c))");
        DatabaseMetaData dbmd = this.conn.getMetaData();
        this.rs = dbmd.getPrimaryKeys(this.conn.getCatalog(), "", "multikey");

        short[] keySeqs = new short[4];
        String[] columnNames = new String[4];
        int i = 0;

        while (this.rs.next()) {
            this.rs.getString("TABLE_NAME");
            columnNames[i] = this.rs.getString("COLUMN_NAME");

            this.rs.getString("PK_NAME");
            keySeqs[i] = this.rs.getShort("KEY_SEQ");
            i++;
        }

        if ((keySeqs[0] != 3) && (keySeqs[1] != 2) && (keySeqs[2] != 4) && (keySeqs[3] != 1)) {
            fail("Keys returned in wrong order");
        }
    } finally {
        if (this.rs != null) {
            try {
                this.rs.close();
            } catch (SQLException sqlEx) {
                /* ignore */
            }
        }
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * 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);
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
private String getPrimaryKeyName(String tableName) throws SQLException {
    DatabaseMetaData meta = this.databaseHandler.getConnection().getMetaData();
    ResultSet primaryKeys = meta.getPrimaryKeys((String)null, (String)null, tableName);
    String primaryKeyName = null;
    if(primaryKeys.next()) {
        primaryKeyName = primaryKeys.getString(4);
    }

    primaryKeys.close();
    return primaryKeyName;
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Basic test of DatabaseMetaData functions that access system tables
 */
public void testTwo() throws Exception {

    Connection conn = newConnection();
    int        updateCount;

    try {
        TestUtil.testScript(conn, "testrun/hsqldb/TestSelf.txt");

        DatabaseMetaData dbmeta = conn.getMetaData();

        dbmeta.allProceduresAreCallable();
        dbmeta.getBestRowIdentifier(null, null, "T_1",
                                    DatabaseMetaData.bestRowTransaction,
                                    true);
        dbmeta.getCatalogs();
        dbmeta.getColumnPrivileges(null, "PUBLIC", "T_1", "%");
        dbmeta.getColumns("PUBLIC", "PUBLIC", "T_1", "%");
        dbmeta.getCrossReference(null, null, "T_1", null, null, "T_1");
        dbmeta.getExportedKeys(null, null, "T_1");
        dbmeta.getFunctionColumns(null, "%", "%", "%");
        dbmeta.getFunctions(null, "%", "%");
        dbmeta.getImportedKeys("PUBLIC", "PUBLIC", "T_1");
        dbmeta.getIndexInfo("PUBLIC", "PUBLIC", "T1", true, true);
        dbmeta.getPrimaryKeys("PUBLIC", "PUBLIC", "T_1");
        dbmeta.getProcedureColumns(null, null, "%", "%");
        dbmeta.getProcedures("PUBLIC", "%", "%");
        dbmeta.getSchemas(null, "#");
        dbmeta.getTablePrivileges(null, "%", "%");
        dbmeta.getUDTs(null, "%", "%", new int[]{ Types.DISTINCT });
    } catch (Exception e) {
        assertTrue("unable to prepare or execute DDL", false);
    } finally {
        conn.close();
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
@Override
public String getPrimaryKey(String tableName) {
  try {
    DatabaseMetaData metaData = this.getConnection().getMetaData();
    ResultSet results = metaData.getPrimaryKeys(null, null, tableName);
    if (null == results) {
      return null;
    }

    try {
      if (results.next()) {
        return results.getString("COLUMN_NAME");
      } else {
        return null;
      }
    } finally {
      results.close();
      getConnection().commit();
    }
  } catch (SQLException sqlException) {
    LoggingUtils.logAll(LOG, "Error reading primary key metadata: "
        + sqlException.toString(), sqlException);
    return null;
  } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      return null;
    }
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
private String exctractPrimaryKeyColumn( String tableName, DatabaseMetaData databaseMetaData, String schemaPattern )
                                                                                                                     throws SQLException {

    ResultSet pkResultSet = databaseMetaData.getPrimaryKeys(null, schemaPattern, tableName);
    while (pkResultSet.next()) {
        // return the primary key column
        return pkResultSet.getString(4);
    }

    // no primary key for this table
    return null;
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Finds the primary keys for the {@code tableName}.
 *
 * @param tableName the table to query for.
 * @return a collection of the primary keys.
 */
protected List<String> getPrimaryKeys(String tableName) {
  List<PrimaryKeyColumn> columns = newArrayList();
  try {
    final DatabaseMetaData databaseMetaData = connection.getMetaData();

    ResultSet primaryKeyResults = databaseMetaData.getPrimaryKeys(null, schemaName, tableName);

    try {
      while (primaryKeyResults.next()) {
        columns.add(new PrimaryKeyColumn(primaryKeyResults.getShort(5), primaryKeyResults.getString(4)));
      }
    } finally {
      primaryKeyResults.close();
    }
  } catch (SQLException sqle) {
    throw new RuntimeSqlException("Error reading primary keys for table [" + tableName + "]", sqle);
  }

  List<PrimaryKeyColumn> sortedColumns = Ordering.from(new Comparator<PrimaryKeyColumn>() {
    @Override
    public int compare(PrimaryKeyColumn o1, PrimaryKeyColumn o2) {
      return o1.sequence.compareTo(o2.sequence);
    }
  }).sortedCopy(columns);

  // Convert to String before returning
  return transform(sortedColumns, new Function<PrimaryKeyColumn, String>() {
    @Override
    public String apply(PrimaryKeyColumn input) {
      return input.name;
    }
  });
}
 

import java.sql.DatabaseMetaData; //导入方法依赖的package包/类
/**
 * Gets tables.
 *
 * @param connection the connection
 * @param keys keys
 * @param tablesOverride the tables override @return the tables
 * @throws SQLException the sql exception
 */
public Map<String, Map<String, Object>> getTables(Connection connection, String keys,
                                                  Map<String, Map<String, Object>> tablesOverride) throws SQLException {
    Map<String, Map<String, Object>> dataMap = new HashMap<>(16);
    DatabaseMetaData metaData = connection.getMetaData();
    // 处理key列表
    List<String> keyList = new ArrayList<>(1);
    if (keys != null) {
        String[] keyArray = keys.split(",");
        keyList = Arrays.asList(keyArray);
    } else {
        keyList.add("%");
    }
    // 根据key列表抽取指定表
    for (String key : keyList) {
        ResultSet resultSet = metaData.getTables(null, null, key, null);
        while (resultSet.next()) {
            Map<String, Object> table = new HashMap<>(4);
            String tableName = resultSet.getString(TABLE_NAME);
            LOGGER.info("抽取表结构>>{}", tableName);
            // 获取主键
            ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableName);
            if (primaryKeys.next()) {
                String primaryColumn = primaryKeys.getString(COLUMN_NAME);
                table.put("primaryColumn", primaryColumn);
                table.put("primaryName", StringUtils.getCamelCaseString(primaryColumn, false));
                table.put("primaryAuto", false);
            } else {
                LOGGER.warn("获取表{}主键失败,跳过该表", tableName);
                continue;
            }
            table.put("tableName", tableName);
            table.put("comment", resultSet.getString(REMARKS));
            String className = StringUtils.getCamelCaseString(tableName, true);
            table.put("className", className);

            table.put("imports", new HashSet<>(5));

            // 使用配置文件进行覆盖
            Map<String, Object> tableOverride = tablesOverride.get(tableName);
            Map<String, Map<String, Object>> columnsOverride = new HashMap<>(0);
            if (tableOverride != null) {
                table.putAll(tableOverride);
                table.remove("columns");
                List<Map<String, Object>> columnsOverrideList = (List<Map<String, Object>>) tableOverride.get("columns");
                if (columnsOverrideList != null) {
                    columnsOverride = columnsOverrideList.stream().collect(Collectors.toMap(o -> (String) o.get("columnName"),
                                                                                            o -> o));
                }
            }
            List<Map<String, Object>> columns = getColumns(connection, table, columnsOverride);
            table.put("columns", columns);
            dataMap.put(tableName, table);
        }
    }
    return dataMap;
}