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