Error with getting all tables from a database using DatabaseMetaData

Question

I'd like to retrieve all tables from a database, in order to count their rows and eventually count all elements currently in the database. I'm trying to do this using Java's DatabaseMetaData.

This is the contents of my constructor, which is responsible for establishing the connection to the database:

String driver = "com.mysql.cj.jdbc.Driver";
String fullUrl = "jdbc:mysql://"+ url + "/" + db;
  
try {
    Class.forName(driver);
    setConn(DriverManager.getConnection(fullUrl, username, password));
}

The connection is made to a WAMP server, and is fully functional based on many attempts before this. Could someone help me with this?

This is the method I'm implementing to count all elements in the database, and the countRowsInTable method is already implemented and working.

public int countElementsInDB(String dbName) {
    DatabaseOperations db = new DatabaseOperations("localhost:3306", dbName, user, password);
    try {
        DatabaseMetaData databaseMetaData = conn.getMetaData();
        String[] types = {"TABLE"};
        ResultSet rs1 = databaseMetaData.getTables(null, null, "%", types);
        int counter = 0;
        while(rs1.next()){
            counter += countRowsInTable(rs1.getString("TABLE_NAME"));
        }
        return counter;
    } catch (Exception e) {
        e.printStackTrace();
    }
    return -1;
}

The code is working, but is in fact returning all tables from all databases present in the server, even though the URL I'm using indicates the database in question here. How do I get it to only return tables from the database I'm targeting?

You probably need to pass the database name as the first parameter to `getTables(..)` (`catalog`), maybe the second parameter. — Mark Rotteveel, Nov 23 '21 at 15:45
You need to set the database name in the second parameter of the getTables method — Thallius, Nov 23 '21 at 15:46
Thank you both for your help. But, passing the database name in first or second parameter as you suggested didn't work. In fact, passing it as the first parameter returned an empty `ResultSet`, and passing it in the second parameter led to the same output as my original code. I've been wondering if there's a problem server-side ? I say this because the same connection targeted only the concerned database and not all of them. — Zouhair, Nov 24 '21 at 12:58

score 0 · Answer 1 · answered Nov 28 '21 at 20:34

A friend made the following suggestion that only works if one has access to the server and its databases and is allowed to change the tables within. For example, if the targetted database is "human_resources", we can simply alter all its tables to have a prefix such as "hr" and then pass it as a third argument in the getTables method :

ResultSet rs1 = databaseMetaData.getTables(null, null, "hr%", types);

This solves the issue, but requires a change in the database. Still a good enough solution for me.

Error with getting all tables from a database using DatabaseMetaData

1 Answers1