10 erroneous ideas Java gurus should avoid when writing SQL

Posted by Sujain Thomas
May 27, 2017

Self-Published

Many if not all Java developers tend to blend imperative thinking and object oriented thinking when coding depending on their experience, moods and dogma. SQL, on the other hand, is a declarative language and that is why most Java gurus find it confusing writing SQL. Expressing a query in SQL is not difficult but expressing it optimally is confusing. One needs to think in the line of set theory and not objected-oriented or imperative. Consequently, several Java developers find themselves making certain mistakes they can easily avoid. The list below sites ten of these common errors.

  1. Data Processing in Java Memory

Only a handful of Java developers understand SQL language very well. Except for the common UNION and JOIN functions, many of these developers have challenges with grouping sets and Window functions. They end up loading SQL data into memory before changing it to a given collection type and finally executing unnecessary maths on the collection. The whole idea is not necessary. By using features like OLAP, you just need to leave the database carry out the processing. The result is what you need to load in Java memory.

By choosing to move OLAP to the database, you will find it simple writing appropriately in SQL compared to Java. In addition, you will achieve faster database than your algorithm and hence improved performance. Just in case you were forgetting, there wouldn’t be need to transfer tonnes of records over the wire. Next time before you think of implementing any data-related algorithm, ask yourself whether the database can perform that task for you, the easier way.

  1. Going for UNION as opposed to UNION ALL

In SQL, UNION ALL requires an additional keyword as compared to UNION. If only SQL could support UNION and UNION DISTINCT for allowing and removing duplicates respectively, things would be better. However, removal of duplicates is not necessary because it is barely used. For someone dealing with huge results containing several columns, it becomes even more difficult to use. As a Java developer, you need to think twice every time you punch UNION; maybe you wanted UNION ALL instead.

  1. Manually inserting Large Data Records

Java developers need to take advantage of batching. Do not stress yourself inserting huge data records one by one. That means you will have to create new PreparedStatement every time. That is hectic. If you have all records going on a single table, use one SQL statement for creating a batch INSERT statement. After inserting a given number of data records, you might be required to commit just to ensure the UNDO log remains narrow.

  1. Confusing Aggregate and Window Functions

Initially, before the entry of window functions, you could only use GROUP BY clause to aggregate SQL data. Then SQL 2003 ushered in window functions. It is now more popular and preferred by database users compared to aggregate functions. With window functions, you will achieve an easily readable SQL writing together with enhanced performance since RDBMS is more likely to optimise window functions with lots of ease. Instead of Java developers complicating issues with GROUP BY clause, they need to find out if the same can be achieved using window function.

  1. Choosing In-Memory Sorting for Sort Indirections

In SQL, the common ORDER BY clause supports various expressions like CASE statements. Such expressions can come handy for sort indirections. Many Java gurus go ahead to sort data in Java memory thinking that SQL sorting is too slow or incapable of handling the same. Try pushing the sorting into your database before thinking of sorting SQL data in memory.

  1. Paginating huge results using JDBC

Instead of paginating in memory, you can explore options like ROWNUM from Oracle. Alternatively, you paginate ordered results using OFFSET, TOP, LIMIT, and FETCH clauses, which are easily supported by multiple databases. It is faster this way unless you want to spend more time handling one task.

  1. Misunderstanding NULL

One of the largest mistakes a Java developer can commit is misunderstanding NULL while writing SQL. Well, NULL is also referred to as UNKNOWN; that should not confuse you. Many people find the idea of SQL NULL being mapped to Java null during binding of variables so confusing. They end up thinking that NULL (SQL) has similar functionalities as null (Java). That is far from truth and the earlier you know the better.

Any dba consultant will admit that training yourself on NULL is the only viable solution. Every time you are writing SQL, find out if NULL influences the result of the function. Most importantly, you need to verify the appropriateness of the predicate with respect to NULL.

 

  1. Failing to use MERGE Statement

It might not really be seen as a mistake. In most cases, it results from ignorance of the powerful capabilities of MERGE statement. For databases like SQL server, having huge extensions of SQL, this statement is very important. Make your work easier.

  1. Joining Data in Java Memory

It is common to find early users of SQL finding it difficult expressing JOINS in this language. Some blatantly think it is slow so they try to avoid it. It can be slow but in rare cases. Otherwise, HASH JOIN and MERGE JOIN operations should prove faster with correct indexes and constraints. At the end of the day, what matters most is appropriate Meta Data. It is not necessary loading two tables from different queries into maps then combining them in Java memory. It is tedious, for no good reason. Before doing a selection from different tables in multiple steps, you need to find out the possibility of expressing the query in a single statement.

  1. Inappropriate Use of Distinct or UNION

It is possible for one to get confused where heavy joining is involved. You can end up not adding necessary predicts like JOIN and ON clauses. Consequently, you will have duplicate records. At this point, some developers choose to use DISTINCT in removing these duplicates. This is wrong. You should consider reviewing your JOIN predicates anytime there is a case of unwanted duplicate.

Conclusion

Java developers do not have to find it difficult learning and taking advantage of SQL. They just need to be keen on slight errors and always remember that the two are separate entities so there is no room for borrowing ideas from Java.

Bio

Sujain Thomas is a renowned content developer based in South Carolina. She currently works for a private dba consultant firm. She is a great fan of football matches. She spends most of her free time watch sports related TV programs. You can visit her blog for related posts.

 

 

 

 

Youth Ki Awaaz is an open platform where anybody can publish. This post does not necessarily represent the platform's views and opinions.