PreparedStatement (displaying records)

[Farakh khan]

Hello,

int j=1,i=0;
String slectedColums="";

String query = "select a.aa,cc from test a,test1 b where a.aa=b.aa and a.aa=?";

PreparedStatement ps=cnn.prepareStatement(query);
ps.setString(1, "1");

rs=ps.executeQuery();
while(rs.next()){
	slectedColums+=rs.getString(j);
	j++;
}		
ps.close();

System.out.println("selected columns="+slectedColums+" and i="+i);

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

whereas the OUTPUT should be "1,test"

Please help me

Best regards

[Paul Sturrock]

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.

while(rs.next()){  
    slectedColums+=rs.getString(j);  
    j++;  
}         

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?

[Farakh khan]

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

[Paul Sturrock]

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.

[Farakh khan]

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

[Farakh khan]

while(rs.next()){ 
	aa[i] = rs.getString(j);
	slectedColums+= aa[i]+",";
 j++;
 i++;

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

[Paul Sturrock]

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:

slectedColums += rs.getString(1) + rs.getString(2);

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?

[Farakh khan]

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

[Paul Sturrock]

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?

[Farakh khan]

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

Yes, this is the case.

[Paul Sturrock]

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.

[Farakh khan]

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

    while(rs.next()){   
        aa[i] = rs.getString(j);  
        slectedColums+= aa[i]+",";  
     j++;  
     i++;  
     }

Best regards

[Farakh khan]

Please read my previous message again as I edited

[Paul Sturrock]

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:

while(rs.next()){     
        aa[i] = rs.getString(j);    
        slectedColums+= aa[i]+",";    
     j++;    
     i++;    
     }

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.

[Farakh khan]

PreparedStatement ps=cnn.prepareStatement(query);  
ps.setString(1, "1");  
  
rs=ps.executeQuery();

    
if(rs.next()){
	for(i=0;i<totalColums; i++){
            	aa[i] = rs.getString(j);  				
		slectedColums+= aa[i]; 
		}
         j++; 
	}     
}  
ps.close();  	

Now its working fine

[Paul Sturrock]

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.

[Ravi Majety]

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

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=0;i<rsmd.getColumnCount();i++){
					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();
		} 

All the best >

[Farakh khan]

Hi Ravi,

Its throwing exception:

DAOMethods.select()java.sql.SQLException: Invalid column index
java.sql.SQLException: Invalid column index
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:231)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:294)
        at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatemen
t.java:3163)
        at oracle.jdbc.driver.OracleStatement.getStringValue(OracleStatement.jav
a:3367)
        at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.
java:337)
        at DAOMethods.select(DAOMethods.java:195)
        at Query.main(Query.java:23)
DAOMethods.select()java.sql.SQLException: Invalid column index

and if I am doing j=1 then

The total no of records is 2
The Retrieved ResultSet is [11]
11

Its printing just first column twice not second column


Best regards

[Ravi Majety]

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.

[Farakh khan]

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

best regards

[Paul Sturrock]

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.

[Farakh khan]

Thanks Ravi and Paul. I learned a lot

Thanks again

[Farakh khan]

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

[Ravi Majety]

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]

//My TestManager class
	
	else if(option.equals("4")){

	TestMethods tm=new TestMethods();
        TestDAO dao=new TestDAO();

	tm.test(dao.select()); 			
	}



//My TestMethods class
	public String test(String aId,String name)throws Exception{
				
	String data=		
		"<tr "+cm.rowColor()+">"+
		"<td     class=normalFont>"+aId+"</td>"+
		"<td  class=normalFont>"+name+"</td>"+
		"</tr>";

	return data;
	}

[Ravi Majety]

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)

[Farakh khan]

TestMethods.java:163: cannot find symbol
symbol  : class ArrayList
location: class TestMethods
        public String test(ArrayList result)throws Exception{
                                          ^
1 error

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

[Paul Sturrock]

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.

[Farakh khan]

Yes, I just imported the ArrayList class

TestDAO.java:219: test(java.util.ArrayList) in testMethods cannot be applied to (java.lang.Object[])
pm.test(resultList.toArray());

[Paul Sturrock]

pm.test(resultList.toArray());

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

[Farakh khan]

Thanks Paul for pointing .toArray();

Now compiled successfully but throwing exception:

java.lang.IndexOutOfBoundsException: Index: 4, Size: 1 

[Paul Sturrock]

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.

[Farakh khan]

rs=ps.executeQuery();     
                ResultSetMetaData rsmd = rs.getMetaData();     
                while(rs.next()){     
                         
                    for(int k=1;k<=rsmd.getColumnCount();k++){			
			slectedColums+=rs.getString(k); 			
                    }     
                     resultList.add(slectedColums);
		     a+=1; 
                }     
		 ps.close();      
 
            } catch (Exception e) {     
                     
                e.printStackTrace();     
            }     

	TestMethods pm=new TesttMethods();
	System.out.println(pm.test(resultList));

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

[Farakh khan]

rs=ps.executeQuery();     
                ResultSetMetaData rsmd = rs.getMetaData();     

                while(rs.next()){     
                      
			      
                    for(int k=1;k<=rsmd.getColumnCount();k++){			
			resultList.add(rs.getString(k));
                    }  
                }     
		ps.close();      
 
            } catch (Exception e) {     
                     
                e.printStackTrace();     
            }     

I change to this code and its working fine

Thanks Paul and Ravi.

[Farakh khan]

Hello again,

	public String viewPatients(ArrayList result)throws Exception{				

	String rId 	=(String) result.get(0);
	String rName 	=(String) result.get(1);

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

[Farakh khan]

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:
[code]
java.lang.IndexOutOfBoundsException: Index: 0, Size: 0

Best regards>

[Farakh khan]

Hi,

Are you there Ravi and Paul?

Best regards

[Jeanne Boyarsky]

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

[Ravi Majety]

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

//My TestMethods class   
    public String test(ArrayList result)throws Exception{   
    String data="";               
	for(ArrayList record : result){// we are fetching the each record which is an ArrayList and fetching the values
     data+=           
        "<tr "+cm.rowColor()+">"+   
        "<td     class=normalFont>"+record.get(0)+"</td>"+   
        "<td  class=normalFont>"+record.get(1)+"</td>"+   
        "</tr>";   
		}
  
    return data;   
    } 
	

[Farakh khan]

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

PatientMethods.java:188: incompatible types
found   : java.lang.Object
required: java.util.ArrayList
        for(ArrayList record : result){
                               ^
DAOMethods.java:221: cannot find symbol
symbol  : method add(java.util.ArrayList<java.lang.String>)
location: class java.util.ArrayList<java.lang.String>
                        resultList.add(resultRecord);
                                  ^
2 errors