Hands on with the HR database (Oracle)

Developer Station Virtual Machine

I have prepared a VirtualBox virtual machine containing a basic environment and software for Java and database development for my students. This VM is based on CentOS 6.4 x64 and it has already installed the PostgreSQL 8.4 and Oracle XE 11g DB management systems, Oracle SQL Developer and Data Modeler, pgAdmin III, the JDK 7, DrJava, a lightweight development environment (ideal for students doing the first steps with Java), and the IDEs NetBeans 7.3.1 and Eclipse Kepler.

For anyone who wish to make a try, this virtual machine is available for downloading at the Ocean Section of this website (default username and password are damdev).

devStationDesktop

Start and stop the Oracle XE database

Only users belonging to DBA group are allowed to complete the full startup and shutdown of the Oracle XE DB, which is not configured to automatically start at OS boot time (the PostgreSQL DB it is, instead). The superuser can start the DB with the /etc/init.d/oracle-xe script (with the typical options start|stop|status), but as a DBA user (damdev and oracle are both DBA members) you can start the listener via terminal with

$> lnsrctl start

and start, mount and open the database with the

$> sqlplus sys as sysdba

command line as follows (default password is oracle):

listenerStartOXE

dbStartOXE

To shutdown the Oracle XE you proceed as show next:

shutdownOXE

If you are familiar with the Oracle 11g full version (not the Express Edition -XE-) you will miss the Enterprise Manager Console, and this is because the XE edition has no dbconsole (thus there is no need for emctl start|stop dbconsole).

Unlock the HR user

Oracle XE comes with a Human Resources Schema as a sample for practicing exercises and labs. But before you can use it, you must unlock the HR user, who is the user whith granted permissions over the HR database:

$> sqlplus sys as sysdba
SQL> ALTER USER hr ACCOUNT UNLOCK;
SQL> ALTER USER hr IDENTIFIED BY password;
SQL> exit

(use the password you wish for the hr user). You can try now to retrieve some data from the HR schema:

HR

The Human Resources ER diagram

A simple ER diagram for the HR schema is shown next:

hrtables

Exercises on the HR schema: Queries

(You can try your answers with sqlplus)

  1. Display details of jobs where the minimum salary is greater than 10000.
  2. Display the first name and join date of the employees who joined between 2002 and 2005.
  3. Display first name and join date of the employees who is either IT Programmer or Sales Man.
  4. Display employees who joined after 1st January 2008.
  5. Display details of employee with ID 150 or 160.
  6. Display first name, salary, commission pct, and hire date for employees with salary less than 10000.
  7. Display job Title, the difference between minimum and maximum salaries for jobs with max salary in the range 10000 to 20000.
  8. Display first name, salary, and round the salary to thousands.
  9. Display details of jobs in the descending order of the title.
  10. Display employees where the first name or last name starts with S.
  11. Display employees who joined in the month of May.
  12. Display details of the employees where commission percentage is null and salary in the range 5000 to 10000 and department is 30.
  13. Display first name and date of first salary of the employees.
  14. Display first name and experience of the employees.
  15. Display first name of employees who joined in 2001.
  16. Display first name and last name after converting the first letter of each name to upper case and the rest to lower case.
  17. Display the first word in job title.
  18. Display the length of first name for employees where last name contain character ‘b’ after 3rd position.
  19. Display first name in upper case and email address in lower case for employees where the first name and email address are same irrespective of the case.
  20. Display employees who joined in the current year.
  21. Display the number of days between system date and 1st January 2011.
  22. Display how many employees joined in each month of the current year.
  23. Display manager ID and number of employees managed by the manager.
  24. Display employee ID and the date on which he ended his previous job.
  25. Display number of employees joined after 15th of the month.
  26. Display the country ID and number of cities we have in the country.
  27. Display average salary of employees in each department who have commission percentage.
  28. Display job ID, number of employees, sum of salary, and difference between highest salary and lowest salary of the employees of the job.
  29. Display job ID for jobs with average salary more than 10000.
  30. Display years in which more than 10 employees joined.
  31. Display departments in which more than five employees have commission percentage.
  32. Display employee ID for employees who did more than one job in the past.
  33. Display job ID of jobs that were done by more than 3 employees for more than 100 days.
  34. Display department ID, year, and Number of employees joined.
  35. Display departments where any manager is managing more than 5 employees.
  36. Change salary of employee 115 to 8000 if the existing salary is less than 6000.
  37. Insert a new employee into employees with all the required details.
  38. Delete department 20.
  39. Change job ID of employee 110 to IT_PROG if the employee belongs to department 10 and the existing job ID does not start with IT.
  40. Insert a row into departments table with manager ID 120 and location ID in any location ID for city Tokyo
  41. Display department name and number of employees in the department.
  42. Display job title, employee ID, number of days between ending date and starting date for all jobs in department 30 from job history.
  43. Display department name and manager first name.
  44. Display department name, manager name, and city.
  45. Display country name, city, and department name.
  46. Display job title, department name, employee last name, starting date for all jobs from 2000 to 2005.
  47. Display job title and average salary of employees
  48. Display job title, employee name, and the difference between maximum salary for the job and salary of the employee.
  49. Display last name, job title of employees who have commission percentage and belongs to department 30.
  50. Display details of jobs that were done by any employee who is currently drawing more than 15000 of salary.
  51. Display department name, manager name, and salary of the manager for all managers whose experience is more than 5 years.
  52. Display employee name if the employee joined before his manager.
  53. Display employee name, job title for the jobs employee did in the past where the job was done less than six months.
  54. Display employee name and country in which he is working.
  55. Display department name, average salary and number of employees with commission within the department.
  56. Display the month in which more than 5 employees joined in any department located in Sydney.
  57. Display details of departments in which the maximum salary is more than 10000.
  58. Display details of departments managed by ‘Smith’.
  59. Display jobs into which employees joined in the current year.
  60. Display employees who did not do any job in the past.
  61. Display job title and average salary for employees who did a job in the past.
  62. Display country name, city, and number of departments where department has more than 5 employees.
  63. Display details of manager who manages more than 5 employees.
  64. Display employee name, job title, start date, and end date of past jobs of all employees with commission percentage null.
  65. Display the departments into which no employee joined in last two years.
  66. Display the details of departments in which the max salary is greater than 10000 for employees who did a job in the past.
  67. Display details of current job for employees who worked as IT Programmers in the past.
  68. Display the details of employees drawing the highest salary in the department.
  69. Display the city of employee whose employee ID is 105.
  70. Display third highest salary of all employees

Exercises on the HR schema: PL/SQL programs

  1. Write a program to interchange the salaries of employee 120 and 122.
  2. Increase the salary of employee 115 based on the following conditions:If experience is more than 10 years, increase salary by 20%If experience is greater than 5 years, increase salary by 10%Otherwise 5% Case by Expression.
  3. Change commission percentage as follows for employee with ID = 150.If salary is more than 10000 then commission is 0.4%, if Salary is less than 10000 but experience is more than 10 years then 0.35%, if salary is less than 3000 then commission is 0.25%.In the remaining cases commission is 0.15%.
  4. Find out the name of the employee and name of the department for the employee who is managing for employee 103.
  5. Display missing employee IDs.
  6. Display the year in which maximum number of employees joined along with how many joined in each month in that year.
  7. Change salary of employee 130 to the salary of the employee with first name ‘Joe’. If Joe is not found then take average salary of all employees. If more than one employee with first name ‘Joe’ is found then take the least salary of the employees with first name Joe.
  8. Display Job Title and Name of the Employee who joined the job first day.
  9. Display 5th and 10th employees in Employees table.
  10. Update salary of an employee based on department and commission percentage. If department is 40 increase salary by 10%. If department is 70 then 15%,if commission is more than .3% then 5% otherwise 10%.
  11. Create a function that takes department ID and returns the name of the manager of the department.
  12. Create a function that takes employee ID and return the number of jobs done by the employee in the past.
  13. Create a procedure that takes department ID and changes the manager ID for the department to the employee in the department with highest salary. (Use Exceptions).
  14. Create a function that takes a manager ID and return the names of employees who report to this manager. The names must be returned as a string with comma separating names.
  15. Ensure no changes can be made to EMPLOYEES table before 6am and after 10pm in a day.
  16. Create a Trigger to ensure the salary of the employee is not decreased.
  17. Create a trigger to ensure the employee and manager belongs to the same department.
  18. Whenever the job is changed for an employee write the following details into job history.Employee ID, old job ID, old department ID, hire date of the employee for start date, system date for end date.But if a row is already present for employee job history then the start date should be the end date of that row +1.

The HR DDL script

You can download here the DDL script for the HR schema; though it is bundled with the Oracle DB and Oracle XE DB installations, you may need it:

hr-30-ddl-246035.zip

Solutions for the above exercises

Only if you are a registered member with the right role you can access to the solutions for the above exercises.

[s2If current_user_is(s2member_level2)]

Queries

  1. Display details of jobs where the minimum salary is greater than 10000.
    SELECT * FROM JOBS WHERE MIN_SALARY > 10000
  2. Display the first name and join date of the employees who joined between 2002 and 2005.
    SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES 
    WHERE TO_CHAR(HIRE_DATE, 'YYYY') BETWEEN 2002 AND 2005 ORDER BY HIRE_DATE
  3. Display first name and join date of the employees who is either IT Programmer or Sales Man.
  4. SELECT FIRST_NAME, HIRE_DATE
    FROM EMPLOYEES WHERE JOB_ID IN ('IT_PROG', 'SA_MAN')
  5. Display employees who joined after 1st January 2008.
  6. SELECT * FROM EMPLOYEES  where hire_date > '01-jan-2008'
  7. Display details of employee with ID 150 or 160.
  8. SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID in (150,160)
  9. Display first name, salary, commission pct, and hire date for employees with salary less than 10000.
  10. SELECT FIRST_NAME, SALARY, COMMISSION_PCT, HIRE_DATE FROM EMPLOYEES WHERE SALARY < 10000
  11. Display job Title, the difference between minimum and maximum salaries for jobs with max salary in the range 10000 to 20000.
  12. SELECT JOB_TITLE, MAX_SALARY-MIN_SALARY DIFFERENCE FROM JOBS WHERE MAX_SALARY BETWEEN 10000 AND 20000
  13. Display first name, salary, and round the salary to thousands.
  14. SELECT FIRST_NAME, SALARY, ROUND(SALARY, -3) FROM EMPLOYEES
  15. Display details of jobs in the descending order of the title.
  16. SELECT * FROM JOBS ORDER BY JOB_TITLE
  17. Display employees where the first name or last name starts with S.
  18. SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE  FIRST_NAME  LIKE 'S%' OR LAST_NAME LIKE 'S%'
  19. Display employees who joined in the month of May.
  20. SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'MON')= 'MAY'
  21. Display details of the employees where commission percentage is null and salary in the range 5000 to 10000 and department is 30.
  22. SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NULL AND SALARY BETWEEN 5000 AND 10000 AND DEPARTMENT_ID=30
  23. Display first name and date of first salary of the employees.
  24. SELECT FIRST_NAME, HIRE_DATE, LAST_DAY(HIRE_DATE)+1 FROM EMPLOYEES
  25. Display first name and experience of the employees.
  26. SELECT FIRST_NAME, HIRE_DATE, FLOOR((SYSDATE-HIRE_DATE)/365)FROM EMPLOYEES
  27. Display first name of employees who joined in 2001.
  28. SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE, 'YYYY')=2001
  29. Display first name and last name after converting the first letter of each name to upper case and the rest to lower case.
  30. SELECT INITCAP(FIRST_NAME), INITCAP(LAST_NAME) FROM EMPLOYEES
  31. Display the first word in job title.
  32. SELECT JOB_TITLE,  SUBSTR(JOB_TITLE,1, INSTR(JOB_TITLE, ' ')-1) FROM JOBS
  33. Display the length of first name for employees where last name contain character ‘b’ after 3rd position.
  34. SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE INSTR(LAST_NAME,'B') > 3
  35. Display first name in upper case and email address in lower case for employees where the first name and email address are same irrespective of the case.
  36. SELECT UPPER(FIRST_NAME), LOWER(EMAIL) FROM EMPLOYEES WHERE UPPER(FIRST_NAME)= UPPER(EMAIL)
  37. Display employees who joined in the current year.
  38. SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')=TO_CHAR(SYSDATE, 'YYYY')
  39. Display the number of days between system date and 1st January 2011.
  40. SELECT SYSDATE - to_date('01-jan-2011') FROM DUAL
  41. Display how many employees joined in each month of the current year.
  42. SELECT TO_CHAR(HIRE_DATE,'MM'), COUNT (*) FROM EMPLOYEES 
    WHERE TO_CHAR(HIRE_DATE,'YYYY')= TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(HIRE_DATE,'MM')
  43. Display manager ID and number of employees managed by the manager.
  44.  SELECT MANAGER_ID, COUNT(*) FROM EMPLOYEES GROUP BY MANAGER_ID
  45. Display employee ID and the date on which he ended his previous job.
  46.  SELECT EMPLOYEE_ID, MAX(END_DATE) FROM JOB_HISTORY GROUP BY EMPLOYEE_ID
  47. Display number of employees joined after 15th of the month.
  48. SELECT COUNT(*) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'DD') > 15
  49. Display the country ID and number of cities we have in the country.
  50. SELECT COUNTRY_ID,  COUNT(*)  FROM LOCATIONS GROUP BY COUNTRY_ID
  51. Display average salary of employees in each department who have commission percentage.
  52. SELECT DEPARTMENT_ID, AVG(SALARY) FROM EMPLOYEES 
    WHERE COMMISSION_PCT IS NOT NULL GROUP BY DEPARTMENT_ID
  53. Display job ID, number of employees, sum of salary, and difference between highest salary and lowest salary of the employees of the job.
  54. SELECT JOB_ID, COUNT(*), SUM(SALARY), MAX(SALARY)-MIN(SALARY) SALARY FROM EMPLOYEES GROUP BY JOB_ID
  55. Display job ID for jobs with average salary more than 10000.
  56. SELECT JOB_ID, AVG(SALARY) FROM EMPLOYEES 
    GROUP BY JOB_ID 
    HAVING AVG(SALARY)>10000
  57. Display years in which more than 10 employees joined.
  58. SELECT TO_CHAR(HIRE_DATE,'YYYY') FROM EMPLOYEES 
    GROUP BY TO_CHAR(HIRE_DATE,'YYYY') 
    HAVING COUNT(EMPLOYEE_ID) > 10
  59. Display departments in which more than five employees have commission percentage.
  60. SELECT DEPARTMENT_ID FROM EMPLOYEES 
    WHERE COMMISSION_PCT IS NOT NULL
    GROUP BY DEPARTMENT_ID 
    HAVING COUNT(COMMISSION_PCT)>5
  61. Display employee ID for employees who did more than one job in the past.
  62. SELECT EMPLOYEE_ID FROM JOB_HISTORY GROUP BY EMPLOYEE_ID HAVING COUNT(*) > 1
  63. Display job ID of jobs that were done by more than 3 employees for more than 100 days.
  64. SELECT JOB_ID FROM JOB_HISTORY 
    WHERE END_DATE-START_DATE > 100 
    GROUP BY JOB_ID 
    HAVING COUNT(*)>3
  65. Display department ID, year, and Number of employees joined.
  66. SELECT DEPARTMENT_ID, TO_CHAR(HIRE_DATE,'YYYY'), COUNT(EMPLOYEE_ID) 
    FROM EMPLOYEES 
    GROUP BY DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY')
    ORDER BY DEPARTMENT_ID
  67. Display departments where any manager is managing more than 5 employees.
  68. SELECT DISTINCT DEPARTMENT_ID
    FROM EMPLOYEES
    GROUP BY DEPARTMENT_ID, MANAGER_ID 
    HAVING COUNT(EMPLOYEE_ID) > 5
  69. Change salary of employee 115 to 8000 if the existing salary is less than 6000.
  70. UPDATE EMPLOYEES SET SALARY = 8000 WHERE EMPLOYEE_ID = 115 AND SALARY < 6000
  71. Insert a new employee into employees with all the required details.
  72. INSERT INTO EMPLOYEES  (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE,JOB_ID, SALARY, DEPARTMENT_ID) 
    VALUES (207, 'ANGELA', 'SNYDER','ANGELA','215 253 4737', SYSDATE, 'SA_MAN', 12000, 80)
  73. Delete department 20.
  74. DELETE FROM DEPARTMENTS WHERE DEPARTMENT_ID=20
  75. Change job ID of employee 110 to IT_PROG if the employee belongs to department 10 and the existing job ID does not start with IT.
  76. UPDATE EMPLOYEES SET JOB_ID= 'IT_PROG' 
    WHERE EMPLOYEE_ID=110 AND DEPARTMENT_ID=10 AND NOT JOB_ID LIKE 'IT%'
  77. Insert a row into departments table with manager ID 120 and location ID in any location ID for city Tokyo.
  78. INSERT INTO DEPARTMENTS (150,'SPORTS',120,1200)
  79. Display department name and number of employees in the department.
  80. SELECT DEPARTMENT_NAME, COUNT(*) FROM EMPLOYEES NATURAL JOIN DEPARTMENTS GROUP BY DEPARTMENT_NAME
  81. Display job title, employee ID, number of days between ending date and starting date for all jobs in department 30 from job history.
  82. SELECT EMPLOYEE_ID, JOB_TITLE, END_DATE-START_DATE DAYS 
    FROM JOB_HISTORY NATURAL JOIN JOBS 
    WHERE DEPARTMENT_ID=30
  83. Display department name and manager first name.
  84. SELECT DEPARTMENT_NAME, FIRST_NAME FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID)
  85. Display department name, manager name, and city.
  86. SELECT DEPARTMENT_NAME, FIRST_NAME, CITY FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.EMPLOYEE_ID) JOIN LOCATIONS L USING (LOCATION_ID)
  87. Display country name, city, and department name.
  88. SELECT COUNTRY_NAME, CITY, DEPARTMENT_NAME 
    FROM COUNTRIES JOIN LOCATIONS USING (COUNTRY_ID) 
    JOIN DEPARTMENTS USING (LOCATION_ID)
  89. Display job title, department name, employee last name, starting date for all jobs from 2000 to 2005.
  90. SELECT JOB_TITLE, DEPARTMENT_NAME, LAST_NAME, START_DATE 
    FROM JOB_HISTORY JOIN JOBS USING (JOB_ID) JOIN DEPARTMENTS 
    USING (DEPARTMENT_ID) JOIN  EMPLOYEES USING (EMPLOYEE_ID) 
    WHERE TO_CHAR(START_DATE,'YYYY') BETWEEN 2000 AND 2005
  91. Display job title and average salary of employees
  92. SELECT JOB_TITLE, AVG(SALARY) FROM EMPLOYEES 
    NATURAL JOIN JOBS GROUP BY JOB_TITLE
  93. Display job title, employee name, and the difference between maximum salary for the job and salary of the employee.
  94. SELECT JOB_TITLE, FIRST_NAME, MAX_SALARY-SALARY DIFFERENCE FROM EMPLOYEES NATURAL JOIN JOBS
  95. Display last name, job title of employees who have commission percentage and belongs to department 30.
  96. SELECT JOB_TITLE, FIRST_NAME, MAX_SALARY-SALARY DIFFERENCE FROM EMPLOYEES NATURAL JOIN JOBS WHERE DEPARTMENT_ID  = 30
  97. Display details of jobs that were done by any employee who is currently drawing more than 15000 of salary.
  98. SELECT JH.*
    FROM  JOB_HISTORY JH JOIN EMPLOYEES E ON (JH.EMPLOYEE_ID = E.EMPLOYEE_ID)
    WHERE SALARY > 15000
  99. Display department name, manager name, and salary of the manager for all managers whose experience is more than 5 years.
  100. SELECT DEPARTMENT_NAME, FIRST_NAME, SALARY 
    FROM DEPARTMENTS D JOIN EMPLOYEES E ON (D.MANAGER_ID=E.MANAGER_ID) 
    WHERE  (SYSDATE-HIRE_DATE) / 365 > 5
  101. Display employee name if the employee joined before his manager.
  102. SELECT FIRST_NAME FROM  EMPLOYEES E1 JOIN EMPLOYEES E2 ON (E1.MANAGER_ID=E2.EMPLOYEE_ID) 
    WHERE E1.HIRE_DATE < E2.HIRE_DATE
  103. Display employee name, job title for the jobs employee did in the past where the job was done less than six months.
  104. SELECT FIRST_NAME, JOB_TITLE FROM EMPLOYEES E JOIN JOB_HISTORY  JH ON (JH.EMPLOYEE_ID = E.EMPLOYEE_ID) JOIN JOBS J  ON( JH.JOB_ID = J.JOB_ID) 
    WHERE  MONTHS_BETWEEN(END_DATE,START_DATE)  < 6
  105. Display employee name and country in which he is working.
  106. SELECT FIRST_NAME, COUNTRY_NAME FROM EMPLOYEES JOIN DEPARTMENTS USING(DEPARTMENT_ID) 
    JOIN LOCATIONS USING( LOCATION_ID) 
    JOIN COUNTRIES USING ( COUNTRY_ID)
  107. Display department name, average salary and number of employees with commission within the department.
  108.  
    SELECT DEPARTMENT_NAME, AVG(SALARY), COUNT(COMMISSION_PCT) 
    FROM DEPARTMENTS JOIN EMPLOYEES USING (DEPARTMENT_ID) 
    GROUP BY DEPARTMENT_NAME
  109. Display the month in which more than 5 employees joined in any department located in Sydney.
  110. SELECT TO_CHAR(HIRE_DATE,'MON-YY')
    FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPARTMENT_ID) JOIN  LOCATIONS USING (LOCATION_ID) 
    WHERE  CITY = 'Seattle'
    GROUP BY TO_CHAR(HIRE_DATE,'MON-YY')
    HAVING COUNT(*) > 5
  111. Display details of departments in which the maximum salary is more than 10000.
  112. SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID IN 
    ( SELECT DEPARTMENT_ID FROM EMPLOYEES 
      GROUP BY DEPARTMENT_ID 
      HAVING MAX(SALARY)>10000)
  113. Display details of departments managed by ‘Smith’.
  114. SELECT * FROM DEPARTMENTS WHERE MANAGER_ID IN 
      (SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE FIRST_NAME='SMITH')
  115. Display jobs into which employees joined in the current year.
  116. SELECT * FROM JOBS WHERE JOB_ID IN 
           (SELECT JOB_ID FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')=TO_CHAR(SYSDATE,'YYYY'))
  117. Display employees who did not do any job in the past.
  118. SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID NOT IN 
           (SELECT EMPLOYEE_ID FROM JOB_HISTORY)
  119. Display job title and average salary for employees who did a job in the past.
  120. SELECT JOB_TITLE, AVG(SALARY) FROM JOBS NATURAL JOIN EMPLOYEES 
    GROUP BY JOB_TITLE 
    WHERE EMPLOYEE_ID IN
        (SELECT EMPLOYEE_ID FROM JOB_HISTORY)
  121. Display country name, city, and number of departments where department has more than 5 employees.
  122. SELECT COUNTRY_NAME, CITY, COUNT(DEPARTMENT_ID)
    FROM COUNTRIES JOIN LOCATIONS USING (COUNTRY_ID) JOIN DEPARTMENTS USING (LOCATION_ID) 
    WHERE DEPARTMENT_ID IN 
        (SELECT DEPARTMENT_ID FROM EMPLOYEES 
    	 GROUP BY DEPARTMENT_ID 
    	 HAVING COUNT(DEPARTMENT_ID)>5)
    GROUP BY COUNTRY_NAME, CITY;
  123. Display details of manager who manages more than 5 employees.
  124. SELECT FIRST_NAME FROM EMPLOYEES 
    WHERE EMPLOYEE_ID IN 
    (SELECT MANAGER_ID FROM EMPLOYEES 
     GROUP BY MANAGER_ID 
     HAVING COUNT(*)>5)
  125. Display employee name, job title, start date, and end date of past jobs of all employees with commission percentage null.
  126. SELECT FIRST_NAME, JOB_TITLE, START_DATE, END_DATE
    FROM JOB_HISTORY JH JOIN JOBS J USING (JOB_ID) JOIN EMPLOYEES E  ON ( JH.EMPLOYEE_ID = E.EMPLOYEE_ID)
    WHERE COMMISSION_PCT IS NULL
  127. Display the departments into which no employee joined in last two years.
  128. SELECT  * FROM DEPARTMENTS
    WHERE DEPARTMENT_ID NOT IN 
    ( SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE FLOOR((SYSDATE-HIRE_DATE)/365) < 2)
  129. Display the details of departments in which the max salary is greater than 10000 for employees who did a job in the past.
  130. SELECT * FROM DEPARTMENTS
    WHERE DEPARTMENT_ID IN 
    (SELECT DEPARTMENT_ID FROM EMPLOYEES 
     WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID FROM JOB_HISTORY) 
     GROUP BY DEPARTMENT_ID
     HAVING MAX(SALARY) >10000)
  131. Display details of current job for employees who worked as IT Programmers in the past.
  132. SELECT * FROM JOBS 
    WHERE JOB_ID IN 
     (SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID IN 
            (SELECT EMPLOYEE_ID FROM JOB_HISTORY WHERE JOB_ID='IT_PROG'))
  133. Display the details of employees drawing the highest salary in the department.
  134. SELECT DEPARTMENT_ID,FIRST_NAME, SALARY FROM EMPLOYEES OUTER WHERE SALARY = 
        (SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = OUTER.DEPARTMENT_ID)
  135. Display the city of employee whose employee ID is 105.
  136. SELECT CITY FROM LOCATIONS WHERE LOCATION_ID = 
        (SELECT LOCATION_ID FROM DEPARTMENTS WHERE DEPARTMENT_ID =
                 	(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=105)
    	)
  137. Display third highest salary of all employees
  138. select salary 
    from employees main
    where  2 = (select count( distinct salary ) 
                from employees
                where  salary > main.salary)

PL/SQL Programs

  1. Write a program to interchange the salaries of employee 120 and 122.
  2. Declare
       V_salary_120   employees.salary%type;
    Begin
      Select  salary into v_salary_120
      From employees where  employee_id = 120;
    
      Update employees set salary  = ( select salary from employees where employee_id = 122)
      Where employee_id = 120;
    
      Update employees set salary  =  v_salary_120  Where employee_id = 122;
    
      Commit;
    End;
  3. Increase the salary of employee 115 based on the following conditions: If experience is more than 10 years, increase salary by 20% If experience is greater than 5 years, increase salary by 10% Otherwise 5% Case by Expression:
  4. declare
        v_exp  number(2);
        v_hike number(5,2);
    begin
        select  floor((sysdate-hire_date) / 365 ) into v_exp
        from employees
        where employee_id = 115;
    
        v_hike := 1.05;
    
        case 
          when  v_exp > 10 then
                v_hike := 1.20;
          when  v_exp > 5  then
                v_hike := 1.10;
        end case;
    
        update employees set salary = salary * v_hike 
        where employee_id = 115;
    end;
  5. Change commission percentage as follows for employee with ID = 150. If salary is more than 10000 then commission is 0.4%, if Salary is less than 10000 but experience is more than 10 years then 0.35%, if salary is less than 3000 then commission is 0.25%. In the remaining cases commission is 0.15%.
  6. declare
        v_salary  employees.salary%type;
        v_exp     number(2);
        v_cp      number(5,2);
    begin
        select  v_salary,  floor ( (sysdate-hire_date)/365) into v_salary, v_exp
        from  employees
        where employee_id = 150;
    
        if v_salary > 10000 then
               v_cp := 0.4;
        elsif  v_exp > 10 then
               v_cp := 0.35;
        elsif  v_salary < 3000 then
               v_cp := 0.25;
        else
               v_cp := 0.15;
    
        end if;
    
        update employees set commission_pct = v_cp
        where employee_id = 150;
    end;
  7. Find out the name of the employee and name of the department for the employee who is managing for employee 103.
  8. declare
        v_name     employees.first_name%type;
        v_deptname departments.department_name%type;
    begin
        select  first_name , department_name into v_name, v_deptname
        from  employees join departments using (department_id)
        where employee_id = ( select manager_id from employees    where employee_id = 103);
    
        dbms_output.put_line(v_name);
        dbms_output.put_line(v_deptname);
    
    end;
  9. Display missing employee IDs.
  10. declare
         v_min  number(3);
         v_max  number(3);
         v_c    number(1);
    begin
         select min(employee_id), max(employee_id) into v_min, v_max
         from employees;
    
         for i in  v_min + 1 .. v_max - 1
         loop
               select count(*) into v_c
               from employees 
               where employee_id = i;
    
               if  v_c = 0 then
                    dbms_output.put_line(i);
               end if;
        end loop;
    
    end;
  11. Display the year in which maximum number of employees joined along with how many joined in each month in that year.
  12. declare
    
          v_year  number(4);
          v_c     number(2);
    begin
          select  to_char(hire_date,'yyyy') into v_year
          from  employees
          group by to_char(hire_date,'yyyy')
          having count(*) = 
                 ( select  max( count(*))
                   from  employees
                   group by to_char(hire_date,'yyyy'));
    
          dbms_output.put_line('Year : ' || v_year);
    
          for month in 1 .. 12
          loop
              select  count(*) into v_c
              from employees
              where  to_char(hire_date,'mm') = month and to_char(hire_date,'yyyy') = v_year;
    
              dbms_output.put_line('Month : ' || to_char(month) || ' Employees : ' || to_char(v_c));
    
         end loop;          
    
    end;
  13. Change salary of employee 130 to the salary of the employee with first name ‘Joe’. If Joe is not found then take average salary of all employees. If more than one employee with first name ‘Joe’ is found then take the least salary of the employees with first name Joe.
  14. declare
        v_salary  employees.salary%type;
    begin
         select salary into v_salary
         from employees where first_name = 'Joe';
    
         update employees set salary = v_salary
         where employee_id = 130;
    
    exception
         when no_data_found then
           update employees set salary = (select avg(salary) from employees)
         where employee_id = 130;
    end;
  15. Display Job Title and Name of the Employee who joined the job first day.
  16. declare
           cursor  jobscur is select  job_id, job_title from jobs;
    	   v_name  employees.first_name%type;
    begin
           for jobrec in jobscur
    	   loop
    	         select first_name into v_name
                 from employees
                 where hire_date = ( select min(hire_date) from employees where job_id = jobrec.job_id)
    			 and  job_id = jobrec.job_id;
    
                 dbms_output.put_line( jobrec.job_title || '-' || v_name); 						  
    	   end loop;
    end;
  17. Display 5th and 10th employees in Employees table.
  18. declare
    
         cursor empcur is
            select employee_id, first_name 
            from employees;
    
    begin
         for emprec  in empcur
         loop
             if empcur%rowcount > 4 then
                  dbms_output.put_line( emprec.first_name);
                  exit  when   empcur%rowcount > 10;
             end if;
         end loop;
    
    end;
  19. Update salary of an employee based on department and commission percentage. If department is 40 increase salary by 10%. If department is 70 then 15%, if commission is more than .3% then 5% otherwise 10%.
  20. declare
        cursor empcur is
         select employee_id, department_id, commission_pct
         from employees;
    
        v_hike  number(2);
    begin
    
        for emprec in empcur
        loop
             if  emprec.department_id = 40 then
                  v_hike := 10;
             elsif emprec.department_id = 70 then
                  v_hike := 15;
             elsif emprec.commission_pct  > 0.30 then
                  v_hike := 5;
             else
                  v_hike := 10;
             end if;
    
             update employees set salary = salary + salary * v_hike/100 
             where employee_id = emprec.employee_id;
    
        end loop;
    end;
  21. Create a function that takes department ID and returns the name of the manager of the department.
  22. create or replace function get_dept_manager_name(deptid number)
    return varchar is
    
       v_name  employees.first_name%type;
    begin
       select first_name into v_name
       from employees
       where  employee_id = ( select manager_id from departments where department_id = deptid);
    
       return v_name;
    end;
  23. Create a function that takes employee ID and return the number of jobs done by the employee in the past.
  24. create or replace function get_no_of_jobs_done(empid number)
    return number is
       v_count  number(2);
    begin
       select count(*) into v_count
       from job_history
       where  employee_id = empid;
    
       return v_count;
    end;
  25. Create a procedure that takes department ID and changes the manager ID for the department to the employee in the department with highest salary. (Use Exceptions).
  26. create or replace procedure change_dept_manager(deptid number)
    is
       v_empid  employees.employee_id%type;
    begin
       select employee_id  into v_empid
       from employees
       where  salary = ( select max(salary) from employees where department_id = deptid)
         and department_id = deptid;
    
       update departments set manager_id = v_empid
       where  department_id = deptid;
    end;
  27. Create a function that takes a manager ID and return the names of employees who report to this manager. The names must be returned as a string with comma separating names.
  28. create or replace function get_employees_for_manager(manager number)
    return varchar2
    is
       v_employees varchar2(1000) := '';
       cursor empcur is
          select  first_name from employees 
          where   manager_id = manager;
    begin
    
         for emprec in empcur
         loop
             v_employees :=  v_employees ||  ',' || emprec.first_name;
         end loop;
         -- remove extra  , at the beginning
         return  ltrim(v_employees,',');
    end;
  29. Ensure no changes can be made to EMPLOYEES table before 6am and after 10pm in a day.
  30. create or replace trigger  trg_employees_time_check
    before update or insert or delete
    on employees
    for each row
    begin
       if  to_char(sysdate,'hh24') < 6 or to_char(sysdate,'hh24') > 10 then
             raise_application_error(-20111,'Sorry! No change can be made before 6 AM and after 10 PM');
       end if;
    end;
  31. Create a Trigger to ensure the salary of the employee is not decreased.
  32. create or replace trigger  trg_employees_salary_check
    before update
    on employees
    for each row
    begin
       if  :old.salary > :new.salary then
             raise_application_error(-20111,'Sorry! Salary can not be decreased!');
       end if;
    end;
  33. Create a trigger to ensure the employee and manager belongs to the same department.
  34. Note: This trigger need to read the row that is being modified, which causes mutating problem. The solution to mutating problem is
    explained at : Work around for mutating problem in Oracle Triggers. Please check it out.

  35. Whenever the job is changed for an employee write the following details into job history. Employee ID, old job ID, old department ID, hire date of the employee for start date, system date for end date. But if a row is already present for employee job history then the start date should be the end date of that row +1.
  36.  create or replace trigger trg_log_job_change
    after update of job_id
    on employees
    for each row
    declare
        v_enddate   date;
        v_startdate date;
    begin
       -- find out whether the employee has any row in job_history table
       select max(end_date) into v_enddate
       from job_history
       where employee_id = :old.employee_id;
    
       if v_enddate is null then
          v_startdate := :old.hire_date;
       else
          v_startdate := v_enddate + 1;
       end if;
    
       insert into  job_history values (:old.employee_id, v_startdate, sysdate, :old.job_id, :old.department_id);
    end;
    

    Note: Before testing the above trigger, you need to disable UPDATE_JOB_HISTORY trigger, which is already present in HR account, as it does the same.

[/s2If]

Introduction to Threads with Java (I)

Java Threading provides the ability to perform multiple tasks at the same time and basically the easy to implement asynchronous behavior. Many of the JDK libraries are thread safe, meaning data structures are safe to be accessed by multiple threads at the same time, for instance as all immutable objects are.

The steps to start working with threads in Java are:

  • Write a class that can have multiple threads: this is achieved by implementing the Runable interface (this is preferred over extending the Thread class, as this leaves the chance for other inheritances).
  • Create a thread using an instance of the above class as a parameter.
  • Control and schedule the thread.

The next graph shows the diferents states in a Thread’s lifetime:
Threads Life cycle
In the first scenario, once running the thread can go sleep() (sleeping and waiting to wake up later) or join() (waiting for another thread to join).

But the thread (second scenario), once running, can also start competing against other threads for access to shared resources, then your code should sinchronize all these threads; this makes the actual thread go to the lock pool waiting for the resource to be freed.

The third scenario is when once running, too, the actual thread may want to collaborate with other threads, therefore it has to wait its turn for starting the collaboration, for which at some point it will be notified to start.

The two main approaches to thread scheduling are:

Preemptive: this is the most common approach. It means:

The running state will continue to run until:

  • It explicitly yields the processor, or
  • it ceases to be runnable, usually because of I/O operations or waiting for another resorurce, or
  • a higher priority threads becomes runnable.

The problem here is the actual thread never knows when it will cease to be runnable, and when it happens does not matter if it is in the middle of an operation o whatever the thread is doing at that moment. The developer should consideer the possible consequences.

Non-preemtive (also called cooperative):

  • Any section of code is critical; it tuns until it explicitly yields the processor.
  • The lower bound for event response latency is fixed by the processing time of the slowest task. As such, implementing real-time applications with non-preemtive scheduling is quite difficult.

A simple example

On one hand, next there is a class implementing Runable and the method run(), where a thread will be scheduled to do a task of a math operation, to sleep or to yield (33% of probability for each task) in each one of the iterations of a bucle (this ia just a simple example):

package com.orboan.threads.simple;

import java.util.Random;

public class Worker implements Runnable {

	private String name;
	private boolean isKilled = false;

	public Worker(String name) {
		this.name = name;
	}

	@Override
	public void run() {
		Random random = new Random();
		int r = random.nextInt(100);
		for (int i = 0; i < 10 && !isKilled; i++) {
			if (r > 66) {
				System.out.println(this.name + ">> yield n:" + i);
				Thread.yield();
				System.out.println(this.name + "<< yield n:" + i);
			} else if (r > 33) {
				System.out.println(this.name + ">> sleep n:" + i);
				try {
					Thread.sleep(random.nextInt(1000));
				} catch (InterruptedException e) {
					e.printStackTrace();
				}
				System.out.println(this.name + "<< sleep n:" + i);
			} else {
				System.out.println(this.name + ">> work n:" + i);
				for (int j = 0; j < 1000000; j++) {
					Math.pow(42, j);
				}
				System.out.println(this.name + "<< work n:" + i);				
			}
			r = random.nextInt(100);
		}
		System.out.println(this.name + " done.");
	}
	
	public void kill() {
		this.isKilled = true;
	}
}

As you may have noticed, this class uses the kill() method to comunicate the thread to terminate. When that notification is sent and the isKilled boolean set to true, there are no more tasks to be executed by the thread, which then terminates by itself.

On the other hand, the scheduler is implemented next:

package com.orboan.threads.simple;

public class Driver {

	public static void main(String[] args) {
		Thread t = null;
		Worker[] workers = new Worker[5];
		for (int i = 0; i < 5; i++) {
			workers[i] = new Worker("worker" + i);
			t = new Thread(workers[i]);
			if (i == 2 || i == 4) {
				System.out.println("- worker" + i + ": " + t.getPriority());
				t.setPriority(t.getPriority() + 2);
			}
			t.start();
		}
		try {
			t.join();
		} catch (InterruptedException e) {
			e.printStackTrace();
		}
		for(int i = 0; i < 5; i++) {
			workers[i].kill();
		}
		System.out.println("Driver done...");
	}
}

First, five threads are created, one per each of the workers also created. Then the second and fourth workers are given a higher priority (preemtive approach), and finally the workers are started within their threads.

After this, the last thread is called to join the current one (the one from which the call to the join is made). Joining a thread means that one waits for the other to end, so that generally speaking, you can safely access its result or continue after both have finished their jobs. In this case, the bucle for killing the threads and the “Driver done…” message won’t be carried out until the fifth thread (number 4) is finished.

You can test the code and the effects by (un)commenting out the relevant pieces, code which you can download as an Eclipse project, and see for instance how the message “Driver done…” appears at the beginning if no join is called, and appears right after the worker4 is done, otherwise.

Threads Code (simple, daemon)

A method to test join()

public void executeMultiThread(int numThreads)
   throws Exception
{
    List threads = new ArrayList();

    for (int i = 0; i < numThreads; i++)
    {
        Thread t = new Thread(new Runnable()
        {
            public void run()
            {
                // do your work
            }
        });

        // System.out.println("STARTING: " + t);
        t.start();
        threads.add(t);
    }

    for (int i = 0; i < threads.size(); i++)
    {
        // Big number to wait so this can be debugged
        // System.out.println("JOINING: " + threads.get(i));
        ((Thread)threads.get(i)).join(1000000);
    }
}

Java Basics (2): Reference vs Primitive Types

The Java language provides us with two data types: primitive types and reference types. The primitive types are eight built-in data types, namely:

Java Primitive Types
Primitive Type Size Range
byte 8 bits -128 to 127
short 16 bits -32768 to 32767
int 32 bits -231 to 231 – 1
long 64 bits -263 to 263 – 1
float 32 bits see here
double 64 bits see here
char 16 bits ‘\u0000′ to ‘\uffff’ (0 to 65535)
boolean unspecified true or false



Primitive types are allocated in memory by declaring them in your code. For instance:

int n;
double d;

Thus, the compiler allocates 32 bits for the variable n and 64 for the variable d. And these allocated spaces in memory can only, and only, store a value of the same type. n can only hold an int and d can only hold a double. For example:

n = 123;
d = 2.99e8;

On the other hand, reference types have a very different behaviour. Reference types include class types, interface types and array types. Unlike primitive types that hold their values in the memory where the variable is allocated, reference types do not hold the value but the memory address where this value is actually stored. In other words, references point to an object (an instantiated class, an array) by storing the memory address where the object is located. This is the concept named “pointer” which other languages (C, C++) offers as a programmer dependant feature. Java automatically manage pointers through reference types. Java does not allow a programmer to access a physical memory address in any way, allows only the use of references to gain access to the fields and methods of the referenced objects.

Once a reference is declared, a new value can be assigned to it as a new object created using the new keyword:

//Reference declaration
java.util.Date today;
//Assigning a new object to the declared reference
today = new java.util.Date();

Another way for assigning new values to some specific reference types is shown next:

//Declaring references
String greeting;
int [] powersOfTwo; //an array of integers
//Assigning new object (new values) to the declared references
greeting = "Hi, have a nice day!"
powersOfTwo = {1, 2, 4, 8, 16, 32, 64, 128};

The following example can clarify, if needed, how reference types work. As it is shown below, a reference not only can be assigned to a new object but also to another reference of a compatible type (that is, of the same type or when one of them is a child from the other):

java.util.ArrayList<Integer> array1 = new java.util.ArrayList<Integer>();
java.util.ArrayList<Integer> array2 = array1
array1.add(new Integer(123));
array2.add(new Integer(456));
for ( int i = 0; i < array2.size(); i++) {
System.out.println(array1.get(i));
}

This code adds the integer 123 to the ArrayList array1, and adds the integer 456 to the ArrayList array2, but as array2 points to the same ArrayList object as array1, the code comiles succesfully and its output will be:

123
456

The null type

The null type is a special data type in Java. It does not have any name, thus it’s not possible to declare a variable of this type. It is used only to assign a reference to the null type. Primitives can not be assigned to null, only references:

String greeting = null;
int [] powersOfTwo = null;
int n = null; //does not compile

JavaBeans, Retrospection & Events

In this post I’m going to show you an example on how Retrospection works in Java. This example makes use of the Event framework as the Retrospection will be done using Bound properties in a JavaBean. Remember, Bound properties are those a bean has that provide property change notifications to listeners (getters and setters with an event associated with them).

Bound properties in JavaBeans

Let’s start by showing you how a Bound property looks like. The starting point is the next JavaBean:

package com.orboan.records.model;
import java.io.Serializable;

// Every JavaBean has to be serializable

public class BeanRecord implements Serializable {

   private static final long serialVersionUID = 1L;

   private String email;
   private String name;
   private String userId;
   private String password;

   // Every JavaBean has to have a default non-parametrized constructor
   public BeanRecord(){};

   public BeanRecord(String email, String name, 
      String userId, String password) {
      super();
      this.email = email;
      this.name = name;
      this.userId = userId;
      this.password = password;
   }

   /* Simple properties */
   public String getEmail() {
      return email;
   }
   public void setEmail(String email) {
      this.email = email;
   }
   public String getName() {
      return name;
   }
   public void setName(String name) {
      this.name = name;
   }
   public String getUserId() {
      return userId;
   }
   public void setUserId(String userId) {
      this.userId = userId;
   }
   public String getPassword() {
      return password;
   }
   public void setPassword(String password) {
      this.password = password;
   }

   @Override
   public String toString() {
      return "BeanRecord" + this.hashCode() +
      " [email=" + email + ", name=" + name + ", userId=" + 
      userId + ", password=" + password + "]";
   }
}

Now, let’s add the code to start firing off events when properties values change. The simple properties will turn into bound properties by adding that code to them. Then, every time a value changes in the bound property, a value change notification will be sent to the registered listeners.

For adding / removing the listeners it’s better to delegate this task to a PropertyChangeSupport object, as it is provided by the JDK API thus it probably is more reliable and for sure less time-consuming than creating our own. So, let’s create (in the BeanRecord JavaBean) a PropertyChangeSupport object and two methods for delegating the registration of listeners:

//Passing in this object as the source of the events (this)
private PropertyChangeSupport changeSupport = 
   new PropertyChangeSupport(this);

//Delegating the registration of listeners to the changeSupport object
public void addPropertyChangeListener(PropertyChangeListener listener) {
   changeSupport.addPropertyChangeListener(listener);
}

public void removePropertyChangeListener(PropertyChangeListener listener) {
   changeSupport.removePropertyChangeListener(listener);
}

Thus, from now on any class that implements the PropertyChangeListener interface can be (de)registered using these two methods.

But as seen in the constructor used for creating the PropertyChangeSupport object, the source where the events come from is this JavaBean itself (by using the autoreference this), thus the bean has to have the code that triggers the event for the value changes. By adding code that triggers events for values changes the simple properties, then, turn into bound properties:

/*By adding code that triggers events for values changes
the simple properties turn into bound properties*/
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		Object old = this.email;
		this.email = email;
		changeSupport.firePropertyChange("email", old, this.email);
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		Object old = this.name;
		this.name = name;
		changeSupport.firePropertyChange("name", old, this.name);
	}
	public String getUserId() {
		return userId;
	}
	public void setUserId(String userId) {
		Object old = this.userId;
		this.userId = userId;
		changeSupport.firePropertyChange("userId", old, this.userId);
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		Object old = this.password;
		this.password = password;
		changeSupport.firePropertyChange("password", old, this.password);
	}

Notice that the first parameter in the

changeSupport.firePropertyChange(String nameProperty, Object oldValue, Object newValue)

method is just the name we give to the event, and must match the name of the property. This name will be used later by the listeners to know which value has changed.

At this point, we have a JavaBean with bound properties triggering events for each value change, and that also has two methods for (de)registering the listeners.

Building a GUI by Retrospection of the model

Before creating and registering any listener for the events triggered by the BeanRecord JavaBean, let’s create a GUI (Swing) with a 2-way flow for events and listeners:

  • Through this GUI we’ll see the values changes in the bean’s properties: in this case, when the BeanRecord bean’s properties are updated (e.g. via console input or via another GUI) the events are triggered and the new values sent to the listener (it’ll be named CModelController, as the BeanRecord plays the role of the model), which will update the view with the new values received.
  • This view will be used, too, to change those values. To accomplish that, we’ll have to add a listener (named CJPanelBeanRecord -where C stands for Controller) for the view which in its turn will update the BeanRecord’s property values.

You may suspect there is a loop, here. Keep reading.

retrospection project

The simple way in creating this view could be adding one text field for each one of the properties BeanRecord has. But… what if at some point in the future somebody decides BeanRecord has to have more properties? Does this necessarily imply that the view of this model has to be modified, too? Not, by all means, if the view is constructed via Restropection. Let’s see how this works.

One possible way in constructing the view is by creating a class that extends the JPanel. We call it VJPanelBeanRecord, where V stands for View, and that has a suffix for explicitily naming the associated model (BeanRecord). The model has to be passed to this view in order the view be dynamically built based upon the properties of the model. We are not dealing in this post with Swing, so don’t expect here any explanations on that. The view is shown next:

package com.orboan.records.views;

import com.orboan.records.model.BeanRecord;

public interface IViewBeanRecord {
	public BeanRecord getModel();
	public void setModel(BeanRecord model);
}
package com.orboan.records;

import java.awt.GridBagConstraints;
import java.awt.GridBagLayout;
import java.awt.Insets;
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyChangeEvent;
import java.beans.PropertyChangeListener;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;

import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;

public class VJPanelBeanRecord extends JPanel implements IViewBeanRecor {

	private static final long serialVersionUID = 1L;

	// The model of this view is the BeanRecord
	private BeanRecord model;

	private GridBagConstraints c;

	// Default constructor
	public VJPanelBeanRecord() {
		this.initComponents();
	}

	private void initComponents() {
		this.setLayout(new GridBagLayout());
		this.c = new GridBagConstraints(0, 0, 1, 1, 0, 0,
				GridBagConstraints.EAST, 10, new Insets(4, 4, 4, 4), 10, 10);
	}

	public BeanRecord getModel() {
		return model;
	}

	public void setModel(BeanRecord model) {
		try {
			this.model = model;
			BeanInfo beanInfo = Introspector.getBeanInfo(model.getClass());

			// Dynamically creating a GUI containing fields
			// for each String property from the model (BeanRecord)
			for (PropertyDescriptor d : beanInfo.getPropertyDescriptors()) {
				if (d.getPropertyType() == String.class) {
					String value = 
(String) d.getReadMethod().invoke(model, (Object[]) null);
			/* Once I have this value I create a JTextField to place it */
					JTextField jtf = new JTextField(20);
					jtf.setText(value);
					JLabel jl = new JLabel(d.getName() + ": ");
					c.gridx = 0;
					this.add(jl, c);
					c.gridx = 1;
					this.add(jtf, c);

					c.gridy++;
				}
			}

		} catch (IntrospectionException 
| IllegalAccessException
| IllegalArgumentException 
| InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

Let’s focus on line 45 from the latter code. The Introspector class provides us with the method getBeanInfo to aquire at runtime all the info from the JavaBean passed as an argument. This info is given encapsulated within an object of type BeanInfo. Then, the info contained in the BeanInfo object can be used to construct the view. As you may suspect, this BeanInfo interface is used to create GUIs as builder tools for IDEs, as those GUIs are dynamically created upon the properties beans have.

Once the BeanInfo object is created the properties can be accessed by calling the method getPropertyDescriptors(), that returns an array containing all available PropertyDescriptor objects, and iterating over it (line 49). Then, in line 50, the property type is checked (once more, at runtime) as we only want to show in the view the properties of type String. To obtain at runtime the current value of each property of type String, the method getReadMethod() returns the getter and then invoke(obj,args) calls that getter (line 52). As there are no additional arguments, there’s only need to pass the model object, so the second parameter is actually a null reference.

Thus, iterating over all the PropertyDescriptor objects we can obtain each of the properties’ values from the BeanRecord bean and put them in the JTextField fields (line 58 – we’ll modify this later, keep reading). Notice JLabels are also created (line 56) obtaining their names from the PropertyDescritor objects, too (by using the getName() method).

Adding a listener to the Model

First, let’s extend the model with a data structure so all beans can be stored in memory:

package com.orboan.records.model;

public interface IModel {

	public void putBeanRecord(BeanRecord beanRecord);

        //email is the key
	public BeanRecord getBeanRecord(String email);

	public Iterable allRecords();
}
package com.orboan.records.model;

import java.util.Map;
import java.util.TreeMap;

public class ModelInMemory implements IModel {

	// Delegating the implementation for storing the records
	// to a Map
	private Map<String, BeanRecord> map = new TreeMap<String, BeanRecord>();

	private static ModelInMemory obj = null;

	private ModelInMemory() {

	}

	public static ModelInMemory getInstance() {
		if(obj == null) {
			obj = new ModelInMemory();
		}
		return obj;
	}

	@Override
	public void putBeanRecord(BeanRecord beanRecord) {
		System.out.println("Saving record: " + beanRecord);
		map.put(beanRecord.getEmail(), beanRecord);
	}

	@Override
	public BeanRecord getBeanRecord(String email) {
		BeanRecord record = map.get(email);
		System.out.println("Getting a record by email  " + email + " -> "
				+ record);
		return record;
	}

	@Override
	public Iterable allRecords() {
		return map.values();
	}

}

Now it’s time to add a listener for the events triggered by the BeanRecord Bound properties. The listener is, in fact, the controller:

package com.orboan.records.controllers;

import java.beans.PropertyChangeEvent;
import java.beans.PropertyChangeListener;

import com.orboan.records.model.BeanRecord;
import com.orboan.records.model.IModel;
import com.orboan.records.model.ModelInMemory;

public class CModelController implements PropertyChangeListener {	

	private IModel model = null;

	public CModelController() {	
		this.model = ModelInMemory.getInstance();
	}

	public CModelController(BeanRecord record){
		this();
		this.model.putBeanRecord(record);
	}

	public void addRecord(BeanRecord record) {
		if(this.model != null) {
			this.model.putBeanRecord(record);
		}
	}

	public void registerAllBeans() {
		for(BeanRecord bean : this.model.allRecords())
			bean.addPropertyChangeListener(this);
	}

	@Override
	public void propertyChange(PropertyChangeEvent event) {
		BeanRecord model = (BeanRecord)event.getSource();
                String newValue = "unknown";
		if ("email".equals(event.getPropertyName())) {
			newValue = model.getEmail();
		} else if ("name".equals(event.getPropertyName())) {
			newValue = model.getName();
		} else if ("userId".equals(event.getPropertyName())) {
			newValue = model.getUserId();
		} else if ("password".equals(event.getPropertyName())) {
			newValue = model.getPassword();
		}
	}
}

Notice that the

event.getPropertyName()

returns the property name that was passed as a first argument in the

changeSupport.firePropertyChange(propertyName, oldValue, newValue)

method (see bound properties in the BeanRecord). This way, the listener can be aware of which one of the properties has changed its value. In this case, the new value is stored in the field newValue. But the code does not include updating the view, yet.

Ok, now it’s gonna be tricky because the JTextField fields were updated with the properties’ values using JTextField.setText(String value). We had (see the above code for the VJPanelBeanRecord class):

public void setModel(BeanRecord model) {
	try {
		this.model = model;
		BeanInfo beanInfo = Introspector.getBeanInfo(model.getClass());

		// Dynamically creating a GUI containing fields
		// for each String property from the model (BeanRecord)
		for (PropertyDescriptor d : beanInfo.getPropertyDescriptors()) {
			if (d.getPropertyType() == String.class) {
				String value = 
(String) d.getReadMethod().invoke(model, (Object[]) null);
		/* Once I have this value I create a JTextField to place it */
				JTextField jtf = new JTextField(20);
				jtf.setText(value);
				JLabel jl = new JLabel(d.getName() + ": ");
				c.gridx = 0;
				this.add(jl, c);
				c.gridx = 1;
				this.add(jtf, c);
				c.gridy++;
			}
		}
	} catch (IntrospectionException 
| IllegalAccessException
| IllegalArgumentException 
| InvocationTargetException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

(At line 14 we are using JTextField.setText() to set the value for the JTextfield objects)

BUT if what we want is the values of the text fields beeing updated automatically when the bean’s properties change, instead of having to click on a button, we must use the JTextField’s model itself, which is a Document object. All JTextComponent components (JTextField, JEditorPane and JTextArea) have a Document object as a model, which can be retrieved by calling the JTextComponent.getDocument() method.

A way to see how the Document works as a model for the JTextField is to take a look at the implementation of the JTextField.setText(String value) method:

public void setText(String t) {
    try {
        Document doc = getDocument();
        if (doc instanceof AbstractDocument) {
            ((AbstractDocument)doc).replace(0, doc.getLength(), t,null);
        }
        else {
            doc.remove(0, doc.getLength());
            doc.insertString(0, t, null);
        }
    } catch (BadLocationException e) {
    UIManager.getLookAndFeel().provideErrorFeedback(JTextComponent.this);
    }
}

Therefore, to accomplish this kind of AJAX effect on JTextField components the VJPanelBeanRecord.setModel(BeanRecord model) method has to be rewritten as follows:

public void setModel(BeanRecord model) {
	try {
		documents.clear();
		this.removeAll();
		this.model = model;
		BeanInfo beanInfo = Introspector.getBeanInfo(model.getClass());

		String value, name;
		Document doc;
		/*
	        * Dynamically creating a GUI containing fields for each String
		* property from the model (BeanRecord)
		*/
		for (PropertyDescriptor d : beanInfo.getPropertyDescriptors()) {
			if (d.getPropertyType() == String.class) {
				value = 
                                (String) d.getReadMethod().invoke(model, (Object[]) null);
				name = d.getName();
				// Once I have this value I have to create a place to put
				// it:
				JTextField jtf = new TextField(20);
				JLabel jl = new JLabel(name + ": ");				
				doc = jtf.getDocument();
				((AbstractDocument)jtf.getDocument()).replace(0, doc.getLength(), value, null);
				documents.put(name, doc);		
				c.gridx = 0;
				this.add(jl, c);
				c.gridx = 1;
				this.add(jtf, c);
				c.gridy++;
			}
		}

	} catch (IntrospectionException | IllegalAccessException
			| IllegalArgumentException 
                        | InvocationTargetException 
                        | BadLocationException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}

At line 24 there is the replacement of the current text value for the new one from the bean’s getters (lines 6, 14 and 17, by Retrospection as explained above).

Line 4, this.removeAll(), is to make all current fields are removed every time a new model is set, to proceed from scratch to build the new view.

Line 3, documents.clear(), and line 25, documents.put(name, doc), refer to a Map named documents, which is added as a field to the VJPanelBeanRecord class:

private Map<String, Document> documents = new HashMap<String, Document>();

(This field has to be added to the VJPanelBeanRecord class).

This Map is used to store the JTextField objects’ model thus it will be accessed from the CModelController to automatically update the view when an event from the BeanRecord bound properties is triggered. So, next let’s add the code to update the view in the CModelController listener. To make it so, we have to access the documents Map in VJPanelBeanRecord from the CModelController. Once the documents Map is accessed, the document associated with the JTextField associated with the BeanRecord bound property which is triggering the event will be get from the map by using the event.getPropertyName() method, that returns the name of the bound property which value has changed, as it plays the role of the map’s key.

The design decision here is to access the documents Map through the CJPanelBeanRecord instead of directly from the VJPanelBeanRecord, where CJPanelBeanRecord is the controller for the view VJPanelBeanRecord.

Thus, with these additions, the CModelController.propertyChange(PropertyChangeEvent event) method becomes:

public class CModelController implements PropertyChangeListener {

	CJPanelBeanRecord viewController = null;
...
@Override
public void propertyChange(PropertyChangeEvent event) {		
	BeanRecord model = (BeanRecord) event.getSource();
	if (viewController != null && viewController.getView() != null)) {
	        String newValue = "unknown";
		//Retrieve the model			
		if ("email".equals(event.getPropertyName())) {
			newValue = model.getEmail();
		} else if ("name".equals(event.getPropertyName())) {
			newValue = model.getName();
		} else if ("userId".equals(event.getPropertyName())) {
			newValue = model.getUserId();
		} else if ("password".equals(event.getPropertyName())) {
			newValue = model.getPassword();
		}
		try {
			//Update the view
			Document doc = viewController.getView().getDocuments()
					.get(event.getPropertyName());
			((AbstractDocument) doc).replace(0, doc.getLength(), newValue,
					null);
		} catch (BadLocationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
}

Adding a listener to the View

Next, the CJPanelBeanRecord controller is shown. As it has to deal with the events triggered from the view, when the contents of the Document objects associated with JTextField components change, it has to implement the DocumentListener, therefore override the corresponding methods. Notice that while the CModelController.propertyChange(PropertyChangeEvent event) method retrieves the model first and next updates the view, right now the CJPanelBeanRecord.update(DocumentEvent event) has to retrieve the view, first, and then update the model (remember the 2-way flow stated before?):

package com.orboan.records.controllers;

import java.util.Map;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.event.DocumentEvent;
import javax.swing.event.DocumentListener;
import javax.swing.text.BadLocationException;
import javax.swing.text.Document;

import com.orboan.records.model.BeanRecord;
import com.orboan.records.views.IViewBeanRecord;

public class CJPanelBeanRecord implements DocumentListener {

	private static CJPanelBeanRecord obj = null;

	private IViewBeanRecord view = null;

	private CJPanelBeanRecord() {		
	}

	public static CJPanelBeanRecord getInstance() {
		if(obj == null) {
			obj = new CJPanelBeanRecord();
		}
		return obj;
	}

	public void start() {
		if (this.view != null) {
			BeanRecord model = this.view.getModel();
			if (model != null) {				
				JFrame f = new JFrame();
				f.setContentPane((JPanel) this.view);
				f.pack();
				f.setVisible(true);
			}
		} else {
			System.err.println("You must set the view, first. "
					+ "Call the view setter (setView(IViewBeanRecord view)).");
		}
	}

	public IViewBeanRecord getView() {
		return view;
	}

	public void setView(IViewBeanRecord view) {
		if(this.view != null) {
			this.deRegisterAllDocuments();
		}
		this.view = view;
	}

	@Override
	public void changedUpdate(DocumentEvent arg0) {
		update(arg0);
	}

	@Override
	public void insertUpdate(DocumentEvent arg0) {
		update(arg0);
	}

	@Override
	public void removeUpdate(DocumentEvent arg0) {
		update(arg0);
	}	

	private void update(DocumentEvent event) {
		Document doc = event.getDocument();
		try {   //Retrieve the view
			String newValue = doc.getText(0, doc.getLength());
                        //Update the model
			BeanRecord model = this.view.getModel();
			Map<Document, String> documentNames = this.view.getDocumentNames();
			if ("email".equals(documentNames.get(doc))) {
				model.setEmail(newValue);
			} else if ("name".equals(documentNames.get(doc))) {
				model.setName(newValue);
			} else if ("userId".equals(documentNames.get(doc))) {
				model.setUserId(newValue);
			} else if ("password".equals(documentNames.get(doc))) {
				model.setPassword(newValue);
			}
		} catch (BadLocationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void registerAllDocuments() {
		Map<Document, String> documentNames = this.view.getDocumentNames();
		for(Document doc : documentNames.keySet()) {
			doc.addDocumentListener(this);
		}
	}

	private void deRegisterAllDocuments() {
		Map<Document, String> documentNames = this.view.getDocumentNames();
		for(Document doc : documentNames.keySet()) {
			doc.removeDocumentListener(this);
		}
	}
}

As you can see there is another helper map,

Map<Document, String> documentNames

which also has to be declared and set in the view (the VJPanelBeanRecord class).

Registering the DocumentListener listeners will be done by calling the CJPanelBeanRecord.registerAllDocuments() method.

Next, the code for the view is shown once more, with the additions regarding the Map:

public class VJPanelBeanRecord extends JPanel implements IViewBeanRecord {

	private static final long serialVersionUID = 1L;	

	// The model of this view is the BeanRecord
	private BeanRecord model;

	private Map<String, Document> documents = new HashMap<>();
	private Map<Document,String> documentNames = new HashMap<>();

	private GridBagConstraints c;

	// Default constructor
	public VJPanelBeanRecord() {
		this.initComponents();
	}
....
....
	public void setModel(BeanRecord model) {
		try {
			documents.clear();
			documentNames.clear();
			this.removeAll();
			this.model = model;
			BeanInfo beanInfo = Introspector.getBeanInfo(model.getClass());

			String value, name;
			Document doc;
			/*
			 * Dynamically creating a GUI containing fields for each String
			 * property from the model (BeanRecord)
			 */
			for (PropertyDescriptor d : beanInfo.getPropertyDescriptors()) {
				if (d.getPropertyType() == String.class) {
					value = (String) d.getReadMethod().invoke(model,
							(Object[]) null);
					name = d.getName();
					// Once I have this value I have to create a place to put
					// it:
					JTextField jtf = new JTextField(20);
					JLabel jl = new JLabel(name + ": ");
					doc = jtf.getDocument();
					((AbstractDocument)doc).replace(
							0, doc.getLength(), value, null);
					documents.put(name, doc);
					documentNames.put(doc, name);

					c.gridx = 0;
					this.add(jl, c);
					c.gridx = 1;
					this.add(jtf, c);
					c.gridy++;
				}
			}

		} catch (IntrospectionException | IllegalAccessException
				| IllegalArgumentException 
| InvocationTargetException 
| BadLocationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

If the app is executed at this point an exception is launched due to a thread issue when attempting to modify the contents of any of the text fields in the view. This happens because the GUI is notifying the model and the model is notifying back the GUI, entering an infinite loop in a thread.

Exception in thread "AWT-EventQueue-0" java.lang.IllegalStateException: Attempt to mutate in notification
at javax.swing.text.AbstractDocument.writeLock(Unknown Source)
.....

A simple way to deal with it is to make the GUI listener run in a different thread than the one of the model controller:

public class CJPanelBeanRecord implements DocumentListener {

	private Thread guiThread = null;
....
....
	private void update(DocumentEvent event) {
		this.guiThread = Thread.currentThread();
		Document doc = event.getDocument();
		try {
			String newValue = doc.getText(0, doc.getLength());
			BeanRecord model = this.view.getModel();
			Map<Document, String> documentNames = this.view.getDocumentNames();
			if ("email".equals(documentNames.get(doc))) {
				model.setEmail(newValue);
			} else if ("name".equals(documentNames.get(doc))) {
				model.setName(newValue);
			} else if ("userId".equals(documentNames.get(doc))) {
				model.setUserId(newValue);
			} else if ("password".equals(documentNames.get(doc))) {
				model.setPassword(newValue);
			}
		} catch (BadLocationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.guiThread = null;
	}
....
}
public class CModelController implements PropertyChangeListener {
        CJPanelBeanRecord viewController = null;
....
....
        @Override
	public void propertyChange(PropertyChangeEvent event) {		
		BeanRecord model = (BeanRecord) event.getSource();
		if (viewController != null && viewController.getView() != null && 
				viewController.getGuiThread() != Thread.currentThread()) {
			String newValue = "unknown";
			//Retrieve the model			
			if ("email".equals(event.getPropertyName())) {
				newValue = model.getEmail();
			} else if ("name".equals(event.getPropertyName())) {
				newValue = model.getName();
			} else if ("userId".equals(event.getPropertyName())) {
				newValue = model.getUserId();
			} else if ("password".equals(event.getPropertyName())) {
				newValue = model.getPassword();
			}
			try {
				//Update the view
				Document doc = viewController.getView().getDocuments()
						.get(event.getPropertyName());
				((AbstractDocument) doc).replace(0, doc.getLength(), newValue,
						null);
			} catch (BadLocationException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}

If the app is now run by launching two instances of the view and also two instances of the controllers (listeners) but using one unique model (one BeanRecord) then all changes on one view are propagated to the model, and the changes on the model are then propagated to the other view. Here is the code as an Eclipse project so you can try it and see how it works:

JavaBeans_Retrospection_Events_code

Java Basics (1): Packages, imports and Classpath

This post starts the Java Basics series, which is targeted to developers preparing for the SCJP o just for Java newbies, besides it can also be used as a brief repository for quick basic references.
This first post explains how the CLASSPATH environment variable works, as many newbies often have problems compiling or running even basic programs due to a classpath misconfiguration. A brief and clear explanation about pakages and imports is also provided.

Packages and directories structure

Java classes must be uniquely identified. This does not mean that their file names have to be different, but when two or more classes have the same name, their namespaces must be different. To be more precise, what has to be different are their fully qualified names (namespace plus class name -the class name must match its file name). A class namespace is provided by its package, and this package is just the directory path where the java file has to be placed, with a syntax rule of directories being dot separated instead of \ (Windows) or / (Linux) separated. When a class belongs to a package, its name actually becomes its fully qualified name, i.e. includes the package. An example is shown next.

As a fictional example, let’s say we’ve created a class named Scientific when developing a management application for scientific employees at Alba sincroton (located in Catalonia). We are an organization which Internet domain is science.cat (cat stands for the catalan culture on Internet). Then we decided a directory structure like this:

cat/science/cells/entities/employees/Scientific.java

(it’s usual to use the organization’s domain name – science.cat in this example – in inverted order as the directories names.)

This means the package for this class is (which is its namespace):

cat.science.cells.entities.employees

And its fully qualified name is:

cat.science.cells.entities.employees.Scientific

(note that the extension .java is not used.)

Thus, the very first line in the Scientific java class file must be (without considering any comment lines):

package cat.science.cells.entities.employees;

If our application has also to deal with the management of scientific devices or intruments, and we think in another class named Scientific (for instance, inherited from a generic Device class instead from an Employee class), then we are allowed to use that name again, but we must change the namespace, so the two classes have to be located in different packages. For instance, we can place the new class in:

cat/science/cells/entities/devices/Scientific.java

so its very first line has to be now:

package cat.science.cells.entities.devices;

The import statement

When a class is instantiated from another class, this can be done by using only the instantiated class name only if it belongs to the same package as the instantiating class. If the two classes belong to different packages, when creating a new object it is neccessary to use the fully qualified name to identify the class itself and its location. For example:

package cat.science.cells.entities.employees;
public class ShiftWork {
...
Scientific physicist = new Scientific ();
...
}

(it’s Ok ’cause both classes – the instatiated and the one where we are instantiating it from- belong to the same package)

package cat.science.cells.entities.employees;
public class Repair {
...
cat.science.cells.entities.devices.Scientific
    oscilloscope =
    new cat.science.cells.entities.devices.Scientific ();
...
}

(it’s ok ’cause the fully qualified name is used as both classes belong now to different packages).

When dealing with few lines of code, writing down the fully qualified name everytime is just a little annoying, but when developing a medium or large application this need turns into a very time-consuming issue not acceptable by any nowadays time-pressed programmer / development project. But there is the import keyword to make us life easier. This keyword is used to import a single class (using its fully qualified name) or, when used with the wildcard (*), to import an entire package:

package cat.science.cells.entities.employees;
import cat.science.cells.entities.devices.Scientific;
public class Repair {
...
Scientific oscilloscope = new Scientific ();
...
}

The import keyword actually overrides the cat.science.cells.alba.entities.employees.Scientific.java class so the Scientific class currently used is the imported one (over the one belonging to the same package).

In case we want to use more than one classes from a specific package, we should use the wildcard (*) with the import statement, instead of importing them one by one. For instance, we need to use the File, FileReader, BufferedReader and the IOException classes, which all belong to the java.io package. Then:

package cat.science.cells.entities.employees;
import java.io.*;
import cat.science.cells.entities.devices.Scientific;
public class Repair {
...
Scientific oscilloscope = new Scientific ();
...
public String PrintRepairReport (String filename) {
    File file = new File(filename);
    FileReader filereader = null;
    try {
        filereader = new FileReader(file);
        BufferedReader in = new BufferedReader(filereader);
        System.out.println(in.ReadLine());
    } catch (IOException e) {
        e.printStackTrace();
    }
}

The CLASSPATH environment variable

The mentioned directory structure in the above example, cat/science/cells/entities/… where has to be located? The answer is wherever you want… but since the parent directory for the root package path (../cat) is subject to no condition, you must tell to the JVM where to look when compiling and running. In other words, you have to inform the JVM where the package directory structure is located, otherwise it won’t be capable of compiling neither running the application.

A common technique is to put your source files in a dirctory named /src and your bytecode (compiled files) in a directory named /build.

An example is shown to illustrate how the CLASSPATH variable works:

Supose the location for the project is:

/usr/projects/java/desktop/albaproject

(Unix)

c:\Documents and Settings\projects\java\desktop\albaproject

(Windows)

thus, the complete path for the Scientific.java class file is (note that the next examples are suposed to form a unique line):

/usr/projects/java/desktop/albaproject/src/cat/science/cells/
entities/employees/Scientific.java

(Unix)

c:\Documents and Settings\projects\java\desktop\albaproject\src\
cat\science\cells\entities\employees\Scientific.java

(Windows).

Compiling can be done using the javac command. Besides other parameters, you should indicate the output directory (-d option) for bytecode (.class files) and, of course, the Java files to compile:

javac -d /usr/projects/java/desktop/albaproject/build ./src/cat/science/cells/
entities/employees/Scientific.java

(from the /usr/projects/java/desktop/albaproject directory; otherwise the full path for the Scientific.java file must be specified)

When compiling one or few Java files there’s no issue in putting them next to the javac command, but when you have a long list of Java files to compile, the best option is writing the entire list (via ls unix command or equivalent) in a file and use the @ option with javac.

For instance, supose you want to compile the following Java files:

./src/cat/science/cells/entities/employees/Scientific.java
./src/cat/science/cells/entities/employees/Engineer.java
./src/cat/science/cells/entities/employees/Administrator.java
...

and once they are all written in a file named (for example) src-list, which is located in /usr/projects/java/desktop/albaproject/ you can compile them with:

javac -d /usr/projects/java/desktop/albaproject/build @./src-list

(from the /usr/projects/java/desktop/albaproject directory)

The CLASSPATH environment variable refers to the path on your file system where your .class files are saved. In our example the .class files are saved in /usr/projects/java/desktop/albaproject/build as specified as the output directory when compiling. Once compiled, you should want to run the application, so the JVM has to be informed of where the .class files are being located when compiled. The CLASSPATH variable can be specified as an environment variable, which has to include the .class files (compiled bytecode) location:

setenv CLASSPATH /usr/projects/java/desktop/albaproject/build

(Unix systems)

set CLASSPATH="c:\Documents and Settings\projects\java\desktop\albaproject\build";

(Windows systems)

We can add other path locations as needed, for instance:

setenv CLASSPATH .:/usr/projects/java/desktop/albaproject/build:/usr/projects/
j2ee/build

(Unix systems)

set CLASSPATH=.;"c:\Documents and Settings\projects\java\
desktop\albaproject\build";"c:\Documents and Settings\projects\
j2ee\build";

(Windows systems)

(it’s usual and a good practice to include always the current working directory).

As an alternative, which overrides the CLASSPATH environment value, it is possible to specify the classpath as a parameter for the java command (when running the application):

java -classpath .:/usr/projects/java/desktop/albaproject/build      
 cat.science.cells.AlbaManagement

When running Java Applications it’s needed to use a valid entry point: a special method called main. Thus, the class cat.science.cells.AlbaManagement (you have to use the fully qualified name) has to include such method. The signature of main must look like this:

public static void main (String [] args)

The only changes you are allowed to make to this signature are the name of the parameter args, which can be arbitrary, and the order of public and static. So, the following is also a valid signature for main:

static public void main (String [] whatever)

The -classpath option can also be used with the javac command when compiling in cases when class libraries (usually archived in JAR files) may be needed for a successfull compilation. In these cases the path to the jar file is used:

javac -d /usr/projects/java/desktop/albaproject/build  
 -classpath .:./lib/swing.jar @./src-list

A note on JAR files: bytecode can be stored in archived and compressed files known as JAR files (Jar is short for Java Archive). The compiler and the JVM (Java Virtual Machine) can find bytecode in JAR files without needing to uncompress them. JAR files are the most common way to distribute Java code. The tool jar.exe (or its equivalent for Unix systems) from the JDK (Java Development Kit) is used for creating and extracting JAR files. What is important to take into account is that JAR files not only archives the .class files, but either the directory structure. Thus, if you want to archive and compress as a JAR the AlbaProject bytecode, then the resulting JAR file will include all .class files from the ./cat directory and all its subdirectories. And as for the non compressed bytecode, you don’t have to include the package directories tree in the classpath, but only the location for the JAR file (instead of the root directory where the package directories tree starts).