PreparedStatement (displaying records)

Hello,

OUTPUT:
=======
selected columns=test and i=2

whereas the OUTPUT should be "1,test"

Please help me

Best regards

You are printing the contents of a.aa, whatever that is. And as you get results you concatenate the next field (cc). Assuming you get more results.

What's going on here? You are going to get an exception if you return more than two results.

Also, what is the variable i for?

Sorry for typo. this is not "i" but its "j" that will iterate

the expected result of this query is: 1, test but now its printing selectedcolums=1 and j=2 that means loop is running right but selectedColums+= is getting just one value.

Can I use selecteColums String to store more than one Strings with + operator?

Thanks for reply

Your declare i as a variable at the start of your program. Why?

You will only concatenate a String if you have two results returned. So you should print the contents of a.aa from the first row concatenated with cc
from the second row. If you have any more than two results you will get an exception.

I would revisit your logic if I were you.

Paul Sturrock wrote:Your declare i as a variable at the start of your program. Why?

first I was trying to store as Array that's why I mentioned on top but the logic was not useful

You will only concatenate a String if you have two results returned. So you should print the contents of a.aa from the first row concatenated with cc
from the second row.

I am trying to concentrate on string but due to incompetency I am unable to concatenate two or more resulted strings.

I am trying to store the resultset in string and to pass it in a method. Please help me again

I am trying to do like this but getting wrong output i.e. 1,
its not getting the next string. Why?

Again, why are you using two variables to track a single number? Could you not just use a single variable?

You know you are looking for two values because you define these in your SQL. Would this not be easier:

Because you assign a value to to your variable slectedColums in a loop this will also concatenate whatever is in each row of your ResultSet. Is that what you intend?

This is what I need rs.getString(1) + rs.getString(2); to automate.

I want to generate rs.getString(int) and then to store them in a string as well as to pass them into another method

Is that possible?

Best regards

I don't understand. Why do you want to automate this? Do you not know how many columns you will be selecting in your SQL?

Paul Sturrock wrote:Do you not know how many columns you will be selecting in your SQL?

Yes, this is the case.

OK. We'll what you are doing is not safe (see my previous comments).

If you need to know about a ResultSet's meta data there is a class that will describe this (java.sql.ResultSetMetaData) so you can safely navigate through the various columns that may be present.

Honestly I never did any work with ResultSetMetaData class but googled on the web and found that this is data about data not to display records/resultSet. I don't know how you recommend this for my case?

Still I am not understanding that why my this code is not working by getting two columns instead of first one

Best regards

Please read my previous message again as I edited

Did you read the JavaDocs? Always better than trying random stuff found elsewhere. There is an example of what you need to do right at the top of the class.

Still I am not understanding that why my this code is not working by getting two columns instead of first one

OK, what you've written:

Means:

For each record in the ResultSet

Get the column indexed at j and assign to the element i in the array aa. First time round j== 1 and i = 0, so you get the field "aa" from the first row in the ResultSet

concatenate this to the variable slectedColums

Increment i and j

So if your result set has one row, slectedColums will be the value in the first column of the first row. If the ResultSet has two rows it will be the value of the first column in the first row concatenated with the value of the second column in the second row. If it has three rows, slectedColums will be the value of the first column in the first row, plus the value of the second column in the second row, plus the value of the third column in the third row etc.

What it won't be is the contents of all columns in the first row. And when you hit a column that is not a text data type, or j references a number higher than the number of columns in your select statement you will get an exception.

What ResultSetMetaData will give you is an understanding of the number of columns in a ResultSet and the type of those columns. Form this its fairly straight forward to write code to get the correct value from each column based on an index.

Now its working fine

Your code is still utterly dependent on the number of results in your ResultSet. Why are you using the variable j? And for that matter why the intermediary step of assigning to an array?

Your code is also dependent on the data type of the columns in your ResultSet always being text. If they are not, unexpected things will occur.

Hi Actually ResultSetMetaData would be useful to know the Column count of the query, Also we could even know even the column names if required. It got many other features.

I got your point .. and hope the below code would be useful for you

All the best

>

Hi Ravi,

Its throwing exception:

and if I am doing j=1 then

Its printing just first column twice not second column

Best regards

int j=0;   
    ArrayList<String> resultList = new ArrayList<String>();   
    ResultSet rs=null;   
    String query = "select a.aa,cc from test a,test1 b where a.aa=b.aa and a.aa=?";      
        try {    
            PreparedStatement ps=cnn.prepareStatement(query);      
            ps.setString(1, "1");   
                 
            rs=ps.executeQuery();   
            ResultSetMetaData rsmd = rs.getMetaData();   
           
            while(rs.next()){   
                   
                for(int i=1;i<=rsmd.getColumnCount();i++){   // Just Made this change . 
                    slectedColums+=rs.getString(i);    
                }   
                 resultList.add(slectedColums);   
                 j++;   
            }      
            System.out.println("The total no of records is "+j);   
            System.out.println("The Retrieved ResultSet is "+resultList);   
            ps.close();     
        } catch (SQLException e) {   
               
            e.printStackTrace();   
        }

Check the above code. It would be fine now.

Now its working but printing [ brackets. Why? can It separate the result with "," ?

best regards

Smarty Ravi's code prints the result of the toString() method of ArrayList. If you want different formatting you will need write code to handle that yourself.

Thanks Ravi and Paul. I learned a lot

Thanks again

Hi again,

I am unable to pass the data as separate parameters to the method as the method is receiving this data as one parameter. what should I do in this case?

Best regards

I am unable to pass the data as separate parameters to the method as the method is receiving this data as one parameter. what should I do in this case?

Can you give me the signature of your method or your method code. So that we could analyze it and support you.

Farakh khan wrote: //My TestMethods class public String test(ArrayList result)throws Exception{ String data= "<tr "+cm.rowColor()+">"+ "<td class=normalFont>"+result.get(0)+"</td>"+ "<td class=normalFont>"+result.get(1)+"</td>"+ "</tr>"; return data; }

Check the above code .

Ensure that dao.select() method returns only ArrayList (ie., resultList)

I want to get the resultset in TestManager and then want to pass it to TestMethods public String test(ArrayList result)throws Exception{

I called this method directly from dao to testMethods but throwing above error

Best regards

Looks like you have not imported the ArrayList class.

As a general rule it makes sense to code method names to use the interface List, rather than ArrayList. This leaves you the room to change the implementation.

Yes, I just imported the ArrayList class

pm.test(resultList.toArray());

Why are you outputting this as an array when your method takes an ArrayList?

Thanks Paul for pointing .toArray();

Now compiled successfully but throwing exception:

So you are accessing an element of an array which doesn't exist. So you've possible got an array or List with n entries and you are trying to access the n + 1 entry.

I am checking again & again the above code but unable to understand where it indexing more then its length

I change to this code and its working fine

Thanks Paul and Ravi.

Hello again,

This returns one record obviously as we are getting 0th and 1th element but how I'll display more than one record as I am not able to set the length of the records in loop and if I am giving length by myself then its not working at all

best regards

I also tried like the following but no success:

The message appears:
[code]
java.lang.IndexOutOfBoundsException: Index: 0, Size: 0

Best regards>

Hi,

Are you there Ravi and Paul?

Best regards

Have you tried adding debugging to see what getColumnCount() is? Maybe the code to add elements to the result isn't being run.

Farakh khan wrote:I also tried like the following but no success:
for(int i=0;i<result.size();i++){ String patientId =(String) result.get(i); String rDate =(String) result.get(i);

The message appears:

java.lang.IndexOutOfBoundsException: Index: 0, Size: 0

Best regards>

As you are having more than one record you should add each record seperately into a arraylist. At last you should add all arraylists to one arraylists .

try this code :

int j=0;      
    ArrayList<ArrayList> resultList = new ArrayList<ArrayList>();      
    ArrayList<String> resultRecord = null//
    ResultSet rs=null;      
    String query = "select a.aa,cc from test a,test1 b where a.aa=b.aa and a.aa=?";         
        try {       
            PreparedStatement ps=cnn.prepareStatement(query);         
            ps.setString(1, "1");      
                    
            rs=ps.executeQuery();      
            ResultSetMetaData rsmd = rs.getMetaData();      
              
            while(rs.next()){      
                     resultRecord= new ArrayList<String>();// here we create a new arraylist for each and every record. 
                for(int i=1;i<=rsmd.getColumnCount();i++){   // Just Made this change .    
                 resultRecord .add(rs.getString(i)); 
                }      
              resultList.add(resultRecord)   ;//here we are adding each record list to another ArrayList
                 j++;      
            }         
            System.out.println("The total no of records is "+j);      
            System.out.println("The Retrieved ResultSet is "+resultList);      
            ps.close();        
        } catch (SQLException e) {      
                  
            e.printStackTrace();      
        }

Also change the below code as follows

Thanks Ravi for helping me again but its throwing the following errors: