How to execute MySQL query, "show tables;" inside a Java Program..?

Question

I am a newbie to Java and MySQL. Please pardon me if my question seems silly, I want to know the answer... I have gone through many articles and questions asked in forums, but I didn't see a relevant answer for my question... That is, I have made a switch statement in Java and I want to show the list of available tables in a database if I press 1( that is go into the case 1 and execute a query "show tables;" ) In MySQL Console, it is easy to check for available tables using the same query. But I want to know whether "show tables;" query or similar queries can be executed inside a Java Program... Here's a sample snippet of my code,

Connection con=null;
String url = "jdbc:mysql://localhost:3306/giftson";
String dbName = "giftson";
String userName = "root";
String password = "password";
con=DriverManager.getConnection(url,userName,password);
Statement st=con.createStatement();
//String query;
Statement st=con.createStatement();
System.out.println("\tDatabase Connection for Various Operations");
System.out.println("\n1. Show list of tables\n2. Show contents of Table\n3. Create New Table\n4. Insert into table\n5. Update Table\n6. Delete From Table\n7. Exit\n");
System.out.println("Enter your option Number  ");
DataInputStream dis=new DataInputStream(System.in);
int ch=Integer.parseInt(dis.readLine());
switch(ch)
{
    case 1:
        System.out.println(" You have selected to Show list of available tables");
        //ResultSet rs=st.executeQuery("Show tables");
        //while(rs.next())
        //{
        //    System.out.println("List of Tables\n" +rs.getString("?????"));
        //}
        break;
}

from the above piece of code, If I execute the query in ResultSet, How do I print the values inside the while loop..? In rs.getString(); we can only pass either the column index or the column label as argument, but how do I get the list of tables... what do I enter in place of "?????" inside print statement...? please do help me, keeping in mind that you are explaining for a beginner... Thanks in advance...!

Take a look at [JDBC is not executing SHOW DATABASES command](http://stackoverflow.com/questions/12771120/jdbc-is-not-executing-show-databases-command?rq=1) and [show tables with like using Java and MySQL?](http://stackoverflow.com/questions/19501067/show-tables-with-like-using-java-and-mysql) — MadProgrammer, Jul 30 '14 at 05:53
The connection object has a method "getMetaData()" which can give you the information you want. It might do what you want. — nablex, Jul 30 '14 at 06:17
this link shows the answer to my problem but it doesn't explain what is " rs.getString(3) and rs = meta.getTables(null, null,"%", null); " what does this (3) and (null, null, "%", null); corresponds to..? http://stackoverflow.com/questions/2780284/how-to-get-all-table-names-from-a-database — Giftson David, Aug 02 '14 at 15:38

Giftson David · Accepted Answer · 2016-02-10T08:34:52.937

We can use the console commands using,

DatabaseMetaData meta=getMetaData();

In the below code, it is shown that there are many ways (but I came to know two ways) of getting the list of tables

        DatabaseMetaData meta = con.getMetaData();
        ResultSet rs1 = meta.getTables(null, null, null,new String[] {"TABLE"});
        ResultSet rs2 = meta.getTables(null, null,"%", null);
        System.out.println("One way of Listing Tables");
        while (rs1.next())
        {
        System.out.println(rs1.getString("TABLE_NAME"));
        }
        System.out.println("Another way of Listing Tables");
        while(rs2.next())
        {
        System.out.println(rs2.getString(3));                
        }

score 0 · Answer 2 · edited May 23 '17 at 12:07

0

A small example would be

String tableNamePattern  = "%_Assessment_" + session + "_" + year;
DatabaseMetaData databaseMetaData = conn.getMetaData();
ResultSet rs = databaseMetaData.getTables(null, null, tableNamePattern, 
                                      null);
while(rs.next()) {
String tableName = rs.getString("TABLE_NAME");
...
}

Check the source

edited May 23 '17 at 12:07

Community

1
1

answered Aug 02 '14 at 16:09

Avinash Babu

6,171
3
21
26

How to execute MySQL query, "show tables;" inside a Java Program..?

2 Answers2