• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Tip for Refactoring SQL Statements in Java or C++ code

 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Chris
I'm working on a legacy system that does a lot of direct calls to the database from the code. It builds up SQL Statements out of strings and executes them directly. It doesn't use PreparedStatements or Stored Procedures. This is a nightmare to maintain, because each SQL string is unique. Also debugging is difficult, because the SQL Statements are built at run-time. I need to run the code in the debugger just to see what the SQL statements look like. Do you have any tips / suggestion of tools that I can use to refactor this?
Thanks
 
Sheriff
Posts: 17644
300
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I suspect most discussions this week will be around refactoring rather than reengineering. Michael Feathers' book "Working Effectively with Legacy Code" (WELC) is probably the most cited book that deals with strategies for refactoring existing code.

My question to Chris, as a followup, would be: Do you have anything in your book in addition to or different from the strategies that Michael Feathers proposes in WELC? I have found Michael's idea of "finding a seam" particularly useful and it has helped me find that little crack that I can start wedging into to loosen up tightly coupled code.
 
Author
Posts: 18
5
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Tanja,

Wow, that sounds painful! The first thing I would do is add a thin wrapper around the code that runs your queries, that spits out the SQL of all queries to a log file. This will make things a lot easier than having to run the debugger every time. Once you have a wrapper layer in place, you could do other cool stuff like timing query execution and outputting warning logs for slow queries.

Second, running SQL queries using raw strings instead of prepared statements is inviting SQL injections, so you should work to fix that asap. It would probably be difficult to jump straight from the current state to a full-blown ORM such as Hibernate, so I wouldn't recommend that. As a starting point, I would try implementing some helpers to take care of building PreparedStatements, with a nice fluent interface to make the code easier to read. I'm thinking something like:



If you make this StatementBuilder immutable, you will be able to share a half-built SQL statement between different queries, thus reducing copy-pasted code.

If you have to build a lot of complex WHERE clauses, you might want to create a Condition or Filter class that can take a StatementBuilder and return a new one with the appropriate WHERE clauses added. E.g.:



Of course, any refactoring you do to the DB queries should be accompanied by tests. You will need to set up a test harness where you can run queries against a real DB, check that the results are what you expect, and setup and teardown the DB data in between tests. You might want to use an in-memory DB such as h2 or HSQLDB for easier setup and faster test execution.
 
Talk sense to a fool and he calls you foolish. -Euripides A foolish tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic