Recommended method to Keyword Search in whole MySQL DB via JavaWeb

Question

I am trying to achieve a keyword search from my whole MySQL DB via JSP, I am confused if the method I chose is inefficient :(

I've read about the information_schema and found all column labels are there.

I've tried the SQL statement below to generate all the possible queries:

SELECT CONCAT('SELECT * FROM ',table_schema,'.',table_name,
            ' WHERE ',column_name,' LIKE ','searchString',';')
      FROM information_schema.columns
      WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
      AND (column_type LIKE 'char(%'
      OR column_type LIKE 'varchar(%'
      OR column_type LIKE '%text')

and tried JSP code to get all the data match possible.

<%
    String searchString =   "malayalam";
            searchString        =   "''%"+searchString+"%'' ";    
            ArrayList<String> queries =   new ArrayList<String>();
            String sql="SELECT CONCAT('SELECT * FROM ',table_schema,'.',table_name,"
                + "' WHERE ',column_name,' LIKE ','"+searchString+"',';')"
                + "FROM information_schema.columns "
                + "WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')"
                + "AND (column_type LIKE 'char(%'"
                + "OR column_type LIKE 'varchar(%'"
                + "OR column_type LIKE '%text')";
            try{
                DBConInfoSchema db =   new DBConInfoSchema();
                ResultSet rs    =   db.getData(sql);
                while(rs.next()){
                    queries.add(rs.getString(1));
                }
                for(int i=0;i<queries.size();i++){
                    DBConInfoSchema dCon    =   new DBConInfoSchema();
                    ResultSet rsDemo        =   dCon.getData(queries.get(i));
                    if(rsDemo.next()){
                        out.print("<br/>Data found n query-"+i+" ->     "+queries.get(i));
                    }
                    dCon.DBClose();
                }
            }catch(Exception w){
                out.print("excep<br/>"+w);
            }
%>

Now I got huge list of Queries. I am confused if its Good or Bad?! Is this bad method when considering efficiency?

Answer 1

In the query building section

SELECT CONCAT('SELECT * FROM ',table_schema,'.',table_name,
            ' WHERE ',column_name,' LIKE ','searchString',';')
      FROM information_schema.columns
      WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
      AND (column_type LIKE 'char(%'
      OR column_type LIKE 'varchar(%'
      OR column_type LIKE '%text')

I have updated line

WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')

to

 WHERE table_schema IN ('<my_table_name>')

to generate all the search results in my_table_name and saved to ArrayList and executed them

ArrayList<String> queries =   new ArrayList<String>();
DBConInfoSchema db =   new DBConInfoSchema();
ResultSet rs    =   db.getData(sql_statement);
while(rs.next()){
  queries.add(rs.getString(1));
}

from there I sorted out the required data.

-Hope this may help some one.

NB: Even though it worked for me I still believe its Naive Correct me If there's a better way yo achieve this