SE 452 Fall 2001/2002
Lecture Notes Set 5
JDBC
- Java Database Connectivity
- Java API for accessing databases or any kind of tabular data
- Access to virtually any data source and run on any platform with a JVM
- Possible to write an industrial strength database applications using an all Java API
- Versions:
- JDBC 1.2 - basic data access functionality
- JDBC 2.0 (JDK 1.2) - added advanced functionality such as scrollable record
sets and the ability to updated rows programmatically by using methods, not SQL
JDBC Driver Types
- JDBC-ODBC bridge
- Provides JDBC access via ODBC drivers
- Can use with any database that has ODBC drivers
- Native API partly Java driver
- Converts JDBC calls into calls on the native client API of a specific DBMS
- JDBC-Net pure Java driver
- Translates JDBC calls into a DBMS-independent network protocol which is
translated to a DBMS protocol by a server
- Native protocol pure Java driver
- Converts JDBC calls directly into the network protocol used by a DBMS
Using JDBC
- Packages:
- java.sql - JDBS core API, in J2SE (required)
- javax.sql - JDBC Standard Extension API, in J2EE (optional)
- Steps:
- Load JDBC driver
- Estabilsh a connection with a data source
- Send queries and update statements
- Process the results
Loading JDBC Drivers
- Driver classes:
- sun.jdbc.odbc.JdbcOdbcDriver (JDBC-ODBC)
- COM.cloudscape.core.JDBCDriver (Cloudscape)
- Loading drivers:
- Explicit loading: Class.forName(<driver class name>)
- Setting system property: jdbc.drivers
- can be set at the command line by using the -D<name>=<value>
switch to java executable
- can be set programmatically using System.setProperty(String name,
String value)
- colon separated list of driver class names
- DriverManager class attempts to load the classes specified in jdbc.drivers
when it is initialized
Establishing Connections
DriverManager.getConnection(url);
DriverManager.getConnection(url, userName, password)
- All DriverManager methods are declered static
- URL
- Access - jdbc:odbc:<ODBC data source name>
- Cloudscape - jdbc:cloudscape:<database name>
Creating Statements
- Connection class
- Statement createStatement() - creates a Statement object for sending SQL
statements to database
- CallableStatement perpareCall(String sql) - crates a CallableStatement object
for calling database stored procedures
- PreparedStatement prepareStatement(String sql) - crates a PreparedStatement
object for sending parameterized SQL statements to the database
Sending Queries and Updates
- Statement class
- ResultSet executeQuery(String sql) - executes a SQL statement that returns a
single ResultSet object
- int executeUpdate(String sql) - executes a SQL INSTERT, UPDATE, or DELETE
statement and returns number of row affected by the statement
Processing the Results
- ResultSet class
- table of data representing a database result set
- maintains a cursor pointing to its current row of data
- Methods:
- boolean next()
- moves the cursor down one row from its current position
- initially the cursor is positioned before the first row
- the first call makes the first row the current row
- returns false if there are no more rows
- to iterate a result set use the following code:
while (resultSet.next()){
// process the current row
}
- To get data in the current row use: Type getType(<column index>)
or Type getType(<column name>)
- get the data at the specified column of the current row
- index of the first column is 1
Data Types
- BigDecimal - DECIMAL or NUMERIC
- Blob - BLOB
- booean - BIT
- byte - TINYINT
- bytes (byte[]) - BINARY or VARBINARY
- Clob - CLOB
- Date - DATE
- double - DOUBLE or FLOAT
- float - REAL
- int - INTEGER
- long - BIGINT
- Object - STRUCT or JAVA OBJECT
- short - SMALLINT
- Time - TIME
- TimeStamp - TIMESTAMP
Building a User Database (Cloudscape 3.6)
Making Database programs portable
- Use a property file to specifiy database and platform specific properties
(db.properties)
- Property files consist of a list of properties
- Each property is a colon separated dey-value pair of strings - key:value
- Use java.util.Properties to load properties files (void load(InputStream in))
- Database properties:
- database - name of the database product (ie Cloudscape, Access, etc)
- jdbcDriver - name of the JDBC driver
- dataSource - name of the data source (either database name or ODBC datasource)
- userName (optional) - user name for accessing database
- password (optional) - password for accessing the database
- connection.create (optional) - create database (true or false)
- connection.shutdown (optional) - shutdown database (true or false)
- home.property (optional) - system property name for setting database home directory
- home.defalut (optional) - default database home directory
- Java class - DBConnector
- Singleton class
- You only want one instance of each database config available to users
- Ask the singleton for an instance of itself to enforce this
- Constructor is protected so you have to ask for an instance
- Static methods:
- DBConnector getInstance()
- void setDBPropertiesFileName(String name)
- Instance methods:
- void setHomeDir(String homeDir)
- void startup()
- Connection getConnection()
- int executeUpdate(String sql)
- ResultSet executeQuery(String sql)
- void shutdown()
- Using the DBConnector class
A database backed web app using JDBC 1.0
- Example
- HTML
- Java
- db.OscarDB
- Java application
- Create database: Oscar
- Create two tables: BestPictures and FavoritePictures
- db.Oscar
- Java servlet
- Show client's vote
- Register vote and update tables
- Show the current vote tally
A database backed web app using JDBC 2.0
- Uses update functionality of a RecordSet instead of SQL statements
- Needs to have a JDBC 2.0 compatible driver (Cloudscape is not 2.0 compliant)
- Example
- HTML
- Java
- db.OscarDB
- Java application
- Create database: Oscar
- Create two tables: BestPictures and FavoritePictures
- db.Oscar
- Java servlet
- Show client's vote
- Register vote and update tables
- Show the current vote tally
ServletContext class
- Servlet can use this to communicate with its servlet container
- One context per web app per Java Virtual Machine
- Some methods:
- String getRealPath(String path) - returns OS path (ie c:\test\...)
for a give virtual path (ie WEB_INF\servlets\...)
- String getServerInfo() - get info on server