Transcript Database Programming With Java & JDBC

Database Programming
With Java & JDBC
Reading: DD Ch. 18, pp905-928
http://java.sun.com/docs/books/tutori
al/jdbc/index.html , or anything
covering JDBC
In this lecture you will learn
• Why connecting via a db is important
• The components of the JDBC interface to
DBMSs
• How to connect to a database from Java
• How to send SQL statements from Java to a
DBMS
• The concepts of the ResultSet object and
what a Cursor is
• How to navigate and update a ResultSet from
Java
Dept. of Computing Science, University of Aberdeen
2
JDBC: Java Database
Connectivity
• API for Java which defines how program can access a db
• JDBC(TM) - developed by Sun Microsystems
• Exploited an existing interface library (ODBC)
• ODBC - Open Database Connectivity (C library)—standard
Application Programming Interface for connecting to db;
independent of programming languages, db systems, OS
• ODBC - C programmers DB interface, early 1990's
Dept. of Computing Science, University of Aberdeen
3
The JDBC Programming Model
• The JDBC uses a client-server programming model:
– Client makes a connection to the DB (the server)
– Client sends SQL statement(s) (query) to the DB
– Can manipulate database metadata (info about tables, data
types, information schema etc.)
– DB sends back a Result Set
– Client processes Result Set
– Client disconnects from the DB
• Points to note:
– Result Set includes query results and metadata
– JDBC throws an exception whenever there's an error
Dept. of Computing Science, University of Aberdeen
4
JDBC Drivers
•
•
Client-side adaptors that convert Java
program requests into a protocol that DBMS
understands
4 types:
1. Type 1—JDBC-ODBC bridge
2. Type 2—Native API driver (converts JDBC calls
to DBMS API)
3. Type 3—Network protocol driver—can connect
JDBC to middleware-net server
4. Type 4—Driver-native protocol driver (converts
JDBC calls directly to DBMS protocol)
Dept. of Computing Science, University of Aberdeen
5
Loading a DriverManager
• DriverManager provides DB interface methods & defines
objects which can connect Java app to driver; it manages set of
drivers program can use to connect to db; finds driver & runs it
to connect to db
• Most Java programs use import to load a class
• With JDBC, usually load driver class at runtime:
import java.lang.*;
public static void main (String args[]) {
try { // this loads & initialises the driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// now connect to DB ...
} catch (ClassNotFoundException e) {
System.err.println(e.getMessage());
e.printStackTrace();
System.exit(1);
}
}
Dept. of Computing Science, University of Aberdeen
6
Making a JDBC-ODBC
Database Connection
import java.sql.*;
Connection con;
String URL = "jdbc:odbc:DataSource";
String Username = "";
String Password = "";
con = DriverManager.getConnection(URL,Username, Password);
// make more calls to JDBC methods here...
con.close();
•
•
‘DataSource’ is a ‘logical name’ (not a real filename)
Need to set DataSource = MyDatabase.mdb
elsewhere
• Use ODBC Data Source Administrator for this...
Dept. of Computing Science, University of Aberdeen
7
Statement and ResultSet
Objects
Statement stmt = con.createStatement();
String sql = "SELECT * FROM Staff;";
ResultSet rs = stmt.executeQuery(sql);
print_rs(rs); // shown later
rs.close(); // free memory
stmt.close();
• Statement objects let you execute SQL queries: 3
types: Statement (simple, no parameters), Prepared
Statement (precompiled queries), Callable Statement
(execute call to db stored sql procedure)
• Can pass any legal SQL query to executeQuery()
• Here, rs holds the entire results table (Staff)...
Dept. of Computing Science, University of Aberdeen
8
ResultSets and Cursors
The ResultSet object is a ‘container’ for the results
of a query (table):
• Each ResultSet
contains:
– Numbered Fields
(columns)
– Field names & types
(metadata)
– A Cursor (current row)
•First cursor position is BEFORE FIRST ROW
• Rows and columns COUNT FROM ONE
•Move cursor via: rs.next(), previous(), first(),
last(), relative(), absolute(), beforeFirst(),
Dept. of Computing Science, University of Aberdeen
afterLast()
9
ResultSet Metadata
• We can find the number of columns, the column
names and data types (REAL, CHAR, etc.) from
the ResultSet metadata:
ResultSetMetaData md = rs.getMetaData();
int num_columns = md.getColumnCount();
for (int i=1; i<=num_columns; i++) {
System.out.println("Column Number = " + i);
System.out.println("Column Name = " + md.getColumnLabel(i));
System.out.println("Column Type = " + md.getColumnTypeName(i));
}
Dept. of Computing Science, University of Aberdeen
10
Example: Printing a ResultSet
• We can move through the rows using the next()
method:
private void print_rs(ResultSet rs) throws
SQLException {
ResultSetMetaData md = rs.getMetaData();
int num_columns = md.getColumnCount();
while (rs.next()) {
String row_buffer = "";
for (int i=1; i<=num_columns; i++) {
row_buffer += row_buffer + " " + rs.getString(i);
}
}
}
System.out.println(row_buffer);
• Note use of metadata and getString() method
Dept. of Computing Science, University of Aberdeen
11
Handling ResultSet Field Types
• There is a ‘get’ function for each of the main SQL
data types (each needs a column no. or a column
name):
–
–
–
–
–
rs.getString(index)
getInt()
getDouble()
getDate()
in general getxxxx()
• To do arithmetic on field values, need to extract into
appropriate type:
– double ave = rs.getDouble("Rent") /rs.getInt("Rooms");
Dept. of Computing Science, University of Aberdeen
12
Updatable & Scrollable Cursors
• There are several types of ‘cursor’. The type we get
is controlled by the call to createStatement():
stmt = con.createStatement(int scrolling, int concurrency);
• scrolling:
– ResultSet.TYPE_FORWARD_ONLY - the default!
– ResultSet.TYPE_SCROLL_INSENSITIVE – cursor can move
forward & backward
– ResultSet.TYPE_SCROLL_SENSITIVE – advanced; sensitive
to changes made by other users
• concurrency: can RS be updated?
– ResultSet.CONCUR_READ_ONLY - the default! RS can’t be
updated
– ResultSet.CONCUR_UPDATABLE—RS can be updated, &
these changes can be placed into underlying DB
Dept. of Computing Science, University of Aberdeen
13
Scrollable Cursor Method
Functions
• Scrollable cursors have a rich set of method
functions:
–
–
–
–
–
Boolean next(), previous()
void absolute(int row_num),
void relative(int row_increment),
Boolean first(), last()
void beforeFirst(), afterLast()
• You can also ask where the cursor is currently
positioned:
– Boolean isFirst(), isLast()
– Boolean isBeforeFirst(), isAfterLast()
Dept. of Computing Science, University of Aberdeen
14
Inserting or Deleting Rows
• For queries that do not return a ResultSet, use the
executeUpdate(sql) method:
Statement stmt = con.createStatement();
String sql = "DELETE FROM Client " +
"WHERE ClientNo = " +
"'CR56';";
int num_rows = stmt.executeUpdate(sql);
• Returns int (row count for insert, update, delete
queries, 0 for sql stmts that return nothing)
• If successful, get num_rows = 1 (no. rows changed)
• Could use "INSERT INTO Client..." in the same way...
• Health warning:
– TAKE SPECIAL CARE WITH SPACES AND QUOTES
Dept. of Computing Science, University of Aberdeen
15
Updating an Existing Row
• To update a row, first get it into the ResultSet:
Statement stmt = con.createStatement();
String code = "CR56";
String sql = "SELECT * FROM Client" +" WHERE ClientNo ="+
" '" + code + "'" + ";";
ResultSet rs = stmt.executeQuery(sql);
rs.absolute(1); // move to first row
rs.updateDouble("MaxRent", 500.0);
rs.updateString("PrefType", "House");
rs.updateRow();
rs.close();
• Here, ResultSet must contain the primary key - why?
• Also, note that ResultSet must be CONCUR_UPDATEABLE
• rs.insertRow() etc.
Dept. of Computing Science, University of Aberdeen
16
Handling NULLs
• The JDBC API handles NULLs using a
special method function: wasNull()
– Have to "get" a field value, and then test
for NULL ...
String x = rs.getString("City");
if (rs.wasNull()) x = "NULL";
int i = rs.getInt("Rooms");
if (rs.wasNull()) i = -1; // or whatever
• Your code can then test for special
value later...
Dept. of Computing Science, University of Aberdeen
17
Summary
• Programming model:
• Client (Java) sends SQL strings to server (DBMS)
• Server (DBMS) sends a ResultSet back to client...
• Programming features:
• JDBC is strongly typed: getString(), getInt()...
• JDBC throws exceptions whenever there's a error
• Consider type of cursor: scrollable/updateable?
• ResultSet rows & columns count from ONE
• ResultSet cursor starts BEFORE the first row
• Some ResultSets are updateable, so changes from
Java program on ResultSet (if concur_updateable)
can be committed to db
Dept. of Computing Science, University of Aberdeen
18