Database Programming
At this point, weve learned how to:
Specify a database (E-R, UML, relational) Express queries and other operations in the relational database model (relational algebra) Dene and manipulate a real instance of that database (SQL, PostgreSQL)
Of course, your end-users wont know SQL; they will
interact with your database the way they interact with all other software through an application
User Interface user
Application Logic
This is where weve been working.
Web Application
Standalone Application
Database
This is what were talking about now.
We develop an application using the same tools for any
other application nothing different here
But, since our application needs to hit the database, In our case, we need programmatic access to a
relational database and SQL
part of our code needs some gateway into that world
Database Programming Interfaces
Embedded SQL: an SQL layer on top of an existing
language; a preprocessor snags the embedded SQL statements and converts them into lower-level programming calls A cursor object represents a current retrieved result, which the rest of the code can access Queries directly expressed (canned) in the code, which leads us to
Dynamic SQL: allows us to form queries on the y
A lot of these things have been developed over the
years: Embedded SQL has been implemented for C, Cobol, Pascal, Java (SQLJ), PL/I, Fortran, etc. Two major dynamic SQL implementations:
ODBC (Open Database Connectivity) for the C family of languages, as well as C# and Visual Basic JDBC (formerly known as Java Database Connectivity) for Java
In this class, we focus on:
Database applications written in Java (Swing, Web, plain text, whatever) JDBC, using the driver for PostgreSQL
How JDBC Works
JDBC is a set of Java interfaces that determine how
older versions Java software can interact with a relational database
Standard-issue with Java ! 1.4; optional extension in To see the full set of interfaces, look up the java.sql and
javax.sql packages
Database vendors (PostgreSQL, MySQL, Oracle,
Sybase) provide implementations of these interfaces (sometimes called drivers)
Java code
Typically a one-time thing and generally the only time that you make a specific reference to the underlying database implementation.
setup/connection
JDBC interfaces
JDBC implementation (vendor-specic)
relational DBMS
For PostgreSQL specically:
Download the driver from http://jdbc.postgresql.org Make sure its in your classpath For questions that are specic to the PostgreSQL JDBC
implementation, see the Documentation section will sufce
Otherwise, any generic JDBC resources on the Web
Key Concepts
Well generally let the code and API do the talking here, but here are some broad strokes:
You reach the database through an explicit Connection,
acquired either through the JDBC DriverManager or a JDBC DataSource (recommended, JDBC 2 and later) facilities are available for parameterizing them
Queries are prepared as straightforward strings; Query results are returned as ResultSet objects, over
which your code would iterate in order to get to the underlying tuples
Implementation Issues
The underlying database is a nite resource your
code needs to clean up after itself (i.e., close connections after youre done with them)
Connection pools help with this; available if you use DataSource
Unless you really really have to, you shouldnt be
married to a specic database implementation as long as its relational, your code should work that differ; otherwise, a lot of the code is functionally identical this observation leads us to
From application to application, it is really the queries
The Data Access Object (DAO) Design Pattern
Ultimately, JDBC is a programming interface but
there is a right way and a wrong way to use it approach that hides the underlying database implementation from the rest of the code
The Data Access Object design pattern is a design The application should worry about what data it needs
and when, but not how the data is retrieved or stored corej2eepatterns/Patterns/DataAccessObject.html
Ofcial Web site: http://java.sun.com/blueprints/
DataSource: the underlying database implementation DataAccessObject (DAO): abstracts your applications data needs for a specic category of data [Data]TransferObject (DTO): the object representation of this data BusinessObject: the rest of the application
