Yet Another 10 Common Mistakes Java Developers Make When Wri

(Sorry for that click-bait heading. Couldn’t resist ;-) )

We’re on a mission. To teach you SQL. But mostly,we want to teach you how to appreciate SQL. You’ll love it!

Getting SQL right or wrong shouldn’t be about that You’re-Doing-It-Wrong attitude that can be encountered often when evangelists promote their object of evangelism. Getting SQL right should be about the fun you’ll have once you do get it right. The things you start appreciating when you notice that you can easily replace 2000 lines of slow,hard-to-maintain,and ugly imperative (or object-oriented) code with 300 lines of lean functional code (e.g. using),or even better,with 50 lines of SQL.

We’re glad to see that our blogging friends have started appreciating SQL,and most specifically,window functions after reading our posts. For instance,take

• that lead to him starting his(among other reasons)
• Es-Queue-El

So,after our previous,very popular posts:

… we’ll bring you:

Yet Another 10 Common Mistakes Java Developers Make When Writing SQL

And of course,this doesn’t apply to Java developers alone,but it’s written from the perspective of a Java (and SQL) developer. So here we go (again):

1. Not Using Window Functions

After all that we’ve been preaching,this must be our number 1 mistake in this series.of them all. They’re so incredibly useful,they should be the number one reason for anyone to switch to a better database,e.g. PostgreSQL:

Mind bending talk byaboutat tonight's. My new resolution: Install PostgreSQL and study SQL standard at once.

— Peter Kofler (@codecopkofler)

If free and/or Open Source is important to you,you have absolutely no better choice than using(and you’ll even get to use the free,if you’re a Java developer).

And if you’re lucky enough to work in an environment with Oracle or SQL Server (or DB2,Sybase) licenses,you get even more out of your new favourite tool.

We won’t repeat all the window function goodness in this section,we’ve blogged about them often enough:

The Cure:

Remove MySQL. Take a decent database. And start playing with window functions. You’ll never go back,guaranteed.

2. Not declaring NOT NULL constraints

This one was already part of a previous list where we claimed that you should add as much metadata as possible to your schema,because your database will be able to leverage that metadata for optimisations. For instance,if your databaseknowsthat a foreign key value inBOOK.AUTHOR_IDmust also becontained exactly once inAUTHOR.ID,then a whole set of optimisations can be achieved in complex queries.

Now let’s have another look atNOT NULLconstraints. If you’re using Oracle,NULLvalues will not be part of your index. This doesn’t matter if you’re expressing anINconstraint,for instance:

But what happens with aNOT INconstraint?
Due toNULL,there is a slight risk of the second query unexpectedly not returning any results at all,namely if there is at least oneNULLvalue as a result from the subquery. This is true for all databases that get SQL right.
But because the index onnullable_columndoesn’t contain anyNULLvalues,Oracle has to look up the complete content in the table,resulting in aFULL TABLE SCAN. Nowthatis unexpected! Details about this can be seen.
The Cure:
Carefully review all your nullable,yet indexed columns,and check if you really cannot add aNOT NULLconstraint to those columns.
The Tool:
If you’re using Oracle,use this query to detect all nullable,yet indexed columns:
Example output:
And then,fix it!
(Accidental criticism of Maven is irrelevant here;-))
If you’re curious about more details,see also these posts:





3. Using PL/SQL Package State
Now,this is a boring one if you’re not using Oracle,but if you are (and you’re a Java developer),be very wary of PL/SQL package state. Are you really doing what you think you’re doing?
,e.g.
FUNCTION next_n RETURN NUMBER;

END pkg;

CREATE OR REPLACE PACKAGE BODY pkg IS

FUNCTION next_n RETURN NUMBER

IS

BEGIN

n := n + 1;

RETURN n;

END next_n;

END pkg;

Wonderful,so you’ve created yourself an in-memory counter that generates a new number every time you callpkg.next_n. But who owns that counter? Yes,the session. Each session has their own initialised “package instance”.
But no,it’s probably not the session you might have thought of.
We Java developers connect to databases through connection pools. When we obtain a JDBC Connection from such a pool,we recycle that connection from a previous “session”,e.g. a previous HTTP Request (not HTTP Session!). But that’s not the same. The database session (probably) outlives the HTTP Request and will be inherited by the next request,possibly from an entirely different user. Now,imagine you had a credit card number in that package…?
Not The Cure:
Nope. Don’t just jump to usingpackages
FUNCTION next_n RETURN NUMBER;

END pkg;

Because:

You cannot even use that package from SQL,now (see).
Mixing thisPRAGMAwith regular package state from other packages just makes things a lot more complex.

So,don’t.
Not The Cure:
I know. PL/SQL can be a beast. It often seems like such a quirky language. But face it. Many things run much much faster when written in PL/SQL,so don’t give up,just yet. Dropping PL/SQL is not the solution either.
The Cure:
At all costs,try to avoid package state in PL/SQL. Think of package state as ofstaticvariables in Java. While they might be useful for caches (and constants,of course) every now and then,you might not actually access that state that you wanted. Think about load-balancers,suddenly transferring you to another JVM. Think about class loaders,that might have loaded the same class twice,for some reason.
Instead,pass state as arguments through procedures and functions. This will avoid side-effects and make your code much cleaner and more predictable.
Or,obviuously,persist state to some table.
4. Running the same query all the time
Master data is boring. You probably wrote some utility to get the latest version of your master data (e.g. language,locale,translations,tenant,system settings),and you can query it every time,once it is available.
At all costs,don’t do that. You don’t have to cache many things in your application,as modern databases have grown to be extremely fast when it comes to caching:

Table / column content
Index content
Query / materialized view results
Procedure results (if they’re deterministic)
Cursors
Execution plans

So,for your average query,there’s virtually no need for an ORM second-level cache,at least from a performance perspective (ORM caches mainly fulfil other purposes,of course).
But when you query master data,i.e. data that never changes,then,network latency,traffic and many other factors will impair your database experience.
The Cure:
                        
（编辑：安卓应用网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!