5 common mistakes Java Developers make when writing SQL

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:-

orderby1

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

rownum_orderby_wrong

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 

rownum_orderby_correct

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.

merge_statement

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

 

 

 

Advertisements

Rownum and order by in oracle

ROWNUM

ROWNUM is a psuedocolumn to return the row number of the row in a table. The order will be based on how oracle selects a row from table.

rownum1

Examples with rownum

rownum2

rownum3

Note that rownum will be decided by Oracle itself ( Oracle will assign a number based on how it retrieves from storage/memory)

Order by

Order by is a sql clause which used to order a selected column

Example :-

orderby1

Order by and Rownum together

Why we should use rownum and Order by clause together ? Many experienced developers ( Mainly java developers ) think they can write rownum and order by in same where clause to get the top N query.

For example – To find lowest salary employee developers write like below

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

see the result

rownum_orderby_wrong.PNG

Result is wrong.  It should be salary 7500 ( Empno – 14 )

Why ? Oracle applies the ROWNUM first and then applies the order by clause.

See the correct query below

rownum_orderby_correct

So always apply the order by and in next level apply the rownum.

To find a lowest salary employee :-

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

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

Few more tips about rownum

  1. Do not write ROWNUM = 0 ( There is no record having ROWNUM = 0)
  2. Do not write ROWNUM <= 1 instead write ROWNUM = 1
  3. In general Oracle assigns ROWNUM while retrieving the records, it is not based on Primary key column
  4. Do not write ROWNUM > 1, which will no yield any results
  5. ROWNUM and ROW_NUMBER() is not same.

Please comment.

 

 

TO_DATE function Oracle

TO_DATE function in Oracle is the most useful as well as ‘annoying’ function.

TO_DATE function converts string into date data types.

Eg :-

select to_date(‘2016/11/13′,’yyyy/mm/dd’) from dual;

returns  2016/11/13

Note that Oracle converts string ‘2016/11/13’into Date ‘2016/11/13’. Now this value can be inserted into a table having a column date data type.

Note :- Before doing any testing on date columns make sure the default date format settings

For example in my sqldeveloper tool, go to Tools–> Preferences –> Database –> NLS

image7
SQLDEVELOPER date format

Below sql should have returned 2016/11/13, but shows 13-NOV-16 which is in sqldeveloper nls date format. This is the most common mistake people make.

 

image8

Alternatively you can use below sql also to change the date format.

alter session set nls_date_format = ‘dd-mon-yyyy’;

The above SQL can be used from SQLPLUS also.

If you do not want to change the nls settings, you can use TO_CHAR function also in required format, see below example.

select to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’yyyy/mm/dd’) from dual;

(TO_CHAR and TO_DATE)

image9

Below are the most used formats with TO_DATE

Format Description Example Value
AD AD or BC to_char(to_date(‘2016/11/13 AD’,’yyyy/mm/dd AD’),’AD’) AD
BC AD or BC to_char(to_date(‘2016/11/13 BC’,’yyyy/mm/dd AD’),’AD’) BC
D Day of week (1 7) to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’D’) 1
DAY Name of day to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DAY’) SUNDAY
DD Day of month (1 31) to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DD’) 13
DDD Day of year (1 366) to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DDD’) 318
DY Abbr name of day to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’DY’) SUN
HH Hour of day (1 12) to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’HH’) ‘01’
HH12 Hour of day (1 12) to_char(to_date(‘2016/11/13 04:12:35 PM’,’yyyy/mm/dd hh:mi:ss PM’),’HH12′) ‘04’
HH24 Hour of day (0 23) to_char(to_date(‘2016/11/13 04:12:35 PM’,’yyyy/mm/dd hh:mi:ss PM’),’HH24′) 16
IW Week of year to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’IW’) 45
J Julian day to_char(to_date(‘2016/11/13′,’yyyy/mm/dd’),’J’) 2457706
MI Minute (0 59) to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MI’) 12
MM Month (01 etc) to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MM’) 11
MON Abbr month to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MON’) NOV
MONTH Name of month to_char(to_date(‘2016/11/13 01:12:35′,’yyyy/mm/dd hh:mi:ss’),’MONTH’) NOVEMBER
AM AM or PM to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’AM’) AM
PM AM or PM to_char(to_date(‘2016/11/13 01:12:35 PM’,’yyyy/mm/dd hh:mi:ss AM’),’AM’) PM
Q Quarter of year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’Q’) 4
RRRR Round year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’RRRR’) 2016
SS Second (0 59) to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’SS’) 35
WW Week of year (1 53) to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’WW’) 46
W Week of month (1 5) to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’W’) 2
YYYY Last 4 digit(s) of year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’YYYY’) 2016
YY Last 2 digit(s) of year to_char(to_date(‘2016/11/13 01:12:35 AM’,’yyyy/mm/dd hh:mi:ss AM’),’YY’) ‘16’

Always check below before you start using TO_DATE function

  1. NLS_DATE_FORMAT ( SELECT sys_context (‘userenv’,’NLS_DATE_FORMAT’) FROM DUAL )

or

2. use TO_CHAR for formating

Also read TO_CHAR Function