Transcript Lecture Notes

JDBC
In This Class We Will Cover:
•
•
•
•
•
What SQL is
What ODBC is
What JDBC is
JDBC basics
Introduction to advanced JDBC topics
SQL
• SQL is a standardized language used to create, manipulate,
examine, and manage relational databases. We will not
extensively cover SQL, although a very basic SQL Primer
and SQL Resources are provided.
SQL
• However, you should understand the following:
– A database is essentially a smart container for tables.
– A table is a container comprised of rows.
– A row is (conceptually) a container comprised of
columns.
– A column is a single data item having a name, type, and
value.
SQL
• While you should review the definitions and understand
the important differences, initially you can use the
following analogs:
–
–
–
–
A database approximates a file system;
a table approximates a file;
a row approximates a record or structure;
and a column approximates a field or variable.
SQL
• A single SQL statement can be very expressive and can
initiate high-level actions, such as sorting and merging, on
an entire set of data.
• SQL was standardized in 1992 so that a program could
communicate with most database systems without having
to change the SQL commands. However, you must connect
to a database before sending SQL commands, and each
database vendor has a different interface to do so, as well
as different extensions of SQL. Enter ODBC.
ODBC
• ODBC (Open Database Connectivity), a C-based interface
to SQL-based database engines, provides a consistent
interface for communicating with a database and for
accessing database metadata (information about the
database system vendor, how the data is stored, and so on).
• Individual vendors provide specific drivers or "bridges" to
their particular database management system.
Consequently, thanks to ODBC and SQL, you can connect
to a database and manipulate it in a standard way. It is no
surprise that, although ODBC began as a PC standard, it
has become nearly an industry standard.
ODBC
• Although SQL is well-suited for manipulating databases, it was not
designed to be a general application language; rather, it was intended
to be used only as a means of communicating with databases.
• Another more general and complete programming language is needed
to host and feed SQL statements to a database and process results for
data manipulation, visual display, or report generation.
• Unfortunately, you cannot easily write a program that will run on
multiple platforms, even though the database connectivity
standardization issue has been largely resolved. For example, if you
wrote a database client in C++, you might have to totally rewrite the
client for another platform; that is to say, your PC version would not
run on a Macintosh.
• Enter the Java programming language and JDBC.
JDBC
• A Java program, written properly and according to
specification, can run on any Java technology-enabled
platform without recompilation.
• The Java programming language is completely specified
and, by definition, a Java technology-enabled platform
must support a known core of libraries. One such library is
the java.sql package or JDBC, which you can think of as a
portable version of ODBC, and is itself a major standard.
• Using the Java programming language in conjunction with
JDBC provides a truly portable solution to writing
database applications.
JDBC
• Note: While portable applications and a standard database
interface are major achievements, keep in mind that, for
historical, competitive, and sometimes nonsensical
reasons, the various databases are not completely
standardized. This may mean that you have to aim for a
lowest common denominator in terms of capabilities or
build-in adjustments for specific databases, even on the
same platform. This problem remains whether you use
standard SQL, ODBC, JDBC, or other solutions.
JDBC
• A JDBC driver is a class that implements the JDBC Driver interface
and understands how to convert program (and typically SQL) requests
for a particular database. Clearly, the driver is what makes it all work.
• There are four different driver types, which are discussed in the JDK
(Java Development Kit) documentation at JDBC Driver Types. This
course uses type 4 drivers because of their nearly zero installation
requirements and dynamic nature.
• Another driver type may make more sense for your particular project.
Most database vendors now provide drivers to implement the JDBC
API for their particular systems. These are generally provided free of
charge. Third party drivers are also available, ranging in cost from free
to very expensive. For links to JDBC driver resources, see Specific
Information and the other Resources.
JDBC
• JDBC 1.0.
– The JDBC 1.0 API provided the basic framework for data access,
consisting primarily of the following interfaces and classes:
• Driver.
• DriverManager.
• Connection.
• Statement.
• PreparedStatement.
• CallableStatement.
• ResultSet.
• DatabaseMetaData.
• ResultSetMetaData.
• Types.
JDBC
• As you will see in this course, you pass a Driver to
the DriverManager and then obtain a
Connection.
• A Statement, PreparedStatement, or
CallableStatement is then created and used to
update the database or execute a query.
• A query returns a ResultSet containing the
requested data, which is retrieved by Type.
DatabaseMetaData and ResultSetMetaData
classes are available to provide information about
a database or a ResultSet.
JDBC Basics
• To access a database using JDBC you must:
–
–
–
–
–
–
create a database and establish it as a datasource on your system.
install a database driver.
set up a connection.
execute a statement.
iterate through a result set for queries.
close the connection.
• Note:
– You must exit out of the DBMS to run your program. If you have
the db open in Access, your Java program will not be able to
access it and it will return an error.
Putting It All Together
• See
www2.bc.edu/~bernier/MC697/LectureNotes/JDBCTest.ja
va
Some Advanced JDBC Topics
• Connection Pooling
• Callable Statements
• CLOBS and BLOBS