I want to write a SpringBatch Tasklet, that automatically activates or de-activates all indexes for a given database table. The code needs to work independantly of the DBMS (SQL Server, Oracle and HSQLDB are required).
This is what I have tried so far:
DatabaseMetaData dbMetaData = connection.getMetaData();
ResultSet rs = dbMetaData.getIndexInfo(null, null, tableName, true, false);
while (rs.next()) {
// work with ResultSet
}
However, I do not get the names of the Indexes or any useful information.
So could anyone give some hints on how to set all indexes of table to active or inactive with just a JDBC connection object?
To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA. STATISTICS WHERE TABLE_SCHEMA = 'your_schema'; Removing the where clause will show you all indexes in all schemas.
You can get the list of tables in the current database in MySQL using the SHOW TABLES query. Show tables; Following JDBC program retrieves the list of tables in the database by executing the show tables query.
Start-Control Panel- Administrative Tools- Data Sources (ODBC)-go to system DSN tab-click add button-select a driver for which you want to set up a data source (for Oracle- Oracle in XE)-select it and click finish-give any name in data source name textbox-then click ok button. Class. forName("sun. jdbc.
The next() method of the ResultSet interface moves the pointer of the current (ResultSet) object to the next row, from the current position. Statement stmt = con. createStatement(); ResultSet rs = stmt. executeQuery("Select * from MyPlayers"); rs. next();
You have to make a difference between primary keys (using DatabaseMetaData.getPrimaryKeys()
to retrieve) and other indexes (via dbMetaData.getIndexInfo(null, null, tableName, true, false)
).
In your loop use:
rs.getString("INDEX_NAME")
to extract index namers.getBoolean("NON_UNIQUE")
to extract unique informationrs.getShort("TYPE")
to extract index typers.getInt("ORDINAL_POSITION")
to extract ordinal positionUse ORDINAL_POSITION
as key break (when current value is <= of previous one) to detect index change.
Read official DatabaseMetaData.getIndexInfo() doc
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With