Create a popup menu

In the case you want to display a set of options once you have clicked a component, you may use a popup menu to show the options.

Lets say you have a JButton, when it is clicked, a menu will popup.

JButton popMenuButton = new JButton("Your Choices');
popMenuButton.addActionListener(new PopupListener());

JPopupMenu popMenu = new JPopupMenu();
JMenuItem choice1 = new JMenuItem();
JMenuItem choice2 = new JMenuItem();

private class PopupListener implements ActionListener {
      public void actionPerformed(ActionEvent e) {
            buildPopupMenu();
      }
}

private void buildPopupMenu() {
      popMenu.setLabel("Options");
   
      choice1.setText("Choice 1");
      choice1.setActionCommand("Choice 1");
      choice1.setMnemonic(1);
      choice1.addActionListener(new ChoiceListener());
      popMenu.add(choice1);

      choice2.setText("Choice 2");
      choice2.setActionCommand("Choice 2");
      choice2.setMnemonic(2);
      choice2.addActionListener(new ChoiceListener());
      popMenu.add(choice2);

      popMenu.setRequestFocusEnabled(true);
      popMenu.setBorderPainted(true);
      popMenu.show(popMenuButton, 5, -60);
}

private class ChoiceListener implements ActionListener {
      public void actionPerformed(ActionEvent e) {
            popMenu.setVisible(false);
            Object obj = e.getSource();
            if (obj == choice1) {
                  //Display result of choice 1
           } else if (obj == choice2) {
                 //Display result of choice 2
           }
      }
}

---------------------------------------------------------------------------------------------------

                        

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

            

SQL: WITH clause (Common Table Expressions)

The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. Not every database supports WITH clause. However, PostgreSQL dose support it. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query. The name assigned to the sub-query is treated as though it is an inline view or table. The SQL WITH clause is basically a drop-in replacement to the normal sub-query.

WITH clause provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

Syntax For The SQL WITH Clause

The following is the syntax of the SQL WITH clause when using a single sub-query alias.

WITH <alias_name> AS (sql_subquery_statement)SELECT column_list FROM <alias_name> [, tablename][WHERE <join_condition>]

When using multiple subquery aliases, the sysntax is as follows.

WITH <alias_name_A> AS (sql_subquery_statement),
<alias_name_B> AS(sql_subquery_statement_from_alias_name_A
or sql_subquery_statement )SELECT <column_list>FROM <alias_name_A>, <alias_name_B> [, tablename][WHERE <join_condition>]

The only advantage of using a CTE over a sub select is that you can actually name the sub query.

Following is an example.

WITH regional_sales AS (

      SELECT region, SUM(amount) AS total_sales
      FROM orders
      GROUP BY region), 
top_regions AS (
      SELECT region
      FROM regional_sales
      WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))

SELECT region,
      product,
      SUM(quantity) AS product_units,
      SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

WITH Clause Is Most Useful For Recursion

When WITH clause is used to recursively perform a task, it can accomplish things not otherwise possible in standard SQL. In this case, a WITH query can refer to its own output. An example is this query to sum the integers from 1 through 100 shown below.

WITH  numbs(n) AS ( //recursively put all qualified n into numbs
      SELECT 1 from dual    //initialize the first value
      UNION ALL
      SELECT n+1 FROM numbs  //select from itself
      WHERE n < 100            //condition to stop
)
SELECT sum(n) FROM numbs;

---------------------------------------------------------------------------------------------------

                        

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

SQL: Keep finding the next missing value from a column of sequential data without inserting the previous/current missing value

You have a FRUIT table in your database. The data of the table look like below.

fruitKey      name                description
----------      -----------------  -------------------------------------
1                 Apple               Sells best in summer
5                 FruitA              Need to be frozen
6                 FruitB              Great
10               FruitC               Sweet
11               FruitD               For children
12               FruitF                Blue color
19               FruitG                Need to buy more

Now, you have more fruits coming in and you want to put them in your FRUIT table. You want the new records to fill in the fruitKey gaps instead of incrementing the max value of it. The following SQL will get the minimum missing fruitKey which is 2.

SELECT MIN(f1.fruitKey + 1) AS fruitKey
FROM FRUIT f1
Where NOT EXISTS (SELECT * FROM FRUIT f2
                                 WHERE f2.fruitKey = f1.fuitKey + 1;

However, you are required to summit all the insert statements at once for the system to process. The above SQL will not return the next missing fruitKey which is 3, instead, it will always return 2. The SQL below will return all the gaps in the fruitKey.

SELECT (f1.fruitKey + 1) AS fruitKey_low,
       (select min(f1.fruitKey) - 1 from FRUIT f3
            where f3.fruitKey > f1.fruitKey) AS fruitKey_high
FROM FRUIT f1
WHERE NOT EXISTS (SELECT * FROM FRUIT f2
                                 WHERE f2.fruitKey = f1.fuitKey + 1

The result looks like this.

fruitKey_low      fruitKey_hight
--------------       -----------------
2                       4
7                       9
13                     18

If you are creating the insert statements manually, you can pick the values from the above result as your fruitKeys. But most likely, you are going to write a program to create the insert statements and submit them to the database automatically. In this case, you need the SQL below to get the next missing fruitKey.

WITH G AS
(
SELECT (f1.fruitKey + 1) AS fruitKey_low,
       (select min(f3.fruitKey) - 1 from FRUIT f3
            where f3.fruitKey > f1.fruitKey) AS fruitKey_high
FROM FRUIT f1
WHERE NOT EXISTS (SELECT * FROM FRUIT f2
                                 WHERE f2.fruitKey = f1.fuitKey + 1
),
M AS
(
SELECT CASE
      WHEN (fruitKey_high - fruitKey_low >= 0) and (fruitKey_low > <current missing key>)                  THEN fruitKey_low
      WHEN (fruitKey_high - fruitKey_low > 0) and (fruitKey_low <= <current missing key>)
             and (fruitKey_high > <current missing key>)
      THEN <current missing key> + 1
      ELSE (select max(fruitKey)+1 from FRUIT)
      END AS fruitKey
FROM G
)
SELECT nvl (MIN(fruitKey), (select max(fruitKey)+1 from FRUIT)) AS fruitKey FROM M;

---------------------------------------------------------------------------------------------------

                        

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

Solved - PostgreSQL: add days or hours to date

PostgreSQL does not have the DateAdd function. It uses the INTERVAL type to add days and hours to a date.

1. Add days to a date.

      select <the date> + cast(<the days> || ' days' as INTERVAL) AS nDate from <your table>;
      Or
      select <the date> + (<the days> * interval '1 day') AS nDate from <your table>;

2. Add hours to a date

      select <the date> + cast(<the hours> || ' hours' as INTERVAL) AS nDate from <your table>;
      Or
      select <the date> + (<the hours> * interval '1 hour') AS nDate from <your table>;

---------------------------------------------------------------------------------------------------

                        

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

   

SQL: remove all duplicate rows

Delete from <table name> a
where a.rowid > ANY (select b.rowid
                                  from <table name> b
                                  //should include all columns required to be unique
                                   where b.<column name 1> = a.<column name 1>
                                             and b.<column name 2> = a.<column name 2>
                                             and b.<column name 3> = a.<column name 3>

                                             . . . . . .

                                    );

OR

Delete from <table name> a
where exists (select *
                    from <table name> b
                    //should include all columns required to be unique
                     where b.<column name 1> = a.<column name 1>
                      and b.<column name 2> = a.<column name 2>
                      and b.<column name 3> = a.<column name 3>

                        . . . . . .

                       and a.rowid > b.rowid);

---------------------------------------------------------------------------------------------------

                        

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.