Efficient, Flexible Data Access in Java at The Home Depot

1
Efficient, Flexible Data Access in Java at The
Home Depot

• June 27, 2002

2
Java at The Home Depot

• Adopted Java in 1997
• Many key systems have used Java since
• Make use of web (servlets) and thick client (AWT
  and JFC) for user interfaces
• Use JDBC and web services (HTTP) for data access
• Utilize a n-tier development model (all database
  access occurs from the application server)

3
n-Tier Model
Servlet Engine
Application Server
Database Server
Servlet
Skeleton
Stub
Program Logic
Heavy Client
JDBC Server
JDBC Client
Stub
DBMS
Client
Client Tier
Logic Tier
Data Tier
4
JDBC

• Versions of the JDBC Specification
• Version 1
• Initial Release
• Base Functionality
• Version 2
• Scrollable Cursors
• Rowsets
• Connection Pooling
• Home Depot uses version 2 drivers, but the data
  architecture was built on version 1 of JDBC

• Version 3
• SavePoints
• Improved Connection Pooling

5
JDBC

• Different Types of Drivers

Type 1
Type 2
Type 4
Type 3
6
Goals of Home Depots Data Access Architecture

• Improve Connection Speed
• Avoid Stored Procedures
• Make Data Access Secure
• Be Driver Agnostic

7
Improving Connection Speed

• Creating a JDBC connection is very expensive
• A Connection Pool allows applications to reuse
  connections, so only the early accesses take the
  connection creation performance hit.
• We needed this functionality in JDBC version 1,
  so we built it ourselves.

8
Home Depots Connection Pool

• All connection requests go through the pooling
  mechanism
• Low Watermark - Always create a new connection
• High Watermark - Never deny a connection request,
  but destroy the next released connection
• Handle high volume corporate servers as well as
  lower volume store environment

9
Home Depots Connection Pool
Connections are created to satisfy request, but
then destroyed when no longer used
Connection 9
Connection 8
High Watermark
Connection 7
Connection 6
Connections are created if needed, but most
requests are fulfilled using existing connections
Connection 5
Connection 4
Low Watermark
Connection 3
Connections are always created. This is usually
only used during initial startup
Connection 2
Connection 1
10
Avoid Stored Procedures

• Corporate-wide direction to avoid using stored
  procedures
• We needed a mechanism to improve performance for
  frequently used queries
• Used prepared statements through the connection
  pool

11
Prepared Statements

• When requesting a connection from the pool, the
  developer can identify a prepared statement
• The pool then attempts to retrieve a connection
  with the statement already prepared
• If no connection has the statement prepared, the
  pool prepares the statement on the connection to
  be returned

12
Make Data Access Secure

• To use JDBC, you must supply a user id and
  password to access the database
• Hard coding the user id and password in source
  code is NOT secure
• Adding the user id and password to a properties
  or config file is NOT secure
• Use a secure location, such as an LDAP directory,
  to store the connection information.

13
Secure Data Access with LDAP

• Create a connection identifier (APP_ID) to be the
  key in LDAP
• Each APP_ID record contains all the information
  needed to create the JDBC connection, such as
• user id/password
• database hostname
• database instance
• driver information

14
Secure Data Access with LDAP

• When creating a connection the APP_ID is supplied
  to the connection manager
• The connection manager does all the conversing
  with LDAP
• All the developer needs to know is the APP_ID,
  instead of a bunch database parameters

15
Be Driver Agnostic

• Home Depot has a number of different databases as
  well as a number of different JDBC drivers
• Need to switch between difference databases and
  drivers without changing code

16
APP_ID Isolation

• The APP_ID mechanism stores the database and
  driver information
• Changing the APP_ID record in LDAP means that no
  code needs to be changed in order to make the
  necessary modifications

17
Putting It All Together

• Basic Steps
• 1. Get a connection
• 2. Create SQL Statement or Prepare SQL Statement
• 3. Execute SQL Statement
• 4. Process Results

18
Putting It All Together
Connection Manager Proxy
getConnectRecord (Application Profile)
Connect Record
getConnection (ConnectRecord)
Data Store Connection
OR
getPreparedStatement (SQL)
executeSelect (SQL)
PrepStatement
executeSelect ()
ResultSet
19
Putting It All Together
20
Questions?