JDBC

1
DB3009N Week 9Database Access using JDBC
(Java DataBase Connectivity)
2
Note

• To complete the examples in this section you need
  access to a database!!
• Most of the examples work for any database with
  JDBC drivers. However, connecting to a database
  is database vendor specific. These examples will
  assume you are using mysql
• Minimal knowledge of SQL is assumed
• Java examples assume you understand servlets

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
3
Resources

• For information on setting up a database at
  London Metropolitan
• http//simt.unl.ac.uk/alext/mysql-howto.html
• For information on SQL commands
• http//www.w3schools.com/sql/default.asp

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
4
What is a Database?

• For those of you not familiar with databases a
  database is organised into tables.
• Every table has a name and is organised into
  columns and rows
• Each column has a name and a data type
• Each row represents a record in the database

Type of the data (similar to a java String)
Name of the column
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
5
Quick Guide to SQL

• Structured Query Language is a standard way to
  represent database commands
• You will not be tested on SQL!
• They can be typed directly into any database
  engine that supports SQL for example mysql
• We will use the commands insert, and select in
  the examples and practical. Both these commands
  manipulate tables.
• For this example, suppose I have a table called
  history which has two columns username and
  message (both are text)

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
6
SQL INSERT

• Suppose I wanted to add a new record consisting
  of the values Gandalf and Good afternoon to the
  history table.
• I would use the INSERT command
• INSERT INTO history VALUES('Gandalf','Good
  afternoon')

Value for the first column (name)
Value for the second column (message)
Name of the table to insert into
Each value is enclosed inside single quotes '
Each value is separated by a ,
Each SQL statement finishes with a
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
7
SQL SELECT

• Suppose I wanted to list all the records in the
  history table
• I would use the SELECT command
• SELECT FROM history

Name of the table to search
Matches all (any) entries in the table
Each SQL statement finishes with a
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
8
SQL SELECT

• Suppose I want to find all the entries in the
  table where the username is james. I can use a
  WHERE clause.
• SELECT FROM history WHERE username 'james'

Name of the column
Value to search for
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
9
SQL DELETE

• Suppose I want to delete one or more records from
  the database.
• Again I can use the WHERE clause
• DELETE FROM history WHERE username 'james1'
• This will delete any record that has james1 as
  the username

Name of the column
Value to search for
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
10
The Reason For JDBC

• Despite almost all databases supporting SQL,
  database vendors (Microsoft Access, Oracle etc.)
  provide proprietary (no standard) Application
  Programming Interfaces for sending SQL to the
  server and receiving results from it!
• Languages such as C/C can make use of these
  proprietary APIs directly
• High performance
• Can make use of non standard features of the
  database
• All the database code needs to be rewritten if
  you change database vendor or product
• JDBC (Java DataBase Connectivity) is a vendor
  independent API for accessing relational data
  from different database vendors in a consistent
  way

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
11
JDBC

• JDBC provides an API that hides the vendor
  specific APIs by inserting a driver between the
  Java application and the database API
• JDBC requires a vendor-specific driver
• The driver converts calls from JDBC API to
  vendors API gt performance penalty
• The driver does not provide access to vendor
  specific functionality
• The same Java application can be used with a
  different vendors database by simply switching
  JDBC driver and changing one line of Java code.
• JDBC 1.0 is included inside JDK 1.1 or higher in
  a package java.sql
• JDBC 2.0 and 3.0 require updated drivers and an
  additional package javax.sql
• We will only look at version 1.0

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
12
JDBC Driver Types

• JDBC database drivers are classified into four
  types based on the nature of the driver provided
• Bridges. Convert from JDBC to another standard
  for example ODBC. That standard then converts
  into the vendor specific API.
• Part Java part Native. Driver contains some Java
  code and some Native code
• Intermediate Server. JDBC talks directly to a
  separate server which then converts from JDBC to
  native API (great for intranets)
• Pure Java. JDBC driver converts directly to
  native API and sends through vendor specific
  network protocol (great for intranets)

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
13
JDBC-ODBC Bridge

• Microsoft has developed the standard ODBC Open
  DataBase Connectivity as a windows specific
  standard. It converts from ODBC to the vendors
  native API
• The JDBC driver converts from JDBC to ODBC.
• This is the only way to access Microsoft Access
  because access does not provide direct access to
  its own API.
• Requires you have ODBC and JDBC drivers installed
• This makes accessing Microsoft Access via Java
  slow and less reliable because two sets of
  conversion and divers are required

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
14
Part Java, Part Native Driver

• JDBC driver consists of java code and partly
  native driver code which uses vendor-specific API
  for accessing databases
• Requires the JDBC driver with the correct native
  code for the database to be loaded on the client
• More efficient than JDBC-ODBC bridge due to fewer
  layers of communication and translation

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
15
Intermediate Database Access Server

• Database is accessed via an intermediate server
  often running on a different machine or network
  from the client gt good for intranet use.
• Many database vendors are now supplying
  intermediate servers which directly understand
  JDBC
• JDBC driver on client talks to JDBC intermediate
  access server via a standard protocol gt no need
  for native driver on client
• The intermediate server handles the request using
  a native driver

network
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
16
Pure Java Driver

• JDBC calls are directly translated to database
  calls specific to vendor and sent across the
  network
• Requires the correct JDBC driver for the vendor
  to be loaded on client
• Simple and high performance
• Normally requires vendor to create driver because
  many databases have proprietary protocols for
  accessing them across a network

network
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
17
The Driver manager

• It is possible that a Java application may need
  to talk to multiple databases, possibly from
  different vendors (especially if it is a
  distributed system)
• The JDBC driver manager provides the ability to
  communicate with multiple databases and keep
  track of which driver is needed for which
  database.
• Even if you only talk to one database you need to
  connect to it via the driver manager

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
18
Java JDBC Programming Model

• DriverManager allows you to connect to one or
  more to databases
• Connection represents the connection to a single
  database
• Statement contains an SQL statement to be sent
  down the connection to be executed by the
  database
• ResultSet contains the results of an SQL query
  such as SELECT

Driver Manager
Database
ResultSet
Connection
Statement
SQL
Database
ResultSet
Connection
Statement
SQL
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
19
Java JDBC Programming Steps

• Load Driver. The forName method of Class loads
  the specified JDBC driver and initialises the
  DriverManager.
• Connect to database. getConnection in
  DriverManager returns a Connection Object. The
  Connection Object is used to communicate with the
  database specified in getConnection.
• Execute SQL. createStatement in Connection
  returns an Statement Object which can be used to
  send SQL statements to the connected database.
• Use executeQuery or executeUpdate methods in
  Statement to search or modify the database.
  (Searches produce a ResultSet object)
• Process the ResultSet or handle any errors
• Close Statement object and close the Connection
  when you have no more statements to send.

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
20
Java JDBC Programming Steps
Class forName
Class.forName loads the DriverManager
DriverManager getConnection
DriverManager.getconnection connects to a
database and returns a Connection object
Connection createStatement
Connection.createStatement provides a Statement
object you can insert SQL into
Statement
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
21
Java JDBC Programming Steps
Once you have a statement object you can send SQL
queries and updates to the database. Queries
return a ResultSet object and updates return the
number of records affected.
Statement.executeQuery returns any found database
records
SQL is placed into the statement object
Statement executeQuery
SQL Select blah blah
ResultSet
Statement.executeUpdate returns the number of
records affected
SQL is placed into the statement object
Statement executeUpdate
SQL Delete blah blah
int
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
22
Java JDBC Programming Example

• All the classes required for standard JDBC
  programming are in the package java.sql
• For the examples in this chapter I will assume
  you have a mysql database
• Running on a machine called www3.unl.ac.uk
• Running on port 3306
• The database is called kingj1 (yours will be your
  login name)
• The login is kingj1 (yours will be your login
  name)
• The password secret (not my real password of
  course!)
• With one table called test with a single field
  called value

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
23
Step 1 Loading a Driver

• A JDBC driver is always needed to connect to a
  database
• Loading a driver requires the class name of the
  driver. For
• JDBC-ODBC sun.jdbc.odbc.JdbcOdbcDriver
• Oracle driver oracle.jdbc.driver.OracleDriver
• mySQL org.gjt.mm.mysql.Driver
• The class for the name of the driver is loaded
  using the static method forName in the class
  Class. (Class is in the package java.lang and so
  imported automatically)
• Class.forName("org.gjt.mm.mysql.Driver")
• This loads and initialises the driver.
• It is possible to load several drivers.
• The class DriverManager manages the loaded
  driver(s)

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
24
Step 2 Connecting to a Database part 1

• The getConnection method of DriverManager
  requires the name of the database to connect to,
  the name of the user connecting and their
  password it returns a Connection Object.
• The syntax for the name of the database is a
  little messy and is unfortunately Driver specific
• A JDBC URL represents a driver and has following
  three-part syntax
• jdbcltsubprotocolgtltsubnamegt

"jdbcmysql//www3.unl.ac.uk3306/kingj1
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
25
Step 2 Connecting to a Database part 2

• The DriverManager allows you to connect to a
  database using the specified JDBC driver,
  database location, database name, username and
  password.
• It returns a Connection object which can then be
  used to communicate with the database.
• The password is passed to the database in plain
  text and therefore anyone who can see the .java
  or .class file can find out the password!
• Connection connection DriverManager.getConnectio
  n("jdbcmysql//www3.unl.ac.uk3306/kingj1","kingj
  1","secret")

Password in plain text!!!!!!!!!!!!!!!
Vendor of database, Location of database server
and name of database
Username
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
26
Step 3 Executing SQL (Creating Statement objects)

• Connection objects can be used to create
  Statement objects. Statement objects allow you to
  send SQL to the database to be executed and
  examine any results
• Statement statement connection.createStatement()
  
• Different SQL statements require different
  handling.
• Updates such as INSERT return the number of rows
  in the table effected
• Queries such as SELECT return a ResultsSet Object
  showing all the matching entries.

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
27
Step 4 Executing SQL (Modifying the Database)

• Once you have a Statement Object you can get the
  database to execute your SQL statements. SQL
  which modifies the database uses the
  executeUpdate method of Statement
• Lets add a record to the database
• Lets assume the database has a table called test
  with a single column called value
• We will add a record value 99" in SQL this would
  be INSERT INTO test VALUES('999')
• int rstatement.executeUpdate("INSERT INTO test
  VALUES('999')")

SQL statement surrounded by " "
Number of records effected. In this example
should be 1 since we are adding a single record
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
28
Step 4 Executing SQL (Modifying the Database)

• To add to a table with several columns simply
  separate each value by a ,
• int rstatement.executeUpdate("INSERT INTO test
  VALUES('999','blob')")
• To use the value of a variable as the value to
  add
• int rstatement.executeUpdate("INSERT INTO test
  VALUES(' "variable" ')")

If the value you want to add to the database is a
text String and includes one or more ' in it you
will have a problem
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
29
Step 4 Executing SQL (Querying the Database)

• SQL which queries the database uses the
  executeQuery method of Statement
• ExecuteQuery returns a ResultSet which we can
  then look at to see what records matched the
  query
• Lets assume the database has a table called test
  with a single column called value
• We will list the entire table in SQL this would
  be SELECT FROM test
• ResultSet rsstatement.executeQuery("SELECT
  FROM test")

SQL statement surrounded by " "
Contains the any matched records
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
30
Step 5 Processing the Returned Data

• When executeQuery has finished it returns a
  ResultSet.
• ResultSet has an internal pointer which can be
  moved forward and backwards. You can only
  retrieve the values of the record it points at.
• Next(), Previous(), First() and Last() move the
  internal pointer between records they return true
  if the record is valid
• So if the pointer is already on the first record
  and you use previous it will return false etc.
• isLast() and isFirst() return true if the pointer
  is at the last or first record respectively

ResultSet
Internal Pointer
Record 1
Record 2
Record 3
Record 4
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
31
Step 5 Processing the Returned Data

• The pointer points at the record to retrieve.
• However, we still have to get the value of each
  of its columns
• GetString("name") returns the value of the column
  called name as a String
• GetInt("name") returns the value of the column
  name as a int etc...
• while (rs.next())
• int valuers.getInt("value")

Name of the field inside the record to fetch
Moves to the next record and returns true if the
record is valid. If the record is not valid the
loop exits
NOTE this code will not skip the first record
because the pointer starts one before the first
record!
Do something with value
CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
32
Step 6 Closing Database Connection

• Just as with file access in Java, it is a good
  idea to close the Statement and Connection
  objects when you have finished with them.
• statement.close()
• connection.close()

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)
33
Rollback and Commit

• By default connections to the database commit
  each update as soon as it is completed.
• If you want to make a set of changes and only
  commit the aggregate result if nothing goes wrong
  auto commit needs to be switched off
• connection.setAutoCommit(false)
• You can then use
• connection.commit() to apply the changes
• connection.rollback() to undo changes since the
  last commit
• You can switch auto commit back on using
• connection.setAutoCommit(true)

CCTM Course material developed by James King
(james.king_at_londonmet.ac.uk)