Handling null values in Oracle Select Statements

Oracle Sql Select statement do not evaluate null values. Null values should be handled in the where condition of sql statements for all nullable columns. Oracle nvl function provides best way to handle null values.

create table emp(
emp_id number,
emp_name varchar2(20),
emp_age number);

alter table emp add constraint emp_pk primary key (emp_id);

insert into emp values(1,'mahesh',null);

insert into emp values(2,null,20);

insert into emp values(3,null,23);

insert into emp values(4,null,null);


An employee table contain column named emp_name that can have null values. When emp_name column is compared with "like" operator the rows with null values for emp_name columns are not evaluated by the select statement.

select * from emp where emp_name like '%';

EMP_ID EMP_NAME EMP_AGE
------ -------------------- ----------
1 mahesh

To evaluate column that can have null values, nvl function can be handled for emp_name column for null values as shown below.

select * from emp
where nvl(emp_name,'!') like '%';

EMP_ID EMP_NAME EMP_AGE
------ -------------------- ----------
1 mahesh
2 20
3 23
4

How To Make Dynamic Sql Query Using Mybatis

Sql queries can be dynamic using Mybatis, may help reusing sql queries for more than one occasion. As per the context, Sql "where" conditions can be appended and executed for resultsaccordingly. Here are few samples that demonstrate the use of test conditons in Sql queries.

Sample 1 demonstrate the simple test of condition when S_EMP_NAME parameter is not null

#Sample 1
SELECT A.EMP_ID,A.EMP_NAME,B.DEPT_ID B
FROM EMP A,DEPT B
WHERE A.DEPT_ID=B.DEPT_ID
AND A.EMP_ID=#{EMP_ID}
<if test="S_EMP_NAME !=null ">
AND A.EMP_NAME LIKE #{S_EMP_NAME} || '%'
</if>

Sample 2 demonstrate the build of total "where" condition. The previxOverrides variable is "AND | OR' is generated dynamically to make the statement complete.

#Sample 2
SELECT A.EMP_ID,A.EMP_NAME
FROM EMP A
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="S_EMP_ID !=null ">
A.EMP_ID=#{S_EMP_ID}
</if>
<if test="S_EMP_NAME !=null ">
A.EMP_NAME LIKE #{S_EMP_NAME}||'%'
</if>
</trim>

Sample 3 demonstrate build of like search for EMP_NAME only when S_EMP_ID parameter is null

#Sample 3
SELECT A.EMP_ID,A.EMP_NAME
FROM EMP A
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="S_EMP_ID !=null ">
A.EMP_ID=#{S_EMP_ID}
</if>
<if test="S_EMP_ID ==null ">
<if test="S_EMP_NAME !=null ">
A.EMP_NAME LIKE #{S_EMP_NAME}||'%'
</if>
</if>
</trim>

How To Return Oracle Cursor Using Mybatis in Oracle stored procedure call

In some cases it is necessary to get the set of data in the same procedure call. Sample example is demonstrated below.

Employee class to hold the single employee.

public class EMPLOYEE {
private Integer EMP_ID;
private String EMP_NAME;

public void setEMP_ID(Integer eMP_ID) {
EMP_ID = eMP_ID;
}
public Integer getEMP_ID() {
return EMP_ID;
}
public void setEMP_NAME(Integer eMP_NAME) {
EMP_NAME = eMP_NAME;
}
public String getEMP_NAME() {
return EMP_NAME;
}
}

Code To Access Cursor in java

Map map = new HashMap();
map.put("EMP_ID",11111);
myConnection.insert("cursor_exec", map);
List emp = (List) map.get("EMP");
for (int i = 0; i < emp.size(); i++) {
System.out.println(emp.get(i).getEMP_ID() || emp.get(i).getEMP_NAME() );
}

Mybatis Mapping for returning Oracle Cursor during procedure call

<resultMap id="getCursor" type="office.main.EMPLOYEE">

</resultMap>
<select id="cursor_exec" statementType="CALLABLE" parameterType="java.util.Map">
{call
proc_name(#{EMP_ID,jdbcType=NUMERIC},#{EMP,jdbcType=CURSOR,mode=OUT,javaType=java.sql.ResultSet,resultMap=getCursor})}
</select>