Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #14767 > unrolled thread

Extract String and StringBuffer

Back to article view | Back to comp.lang.java.programmer

Contents

  Extract String and StringBuffer mikew01 <mikew01@blueyonder.co.uk> - 2012-05-24 01:29 -0700
    Re: Extract String and StringBuffer Leif Roar Moldskred <leifm@dimnakorr.com> - 2012-05-24 04:02 -0500
      Re: Extract String and StringBuffer mikew01 <mikew01@blueyonder.co.uk> - 2012-05-24 03:21 -0700
        Re: Extract String and StringBuffer markspace <-@.> - 2012-05-24 08:21 -0700
          Re: Extract String and StringBuffer Roedy Green <see_website@mindprod.com.invalid> - 2012-05-28 09:28 -0700
            Re: Extract String and StringBuffer markspace <-@.> - 2012-05-28 12:19 -0700
        Re: Extract String and StringBuffer Robert Klemme <shortcutter@googlemail.com> - 2012-05-24 08:20 -0700
          Re: Extract String and StringBuffer markspace <-@.> - 2012-05-24 08:47 -0700
            Re: Extract String and StringBuffer Lew <lewbloch@gmail.com> - 2012-05-24 09:52 -0700
        Re: Extract String and StringBuffer Martin Gregorie <martin@address-in-sig.invalid> - 2012-05-24 20:29 +0000
    Re: Extract String and StringBuffer Roedy Green <see_website@mindprod.com.invalid> - 2012-05-28 09:29 -0700

#14767 — Extract String and StringBuffer

Hi all

I need to extract all of the SQL select statements from a codebase ideally during build time.
The SQL queries are embedded in the source code in either String or StringBuffer form, none of them are annotated with a unique annotation.

I've been looking into AspectJ and maybe applying an aspect to the class Connection for instance which could output the sql to whereever at some point during execution, this does mean however that I'd need some way of executing each and every method which executes a select statement in a unit test for instance.

I've also briefly looked into AST as a possibility.

If the queries were annotated I could have used an annotation processor but alas they aren't.

Has anyone done this kind of thing before or can reccommend a suitable method?

Thanks

[toc] | [next] | [standalone]

#14768

mikew01 <mikew01@blueyonder.co.uk> wrote:

> I need to extract all of the SQL select statements from a codebase
> ideally during build time.

That sounds like an odd requirement. Why do you need to do so?

> The SQL queries are embedded in the source code in either String or
> StringBuffer form, none of them are annotated with a unique
> annotation. 

I think you are going to struggle. The SQL statements in StringBuffers
are not accessible at build time at all, and some of the ones in
Strings might not be either.

If you truly need this, you should probably bite the bullet and do the
work of digging through the code and annotate all the statements by
hand.

-- 
Leif Roar Moldskred

[toc] | [prev] | [next] | [standalone]

#14769

The requirement is to pull out the sql statements and profile them using the Oracle explain plan which will be executed by Sonar.

[toc] | [prev] | [next] | [standalone]

#14774

On 5/24/2012 3:21 AM, mikew01 wrote:
> The requirement is to pull out the sql statements and profile them
> using the Oracle explain plan which will be executed by Sonar.


In my opinion, I better requirement would be to isolate the SQL so that 
it was no longer part of the code, but was in a separate file where it 
could be accessed.  That way no one has to do this kind of busy work 
again.

Another better requirement would be to produce a decent integration test 
so that it could be used to as the test bed for performance/Sonar.

[toc] | [prev] | [next] | [standalone]

#14865

On Thu, 24 May 2012 08:21:28 -0700, markspace <-@.> wrote, quoted or
indirectly quoted someone who said :

>
>In my opinion, I better requirement would be to isolate the SQL so that 
>it was no longer part of the code, but was in a separate file where it 
>could be accessed.  That way no one has to do this kind of busy work 
>again.

It would be something like the way you do internationalisation with
resource bundles.
 
It could for example let you specialize code for different SQL
engines.
-- 
Roedy Green Canadian Mind Products
http://mindprod.com
Controlling complexity is the essence of computer programming.
~ Brian W. Kernighan 1942-01-01
.

[toc] | [prev] | [next] | [standalone]

#14870

On 5/28/2012 9:28 AM, Roedy Green wrote:
> On Thu, 24 May 2012 08:21:28 -0700, markspace<-@.>  wrote, quoted or
> indirectly quoted someone who said :
>
>>
>> In my opinion, I better requirement would be to isolate the SQL so that
>> it was no longer part of the code, but was in a separate file where it
>> could be accessed.  That way no one has to do this kind of busy work
>> again.
>
> It would be something like the way you do internationalisation with
> resource bundles.
>
> It could for example let you specialize code for different SQL
> engines.


Yes, that's exactly what I implemented.  JavaDB (i.e. Derby) gets used 
for unit testing, and something else for integration and production, so 
I had a little properties file with the SQL in it.  Just swap out the 
properties files to switch SQL flavors.

[toc] | [prev] | [next] | [standalone]

#14775

On Thursday, May 24, 2012 12:21:46 PM UTC+2, mikew01 wrote:
> The requirement is to pull out the sql statements and profile them using the Oracle explain plan which will be executed by Sonar.

Plans can vary dramatically depending on data.  How do you want to deal with that, especially if SQL statements are created using StringBuilder?  Where do you get the data from which you use in your test case?

Also creation of a single statement can be spread across different methods.  That'll be difficult to resolve.


It's probably better to benchmark the application while enabling one of the Oracle traces during execution.  You'll then get the real data and execution plans.  For production usage the point in time when statistics are updated is also an important factor.  That might be difficult to properly test in the lab if you only have those SQL statements (assumed you can recover them from the source).

Kind regards

robert

[toc] | [prev] | [next] | [standalone]

#14776

On 5/24/2012 8:20 AM, Robert Klemme wrote:

> It's probably better to benchmark the application while enabling one
> of the Oracle traces during execution.  You'll then get the real data
> and execution plans.


And this is an even better idea than trying to rely on integration tests 
(although a basic check of SQL goodness isn't a bad idea either, to be 
done before things get to production).

Another idea would be to capture the inputs to a typical production 
case, and add them to a performance test or stress test.  Instrumenting 
production systems is OK if you can do it, but I like automated, 
reproducible tests better.


I just did a quick web search for SONAR.  It's one of those cruddy 
little "code goodness" tools, where you apparently don't trust your 
programmers to write good code, or you have no control over software 
quality, so instead of fixing those problems you install a tool to give 
you little reports about how crappy your code is.  Dear sweet Jebus on a 
pogo stick.

<http://www.sonarsource.org/>

[toc] | [prev] | [next] | [standalone]

#14779

markspace wrote:
> Robert Klemme wrote:
> 
>> It's probably better to benchmark the application while enabling one
>> of the Oracle traces during execution.  You'll then get the real data
>> and execution plans.
> 
> And this is an even better idea than trying to rely on integration tests 
> (although a basic check of SQL goodness isn't a bad idea either, to be 
> done before things get to production).
> 
> Another idea would be to capture the inputs to a typical production 
> case, and add them to a performance test or stress test.  Instrumenting 
> production systems is OK if you can do it, but I like automated, 
> reproducible tests better.
> 
> I just did a quick web search for SONAR.  It's one of those cruddy 
> little "code goodness" tools, where you apparently don't trust your 
> programmers to write good code, or you have no control over software 
> quality, so instead of fixing those problems you install a tool to give 
> you little reports about how crappy your code is.  Dear sweet Jebus on a 
> pogo stick.
> 
> <http://www.sonarsource.org/>

Here's an interceptor for JDBC calls. I've seen this in action and it's awesome.

<http://code.google.com/p/log4jdbc/>

-- 
Lew

[toc] | [prev] | [next] | [standalone]

#14782

On Thu, 24 May 2012 03:21:46 -0700, mikew01 wrote:

> The requirement is to pull out the sql statements and profile them using
> the Oracle explain plan which will be executed by Sonar.
>
Has time I had to do that I was able to use ODBC tracing facilities to 
capture SQL statements as they were executed, but that was a while ago 
and I can't recall whether all ODBC implementations had that ability or 
was a capability of the particular driver I was using.

Of course, using this approach does rather assume that you have a 
regression test that is known to exercise all SQL statements and/or there 
is somebody who knows enough about the system to be able to reliably run 
them all. 

If this is part of a DB tuning exercise you should be aware that you'll 
need to run the complete set of statements more than once, so it would be 
worthwhile to build a set of scripts that can can be used to (a) re-
capture the set of scripts and (b) run them and record performance data 
for analysis.

There's one other vital bit of information you need too - a use frequency 
for each SQL statement. If you don't have this information it would be 
well worth instrumenting your system to capture it. DB tuning is a really 
good example of a place where the 80/20 rule applies: unless a piece of 
SQL isn't in the top 20% of most frequently run statements there's little 
point in optimising the DB for it unless its something quite unusual, 
e.g. the CEO uses it once a day/week/month or it is run once a night, is 
tooth-achingly slow, accesses most of the DB and tends to run longer than 
the quiet period its meant to complete its task in.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

[toc] | [prev] | [next] | [standalone]

#14866

On Thu, 24 May 2012 01:29:25 -0700 (PDT), mikew01
<mikew01@blueyonder.co.uk> wrote, quoted or indirectly quoted someone
who said :

>I need to extract all of the SQL select statements from a codebase 

see http://mindprod.com/jgloss/parser.html

You might concoct a parser to find them or to add annotations that you
manually proofread.
-- 
Roedy Green Canadian Mind Products
http://mindprod.com
Controlling complexity is the essence of computer programming.
~ Brian W. Kernighan 1942-01-01
.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.programmer

csiph-web