CODEWITHSUNDEEP
javajdbcvectorjtabledefaulttablemodel
David Jahn
David Jahn

Reputation: 11

my gives an empty resultSet. My JTable is empty. It shouldn't be

The problem seems to be with the ‘generic’ syntax in the setInt and setString methods. - of course, could be other problems as well but let me tell you what I found.

Below is my table in mySql that I’m trying to query using java.

Id   | CashFlowSensitive | Sp1
-----------------------------------
  37 |                 1 | Hello
1000 |                 0 | Hello
1401 |                 0 | number3
1500 |                 0 | number4
1504 |                 1 | Hello

If I code like “give me all rows where CashFlowSensitive=1 and Sp1 = “Hello”, then my code works. It gives me the first and last row as they exactly correspond to the filtering criteria.

Now, imagine that I change the last row in my table, replacing the Hello entry for number5. The table then looks like this

Id   | CashFlowSensitive | Sp1
-----------------------------------
  37 |                 1 | Hello
1000 |                 0 | Hello
1401 |                 0 | number3
1500 |                 0 | number4
1504 |                 1 | number5

Finally I launch my query asking it to give me all rows where ‘CashFlowSensitive’ = 1. I should get 37 and 1504. Sadly, this gives an empty resultSet. My JTable is empty. In this failing example I do:

public void searchStringTest( DefaultTableModel model, Database db, int CashFlowSensitive, String Sp1 )
{
    int first = -77;
    String second = "-55"; 

    String queryTest = " select * from helm.activitiesextended where CashFlowSensitive = ? and Sp1 = ?  ";

    try 
    {
        java.sql.PreparedStatement selectStmt = CON.prepareStatement( queryTest );

        if ( first == -77 ) {
            ((PreparedStatement) selectStmt).setInt(1, 1 );
        } else {
            ((PreparedStatement) selectStmt).setInt(1, '%' + CashFlowSensitive + '%' );    
        }

        if ( second == "-77" ) {
            ((PreparedStatement) selectStmt).setString(2, "Hello");
        } else {
            ((PreparedStatement) selectStmt).setString(2,  "%" + Sp1 + "%" );
        }            

        ResultSet rsTest = selectStmt.executeQuery();

        model.setRowCount(0);         

        while ( rsTest.next())            
        {
            Vector <Object> vector = new Vector<Object>();
            for ( int columnIndex = 1; columnIndex <=4;  columnIndex ++ )
            {
                vector.add( rsTest.getObject ( columnIndex ) );// pick next column
            }
            data.add(vector);

            model.addRow(vector);
        }

I have a searchString declared as ( int CFS, int ToBeMonitored, String Sp1, String Sp2, String Sp3 and an addition 7 paramaters ) I defined my query string as: select * from table where CFS = ? and ToBeMonitored = ? and Sp1 = ? and Sp2 = ? and Sp3 = ? etc enumerating ten parameters. Now, at any given run of this query I only activate max 4 parameters, for example say I want to filter out 2 par: CFS and Sp1, remaining parameters should be neutral to the filtering process, I call this 'neutral' . In so doing I set up prepared statements ((PreparedStatement)selectStmt).setInt(1, 1);// filter on this one ((PreparedStatement)selectStmt).setInt(2, '%' + ToBeMonitored + '%'); // accept any value ((PreparedStatement)selectStmt).setString(3, "Hello");// filter on this one ((PreparedStatement)selectStmt).setString(4, "%" + Sp2 + "%"); ((PreparedStatement)selectStmt).setString(5, "%" + Sp3 + "%"); As you can see, I'm focing my sql to honor parameter 1 and 3 whilst ignoring the other parameters, sort of making them neutral in the filtering process. Problem: no resultset created despite valid data.

Your answer is a bit off the question I raised. I want you to honor the fact that the same query string is to be retained over and over again regardless what parameters I want to filter at each separate run. For example, if I only 'turn on' the CashFlowSensitive flag then that particular parameter should be the active one when running the query. The other parameters should behave in a neutral fashion to the filtering process. At other times I may choose to to activate the Sp1 field ( a CHAR field) then the other fields should lie dormant. Still at other occasions I may pick a combination of several fields, the those should be active and the not chosen fields should be neutral. Let me reduce the scope of your assignment. 1) tell me how to query a CHAR field using bind parameters where the field should be neutral in the filtering process, in other words any content should be accepted 2) can you also tell me how I can check the query string that the software generates

Upvotes: 0

Views: 175

Answers (1)

MicSim
MicSim

Reputation: 26816

The problem is passing in the value for the second parameter in the else path. Your final query will look like:

select * from helm.activitiesextended
where CashFlowSensitive = 1 and Sp1 = '%<value of Sp1>%'

This obviously returns no records. You should query only the first parameter, if you just need that one. Something like:

select * from helm.activitiesextended where CashFlowSensitive = 1

Upvotes: 1

Related Questions