Extract String and StringBuffer

Discussion in 'Java' started by mikew01, May 24, 2012.

  1. mikew01

    mikew01 Guest

    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 pointduring 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 butalas they aren't.

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

    Thanks
     
    mikew01, May 24, 2012
    #1
    1. Advertising

  2. mikew01

    mikew01 Guest

    The requirement is to pull out the sql statements and profile them using the Oracle explain plan which will be executed by Sonar.
     
    mikew01, May 24, 2012
    #2
    1. Advertising

  3. 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 theOracle 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
     
    Robert Klemme, May 24, 2012
    #3
  4. mikew01

    markspace Guest

    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.
     
    markspace, May 24, 2012
    #4
  5. mikew01

    markspace Guest

    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/>
     
    markspace, May 24, 2012
    #5
  6. mikew01

    Lew Guest

    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
     
    Lew, May 24, 2012
    #6
  7. 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 |
     
    Martin Gregorie, May 24, 2012
    #7
  8. mikew01

    Roedy Green Guest

    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
    ..
     
    Roedy Green, May 28, 2012
    #8
  9. mikew01

    Roedy Green Guest

    On Thu, 24 May 2012 01:29:25 -0700 (PDT), mikew01
    <> 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
    ..
     
    Roedy Green, May 28, 2012
    #9
  10. mikew01

    markspace Guest

    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.
     
    markspace, May 28, 2012
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike
    Replies:
    3
    Views:
    1,865
  2. John Galt

    hash for String and StringBuffer

    John Galt, Feb 24, 2004, in forum: Java
    Replies:
    4
    Views:
    560
    Doug Pardee
    Feb 25, 2004
  3. Darren
    Replies:
    5
    Views:
    4,491
    Darren
    Jul 28, 2004
  4. gaurav v bagga

    String Vs. StringBuffer

    gaurav v bagga, Jan 18, 2007, in forum: Java
    Replies:
    5
    Views:
    512
  5. vj
    Replies:
    10
    Views:
    1,838
    Chris Uppal
    Mar 30, 2007
Loading...

Share This Page