Retrieve entire row with ResultSet

Question

Is it possible to retrieve entire row without calling getInt(..) getString(..) for every column?

I have multiple threads, each tread needs to write the result to some thread safe collection.
I want to be able to write the rows directly to this collection, and after that parse the members of this collection and retrieve the values based on column types.

No. You need to parse the row into `Objects` and store those in the `Collection`. Consider _querying_ the data in chunks in threads. — Boris the Spider, Jun 02 '13 at 12:58
If you don't want to write such code, have a look at e.g. Spring JDBC template. It reliefs you of a lot of such boiler plate code without the burden of an ORM. — , Jun 02 '13 at 13:01
You want an ORM. http://stackoverflow.com/questions/tagged/java+orm?sort=frequent&pagesize=50 or google for java orm — Jeff Miller, Jun 02 '13 at 16:18

score 12 · Answer 1 · edited May 20 '21 at 03:17

You can build a class like this one, which maps sql data types with java data types:

class Row
{
    public Map <Object,Class> row;
    public static Map <String, Class> TYPE;

    static
    {
        TYPE = new HashMap<String, Class>();

        TYPE.put("INTEGER", Integer.class);
        TYPE.put("TINYINT", Byte.class);
        TYPE.put("SMALLINT", Short.class);
        TYPE.put("BIGINT", Long.class);
        TYPE.put("REAL", Float.class);
        TYPE.put("FLOAT", Double.class);
        TYPE.put("DOUBLE", Double.class);
        TYPE.put("DECIMAL", BigDecimal.class);
        TYPE.put("NUMERIC", BigDecimal.class);
        TYPE.put("BOOLEAN", Boolean.class);
        TYPE.put("CHAR", String.class);
        TYPE.put("VARCHAR", String.class);
        TYPE.put("LONGVARCHAR", String.class);
        TYPE.put("DATE", Date.class);
        TYPE.put("TIME", Time.class);
        TYPE.put("TIMESTAMP", Timestamp.class);
        // ...
    }
    
    public Row ()
    {
        row = new HashMap<Object,Class>();
    }
    
    public <T> void add (T data)
    {
        row.put(data, data.getClass());
    }

    public void add (Object data, String sqlType)
    {
        add((Row.TYPE.get(sqlType)) data);
    }

    public static void formTable (ResultSet rs, ArrayList<Row> table)
    {
        if (rs == null) return;
    
        ResultSetMetaData rsmd = rs.getMetaData();
    
        int NumOfCol = rsmd.getColumnCount();
    
        while (rs.next())
        {
            row = new Row ();
        
            for(int i = 1; i <= NumOfCol; i++)
            {
                row.add(rs.getObject(i), rsmd.getColumnTypeName(i));
            }

            table.add(row);
        }
    }
}

Which you can use it like this:

List<Row> table = new ArrayList<Row>();

Row row = null;

ResultSet rs = st.executeQuery("SELECT * FROM table_name");

Row.formTable(rs, table);

Then you can retrieve fields and cast them to their respective data types:

for (Row row : table)
{

    for (Object data : row.row.getKeySet())
    {
        System.out.print(" > " + ((row.row.get(data) data));
    }
    
    System.out.println();

}

Thanks, good piece of code. One comment: I think that a single row should not be represented as a Map, but as a List because the columns are ordered in a table. Thus, I'd change the Row's row to something like that: public List> row; There is not enough space here so I pasted my code in a new entry below. — ady, Jul 14 '15 at 17:13

score 6 · Answer 2 · answered Jul 14 '15 at 17:18

Row represented as a list:

import java.math.BigDecimal;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.AbstractMap;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * @author Adam Dziedzic
 * 
 */
public class Row {
    public List<Entry<Object, Class>> row;
    public static Map<String, Class> TYPE;

    static {
        TYPE = new HashMap<String, Class>();

        TYPE.put("INTEGER", Integer.class);
        TYPE.put("TINYINT", Byte.class);
        TYPE.put("SMALLINT", Short.class);
        TYPE.put("BIGINT", Long.class);
        TYPE.put("REAL", Float.class);
        TYPE.put("FLOAT", Double.class);
        TYPE.put("DOUBLE", Double.class);
        TYPE.put("DECIMAL", BigDecimal.class);
        TYPE.put("NUMERIC", BigDecimal.class);
        TYPE.put("BOOLEAN", Boolean.class);
        TYPE.put("CHAR", String.class);
        TYPE.put("VARCHAR", String.class);
        TYPE.put("LONGVARCHAR", String.class);
        TYPE.put("DATE", Date.class);
        TYPE.put("TIME", Time.class);
        TYPE.put("TIMESTAMP", Timestamp.class);
        TYPE.put("SERIAL",Integer.class);
        // ...
    }

    public Row() {
        row = new ArrayList<Entry<Object, Class>>();
    }

    public <T> void add(T data) {
        row.add(new AbstractMap.SimpleImmutableEntry<Object,Class>(data, data.getClass()));
    }

    public void add(Object data, String sqlType) {
        Class castType = Row.TYPE.get(sqlType.toUpperCase());
        try {
            this.add(castType.cast(data));
        } catch (NullPointerException e) {
            e.printStackTrace();
            Logger lgr = Logger.getLogger(Row.class.getName());
            lgr.log(Level.SEVERE, e.getMessage()+" Add the type "+sqlType+" to the TYPE hash map in the Row class.", e);
            throw e;
        }
    }

    public static void formTable(ResultSet rs, List<Row> table)
            throws SQLException {
        if (rs == null)
            return;

        ResultSetMetaData rsmd;
        try {
            rsmd = rs.getMetaData();

            int NumOfCol = rsmd.getColumnCount();

            while (rs.next()) {
                Row current_row = new Row();

                for (int i = 1; i <= NumOfCol; i++) {
                    current_row.add(rs.getObject(i), rsmd.getColumnTypeName(i));
                }

                table.add(current_row);
            }
        } catch (SQLException e) {
            throw e;
        }
    }
}

Usage:

List<Row> table = new ArrayList<Row>();

ResultSet rs = st.executeQuery("SELECT * FROM table_name");

Row.formTable(rs, table);

for (Row row : table)
{
    for (Entry<Object, Class> col: row.row)
    {
        System.out.print(" > " + ((col.getValue()).cast(col.getKey())));
    }
    System.out.println();
}

*TYPE.put("INTEGER", Integer.class);* could be changed to *TYPE.put("INT", Integer.class);* — Ashok kumar Ganesan, Mar 29 '18 at 11:04
*TYPE.put("INTEGER", Integer.class);* could be changed to *TYPE.put("INT", Integer.class);* — Ashok kumar Ganesan, Mar 29 '18 at 11:05
the OP specifically asked for a solution that does NOT iterate through the columns — john k, Oct 02 '20 at 16:40
It would be better to catch the SQL exception and to log it instead of just catching the exception and throwing it back to the caller. — Michelle, May 20 '21 at 03:16

Boris the Spider · Answer 3 · 2013-06-02T13:38:40.693

Here is an example using a query to work out the size of the query and then splitting up the processing to multiple threads.

I use MySQL, so the queries are written for that. You'll need to change the queries for you database engine.

public static void main(String[] args) throws Exception {
    final int count;
    try (final Connection conn = DATA_SOURCE.getConnection()) {
        final String countQuery = "SELECT COUNT(*) FROM my_table";
        try (final PreparedStatement ps = conn.prepareStatement(countQuery);
                final ResultSet resultSet = ps.executeQuery()) {
            resultSet.next();
            count = resultSet.getInt(1);
        }
    }
    final int chunksize = 1000;
    final Queue<SqlResult> results = new ConcurrentLinkedQueue<>();
    final ExecutorService es = Executors.newFixedThreadPool(10);
    for (int end = 0; end < count; end += chunksize) {
        es.execute(new ResultReader(count, end, DATA_SOURCE, results));
    }
}

private static class ResultReader implements Runnable {

    private final int start;
    private final int size;
    private final DataSource dataSource;
    private final Queue<SqlResult> results;

    public ResultReader(int start, int size, DataSource dataSource, Queue<SqlResult> results) {
        this.start = start;
        this.size = size;
        this.dataSource = dataSource;
        this.results = results;
    }

    @Override
    public void run() {
        try (final Connection connection = dataSource.getConnection()) {
            final String query = "SELECT id, something, somethingElse FROM my_table LIMIT ?, ?";
            try (final PreparedStatement ps = connection.prepareStatement(query)) {
                ps.setInt(1, start);
                ps.setInt(2, size);
                try (final ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        final SqlResult sqlResult = new SqlResult();
                        sqlResult.setId(rs.getInt("id"));
                        sqlResult.setSomething(rs.getString("something"));
                        sqlResult.setSomethingElse(rs.getString("somethingElse"));
                        results.add(sqlResult);
                    }
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(App.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

private static class SqlResult {

    private int id;
    private String something;
    private String somethingElse;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getSomething() {
        return something;
    }

    public void setSomething(String something) {
        this.something = something;
    }

    public String getSomethingElse() {
        return somethingElse;
    }

    public void setSomethingElse(String somethingElse) {
        this.somethingElse = somethingElse;
    }
}

This assumes that you already have connection pooling, using a DataSource.

Each of the workers, runs a query using LIMIT and processes the results into SqlResult objects and adds them to a concurrent Queue.

Is not `LIMIT ?, ?` a MySQL reserved keywork? AFAIK it won't work on Oracle nor SQL Server. — Luiggi Mendoza, Jun 02 '13 at 13:35
@LuiggiMendoza fair point. I don't know of a generic way to do this. I will amend my answer to mention that it is MySQL specific. — Boris the Spider, Jun 02 '13 at 13:37
thanks, but my "issue" is with resultSet.getInt(1) I wanted to avoid that. — yuris, Jun 02 '13 at 13:41
p.s. I am not using a connection pool (don't think I need it) I am creating 4 connection for 4 theeads. — yuris, Jun 02 '13 at 13:42

score 0 · Answer 4 · answered Dec 04 '15 at 17:58

0

I used adam.cajf answer, very useful, however, I needed to add one line of code to add function, otherwise it was throwing an error in my particular data set.

if (data != null) {

answered Dec 04 '15 at 17:58

Dmitry Buslaev

290
3
7

2

You could improve your answer by adding the relevant code for the referenced answer, or simply post your feedback as a comment on the answer itself. – dub stylee Dec 04 '15 at 18:25

score 0 · Answer 5 · answered Oct 02 '20 at 16:46

0

No.

According to the Java documentation, there is no method that allows you to retrieve the results without specifying the column label or index.

https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

answered Oct 02 '20 at 16:46

john k

6,268
4
55
59

Retrieve entire row with ResultSet

5 Answers5