SQL May Do It Better Than Your Code

Ohh, How I Love Java!!!!....but do i really need to write that code?

A question:

"Never do in code what you can get the SQL server to do well for you” - Is this a recipe for a bad design?

Yes!!!. There are things that SQL is made to do and, sometimes we do them in code:

• Joins - codewise it'd require complex array manipulation - Filtering data (where) - codewise it'd require heavy inserting and deleting of items in lists
• Selecting columns - codewise it'd require heavy list or array manipulation
• Aggregate functions - codewise it'd require arrays to hold values and complex switch cases
• Foreign key integrity - codewise it'd require queries prior to inserting and assumes nobody will use the data outside app
• Primary key integrity - codewise it'd require queries prior to inserting and assumes nobody will use the data outside app

Source: StackExchange Q/A

Doing these things instead of relying upon SQL or the RDBMS leads to writing tons of code with no added value, meaning more code to debug and maintain. Source: StackExchange Q/A.

We developers have been guilty of this at some point in our career or maybe you're a beginner; don't be like us or rather don't be like me 🥺.

I have been caught wanting to do this recently and that's why I'm writing this post. Below are two ideas I tried implementing with code but an SQL query saved me.

A feed feature

Idea: Return all articles and gifs, showing the most recently posted articles or gifs first.

Code

The code snippet 👇 didn't return the expected result; it was buggy and I spent several hours working through different iterations.

// ps: NOT THE FULL METHOD/FUNCTION CODE
public LinkedHashMap<String,Object> getArticlesAndGifsByMostRecent(){

    LinkedHashMap<String, Object> feed = new LinkedHashMap<>();

    ArrayList<Object> dataList = new  ArrayList<>();

    Iterator<Article> articleIterator = getArticleRepository().findByCreatedOnDesc().iterator();
    Iterator<Gif> gifIterator = getGifRepository().findByCreatedOnDesc().iterator();

    while (articleIterator.hasNext() && gifIterator.hasNext()){
        if (articleIterator.next().getCreatedOn().getTime() > gifIterator.next().getCreatedOn().getTime()){
            dataList.add(articleIterator.next());
        }else {
            dataList.add(gifIterator.next());
        }
    }

   ... // Not the end there was more of this sadly

}

Using Java Stream API would have made the code a lot readable and shorter but check out the SQL query 👇 that did it better.

SQL

The Query below is way shorter on a single line 😔.

SELECT created_on as createdOn
FROM article
UNION ALL
SELECT created_on as createdOn
FROM gif
ORDER BY createdOn DESC

It saved me from looping for a while....it saved me from asking a lot of ifs 😂

• It was faster
• It could have saved me a lot of time if used initially
• It is a lot more readable than my Java code

Deletion of data

Idea: User can DELETE all of their images.

Well, this time it wasn't just my code, but relying on Spring JPA(ORM) to perform the deletion.

Code

@Repository
public interface ImageRepository extends JpaRepository<Image, Long> {
    // Using Spring Java Persistence API
    void deleteImagesByUser(User currentUser);

}

This looks right and should work....but the runtime errors I got back 😭. Now see the SQL query👇 that did it better.

SQL

• :userId sets named parameter for the query method

DELETE FROM image WHERE user_user_id = :userId

So you see 👀.... give to the Database what belongs to the Database 😃.

Summary

In this post, we've seen why we need to delegate to databases for certain tasks. This saves

• Time
• Memory Space

  PS... Good code is better than bad queries, good queries instead of lots of needless code; that's my point.

Next time you want to perform certain tasks involving your DB, stop to think if SQL could do it better. And you will be building better software.

Thank you for reading. I mostly write tutorials on the problems I have faced and whatever new tech I explore, so stay tuned for the next article(I will be writing about Java Stream API).

If you found this article helpful, react and share. Cheers.

And you can follow me on Twitter.