Understanding data access architecture in a client server environment

With so many applications being created that use databases, its very important to understand how data access happens.. at least at a high level. What are the various components that are involved in this process. The architecture will help us understand how seamless integration with databases is achieved. This post will explain JDBC, ODBC, database drivers etc.

The diagram below explains basic design:

Database Connectivity APIs (eg. JDBC, ODBC)
These APIs (specifications) are provided and supported by software vendors (JDBC by Oracle and ODBC by Microsoft). They help in programmatic interaction with various databases. The beauty of this architecture is that developers do not write the program to a particular database. JDBC creates a loose coupling between the java program and the type of database used. This mechanism makes the java client applications immune to change in the databases offered by different vendors. 

The APIs help in

• Connecting to a database
• Execute queries to retrieve, create, modify or delete data.
• Close a database connection

Driver Manager
The ODBC or JDBC APIs interact with respective driver managers to transparently connect to database. The driver manager uses a specific database driver to connect to a specific type of database.

Database Drivers
Every database vendor has to provide database drivers adhering to the Database APIs. For example, if Microsoft SQL Server needs to be connected from a java client, then a JDBC driver for MS SQL Server has to be created by the vendor. JDBC drivers are specifically designed to interact with their respective DBMSs.

Abstract Classes vs Interfaces

In order to decide on what to choose between an abstract class or an interface, its important to know the strengths and weaknesses of each. Based on the needs that match the requirements, once can choose between interfaces and abstract classes.

Abstract Classes	Interfaces
May contain implementation code. However, it must have at least one abstract member.	Must not contain any implementation code
A class may inherit from a single base class	A class may implement multiple interfaces
Members have to have access modifiers	Members are automatically public so should not have access modifiers. In fact declaring access modifiers with members would lead to compile time exception.
May contain ·         Constructors ·         Destructors ·         Fields ·         Properties ·         Methods ·         Events ·         Indexers	May contain ·         Properties ·         Methods ·         Indexers ·         events