Sequence Number in J2EE/Oracle web app

In a medium to large scale web app we have different modules with relevant tables.
In those tables we have sequence number which has to be filled in automatically. So to create this kind of auto generated numbers we use Sequences in Oracle.


public static final Class CLASS_NAME = PKSequenceUtility.class;
public static Integer populateApplnData(String tableName){
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
int seqNum = 0;
try {
con = DBUtility.getConnection();
// Constants.SCHEMA_NAME+".sq_"+tableName+".nextval from dual ==> "own_abc.sq_requesttable.nextval from dual"
String sql = "select "+Constants.SCHEMA_NAME+".sq_"+tableName+".nextval from dual";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
seqNum = rs.getInt(1);
}
} catch (SQLException sqle) {
sqle.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
finally
{
DBUtility.closeResultSet(rs);
DBUtility.closeStatement(ps);
DBUtility.releaseConnection(con);
}
return (seqNum);
}

NVL function

Tutorial Link

Space between names from database

TRIM(GU.FIRSTNAME), TRIM(GU.LASTNAME)

Result: abc,xyz

Requirement: I want both first and last name with space in-between.
Question: How to get this in SQL query.

TRIM(GU.FIRSTNAME << concatenation operator (2 straight lines) >> ' ' << concatenation operator >> GU.LASTNAME)

Tutorial Link

to_date, to_char, to_number

TO_DATE

Example:
TO_DATE(:SUBMISSIONDATE,'MM/DD/YYYY')

:SUBMISSIONDATE -- This is an input to the sql query which comes from java layer.

Tutorial Link

TO_CHAR

If we want to convert a date or number to string then we use to_char

Tutorial Link

How to compare 2 dates ?

TO_DATE(TO_CHAR(RD.SUBMISSIONDATE,'MM/DD/YYYY'),'MM/DD/YYYY') LIKE TO_DATE(:SUBMISSIONDATE,'MM/DD/YYYY'))

RD.SUBMISSIONDATE will return a timestamp (which includes hours, minutes and seconds). So first we convert a timestamp to a string date with the specified format and pass it to to_date as input, then we compare this date with other date using LIKE keyword.

For Greater than and Less than we can use regular symbols like < and >

AND TBU_NEW.LASTMODDATE > TBU_OLD.LASTMODDATE

We can also use equal to operator to compare dates

RD.MAXIMUMMATURITYDATE =RD_ADT.MAXIMUMMATURITYDATE


TO_NUMBER

Tutorial Link

ROWNUM Tutorial

Basic Tutorial:


ROWNUM for Pagination:

Designing the search query for 3 drop downs containing the same values

We have 2 search modes. Normal and Advanced. In Normal mode we have only one drop-down with 10 search criteria’s. In advanced mode we have 3 drop-downs with the same search criteria’s. We have this advanced mode to filter the search values.

Now that we have a basic understanding of our requirement we shall go ahead and design the implementation.

We have 3 drop-downs to select the search criteria and 3 textboxes to enter the values. Once the user selects the search criteria and enters values, search values are added to a HashMap with the search criteria as key and user condition as value. Before we put the values in a map we trim the user conditions to remove extra spaces like

criteriaSelectedMap.put(criteriaSelected1, valueSelected1.trim());

The keys in the map are nothing but the search criteria which are fetched from database, means those values are pre-defined and known to us before hand.

We then do a null check and get the value from the map using the pre-defined key and check for wild card search and finally store it in a bean as a property. We follow the same approach for all the 10 drop-down values and store the user search condition in one of the 10 property of the bean.

Wild card search: Wild card search is nothing but using * for searching in UI (JSP Pages). In the back end (database) all the * has to be replaced with % as we use this (%) symbol for searches.

Example:
Select name from employee where name like ‘s%’;
Here we are searching for names that start with s.

Select name from employee where name like ‘%s’;
Here we are searching for names that end with s.

RequestSearchBean objRequestSearchBean = new RequestSearchBean();

if (searchCriteriaMap != null && !searchCriteriaMap.isEmpty()) {
String countryName = searchCriteriaMap.get(SEARCH_COUNTRY);
}

if (countryName != null) {
objRequestSearchBean.setCountryName(
frameWildCardSearch (countryName));
}

public static String frameWildCardSearch(String criteria)
{
if (criteria == null) return criteria;
if (criteria.contains("*"))
{
criteria = criteria.replace('*','%');
}
return (criteria);
}

We will now design the JDBC part.

preparedStatement = objConnection.prepareStatement(sqlQuery);

ps.setString(1, objRequestSearchBean.getCountryName());
ps.setString(2, objRequestSearchBean.getCountryName());
ps.setString(3, objRequestSearchBean.getBankName());
ps.setString(4, objRequestSearchBean.getBankName());

Query part:

AND RD.REQUESTDETAILSKEY = BFC.REQUESTDETAILSKEY(+)
AND BFC.BANKFACILITYKEY = BFTY.BANKFACILITYKEY(+)
AND BFTY.FACILITYTYPEKEY = FT.FACILITYTYPEKEY(+)

AND (1 = NVL2 (:COUNTRYNAME, 0, 1) OR UPPER (CD1.DISPLAYNAME) LIKE UPPER(:COUNTRYNAME))
AND (1 = NVL2 (:BANKNAME, 0, 1) OR UPPER (BD.BANKNAME) LIKE UPPER(:BANKNAME))

This is how we pass all the 10 user search conditions to the query.

NVL2
=====

Syntax:
NVL2( string1, value_if_NOT_null, value_if_null )

Example:
select NVL2(supplier_city, 'Completed', 'n/a') from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the 'Completed'.

So NVL2 function will return either 1 or 0. If it returns 1 then it is understood that the input value is null and the condition of (1 = 1) is satisfied and the control won’t go the next part that’s the OR part.

Deleting multiple values using a single query

We have a table called userroles with 2 columns 1. userid and 2. userroletypekey

Userid is string field containing different user ids. userroletypekey is an int field with values from 1 to 8.

Now I want to retain only a single roletypekey for a particular user and delete the rest of userroletypekeys. Frame the query of this requirement.

delete from userroles where userid = 'NBK4HKA' and userroletypekey in (1,2,4,5,6,7,8)
commit

SQL Basic's - Tutorial

Tutorial Link - I

Tutorial Link - II