show tables with like using Java and MySQL?

Question

i have some jsp file. it has some variables. i want to use these varibles inside a mysql query as follows.

String given_session="monsoon";
String given_year="2012";
// note above two will be dynamically added.

ResultSet rs11 = (ResultSet) st11.executeQuery("show tables like '%_Assessment_" + given_session + "_" + given_year+"'");

I got the following exception :

 java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%_Assessment_Monsoon_2013''

You should read MySQL docs http://dev.mysql.com/doc/refman/5.0/en/show-tables.html — Germann Arlington, Oct 21 '13 at 17:36
Your error message has two single quotes at the end. Are you sure you're not passing those in your SQL statement? — Dawood ibn Kareem, Oct 21 '13 at 18:05

score 3 · Answer 1 · edited Jul 09 '14 at 20:39

There is a better and more elegant way.

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

Moreover, you must know the best practices of programming in Java because what you are using in your code is not smart. You can see a lot in Java Collected Practices.

show tables with like using Java and MySQL?

1 Answers1

Linked