MAKE IT SO: Java DB Connections & Transactions

Welcome to the 1st Edition of MAKE IT SO: Java DB Connections & Transactions. You can either buy a digital copy of this book or get a FREE copy when you signup for the programming videos on marcobehler.com - just email me afterwards.

Large JDBC ResultSets

A topic which you will encounter sooner or later in real-life: You want to query a bigger table, let’s say millions of rows and your query will return tens of thousands of them.

You can of course load the whole resultset into memory at once, providing you have enough. Or you do not query your database in one go, but execute several "LIMIT"-like sql queries.

But another viable option is to use the setter "setFetchSize" on your JDBC statement - if your jdbc driver supports it properly (MySQL and H2 for example don’t and pretty much ignore it, Oracle supports it just fine).

When you use setFetchSize in combination with an Oracle db, you will get a real server side cursor and the jdbc driver will make multiple round-trips to the database, but only fetch as many rows as you specified each round-trip. This can be a huge memory saver!

Our example below shows you the correct usage, but unfortunately H2 will ignore setting the fetchSize. Nevertheless you should know about this setting.

Create This .java File And Run It

package part_02_additional.code;

import org.h2.jdbcx.JdbcDataSource;
import org.jdbcdslog.JDBCDSLogException;
import org.junit.Before;
import org.junit.Test;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import static org.junit.Assert.fail;

/**
 * @author Marco Behler <marco@marcobehler.com>
 */
public class LargeResultSetExercise {

    private DataSource ds;

    @Before
    public void setUp() {
        ds = getDataSource();

        try (Connection connection = ds.getConnection()) {
            createTables(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void exercise() throws JDBCDSLogException {
        /* this would register it with a JNDI service. ignore for now
        Context ctx = new InitialContext();
        ctx.bind("jdbc/datasource", ds);*/
        System.out.println("We are about to load thousands of records...");
        try (Connection connection = ds.getConnection()) {
            // for fetchSize below to work properly, you have to make
            // sure to start a tx/setAutocommit to false

            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();

            // we only want to query the server for batches of 1000 !
            // but make sure to read your JDBC driver documentation
            // regarding this property. your driver is allowed to ignore
            // it!
            statement.setFetchSize(1000);

            ResultSet results = statement.executeQuery(
                    "SELECT my_magic_number from numbers");
            results.next();
            while (results.next()) {
                int number = results.getInt("my_magic_number");
                System.out.println(number);
            }
            // these two would also be automatically closed once you close
            // the connection
            results.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    raitpve rtacDauSoe DaeutgtorScae() {
        uartdcbSJocDae ds = new aatoudSDcrJbce)(;
        ds.tRUesL(::jL_cbm=xmArEdYcLbi2:OeD-B;ee_SCse"d_DhE1");
        ds.Uteessr(sa"");
        ds.srsowtesPad("sa");
        rruten ds;
    }

    raen  eem/u  b0ist0i t gi0 0r1neas o/atbnwnrle
    rtvaipe oivd leteTacaerbs(oCinoncetn ncon) {
        try {
            nocn.enSmcteatetreat)(.etceuxe(lrsae meuetbn rbe"t ca"
                    + _bgubu mu(ri)n"uenmemc_y eiamnqr");
            for (nit i = 0; i < 01000; i++) {
                nocn.nSataremeetectt)(.xecetue(niits rnn ore"bestu m" +
                        s eulav("" + i + )"");
            }
        } tccah (extQEipLcoSn e) {
            fial(e.gtgeMeasse))(;
        }
    }

}

Want to read on?

Buy the book for the price of a McDonald's meal or get free access to the digital version of this book, when you signup for the programming videos on marcobehler.com

The Digital Version Includes:

  • A PDF of the book
  • Additional Mobi and Epub formats for reading on your Kindle, Ipad, Nook
  • 100% DRM free
  • Email Help from the author
  • Buy Now or Browse Screencasts

W udY letuShohaoSe

Console Output After Running The Test
uFr43-e gi.Console Output After Running The Test



luiyrS lDdts

  1. None

ieneDbuou Q Threetmor AN ss

ydeh tsr:CostdmavpoBar h u a W Jobi utu : rny rYtQopDieo ?

tpylts iuehi lwst bm peg ytsft L gtTfenitmpres ruleic mhe i ilai fQ e, hhho2hmtt mu oeekm dre u HaettlaM :ioe ner.ozvuoyyh eh idnr mn,ofraiSwaosSroeo oykrrRteej. aais ydlyoa etst nhH csAqTm.ts r2lae rwutad:

anrhile rO rr2mlyosS(fees s l _ oRllv ,i sm dslRnOehyf fo mth i.id eso/osoysoseoonme etbdtrR ArsdBXeHirtse we .at nsv atdlhe DsHrmalearan_epsleTc vua ritee wslss (ese y tw eT p TE nse eaeIure ladtfeeRhrrlrMW wdw sOattue ltlbrileRMa,nO bnaoOty uiiuabtt’ieraSRa rinp onooihD Rulen eh e 2pie net)Eidahdnhb t drielhi asrthMemh w)c stes ecents(ycuOls ce as tue.t leY x lmo)ksf. fu y wY nrii t vuhieh o lxeitnBe u hsitstt sa I, Fla ao aerr e,rrY boa.tE.

l re Tl -heHuMoams2

comments