Java developers always have tough time understanding SQL implementation. SQL is straight forward, simple and easy to write. Compared to SQL, java is very complex. Java developers are highly obsessed with objects, they think every thing should be object oriented. Seen many java developers design/create tables after designing/creating the UI screen, actually it should be other way around.

Let us examine some mistakes they make while developing data-centric applications

1. Using ROWNUM and ORDER BY CLAUSE together 

Example:-

To find lowest salary employee, some java developers write like below

select * from emp where rownum = 1 order by salary asc ;  — wrong query

Surrely above query will attract logical bugs.

See the result

The result is wrong. Please use the correct query as given below

select * from (select * from emp order by salary asc) where rownum = 1 — correct query 

Best Practice :-  Always apply rownum after order the rows. Note that do not use order by and rownum in the same where clause, use in each level of queries.

2. Pagination in Java memory

Java developers do pagination in Java instead in database side. Load all data into memory and paginating is a time-consuming process especially for large data sets. So if possible apply pagination before it loads into Java.

Example :- Top 5 salaried employees

select * from
(
select empno,empname,salary,row_number() over ( order by salary desc ) rk from emp
)
where rk <= 5;

Example :- 3rd to 6th top salaried employees

select * from
(
select empno,empname,salary,row_number() over ( order by salary desc ) rk from emp
)
where rk between 3 and 6;

Best Practice :- If possible use pagination in database level itself

3. Scare to use database procedures or packages from Java

Many Java developers scare to use database procedures or packages for processing the business logic. Instead they use standalone queries either directly in java code or in xml files. This is inefficient and use more network round trips. Some believe that stored procedures downgrade the performance which is not true.

Best Practice :- To write data centric business logic in database procedures rather than exposing direct queries in Java.

4. Loading all data into java memory

Seen in many java applications loading huge data into the memory (java cache) and process whenever require. This negatively impact the performance and increase the overhead of the applications.

Best Practice :- Load data into memory only when require.

5.  Using separate INSERT and UPDATE instead of MERGE statement

Many java developers (who use Oracle as their database) does not know there is a MERGE SQL command which will combine INSERT and UPDATE into one single SQL

a) INSERT statement

insert into emp (empno,empname,salary,deptno) values(19,’JOHN’,7800,5);

b) UPDATE statement

update emp set empname = ‘JOHNSON’where empno  = 19;

c) MERGE statement

MERGE INTO emp e
USING (select 19 as empno,’JOHNSON’ as empname,7800 salary,5 as deptno from dual) a
ON (e.empno = a.empno)
WHEN MATCHED THEN
UPDATE SET e.empname = a.empname,e.salary = a.salary, e.deptno = a.deptno
WHEN NOT MATCHED THEN
INSERT (empno, empname,salary, deptno)
VALUES (a.empno, a.empname,a.salary, a.deptno);

see the result.

get and set method can be combined if properly coded

Best Practice :- Try to combine insert and update SQL statements using merge SQL statement (Only in Oracle )

Some more common mistakes…………

a) SQL NULL and Java NULL implementation is not same.

b) Unnecessary usage of multiple distinct clases in each union query

c) UNION and UNION ALL confusions

d) Write multiple SQL even though they can be written into one single SQL