Google

Nov 30, 2013

Java coding question on iteration and generics -2

This is an extension to Java coding question on recursion and generics.

Q. Can you replace the recursion approach to iteration?


"The recursive implementation will overflow the stack for any data set of real size, your job is to implement a
 non-recursive solution."

A. Since we have defined "Compute" as an interface we can provide a separate implementation for iterative approach.

package recursiontoiteration;

import java.util.Queue;

public class Iteration<R, N> implements Compute<R, N>
{
    
    @Override
    public R compute(R r, Queue<N> q, Function<R, N> function)
    {
        
        while (q.peek() != null)
        {
            r = function.apply(r, q.poll());
        }
        
        return r;
    }
    
}


Now, the test class will only have a small change as shown below.

package recursiontoiteration;

import java.util.Queue;
import java.util.concurrent.ArrayBlockingQueue;

public class IterationTest
{
    public static void main(String[] args)
    {
        Queue<Integer> q = new ArrayBlockingQueue<Integer>(5);
        q.add(5);
        q.add(10);
        q.add(12);
        
        Compute<Double, Integer> compute = new Iteration<Double, Integer>(); // change
        Double result = compute.compute(null, q, new Sum<Double, Integer>());
        System.out.println(result);
        
    }
}

Changing the function to Multiply from  Sum is simple as well.

package recursiontoiteration;

import java.util.Queue;
import java.util.concurrent.ArrayBlockingQueue;

public class IterationTest
{
    public static void main(String[] args)
    {
        Queue<Integer> q = new ArrayBlockingQueue<Integer>(5);
        q.add(5);
        q.add(10);
        q.add(12);
        
        Compute<Double, Integer> compute = new Iteration<Double, Integer>(); // change
        Double result = compute.compute(null, q, new Multiply<Double, Integer>()); //change 
        System.out.println(result);
        
    }
}

So, in other words, the classes are open for extension and closed for modification. This is a design principle known as the "Open Closed Principle (OCP)", which is one of the SOLID design principles. The SOLID design principles are covered in more detail in "Core Java Carteer Essentials"

Labels:

Nov 25, 2013

Java FutureTask example

Java 5 introduced the concurrent package for more efficient multi-threading. The executor framework and callable/Future interfaces were covered in the post entitled

Java 5 Executor framework

Q. What is the difference between Future and FutureTask in asynchronous processing?
A. Future is the interface and FutureTask is the base implementation of the Future with methods to start and cancel a computation. The FutureTask provides asynchronous computation with methods to start and cancel a computation, query to see if the computation is complete, and retrieve the result of the computation. The result can only be retrieved when the computation has completed. The get method will block if the computation has not yet completed. Once the computation has completed, the computation cannot be restarted or cancelled.

Here is an example with 2 tasks. One is an internal short task that takes ~1 second. The second task is an external long running task taking 4 ~ 10 seconds. It is imperative that long running tasks need to have proper processing timeouts.


 
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.FutureTask;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;

public class FutureTaskExample {

 // inner class
 static class InternalProcess implements Callable&t;Integer> {

  @Override
  public Integer call() throws Exception {
   
   // just to simulate short internal process
   TimeUnit.SECONDS.sleep(1);
   return 2;
  }

 }

 // inner class
 static class ExternalProcess implements Callable&t;Integer> {

  @Override
  public Integer call() throws Exception {
   // just to simulate long running external process
   TimeUnit.SECONDS.sleep(15);
   return 12;
  }
 }

 public static void main(String[] args) {
  InternalProcess callable1 = new InternalProcess();
  ExternalProcess callable2 = new ExternalProcess();

  FutureTask&t;Integer> futureTask1 = new FutureTask&t;Integer>(callable1);
  FutureTask&t;Integer> futureTask2 = new FutureTask&t;Integer>(callable2);

  // create a fixed thread pool with 2 threads
  ExecutorService executor = Executors.newFixedThreadPool(2);
  // add future tasks to the pool
  executor.execute(futureTask1);
  executor.execute(futureTask2);

  while (true) {
   try {
    if (futureTask1.isDone() && futureTask2.isDone()) {
     System.out.println("Shutting down the executor.");
     // shut down executor service
     executor.shutdown();
     return;
    }

    //if not done do it once
    if (!futureTask1.isDone()) {
     // wait indefinitely for future task to complete
     System.out.println("Task1 output = "
       + futureTask1.get());
    }

    System.out.println("Waiting for FutureTask2 to complete");
    //try the external task with the timeout of 5 seconds
    Integer result = futureTask2.get(5, TimeUnit.SECONDS);
    if (result != null) {
     System.out.println("Task2 output = " + result);
    }
   } catch (InterruptedException ie) {
    ie.printStackTrace();
   } catch (TimeoutException e) {
    // do nothing as we want to process it asynchronously
    // if you want to time out then uncomment 2 lines
    
    //System.out.println("Cancelling Task2 due to timeout");
    //futureTask2.cancel(true); // true means interrupt
   } catch (ExecutionException e) {
    e.printStackTrace();
   }
  }
 }

}




The output is

 
Task1 output = 2
Waiting for FutureTask2 to complete
Waiting for FutureTask2 to complete
Waiting for FutureTask2 to complete
Task2 output = 12
Shutting down the executor.


If you re-run it by uncommenting the last 2 lines in the TimeoutException catch block, you will get task2 cancelled.

 
Task1 output = 2
Waiting for FutureTask2 to complete
Cancelling Task2 due to timeout
Shutting down the executor.

Labels:

Drools example with spreadsheets tutorial

This is an  industrial strength extension to Drools with Decision tables (Excel spreadsheet).

The drool file: @{variable_name} read from spreadsheet values. Others are from Java classes and objects.


//read from an excel spreadsheet
template header
rules
group
inCashTransactionNm
inAnalysisCd
inTransactionTypeCd
inTransactionSubTypeCd
transactionTypeCd
transactionSubTypeCd
incomeSubTypeCd
analysisCd
audAmount
fxGainAndLoss

package com.myapp.calc;

//... 

global DroolsHelper droolsHelper;
global BankAccountService bankAccountService;
global StockTransactionService stockTransactionService;

template "CashTransaction"

rule "@{rules}_@{row.rowNumber}"
    activation-group "@{group}"
    salience -@{row.rowNumber}
    when
       cashTransaction : CashTransaction(true
                                         ,cashTransactionNm in (@{inCashTransactionNm})
                                         ,analysisCd in (@{inAnalysisCd})
                                         )
       bankAccount : BankAccount() from bankAccountService.getBankAccount(cashTransaction.getBankAccountId())
       stockTransaction : StockTransaction(getTransactionTypeCd() != null, transactionTypeCd.getTypeCode() in (@{inTransactionTypeCd}), transactionSubTypeCd.getTypeCode() in (@{inTransactionSubTypeCd})) from stockTransactionService.findByPortfolio(cashTransaction.portfolioId, cashTransaction.sourceTransactionId)
    then
       Transaction transaction = new Transaction(); // create a new object
       transactionsExtract.add(transaction);
       transaction.setRuleActivated("@{rules}_@{row.rowNumber}");
       transaction.setAssetCode(bankAccount.getAccountCd());
       transaction.setTransactionId(Long.valueOf(cashTransaction.getSourceTransactionId()));
       transaction.setTransactionDtTm(cashTransaction.getAccountingDt());
       transaction.setValueAud(droolsHelper.@{audAmount}(cashTransaction));
       deTransaction.setForeignExchangeGainsAndLosses(@{fxGainAndLoss}(cashTransaction));
        
end

end template


Define the method that invokes the rules.

  //....
  public List<Transaction> calcCash(int accountId)
    {
        LOG.info(String.format("Calculating : calcCash"));
        
        List<Transaction> transactions = new ArrayList<Transaction>();
        
  //read cash transactions from the database
        List<CashTransaction> cashTransactions = cashTransactionService.findByAccountId(accountId);
        
        
        for (CashTransaction cashTransaction : cashTransactions)
        {
            if (cashTransaction.getAnalysisCd() != null)
            {
                @SuppressWarnings("rawtypes")
                List<Command> cmds = new ArrayList<Command>();
                
    //make things available to drools file defined above
                cmds.add(CommandFactory.newSetGlobal("transactionsExtract", new ArrayList<Transaction>(), true));
                cmds.add(CommandFactory.newSetGlobal("bankAccountService", bankAccountService));
                cmds.add(CommandFactory.newSetGlobal("stockTransactionService", stockTransactionService));
                cmds.add(CommandFactory.newSetGlobal("droolsHelper", DroolsHelper));
                cmds.add(CommandFactory.newInsert(cashTransaction));
                
    //execute drools rules
                ExecutionResults results = ksession.execute(CommandFactory.newBatchExecution(cmds));
                
    List<Transaction> trans = (List<Transaction>) results.getValue("transactionsExtract");
                
                transactions.addAll(trans);
            }
        }


Finally, if the value of audAmount = "cash" the DroolsHelper static method invoked will be as shown below

    public static BigDecimal cash(CashTransaction transaction)
    {
        return cashTransaction.getCashValuePortfolio();
    }

Labels:

Nov 21, 2013

Spring Java configuration to override and mock services using Mockito

In one of the previous posts entitled  Spring Java based configuration using @Configuration and @Bean we discussed how to use Java based configuration in Spring. We also looked at Mockito based examples and BDD using jBehave in this site.


Step 1: Let's say we have a AccountService class that we want to subject under test. While testing, we want to supply mocks for  BankAccountService and CashTransactionService. But use the real CalcEngine.


package com.myapp.services;

import static com.jpmorgan.wss.aes.calculation.strongersuper.engine.CalcEngineDroolsHelper.isNull;
//..

import java.io.IOException;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.annotation.Resource;

import org.joda.time.DateTime;
import org.joda.time.LocalDate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;


@Component("accountService")
public class AccountService
{
  
   
    @Resource(name = "cashTransactionService")
    private CashTransactionService cashTransactionService;
    
        
    @Resource(name = "bankAccountService")
    private BankAccountService bankAccountService;
    
            
    @Resource(name = "calcEngine")
    private CalcEngine calcEngine;
    
      
    @Transactional(readOnly = true)
    public List<balances> calcTransaction(int accountId)
            throws IOException
    {
        //..............uses  cashTransactionService, bankAccountService, and calcEngine
    }
    
    //...other methods
    
}


Step 2: The jBehave step class with Spring DI.

package com.myapp.bdd.steps;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.drools.runtime.StatelessKnowledgeSession;
import org.hamcrest.MatcherAssert;
import org.hamcrest.Matchers;
import org.jbehave.core.annotations.Given;
import org.jbehave.core.annotations.Then;
import org.jbehave.core.annotations.When;
import org.jbehave.core.model.ExamplesTable;
import org.joda.time.DateTime;
import org.mockito.Mockito;
import org.springframework.stereotype.Component;

@Component
public class CashTransactionRulesStep
{
    
    @Resource(name = "accountService")
    private AccountService accountService;
    
    @Resource(name = "cashTransactionService")
    private CashTransactionService cashTransactionService;
    
    @Resource(name = "bankAccountService")
    private BankAccountService bankAccountService;

    BankAccount bankAccount = new BankAccount();
 List<CashTransaction> cashTransactionsList = Collections.EMPTY_LIST;
 List<Transaction> result = null;
       
    @Given("a bankAccountCd = $bankAccountCd and bankAccountNm = $bankAccountNm")
    public void bankAccountDetails(String bankAccountCd, String bankAccountNm)
    {
        bankAccount.setAccountCd(bankAccountCd);
        bankAccount.setAccountNm1(bankAccountNm);
    }
    
    @When("calcTransaction method is fired with portfoliocd = $portfolioCode")
    public void calcTransaction(String portfolioCode)
    {
        
        try
        {
           
            Mockito.when(
                    cashTransactionService.findByAccountDt(Mockito.anyInt(), (Date) Mockito.anyObject(),
                            (Date) Mockito.anyObject())).thenReturn(cashTransactionsList);
            
            Mockito.when(
                    bankAccountService.getBankAccount(Mockito.anyInt())).thenReturn(bankAccount);
            
            result = transactionService.calcTransaction(1);
             
        }
        catch (Exception e)
        {
            throw new RuntimeException(e);
        }
        
    }
    
    //......
}

Step 3: Now the Spring config class that overrides DI in AccountService with fully and partially mocked injection. The resource names need to be same to override, and nor defined under  @ComponentScan but defined with @Bean.

package com.myapp.bdd.stories;

import org.mockito.Mockito;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.FilterType;
import org.springframework.context.annotation.Import;

@Configuration("AccountingStoryConfig")
@ComponentScan(
        basePackages =
        {
            "com.myapp.bdd",
            "com.myapp.accounting",
          
        
        },
        useDefaultFilters = false,
        includeFilters =
        {
            @ComponentScan.Filter(type = FilterType.ASSIGNABLE_TYPE, value = ValidatorChain.class),
            @ComponentScan.Filter(type = FilterType.ASSIGNABLE_TYPE, value = TransactionValidator.class)
        })
@Import(
{
    StoryConfig.class //import other cobfigs
})
public class AccountingStoryConfig
{
    
    @Bean(name = "accountService")
    public TransactionService getAccountService()
    {
        return Mockito.spy(new AccountServiceImpl()); //partially mock
    }
    
    @Bean(name = "cashTransactionService")
    public CashTransactionService getCashTransactionService()
    {
        return Mockito.mock(CashTransactionServiceImpl.class); //fully mock
    }
    
    @Bean(name = "bankAccountService")
    public BankAccountService getBankAccountService()
    {
        return Mockito.mock(BankAccountService.class); //fully mock
    }
    
}


Step 4: Finally, for completion sake, the jBehave story class that can run as jUnit test.

package com.myapp.bdd.stories;

import java.util.List;

import org.jbehave.core.io.CodeLocations;
import org.jbehave.core.io.StoryFinder;
import org.springframework.batch.core.configuration.support.ClassPathXmlApplicationContextFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

public class AccountStory extends AllStories
{
    
    public AccountStory()
    {
        super();
    }
    
    protected ApplicationContext context()
    {
        if (context == null)
        {
            try
            {
                ClassPathXmlApplicationContextFactory factory = new ClassPathXmlApplicationContextFactory(null);
                factory.setApplicationContext(new AnnotationConfigApplicationContext(AccountStoryConfig.class));
                
                context = factory.createApplicationContext();
            }
            catch (Exception e)
            {
                throw new RuntimeException(e);
            }
        }
        return context;
    }
    
    @Override
    protected List<string> storyPaths()
    {
        return new StoryFinder().findPaths(
                CodeLocations.codeLocationFromClass(getClass()), "**/account.story", "");
    }
    
}



Labels: ,

Nov 17, 2013

Java coding question on recursion and generics

Q. Can you write Java code to compute a collection of numbers supplied to it? The computation could be addition, subtraction, etc. Use recursion to compute the numbers. Here are some requirements to take into considerations.

1. It should be flexible enough to convert from Recursion to iteration if required.
2. Computation will initially be "addition", but should be extendable to multiplication, subtraction, etc.
3. Should handle integers and floating point numbers.
4. Make use of generics.

Here is a sample test class.


 
package recursiontoiteration;

import java.util.Queue;
import java.util.concurrent.ArrayBlockingQueue;

public class RecursionTest
{
    public static void main(String[] args)
    {
        Queue<Integer> q = new ArrayBlockingQueue<Integer>(5);
        q.add(5);
        q.add(10);
        q.add(12);
        
        Compute<Double, Integer> compute = new Recursion<Double, Integer>();
        Double result = compute.compute(null, q, new Multiply<Double, Integer>());
        System.out.println(result);
    }
}

A. Firstly, define the "Compute" interface and the "Recursion" implementation.

 
package recursiontoiteration;

import java.util.Queue;

public interface Compute<R, N>
{
    R compute(R r, Queue<N> q, Function<R, N> function);
}

R and N are generic types meaning say Result and Number respectively. In the above example, they will be  inferred at compile time as Double and Integer types respectively. Here is the Recursion implementation.

package recursiontoiteration;

import java.util.Queue;

public class Recursion<R, N> implements Compute<R, N>
{
    public R compute(R r, Queue<N> q, Function<R, N> function)
    {
        //recursion exit condition - no items in the queue to process
        if (q.size() == 0)
        {
            return r;
        }
        
        r = compute(function.apply(r, q.poll()), q, function);
        
        return r;
        
    }
}

Let's define the Function interface to represent mathematical operations like Sum, Multiply, Divide, etc.

package recursiontoiteration;

public interface Function<R, N>
{
    R apply(R r, N n);
}

The implementation Sum will be:

package recursiontoiteration;

import java.math.BigDecimal;

public class Sum<R, N> implements Function<R, N>
{
    
    //add two numbers
    @SuppressWarnings("unchecked")
    @Override
    public R apply(R r, N n)
    {
        Number result = Double.valueOf(0);
        Number number = Double.valueOf(0);
        if (r != null)
        {
            result = (Double) r;
        }
        
        if (n != null)
        {
            number = (Number) n;
        }
        
        //big decimal is better for rounding values
        BigDecimal addedValue = new BigDecimal(result.doubleValue()).add(new BigDecimal(number.doubleValue()));
        
        return (R) (Number) Double.valueOf(addedValue.toPlainString());
    }
}

Multiply will be implemented as shown below.

package recursiontoiteration;

import java.math.BigDecimal;

public class Multiply<R, N> implements Function<R, N>
{
    
    @SuppressWarnings("unchecked")
    @Override
    public R apply(R r, N n)
    {
        Number result = Double.valueOf(1);
        Number number = Double.valueOf(1);
        if (r != null)
        {
            result = (Double) r;
        }
        
        if (n != null)
        {
            number = (Number) n;
        }
        
        //big decimal is better for rounding values
        BigDecimal multipliedValue = new BigDecimal(result.doubleValue())
                .multiply(new BigDecimal(number.doubleValue()));
        
        return (R) (Number) Double.valueOf(multipliedValue.toPlainString());
    }
}

Even though it is a trivial example, many beginner to intermediate level developers struggle with --> recursion, generics, and writing extendable programs with proper interfaces.

In the next blog post, we will find how to convert recursion to iteration.

Labels: ,

Nov 14, 2013

Hibernate custom data type : Blob type example

There are times you want to define a custom data type in Hibernate. Here is an example of using a custom ImageType defined as a Blob databse type to store images, excel files, etc.

Step 1: Define the custom Hibernate data type "ImageTpe" as shown below.


 
package com.myapp.common;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.sql.Blob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.usertype.UserType;

public class ImageType implements UserType
{
    
    @Override
    public int[] sqlTypes()
    {
        return new int[]
        {Types.BLOB
        };
    }
    
    @Override
    public Class<Blob> returnedClass()
    {
        return Blob.class;
    }
    
    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner)
            throws HibernateException, SQLException
    {
        return getBlobFromBinaryStream(rs, names[0]);
    }
    
    private byte[] getBlobFromBinaryStream(ResultSet rs, String aColName)
            throws SQLException
    {
        
        byte[] theBuff = new byte[2 * 1024];
        InputStream theInStream = rs.getBinaryStream(aColName);
        
        ByteArrayOutputStream theBaos = new ByteArrayOutputStream();
        int n = 0;
        try
        {
            if (theInStream != null)
            {
                while (-1 != (n = theInStream.read(theBuff)))
                {
                    theBaos.write(theBuff, 0, n);
                }
            }
            theBaos.flush();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        
        return theBaos.toByteArray();
    }
    
    @Override
    public void nullSafeSet(PreparedStatement stmt, Object aValue, int aIndex, SessionImplementor session)
            throws HibernateException, SQLException
    {
        stmt.setBytes(aIndex, (byte[]) aValue);
    }
    
    @Override
    public boolean equals(Object obj, Object other)
    {
        if ((obj == other) ||
                (obj != null && other != null && Arraothers.equals(
                        ((byte[]) obj),
                        ((byte[]) other))))
        {
            return true;
        }
        return false;
    }
    
    @Override
    public int hashCode(Object obj) throws HibernateException
    {
        return obj.hashCode();
    }
    
    @Override
    public boolean isMutable()
    {
        return false;
    }
    
    @Override
    public Object assemble(Serializable aSerializableObject, Object obj) throws HibernateException
    {
        return null;
    }
    
    @Override
    public Serializable disassemble(Object onj) throws HibernateException
    {
        return null;
    }
    
    @Override
    public Object replace(Object obj1, obj2, Object obj3) throws HibernateException
    {
        return null;
    }
    
    @Override
    public Object deepCopy(Object obj)
    {
        return obj;
    }   
}


Step 2: Use the custom data type as shown below in Hibernate entity.

 
package com.myapp.model.images;

import java.util.Date;

import javax.persistence.AttributeOverride;
import javax.persistence.AttributeOverrides;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

import org.hibernate.annotations.SQLDelete;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.Where;

@Entity
@Table(name = "images", schema = "dbo", catalog = "my_catalog")
@Where(clause = "inactiveFlag = 'N'")
@SQLDelete(sql = "UPDATE MyImages SET inactiveFlag = 'Y' WHERE MyImagesID = ? and timestamp = ?")

public class MyImages 
{
    
    private static final long serialVersionUID = 1L;
    
    public enum Status
    {
        PENDINDING_RELEASE, RELEASED;
    }
    
    private String name;
    private byte[] image = new byte[0];
    private Status status;
    private byte[] timestamp;

    
    public MyImages()
    {
    }
    
    @Column(name = "name", nullable = false, length = 100)
    public String getName()
    {
        return name;
    }
    
    public void setName(String name)
    {
        this.name = name;
    }
    
 //custom type
    @Type(type = "com.myapp.common.ImageType")
    @Column(name = "rules", nullable = false)
    public byte[] getImage()
    {
        return image;
    }
    
    public void setImage(byte[] image)
    {
        this.image = image;
    }
    
    @Enumerated(EnumType.STRING)
    @Column(name = "Status", nullable = false, length = 30)
    public Status getStatus()
    {
        return status;
    }
    
    public void setStatus(Status status)
    {
        this.status = status;
    }
    
    @Version    
    @Column(name = "Timestamp", insertable = false, updatable = false, unique = true, nullable = false)
    @Generated(GenerationTime.ALWAYS)
 public byte[] getTimestamp() {
  return timestamp;
 }


 public void setTimestamp(byte[] timestamp) {
  this.timestamp = timestamp;
 }   
}




Labels:

Nov 10, 2013

SQL Subquery interview questions and answers

Here are some beginner to intermediate level SQL interview questions and answers. This post compliments my other posts.

Q. What is a subquery?
A. Subquery or Inner query or Nested query is a query in a query. A subquery is usually added in the WHERE clause of the sql statement. A subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.  Subqueries are an alternate way of returning data from multiple tables.


Q. Can you create a subquery in a From clause?
A. Yes. Subqueries can be used in From, Where and Having clauses. For example, in Sybase


select * 
from 
(
    select 'A' as colVal
    union 
    select 'B' as colVal
) data

Returns:

colVal
------
A
B

Joining virtual tables is one of the most powerful feature of subqueries. Virtual in this context means the result set you are joining is built on the fly. Here is a more advanced example:

  declare @clientId varchar(30),
          @reportDate date, 
          
  set nocount on

  select reportId   from   
          Report_Status s,   
          ReportKey k,  
          ReportGroupKey gk,  
    
           --subquery in from clause
          (select  max(s.createddttm) as maxdate, k1.clientId from   
              Report_Status  s,   
              ReportKey k1,  
              ReportGroupKey gk      
           where   k1.InactiveFlag ='N'  
             and  gk.InactiveFlag ='N'  
             and  gk.KeyId = k1.Id  
             and  gk.Id = s.GroupKeyId  
           group by k1.clientId  
          )  maxdates    
            
      where k.InactiveFlag ='N'  
      and  gk.InactiveFlag ='N'  
      and  gk.KeyId = k.Id  
      and  gk.Id = s.GroupKeyId  
      and s.CreatedDtTm = maxdates.maxdate  
      and k.ClientId = @clientId 
      and maxdates.ClientId = k.ClientId
      and k.reportDate = @reportDate 
   


Q.What is a correlated subquery?
A. A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

 
SELECT outer.product_name FROM product outer 
  
 WHERE outer.product_id = (SELECT inner.product_id FROM order_items inner 
                            
                            WHERE outer.product_id = inner.product_id);


If a subquery is not dependent on the outer query it is called a non-correlated subquery.




Q. What are the advantages and disadvantages of using a subquery?
A.

Advantages:

  • Subqueries allow you to use the results of another query in the outer query.
  • Subqueries in some complex SQL queries can simplify coding and improve maintainability by breaking down the complex query into a series of logical steps.
  • In some cases, subqueries are easier to understand than complex joins and unions.

Disadvantages:

  •  When a subquery is used, the query optimizer of the database server may have to perform additional steps like sorting the results, etc. Hence, in some cases subqueries can be less efficient than using joins. So, favor joins to subqueries.

You may also like:

Labels: ,

Nov 7, 2013

My top 5 tips as a Java freelancer

If you are already working as a Java freelancer or aspiring to become one, here are 5 tips from my experience. Feel free to share your experience and tips as a freelancer.


1. Diversify: Don't put all you eggs in one basket. Freelancing gives you professional freedom. For example, you can work for 18 months for a large client of yours on a contract basis and take 3 months off to pursue your other passions like self-publishing the book that you always wanted  to write or  laying the ground work for the start up that you dreamed of.

The hardest part of being a freelancer is to find work continuously. So, it is imperative that you diversify your income streams as described in my blog post entitled "How to earn additional (passive) income streams as a developer". Another 2 reasons why you need to diversify are
  1. The freelancing or contract rates are on the decline due to more competition from the other freelancers who are prepared to drop their rates to get a foot in the door and due to  more competitive off shore development option. When I started contracting in 2003, the large organizations offered hourly contracts rates, and then they moved to daily rates, and now a days opting for a fixed term contract amount.
  2. The IT salary or rates start to plateau after 8-10 year experience unless you move into more management roles. I know that many of you out there like me, more passionate about the hands-on technical role.
So, don't put all your eggs in one basket.  Motivate yourself to diversify. Many of the other diversified actions may not bring in the income for many of you as a full-time contract based assignment with an organization, but they can certainly cushion the effects of being without work in between contracts both emotionally and financially. This also means diversifying your skills into other sought-after programming languages (e.g. JavaScript, Scala, Clojure, etc), technologies (SOA, BPM, BigData, etc) and frameworks (e.g. Angular JS, Hadoop, etc).


2.  Don't just expect your first idea or plan to always work:

If your first idea did not work, come up with more ideas and plans until it works. For example, 
  • Your first freelance job was not as you had expected.  You were assigned boring tasks. You were asked to do things that other permanent staff did not want to touch. You mitigate this by asking the right questions about the job on offer at the interview. An interview is a 2 way street.
  • Your first online article, blog, or book might receive bad reviews. Don't let those criticisms stop you from keep writing. Learn from your mistakes and grow stronger. 
Be an egg hatcher, not a chicken counter. Put your thinking cap on and keep adding values.
 



3.  Maintain a repository of your experience: From my personal experience, this takes time. So, start early by spending 15 to 20 hours every week. Blog your experience on your own unique style to be useful to you and others. As a freelancer, I work with wider range of tools and frameworks. By blogging my experience, I don't have to worry about using a particular tool or framework after a 2 year absence of using it. I know where to go to jog my memory. For example, JasperReports is a very powerful reporting framework. But you don't get to work on it all the time. Blogging will also enhance your understanding as you will be compelled to do more research on the subject. You may also get free tips from your readers in the form of comments. This repository will even come in handy for your future job interview preparation and be the inspiration for your next POD (Print On Demand) book.


4. Get your current employer to say positive parting words like"keep in touch", "see you in 6 - 12 months", or give you a decent farewell.  Generally, contractors stay with an organization for a short term, hence they don't get a decent farewell as the permanent staff do. This is quite understandable. But, if you had impressed your current employer both technically and non-technically by getting things done effectively, proactively, and on time, you are likely to hear more positive parting words from your managers meaning that you have done a good job and they won't hesitate to have you back again in the future. I have underlined the word non-technically because your soft skills and attitude are at least equally important as your technical ability to win the hearts and minds of your managers. I have worked with the so called some super star coders who didn't either get the decent farewell, parting words or the further extension they thought they deserved mainly due to lack of their non-technical skills.


5. Harness the power of networking: It is imperative to network professionally with all your colleagues and superiors via linkedin.com and other avenues like regular catch ups, emails, etc. As mentioned before, only 30% to 40% of the vacancies or job opportunities are advertised and the remaining are filled internally or via contacts. You will also face less competition and scrutiny when you find your future work via your network. You can even eliminate or reduce the share that is charged by the recruitment or consulting agencies. Some companies do not hire directly and have policy in place to deal through recruitment or consulting company. So, you need to maintain a good rapport with them as well.




You may also like:

Labels: ,