Security concepts while developing web applications in Java

Few Security concepts while developing web applications in Java

1) Difference between Hashing and Encryption:
 Hashing takes any amount of data (binary or text) and creates a constant-length hash representing 
 a checksum for the data. For example, the hash might be 16 bytes. Different hashing algorithms produce 
 different size hashes. You obviously cannot re-create the original data from the hash, but you can 
 hash the Original data again to see if the same hash value is generated. 
 Ex: Unix-based passwords work this way. The password is stored as a hash value, and to log onto a system, 
 the password you type is hashed, and the hash value is compared against the hash of the real password. 
 If they match, then you must've typed the correct password. 

 Encryption: Encryption means you are encrypting the Original Data into some un-readable format
 using some Key or Algorithms and store it or send through Network. Again you can decrypt the same
 un-readable data using same Key or Algorithm and generates Original Data.

2) Points to be taken while writing Dynamic Queries in application to prevent Hacking:
 a) SQL injection example one:

 SELECT * FROM admin_auth_user WHERE Firstname='Obopay' AND FamilyName='Admin';
 //8 records returned

 SELECT * FROM admin_auth_user WHERE Firstname='Obopay' AND FamilyName='Admin' OR '1'=1;
 //ALL 26 records returned, adding the OR clause makes WHERE clause condition always TRUE, so this query becomes:
 SELECT * FROM admin_auth_user;


 b) SQL injection example two:
 //Your application query:
 SELECT * FROM items WHERE owner = '" +hackerName+ "' AND itemname = '" +name+"'";

 //Query is modified by Hackers using some script in URL and added something like below,
 //This will delete all records from table on those DB which supports Batch Execution, eg: SQL server-2000, 
 //Oracle doesn't support this.
 SELECT * FROM items WHERE owner = '" +hackerName+ "' AND itemname = '" +name+ "';DELETE FROM items;SELECT * FROM 
 items WHERE 'a'='a'";

 #######################################
 A safe version of the above SQL statement could be coded in Java as:

 String firstname = req.getParameter("firstname");
 String lastname = req.getParameter("lastname");
 //FIXME: Do your own validation to detect attacks
 String query = "SELECT id, firstname, lastname FROM authors WHERE forename = ? and surname = ?";
 PreparedStatement pstmt = connection.prepareStatement( query );
 pstmt.setString( 1, firstname );
 pstmt.setString( 2, lastname );
 try {
    ResultSet results = pstmt.execute( );
 }
 catch(Exception e){}
 ########################################

 c) Visible Content
 Having a simple page, which displays article with given ID as the parameter, the attacker may 
 perform a couple of simple tests if a page is vulnerable to SQL Injection attack.
 
 Example URL:
    http://newspaper.com/items.php?id=2
 Sends the following query to the database:
    SELECT title, description, body FROM items WHERE ID = 2;   //Assume: Returns 1 record

 The attacker may try to inject any (even invalid) query, that may cause the query to return no results:
    http://newspaper.com/items.php?id=2 and 1=2
 Now the SQL query should looks like this:
    SELECT title, description, body FROM items WHERE ID = 2 and 1=2;   //No records will return as 1=2 is False.

 Which means that the query is not going to return anything. Use Again PreparedStatements to fetch only Id.

3) Writing and Inserting Java Script to steal SessionId for a user and use it:

 It is easy to steal SessionId cookies with javascript functions planted in trusted sites by 
 other users. Here we are discussing the possible counter-measures for this kind of attack.

 How Hackers do this:
 <script>document.cookie</script>

 Help is taken from this site (Open Web Application Security Project):
 https://www.owasp.org/index.php/HttpOnly
---------------------END---------------------

Regex: Pattern and Matcher in Java

Java provides the java.util.regex package for pattern matching with regular expressions.
A regular expression defines a search pattern for strings that helps you match or find 
other strings or sets of strings, using a specialized syntax held in a pattern. They can be 
used to search, edit, or manipulate text and data.

The java.util.regex package primarily consists of the following three classes:
   Pattern Class: A Pattern object is a compiled representation of a regular expression. 
   The Pattern class provides no public constructors. To create a pattern, you must first 
   invoke one of its public static compile methods, which will then return a Pattern object. 
   These methods accept a regular expression as the first argument.

   Matcher Class: A Matcher object is the engine that interprets the pattern and performs 
   match operations against an input string. Like the Pattern class, Matcher defines no public 
   constructors. You obtain a Matcher object by invoking the matcher method on a Pattern object.

   PatternSyntaxException: A PatternSyntaxException object is an unchecked exception that indicates 
   a syntax error in a regular expression pattern.

Starting with Example:
==================
package regex;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class RegexMatcher {
    private static String weeks[] = {"monday","tuesday","wednesday","thursday","friday","saturday","sunday"};

    static Pattern p;
    static Matcher m;

    public static void main(String[] args) {
        System.out.println(isNumeric("9916473353"));  //true
        System.out.println(isNumeric("ad9916473353"));  //false

        System.out.println(weekPassword("monday123"));  //true
        System.out.println(weekPassword("ad9916473353"));  //false

        System.out.println(checkAlphabetPassword("monday123"));  //true
        System.out.println(checkAlphabetPassword("AbCd12"));  //true
        System.out.println(checkAlphabetPassword("12"));  //false
        
        System.out.println(check3DigitNumber("dd1d23"));  //false
        System.out.println(check3DigitNumber("45"));  //false
        System.out.println(check3DigitNumber("-124"));  //false
        System.out.println(check3DigitNumber("124"));  //true
    }
    public static boolean isNumeric(String mobNumber){
        p = Pattern.compile("[^\\d]");  //Matches only all digits [0-9]
        //p = Pattern.compile("^[0-9]");  //Matches single non-digit: [^0-9]
        //p = Pattern.compile("[0-9]");  //Matches a single digit [0-9]
        m = p.matcher(mobNumber);
        while (m.find()) {                
            return false;
        }
        return true;
    }

    public boolean isTrue(String s){ 
        return s.matches("true"); //Returns true if the string matches exactly "true"
    }

    public boolean isTrueVersion2(String s){
        return s.matches("[tT]rue"); //Returns true if the string matches exactly "true" or "True"
    }

    //Returns true if the string matches exactly "true" or "True" or "yes" or "Yes"
    public boolean isTrueOrYes(String s){
        return s.matches("[tT]rue|[yY]es");   
    }

    public boolean containsTrue(String s){
        return s.matches(".*true.*");  //Returns true if the string CONTAINS exactly "true"
    }

    public boolean isThreeLetters(String s){
        return s.matches("[a-zA-Z]{3}"); //Returns true if the string contains of three letters
        //return s.matches("[a-Z][a-Z][a-Z]");  //Same as this.
    }

    public boolean isNoNumberAtBeginning(String s){
        return s.matches("^[^\\d].*"); //Returns true if the string does not have a number at the beginning
    }

    public static boolean weekPassword(String password){
        for(int i=0;i<7;i++){  //7 days of week, takes input from week days name array
            p = Pattern.compile(weeks[i], 2); //2 is Pattern.CASE_INSENSITIVE means case-insensitive search
            m = p.matcher(password);          //1 means case sensitive
            while (m.find()) {                
                return true;    
            }
        }
        return false;
    }
    public static boolean checkAlphabetPassword(String password){
        p = Pattern.compile(".??[a-z]"); //Dangling meta char
        m = p.matcher(password);
        while(m.find()) {
            return true;    
        }
        return false;
    }
    public static boolean check3DigitNumber(String s){
        //Pattern pattern = Pattern.compile("\\d{3}");  //Contains minimum 3 consecutive numerics
        Pattern pattern = Pattern.compile("^[0-9]{3}");  //Contains exact 3 numerics
        Matcher matcher = pattern.matcher(s);
        if(matcher.find()){
            return true; 
        } 
        return false; 
    }
}
========================
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class Pattern1 {
    public static void main(String[] args) {
        String source="a1b2c3dBCD45Fee6743";
        String destination=null;  

        Pattern p = Pattern.compile("[^a-z]");  //Pick all except a-z
        //Pattern p = Pattern.compile("[^0-9]");  //Pick all except 0-9
        //Pattern p = Pattern.compile("[^A-Z]");  //Pick all except A-Z
        
        Matcher m = p.matcher(source);

        boolean result = m.find();        
        while(result) {
            destination=m.replaceAll("");       //Replace above match with empty characters.
            result = m.find();
        }
       System.out.println(destination); //Print after replacement. 
    }
}

//Uncomment line 11, output: BCDF
//Uncomment line 10, output: 123456743
//Uncomment line  9, output: abcdee

-----------------------------------
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class Pattern2 {
    public static void main(String[] args) {
        String source="9916473353";
        String sourceWithChar="99r16d4e73353";
        String destination=null;  

        Pattern p = Pattern.compile("[0-9]{7}");  
        //Input only Digits, picks first 7 digits for replacement. 
        //If any character is not number, will return null;
        
        Matcher m = p.matcher(source);  //Without Character
        Matcher mWithChar = p.matcher(sourceWithChar); //With Character

        boolean result = m.find();        
        while(result) {
            destination=m.replaceAll("");  //Replace above match with empty characters.
            result = m.find();
        }
        System.out.println(destination); //Print after replacement. 

        destination=null;
        result = mWithChar.find();   //no match here, as source contains Characters too.            
        while(result) {              //Not entering inside while loop.      
            destination=mWithChar.replaceAll("");  //Replace above match with empty characters.
            result = mWithChar.find();
        }
        System.out.println(destination); //Print after replacement. 
    }
}
//Output:
353
null
========================================================

More help: 
http://www.mkyong.com/regular-expressions/10-java-regular-expression-examples-you-should-know/
http://www.ocpsoft.org/opensource/guide-to-regular-expressions-in-java-part-1/
http://www.regular-expressions.info/email.html

Normalization in Database

Normalization
It is a process of refining the data model by ER diagram.

It includes
  --> Refinement of ER model.
  --> Segregation of data over groups, entities/tables with min redundancy).
  --> Converts ER diagram to tables.

Advantages
  --> min data redundancy.
  --> retreieve information easily.

Need for normalization
*) improves data base design.
*) ensures min redundancy of data
*) reduces need to reorganize data when design is deleted/enhanced.

Unnormalized tables
--> contain redundant data
--> disorganized data
--> problems arise with insertion,updation and deletion.


First Normalization 
Steps
1) identify repeating groups of fields.
2) remove repeating groups of fields to a seperate table.
3) identify the keys for the table.
4) key of parent table is brought as a part of concatenated key of second table.

Second Normalization
Steps
1) check if all fields are dependent on whole key.
2) remove fields that are dependent on partial key.
3) group partially dependent fields as a seperate table.
4) name the table.
5) identify key / keys of the table.

Third Normalization
Steps
1) removes fields that depend on other non key attribute 
2) can be calculated or derived from logic.
3) group independent fields as seperate tables,identify the key.

Oracle programming code_Part2

//Oracle tutorial_part2
//Cursor programming
==========================================
//Explicit cursor
/* using cursor display the details of all those emp from emp whose sum of sal and comm is>3000; */
declare
 vempno emp.empno%type;
 vename emp.ename%type;
 vsal emp.sal%type;
 vdeptno emp.deptno%type;
 cursor c1 is 
   select empno,ename,sal,deptno from emp where sal+nvl(comm,0)>3000;
 begin
   open c1;
   loop
     fetch c1 into vempno,vename,vsal,vdeptno;
     if c1%FOUND then
       dbms_output.put_line(vempno||'    '||vename||'    '||vsal||'       '||vdeptno);
     else
       exit;
     end if;
   end loop;
  close c1;
end;
-----------------------------
declare
 name emp.ename%type;
 no  emp.empno%type;
 cursor empc is select ename,empno from emp;
begin
 open empc;
 dbms_output.put_line('rowcount '||empc%ROWCOUNT);
 loop
  fetch empc into name,no;
  dbms_output.put_line('name '||name||' no'||no);
  exit when empc%NOTFOUND;
 end loop;
 dbms_output.put_line('rowcount'||empc%ROWCOUNT);
 close empc;
end;
-----------------------------
//Implicit cursor (here cursor keyword is not used):
begin
 delete from dept where deptno=100;
 if sql%notfound then
  raise_application_error(-20303,'No such department in the dept table'); 
 end if;
end;
/
//Other commands used in implicit cursor==> sql%FOUND
==========================================

Table creation with constraints:
create table Doctors(
doctor_id number(6) primary key,
doctor_name varchar2(20) not null,
doct_address varchar2(35),
doct_ph_no number(10)
);

create table Test (
test_id number(4) primary key,
test_name varchar2(20),
doctor_id number(6),
constraint fk_doctor_id foreign key(doctor_id) references Doctors
);
==========================================

//Defining values
DEFINE P_SALARY=50000;
DEFINE P_BONUS=10;
DECLARE
RESULT NUMBER:=0;
BEGIN
RESULT:=(P_SALARY*P_BONUS/100)+P_SALARY;
DBMS_OUTPUT.PUT_LINE(RESULT);
END;
/
==========================================
//Stored Procedures:
CREATE OR REPLACE PROCEDURE Prof_Chng_Merchant_To_Silver(INPUT_MOBILENUMBER VARCHAR2)
AS
  Src_memberid   NUMBER(24);
  Src_account_id NUMBER(24);
  Src_programnameref VARCHAR(20);
  Check_programnameref VARCHAR(20);
BEGIN
  SELECT mac.memberid,acc.id INTO Src_memberid,Src_account_id FROM account acc, memberaccountrole mac
  WHERE acc.id = mac.accountid AND acc.devicenumber LIKE INPUT_MOBILENUMBER;

  SELECT Value INTO Check_programnameref FROM account_dtl WHERE code='OP_FR_PROG' AND account_id IN
  (SELECT id FROM account WHERE devicenumber=INPUT_MOBILENUMBER);

  IF Check_programnameref='MERCHANTSVA' then

    SELECT programnameref INTO Src_programnameref FROM account WHERE devicenumber LIKE INPUT_MOBILENUMBER;
    UPDATE ACCOUNT SET PROGRAMNAMEREF = Decode(Src_programnameref,'MBILL','MBILL','SILVER') 
 WHERE DEVICENUMBER LIKE INPUT_MOBILENUMBER;

    IF SQL%ROWCOUNT = 1 THEN     
      UPDATE account_dtl SET Value = 'SILVER' WHERE code LIKE'OP_FR_PROG' AND ACCOUNT_ID = Src_account_id;
      UPDATE bank_temp_upload_customer  SET PRODUCT = 'SILVER' WHERE ACCOUNT_ID = Src_account_id
        AND customer_id = (SELECT Max(customer_id) FROM bank_temp_upload_customer WHERE account_id = Src_account_id);
      Insert into ACCOUNT_LOCK_LOG (id, account_id, reason_code, lock_reason, createdt,
        createdby, modifydt, modifiedby, auth_user_id, locked_by_user_type, action_type)
      Values
        ((SELECT Max(id)+1 FROM ACCOUNT_LOCK_LOG), Src_account_id, 'NOTE', 
  'Coverted the Product type from MERCHANT to SILVER', sysdate, NULL, sysdate, 'PROCEDURE', NULL, NULL, 'NOTE');
    END IF;
  END IF;

  dbms_output.put_line ('No MerchantSVA customer found with this mobile number');

  EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO AUDIT_LOG (
      AUDIT_LOG_ID, TRANSACTION_ID, TRANSACTION_DATE,
      OBJECT_NAME, EVENT_CATEGORY, EVENT_TYPE, OBJECT_PK_VALUE,
      COLUMN_NAME, OLD_VALUE, NEW_VALUE,ADMIN_USER, EVENT_DATA)
    VALUES ( seq_audit_log_id.nextval, null, SYSDATE
      ,'Prof_Chng_Merchant_To_Silver','PLEXCEPTION', 'PROFILE',NULL
      ,'OTHERS' ,NULL, NULL, 'PROCEDURE', INPUT_MOBILENUMBER);

END Prof_Chng_Merchant_To_Silver;
/

//Way to execute
EXEC Prof_Chng_Merchant_To_Silver('919902029064');
COMMIT;
==========================================
create or replace procedure area(length in number,breadth in number)
is
v_area number;
begin
v_area:=length*breadth;
dbms_output.put_line('The area is '||v_area);
end area;
/
-----------------------------
CREATE or replace procedure proc1(p_id in number)
is
emp_record emp%rowtype;
begin
select * into emp_record from emp where empno =p_id;
dbms_output.put_line(emp_record.ename||'   '||emp_record.job);
end proc1;
/
-----------------------------
//Way to execute
begin
  area(6,6);
  proc1(80);
end;
/
OR
Execute area(4,5);
-----------------------------
//Calling another stored procedure
create or replace procedure up_fees(id number)
is
begin
update stud set fees=fees+500
where sl=id;
end up_fees;
/
-----------------------------
create or replace procedure proc7(pid number)
is
begin
up_fees(pid);
exception
when no_data_found then
dbms_output.put_line('No Such data in student table');
end proc7;
/
==========================================

//Function
CREATE or replace function func3(psal number)
return number
is
begin 
insert into emp (empno,ename,job,sal,deptno) values(3455,'Deepak','SSE',4500,20);
return (psal+500);
end func3;
/
-----------------------------
create or replace function day(num IN number)
return varchar2 
is
begin
  case num
           when 1 then return  'SUNDAY'
           when 2 then return  'MONDAY'
           when 3 then return 'TUESDAY'
           when 4 then return 'WENESDAY'
           when 5 then return 'THURSDAY'
           when 6 then return 'FRIDAY'
           when 7 then return 'SATURDAY'
         else return 'INVALID NUMBER'
  END;
 END day;
/
-----------------------------
create or replace function wish(name varchar2)
return varchar2
is
begin
if name='deepak' or name='kumar' or name='modi' then
 return 'Welcome to the creation of function in oracle';
else
return 'Not a valid user of scott';
end if;
end wish;
/
==========================================
declare
 pName bank_temp_upload_customer.product%type;
 PPID  bank_temp_upload_customer.PROGRAM_PROFILE_ID%type;
 custId  bank_temp_upload_customer.CUSTOMER_ID%type;
 custIdMax NUMBER(24);
 mobNumber bank_temp_upload_customer.mobile_number%TYPE; 
 cursor tempCursor is 
    select product,program_profile_id,mobile_number,customer_id from bank_temp_upload_customer 
    WHERE mobile_number IN ('919686680837','919686680836') AND
    pi_master_id=(SELECT pi_master_id FROM PI_MASTER WHERE pi_code='YBL') 
    ORDER BY mobile_number desc;
begin
 open tempCursor; 
 loop
  fetch tempCursor into pName,PPID,mobNumber,custId;
  SELECT Max(customer_id) INTO custIdMax FROM bank_temp_upload_customer WHERE mobile_number=mobNumber;
  IF custId=custIdMax then
     dbms_output.put_line('ProductName ==>'||pName||', Profile_ID==>'||PPID||', Max_Customer_Id==>'||custId||',
  Mobile_Number==>'||mobNumber);     
  UPDATE bank_temp_upload_customer SET PROGRAM_PROFILE_ID=(SELECT ID FROM program_profile WHERE 
  partner_code='YBL' AND PROGRAM_NAME =pName) 
      WHERE MOBILE_NUMBER=mobNumber AND customer_id=custIdMax; 
  END IF;
  exit when tempCursor%NOTFOUND;
 end loop;
 dbms_output.put_line('Task completed');
 close tempCursor;
end;

==========================================

//Triggers in Oracle
Can be seen here too: http://deepakmodi2006.blogspot.com/2011/01/database-triggers-in-oracle.html
CREATE OR REPLACE TRIGGER SECURE_EMP
 BEFORE INSERT ON EMPLOYEES
 BEGIN
 IF
 (TO_CHAR(SYSDATE,'DY') IN  ('THU','FRI')) OR
 (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '17:00')
 THEN RAISE_APPLICATION_ERROR(-20500,'YOU MAY INSERT INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
 END IF;
 END;
/
------------------------------
CREATE OR REPLACE TRIGGER SECURE_EMP1
 BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEES
 BEGIN
  IF
   (TO_CHAR(SYSDATE,'DY') IN  ('WED','SUN')) OR
   (TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00')
  THEN 
IF DELETING THEN 
RAISE_APPLICATION_ERROR(-20501,'YOU MAY DELETE INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
ELSIF INSERTING THEN 
RAISE_APPLICATION_ERROR(-20502,'YOU MAY INSERT INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
ELSIF UPDATING ('DATEOFJOINING') THEN
RAISE_APPLICATION_ERROR(-20503,'YOU MAY UPDATE DOJ INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
ELSE
RAISE_APPLICATION_ERROR(-20504,'YOU MAY DO TRANSACTION INTO EMPLOYEES TABLE ONLY DURING BUSINESS HOURS');
 END IF;
END IF;
END;
/
------------------------------
create or replace trigger tri_emp
after insert or update or delete on employee
for each row
begin
  insert into audit_table values(user,sysdate,:old.empno,:old.ename,:new.ename,
  :old.job,:new.job,:old.sal,:new.sal);
end;
/
==========================================

//Package and Package body
//Package is like Interface in java, only declaration. Package body is having basically details of those.
//One full example to start with:
create or replace package summul as
  procedure pr1(a number,b number);
  function fr1(a number,b number)return number;
  c number;
end summul;

create or replace package body summul
 as
procedure pr1 (a number,b number) 
 is
 sumValue number(4);
 begin
  sumValue:=a+b;
  dbms_output.put_line(sumValue);
end pr1;

function fr1(a number,b number) return number is
mul number;
begin
 mul:=a*b;
 return (mul);
end fr1;
end summul;

//execute summul.pr1(10,20);
//select summul.fr1(10,20) from dual;
SQL> execute summul.pr1(10,20);
     PL/SQL procedure successfully completed.
SQL> set serveroutput on;
SQL> execute summul.pr1(10,20);
30
PL/SQL procedure successfully completed.
SQL> select summul.fr1(12,34) from dual;
SUMMUL.FR1(12,34)
-----------------
408
==========================================

create or replace package var_pack
is
function g_del return number;
procedure set_g_del (p_val in number);
end var_pack;
/
-------------------
create or replace package body var_pack is
gv_del number :=0;

function g_del return number is 
begin
 return gv_del;
end;

procedure set_g_del (p_val in number) is 
begin
if p_val=0 then
  gv_del := p_val;
else 
  gv_del :=gv_del +1;
end if;
end set_g_del;
end var_pack;
/
------------------------------
//Another package and package body
create or replace package dept_pack
 is 
 v_dept number;
 procedure dept_proc;
 end dept_pack;
/
-------------------
create or replace package body dept_pack
 is
 procedure dept_proc
  is
  v_name varchar2(20);
  v_loc varchar2(30);
 begin
  select dname,loc into v_name,v_loc from dept where deptno=v_dept;
  dbms_output.put_line(v_name||'  '||v_loc);
 end dept_proc;
 begin
  select deptno into v_dept from emp where ename='FORD';end dept_pack;
/
==================END=======================

Generating Reports in Oracle

sql * plus allows the user the flexibility for formatting the results in the form of reports.

It uses sql to retrieve information from the oracle data base and lets the user create polished,well formatted reports.
//Commands used in generation of reports
1. REM
SQLPlus ignores anything on a line that begins with the letters REM. It is used to add comments,
documnetation and explanations etc to a FILE.

2. SET HEAD SEP
SET HEAD SEP is used for head seperator .SQLPLUS will indicate to break a page title
or a column heading that no longer than one line.

3. TTILE AND BTITLE
TTITLE is used to set header for each page and BTITLE is used to set the footer for each page.

4. COLUMN
Column allows us to change the heading and format of any column in a select statement.

5. BREAKON <Column- name> SKIP page <n>
breakon to skip n lines every time the item is changed. breakon command and order by clause must be used together.

6. computesum
compute sum command used to calculate the sum for a particular column and always works with
breakon command.

7. set linesize
set Line size determines the max no of characters that appear in a single line.

8. set pagesize
set page size command sets the total no of lines PLSQL will place on each page including 
ttitle,btitle,column headingsand blank lines it prints.

9. set NewPage
set new page print blank lines before the top line of each page of the reports.

10. list<n>
list command list the buffer contents depending on n.

11. set pause on 
set pause on command is used to view the contents page by page.

12. set pause off
set pause off command is used to disable the pause  on command.

13. spool <file-name>  all the information displayed on the screen is displayed in the specified file
After setting all the above formatting commands if we execute the select query then those commands
will be applied to sql query and will generate a report.

14. spool off : will stop spooling.

15. spool out : close the listed file and prints the file to the system printer.

To reset all the above statements ,execute the following
16. ttitle off;
17. btitle off;


//Sample Example:
ttitle 'Deepak writes a good blog having Account report | Account report';
btitle 'Day begins with hello, good morning';
column Name heading 'Name';
column ProductType heading 'Product Type';
column Status heading 'Status';
break on Status;
spool Account_report.doc;
select Name, ProductType,Status from Account where ProductType='PLATINUM';
spool off;
spool out;
ttitle off;
btitle off;
column Name clear;
column ProductType clear;
column Status clear;
clear breaks;
------------------------------End-----------------------

Oracle programming code_Part1

Oracle programming codes_Part_1

//If-else block
declare
a number;
b number;
begin
a := &a;  --input from console
b := &b;  --input from console
if a<b  then
   dbms_output.put_line('a is smallest');
else
   dbms_output.put_line('b is smallest');
end if;
end;
-----------------------
declare
a number;
b number;
c number;
begin
 a := &a;
 b := &b;
 c := &c;
 if a>b and a>c then
   dbms_output.put_line('a is  >');
 elsif b>c then
   dbms_output.put_line('b is  >');
 elsif a=b and b=c then
   dbms_output.put_line('a=b=c');
 else
   dbms_output.put_line('c is  >');
 end if;
end;
========================

//Simple input output
declare
a number;
b number;
c number;
begin
  a := &a;
  b := &b;
  c := a+b;
  dbms_output.put_line('a+b = ' || c);
end;
=========================

//Loop programs
declare
 c number;
begin
 c := 0;
 loop
  c := c+1;
  dbms_output.put_line(c);
  if c = 10 then exit;
  end if;
 end loop;
end;
--------------------
declare
c number;
begin
 c := 0;
 until c>11 loop
  c:=c+1;
  dbms_output.put_line(c);
 end loop;
end;
--------------------
declare
c number;
begin
for c in 1..10  loop
 dbms_output.put_line(c);
end loop;
end;
---------------------
declare
 c number;
begin
 c := 0;
 loop
  c := c+1;
  dbms_output.put_line(c);
  exit when c = 10;
 end loop;
end;

==========================

//Rowtype
declare
x emp%rowtype;
begin
 select * into x from emp where empno=7839;
 if(x.sal >3000) then
  update temp2 set comm=300 where x.empno=7839;
 elsif (x.sal < 6000) then
  update temp2 set comm=600 where x.empno=7839;
 else
  update temp2 set comm=100 where x.empno=7839;
 end if;
 
 dbms_output.put_line('job done');
end;
--------------------------
//Type
declare
 x emp.sal%type;
 e emp.ename%type;
 eno  emp.empno%type;
begin
 eno := &eno;
 select sal into x from emp where empno=eno;
 select ename into e from emp     where empno=eno;
 if x>3000 then
  dbms_output.put_line(e||'s salary is greater than 3000'); 
 else
  dbms_output.put_line(e||'s salary is less than 3000'); 
end if;
end;
==========================
//Exception in oracle
//User defined exception
declare
 a number;
 b number;
 c number;
 my_zero_divide exception;
begin
 a := &a;
 b := &b;
 if b=0 then
   raise my_zero_divide; 
 else
   c := a/b;
   dbms_output.put_line('a/b = ' || c);
 end if;

Exception
when my_zero_divide then
  dbms_output.put_line('you tried to divide a number by zero');

dbms_output.put_line('There was an exception');
end;
--------------------------
//System defined exception
declare
 vename emp.ename%type;
 vjob emp.job%type;
begin
 select ename ,job into vename,vjob from emp where sal>2000;

 Exception
 when NO_DATA_FOUND then
    dbms_output.put_line('NO_DATA_FOUND SQLCODE '||SQLCODE||'SQLERRM '||SQLERRM);
 when TOO_MANY_ROWS then
    dbms_output.put_line('TOO_MANY_ROWS'||'sqlcode   '||sqlcode||'   SQLERRM '||SQLERRM);
 when others then 
    dbms_output.put_line('other Exception');
end;
==========End of chapter1==================

JUnit Tutorial with Examples

//JUnit test case
Functional testing, or integration testing, is concerned with the entire system, not just small 
pieces (or units) of code. It involves taking features that have been tested independently, combining 
them into components, and verifying if they work together as expected. For Java, this testing is 
typically performed using the JUnit framework.

This article introduces and demonstrates the following strategy for building an effective JUnit functional test suite:

    1. Identify use cases that cover all actions that your program should be able to perform.
    2. Identify the code's entry points - central pieces of code that exercise the functionality 
       that the code as a whole is designed to undertake.
    3. Pair entry points with the use cases that they implement.
    4. Create test cases by applying the initialize/work/check procedure.
    5. Develop runtime event diagrams and use them to facilitate testing. 
    6. Tests can rely on each other, but no single test should verify two things.

Translating Test Cases:
Each test case is divided into two parts: input and expected output. The input part lists all the test 
case statements that create variables or assign values to variables. The expected output part indicates 
the expected results; it shows either assertions or the message 'no exception' (when no assertions exist).

The basic input/output format is the simplest, easiest to understand model to follow for test cases. 
It follows the pattern of normal functions (pass arguments, get return value), and most user actions 
(press this button to start this test action). The pattern, then, is to:

   1. Initialize: Create the environment that the test expects to run in. The initialization code can 
      either be in the beginning of the test or in the setUp() method.
   2. Work: Call the code that is being tested, capturing any interesting output and recording any 
      interesting statistics.
   3. Check: Use assert statements to ensure that the code worked as expected. 
   
Example For Testing TOPUP transactions:
======================
//TestData.java
package com.ewp.test.data;
public class TestData {
    private String mobileNumber;
    private String targetMobileNumber;
    
    public String getMobileNumber() {
        return mobileNumber;
    }
    public void setMobileNumber(String mobileNumber) {
        this.mobileNumber = mobileNumber;
    }
    public String getTargetMobileNumber() {
        return targetMobileNumber;
    }
    public void setTargetMobileNumber(String targetMobileNumber) {
        this.targetMobileNumber = targetMobileNumber;
    }
}

======================
//testContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN 2.0//EN" "http://www.springframework.org/dtd/spring-beans-2.0.dtd">
<beans>
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName"><value>oracle.jdbc.driver.OracleDriver</value></property>
        <property name="url"><value>jdbc:oracle:thin:@10.200.101.45:1521:obdbdev</value></property>
        <property name="username"><value>nkq4dev</value></property>
        <property name="password"><value>nkq4dev</value></property>
    </bean>
    
    <bean id="testData" class="com.ewp.test.data.TestData">
        <property name="mobileNumber" value="919686691376"/> 
        <property name="targetMobileNumber" value="919752890670"/>
    </bean>
</beans>
======================
//TopUpTest.java
import java.math.BigDecimal;
import java.util.Random;
import javax.sql.DataSource;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import com.ewp.test.data.TestData;
import junit.framework.TestCase;

public class TopUpTest extends TestCase {
    final String PIN = "1111";
    private static ApplicationContext context;
    private static JdbcTemplate jdbcTemplate;
    private static TestData testData;
    private static DataSource dataSource;
    private static String mobileNumber = null;
    private static String targetMobileNumber = null;
    private static String topupAmount = 40;    

    static {
        context = new ClassPathXmlApplicationContext("testContext.xml");
        testData = (TestData) context.getBean("testData");        
        mobileNumber = testData.getMobileNumber();
        targetMobileNumber = testData.getTargetMobileNumber();    
    }
    /** ************ IMPLEMENTATION LEVEL TESTING *************** */

    @Override
    protected void setUp() throws Exception {
        dataSource = (DataSource) context.getBean("dataSource");
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    protected void tearDown() throws Exception {
        if (dataSource != null) {
            dataSource.getConnection().close();
        }
    }

    private TopUpServiceInterface getTopUpProxy() {
        TopUpServiceInterface paySvc = null;
        paySvc = (TopUpServiceInterface) ServiceProxy.getServiceProxy(TopUpServiceInterface.SERVICE_NAME);
        return paySvc;
    }

    //Mbill/Silver/Merchant,Correct pin
    public void testTopUpNumberCorrectPin() {
        TopUpServiceInterface impl = (TopUpServiceInterface) getTopUpProxy();
        try {
            TopUpSummary summary = impl.processTopup(mobileNumber,PIN, new BigDecimal(topupAmount), targetMobileNumber);
            int status = Integer.parseInt(summary.getStatus());
            assertEquals(summary.getErrorMessage(), 1, status);
        } catch (Exception exception) {
            fail(exception.toString()); //fail() is a method of Assert class in JUnit lib.
        }

    }
}
======================================
//Another test case methods with different return types/features
===============================
public void testUpgradeAccountForLockedAssistant()    throws EWPServiceException {
        try {
            token = TestUtility.getSecurityToken(testData.getTargetMobileNumber(), PIN);
            upgradeAcct(testData.getMobileNumber());
        } 
        catch (EWPServiceException e) {
            e.printStackTrace();
            assertEquals("BE2311", e.getErrorCode());            
        }
}
==============================
public void testDeleteBiller() {
        Boolean retVal = null;
        try {
            retVal = billPayServiceInterface.deleteBiller(testData.getMobileNumber(), new Long(12));
            LogHelper.error(CLASS_NAME, METHOD_NAME, "Delete Biller Status :["+ retVal+"]");
        } catch (com.ewp.core.exceptions.BusinessException e) {
            assertTrue(e instanceof com.ewp.core.exceptions.BusinessException);
        } catch (Exception e) {
            fail();
            e.printStackTrace();
        }
}
=============================
public void testIssueCard() {
        try {    
            NokiaPayAtmCardTest securitytoken = new NokiaPayAtmCardTest();            
            test.isAssistantActiveByMobileNumber(CommonHelpers.normalizePhoneNumber(assistantMobileNumber));
            test.issueCard(assistantMobileNumber, PIN,assistantMobileNumber, silverMobile, cardRefNumber1);
            assertTrue(true);
        }
        catch (Exception e) {

            e.printStackTrace();
            fail(e.getMessage());
        }
}
==============================

Now Again JUnit details:
junit.framework.Test (Interface)
Method Summary (only two methods):
 int     countTestCases()       ==> Counts the number of test cases that will be run by this test.
 void     run(TestResult result) ==> Runs a test and collects its result in a TestResult instance.

All Known Implementing Classes:
    ActiveTestSuite, JUnit4TestAdapter, JUnit4TestCaseFacade, 
    RepeatedTest, TestCase, TestDecorator, TestSetup, TestSuite 

==============================
public abstract class TestCase extends Assert implements Test

A test case defines the fixture to run multiple tests. To define a test case 
 1. implement a subclass of TestCase
 2. define instance variables that store the state of the fixture
 3. initialize the fixture state by overriding setUp()
 4. clean-up after a test by overriding tearDown().

Each test runs in its own fixture so there can be no side effects among test runs. Here is an example:

public class MathTest extends TestCase {
    protected double fValue1;
    protected double fValue2;

    protected void setUp() {
       fValue1= 2.0;
       fValue2= 3.0;
    }
}

For each test implement a method which interacts with the fixture. Verify the expected results with 
assertions specified by calling Assert.assertTrue(String, boolean) with a boolean.

    public void testAdd() {
       double result= fValue1 + fValue2;
       assertTrue(result == 5.0);
    }
====================================================

Once the methods are defined you can run them. The framework supports both a static type safe and 
more dynamic way to run a test. In the static way you override the runTest method and define the method 
to be invoked. A convenient way to do so is with an anonymous inner class.

TestCase test= new MathTest("add") {
    public void runTest() {
       testAdd();
    }
};
test.run();
--------------- 
The dynamic way uses reflection to implement runTest(). It dynamically finds and invokes a method. 
In this case the name of the test case has to correspond to the test method to be run.

TestCase test= new MathTest("testAdd");
test.run();
--------------- 

The tests to be run can be collected into a TestSuite. JUnit provides different test runners which 
can run a test suite and collect the results. A test runner either expects a static method suite as 
the entry point to get a test to run or it will extract the suite automatically.

//public class TestSuite extends java.lang.Object implements Test

A TestSuite is a Composite of Tests. It runs a collection of test cases. Here is an example using the 
dynamic test definition.

TestSuite suite= new TestSuite();
suite.addTest(new MathTest("testAdd"));
suite.addTest(new MathTest("testDivideByZero"));
 
Alternatively, a TestSuite can extract the tests to be run automatically. To do so you pass the class 
of your TestCase class to the TestSuite constructor.

TestSuite suite= new TestSuite(MathTest.class);

This constructor creates a suite with all the methods starting with "test" that take no arguments.
A final option is to do the same for a large array of test classes.

Class[] testClasses = { MathTest.class, AnotherTest.class }
TestSuite suite= new TestSuite(testClasses);

//Another example
public class TestA extends UITestCase { ... }

public class TestB extends UITestCase { ... }

public class TestC extends UITestCase { ... }

//Putting them in a suite is as simple as defining a class like this:
public class MyTestSuite extends TestCase {
        public static TestSuite suite() {
            TestSuite suite = new TestSuite();       
            suite.addTestSuite(TestA.class);
            suite.addTestSuite(TestB.class);
            suite.addTestSuite(TestC.class);
            return suite;
        }
}
-----------------------------------------------
Few more Test methods:
    fail(String)     :  Let the method fail, might be usable to check that a certain part of the code is not reached.
    assertTrue(true) :    True

    assertsEquals([String message], expected, actual):    Test if the values are the same. Note: for 
                                                    arrays the reference is checked not the content of the arrays
    assertsEquals([String message], expected, actual, tolerance): Usage for float and double; the tolerance 
                                                    are the number of decimals which must be the same
    assertNull([message], object):    Checks if the object is null
    assertNotNull([message], object):    Check if the object is not null
    assertSame([String], expected, actual):    Check if both variables refer to the same object
    assertNotSame([String], expected, actual):    Check that both variables refer not to the same object
    assertTrue([message], boolean condition):    Check if the boolean condition is true.
==============================================END====================================================

Primitive data types in Java

Java's Primitive Data Types

boolean
    1-bit. May take on the values true and false only.
    "true" and "false" are defined constants of the language and are not the same as 
    True and False, TRUE and FALSE, zero and nonzero, 1 and 0 or any other numeric value. 
    Booleans may not be cast into any other type of variable nor any other variable can be cast into a boolean.

byte
    1 signed byte (two's complement). Covers values from -128 to 127 (From 2^8 to 2^7).

short
    2 bytes, signed (two's complement), -32,768 to 32,767  (From 2^16 to 2^15)

int
    4 bytes, signed (two's complement). -2,147,483,648 to 2,147,483,647. Like all numeric types integers may 
    be cast into other numeric types (byte, short, long, float, double). When lossy casts are done 
    (e.g. int to byte) the conversion is done modulo the length of the smaller type.

long
    8 bytes signed (two's complement). Ranges from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807.

float
    4 bytes, IEEE 754. Covers a range from 1.40129846432481707e-45 to 3.40282346638528860e+38 (positive or negative).
    Like all numeric types floats may be cast into other numeric types (byte, short, long, int, double). 
    When lossy casts to integer types are done (e.g. float to short) the fractional part is truncated and 
    the conversion is done modulo the length of the smaller type.

double
    8 bytes IEEE 754. Covers a range from 4.94065645841246544e-324d to 1.79769313486231570e+308d (positive or negative). 

char
    2 bytes, unsigned, Unicode, 0 to 65,535
    Chars are not the same as bytes, ints, shorts or Strings.