Filtering rows from a resultset

Question

I have created a java method that returns a resultset like this:

String query = "SELECT * FROM "+table_name+" WHERE "+parameter+ " = " +condition;
ResultSet rs = stmt.excecuteQuery(query);
return rs;

My understanding is that ResultSet can be though of like a SQL table, and Im looking for a way to further select specific rows from this resultset.

Something like another method:

public ResultSet filterRS (ResultSet rs){
String query = "SELECT * FROM "+rs+" WHERE "+new_parameter+ " = " +new_condition;
ResultSet rs_new = stmt.excecuteQuery(query);
return rs_new;
}

Ive read the docs but it says nothing of how to do this. Is it possible in a simple way?

score 0 · Answer 1 · answered Oct 17 '18 at 19:56

Just a note: you should not be conactonating your queries like that. You should bind the arguments in the statement like so:

    String queryString = "SELECT * FROM FOO WHER BAR = ?1 ";
    Query q = em.createNativeQuery(queryString);
    q.setParameter(1, conditionGoesHere);
    List <Result> result = (TCiletEpsTracking) query.getResultList();

Once you have the resultset, there's a couple of ways you can do it. I have always gone the old-fashioned way by looping over the entire result set, and adding entries to a List if they meet my condition(s).

If you're using Java 8 though, using streams I think could be better for you. Here's some literature here : https://beginnersbook.com/2017/10/java-8-stream-filter/

Though technically possible, this solution is very inefficient. It defeats all optimization on the database side. — The Impaler, Oct 17 '18 at 19:57
I agree, if the database has the data by which you are filtering your results, you should be doing it there. If the filtering must be done on the Java side, that's the best option(s) I know of. — Josh, Oct 17 '18 at 20:00

score 0 · Answer 2 · answered Oct 17 '18 at 19:56

0

No, you cannot.

You need to specify all the filtering conditions in the first query, as in:

select * from my_table
WHERE column1 = x
  AND column2 = y

Then, you run this SQL and you get the rows you want. You can have any combination of AND and OR.

answered Oct 17 '18 at 19:56

The Impaler

45,731
9
39
76

Clarius · Answer 3 · 2022-05-30T13:31:34.720

I wanted to write a generic solution allowing a user to submit a search criteria and have a servlet return all rows where some field matched the search criteria. I used Java's FilteredResultSet to accomplish this rather than construct a SELECT * FROM table WHERE field1 = criteria OR field2 = criteria, ... and so on and so forth. Here's the bare bones working code.

import java.sql.*;
import java.util.regex.*;
import javax.sql.*;
import javax.sql.rowset.*;

public class CustomFilter {

    private static class SearchFilter implements Predicate {

        private Pattern pattern;    // it's a regular expression

        public SearchFilter(String searchRegex){
            if ( searchRegex != null && !searchRegex.isEmpty() ){
                pattern = Pattern.compile(searchRegex);
            }
        }

        public boolean evaluate(RowSet rset){
            boolean result = false;
            try{
                // test each column to see if it's a match
                ResultSetMetaData rsmd = rset.getMetaData();
                for ( int index = 1; index <= rsmd.getColumnCount(); index++ ){ // not a zero based array
                    String columnValue = rset.getString(index);
                    if ( columnValue != null ){ // it's possible some columns will be null
                        Matcher matcher = pattern.matcher(columnValue);
                        if ( matcher.matches() )
                            return true;    // only one column has to match for the row to be included
                    }
                }           
            }catch(Exception e){
                e.printStackTrace(System.err);
            };
            return false;   // no match, no include
        }

        // don't omit these method stubs

        public boolean evaluate(Object value, int column) throws SQLException{
            throw new UnsupportedOperationException("implementation pending...");
        }

        public boolean evaluate(Object value, String columnName) throws SQLException{
            throw new UnsupportedOperationException("implementation pending...");
        } 

    }

    public static void main(String[] args){

        Connection conn = null;
        Statement  stmt = null;
        ResultSet  rset = null;

        try{

            // register the driver
            Class.forName("com.mysql.jdbc.Driver");

            // get a connection
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mes", "root", "root");

            // create a statement
            stmt = conn.createStatement();

            // get the unfiltered results
            String sql = "SELECT * FROM employee";

            rset = stmt.executeQuery(sql);

            // create a filtered result set
            FilteredRowSet frst = RowSetProvider.newFactory().createFilteredRowSet();

            // populate it with the unfiltered results
            frst.populate(rset);

            // create and apply a filter (filters utilise regular expressons)
            frst.setFilter(new SearchFilter("^August"));

            // dump the results
            while ( frst.next() ){
                System.out.println(String.format("%s-%s", frst.getString("forename"), frst.getString("surname")));
            }

        }catch(Exception e){
            e.printStackTrace(System.err);
        }

    }

}

Here's how I compiled and ran it on a Raspberry Pi.

java -cp /usr/share/java/mysql.jar CustomFilter.java

For anyone reading my answer above please note that I added it only for academic completeness in order I might provide a possible solution to the question asked as was (as a Java question).

Filtering rows from a resultset

3 Answers3