我需要以编程方式在数据存储中生成序列,但需要能够检测它们的存在并在已存在时不创建。有人知道提取此信息所需的JDBC元数据吗?
DatabaseMetadata 的粗略扫描未显示出合适的方法;我可以获取该模式下的所有表/视图以及相关的键/索引等,但不能获取序列。有人知道一种方法,最好是与数据库无关的,但如果不行,尽可能支持多个数据库(想到Oracle有一个user_sequence表?但这只是一个数据库,我需要支持其他数据库)。
提前致谢。
提前致谢。
public static void main(String[] args) {
Connection jdbcConnection = null;
try {
jdbcConnection = DriverManager.getConnection("", "", "");
String sequenceName = "xyz" ; // name of sequence for check
System.out.println("Check Sequence :" + checkSequenceName(sequenceName, jdbcConnection));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(jdbcConnection != null) {
try {
jdbcConnection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
public static boolean checkSequenceName(String sequenceName, Connection conn) throws JDBCConnectionException, SQLException {
DialectResolver dialectResolver = new StandardDialectResolver();
Dialect dialect = dialectResolver.resolveDialect(conn.getMetaData());
if ( dialect.supportsSequences() ) {
String sql = dialect.getQuerySequencesString();
if (sql!=null) {
Statement statement = null;
ResultSet rs = null;
try {
statement = conn.createStatement();
rs = statement.executeQuery(sql);
while ( rs.next() ) {
if(sequenceName.equals(rs.getString(1))) {
return true;
}
}
}
finally {
if (rs!=null) rs.close();
if (statement!=null) statement.close();
}
}
}
return false;
}
如果你不想使用Hibernate,那么你需要创建自定义的顺序特定实现。
自定义实现的示例代码:
interface SequenceQueryGenerator {
String getSelectSequenceNextValString(String sequenceName);
String getCreateSequenceString(String sequenceName, int initialValue, int incrementSize);
String getDropSequenceStrings(String sequenceName);
String getQuerySequencesString();
}
class OracleSequenceQueryGenerator implements SequenceQueryGenerator {
@Override
public String getSelectSequenceNextValString(String sequenceName) {
return "select " + getSelectSequenceNextValString( sequenceName ) + " from dual";
}
@Override
public String getCreateSequenceString(String sequenceName,
int initialValue, int incrementSize) {
return "create sequence " + sequenceName + " start with " + initialValue + " increment by " + incrementSize;
}
@Override
public String getDropSequenceStrings(String sequenceName) {
return "drop sequence " + sequenceName;
}
@Override
public String getQuerySequencesString() {
return "select sequence_name from user_sequences";
}
}
class PostgresSequenceQueryGenerator implements SequenceQueryGenerator {
@Override
public String getSelectSequenceNextValString(String sequenceName) {
return "select " + getSelectSequenceNextValString( sequenceName );
}
@Override
public String getCreateSequenceString(String sequenceName,
int initialValue, int incrementSize) {
return "create sequence " + sequenceName + " start " + initialValue + " increment " + incrementSize;
}
@Override
public String getDropSequenceStrings(String sequenceName) {
return "drop sequence " + sequenceName;
}
@Override
public String getQuerySequencesString() {
return "select relname from pg_class where relkind='S'";
}
}
public boolean checkSequence (String sequenceName, SequenceQueryGenerator queryGenerator, Connection conn) throws SQLException {
String sql = queryGenerator.getQuerySequencesString();
if (sql!=null) {
Statement statement = null;
ResultSet rs = null;
try {
statement = conn.createStatement();
rs = statement.executeQuery(sql);
while ( rs.next() ) {
if(sequenceName.equals(rs.getString(1))) {
return true;
}
}
}
finally {
if (rs!=null) rs.close();
if (statement!=null) statement.close();
}
}
return false;
}
public static void main(String[] args) {
Connection jdbcConnection = null;
try {
jdbcConnection = DriverManager.getConnection("", "", "");
String sequenceName = "xyz" ; // name of sequence for check
System.out.println(checkSequence(sequenceName, new OracleSequenceQueryGenerator(), jdbcConnection));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(jdbcConnection != null) {
try {
jdbcConnection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
Connection.getMetaData
包装在DatabaseMetaDataDialectResolutionInfoAdapter
中。 - Kalle RichterDialect.get...String
方法返回null,就抛出异常,因为null
不是一个文档化的返回值。 - Kalle Richterinterface ISequenceChecker{ // or some name which suits you
SequenceObject getSequence();
}
不同数据库/存储的实现(例如,针对Oracle的实现如下):
public class OracleSequenceChecker implements ISequenceChecker{
OracleSequenceObject getSequence(){
// some jdbc or similar call
// to get SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL
}
}
我使用Postgres 9、Java 8和JDBC 4.2进行了测试。
为了检索序列列表,我做了以下操作:
结果如下:
* FOREIGN TABLE
* INDEX
* MATERIALIZED VIEW
* SEQUENCE
* SYSTEM INDEX
* SYSTEM TABLE
* SYSTEM TOAST INDEX
* SYSTEM TOAST TABLE
* SYSTEM VIEW
* TABLE
* TEMPORARY INDEX
* TEMPORARY SEQUENCE
* TEMPORARY TABLE
* TEMPORARY VIEW
* TYPE
* VIEW
使用Postgres JDBC驱动程序,有一个名为“SEQUENCE”的表类型。
然后我像这样使用java.sql.DatabaseMetaData#getTables:
String catalog = "";
String tableNamePattern = "%";
String schemaPattern = "my_schema";
ResultSet tablesRS = cnx.getMetaData().getTables(catalog, schemaPattern, tableNamePattern, new String[] {"SEQUENCE"});
tablesRS.getString("TABLE_NAME")
会给出找到的每个序列的名称。
我创建了一个实用类ExtractMetadataUtil来测试这个功能(在GitHub上)。