Transcript Mike Bradley

Beginning Databases with JDBC
Mike Bradley
Adapted from
http://java.sun.com/docs/books/tutorial/jdbc/basics/index.html
and notes by Kevin Parker, Ph.D.
Overview
Getting Started
Setting Up a Database
Establishing a Connection
Creating Statements
Executing Statements
Retrieving Values from Result Sets
Updating Tables
Intermediate JDBC
Getting Started
Things you need installed

Java and JDBC
 Get the latest JDK at
http://java.sun.com/products/JDK/CurrentRelease

Database drivers
 Can be supplied by the vendor
 JDBC-ODBC bridge is installed automatically with JDK for
Windows and Solaris

Database Management System
 If needed, not necessary for some databases, such as
Microsoft Access
Setting Up a Database
JDBC allows for



Connections to a database
Sending SQL commands to the database
Receiving responses from the database
Things to remember

Must have access to proper library
 Import java.sql.*

Objects must be in methods that can throw
exceptions, or try..catch blocks to catch
ClassNotFoundException and SQLException
Establishing a Connection
Establishing a connection consists of
two steps

Loading drivers
 Class.forName(“jdbc.DriverName”);
 Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”
);

Making the connection
 Connection con =
DriverManager.getConnection(url, “myLogin”,
“myPassword”)
Establishing a Connection: The URL
What to put in the URL


Documentation for the driver should indicate what
to put after the jdbc:
For the JDBC-ODBC bridge it will be
jdbc:odbc:something
 Something can be a DSN, or connection string
information


jdbc:odbc:Driver={Microsoft Access Driver
(*.mdb)};DBQ=C:/comic.mdb
Jdbc:odbc:Driver={SQL
Server};Server=SERVERNAME;UID=user;PWD=pass;datab
ase=DATABASENAME
Creating Statements
Statement objects send SQL commands
to the database
Statements require an active
Connection object to be created

Statement stmt = con.createStatement();
Executing Statements
Three types of statements to execute



Queries
Data updates
Data definition language (DDL) statements
Called with stmt.executeXXX


executeQuery(“SQL statement”) for returning
ResultSet
executeUpdate(“SQL statement”) for data updates
and DDL statements
Retrieving Values from Result Sets
Creating a result set

ResultSet rs =
stmt.executeQuery(someQuery)
Using next method


Initially the cursor for the RecordSet is
prior to the first record
rs.next() moves the cursor to the record in
the record set
Retrieving (cont.)
Using the rs.getXXX() methods


getXXX methods exist for most primitive
data types and some common objects
(String, Date, Object)
Can take either String or int parameter
 String parameter is the field (column) name

String s = rs.getString(“IssueName”);
 Int parameter is the 1-based ordinal number of
the field

String s = rs.getString(3);
Updating Tables
Uses executeQuery(someQuery)
Used for data updates and DDL statements


INSERT, UPDATE, and DELETE
CREATE TABLE
Can either get return value or ignore it


int n = stmt.executeQuery(“UPDATE foo SET bar
= 1 WHERE snafu = ‘tarfu’”)
n will equal the number of rows affected
 DDL statements always return 0
Intermediate JDBC
Prepared Statements

Similar to normal statements, but query is
supplied during creation, and can contain
parameter values
Transactions

For processing all or nothing batch updates
Stored Procedures

For calling procedures which exist in the DBMS
rather than supplied in code
Summary
JDBC is very similar to working with
databases in .NET or other OO and OB
languages
Syntax is most difficult thing

Remember to catch or throw errors