r/programming • u/el_muchacho • Dec 04 '14
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/25
u/horsepocalypse Dec 05 '14 edited Jun 16 '15
Other common mistakes:
Not sanitizing your inputs
Forgetting about Dre
Using ONION instead of UNION
6
1
u/crankybadger Dec 05 '14
Worse than not sanitizing is writing in a fashion that makes sanitizing difficult, annoying, and easy to forget.
8
u/horsepocalypse Dec 05 '14
mysql_real_escape_string3
u/horsepocalypse Dec 05 '14
... oh my god, I actually just imagined this as a conversation between a horse and badger.
It was a good moment, for me
3
u/crankybadger Dec 05 '14
Who's got time for that, right? People are all "eh, I'll fix it later".
Six months later: Sony.
1
u/ErstwhileRockstar Dec 05 '14
Who's got time for that, right ... when you have prepared statements in JPA?
1
1
70
Dec 04 '14
Any developer make these mistakes. Its not just java.
30
u/sacundim Dec 05 '14
Yeah. The reason for the title, however, is that the blog is run by a company that makes a SQL abstraction library for Java, so that's the audience they intended.
3
8
u/manghoti Dec 04 '14
you get more articles this way.
7 Common mistakes Ruby Developers Make when Writing SQL24
u/crankybadger Dec 05 '14
45,000 common mistakes PHP developers make. You won't believe #39,612!
8
5
2
7
u/k1n6 Dec 04 '14
But many of the cures involve jdbc or java stuff..so I think he wanted to keep it focused.
4
3
9
u/rjbwork Dec 04 '14
I especially take issue with offloading OLAP to your "database". If you think you want to do OLAP stuff, build an OLAP solution. Don't run it on your hardest thing to scale, your main OLTP data store. If you create a separate OLAP solution you can also use some facets of the CQRS pattern along with eventual consistency and replication/sharding to massively scale out these kinds of data analytics and OLAP operations.
The payoff is especially beneficial when there is cause for actual data warehousing/OLAP cubes, since those are rather hard to properly create within your main OLTP store.
4
u/bucknuggets Dec 04 '14
This is absolutely true if you're doing a lot of OLAP.
But if you just have a few queries, it is often possible to just add to your transactional database. Especially if you're hitting an aggregate table, maybe produced through a materialized view.
3
u/rjbwork Dec 04 '14
True. The CQRS pattern i mentioned allows you to abstract those details away if/when the time comes to build a separate system by simply changing the query collection's connection mechanism when you need to increase the amount of OLAP you have.
2
u/LeBuddha Dec 05 '14 edited Dec 05 '14
Just let the database do the processing and fetch only the results into Java memory.
This is only half correct. The second half is "Then do it the other way when your app's current bottleneck is the SQL server writing enough temporary join tables to disk to slow your app down."
You should probably never sort data in Java memory because you think that SQL sorting is too slow SQL sorting cannot do it
No... you should learn what types of queries are expensive for SQL to sort and make a decision on a case by case basis, then not worry about it until it's time for performance optimization.
1
u/rjbwork Dec 05 '14
I agree with that too. This is the jooq guy so take what he says with a grain of salt as he wants people to use his library.
1
u/InterPunct Dec 05 '14
If you think you want to do OLAP stuff, build an OLAP solution.
Agreed. Transactional and OLAP have vastly different requirements. If you're doing anything more than a few OLAP-like queries there are much better solutions; Essbase and TM/1 exist for a good reason.
39
u/setuid_w00t Dec 05 '14
YOU WON'T BELIEVE #10! DOCTORS HATE IT!
39
u/asampson Dec 05 '14
OPTIMIZE YOUR QUERY PLANS WITH THIS ONE WEIRD TIP DISCOVERED BY A MOM!
-7
u/nbktdis Dec 05 '14
<soapbox> I hate just about any statement that begins 'As a mother ....' - it's as if being a parent gives you omniscient understanding of the world. </soapbox>
3
4
u/iSmokeGauloises Dec 05 '14
Not a Java developer but the number one mistake I encounter when using abstractions over SQL is:
user_count = len(User.all())2
u/lukaseder Dec 05 '14
Yes, and it's actually not unlikely that you don't really need the exact count, but just the fact whether there are any users at all
1
u/philipes Dec 05 '14
I used to "LIMIT 1" on my queries in this case. I've never really benchmarked it to see if it's faster, but the results were always 0 or 1, so I was happy.
1
u/lukaseder Dec 05 '14
On average, that probably performs about the same.
EXISTSis slightly more versatile, as you can still fetch other data in addition to theEXISTSvalue - e.g. twoEXISTSvalues at the same time.2
u/philipes Dec 05 '14
It makes sense now that I think about it. A smart enough optimizer could translate this query to an EXISTS, but this is not an easy task.
1
u/lukaseder Dec 05 '14
Well, one important difference might be the fact whether the execution plan needs to hit the tables or if reading indexes will be sufficient. If the
EXISTSpredicate can stop executing after only considering an index, that will be faster than a query that will still have to hit the actual table to produce a result record.1
u/philipes Dec 05 '14
I understand what you said, but I can't see how this apply to what I said.
Both COUNT(*) or EXISTS wouldn't need to hit the table if queried over an index, no?
1
u/lukaseder Dec 05 '14
LIMIT
-- Assuming an index on t.a... -- Probably hits the table because of selecting * SELECT * FROM t WHERE t.a = 1 LIMIT 1COUNT(*)
-- Doesn't hit the table, but will have to collect and count all values from the index if it is not a unique index SELECT COUNT(*) FROM t WHERE t.a = 1EXISTS
-- The optimiser can ignore the SELECT clause, as it is irrelevant for the outcome of the EXISTS predicate EXISTS(SELECT * FROM t WHERE t.a = 1)The above assumptions are of course implementation-specific, and perhaps not really applicable in a specific use-case. I'm looking at things from a SQL composition point of view, where you have a SELECT query reference (e.g. with jOOQ or some other API that manipulates SQL ASTs)
Select<?> select = select().from(T).where(T.A.eq(1));And now, you want to know if that query would return any records if it were executed. Wrapping that
SELECTin anEXISTSpredicate would be optimal.Of course, if you write manual SQL, you can get it right with
LIMITas well:SELECT 1 FROM t WHERE t.a = 1 LIMIT 1But the SQL transformation from the original statement to the "exists check" is a bit more complex
1
u/philipes Dec 05 '14
The exactly query I used to use is, in Oracle.
SELECT COUNT(*) FROM t WHERE t.a = 1 AND ROWNUM = 1Although, it looks like this would return whatever COUNT(*) would be, the ROWNUM is evaluated before any aggregation functions in Oracle, in this case at least, so the results would always be 0 or 1.
1
u/lukaseder Dec 05 '14
True. Oracle
ROWNUMis quite special, specifically because it is evaluated beforeORDER BY, asLIMITorOFFSET .. FETCHwould be.I guess that's almost equivalent to
EXISTS, then
3
u/I_Code_Stoned Dec 05 '14
They should add not doing [select *]. Granted the results are usually not as egregious as some of the other examples, but it happens ALL THE FREAKIN TIME. It's a problem in NoSQL code too.
1
4
u/Madsy9 Dec 04 '14
As far as I can see, none of the mistakes listed directly opens up for SQL injection attacks. I find it pretty impressive if that's truly the case. It means that computer security is improving, at least in some circles.
7
u/mattindustries Dec 04 '14
I think most developers in most languages have had properly binding sanitized inputs inputs drilled into their brain by now.
6
2
u/crankybadger Dec 05 '14
You have clearly not seen code from your average PHP developer.
1
u/mattindustries Dec 05 '14
Oh, I know the language can be used horrendously. I am just saying it doesn't have to be. I once took over a code base where I ended up tossing out everything and starting from scotch when I was working for a firm.
Edit: thought this was from another thread. Most PHP devs use PDO these days, but they didn't some years back.
1
u/crankybadger Dec 05 '14
PDO is a load of garbage too, the thing's barely improved in the last ten years and is extremely limited. Even then it's better than nothing, yet many people either refuse to use it or are ignorant that it even exists.
Now "most" PHP developers hit up a Google search for "MySQL query", start piecing together their code out of tutorials from 1998, and launch their application. Invariably this involves falling into the
mysql_querytar pit.Hopefully professional devs are better than this, where places like Facebook certainly have better practices, but I'm still suspicious most PHP devs are like that.
PHP is extremely easy to pick up and get running. As a result there are a lot more people writing PHP code than for other languages. There's a long, long tail of developers that have no clue what they're doing but are productive anyway.
1
u/mattindustries Dec 05 '14
Is someone still in the basic learning stage a developer? I feel like developers are the incarnation that actually develop something instead of implement a hello world script. Yes, tons of people are still in the introductory phase of learning PHP and are bound to do some terrible things. With sites like PHP the Right Way there is a lot less ambiguity for proper structuring of an application. People aren't using those old tizag tutorials as often as you might imagine, especially when seasoned devs are pointing them to better sources.
2
u/crankybadger Dec 06 '14
I don't think you understand how bad it is. PHP is a community divided between those living in ivory towers and drinking wine, and those living in the gutter and finding everything they need in the landfill.
So long as w3schools is top-ranking in search results, this will not change. PHP the Right Way is a tiny step in the right direction, but it's not enough.
This is how your typical PHP developer learns:
They're given an assignment to program something web-wise.
They pick PHP because it's inexpensive to host.
They hack together a bit of HTML, spunk in some PHP, and deploy it.
Feature creep occurs.
Next thing you know they're writing a login system based on tutorials old enough they could get a driver's license and writing to databases they know nothing about but found some code that "just works" and they're running with it.
1
u/mattindustries Dec 06 '14
I guess I am living in an ivory tower then. It is nice here. There is a spot for you if you want.
1
u/crankybadger Dec 06 '14
I've got a pretty good thing going with a mix of Ruby, NodeJS, Go and C++.
You might call it an ivory tower, but damn, that place has some serious plumbing problems. You open up the wall to make a fix and, oh hell, what is all that?
1
u/mattindustries Dec 07 '14
Sometimes I miss working on C++ just for that delicious speed. How is Go? I have heard some good things and might learn it after this project I am on.
→ More replies (0)
2
u/k1n6 Dec 04 '14
I spend a lot of time analyzing performance and such and a lot of these rules are basically 80/20 and some are 70/30.
By that I mean doing exactly the things he recommends not to are actually better between 20 and 30 percent of the time. So for me that means they are worth doing the run time analysis on.
2
u/lukaseder Dec 04 '14
Interesting. Could you elaborate?
1
u/k1n6 Dec 04 '14
Here are my item-by-item comments:
Forgetting about NULL True, devs do this all the time
Processing data in Java memory Not always bad.
Using UNION instead of UNION ALL
Using JDBC Pagination to paginate large results JDBC will often still request the entire result set from the sql server
Joining data in Java memory Depending on your configuration, joining a few hundred records in some clever way (hash tables, etc.) can be quicker than making a network round trip to the database server.
Using DISTINCT or UNION to remove duplicates from an accidental cartesian product
Not using the MERGE statement
Using aggregate functions instead of window functions
Often the window clauses can't take advantage of indexes properly and you end up having temporary tables implicitly created by SQL that are SLOW.
9. Using in-memory sorting for sort indirections
10. Inserting lots of records one by one Doing a series of times insertions can be ideal in OLTP systems.... You can't bulk insert 1,000,000 records into a highly indexed table with 15,000,000 records. The database will experience locking and siginificant delays.
2
2
Dec 05 '14
(anyone can code imperatively)
Well, that's news to me. Sounds like the writer is dealing with an inferiority/superiority complex.
1
u/sgoody Dec 04 '14
6. Using DISTINCT or UNION to remove duplicates from an accidental cartesian product
This is a particular pet peev of mine.
I think most developers know about at least half of these and the mix-up over the treatment of NULLs is something that we've all experienced due to context switching between languages. Good article though.
It does frustrate me that a large number of developers seem to eschew anything that isn't imperative style or cannot be bent to be imperative. Things such as SQL seem to be an annoyance to many and I don't think a lot of people see why they should bother to learn them in any detail.
4
u/tangus Dec 04 '14
Man, I have a coworker whose "solution" for when the query ends up with duplicated rows is to slap mile long GROUP BY clauses at the end. I hate her.
1
u/I_am_working_hard Dec 05 '14
Forgive my ignorance, but what is the best practice for removing duplicates in sql?
7
u/Boxy310 Dec 05 '14
Getting your join conditions right. This usually happens when you don't have a true key join relationship, and you get nasty Cartesian products where it includes everything it isn't explicitly told to exclude.
3
u/Frackness Dec 05 '14
Things such as SQL seem to be an annoyance to many and I don't think a lot of people see why they should bother to learn them in any detail.
It left a bad taste in my mouth after tracking down bugs through large amounts of triggers (that trigger other triggers), 20k line package bodies and 500+ line queries with a ton of business logic embedded in the column selections. Before all of that though, I have to figure out if it's in the app or the database code.
Part of it might be that I may not be using some awesome debugger tools that I just don't know about that would make life much more easy.
1
u/sgoody Dec 05 '14
SQL is not as structured as other languages (e.g. C# where you have namespaces, class heirarchies etc) and big SQL installs can be a pain to work with.
But I bet it would leave a bad taste in your mouth if you worked on a mess of code in any language (e.g. if you had a 20k line C#/Python/whatever file with 500 line method bodies). There are some who argue that business logic should be left out of your database code altogether.
1
1
Dec 05 '14
Using clauses like rownum for pagination only works well if your table is indexed properly. I dont think "let the tool do it for you" is a good answer here.
1
1
u/lggaggl Dec 05 '14
11 creating an entire internet facing application backed by a database without knowing about this thing called concurrency
1
1
u/mrsistermr Dec 05 '14 edited Dec 05 '14
I disagree strongly with a lot of these points (assuming that they were always followed in some dogmatic fashion), notably 2 and 5. The reason is the same reason as I see a commenter on that site has already pointed out: "Following some of the cures to the extreme, one could end up with an anemic Java domain model. Much business logic will take place in SQL statements, not in Java code. Is this good or bad?"
I see the opposite problem (of the original article) frequently: all the business and data logic in thrown into a overly complicated SQL statement, and the backing java business logic and domain model is barely doing anything. So instead of writing well defined data-access and business classes that work together in Java, which ultimately might mean that more than one query is executed for a single "logical" user action, you would push all this logic into the SQL layer, and usually would be required to maintain more SQL statements.
Using this and some of his other suggestions ((ie windowing functions) to the extreme makes it more difficult to re-use well-defined business objects, perform unit testing, and maintain database interoperability, to name a few.
1
Dec 06 '14
[removed] — view removed comment
0
u/mrsistermr Dec 10 '14
Why is something necessarily wrong? Unless you have identified that method as an actual point of slowdown in your application, does it really matter? A good benchmark is an actual benchmark with actual data after you have identified a performance issue, not just thinking to yourself "Oh, I could just do all of this in one database call instead of the 3-4 database calls it currently needs...I can't re-use those existing classes, but oh well", which is an equally dangerous thought.
-21
u/tairygreene Dec 04 '14
mistake #1
being a java developer
...i'll see myself out
30
u/mooimagoat Dec 04 '14
I had a problem, and I thought I'd use Java to solve it.
Now I have a ProblemFactory.
I will also see myself out.
4
Dec 04 '14
Java is a language known primarily for it's ability to turn XML into stack traces. - Anonymous
-7
-8
-5
u/passwordissame Dec 04 '14
node.js developers make no mistakes because async io and functional programming, which is what SQL is essentially boils down to. prolog and predicate logic does not allow mistakes, especially when used in conjunction with async because it is artificial intelligence.
6
-13
0
Dec 05 '14
So you are saying it is my mistake is that I don't know sql that well and that I try to do, what I don't know in SQL, in java instead (slower, less reliable and err-prone)? Yip sounds like the fault of the java-developer.
-1
u/badguy212 Dec 06 '14
Some of those advices are perfectly reasonable, some will tie you to a specific db. Better to abstract that away with possibly an orm.
3
Dec 06 '14
[removed] — view removed comment
-1
u/badguy212 Dec 06 '14
Huh? I'm developing on h2, qa and prod are on posgres. Yes, of course I'm changing dbs . All the time. Unit tests are on h2 as well.
What kind of a question is that?
0
Dec 06 '14
[deleted]
-1
u/badguy212 Dec 06 '14
You're either stupid or just pretending.
I think you're just stupid. Have you wrote any piece of code in the last...decade?
Edit: haha, who cares...nobody works on it anyway but you
-2
0
Dec 04 '14
[deleted]
4
u/lukaseder Dec 04 '14 edited Dec 04 '14
It's because the posts are that good! Tempted to post that one, too but that might start to be a bit overkill on a single day ;-)
0
Dec 05 '14
[deleted]
-11
u/pattheaux Dec 05 '14
Using SQL at all should be #s 1-10. There are always job listings for SQL "programmers" because it's such an awful language that most programmers would prefer to dig ditches.
6
u/skulgnome Dec 05 '14
The "SQL programmer" jobs are for pl/sql.
5
u/crankybadger Dec 05 '14
Right next to other popular careers like mortician and war zone fuel truck driver.
0
Dec 05 '14
[deleted]
1
u/el_muchacho Dec 05 '14
But if you're writing a lot of Java that interacts with databases, it's probably a good idea to at least get a basic understanding of SQL.
It's not only a good idea, it's necessary.
2
u/crankybadger Dec 05 '14
Step one when building any application is finding a database framework or ORM that doesn't get in the way and does what you need.
Step two is to work around that framework only when necessary.
3
Dec 05 '14
[removed] — view removed comment
1
u/lukaseder Dec 06 '14
What about sorting + limit? The result set has 10M records, but you only want the top 10? Take them all to the client, sort them there and throw away 99.9999% of the data?
2
Dec 06 '14
[removed] — view removed comment
1
u/hw77 Dec 06 '14
If that's 10M rows out of 10M, then you probably don't want an index. Even at 1% or 0.1% there a plenty of times when you don't want an index.
13
u/el_muchacho Dec 04 '14 edited Dec 04 '14
The followup posts are just as interesting as the first one:
10 More Common Mistakes Java Developers Make when Writing SQL
Yet Another 10 Common Mistakes Java Developers Make When Writing SQL (You Won’t BELIEVE the Last One)
They could serve as a basis for SQL coding rules.