JDBC

1
JDBC
2
Java Database Connectivity (JDBC)

• Use the java.sql package to query and update the
  database.
• JDBC is an API that allows java to communicate
  with a database server using SQL commands.
• Most important members, such as Connection,
  Statement, and ResultSet, are interfaces instead
  of being classes.
• This is because, the point of JDBC is to hide the
  specifics of accessing a particular database.
• The implementation of the underlying classes is
  done in the vendor provided driver and associated
  classes.

3
Basics

• DriverManager is responsible for keeping track of
  all the JDBC drivers that are available on a
  system.
• First task of a JDBC program is to load an
  appropriate driver for the type of database being
  used.
• After that a JDBC program should connect to the
  database by calling DriverManager.getConnection().
  
• You specify the database to connect with a
  jdbcURL. This URL has the following general
  syntax jdbcsubprotocolhostportdatabasename
• import java.sql.
• class InsertMovie
• public static void main (String args ) throws
  SQLException
• DriverManager.registerDriver (new
  oracle.jdbc.driver.OracleDriver())
• Connection conn DriverManager.getConnect
  ion
• ("jdbcoraclethin_at_oracle.csc.uvic.c
  a1521TEACH", "thomo", password")
• //
  _at_machineNameportSID, userid, password
• // If connecting from home through an ssh
  tunel, replace
• //oracle.csc.uvic.ca by localhost

4
Creating JDBC Statements

• A Statement object is what sends SQL statements
  to the DBMS.
• You simply create a Statement object and then
  execute it
• For a SELECT statement, use executeQuery.
• For statements that create or modify tables, use
  executeUpdate.
• It takes an instance of an active connection to
  create a Statement object.
• In the following example, we use our Connection
  object conn to create the Statement object stmt
• Statement stmt conn.createStatement()
• At this point stmt exists, but it does not have
  an SQL statement to pass on to DBMS.
• We need to supply that with execute
• E.g.
• Statement stmt conn.createStatement()
• ResultSet rset stmt.executeQuery(
• "SELECT title, year "
• "FROM Movie")

5
Getting Data

• Example
• ResultSet rset stmt.executeQuery(
• "SELECT title, year "
• "FROM Movie")
• while (rset.next())
• Variable rset, contains the rows of the query
  result.
• The first call of next() positions a "cursor" on
  the first row.
• Successive invocations of next() move the cursor
  down one row at a time.

6
Using the getXXX methods

• Use the getXXX method of the appropriate type to
  retrieve the value in each column.
• getString() for VARCHAR, CHAR
• getInt() for INT, NUMBER
• etc.
• while (rset.next())
• String s rset.getString("Title")
• int n rset.getInt("Year")
• System.out.println(s " " n)
• However we can also do instead
• String s rset.getString(1)
• int n rset.getInt(2)

Column Name
Column Position
7
import java.sql. class dbAccess public
static void main (String args ) throws
SQLException DriverManager.registerDri
ver (new oracle.jdbc.driver.OracleDriver())
Connection conn DriverManager.getConnection
("jdbcoraclethin_at_orcus.csc.uvic.ca1521TEAC
H", "thomo", password")
// _at_machineNameportSID, userid,
password Statement stmt
conn.createStatement() ResultSet rset
stmt.executeQuery(
"SELECT title, year "
"FROM Movie") while (rset.next())
System.out.println (rset.getString("title")
" " rset.getString("year"))
stmt.close()
8
Prepared statements

• When we want to execute a Statement object many
  times, it will reduce execution time to use a
  PreparedStatement object instead.
• A PreparedStatement is given an SQL template
  statement when it is created.
• The template statement is precompiled and ready
  to run many times without the need to be compiled
  each time a query conforming to it is given.
• The template statement will have several
  placeholders denoted by ?.
• To create an executable query, we need to supply
  values for each of the ? placeholders (see
  example).
• Example
• PreparedStatement updateMovies
  conn.prepareStatement(
• "UPDATE Movie SET studioName ? WHERE studioName
  ?")

9
import java.sql. class UpdateMovie public
static void main (String args ) throws
SQLException DriverManager.registerDri
ver (new oracle.jdbc.driver.OracleDriver())
Connection conn DriverManager.getConnection
("jdbcoraclethin_at_oracle.csc.uvic.ca1521TEA
CH", "thomo", password")
// _at_machineNameportSID, userid,
password PreparedStatement
updateMovieStatement String
updateMovieString "UPDATE Movie "
"SET studioName ? "
"WHERE studioName
LIKE ?" updateMovieStatement
conn.prepareStatement(updateMovieString)
10
String studiosBoughtByParamount
"Disney", "Fox" for(int i0
iltstudiosBoughtByParamount.length i)
updateMovieStatement.setString(1
, "Paramount")
updateMovieStatement.setString(2,
""studiosBoughtByParamounti"")
updateMovieStatement.executeUpdate()
updateMovieStatement.close()
11
Remark

• Using a PreparedStatement is the recommended way
  for populating the postings table.