Wednesday, December 28, 2016

java: difference between == and equals() with examples

Both "==" and "equals()" are used to check if two things equal to each other.

The "==" is an operator which can be used to compare primitives as well as Objects. It checks if the two items reference to the same memory location. Since it is an operator, it cannot be overridden.

The "equals()" is a method in the Object class. The "equals()" method in the Object class works the same as the "==" operator. However, since it is a method, it can be overridden to compare based on object components or business logic. As in the case of ArrayList, the equals method compares if the two ArrayLists have the same sequence of objects.

Below are some examples.

1. The String class

The String class is peculiar for it can be initiated with a string constant or a new String instance. Two String constants with the same sequence of characters are always equal regardless of using "==" or "equals()". However, it is a good practice to use equals when comparing two Strings.

String strA = "Welcome!";
String strB = "Wel" + "come!";
String strC = new String(strA);
String strD = new String("Welcome!");

System.out.println("strA == strB? " + (strA == strB));
System.out.println("strA.equals(strB)? " + (strA.equals(strB)));
System.out.println("strA == strC? " + (strA == strC));
System.out.println("strA.equals(strC)? " + (strA.equals(strC)));
System.out.println("strC == strD? " + (strC == strD));
System.out.println("strC.equals(strD)? " + (strC.equals(strD)));

The output:
strA == strB? true
strA.equals(strB)? true
strA == strC? false
strA.equals(strC)? true
strC == strD? false
strC.equals(strD)? true

2. Without overriding the equals() method

public class ObjA {
private ArrayList<String> array;
private int numb;

public ObjA() {
array = new ArrayList<>();
numb = 3;
}

public static void main(String[] args) {
ObjA theFirst = new ObjA();
ObjA theSecond = new ObjA();
ObjA theThird = theFirst;

System.out.println("Is theFirst == theSecond? " + (theFirst == theSecond));
System.out.println("Is theFirst.equals(theSecond)? " + (theFirst.equals(theSecond)));
System.out.println("Is theFirst == theThird? " + (theFirst == theThird));
System.out.println("Is theFirst.equals(theThird)? " + (theFirst.equals(theThird)));
System.out.println("Is theSecond == theThird? " + (theSecond == theThird));
System.out.println("Is theSecond.equals(theThird)? " + (theSecond.equals(theThird)));
}
}

The Outpput:
Is theFirst == theSecond? false
Is theFirst.equals(theSecond)? false
Is theFirst == theThird? true
Is theFirst.equals(theThird)? true
Is theSecond == theThird? false
Is theSecond.equals(theThird)? false

3. With overriding the equals() method

public class ObjB {
private ArrayList<String> array;
private int numb;

public ObjB() {
array = new ArrayList<>();
numb = 3;
}

public ArrayList getArray() {
return array;
}

public int getNumber() {
return numb;
}

@Override
public boolean equals(Object obj) {
if (obj instancdof ObjB){
ObjB ob = (ObjB)obj;
if (getArray().equals(ob.getArray()) && getNumber() == ob.getNumber()){
return true;
}
}
return false;
}

public static void main(String[] args) {
ObjB theFirst = new ObjB();
ObjB theSecond = new ObjB();
ObjB theThird = theFirst;

System.out.println("Is theFirst == theSecond? " + (theFirst == theSecond));
System.out.println("Is theFirst.equals(theSecond)? " + (theFirst.equals(theSecond)));
System.out.println("Is theFirst == theThird? " + (theFirst == theThird));
System.out.println("Is theFirst.equals(theThird)? " + (theFirst.equals(theThird)));
System.out.println("Is theSecond == theThird? " + (theSecond == theThird));
System.out.println("Is theSecond.equals(theThird)? " + (theSecond.equals(theThird)));
}
}

The Outpput:
Is theFirst == theSecond? false
Is theFirst.equals(theSecond)? true
Is theFirst == theThird? true
Is theFirst.equals(theThird)? true
Is theSecond == theThird? false
Is theSecond.equals(theThird)? true

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

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.

Friday, December 23, 2016

NetBeans: warning: [unchecked] unchecked call to setModel(ComboBoxModel) as a member of the raw type

After building a project in NetBeans, the following messages are displayed in the Output area:

warning: [unchecked] unchecked call to setModel(ComboBoxModel<E>) as a member of the raw type JComboBox
this.typeCombo.setModel(new DefaultComboBoxModel(type));
where E is a type-variable:
E extends Object declared in class JComboBox

This is because, since Java 7, JComoBox has a generic type for its components.

If you are displaying String in the combo box, you may declare it as below.

JComboBox<String> = new JComboBox<>();

If you dragged the Combo Box from the Palette to the Design area in NetBeans to build your display, click on the combo box in Design. In general, the Properties will display at the right of the screen. Select the Code tab, and enter <String> in Type Parameters.

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

Watch the blessing and loving online channel: SupremeMasterTV live

Friday, December 16, 2016

SQL: UPSERT syntax and examples

UPSERT is part of the data manipulation language (DML) that allows one to use a single statement to atomically either INSERT a row, or on the basis of the row already existing, UPDATE that row instead, while safely giving little to no further thought to concurrency. The "essential property of UPSERT" is that one of those two outcomes must be guaranteed, regardless of concurrent activity.

PostgreSQL:

Starting PostgreSQL 9.5, UPSERT becomes part of its DML.

Use the ON CONFLICT clause:

For example, you have a store table and you want to set the store name to 'Unforgettable Fruits' where the store id is 559. You can use the following SQL to guarantee the result regardless if store 559 already exists.

INSERT INTO store (id, name) values (559, 'Unforgettable Fruits')
ON CONFLICT (id)
DO UPDATE SET name = 'Unforgettable Fruits';

Use the WITH clause:

Syntax:

WITH upsert AS
(UPDATE <table name>
SET <column name> = <value>
WHERE <condition> RETURNING *)
INSERT into <table name> (<columns separated by comma>)
SELECT <values separated by comma>
WHERE not exists (SELECT * from upsert);

With the above store example:

WITH upsert AS
(update store set name = 'Unforgettable Fruits' where id=559 returning *) insert into store (id, name) select 559, 'Unforgettable Fruits' where not exists (select * from upsert);

Oracle:

The above SQL starting with 'WITH upsert AS' does not work in Oracle. However Oracle can use the MERGE clause to do the same job. MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard.

Syntax:

MERGE into <table name>
USING
<source table/view/result of sub-query>
ON
<match condition>
WHEN MATCHED THEN
<update clause>
<delete clause>
WHEN NOT MATCHED THEN
<insert clause>

Using the above store example.

merge into store using (select id, name from store_temp b) on (a.id = b.id) when matched then update set a.name = b.name when not matched then insert (a.id, a.name) values (b.id, b.name);

The MERGE clause is also supported in DB2 and MS SQL.

MySQL:

The ON DUPLICATE KEY UPDATE clause: Checks if a primary key or unique index is violated by an insert, it will then do an update.

Syntax:

INSERT into <table name> (<columns separated by comma>) values (<values separated by comma>)
ON DUPLICATE KEY <update clause>

Using the above store example, which has the id as the primary key:

INSERT into store (id, name) values (559, 'Unforgettable Fruits')
ON DUPLICATE KEY UPDATE name='Unforgettable Fruits';

Of course, you can always do a select first to check if the record exists, then decide if you need to update or insert. You can also do an update first, if it returns zero, then insert.

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

Watch the blessing and loving online channel: SupremeMasterTV live

Syntax:

Wednesday, November 30, 2016

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

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

Tuesday, November 22, 2016

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;

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

Thursday, November 17, 2016

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;

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

Friday, November 11, 2016

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

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

Wednesday, November 2, 2016

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);

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

Friday, October 28, 2016

Solved - java.sql.SQLException: ORA-02396: exceeded maximum idle time, please connect again

This kind of error is usually caused by the IDLE_TIME limit set to database users.

SQL> select * from USER_RESOURCE_LIMITS;

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME 60
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED

9 rows selected.

Set the IDLE_TIME to UNLIMITED shall fix this problem if the user does not have a profile. Note: this will set the default idle_time for all users to UNLIMITED.

Very often, the IDLE_TIME limit for a user is set in a profile assigned to the user. To change the IDLE_TIME in a user's profile, do the following.

1. Find the user's profile

SQL> SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

USERNAME PROFILE ACCOUNT_STATUS
------------------------------------- ---------------------------------- ---------------------------------
ORAUSER1 DEFAULT OPEN

ADMIN1 ADMIN_USER OPEN

2. View the profile

SQL> SELECT * FROM DBA_PROFILES where profile = 'ADMIN_USER' and RESOURCE_NAME='IDLE_TIME';

PROFILE RESOURCE_NAME RESOURCE LIMIT COM
-------------------------------------------------------------------------------------------------------------
ADMIN_USER IDLE_TIME KERNEL 60 NO

3. Change the LIMIT of IDLE_TIME

SQL> Alter profile ADMIN_USER limit IDLE_TIME UNLIMITED;

or

SQL> Update DBA_PROFILES set LIMIT='UNLIMITED' where profile = 'ADMIN_USER' and RESOURCE_NAME='IDLE_TIME';

4. Restart Database

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

Watch the blessing and loving online channel: SupremeMasterTV live

Tuesday, October 25, 2016

make: pg_config: Command not found

To solve this problem, add the bin directory containing the pg_config to your PATH. For example, add /usr/pgsql-9.5/bin to your environmental variable PATH.

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

Watch the blessing and loving online channel: SupremeMasterTV live

Thursday, October 6, 2016

postgresql alter table hangs

When the SQL, "Alter table Fruit add Column Location VarChar(30))", is executed at the psql terminal, it hangs.

It turns out that there is a lock on a table. The SQL below helps find all the locks in your database.

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

Then use the SQL below to kill the blocking process.

select pg_terminate_backend(<blocking_pid>);

Reference:

Lock Monitoring

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

Wednesday, October 5, 2016

crystal reports group sum and total sum

It is very often that a report displays data grouped by some category, displays a group summary at the end of each group and a total summary at the end of the report.

Following is about how to create the formula fields to accumulate the counts and display them.

If it is a simple account such as the sum or count of a single field, you can insert a summary by

1. Click the Insert on the top menu and select Summary.
2. In the Insert Summary window, choose the field you would like to use, the type of calculation you would like to perform on this field, and the location you want this summary to be on your report.
3. Click OK to close the window.
4. Find the summary on your report, right click on it and select Format Field.
5. Click each tab on the popup Format Editor window to specify the appearance of it.

However, if you want to create a complicated counter or summary that involve more than one field or complex logic, you may following the guide below to create it.

1. Open the Formula Workshop. From top menu, click Report, then select Formula Workshop
2. Create a reset formula, which will set the count to zero when a new group starts to display. Right click on Formula Fields and select new, enter the formula name, and click OK. Enter the following into the lower-right pane.

Shared Numbervar recordCount;
recordCount := 0;

For example you want to count on the records which have been created or updated, you can set it like this.

  Shared Numbervar countInserted;
Shared Numbervar countUpdated;
countInserted:=0;
countUpdated :=0;

Place the formula in group header.

3. Create the increment formula.

In general, it is like this.

Shared Numbervar recordCount;
recordCount := recordCount + 1;

As in the above example, it will look like this.

  Shared Numbervar countInserted;
  Shared Numbervar countUpdated;

if(not isModified({<createDate>}, {<modifyDate>})) then
countInserted:=countInserted+1;
else
  countUpdated:=countUpdated+1;

countInserted;
countUpdated;

Add the formula to the detail section.

4. Create the display formula(s).

In general

Shared Numbervar recordCount;
Shared Numbervar totalRecordCount;
totalRecordCount := totalRecordCount + recordCount;
recordCount;

As in the example, you need to created two display formulas, one for the newly created records and one for the updated records.

Add the formula(s) to the group footer.

5. Create the display total formula(s).

In general

Shared Numbervar totalRecordCount;
  totalRecordCount;

As in the example, create two display total formulas, one for the created and one for the updated records.

Add the formula(s) to the report footer.

Reference:

1. How to count number of grouped rows in the Crystal Report

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

Wednesday, September 28, 2016

Remote psql access to postgresql database

If you would like to access a postgresql database installed on a remote machine using psql. Follow these steps to enable it.

1. Modify the /var/lib/pgsql/9.5/data/pg_hba.conf file.

Change the IPv4 and IPv6 local connections to: host all all all trust

2. Modify the /var/lib/pgsql/9.5/data/postgresql.conf file.

Uncomment and change listen_addresses = 'localhost' to listen_addresses = '*'.

3. Add a rule to the iptables if the port is not open. In most cases you do not need to do this.

iptables -A INPUT -s 0/0 -p tcp --dport <your port> -j ACCEPT

4. On a remote machine.

psql -U postgres -h <database host IP address> -p <database port>

If you don't want to type the IP address each time, you can set a PGHOST=<database host IP address> environmental variable to bypass it.

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