Passing array of objects......ResultSet

[Anchit Kalra]

hi guys,

well I am writing a program in which I have Two Classes(atleast for this Q) The Main class has the Gui and DB_conn class has the database connectios and queries.

The problem ocours when i need to return the array of tpe Objects from DB_conn to Main. the function "tmodel.addRow(data1);" takes an array of objects But if I dirrcly pass this Only the last element is shown........... here is the code.....

//This a Function in MAIN
public void get_init()
    {
        DB_conn DB = new DB_conn();
        int rsrows= DB.getrsRow("SELECT * FROM QUARTER");
        System.out.println(rsrows);
        Object[] data =DB.getrs();

        Object[] data1 = new Object[rsrows] ;

        for(int j=1;j<rsrows;j++)
        {
            for(int i=1;i<=(DB.getcol());i++)
            {
                data1 = (Object[]) data[i-1];
                System.out.println("Main "+data[i-1]);
            }
        tmodel.addRow(data1);
        }
    }

//This Function in class DB_conn

public Object[] getrs()
    {
        try
        {
            ResultSet rs = stmt.executeQuery("SELECT * FROM QUARTER");

            ResultSetMetaData md = rs.getMetaData();
			int colcount = md.getColumnCount();

			Object[] data = new Object[colcount];
            //Object[] data1 = new Object[];

            //Block Takes number of Rows.

                rs.last();
                int count = rs.getRow();
                rs.beforeFirst();
                
            
            Object[] data1 = new Object[count];
            int count1=0;
            while (rs.next())
			{
				for (int i=1; i<=colcount; i++)
				{
					data[i-1] = rs.getString(i);
                                                                }
                System.out.println(count1);
                data1[count1++]= (Object[])data;
                
}
            return data1;
    

    

Please help

[Ernest Friedman-Hill]

Your loop over "i" retrieves many different arrays and assigns them to the variable "data1", but it isn't until after the loop over "i" runs that you call addRow(). That means you only call addRow() on the last value that the array takes during the loop over "i".

But I'm pretty confused, frankly, as to what the various variables here represent. You may need to make more changes.

[Anchit Kalra]

Let me rewrite the code and then post it again

[Anchit Kalra]

//MAin Class
 
public void get_init()
    {
        
        DB_conn DB = new DB_conn();
        int rsrows= DB.getrsRow("SELECT * FROM QUARTER");
        System.out.println(rsrows);
        Object[] data =DB.getrs();

        tmodel.addRow(data);
        
    }
//DB_conn

public Object[] getrs()
    {
        try
        {
            ResultSet rs = stmt.executeQuery("SELECT * FROM QUARTER");

            ResultSetMetaData md = rs.getMetaData();
			int colcount = md.getColumnCount();

			Object[] data = new Object[colcount];
            

            //Block Takes number of Rows in the result set

                rs.last();
                int count = rs.getRow();
                rs.beforeFirst();
                
            
            //int count1=0;


            while (rs.next())
			{
				for (int i=1; i<=colcount; i++)
				{
					data[i-1] = rs.getString(i);
                    //System.out.print(data[i-1]);
                }
                
                
			}
            return data;
            }
            catch(......){.......}
    
}

   

This code shows only the last row so how do i pass all the rows tom my Main Class
p.s sorry for using so many variables!!

[Steve Luke]

            while (rs.next())
            {
                for (int i=1; i<=colcount; i++)
                {
                    data[i-1] = rs.getString(i);
                    //System.out.print(data[i-1]);
                }
            }
            return data;

You loop over all the rows, put keep overwriting the same positions inside the data array, so yeah, only the last row will be available. You really need to redesign. Here are some options:
1) Use a 2D Object array:

    Object[][] data = new Object[rowCount][colCount];

Then when iterating over the result:

            //keep track of the row we are working on
            int currentRow = 0;
           //iterate over all rows
            while (rs.next())
            {
                //iterated over all the columns
                for (int i=1; i<=colcount; i++)
                {
                    //Put the data in the proper column for the current row
                    data[currentRow][i-1] = rs.getString(i);
                    //System.out.print(data[i-1]);
                }
                //increment row
                currentRow++;
            }
            return data;

2) Make the data array long enough to hold all the rows and columns, so instead of Object[] data = new Object[colcount] it would be Object[] data = new Object[rowCount * colCount]; Then you have to keep track of what your offsets are in terms of both row and column:

            int currentRow = 0;
            while (rs.next())
            {
                for (int i=1; i<=colcount; i++)
                {
                    int currentDataOffset = (currentRow * colcount) + (i-1);
                    data[currentDataOffset] = rs.getString(i);
                    //System.out.print(data[i-1]);
                }
                currentRow++;
            }
            return data;

3) Make a DataTransferObject which has a meaningful representation of the Row and fill in the correct values from the result set. Then pass back an array of the data transfer object. This would make handling the data a lot more maintenance friendly and understandable. As an example, if your database row represents the information about a Client, and the columns where:
[First Name, Last Name, Company, Position]
then your DTO might look like:

public class Client
{
    private String firstName, lastName, company, position;

    public void setFirstName(String fn) { this.firstName = fn; }
    public void setLastName(String ln) { this.lastName = ln; }
    public void setCompany(String c) { this.company = c; }
    public void setPosition(String p) { this.position = p; }

    public String getFirstName() { return this.firstName; }
    public String getLastName() { return this.lastName; }
    public String getCompany() { return this.company; }
    public String getPosition() { return this.position; }
}

And when you gather data from the database it could look like:

            List<Client> data = new ArrayList<Client>();
            while (rs.next())
            {
                Client c = new Client();
                c.setFirstName(rs.getString(1);
                c.setLastName(rs.getString(2);
                c.setCompany(rs.getString(3);
                c.setPosition(rs.getString(4);
                data.add(c);
            }
            return data.toArray();

Of course it would be a lot easier to pass around a generic list of type Client, or a Client[] instead of Object[] at this point...

Of the three options, 2) is the worst, and 3) is the best, in my opinion.

[Anchit Kalra]

Thanks a lot guys!!!

well i figured it out but have used the second way which is kind of !@#$@!#$
I will try doing it with making the class and then put the input in the variables....tht looks intresting...

[denis sorn]

You also have to close ResultSet, Statement and Connection in that order or you'll encounter problems sooner or later.