SubQuery Tutorial with example in SQL – Correlated vs Noncorrelated

SubQuery in SQL is a query inside another query. Some time to get a particular information from database you may need to fire two separate sql queries, subQuery is a way to combine or join them in single query. SQL query which is on inner part of main query is called inner query while outer part of main query is called outer query. for example in below sql query

SELECT name FROM City WHERE pincode IN (SELECT pincode FROM pin WHERE zone='west')

section not highlighted is OUTER query while section highlighted with grey is INNER query. In this SQL tutorial we will see both Correlated and non correlated sub-query and there examples, some differences between correlated and noncorrelated subqueries and finally subquery vs join which is classic debatable topic in SQL. By the way this SQL tutorial is next in series of SQL and database articles in Javarevisited like truncate vs delete and 10 examples of  SELECT queries. If you are new here then you may find those examples interesting.

SubQuery Rules in SQL
Like any other concept in SQL, subquery also has some rules and you can only embed one query inside another by following rules :
1. subquery can be used in insert statement.
2. subquery can be used in select statement as column.
3. subquery should always return either a scaler value if used with where clause or value from a column if used with IN or NOT IN clause.

Before going to understand non-correlated  and correlated subquery, let’s see the table and data which we are going to use in this example. Until you have an understanding of how table look like and what kind of data it stores its little difficult to understand queries. In this subquery example we will use two table Stock and Market. Stock holds different stocks and Market holds all stock exchanges in the world.

mysql> select * from stock;
+---------+-------------------------+--------------------+
| RIC     | COMPANY                 | LISTED_ON_EXCHANGE |
+---------+-------------------------+--------------------+
| 6758.T  | Sony                    | T                  |
| GOOG.O  | Google Inc              | O                  |
| GS.N    | Goldman Sachs Group Inc | N                  |
| INDIGO  | INDIGO Airlines         | NULL               |
| INFY.BO | InfoSys                 | BO                 |
| VOD.L   | Vodafone Group PLC      | L                  |
+---------+-------------------------+--------------------+
6 rows in set (0.00 sec)

mysql> select  from Market;
+------+-------------------------+---------------+
| RIC  | NAME                    | COUNTRY       |
+------+-------------------------+---------------+
| T    | Tokyo Stock Exchange    | Japan         |
| O    | NASDAQ                  | United States |
| N    | New York Stock Exchange | United States |
| BO   | Bombay Stock Exchange   | India         |
+------+-------------------------+---------------+
4 rows in set (0.00 sec)


Noncorrelated subquery in SQL
There are two kind of subquery in SQL one is called non-correlated and other is called correlated subquery. In non correlated subquery, inner query doesn't depend on outer query and can run as stand alone query.Subquery used along-with IN or NOT IN sql clause is good examples of Noncorrelated subquery in SQL. Let's a noncorrelated subquery example to understand it better.

NonCorrelated Subquery Example:
Difference between correlated and noncorrelated suqueryLet’s see the query  “Find all stocks from Japan”, If we analyze this query we know that stock names are stored in Stock table while Country name is stored in Market table, so we need to fire two query first to get RIC for Japanese market and than all stocks which is listed on that Market. we can combine these two queries into one sql query by using subquery as shown in below example:

mysql> SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE = (SELECT RIC FROM Market WHERE COUNTRY='Japan');
+---------+
| COMPANY |
+---------+
| Sony    |
+---------+
1 row IN SET (0.02 sec)

Here part which is inside bracket is called inner query or subquery. As you see in this example of subquery, inner query can run alone and its not depended on outer query and that's why its called NonCorrelated query.

NonCorrelated Subquery Example with IN Clause SQL
NonCorrelated subquery are used along-with IN and NOT IN clause. here is an example of subquery with IN clause in SQL.
SQL query: Find all stocks from United States and India

mysql> SELECT COMPANY FROM Stock WHERE LISTED_ON_EXCHANGE IN (SELECT RIC FROM Market WHERE COUNTRY='United States' OR COUNTRY= 'INDIA');
+-------------------------+
| COMPANY                 |
+-------------------------+
| Google Inc              |
| Goldman Sachs GROUP Inc |
| InfoSys                 |
+-------------------------+

When Subquery is used along-with IN or NOT IN Clause it returns result from one column instead of Scaler value.

Correlated SubQuery in SQL
Correlated subqueries are the one in which inner query or subquery reference outer query. Outer query needs to be executed before inner query. One of the most common example of correlated subquery is using keywords exits and not exits. An important point to note is that correlated subqueries are slower queries and one should avoid it as much as possible.

Example of Correlated Subquery in SQL
Here is an example of Correlated subquery “Return all markets which has at least one stock listed on it.”

mysql> SELECT m.NAME FROM Market m WHERE m.RIC = (SELECT s.LISTED_ON_EXCHANGE FROM Stock s WHERE s.LISTED_ON_EXCHANGE=m.RIC);

+-------------------------+
| NAME                    |
+-------------------------+
| Tokyo Stock Exchange    |
| NASDAQ                  |
| New York Stock Exchange |
| Bombay Stock Exchange   |
+-------------------------+
4 rows IN SET (0.00 sec)

Here inner query will execute for every Market as RIC will be changed for every market.

Difference between Correlated and NonCorrelated Subquery
Now we have seen correlated and noncorrelated subqueries and there example its much easier to understand difference between correlated vs noncorrelated queries. By the way this is also one of the popular sql interview question and its good to know few differences:

1.In case of correlated subquery inner query depends on outer query while in case of noncorrelated query inner query or subquery doesn't depends on outer query and run by its own.
2.In case of correlated subquery, outer query executed before inner query or subquery while in case of NonCorrelated subquery inner query executes before outer query.
3.Correlated Sub-queries are slower than non correlated subquery and should be avoided in favor of sql joins.
4.Common example of correlated subquery is using exits and not exists keyword while non correlated query mostly use IN or NOT IN keywords.

SubQuery vs Join in SQL
Any information which you retrieve from database using subquery can be retrieved by using different types os joins also. Since SQL is flexible and it provides different way of doing same thing. Some people find SQL Joins confusing and subquery specially noncorrelated more intuitive but in terms of performance SQL Joins are more efficient than subqueries.

Important points about SubQuery in DBMS
1.Almost whatever you want to do with subquery can also be done using join, it just matter of choice
subquery seems more intuitive to many user.
2.Subquery normally return an scaler value as result or result from one column if used along with
IN Clause.
3.You can use subqueries in four places: subquery as a column in select clause,
4.In case of correlated subquery outer query gets processed before inner query

Memory-mapped files in java tutorial with example . File I/O vs Memory-Mapped Files tutorial

The following tutorial covers about what is memory mapped files and  what are the advantages and drawbacks of using Memory-Mapped Files and also covers that how to map a  file into memory with example code.
 Any files can be accessed using  

1.  Simple  File I/O
 2.  Memory-Mapped Files

Some of  the drawbacks of Simple File I/O  (Usual  read() and write()) is as follows.

 When an  application requires to read data from outside  such as   file data  on disk (outside of virtual  / process  address space)  ,  system call to usual file I/O functions (e.g., read() and write() subroutines )  , copies the file data to intermediate buffer  . Then the data is transferred  to the physical file or the process .  This  Intermediate buffering is slow and expensive which reduces the I/O performance.
 The alternative mechanishm is Memory mapped files . Memory mapped files provide a mechanism  to map the  file data  into the area of Virtual Memory (process address space) .   This enables an application, including multiple processes, to read and write  the file data directly to the memory  without performing any explicit file read or write operations on the physical file .   When we access a  part of the file which is  not  in  memory, it will be automatically paged in  by  the  OS.  Subsequent reads / writes to / from that page are treated as ordinary memory accesses .  There is no separation between modifying the data and saving it to a disk.

Some of the benefits using  Memory mapped files ( Accessing a data directly from main memory )
 1. Eliminate intermediary buffering

2. Increases I/O performance

3. More than one processes can map the same file  i.e  pages in memory can be be shared among the processes which saves memory space and  supports inter-process communications

4. supports  lazy loading i.e   the process of allocating and loading pages in main memory  must be deferred as long as possible . The page is loaded into RAM when the page is actually needed .   You don't need to have memory for the entire file.  This helps  to read  a large file with small amount of RAM

5. File data can be accessed and modified with out having to execute any explicit I/O operations  on the file.

6. Reading / Writing  large files this is often  more efficient than invoking the usual read or write methods.

      Mapping a file into memory is implemented by a FileChannel object that  is packaged with java.nio   which is available from JDK 1.4 .   The map() method of a FileChannel object  maps to  a portion or all of channel’s file  into memory  and  returns a reference to a buffer of type MappedByteBuffer .

 Syntax for the map() method is
 public abstract MappedByteBuffer map(FileChannel.MapMode mode,      long position,  long size)       throws IOException
              - Maps a region of this channel's file directly into memory.   The map() method returns a MappedByteBuffer, which is a subclass of ByteBuffer. Methods of ByteBuffer can be used with MappedByteBuffer class . 
 A region of a file may be mapped into memory in one of the following three modes:
 1. MapMode.READ_ONLY - Can not modify the resulting buffer
 2. MapMode.READ_WRITE - Can change the resulting buffer
 3. MapMode.PRIVATE  -  creates a private copies of the modified portions of the buffer  which is  not  visible to other processes hat have mapped the same file.  Modification to the resulting buffer will not be reproduced to the file

The following line of code maps the first 1024 bytes of a file into memory in Read / write mode. 

MappedByteBuffer mbb = fc.map( FileChannel.MapMode.READ_WRITE, 0, 1024 );

To map the entire file specify the start file position as zero, and the length that is mapped as the length of the file.

MappedByteBuffer mbb= fc.map(READ_WRITE, 0L,fc.size()).load();

The buffer is created with the READ_WRITE mode, which permits the buffer to be accessed or modified and maps to the entire file.  The map() method returns a reference to the MappedByteBuffer object
 Drawbacks of  Memory mapped files

                 1.  Wastage of memory for small files .  In Memory mapped files , disk block is mapped  to a page   in memory . The size of the  page  is usually  4 KB  . To map a  file  with size of 9 KB , 3 pages are allocated with total size of 12 KB in Memory.  3 KB   memory is wasted.

                 2. When a requested page is not in the main memory , page fault occurs which reduces performance.

                3. Another limitation is Maping  of file contents in memory depends on available Virtual Address Space.   32 bit OS gets a set of virtual memory addresses from 0 to 4,294,967,295 (2*32-1 = 4 GB) .
 Now let us see the code example of Maemory Mapped Files . I have written two programs  to read a large log file  using the Standard File IO and Memory-mapped I/O  and you can run the two programs to get the time taken to read the given big log files by Standard File IO and Memory-mapped I/O . Obviously , Reading large file using Memory-mapped I/O  is faster than using Standard File IO.

Using Memory-mapped I/O
  

 

import java.io.FileInputStream;

import java.io.*;

import java.nio.MappedByteBuffer;

import java.nio.channels.FileChannel;

public class MemoryMappedIO1 {

    public static void main(String[] args) {

        long tm = 0;


        FileInputStream fis = null;


        try {




           fis = new FileInputStream("CBS.log");


            int len=1024;


            byte[] buf = new byte[len];



            tm = System.currentTimeMillis();




            FileChannel fc = fis.getChannel();




            MappedByteBuffer mbb = fc.map(FileChannel.MapMode.READ_ONLY, 0, fc.size());




            while (mbb.hasRemaining()) {



 if (len>mbb.remaining())




 mbb.get(buf,0,mbb.remaining());




 else




 mbb.get(buf,0,len);



//System.out.println(new String(buf));


            }




            System.out.printf("Time to read file TestLog.log: %d ms\n", (System.currentTimeMillis()-

tm));




        }




        catch (Exception ex) {




            ex.printStackTrace(System.err);




        }




        finally {




            if (fis != null) {




                try {




                    fis.close();




                }




                catch (Exception ex) {




                }




            }




        }




    }




}



Using Standard File IO:
   

 

import java.io.BufferedReader;




import java.io.*;




public class StandardBufferedIO1 {


    public static void main(String[] args) {


        long ts, te = 0;

         InputStream in = null;


        try {


    in=new FileInputStream("CBS.log");


            ts = System.currentTimeMillis();


      byte[] buf = new byte[1024];


    int len;

    while ((len = in.read(buf)) !=-1) {


 //System.out.println(new String(buf));


      //  out.write(buf, 0, len);


    }


              te=System.currentTimeMillis();


            System.out.printf("Time taken to read log file  %d ms\n", (te-ts));


        }




        catch (Exception e) {


            e.printStackTrace(System.err);


        }


        finally {




            if (in!= null) {




                try {


                  in.close();


                }


                catch (Exception e) {e.printStackTrace(System.err);    }


            }


        }


    }


}

Apache ofbiz tutorial : how to change ofbiz's administrator login password

To change administrator login password apply below step :

If you are the OFBiz administrator and you forget this user's password, you could be out of luck
since the administrator (the "admin" user login) has all the privileges necessary to perform
any OFBiz task, including logging in and assigning the admin user a new password.
If you find yourself in this situation, there is no easy way to retrieve this password such that
you may use it to log in. The only solution to this problem is to reset the password by directly
manipulating the correct entity in the database, or let OFBiz do that for you using the
following recipe.
Getting ready
Before following this recipe, ensure these steps are performed:
1. Navigate to the OFBiz install directory.
2. Open a command line or console window.
3. If you are running OFBiz, it is best to stop it at this time.

To reset the admin password, follow these steps:
1. From the OFBiz install directory, run the following command:
ant load-admin-user-login -DuserLoginId=admin
2. Restart OFBiz.
3. Log in to OFBiz as the admin user where the login user name is "admin" and the
password is "ofbiz".
4. When prompted to change the admin user's password, enter the new password or
"ofbiz" to maintain the existing value.

How to change JavaScript fuction call name submitFormDisableSubmits in Apache ofbiz minilang form onSubmit event

When i  am using minilang form it is always call submitFormDisableSubmits() javaScript function on onSubmit event 

we can change it by following way

apache use HtmlFormMacroLibrary.ftl where you can change the function name submitFormDisableSubmits according to you

How to use own JavaScript file in Apache ofbiz | implement own JavaScript function call in Apache ofbiz

In my Apache ofbiz project i have implemented own JavaScript in Minilang code

1. Add below line of code to give reference of JavaScript File in ogbiz Screen

           <actions>

                <set field="titleProperty" value="ProductFindFacilities"/>

                <set field="headerItem" value="facility"/>

                <set field="layoutSettings.javaScripts[+0]" value="/images/TestJavaScript.js" global="true"/>

            </actions>

2. by using Event and Action attribute as below to call javascript function from minilang form code

<field name="submitButton" title="Get Inventory Feed" widget-style="smallSubmit" event="onclick" action="javascript:return submitFormEnableButtonByNameTest(this,submitButton)">

                                                <submit button-type="submit" />

event  > it use for write javaScript event like onClick, onMouseOver etc

action > use for call funtion name of JavaScript

How to Reboot remote computers on a domain

Below Step to reboot remote computers
arrComputer = Array("Computer1", "Computer2", "Computer3")
For Each strComputer In arrComputer
Set objWMIService = GetObject ("winmgmts:{impersonationLevel=impersonate,(Shutdown)}\\" & strComputer & "\root\cimv2")
Set colOperatingSystems = objWMIService.ExecQuery ("Select * from Win32_OperatingSystem")

For Each objOperatingSystem in colOperatingSystems
objOperatingSystem.Reboot(2)
Next
Next

Builder Design pattern in Java ? GoF design pattern

Builder design pattern in Java is a creational pattern i.e. used to create objects, similar to factory method design pattern which is also creational design pattern. Before learning any design pattern I suggest find out the problem a particular design pattern solves. Its been well said necessity is mother on invention. learning design pattern without facing problem is not that effective, Instead if you have already faced issues than its much easier to understand design pattern and learn how its solve the issue. In this Java design pattern tutorial we will first see what problem Builder design pattern solves which will give some insight on when to use builder design pattern in Java, which is also a popular design pattern interview question and then we will see example of Builder design pattern and pros and cons of using Builder pattern in Java.

What problem Builder pattern solves in Java

What is builder design pattern in Java with exampleAs I said earlier Builder pattern is a creational design pattern it means its solves problem related to object creation. Constructors in Java are used to create object and can take parameters required to create object. Problem starts when an Object can be created with lot of parameters, some of them may be mandatory and others may be optional. Consider a class which is used to create Cake, now you need number of item like egg, milk, flour to create cake. many of them are mandatory and some  of them are optional like cherry, fruits etc. If we are going to have overloaded constructor for different kind of cake then there will be many constructor and even worst they will accept many parameter.

Problems:
1) too many constructors to maintain.
2) error prone because many fields has same type e.g. sugar and and butter are in cups so instead of 2 cup sugar if you pass 2 cup butter, your compiler will not complain but will get a buttery cake with almost no sugar with high cost of wasting butter.

You can partially solve this problem by creating Cake and then adding ingredients but that will impose another problem of leaving Object on inconsistent state during building, ideally cake should not be available until its created. Both of these problem can be solved by using Builder design pattern in Java. Builder design pattern not only improves readability but also reduces chance of error by adding ingredients explicitly and making object available once fully constructed.

By the way there are many design pattern tutorial already there in Javarevisited like Decorator pattern tutorial and  Observer pattern in Java. If you haven’t read them already then its worth looking.

Example of Builder Design pattern in Java

We will use same example of creating Cake using Builder design pattern in Java. here we have static nested builder class inside Cake which is used to create object.

Guidelines for Builder design pattern in Java

1) Make a static nested class called Builder inside the class whose object will be build by Builder. In this example its Cake.

2) Builder class will have exactly same set of fields as original class.
3) Builder class will expose method for adding ingredients e.g. sugar() in this example. each method will return same Builder object. Builder will be enriched with each method call.

4) Builder.build() method will copy all builder field values into actual class and return object of Item class.
5) Item class (class for which we are creating Builder) should have private constructor to create its object from build() method and prevent outsider to access its constructor.

public class BuilderPatternExample {

    public static void main(String args[]) {

    

        //Creating object using Builder pattern in java

        Cake whiteCake = new Cake.Builder().sugar(1).butter(0.5).  eggs(2).vanila(2).flour(1.5). bakingpowder(0.75).milk(0.5).build();

    

        //Cake is ready to eat :)

        System.out.println(whiteCake);

    }

}

class Cake {

    private final double sugar;   //cup

    private final double butter;  //cup

    private final int eggs;

    private final int vanila;     //spoon

    private final double flour;   //cup

    private final double bakingpowder; //spoon

    private final double milk;  //cup

    private final int cherry;

    public static class Builder {

        private double sugar;   //cup

        private double butter;  //cup

        private int eggs;

        private int vanila;     //spoon

        private double flour;   //cup

        private double bakingpowder; //spoon

        private double milk;  //cup

        private int cherry;

        //builder methods for setting property

        public Builder sugar(double cup){this.sugar = cup; return this; }

        public Builder butter(double cup){this.butter = cup; return this; }

        public Builder eggs(int number){this.eggs = number; return this; }

        public Builder vanila(int spoon){this.vanila = spoon; return this; }

        public Builder flour(double cup){this.flour = cup; return this; }

        public Builder bakingpowder(double spoon){this.sugar = spoon; return this; }

        public Builder milk(double cup){this.milk = cup; return this; }

        public Builder cherry(int number){this.cherry = number; return this; }

    

    

        //return fully build object

        public Cake build() {

            return new Cake(this);

        }

    }

    //private constructor to enforce object creation through builder

    private Cake(Builder builder) {

        this.sugar = builder.sugar;

        this.butter = builder.butter;

        this.eggs = builder.eggs;

        this.vanila = builder.vanila;

        this.flour = builder.flour;

        this.bakingpowder = builder.bakingpowder;

        this.milk = builder.milk;

        this.cherry = builder.cherry;     

    }

    @Override

    public String toString() {

        return "Cake{" + "sugar=" + sugar + ", butter=" + butter + ", eggs=" + eggs + ", vanila=" + vanila + ", flour=" + flour + ", bakingpowder=" + bakingpowder + ", milk=" + milk + ", cherry=" + cherry + '}';

    }

}

Output:
Cake{sugar=0.75, butter=0.5, eggs=2, vanila=2, flour=1.5, bakingpowder=0.0, milk=0.5, cherry=0}


Builder design pattern in Java – Pros and Cons
Live everything Builder pattern also has some disadvantages, but if you look at below, advantages clearly outnumber disadvantages of Builder design pattern. Any way here are few advantages and disadvantage of Builder design pattern for creating objects in Java.

Advantages:

1) more maintainable if number of fields required to create object is more than 4 or 5.
2) less error-prone as user will know what they are passing because of explicit method call.
3) more robust as only fully constructed object will be available to client.

Disadvantages:

1) verbose and code duplication as Builder needs to copy all fields from Original or Item class.

When to use Builder Design pattern in Java

Builder Design pattern is a creational pattern and should be used when number of parameter required in constructor is more than manageable usually 4 or at most 5. Don't confuse with Builder and Factory pattern there is an obvious difference between Builder and Factory pattern, as Factory can be used to create different implementation of same interface but Builder is tied up with its Container class and only returns object of Outer class.

That's all on Builder design pattern in Java. we have seen why we need Builder pattern , what problem it solves, Example of builder design pattern in Java and finally when to use Builder patter with pros and cons. So if you are not using telescoping constructor pattern or have a choice not to use it than Builder pattern is way to go