[prev in list] [next in list] [prev in thread] [next in thread]
List: groovy-user
Subject: Re: [groovy-user] [ann] groovy.sql.Sql and streaming ResultSet
From: Guillaume Laforge <glaforge () codehaus ! org>
Date: 2014-01-31 14:07:50
Message-ID: CAJ2HCd8DMu5osFFjZiAvwrY-GSm0MQiyPSoBFy8R8Ev_K4oc+Q () mail ! gmail ! com
[Download RAW message or body]
That's pretty elegant :-)
On Fri, Jan 31, 2014 at 12:11 PM, Dinko Srkoč <dinko.srkoc@gmail.com> wrote:
> Hi all,
>
> for a while I've been having an itch that now I'm trying to scratch. I
> like using collection methods to transform the data fetched from the
> database (Sql#rows() + collectMany/collect/…). However, for large data
> sets this can be expensive, both in time, and memory. The alternative is
> using Sql#eachRow(), which is as efficient as can get, but has that
> imperative touch that I'd like to avoid.
>
> Enter *Groovy Sql Stream Extension*
>
> The project is trying to combine what I like in both of the above
> mentioned approaches - the convenience of collection processing with the
> efficiency of eachRow().
>
> The code is worth a thousand words:
>
> @Grab('hr.helix:groovy-sql-stream-extension:0.4.3')import groovy.sql.Sql
>
> def sql = Sql.newInstance(...)
>
> sql.withStream('SELECT * FROM a_table') { stream ->
> stream.collectMany {
> [it.col_foo, calcMe(it.col_bar)]
> }.collect {
> calcMeAgain(it)
> }.findAll {
> it < 10000
> }.take(100)
> .toList() // forces the realization of the stream
> }
>
> The idea is to delay the evaluation until forced and then to iterate over
> the data set only once.
>
> *Note*: it is necessary to call toList() or force() inside the
> withStream()‘s code block because withStream will release its resources
> when it's done. Attempting to realize the stream after that will result in
> SQLException being thrown (*ResultSet not open*).
>
> Any suggestions or comments are more than welcome.
> Please excuse the somewhat lacking documentation, the project is still
> fresh.
>
> https://github.com/dsrkoc/groovy-sql-stream-extension
>
> Cheers,
> Dinko
>
--
Guillaume Laforge
Groovy Project Manager
Head of Groovy Development at SpringSource
http://www.springsource.com/g2one
[Attachment #3 (text/html)]
<div dir="ltr">That's pretty elegant :-)</div><div \
class="gmail_extra"><br><br><div class="gmail_quote">On Fri, Jan 31, 2014 at 12:11 \
PM, Dinko Srkoč <span dir="ltr"><<a href="mailto:dinko.srkoc@gmail.com" \
target="_blank">dinko.srkoc@gmail.com</a>></span> wrote:<br> <blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div><p style="margin:1.2em 0px!important">Hi \
all,</p> <p style="margin:1.2em 0px!important">for a while I've been having an itch \
that now I'm trying to scratch. I like using collection methods to transform the data \
fetched from the database (<code \
style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;display:inline">Sql#rows()</code> \
+ <code style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;display:inline">collectMany</code>/<code \
style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;display:inline">collect</code>/…). \
However, for large data sets this can be expensive, both in time, and memory. The \
alternative is using <code \
style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;display:inline">Sql#eachRow()</code>, \
which is as efficient as can get, but has that imperative touch that I'd like to \
avoid.</p>
<p style="margin:1.2em 0px!important">Enter <em>Groovy Sql Stream Extension</em></p>
<p style="margin:1.2em 0px!important">The project is trying to combine what I like in \
both of the above mentioned approaches - the convenience of collection processing \
with the efficiency of <code \
style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;display:inline">eachRow()</code>.</p>
<p style="margin:1.2em 0px!important">The code is worth a thousand words:</p>
<pre style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;font-size:1em;line-height:1.2em;margin:1.2em \
0px"><code style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;displ \
ay:inline;white-space:pre-wrap;overflow:auto;border-top-left-radius:3px;border-top-rig \
ht-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;border:1px \
solid rgb(204,204,204);padding:0.5em \
0.7em;display:block!important;display:block;padding:0.5em;background-color:rgb(35,36,31);background-repeat:initial \
initial;color:rgb(248,248,242)"><span>@Grab</span>(<span \
style="color:rgb(230,219,116)">'hr.helix:groovy-sql-stream-extension:0.4.3'</span>)
<span style="color:rgb(102,217,239)">import</span> groovy.sql.Sql
def sql = Sql.newInstance(...)
sql.withStream(<span style="color:rgb(230,219,116)">'SELECT * FROM \
a_table'</span>) { stream -> stream.collectMany {
[it.col_foo, calcMe(it.col_bar)]
}.collect {
calcMeAgain(it)
}.findAll {
it < <span style="color:rgb(174,129,255)">10000</span>
}.take(<span style="color:rgb(174,129,255)">100</span>)
.toList() <span style="color:rgb(117,113,94)">// forces the realization of the \
stream</span> }
</code></pre>
<p style="margin:1.2em 0px!important">The idea is to delay the evaluation until \
forced and then to iterate over the data set only once.</p> <p style="margin:1.2em \
0px!important"><em>Note</em>: it is necessary to call <code \
style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;display:inline">toList()</code> \
or <code style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;display:inline">force()</code> \
inside the <code style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;display:inline">withStream()</code>‘s \
code block because <code \
style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;display:inline">withStream</code> \
will release its resources when it's done. Attempting to realize the stream after \
that will result in <code \
style="font-size:0.85em;font-family:Consolas,Inconsolata,Courier,monospace;margin:0px \
0.15em;padding:0px 0.3em;white-space:pre-wrap;border:1px solid \
rgb(234,234,234);background-color:rgb(248,248,248);border-top-left-radius:3px;border-t \
op-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;display:inline">SQLException</code> \
being thrown (<em>ResultSet not open</em>).</p>
<p style="margin:1.2em 0px!important">Any suggestions or comments are more than \
welcome.<br>Please excuse the somewhat lacking documentation, the project is still \
fresh.</p> <p style="margin:1.2em 0px!important"><a \
href="https://github.com/dsrkoc/groovy-sql-stream-extension" \
target="_blank">https://github.com/dsrkoc/groovy-sql-stream-extension</a></p> <p \
style="margin:1.2em 0px!important">Cheers,<br>Dinko</p> </div></div>
</blockquote></div><br><br clear="all"><div><br></div>-- <br>Guillaume \
Laforge<br>Groovy Project Manager<br>Head of Groovy Development at SpringSource<br><a \
href="http://www.springsource.com/g2one" \
target="_blank">http://www.springsource.com/g2one</a> </div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic