Arrayilst for join query using existing bean classes

[sugumarclick]

hi friends,
I am a novice java programmer.
I am in a learning process in java servlets.

But i struck into a Class method which should return a result set(result of a join query)as a an arrayList using the existing bean class.

I will show my code as some one can point out what i should do or suggest me a (standard) method to access it.

I am having a separate bean class for table users and table books.
A book table which has list of books info,
A user table which has list of users info,
when a user is going to apply book, a ticket id is genrated which users can track the status of the applied book .

public ArrayList<//what to give here??> getAppliedBooks(UserList user){

   Connection connection = getDbConnection();
    Statement statement = null;
    ResultSet resultSet = null;
    ArrayList<//what to give here??> bookList = new ArrayList<//what to give here??>();

    try {

        statement = connection.createStatement();
        String sql="SELECT be.ticket_id,be.applied_date,bk.title,bk.author,be.status FROM book_entry be,books bk,USER us "
                +" id=be.user_id AND bk.id=be.book_id AND username ='"+user.getUserName()+"'";

        resultSet = statement.executeQuery(sql);

        BookList book;
        UserList appliedUser;
        while (resultSet.next()) {
            book= new BookList();
            appliedUser = new UserList();
            book.setId(resultSet.getLong("book_id"));
            book.setId(resultSet.getLong("be.ticket_id"));
            book.setAuthor(resultSet.getString("author").toString());
            book.setTitle(resultSet.getString("title").toString());
            book.setAvailable(resultSet.getBoolean("available"));
            appliedUser.setId(resultSet.getLong("user_id"));
            //bookList.add(book);
            //bookList.add(appliedUser);
            //This is the place where i struck and felt hard to add an item into arraylist
        }
        
    }
    catch(Exception e){

        e.printStackTrace();
    }

    finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return bookList;
    }

Note: i dont want to create a new Bean class for this join which an alter in the db table makes me to alter in table bean class and joins bean class. Please suggest the right way to proceed further

[javaAddict]

You have to "connect" the objects: BookList and UserList. Either add an attirubte to one of those to be the other. Add to the BookList an UserList attribute.

book.setAvailable(resultSet.getBoolean("available"));
appliedUser.setId(resultSet.getLong("user_id"));

book.setAppliedUser(appliedUser);

bookList.add(book);

Or create a new class that has those 2 as attribute and return that class.

In the first case the code would look like that:

public ArrayList<BookList> getAppliedBooks(UserList user){
...

    ArrayList<BookList> bookList = new ArrayList<BookList>();

    try {

...

        BookList book;
        UserList appliedUser;
        while (resultSet.next()) {
            book= new BookList();
            appliedUser = new UserList();

...

            book.setAvailable(resultSet.getBoolean("available"));
            appliedUser.setId(resultSet.getLong("user_id"));

            book.setAppliedUser(appliedUser);

            bookList.add(book);
        }
        
    }
    catch(Exception e){

        e.printStackTrace();
    }

    finally {
        if (resultSet != null) try { resultSet.close(); } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close(); } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close(); } catch (SQLException logOrIgnore) {}
    }

    return bookList;
    }

[sugumarclick]

yes. i got it now. I created a new bean class where i have method to set the two class objects into it. thats working great.