Transcript JDBC

Overview
1. What is JDBC?
2. The JDBC-ODBC Bridge
3. JDBC Pseudocode
4. simpJDBC.java
5.
Meta Data
6.
Books.mdb as an ODBC
Data Source
What is JDBC?



JDBC provides a set of classes for Java with
a standard SQL database access interface.
Allow programs to access to a wide range of
relational databases which follow the ANSI
SQL-2 standard
Provides an API for database "drivers" to
make actual connections and transactions to
databases.
JDBC in Use
Java
program
JDBC
connectivity
data processing
utilities
jdbc-odbc
bridge
driver
for Oracle
driver
For MySQL
odbc
driver
The JDBC-ODBC Bridge



ODBC (Open Database Connectivity) is
a Microsoft standard from the mid
1990’s.
It is an API that allows C/C++
programs to execute SQL inside
databases
ODBC is supported by many products.

The JDBC-ODBC bridge allows Java
code to use the C/C++ interface of
ODBC
– it means that JDBC can access many
different database products

The layers of translation (Java --> C -> SQL) can slow down execution.

The JDBC-ODBC bridge comes free
with the JDK:
– called sun.jdbc.odbc.JdbcOdbcDriver

The ODBC driver for Microsoft Access
comes with MS Office
– so it is easy to connect Java and Access
JDBC Drivers


list of drivers (freeware, shareware,
and commercial)
Sun Microsystems JDBC home page
– Java.sun.com/products/jdbc

SQL materials
– www.sql.org
JDBC PseudoCode

All JDBC programs do the following:
– 1) load the JDBC driver
– 2) Specify the name and location of the
database being used
– 3) Connect to the database with a
Connection object
Continued
– 4) Execute a SQL query using a
Statement object
– 5) Get the results in a ResultSet object
– 6) Finish by closing the ResultSet,
Statement and Connection objects
Pseudocode Diagram
DriveManager
creates
Connection
creates
Statement
creates
SQL
make link
to driver
ResultSet
data
Driver
SQL
data
DriveManager


It is responsible for establishing the
connection to the database through the
driver.
e.g.
Class.forName(
"sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn =
DriveManager.getConnection(url);
Name the Database

The name and location of the
database is given as a URL
– the details of the URL vary depending on
the type of database that is being used
ODBC Database URL
jdbc:odbc: //host.domain.com: 1511 /data/file
The comms
protocol
The machine
holding the
database.
e.g. jdbc:odbc:Books
The port
used for the
connection.
The path to
the database
on the machine
Statement Object


The Statement object provides a
‘workspace’ where SQL queries can be
created, executed, and results
collected.
e.g.
Statement st =
conn.createStatement():
ResultSet rs = st.executeQuery(
“ select * from Students” );
:
st.close();
ResultSet Object


Stores the results of a SQL query.
A ResultSet object is similar to a
‘table’ of answers, which can be
examined by moving a ‘pointer’
(cursor).
Continued
cursor
3

Cursor operations:
5
John
Mark
17
98
Paul
Peter
– first(), last(), next(), previous(), etc.

Typical code:
while( rs.next() ) {
// process the row;
}
Demo


Set up Access Database
Set up ODBC source
Make a Access Database
“JDBCStudent”
Add Data to Students Table
Add ODBC datasource

Press “Add’ to
add a data source
and select
Microsoft Access
Driver (*.mdb).
Press “Finish”.
ControlPanel: administrator tools.
ODBC Data Sources Administrator
Type in a
source name, description,
and press “Select”
to browse to set
the path to the
JDBCStduent.mdb file.
Username & Password

The database’s link to the outside
(e.g. its ODBC interface) must be
configured to have a login and
password


ClicK Advanced
Type in a username
and password
(guest).
Click “Ok”
simpJDBC.java
// simpJDBC.java
import java.sql.*;
public class JdbcSimple {
private java.sql.Connection connection;
public JdbcSimple(){
String url = "jdbc:odbc:cs483";
String username = "anonymous";
String password = "guest";
try {
// load the JDBC-ODBC Bridge driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// connect to db using DriverManager
Connection conn =
DriverManager.getConnection( url,
username, password );
// Create a statement object
Statement statement = conn.createStatement();
// Execute the SQL query
ResultSet rs = statement.executeQuery(
"SELECT lastName, firstName FROM Authors" );
:
// Print the result set
while( rs.next() )
System.out.println(
rs.getString("lastName") + ", " +
rs.getString("firstName") );
// Close down
statement.close();
conn.close();
}
:
catch ( ClassNotFoundException cnfex ) {
System.err.println(
"Failed to load JDBC/ODBC driver." );
cnfex.printStackTrace();
System.exit( 1 ); // terminate program
}
catch ( SQLException sqlex ) {
System.err.println( sqlex );
sqlex.printStackTrace();
}
} // end of main()
} // end of simpJDBC class
Accessing a ResultSet

The ResultSet class contains many
methods for accessing the value of a
column of the current row
– can use the column name or position
– e.g. get the value in the lastName
column:
rs.getString("lastName")
Continued


The ‘tricky’ aspect is that the values are
SQL data, and so must be converted to
Java types/objects.
There are many methods for
accessing/converting the data, e.g.
– getString(), getDate(), getInt(),
getFloat(), getObject()
Meta Data

Meta data is the information about the
database:
– e.g. the number of columns, the types of
the columns
– meta data is the schema information
ID
007
008
Name
James
Jet
Course
Mark
CS100
Math100
90
80
meta data
Accessing Meta Data


The getMetaData() method can be
used on a ResultSet object to create
its meta data object.
e.g.
ResultSetMetaData md =
rs.getMetaData();
Using Meta Data
int numCols = md.getColumnCount();
for (int i = 0; i <= numCols; i++) {
if (md.getColumnType(i) ==
Types.CHAR)
System.out.println(
md.getColumnName(i) )
}
More Meta Data Methods

getTableName()
getPrecision()

– number of decimal digits in the column
isSigned()

– returns true if column has signed
numbers
isCurrency()

etc.

Summary: Setting up the
Data Source

Create a new Database
– AddressBookDB


ID, firstName, lastName, ... email
Create a DSN for the Database
– DSN: Data Source Name
tells your program which database to use!
 Done in the ODBC control panel


the DSN is what will be required in the
URL to the Database for ODBC!!
Summary: Steps in using
JDBC








Load the appropriate JDBC driver
– Done using dynamic class loading in Java
Open a Connection to the Database
Create a new query as an SQL Statement
Execute the query
Process the ResultSet
– for database meta-data and the records
Close the Statement
Close the ResultSet
Close the database connection
Creating JDBC statements

A statement object is what sends your SQL
statement to DBMS. You create a statement object
and execute it. The method to use is executeQuery
or executeUpdate.
–
–
Statement stmt = dbConnection.createStatement();
String sqlState = new String ( "SELECT FirstName,
LastName FROM ATable");
–
ResultSet myResults = stmt.executeQuery( sqlState );
Statement

Statement stmt =
connection.createStatement();
stmt.executeQuery(String);
stmt.executeUpdate(String);
PreparedStatement

Better performance
String sql = "select ? from atable";
PreparedStatement stmt =
connection.prepareStatement(sql);
stmt.setString(1, ”acolumn");
ResultSet rs = stmt.execute();
The "1" replaces the first "?" in the statement
A "2" would replace the second "?" in the
statement
ResultSet





Queries return results in a ResultSet
Provides row-by-row access to results
Must call next() before getting data
Can get data out by data type
Can refer to columns by index or by
name
Getting and processing
the results
while( myResults.next())
{
System.out.println(myResults.getString(1) + "
+ myResults.getString(2));
}
"
Example: getTable
try {
String query = "SELECT * FROM Authors";
statement = connection.createStatement();
resultSet = statement.executeQuery( query );
displayResultSet( resultSet );
statement.close();
}
catch ( SQLException sqlex ) {
sqlex.printStackTrace();
}
Display Table
// position to first record
boolean moreRecords = rs.next();
if ( ! moreRecords ) {
return;
}
// get column heads
ResultSetMetaData rsmd = rs.getMetaData();
for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
columnHeads.addElement( rsmd.getColumnName( i ) );
// get row data
do {
rows.addElement( getNextRow( rs, rsmd ) );
} while ( rs.next() );
Get a row
Vector currentRow = new Vector();
for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
switch( rsmd.getColumnType( i ) ) {
case Types.VARCHAR:
currentRow.addElement( rs.getString( i ) );
break;
case Types.INTEGER:
currentRow.addElement(
new Long( rs.getLong( i ) ) );
break;
default:
System.out.println( "Type was: " +
rsmd.getColumnTypeName( i ) );
ResultSetMetaData




ResultSetMetaData md =
rs.getMetaData();
md.getColumnName(int);
md.getColumnType(int);
md.getColumnCount();
Find in a Table
Statement statement =connection.createStatement();
String query = "SELECT * FROM addresses "
+
"WHERE lastname = '" +
fields.last.getText() + "'";
ResultSet rs = statement.executeQuery(
query );
display( rs );
Update a Table
Statement statement = connection.createStatement();
if ( ! fields.id.getText().equals( "" ) ) {
String query = "UPDATE addresses SET " +
"firstname='" + fields.first.getText() +
"', lastname='" + fields.last.getText() +
"' WHERE id=" + fields.id.getText();
int result = statement.executeUpdate( query );
if ( result == 1 )
output.append( "\nUpdate successful\n" );
else { output.append( "\nUpdate failed\n" ); }
Another Database
Books.mdb
1
Titles
ISBN
Title
EditionNumber
YearPublished
Description
PublisherID
1
8
PublisherID
PublisherName
8
Publishers
1
AuthorISBN
ISBN
AuthorID
Authors
8
AuthorID
FirstName
LastName
YearBorn
Building large
information systems



Client
server
Database