How to read the entire resultSet data obtained from the query without using while loop

Question

I am having a resultSet which contains data fetched from the query after which i want to print all the data captured without using while loop, below is my sample code

@Autowired
private OrderRepository orderRepository;

String msAccDB = "jdbc:ucanaccess://" + datafile.mdb;
Connection conn = DriverManager.getConnection(msAccDB);

Statement orders = conn.createStatement();
ResultSet result = orders
                        .executeQuery("SELECT [ORDERID], [ORDERDATE], [QUANTIRY], [PAYMENT] FROM [ORDERS]");
                
            while (result.next()) {
                Order order = new Order();
                order.setOrderId(result.getString("ORDERID"));
                order.setOrderDate(result.getString("ORDERDATE"));
                order.setQuantity(result.getString("QUANTIRY"));
                order.setPayment(result.getString("PAYMENT"));
                orderRepository.save(order);
            }                

result.close();
orders.close(); 

Using while loop takes much time to complete since the mdb file that is being read is large in size of about 1GB, any help will be much appreciated.

Answer 1

Don't use JPA for this kind of processing. It just adds overhead that you don't seem to need.

Instead use a JdbcTemplate to execute an insert. Use the batch features to reduce the overhead of database round trips.

Access ResultSet values by index, instead of by name. Some drivers seem to be rather inefficient with names.

While you're at it you might want to use JdbcTemplate also for the original query. Makes for simpler code and fixes your exception handling.