JDBC Data Access API

1
JDBC Data Access API

• CSC3170

2
Outline

• What is JDBC?
• Setting up the Environment for JDBC
• JDBC Step by Step
• More about JDBC
• Reference

3
What is JDBC?
4
Is it Java Database Connectivity?

• As a point of interest, JDBC is the trademarked
  name and is not an acronym nevertheless, JDBC is
  often thought of as standing for Java Database
  Connectivity.
• JDBC Technology Guide Getting Started
• What Is the JDBC API?
• http//java.sun.com/j2se/1.5.0/docs/guide/jdbc/get
  start/intro.html1018464
• JDBC name
• http//forum.java.sun.com/thread.jsp?thread329483
  forum48message1341045

5
What is JDBC?

• An API that lets you access virtually any
  tabular data source from the Java programming
  language
• JDBC Data Access API JDBC Technology Homepage
• access virtually any data source, from
  relational databases to spreadsheets and flat
  files.
• JDBC Documentation
• We will focus on accessing databases in an Oracle
  DBMS

6
How does JDBC work?

• The Trick
• A JDBC technology-based driver to mediate
  between JDBC technology and the database
• JDBC Documentation
• Each DMBS vendor provides their own driver
• Use the included JDBC-ODBC Bridge
• Access
• Excel
• FoxPro

7
Setting up the Environment for JDBC
8
Setting up the Environment for JDBC

• Get Java! Did you?
• Get the JDBC Driver for Oracle DBMS
• http//www.cse.cuhk.edu.hk/csc3170/project/classe
  s12.zip
• Are you inside the CUHK Network?
• CSE Lab, ITSC User Area, ResNet, ClassNet,
  Wireless LAN
• CUHK Internet Access off Campus
• VPN
• http//www.cuhk.edu.hk/itsc/network/vpn/vpn.html
• Dial-Up
• http//www.cuhk.edu.hk/itsc/network/dialup/method.
  html
• CSE Remote Access(CSE Majors ONLY)
• IPsec VPN
• http//www.cse.cuhk.edu.hk/vpn
• Dialup
• http//www.cse.cuhk.edu.hk/corner/tech/guide/dialu
  p.html

9
The Class Search Path Dissected

• Commonly known as the Class Path
• .lib\classes12.zip
• .(dot) represents the current directory
• (semi-colon) separates two paths, nix uses
  (colon)
• lib is the directory where the archives are in
• Remember to substitute with you own path!
• e.g. C\CSC3170\Project
• Avoid using paths with space, which may require
  quoting
• "C\My Library\csc3170.jar"
• classes12.zip is the JDBC driver for Oracle DMBS

10
Setting the Class Path

• The Preferred Method (according to the docs)
• Windows
• C\jdk1.5.0_05\bin\javac classpath
  .lib\classes12.zip .java
• C\jdk1.5.0_05\bin\java classpath
  .lib\classes12.zip Query
• Solaris and Linux
• Replace (semi-colon) with (colon)
• Replace \ (backward slash) with / (forward
  slash)
• Tips
• Write batch files or Makefile to save typing and
  avoid typos

11
Setting the Class Path

• The Popular Method 1
• Windows XP/2000/NT
• Right-click My Computer(????)
• In the pop-up menu, click Properties(??)
• On the Advanced(??) tab
• click Environment Variables(????)
• Click New(??) or Edit(??) as appropriate
• Variable name (????) CLASSPATH
• Variable value (???)
• .lib\classes12.zip

12
Setting the Class Path

• The Popular Method 2
• Windows (ALL versions)
• Click Start(??), Run(??)
• Type sysedit to start System Configuration
  Editor(????????)
• Append the following to C\AUTOEXEC.BAT
• set CLASSPATH.lib\classes12.zip
• Click File(??), Save(??)
• You may need to reboot

13
JDBC Step by Step
14
JDBC Step by Step

• Importing the required packages
• Establishing a Connection
• Creating and Using a Statement
• Retrieving Values from Result Sets

15
Importing the required packages

• JDBC API
• import java.sql.

16
Establishing a Connection
This is different from Lecture Notes!

• Loading the JDBC Driver for Oracle DBMS
• Class.forName("oracle.jdbc.driver.OracleDriver")
• Establishing a Connection
• Connection con DriverManager.getConnection("jdb
  cltsubprotocolgtltsubnamegt",ltlogingt, ltpasswordgt)
• e.g. (N.B. The servers are totally independent)
• Connection con DriverManager.getConnection("jdb
  coraclethin_at_db00.cse.cuhk.edu.hk1521db00",
  "db000", "u945htWjd")
• Connection con DriverManager.getConnection("jdb
  coraclethin_at_db01.cse.cuhk.edu.hk1521db01",
  "db000", "u945htWjd")

subname is vendor-specific
17
The Difference Explained

• Loading the JDBC Driver for Oracle DMBS
• All Driver classes should register it with
  the DriverManager class when it is loaded. Thus,
  a user would not normally call DriverManager.regis
  terDriver directly
• http//java.sun.com/j2se/1.5.0/docs/guide/jdbc/get
  start/drivermanager.html997322
• However, this method Class.forName is valid
  only for JDK-compliant Java virtual machines. It
  is not valid for Microsoft Java virtual
  machines.
• http//otn.oracle.com/tech/java/sqlj_jdbc/pdf/a966
  54.pdf
• Types of JDBC Drivers
• Oracle Call Interface (OCI)
• Use native methods ? platform specific
• Thin
• Written entirely in Java ? platform-independent
• http//otn.oracle.com/tech/java/sqlj_jdbc/htdocs/j
  dbc_faq.htm_59_
• http//java.sun.com/products/jdbc/driverdesc.html
• You should use what is written in the tutorial
  note for the project

18
Creating and Using a Statement

• Create a Statement
• Statement stmt con.createStatement()
• Note Statement is reusable
• Update (Create, Insert, Delete, Drop)
• stmt.executeUpdate("CREATE TABLE Nurse "
  "(UserID VARCHAR2(10), " "Password
  VARCHAR2(8))")
• Query
• ResultSet rs stmt.executeQuery(
• "SELECT FROM csc3170.FOODS")

Make sure you include proper spacing
19
Retrieving Values from Result Sets

• while (rs.next())
• String id rs.getString(1)
• String name rs.getString(2)
• float price rs.getFloat(3)
• rs.next() moves the cursor down one row from it
  current position
• A ResultSet cursor is initially positioned before
  the first row
• Use of getXXX Methods
• http//java.sun.com/docs/books/tutorial/jdbc/basic
  s/retrieving.html

20
Retrieving Values from Result Sets
21
More about JDBC

• Improving Performance
• Exception Handling
• Commit!
• Topics Not Covered

22
Be Prepared

• Need to insert a large number of records
• while ((nurse cois.readNurse()) ! null)
• stmt.executeUpdate("INSERT INTO Nurse VALUES ("
  nurse.id ", " nurse.password)
• Need to re-compile the SQL statement for every
  insertion
• String concatenation is slow
• PreparedStatement pstmt con.prepareStatement(I
  NSERT INTO Nurse VALUES (?, ?))
• while ((nurse cois.readNurse()) ! null)
• pstmt.setString(1, nurse.id)
• pstmt.setString(2, nurse.password)
• pstmt.executeUpdate()
• Use pstmt.executeQuery() for querying

The setXXX methods set the designated parameters
(?)
23
Performance Tips

• Use PreparedStatement to speed up repetitive
  tasks
• PreparedStatement allow DBMS to pre-compile SQL
  statements
• Never do
• SELECT FROM table1, , tableN
• When this suffices
• SELECT name FROM table1, table2WHERE table1.id
  table2.id
• Do use the standard packages
• Often (but not always)
• Faster
• Correct
• java.util package
• StringTokenizer, Collections

24
Performance Tips

• Connection and Statement Objects are reusable
• Avoid opening, closing and re-opening Connection
• Avoid open several Connections simultaneously
• But avoid holding unnecessary resources
• rs.close()
• stmt.close() / pstmt.close()
• con.close()
• Once you finished using them
• Before your application terminates

25
Exception Handling

• Class.forName() throws ClassNotFoundException
• Almost all other methods throws SQLException
• try
• / JDBC Method Calls /
• catch (SQLException e)
• System.err.println("Caught SQLException ")
• do
• System.err.println("Message "
  e.getMessage())
• System.err.println("SQL State "
  e.getSQLState())
• System.err.println("Error Code "
  e.getErrorCode())
• e e.getNextException()
• while (e ! null)

Exception Description
XOPEN SQLstate conventions or SQL 99 conventions2
Vendor-specific integer error code
26
Commit!

• By default a Connection is in
• auto-commit mode
• Sometimes you want to commit manually
• Transaction
• Money Transfer
• con.setAutoCommit(false)
• con.commit()
• con.rollback()

27
Topics Not Covered

• A lot!
• SQLWarning
• See Sample Code
• http//java.sun.com/docs/books/tutorial/jdbc/basic
  s/complete.html
• CallableStatement
• Stored Procedure
• http//java.sun.com/docs/books/tutorial/jdbc/basic
  s/sql.html
• javax.sql package
• JDBC Optional Package API

28
Reference
29
JDBC

• JDBC Data Access API JDBC Technology Homepage
• http//java.sun.com/products/jdbc/index.html
• JDBC Database Access The Java Tutorial
• http//java.sun.com/docs/books/tutorial/jdbc/index
  .html
• JDBC Documentation
• http//java.sun.com/j2se/1.5.0/docs/guide/jdbc/ind
  ex.html
• java.sql package
• http//java.sun.com/j2se/1.5.0/docs/api/java/sql/p
  ackage-summary.html
• JDBC Technology Guide Getting Started
• http//java.sun.com/j2se/1.5.0/docs/guide/jdbc/get
  start/GettingStartedTOC.fm.html
• JDBC API Tutorial and Reference (book)
• http//java.sun.com/docs/books/jdbc/

30
Miscellaneous

• Setting the Class Path
• Windows
• http//java.sun.com/j2se/1.5.0/docs/tooldocs/windo
  ws/classpath.html
• Solaris and Linux
• http//java.sun.com/j2se/1.5.0/docs/tooldocs/solar
  is/classpath.html
• CSE Oracle Users' Guide
• Oracle 8.1.7
• http//db01.cse.cuhk.edu.hk
• Oracle 8.1.6
• http//db00.cse.cuhk.edu.hk