Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I'd like to retrieve all table names from a database schema, and, if possible, get all table starting with a specified prefix.
I tried using JDBC's
connection.getMetaData().getTables()
but it didn't work at all.
Connection jdbcConnection = DriverManager.getConnection("", "", "");
DatabaseMetaData m = jdbcConnection.getMetaData();
ResultSet tables = m.getTables(jdbcConnection.getCatalog(), null, "TAB_%", null);
for (int i = 0; i < tables.getMetaData().getColumnCount(); i++) {
System.out.println("table = " + tables.getMetaData().getTableName(i));
Could someone help me on this?
You need to iterate over your ResultSet calling next()
.
This is an example from java2s.com:
DatabaseMetaData md = conn.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
System.out.println(rs.getString(3));
Column 3 is the TABLE_NAME
(see documentation of DatabaseMetaData::getTables
).
–
–
–
DatabaseMetaData dbmd = conn.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = dbmd.getTables(null, null, "%", types);
while (rs.next()) {
System.out.println(rs.getString("TABLE_NAME"));
catch (SQLException e) {
e.printStackTrace();
–
–
In your example problem is passed table name pattern in getTables function of DatabaseMetaData.
Some database supports Uppercase identifier, some support lower case identifiers. For example oracle fetches the table name in upper case, while postgreSQL fetch it in lower case.
DatabaseMetaDeta provides a method to determine how the database stores identifiers, can be mixed case, uppercase, lowercase see:http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#storesMixedCaseIdentifiers()
From below example, you can get all tables and view of providing table name pattern, if you want only tables then remove "VIEW" from TYPES array.
public class DBUtility {
private static final String[] TYPES = {"TABLE", "VIEW"};
public static void getTableMetadata(Connection jdbcConnection, String tableNamePattern, String schema, String catalog, boolean isQuoted) throws HibernateException {
try {
DatabaseMetaData meta = jdbcConnection.getMetaData();
ResultSet rs = null;
try {
if ( (isQuoted && meta.storesMixedCaseQuotedIdentifiers())) {
rs = meta.getTables(catalog, schema, tableNamePattern, TYPES);
} else if ( (isQuoted && meta.storesUpperCaseQuotedIdentifiers())
|| (!isQuoted && meta.storesUpperCaseIdentifiers() )) {
rs = meta.getTables(
StringHelper.toUpperCase(catalog),
StringHelper.toUpperCase(schema),
StringHelper.toUpperCase(tableNamePattern),
TYPES
else if ( (isQuoted && meta.storesLowerCaseQuotedIdentifiers())
|| (!isQuoted && meta.storesLowerCaseIdentifiers() )) {
rs = meta.getTables(
StringHelper.toLowerCase( catalog ),
StringHelper.toLowerCase(schema),
StringHelper.toLowerCase(tableNamePattern),
TYPES
else {
rs = meta.getTables(catalog, schema, tableNamePattern, TYPES);
while ( rs.next() ) {
String tableName = rs.getString("TABLE_NAME");
System.out.println("table = " + tableName);
finally {
if (rs!=null) rs.close();
catch (SQLException sqlException) {
// TODO
sqlException.printStackTrace();
public static void main(String[] args) {
Connection jdbcConnection;
try {
jdbcConnection = DriverManager.getConnection("", "", "");
getTableMetadata(jdbcConnection, "tbl%", null, null, false);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
In newer versions of MySQL connectors the default tables are also listed if catalog is not passed
DatabaseMetaData dbMeta = con.getMetaData();
//con.getCatalog() returns database name
ResultSet rs = dbMeta.getTables(con.getCatalog(), "", null, new String[]{"TABLE"});
ArrayList<String> tables = new ArrayList<String>();
while(rs.next()) {
String tableName = rs.getString("TABLE_NAME");
tables.add(tableName);
return tables;
public static ArrayList<String> getTablesList(Connection conn) throws SQLException {
ArrayList<String> listofTable = new ArrayList<String>();
DatabaseMetaData md = conn.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
while (rs.next()) {
if (rs.getString(4).equalsIgnoreCase("TABLE")) {
listofTable.add(rs.getString(3));
return listofTable;
DatabaseMetaData md = conn.getMetaData();
String[] types = {"TABLE"};
ResultSet rs = md.getTables("Your_DB_Name", null, "%", types);
while (rs.next()) {
System.out.println(rs.getString("TABLE_NAME"));
@Transactional
@RequestMapping(value = { "/getDatabaseTables" }, method = RequestMethod.GET)
public @ResponseBody String getDatabaseTables() throws Exception{
Connection con = ((SessionImpl) sessionFactory.getCurrentSession()).connection();
DatabaseMetaData md = con.getMetaData();
ResultSet rs = md.getTables(null, null, "%", null);
HashMap<String,List<String>> databaseTables = new HashMap<String,List<String>>();
List<String> tables = new ArrayList<String>();
String db = "";
while (rs.next()) {
tables.add(rs.getString(3));
db = rs.getString(1);
List<String> database = new ArrayList<String>();
database.add(db);
databaseTables.put("database", database);
Collections.reverse(tables);
databaseTables.put("tables", tables);
return new ObjectMapper().writeValueAsString(databaseTables);
@Transactional
@RequestMapping(value = { "/getTableDetails" }, method = RequestMethod.GET)
public @ResponseBody String getTableDetails(@RequestParam(value="tablename")String tablename) throws Exception{
System.out.println("...tablename......"+tablename);
Connection con = ((SessionImpl) sessionFactory.getCurrentSession()).connection();
Statement st = con.createStatement();
String sql = "select * from "+tablename;
ResultSet rs = st.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int rowCount = metaData.getColumnCount();
List<HashMap<String,String>> databaseColumns = new ArrayList<HashMap<String,String>>();
HashMap<String,String> columnDetails = new HashMap<String,String>();
for (int i = 0; i < rowCount; i++) {
columnDetails = new HashMap<String,String>();
Method method = com.mysql.jdbc.ResultSetMetaData.class.getDeclaredMethod("getField", int.class);
method.setAccessible(true);
com.mysql.jdbc.Field field = (com.mysql.jdbc.Field) method.invoke(metaData, i+1);
columnDetails.put("columnName", field.getName());//metaData.getColumnName(i + 1));
columnDetails.put("columnType", metaData.getColumnTypeName(i + 1));
columnDetails.put("columnSize", field.getLength()+"");//metaData.getColumnDisplaySize(i + 1)+"");
columnDetails.put("columnColl", field.getCollation());
columnDetails.put("columnNull", ((metaData.isNullable(i + 1)==0)?"NO":"YES"));
if (field.isPrimaryKey()) {
columnDetails.put("columnKEY", "PRI");
} else if(field.isMultipleKey()) {
columnDetails.put("columnKEY", "MUL");
} else if(field.isUniqueKey()) {
columnDetails.put("columnKEY", "UNI");
} else {
columnDetails.put("columnKEY", "");
columnDetails.put("columnAINC", (field.isAutoIncrement()?"AUTO_INC":""));
databaseColumns.add(columnDetails);
HashMap<String,List<HashMap<String,String>>> tableColumns = new HashMap<String,List<HashMap<String,String>>>();
Collections.reverse(databaseColumns);
tableColumns.put("columns", databaseColumns);
return new ObjectMapper().writeValueAsString(tableColumns);
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.