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
W udY letuShohaoSe

luiyrS lDdts
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