What is Listener?

Listener is something sitting there and wait for specified event happened, then “hijack” the event and run it’s own event.

Let’s say…

You want to initialize the database connection pool before the web application is start, is there a “main()” method in whole web application?

Solution

The “ServletContextListener” is what you want. It will make your code run before the web application is start.


ServletContextListener is a interface which contains two methods:

• public void contextInitialized(ServletContextEvent event)
• public void contextDestroyed(ServletContextEvent event) 

1) Create a class and implement the ServletContextListener interface

package com.mkyong;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
 
public class AppServletContextListener implements ServletContextListener{
 
 @Override
 public void contextDestroyed(ServletContextEvent arg0) {
  System.out.println("ServletContextListener destroyed");
 }
 
 @Override
 public void contextInitialized(ServletContextEvent arg0) {
  System.out.println("ServletContextListener started"); 
 }
}

2) Put it in deployment descriptor

<web-app ...>
 <listener>
  <listener-class>com.mkyong.AppServletContextListener</listener-class>
 </listener>
</web-app>

Oracle/PLSQL: Sequences (Autonumber)

---

In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

The syntax for a sequence is:

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

For example:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.

If you omit the MAXVALUE option, your sequence will automatically default to:

MAXVALUE 999999999999999999999999999

So you can simplify your CREATE SEQUENCE command as follows:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use nextval.

For example:

supplier_seq.nextval

This would retrieve the next value from supplier_seq. The nextval statement needs to be used in an SQL statement. For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Kraft Foods');

This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.

Frequently Asked Questions

---

One common question about sequences is:

Question: While creating a sequence, what does cache and nocache options mean? For example, you could create a sequence with a cache of 20 as follows:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

Or you could create the same sequence with the nocache option:

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

Answer: With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.

The downside of creating a sequence with a cache is that if a system failure occurs, all cached sequence values that have not be used, will be "lost". This results in a "gap" in the assigned sequence values. When the system comes back up, Oracle will cache new numbers from where it left off in the sequence, ignoring the so called "lost" sequence values.

Note: To recover the lost sequence values, you can always execute an ALTER SEQUENCE command to reset the counter to the correct value.

Nocache means that none of the sequence values are stored in memory. This option may sacrifice some performance, however, you should not encounter a gap in the assigned sequence values.

---

Question: How do we set the LASTVALUE value in an Oracle Sequence?

Answer: You can change the LASTVALUE for an Oracle sequence, by executing an ALTER SEQUENCE command.

For example, if the last value used by the Oracle sequence was 100 and you would like to reset the sequence to serve 225 as the next value. You would execute the following commands.

alter sequence seq_name
increment by 124;

select seq_name.nextval from dual;

alter sequence seq_name
increment by 1;

Now, the next value to be served by the sequence will be 225.

f you want to set it to a specific value, you can set the INCREMENT to a negative value and get the next value.

That is, if your sequence is at 500, you can set it to 100 via

ALTER SEQUENCE serial INCREMENT BY -400;
SELECT serial.NEXTVAL FROM foo;
ALTER SEQUENCE serial INCREMENT BY 1;

Frequently used SQL queries

Finding the nth highest salary of an employee.

It is very easy to find the highest salary as:-

--Highest Salary

select max(Emp_Sal) from Employee_Test

Now, if you are asked to find the 3rd highest salary, then the query is as:-

--3rd Highest Salary

select min(Emp_Sal) from Employee_Test where Emp_Sal in

(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)

To find the nth highest salary, replace the top 3 with top n (n being an integer 1,2,3 etc.)

--nth Highest Salary

select min(Emp_Sal) from Employee_Test where Emp_Sal in

(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)

Alternate Way:-

SELECT * FROM MYTABLE T1

WHERE (N =

(SELECT COUNT(DISTINCT (T2.MYCOLUMN))

FROM MYTABLE T2

WHERE T2.MYCOLUMN >= T1.MYCOLUMN))

Finding TOP X records from each group

There are three groups of pgm_main_category_id each with a value of 17 (group 17 has four records),18 (group 18 has three records) and 19 (group 19 has three records).
Now, if you want to select top 2 records from each group, the query is as follows:-

select pgm_main_category_id,pgm_sub_category_id,file_path from

(

select pgm_main_category_id,pgm_sub_category_id,file_path,

rank() over (partition by pgm_main_category_id order by pgm_sub_category_id asc) as rankid

from photo_test

) photo_test

where rankid < 3 -- replace 3 by any number 2,3 etc for top2 or top3.

order by pgm_main_category_id,pgm_sub_category_id

The result is as:-

pgm_main_category_id   pgm_sub_category_id    file_path

17                         15                     photo/bb1.jpg

17                         16                     photo/cricket1.jpg

18                         18                     photo/forest1.jpg

18                         19                     photo/tree1.jpg

19                         21                     photo/laptop1.jpg

19                         22     

Deleting duplicate rows from a table

A table with a primary key doesn’t contain duplicates. But if due to some reason, the keys have to be disabled or when importing data from other sources, duplicates come up in the table data, it is often needed to get rid of such duplicates.
This can be achieved in tow ways :-
(a) Using a temporary table.
(b) Without using a temporary table.

(a) Using a temporary or staging table

Step 1: Create a temporary table from the main table as:-

select top 0* into employee_test1_temp from employee_test1

Step2 : Insert the result of the GROUP BY query into the temporary table as:-

insert into employee_test1_temp

select Emp_ID,Emp_name,Emp_Sal

from employee_test1

group by Emp_ID,Emp_name,Emp_Sal

Step3: Truncate the original table as:-

truncate table employee_test1

Step4: Fill the original table with the rows of the temporary table as:-

insert into employee_test1

select * from employee_test1_temp

Now, the duplicate rows from the main table have been removed.

select * from employee_test1

gives the result as:-

Emp_ID  Emp_name   Emp_Sal

1       Anees      1000

2       Rick       1200

3       John       1100

4       Stephen    1300

5       Maria      1400

6       Tim        1150

(b) Without using a temporary table

;with T as

(

        select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank

        from employee_test1

)

delete

from T

where rank > 1

The result is as:-

Emp_ID  Emp_name   Emp_Sal

1       Anees      1000

2       Rick       1200

3       John       1100

4       Stephen    1300

5       Maria      1400

6       Tim        1150

  

Find DUPLICATE DATA

SELECT OBJECT_NAME NAME,

COUNT(OBJECT_NAME) MYCOUNT

FROM GENERALDB.JAVAOBJECTS

GROUP BY OBJECT_NAME

HAVING COUNT(OBJECT_NAME)>0