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 dev.mysql.com/doc/refman/5.0/en/show-tables.html — Germann Arlington
– Germann Arlington, Commented Oct 21, 2013 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
– Dawood ibn Kareem, Commented Oct 21, 2013 at 18:05

Andrzej Bobak · Accepted Answer · 2014-07-09 20:39:05Z

3

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.

edited Jul 9, 2014 at 20:39

Andrzej Bobak

2,1303 gold badges28 silver badges36 bronze badges

answered Oct 21, 2013 at 19:20

Paul Vargas

42.2k16 gold badges108 silver badges148 bronze badges

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

Collectives™ on Stack Overflow

show tables with like using Java and MySQL?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related