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 and show tables with like using Java and MySQL? — MadProgrammer
– MadProgrammer, Commented Jul 30, 2014 at 5:53
The connection object has a method "getMetaData()" which can give you the information you want. It might do what you want. — nablex
– nablex, Commented Jul 30, 2014 at 6: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..? stackoverflow.com/questions/2780284/… — Giftson David
– Giftson David, Commented Aug 2, 2014 at 15:38

Giftson David · Accepted Answer · 2016-02-10 08:34:52Z

1

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));                
        }

edited Feb 10, 2016 at 8:34

answered Aug 2, 2014 at 16:05

Giftson David

232 silver badges10 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:07:34Z

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, 2017 at 12:07

CommunityBot

11 silver badge

answered Aug 2, 2014 at 16:09

Avinash Babu

6,2534 gold badges23 silver badges26 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related