I'm working on a java application, and I want to display the list of the tables in a Mysql database, I searched on the net and I found that "SHOW TABLES FROM [DB]" is the appropriate request, but how can I dispaly the result of this last on a java swing/awt application, PS : I tried to put the result of the request on a resultset,
res = sta.executeQuery("SHOW TABLES FROM sinpec ");
but it doesnt work ...
How can I proceed ?
Please try this query:-
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = "DATABASE_NAME"
According to this blog, you could use the following statement:
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema='test';
Which seems to have a similar effect to that of SHOW TABLES.
This should work with the result result that you are using.
This example may help you. Basically you can use DatabaseMetaData class to full fill your task.
public static String SCHEMA_NAME="${YOUR_SCHEMA_NAME}";
public static void main(String[] args) {
//create and setup your database and get db connection
DataBase db = new DataBase();
db.init();
try {
Connection con = db.getConnection();
DatabaseMetaData metaData = con.getMetaData();
String tableType[] = {"TABLE"};
StringBuilder builder = new StringBuilder();
ResultSet result = metaData.getTables(null,SCHEMA_NAME,null,tableType);
while(result.next())
{
String tableName = result.getString(3);
builder.append(tableName + "( ");
ResultSet columns = metaData.getColumns(null,null,tableName,null);
while(columns.next())
{
String columnName = columns.getString(4);
builder.append(columnName);
builder.append(",");
}
builder.deleteCharAt(builder.lastIndexOf(","));
builder.append(" )");
builder.append("\n");
builder.append("----------------");
builder.append("\n");
}
System.out.println(builder.toString());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
You can try this solution it shows all the tables
try {
DbMd = myconn.getMetaData();
Rslt = DbMd.getTables(null, null, "%", null);
while (Rslt.next()) {
System.out.println(Rslt.getString(3));
}
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
resultsetin a loop and pass the values to an appropriate view.