How to retrieve all the tables from database using Java

Question

I am using JDBC and PostgreSQL as database, I was trying to create a logic in such a way that we can fetch all the data from a table, whatever table name user gives in the input it should get fetched, but the issue here is, I don't know how to do that.

Whenever we used to fetch table data from the database we are required to specify the the type of data we are getting on every index while we use ResultSet.

How to overcome from this hardcoded need of providing this metadata and make our code more general for any table with any number of columns and with any type

My code:

Statement sttm = con1.createStatement();
System.out.println("Enter table name (usertable)");
String name = sc.next();
String tableData="";

String qu = "select * from "+name;
ResultSet rs =sttm.executeQuery(qu);
while(rs.next()) {
    // here we need to define the type by writing .getInt or getString
    tableData = rs.getInt(1)+":"+rs.getString(2)+":"+rs.getInt(3);
    System.out.println(tableData);
}
System.out.println("*********---------***********-----------**********");
sttm.close();

Anyone please suggest me some way to do it.

Mark Rotteveel · Accepted Answer · 2021-07-06 08:50:07Z

1

You can use ResultSet.getObject(int). getObject will automatically retrieve the data in the most appropriate Java type for the SQL datatype of the column.

To retrieve the number of columns, you can use ResultSet.getMetaData(), and then use ResultSetMetaData.getColumnCount() to retrieve the number of columns.

In short, to print all columns of all rows, you can do something like:

try (ResultSet rs = stmt.executeQuery(qu)) {
    ResultSetMetaData rsmd = rs.getMetaData();
    int columnCount = rsmd.getColumnCount();
    while (rs.next()) {
        StringBuilder tableData = new StringBuilder();
        for (int colIdx = 1; colIdx <= columnCount; colIdx++) {
            tableData.append(rs.getObject(colIdx));
            if (colIdx != columnCount) {
                tableData.append(':');
            }
        }
        System.out.println(TableData);
    }
}

You can also use ResultSetMetaData to get more information on the columns of the result set, for example if you need specific handling for certain types of columns. You can use getColumnType to get the java.sql.Types value of the column, or getColumnTypeName to get the type name in the database, or getColumnClassName to get the name of the class returned by ResultSet.getObject(int/String), etc.

However, as Sorin pointed out in the comments, accepting user input and concatenating it into a query string like you're currently doing, makes you vulnerable to SQL injection. Unfortunately, it is not possible to parameterize object names, but you can mitigate this risk somewhat by 1) checking the table against the database metadata (e.g. DatabaseMetaData.getTables), and 2) using Statement.enquoteIdentifier (though this won't necessarily protect you against all forms of injection).

edited Jul 6, 2021 at 8:50

answered Jul 5, 2021 at 19:59

Mark Rotteveel

110k241 gold badges160 silver badges233 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sorin Over a year ago

Should use a PreparedStatement though to guard against SQL Injection via user supplied table name.

Chitrang Sharma Over a year ago

@Sorin right, is there any way to do it in jdbc ? is that above code will help !

Mark Rotteveel Over a year ago

@Sorin You can't parameterize object names like tables, so you cannot use a PreparedStatement to protect against SQL injection at that point. However, JDBC 4.3, does provide Statement.enquoteIdentifier to address this somewhat.

Rohit gupta Over a year ago

just replace qu with "show tables;"

Mark Rotteveel Over a year ago

@Rohitgupta That is a database specific solution, while the DatabaseMetaData.getTables is database agnostic. As an aside, it wouldn't actually address the question, as that asks about retrieving all data from any table, not about retrieving all the tables (not withstanding the title).

Rohit gupta · Accepted Answer · 2022-03-31 17:52:11Z

0

If you want to print data of any table from a database then check my github project over CRUD java MySQL

https://github.com/gptshubham595/jdbc_mysql_CRUD-JAVA-

These are implemented

answered Mar 31, 2022 at 17:52

Rohit gupta

2814 silver badges22 bronze badges

Collectives™ on Stack Overflow

How to retrieve all the tables from database using Java

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related