Implement Iterator design pattern using JDBC

Question

I'm tackling the following question:

The iterator design pattern is one that has strong encapsulation. As an example; a library wants a book management system. A class for the books storing their details and a class for the library storing the books and the shelf number. Lets say the library wants the data stored in a database using JDBC.

How can the Iterator design pattern be implemented using JDBC ensuring encapsulation of the data?

My concern is where the database is being handled and how the data is being shared across the application.

Can the database handler be an inner-class of the library class? Is it then possible to save the data and retrieve it on request without affecting the encapsulation?

I am still learning and have a long way to go so be gentle :)

iterators are for traversing through a collection. Where did this question come from? — Tony Hopkinson, May 16 '13 at 21:08
@TonyHopkinson - Actually Iterators are for traversing through **anything** and database query results fit **very** well with iterators. — OldCurmudgeon, May 16 '13 at 21:16
@Waddas - You are on the right track with an inner class implementing `Iterator` and making the outer class implement `Iterable`. What do you think `Something` would be in this context. — OldCurmudgeon, May 16 '13 at 21:18
@OldCurmudgeon I'm thinking (to follow the example) `Map` with the Integer being a UID. Quick thought, is it not significantly inefficient having it as an inner class? As each time a library's data is requested the current database needs to be closed and a new one opened. — Waddas, May 16 '13 at 21:51
@OldCurmudgeon Ah i see, thank you. I really do have a long way to go. Do you happen to know if this JDBC implements the connection pool API? https://bitbucket.org/xerial/sqlite-jdbc/overview — Waddas, May 16 '13 at 22:54
@Waddas - No - that is just the driver that implements the connection - have a look at the discussion [here](http://stackoverflow.com/q/8057110/823393). — OldCurmudgeon, May 17 '13 at 08:14
@OldCurmudgeon. Agreed, but putting database save in an iterator gives me the shudders. — Tony Hopkinson, May 17 '13 at 13:19
@TonyHopkinson - I was thinking of database **read** and wrapping the `ResultSet` in an iterator, which works quite naturally. I hope I haven't misunderstood the question. Writing **to** a `Statement` **from** an `Iterator` would be fine but I agree, putting one inside the other would be a mistake. — OldCurmudgeon, May 17 '13 at 13:23
@OldCurmudgeon, me too. Nipping through a result set, and then posting to the db is one thing. Connected dataset, is a different animal. — Tony Hopkinson, May 17 '13 at 20:16
@TonyHopkinson From what you're saying, having the database handler class (which handles the connection and methods for posting and retrieving data) as an inner-class of the iterator class is a bad idea? I would be treating them as separate classes but I'm trying to ensure tight encapsulation. — Waddas, May 20 '13 at 16:42
Link the iterator to a class to manage the db stuff, through an interface by injecting it into the iterator. That way you can skip the db bit or swap out JDBC for something else. Not to mention unit test it. — Tony Hopkinson, May 20 '13 at 20:34
@TonyHopkinson So have the iterator implement the database management class? Sorry if I'm not understanding. — Waddas, May 21 '13 at 11:46
Not directly. Question. Does iterator.Next go back to the db to get the 'next' record? — Tony Hopkinson, May 21 '13 at 14:48
@TonyHopkinson At the moment my idea is having the iterator retrieve a set from the database based on a query (for example: all books beginning with 'A' or all transactions in a specific account), storing that locally and using that data to iterate through. — Waddas, May 21 '13 at 15:05
Oh! Can't think of a single reason to put any db code in the iterator then. You donlt need the db to iterate and getting a collection from the db does not require iteration. Any particular reason why you are reinventing IEnumerable? — Tony Hopkinson, May 21 '13 at 22:05
@TonyHopkinson With that idea no, i would like to have the db code inside the iterator so that depending on a parameter (such as prev example) the iterator will go through all those values. By implementing the iterator pattern im hoping to ensure encapsulation and allow for code re-usability. However i dont know how to go about this. — Waddas, May 22 '13 at 01:32
As soon as you take that local copy of the db data, the collection doesn't need to know anything about the db, which is a good design (and the existing one in .net). so you have some class to load and save a collection, and your collection implements iteration, that's both encapsulated and reusable, either singly or together. Put one inside the other, and it will be less reusable. What about a third class that aggregates the db stuff, and iteration. — Tony Hopkinson, May 22 '13 at 09:54

score 1 · Answer 1 · answered Jul 10 '13 at 18:03

This is an aproximation about the use of Iterator pattern accesing to database.

package tk.ezequielantunez.stackoverflow.examples;

import java.util.Collection;
import java.util.Iterator;

/**
 * Paged implementatios of a DAO. Iterator interface is used.
 * Nottice you get pages of Collections, where resides your data.
 * @author Ezequiel
 */
public class DAOIterator implements Iterator<Collection> {

    int actualPage;
    int pageSize;

    public DAOIterator(int pageSize) {
        this.actualPage = 0;
        this.pageSize = pageSize;
    }

    /**
     * Indicates if you have more pages of datga.
     */
    @Override
    public boolean hasNext() {
        return actualPage < getTotalPages();
    }

    /**
     * Gets the next page of data.
     */
    @Override
    public Collection next() {
        return getPage(++actualPage);
    }

    @Override
    public void remove() {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    /**
     * Calculates total number of pages.
     */
    private int getTotalPages() {
        /* You could do a count of total results and divide them by pageSize */
        throw new UnsupportedOperationException("Not supported yet.");
    }

    /**
     * Get a page of results with X objects, where X is the pageSize used in
     * constructor.
     */
    private Collection getPage(int page) {
        /* Get data from database here */
        throw new UnsupportedOperationException("Not supported yet.");
    }
}

Implement Iterator design pattern using JDBC

1 Answers1

Linked