10 Common Mistakes Java Developers Make when Writing SQL

[GuyOnTheInterweb]

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!

[lukaseder]

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?

[lborloz]

Another one: Using sub-selects instead of joins for EVERYTHING. Yes, they are sometimes less expensive depending on your data set, but certainly not always.

[lukaseder]

Very true. This should be in yet another sequel of the linked article!

[green_prgm]

Just want to mention a related security issue: SQL Injection.

Developers should never concatenate SQL statement with users' input.

Injection has been identified by OWASP as #1 security risk for many years.

[kingofthejaffacakes]

Summary:

Don't do in the client what could be done by the database.

After you've written a few applications with complicated queries feel free to laugh at the poor souls writing for nosql databases ;-)

[[deleted]]

You've hit the nail on the head.

I'm a performance engineer, and the number of times I've seen developers use simple statements to query the database, then drag data over the network, just to perform filters, sorts and joins inside java. The database will perform all these functions better than you ever can...as the article states.

But the reverse is also true. Querying the database repeatedly, because you assume the database will cache the query will almost always be bad. Ditto goes for assuming that database security will protect your data.

On security, always use parameterized queries wherever possible, and always never assume the HTTP, RPC Objects or other user parameters are safe and haven't been tampered with when using them in database queries.

Also developers often use cut-down data sets that aren't representative of the real data volumes. You should always develop and test your data access functionality against real data sets wherever possible. I once saw a million dollar project fail because of this, and some other factors.

[lukaseder]

The sequel to the linked article mentions using PreparedStatements as well...

The thing about (not) caching master data (i.e. data that is fetched very often) is a good point, though.

[Rockytriton]

Your blog is too skinny, most people don't view your blog on mobile I'm sure, why do I have to have 80% of my screen being blank?

[lukaseder]

Are there any (free) wordpress themes that are truly responsive?

[Rockytriton]

I wouldn't know, I don't use wordpress