Java SQL reading database table one row at a time

Question

I want to write a class to be able to read data from SQL database. In the class, there should be a method that can read a given database table one row at a time. The data will pass into Java object and user can do something with it. Then user can read the next row and so on.

My codes:

public class DatabaseReader {

    String table = "";

    protected boolean available;

    public boolean isAvailable()
    {
        return available;
    }

    public void setTable(String table)
    {
        this.table = table;
    }

    public String[] getData()
    {
        String sql = "SELECT * FROM "+table+";";

        Connection  con  = getConnection();
        Statement   stmt = con.createStatement();
        ResultSet   rs   = stmt.executeQuery(sql);

        ResultSetMetaData rm = rs.getMetaData();

        int count = rm.getColumnCount();

        /**
         * so I don't know what to do in here
         * is there a better way to keep track of what rows already been read?
        */
        if (rs.next()) {
            for (int i = 1; i <= count; i++) {
                System.out.print(rs.getString(i)+" , ");
            }
            System.out.println();
            available = true;
        } else {
            available = false;
        }
    }

}

I assume user can do the following to get data one at a time:

DatabaseReader reader = new DatabaseReader();
reader.setTable("Product"); // assume Product table contains ID, name, price, etc.
while (reader.isAvailable) {
    String[] data = getData();
    Product product = new Product(data);
    // add to a list of products or they can do something else
}
// maybe calculate total price of all products or whatever

The only way I can think of is keep a local variable for ResultSet and use rs.next() until it is done. But I am not sure if that is a good way to do. Is there any better way to do what I want without using ResultSet? I just want to read data from table one at a time. Thanks.

You can possibly use a limit clause. For example in mysql LIMIT http://dev.mysql.com/doc/refman/5.7/en/select.html you need pass in the parameters to track your current row and offset which is 1 in this case. That would limit your resultset to just one. Not sure what your options are for the DB you are using, which is? — jasonlam604, Aug 10 '16 at 20:22
@jasonlam604 thanks, but that is no good because I need to support more than one relational database for customers. That looks like easy to do because it takes an offset and use that for tracking. Database I need to support are many, such like SQLServer, MySQL, Oracle, PostgreSQL, and few others. — Cao Felix, Aug 10 '16 at 20:27
Which database? I think unfortunately there isn't a common SQL statement that can be applied to all. You will need to find the individual clauses for each db and then through code dynamically apply the appropriate one. Another example using Oracle http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering — jasonlam604, Aug 10 '16 at 20:33
ah, that is no good... I wonder if SELECT * FROM tablename is common for every different database. — Cao Felix, Aug 10 '16 at 20:37
More than likely all the RDMS databases you are working with should be following the ANSI/ISO SQL standards. Plus such a simple statement shouldn't be an issue. — jasonlam604, Aug 10 '16 at 20:44
Because I also need to handle BigData database such as Mongo, Apache Cassandra, Cloudera Impala, Amazon Redshift and few others. Do you know about those too? I try to install their database, but most cost money and I don't know how they want me to test later... — Cao Felix, Aug 10 '16 at 20:57
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/120641/discussion-between-jasonlam604-and-cao-felix). — jasonlam604, Aug 10 '16 at 21:43

score 0 · Accepted Answer · answered Aug 11 '16 at 02:17

0

You can hold the ResultSet object in you class, and every time the method getData() is called, you call resultSet.next() to get another row in this table. However, this is not a good idea. because the database connection will not be released, and the database connection is rare source of the whole system. What's more you will not be able to read updates by other thread due to the database isolation.

answered Aug 11 '16 at 02:17

Jade Tang

321
4
23

Right, that is what I was thinking. I am using DataSource to get the connection. I learned the close() will not actually close the connection but it will return it to the pool. So if holding a local variable is the only way for this case, should not be too much concern about connection releasing then? – Cao Felix Aug 11 '16 at 16:05
1

If you never return the connection, the other thread may never have the chance to use it. – Jade Tang Aug 12 '16 at 00:37
I accept this as my answer since the seniors in my team say this is a fine solution. In the application that I am building, we create workers that continue to read data from database. So it is alright to keep the connection open and result set object alive until the job is done. thx – Cao Felix Aug 18 '16 at 21:19

Java SQL reading database table one row at a time

1 Answers1