Transcript JDBC Presentation

JDBC
Java and Databases
JDBC
• JDBC – Java DataBase Connectivity
• An API (i.e. a set of classes and methods),
for working with databases in Java code
• Primary goal: to make the Java code
(almost) independent of the actual database being used
SWC – 2011
2
JDBC
• JDBC allows us to use data from a
database with relative ease in a Java
program
• Main steps
1.
2.
3.
4.
Connect to a data source
Execute queries on the database
Store result of query in a data structure
Process the data according to business logic
SWC – 2011
3
JDBC
• There is nothing ”magical”
about the JDBC classes
and methods – it is just yet
another part of the library
• Is found in java.sql.*
packages
SWC – 2011
4
JDBC - setup
• Before starting, we need to install a socalled ”driver” for MySQL
• The driver program enables NetBeans to
communicate with MySQL databases
• Comes as a .jar file (Java archive)
SWC – 2011
5
JDBC - setup
•
The MySQL driver is called a ”MySQL
connector”
1.
2.
3.
4.
Go to www.mysql.com/products/connector
Download the ”JDBC Driver for MySQL”
Dowload the .ZIP file
Unzip somewhere proper, e.g. in the
”MySQL” folder under ”Program Files”
SWC – 2011
6
JDBC - setup
SWC – 2011
7
JDBC - setup
• After having downloaded the driver, we
must make a reference to it from the
project that will use the driver
• We use the NetBeans Library Manager for
this
1. Add MySQL driver to a library
2. Refer to the library from the project
SWC – 2011
8
JDBC - setup
SWC – 2011
9
JDBC - setup
SWC – 2011
10
JDBC - setup
MySQL driver
SWC – 2011
11
JDBC - setup
SWC – 2011
12
JDBC - setup
SWC – 2011
13
JDBC - setup
SWC – 2011
14
JDBC - setup
SWC – 2011
15
JDBC - setup
You’re done!
SWC – 2011
16
JDBC - connecting
• Making a connection to a data source
(database) in done like this.
Connection conn = DriverManager.getConnection(
url, user, password);
• What are proper values for these three
parameters?
SWC – 2011
17
JDBC - connecting
• In general, we can connect to a data
source ”anywhere on the web”
• Therefore, the data source is specified as
a URL (uniform resource locator)
• What is the URL for a database on our
own computer…?
SWC – 2011
18
JDBC - connecting
URL
SWC – 2011
19
JDBC - connecting
SWC – 2011
20
JDBC - connecting
• Simply copy the URL into the connection
statement:
Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/MovieInformation”,…,…);
• In practice, URL can be read from a setup
file, registry, etc..
SWC – 2011
21
JDBC - connecting
• The ”user” and ”password”
are those values you also
use when connecting
”directly” to the database
• In practice, they should not
be hard-coded into the
Java code…
SWC – 2011
22
JDBC - connecting
• If connection succeeds, we
have a connection object
available
• Actual data source is now
abstracted away…
• We can now execute
statements on the
connection object
SWC – 2011
23
JDBC - statements
• We can execute various types of
statements on the connection object
• First, we must create a statement object
Statement stmt =
conn.createStatement();
SWC – 2011
24
JDBC - statements
• On the statement object, we can call
various methods, that perform an
operation on the database
• In particular, we can execute an SQL
query on the statement object
SWC – 2011
25
JDBC - queries
ResultSet srs =
stmt.executeQuery("SELECT
Title, prodYear FROM Movie");
SWC – 2011
26
JDBC - queries
• The return value of executeQuery is a
result set (type ResultSet)
• The result set object contains the result of
the query, i.e the set of rows returned by
the query
• A result set is somewhat similar to an
ArrayList, but with some important
differences
SWC – 2011
27
JDBC - queries
• A result set object has a cursor, which
points to a specific row in the set
• The cursor can be moved in the result set,
by calling certain methods
• For instance the next() method
– Returns a boolean value (any more rows?)
– Advances the cursor one row
SWC – 2011
28
JDBC - queries
• Typical processing of a result set:
while (srs.next())
{
// Process the record...
}
SWC – 2011
29
JDBC - queries
• The actual processing of the result set is –
of course – application dependent
• Typically, we will here map the result to
the object model in the application
• This is a topic in itself…
• Typically, some application-specific class
acts as a ”bridge” between relational
model and object model
SWC – 2011
30
JDBC - queries
while (srs.next())
{
String title = srs.getString("Title");
int year = srs.getInt("prodYear");
objectModel.addMovie(new Movie(title, year));
}
SWC – 2011
31
JDBC - queries
• The ResultSet methods enable us to
retrieve the actual field values from each
row in the result set
• Many more details about result set
methods are found at
http://java.sun.com/docs/books/tutorial/jdbc/basics/retrieving.html
SWC – 2011
32
JDBC - updates
• It is fairly straightforward to retrieve data
from a database using JDBC
– Create a connection object, using a URL
– Create a statement object
– Execute a query on the statement object
– Process the result set returned from the query
• What if we wish to update some data…?
SWC – 2011
33
JDBC - updates
• It it very important to realise, that the result
set object and the underlying database are
still ”connected” to each other
• If we update a value in the result set, the
corresponding field in the database is also
updated!
• This is the beauty of JDBC!
SWC – 2011
34
JDBC - updates
• A few technicalities…
• A result set has some properties with
regards to
– Freedom of cursor movement
– Ability to update database
• These properties are specified when we
create the statement object
SWC – 2011
35
JDBC - updates
• In order to let us update the data, we must
create the statement object like:
Statement stmt =
conn.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
SWC – 2011
36
JDBC - updates
while (srs.next())
{
String title = srs.getString("Title");
int year = srs.getInt("prodYear");
...
srs.updateInt("prodYear", year + 30);
srs.updateRow();
}
SWC – 2011
37
JDBC - insertion
• A result set also allows us to insert a new
record into the result set – and thereby
into the database
• The procedure requires a few steps:
– Move the cursor to a special row called the
”insert row”
– Set field values by using the update… methods
– Finally, the row is actually inserted
SWC – 2011
38
JDBC - insertion
srs.moveToInsertRow();
srs.updateString("TITLE", "District 9");
srs.updateString("COUNTRY", "UK");
srs.updateString("GENRE", "Sci-Fi");
srs.updateInt("MOVIEID", 11);
srs.updateInt("PRODYEAR", 2009);
srs.updateInt("OSCARS", 0);
srs.insertRow();
SWC – 2011
39
JDBC - deletion
• Finally, it is also possible to delete a row
from the result set – and thereby also from
the database
srs.deleteRow();
• Deletes the row to which the result set
cursor currently points
SWC – 2011
40
Keeping models in sync
• We could – in principle – just execute
queries, updates, etc. directly on the
database, without using result sets
• It is much more efficient to use result sets
• But…when and how do we update…?
SWC – 2011
41
Keeping models in sync
Application
Object
model
Result
set
DB
SWC – 2011
42
Keeping models in sync
• Questions to ponder
– When do we load the data initially?
• Application start-up
• When object model is accessed
• User initiative
– When (how often) is data saved?
• Application shutdown
• Upon every change to object model
• User initiative
SWC – 2011
43
Keeping models in sync
• Questions to ponder (continued)
– How much data do we save, when we save
• Everything (brute force). Inefficient, but easy to
implement in code
• Difference between current object model, and
object model at last save. Efficient, but requires
more book-keeping
SWC – 2011
44