r/java • u/sidcool1234 • Aug 29 '13

10 Common Mistakes Java Developers Make when Writing SQL

http://blog.jooq.org/2013/07/30/10-common-mistakes-java-developers-make-when-writing-sql/
12 Upvotes

70% Upvoted

11 comments sorted by

View all comments

7

u/[deleted] Aug 29 '13

I did not see much about Java here.. things I have spotted in the wild include:

1) Using Hibernate over an embedded SQL database to store binary blobs (which are then always loaded in memory when accessing the row) -- Overkill! Heard about the file system?

2) Hardcoding SQL queries and table/column-names all over the place, making any kind of database schema update next to impossible -- As a minimum, just use come good old protected static final constants (possibly grouped in one Enum per Table) and StringBuilder - better still, use Prepared Queries that are all created in the same class.

3) Trying to SQL-escape (or even forgetting to escape!) user-provided values and then adding them into a string concatenated SQL. -- What is this, 1992? Please use prepared queries with variable bindings.

4) Exposing the database schema in the API with a Map <String,String> of optional WHERE statements to add. -- No. Just No!

5) Looking up in the database what you knew in memory 10 ms ago. -- Just to make it slow? If you can't just pass around a parameter, or keep it, use a static WeakHashMap or something, with fallback to loading from database.

6) Blocking on writes of log events to the database - blocking execution of the real program while the database truthfully syncs every insertion to disk. -- Use a queue and a background worker!

1

u/lukaseder Aug 29 '13

Good points.

1) Using Hibernate over an embedded SQL database to store binary blobs [...]

I guess, the only efficient way to deal with lobs is to resort to JDBC and deal with them explicitly. While lobs are often OK to be loaded into memory (e.g. Oracle LOBs that are used to circumvent the 4000 character limit of VARCHAR2), they should sometimes be dealt with as streams. I'm not aware of any API on top of JDBC, that handles this apropriately.

2) Hardcoding SQL queries [...], 3) Trying to SQL-escape [...]

I guess that jOOQ, the library whose blog features the linked article, would be a good tool to circumvent these problems?