JDBC Java and Databases

1
JDBCJava and Databases
2
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 data-base being used
• Encapsulate what varies

3
JDBC

• JDBC allows us to use data from a database with
  relative ease in a Java program
• Main steps
• 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

4
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

5
JDBC - connecting

• Making a connection to a data source (database)
  in done like this.
• Connection conn DriverManager.getConnection(ur
  l)
• So what is the url..?

6
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?

7
JDBC - connecting
URL
8
JDBC - connecting
9
JDBC - connecting

• Simply copy the URL into the connection
  statement
• Connection conn DriverManager.getConnection(
• "jdbcderby//localhost1527/MovieInformation")
• In practice, URL can be read from a setup file,
  registry, etc..

10
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

11
JDBC - statements

• We can execute various types of statements on the
  connection object
• First, we must create a statement object
• Statement stmt conn.createStatement()

12
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

13
JDBC - queries

• ResultSet srs stmt.executeQuery("SELECT Title,
  prodYear FROM Movie")

14
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

15
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

16
JDBC - queries

• Typical processing of a result set
• while (srs.next())
• // What should we do here?

17
JDBC - queries

• The precise 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

18
JDBC - queries

• while (srs.next())
• String title srs.getString("Title")
• int year srs.getInt("prodYear")
• objectModel.addMovie(new Movie(title, year))

19
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/basi
  cs/retrieving.html

20
JDBC exercise 1

• In this exercise, you must use the methods
  available in the JDBC library
• Make sure you have the MovieInformation database
  on your own computer. If not, create it and use
  the SQL code in the file Movie-InformationSQL.txt
  to create the tables and populate them with data
• Obtain a connection to the MovieInformation
  database. You need the URL of the database to do
  this. You will also need to handle exceptions in
  the code
• When you have successfully obtained a connection
  (no exceptions occurred), create a statement
  object. On the statement object, execute the
  query SELECT FROM movie
• When you have successfully executed the query (no
  exceptions occurred), process the ResultSet
  object, by printing out some of the fields for
  each record in the set

21
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?

22
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!

23
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

24
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)

25
JDBC - updates

• while (srs.next())
• String title srs.getString("Title")
• int year srs.getInt("prodYear")
• ...
• srs.updateInt("prodYear", year 30)
• srs.updateRow()

This also updates the DB
26
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

27
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()

28
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

29
JDBC exercise 2

• In this exercise, you must (again) use the
  methods available in the JDBC library
• First, you must have completed the steps in
  exercise 1
• Change the statement object, such that it allows
  for result sets that can be scrolled and updated
• In the processing of the records, now also update
  the movie records, by adding 2 to the numbers of
  Oscars won by each movie
• After having successfully updated the records (no
  exceptions occurred), use Derby to check that the
  records were indeed updated
• Finally, also try to insert a couple of new
  records into the movie and actor tables (follow
  the procedure described in the presentation)
• After having successfully inserted the records
  (no exceptions occurred), use Derby to check that
  the records were indeed inserted

30
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
• Butwhen and how do we update?

31
Keeping models in sync
Appli- cation
Object model
Result set
DB
32
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

33
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

34
JDBC exercise 3

1. Consider how a class model for movie information
   - containing information about movies, actors
   and casting should be designed
2. How should the class model and the database
   interact? What happens, when the class model is
   updated (e.g by a user through a suitable GUI)?
3. Try to consider different alternatives with
   regards to when to update the database, and what
   to update
4. If you have time left, try to outline Java code
   for interaction between the class model and the
   database