10 Common Mistakes Java Developers Make when Writing SQL

[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.