Accessing Databases using Java: JDBC
Now, that you know to get at the data you have to use SQL commands/querries, you need to solve the problem of gaining access or in other words connecting to the database so you can issue these SQL commands. For us in Internet Programming, we will explore how we can do this with Java and JDBC. There are other solutions, some companies like Oracle will have their own special version of SQL (e.g. PL/SQL) and server software that can be invoked to connect to the database and issue SQL commands.
Java and JDBC connecting to a Database
from www.oracle.com
Features of Java-JDBC Database System Architecture
- Easily Change Database** you are using
if:
- It supports SQL (which most do).
- Has "Java Driver".
- You need Driver on each client machine.
**This should be compared to using proprietary langauges provided by the Database manufacturer which would require a major re-writting of the code used.
Driver Manager
Driver
Connection
Statement
Metadata
ResultSet
|
STEPS in Java Code to Connect
(regardless of driver choice)
The Process (see below for example)
|
EXAMPLE for ORACLE
Oracle JDBC Drivers: OCI versus Thin
- You will use OCI for Java Applications
- You will use Thin for Java Applets or Applications.
What you need to do:
1) Have your database administrator load the Java JDBC driver for the database you are using.(you will use OCI for a Java Application and Thin for an Applet...see above) 2) You with your DBA (database administrator) set up your database table(s) and enter in the data as desired (note you can also if you wish to have data entry take place through an interface to your Java program). 3) Now write the code. Steps in the code (SEE EXAMPLE):
|
|||
Running Compiled Example Above:
|
Specifying Database you wish to connect to via JDBC
Uses Internet-standard URLs to identify database connections.
URL Specification will be given to you in documentation or examples provided by the JDBC driver provider.
Below are examples for Oracle that we will use.
OPTION 1: DriverManager.getConnection ("jdbc:oracle:oci8:@labdb", "userid", "password");
|
OPTION 2: DriverManager.getConnection ("jdbc:oracle:oci8:@host : port : sid", "userid", "password");
|
-
-
ALTERNATIVES FOR CONNECTING
Connecting Directly from Applet to DB through Oracle's Net8 Connection Manager
from www.oracle.com- Gets around SECURITY problem that Web-Server and Database Server Must be the same machine.
- Oracle Connection Manager is a multipurpose networking service for Oracle environments. Included with Oracle8 Enterprise Edition, Oracle Connection Manager provides client connection concentration, client connection access control, and multiprotocol connectivity.
- Using the Connection Manager requires two steps:
- Installing and running the connection manager on the Web Server host.
- Using a connect string in the applet that routes through the Connection Manager.
- Using the proper connect string is critical to
access a remote database through Connection Manager. The
following connect string will connect a JDBC applet to a
database on host
DB_server, at port 1521 and SID ORCL: - Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:" + "@(description=(address_list="
+ " (address=(protocol=tcp)(host=CM_server
)(port=1610))" + " (address=(protocol=tcp)(host= DB_server)(port=1521)))" + " (source_route=yes)" + " (connect_data=(sid=orcl)))", "scott", "tiger");
- The first element in the "address_list" entry represents the connection to the Connection Manager.
- The second element represents the destination database. The "source_route" entry tells JDBC that it should pass the second address to the Connection Manager. Note that if the source_route parameter is omitted, JDBC will treat the second address as a fall back address in case the first one does not respond.
- Connection conn = DriverManager.getConnection
("jdbc:oracle:thin:" + "@(description=(address_list="
+ " (address=(protocol=tcp)(host=CM_server
GOOD THING: One Oracle Connection Manager connection represents only one socket used, even if the Connection Manager is serving 100 clients. So, when multiple Oracle Connection Managers are employed, thereby connecting multiple clients, the total number of clients connecting to a single database grows exponentially. The benefits of using Oracle Connection Manager include support for large client populations and the ability to identify and monitor the actual users connected. Additionally Oracle Connection Manager is an ideal implementation for very many "heavy" or continuous client applications.
Also, see new optional JDBC package that is part of J2EE.