1

Software Architecture

EMS Write-up

Basic structure

1. Introduction

Implementation of application is in Java for employee database and it show updates in Oracle
database which is used as backend in our case and it performs following basic operations for the
database. The validations are being performed at the frontend.

1. Insert
2. Update
3. Delete
4. Select

2. Design

The design of the employee database system is entirely based on the requirements of the
assignment.

It fulfills user’s need of insert, update, select and also delete the records that are stored in
Oracle 10 g database.

For database related operation such as insert/update/delete/select, we are using SQL queries.

Data base table structure:

Employee table

1. Employee id – Number(Primary key)

2. Employee name – Varchar2
3. Email – Varchar2
4. Phone Number – Number
5. Location – Varchar2
 2

Spring - JDBC Framework:

While working with database using plain old JDBC, it becomes cumbersome to write
unnecessary code to handle exceptions, opening and closing database connections etc. But
Spring JDBC Framework takes care of all the low-level details starting from opening the
connection, prepare and execute the SQL statement, process exceptions, handle transactions
and finally close the connection.

So what we have done is just define connection parameters and specify the SQL statement to
be executed and do the required work for each iteration while fetching data from the database.

Spring JDBC provides several approaches and correspondingly different classes to interface with
the database. I'm going to take classic and the most popular approach which makes use of
SimpleJdbcTemplate class of the framework. This is the central framework class that manages
all the database communication and exception handling.

Spring SimpleJdbcTemplate

SimpleJdbcTemplate combines the most frequently used operations of JdbcTemplate and

NamedParameterJdbcTemplate.

The SimpleJdbcTemplate has all the features of old JdbcTemplate and also support some
features of Java 5 i.e varargs and autoboxing. It best suited when you need not to access all the
feature of JdbcTemplate. It has a simpler API and basically construct to support java 5.Thats
why it has more method to exploit varargs.

The getJdbcOperations() method is used to access those methods which are defined in
JdbcTemplate. You have to call these method on SimpleJdbcTemplate. The main drawback is
that you need to cast these methods as the methods on JdbcOperations interface are not
generic.

In JdbcTemplate query(), we need to manually cast the returned result to desire object type,
and pass an Object array as parameters. In SimpleJdbcTemplate, it is more user friendly and
simple.

Basic steps to use a database in Spring MVC application:

 Configuring Data Source

 Data Access Object (DAO)
 Result Set Extractor
 Row Mapper
 3

 Execute SQL Statements

Configuring Data Source

Let us create a database table Employee in our database EMS.

Now we need to supply a DataSource to the SimpleJdbcTemplate so it can configure

itself to get database access. We can configure the DataSource in the XML file with a
piece of code as shown below:

Data Access Object (DAO)

DAO stands for data access object which is commonly used for database interaction.
DAOs exist to provide a means to read and write data to the database and they
should expose this functionality through an interface by which the rest of the
application will access them.

The Data Access Object (DAO) support in Spring makes it easy to work with data
access technologies like JDBC, Hibernate, JPA or JDO in a consistent way.

ResultSetExtractor

We can easily fetch the records from the database using query() method
of SimpleJdbcTemplate class where we need to pass the instance of
ResultSetExtractor.

ResultSetExtractor interface can be used to fetch records from the database. It

accepts a ResultSet and returns the list.
 4

RowMapper

Like ResultSetExtractor, we can use RowMapper interface to fetch the records from
the database using query() method of SimpleJdbcTemplate class. In the execute of
we need to pass the instance of RowMapper now.

Executing SQL statements

Let us see how we can perform CRUD (Create, Read, Update and Delete) operation
on database tables using SQL and SimpleJdbcTemplate object.
 5

Pre-Requisite:
 Core JAVA Programming
 Oracle 10g Database

JDBC Architecture:

The JDBC API supports both two-tier and three-tier processing models for database access but
in general JDBC Architecture consists of two layers:

 JDBC API: This provides the application-to-JDBC Manager connection.

 JDBC Driver API: This supports the JDBC Manager-to-Driver Connection.

The JDBC API uses a driver manager and database-specific drivers to provide transparent
connectivity to heterogeneous databases.

The JDBC driver manager ensures that the correct driver is used to access each data source. The
driver manager is capable of supporting multiple concurrent drivers connected to multiple
heterogeneous databases.

Following is the architectural diagram, which shows the location of the driver manager with
respect to the JDBC drivers and the Java application:

Common JDBC Components:

The JDBC API provides the following interfaces and classes:

 6

 DriverManager: This class manages a list of database drivers. Matches connection

requests from the java application with the proper database driver using
communication subprotocol. The first driver that recognizes a certain subprotocol under
JDBC will be used to establish a database Connection.
 Driver: This interface handles the communications with the database server. You will
interact directly with Driver objects very rarely. Instead, you use DriverManager objects,
which manages objects of this type. It also abstracts the details associated with working
with Driver objects
 Connection : This interface with all methods for contacting a database. The connection
object represents communication context, i.e., all communication with database is
through connection object only.
 Statement : You use objects created from this interface to submit the SQL statements to
the database. Some derived interfaces accept parameters in addition to executing
stored procedures.
 ResultSet: These objects hold data retrieved from a database after you execute an SQL
query using Statement objects. It acts as an iterator to allow you to move through its
data.
 SQLException: This class handles any errors that occur in a database application.

4. Implementation

For development of code we have used IDE as Eclipse Luna that provides us execution of code
as well as Oracle database connection.

Steps for executing the code:

 Create a new EMS Oracle database

OR

Use an existing oracle database

If you are using an existing database, update oracle.properties file with

connection details.
 7

 Create the table Employee in Oracle database

Run the following code:

create table employee(employeeID number(8) PRIMARY KEY,employeeName

varchar2(50),emailaddress varchar2(50), phonenumber number(10),location
varchar2(20));

 Sample Employee table

 Extract EMS.zip.

Extract the EMS.zip and import the code in Eclipse.

 Execute the code.

For database we have used Oracle 10g Enterprise Edition

For connecting java application with the oracle database, you need to follow 5 steps to perform
database connectivity. In this example we are using Oracle10g as the database. So we need to
know following information’s for the oracle database:

1. Driver class: The driver class for the oracle database is oracle.jdbc.driver.OracleDriver.
2. Connection URL: The connection URL for the oracle10G database is
jdbc:oracle:thin:@127.0.0.1:1521:EMS where jdbc is the API, oracle is the database,
thin is the driver, localhost is the server name on which oracle is running, we may also
use IP address, 1521 is the port number and EMS is the Oracle Database name. You may
get all these information from the tnsnames.ora file.
 8

3. Username: The default username for the oracle database is system.

4. Password: Password is given by the user at the time of installing the oracle database.

6. Conclusions

Employee Management system can help administration department to keep the records of the
employees up to date and by using a popular object oriented language one can easily modified
it and make the code accordingly.

Oracle 10g is robust database system by which it easy to maintain the database.

Practical issues

Java IDE or complier required

To execute the code it is necessary to have one java complier and must have an oracle 10g
database.

System requirement

Operating system: Mac OS (x or latest), Window XP or latest.

Ram: 512MB or more.

Hard disk: 50 GB or more

Database: Oracle 10g or latest.etc

Summary

We have used Java as frontend and Oracle database as backend.

Following basic operations are performed on database through frontend.

 Insert
 Update
 Modify
 Delete
 This database system is java based user interactive system which accepts the input(s)
from end user of database
 9

REFERNCES

 JDBC Data Access API – JDBC Technology Homepage

http://java.sun.com/products/jdbc/index.html

 JDBC Database Access – The Java Tutorial

http://java.sun.com/docs/books/tutorial/jdbc/index.html

 JDBC Documentation

http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/index.html

 java.sql package

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/package-summary.html

 JDBC Technology Guide: GettingStarted

http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html

 JDBC API Tutorial and Reference (book)

http://java.sun.com/docs/books/jdbc/