Java DataBase Connectivity JDBC

1
Java DataBase Connectivity (JDBC)

• Joe Komar

2
JDBC Overview

• API (java.sql)
• Classes and methods to
• establish a connection with a database
• send SQL statements to that database
• process the results
• Vendors of database systems write drivers for
  JDBC
• Java code remains the same (except maybe
  sometimes for driver reference)

3
Example JDBC code
Connection con DriverManager.getConnection (
jdbcodbcData Base Alpha,
login, password) Statement stmt
con.createStatement() ResultSet rs
stmt.executeQuery(SELECT a,b,c FROM
Table1) while (rs.next()) int x
rs.getInt(a) String s
rs.getString(b) float f
rs.getFloat(c)
4
JDBC Credibility

• Based on the X/Open SQLCall Level Interface (CLI)
  for client/server interactions
• Based on ANSI SQL-92 standard for SQL compliance
• Endorsed by all the major database vendors
• Drivers already written for most of those
  databases

5
Overall JDBC Structure
Java Application
JDBC API
JDBC Driver Manager
JDBC Driver
JDBC Driver
DBMS
DBMS
6
JDBC-ODBC Bridge

• A bridge that will link Java code to Windows ODBC
  drivers
• Need to register database with ODBC before use
• ODBC uses SQL statements for manipulation and
  query

7
Registering or Creating an ODBC Database

• Click on Start, Settings, Control Panel
• Click on 32 bit ODBC
• Click on the Add button and choose a driver such
  as Microsoft Access
• Type in Data Source Name (the name you will use
  in your Java Program) and description (anything
  you want)
• Click on Select to select an existing file or
  Create to create an empty file

8
Registering or Creating an ODBC Database

• Locate the file or specify the file to create
  (note can use Network button to identify file on
  server)
• The Advanced button allows you to set security
  such as username, password, type of access
  allowed, etc.
• Can then view the file using Access

9
Example Create a Table
import java.net.URL import java.sql. public
class CreateSuppliers public static
void main(String args) String
url "jdbcodbcTest One"
Connection con String
createString createString
"create table SUPPLIERS "
"(SUP_ID int, "
"SUP_NAME varchar(40), "
"STREET varchar(40), "
"CITY varchar(20), "
"STATE char(2), ZIP
char(5))" Statement stmt
10
Example Create Table
try Class.forName("sun.jdbc.odbc.Jdb
cOdbcDriver") catch(java.lang.ClassNotFound
Exception e) System.err.print("ClassN
otFoundException ")
System.err.println(e.getMessage())
11
Example Create Table
try con DriverManager.getConnection(ur
l, "myLogin",
"myPassword") stmt con.createStatement
() stmt.executeUpdate(createString)
stmt.close() con.close()
catch(SQLException ex) System.err.println("S
QLException " ex.getMessage())
12
Example Populate Table
import java.sql. public class InsertSuppliers
public static void main(String args)
String url "jdbcodbcTest
One" Connection con
Statement stmt String query
"select SUP_NAME, SUP_ID from SUPPLIERS"
13
Example Populate Table
try Class.forName("sun.jdbc.odbc.JdbcO
dbcDriver") catch(java.lang.ClassNotFoundExce
ption e) System.err.print("ClassNotFoun
dException ") System.err.println(e.getM
essage())
14
Example Populate Table
try con DriverManager.getConnection(ur
l, "myLogin",
"myPassword") stmt con.createStatement
()
15
Example Populate Table
stmt.executeUpdate("insert into SUPPLIERS "
"values(49, 'Superior Coffee', '1 Party Place', "
"'Mendocino', 'CA', '95460')") stmt.executeUp
date("insert into SUPPLIERS " "values(101,
'Acme, Inc.', '99 Market Street', "
"'Groundsville', 'CA', '95199')") stmt.executeUp
date("insert into SUPPLIERS " "values(150,
'The High Ground', '100 Coffee Lane', "
"'Meadows', 'CA', '93966')")
16
Example Populate Table
ResultSet rs stmt.executeQuery(query) System.o
ut.println("Suppliers and their ID
Numbers") while (rs.next()) String s
rs.getString("SUP_NAME") int n
rs.getInt("SUP_ID") System.out.println(s
" " n) stmt.close() con.close()
17
Example Populate Table
catch(SQLException ex)
System.err.println("SQLException "
ex.getMessage())
18
Connection Interface

• Establishes communication (connection) between
  program and database
• Established by invoking the static
  getConnection(url) method of the DriverManager
  class
• url String jdbcltsubprotocolgtltsubnamegt
• subprotocol is typically the driver name
• subname may include a network connection
  specification and file name

19
Connection Interface

• Used to create a new Statement object
• con.createStatement()
• Other methods of interest
• void close() - frees resources
• void commit() - commits transactions
• DataBaseMetaData getMetaData()
• CallableStatement prepareCall(String sql)
• void rollback() - rolls back to previous commit
  or rollback
• void setAutoCommit(boolean enable)

20
DriverManager Class

• Management layer of JDBC
• Keeps track of drivers available (drivers
  register with the DriverManager)
• Establishes connections between drivers and
  databases
• Monitors connection for things like time out, log
  messages, and trace messages
• All methods are static (constructor is private)

21
Statement Class

• Object created with createStatement() of the
  Connection class
• Used to send a simple SQL statement with no
  parameters
• PreparedStatement object executes a precompiled
  SQL statement
• CallableStatement object executes a call to a
  database stored procedure

22
Statement Class Methods

• void close()
• ResultSet executeQuery(String sql)
• int executeUpdate(String sql)
• void setMaxRows(int max)
• void setQueryTimeout(int seconds)

23
ResultSet Interface

• Queries return a table of rows and columns
• SELECT a,b,c FROM Table1 gives

12345 Joe Komar 12.50 33333 Tom
Sturm 350.35 55555 Jennifer Page 756.44
24
ResultSet Sample Code
ResultSet rs stmt.executeQuery(SELECT a,b,c
FROM Table1) while rs.next() int i
rs.getInt(a) String s rs.getString(2)
//can use column number float f
rs.getFloat(c) System.out.println(i
s f)
25
ResultSet Interface

• get methods for all of the java primitive data
  types, plus some
• Can use Object getObject(int col or String name)
  to get an unknown type
• Object returned will have the Java type
  corresponding to the JDBC type -- see table

26
JDBC Type Java Type CHAR String VARCHAR Str
ing LONGVARCHAR String NUMERIC java.math.BigDec
imal DECIMAL java.math.BigDecimal BIT boolean
TINYINT byte SMALLINT short INTEGER int BIG
INT long REAL float FLOAT double DOUBLE
double BINARY byte VARBINARY byte LONGVAR
BINARY byte DATE java.sql.Date TIME java.
sql.Time TIMESTAMP java.sql.Timestamp
27
ResultSetMetaData Interface

• Object created from a ResultSet object
• ResultSet rs stmt.executeQuery()
• ResultSetMetaData rsmd rs.getMetaData()
• Invoke ResultSetMetaData methods to get at the
  information in the object

28
ResultSetMetaData Interface
int numCols rsmd.getColumnCount() for (int i
1 i lt numCols i) int jdbcType
rsmdgetColumnType(i) String name
rsmd.getColumnTypeName(i) System.out.println(
Column i is JDBC type jdbcType
, which is JDBC type name)
29
ResultSetMetaData Interface

• String getColumnLabel(int col) -- heading
• String getColumnName(int col) -- name
• int getScale(int col) -- decimal places
• String getTableName(int col) -- table name
• boolean isCurrency(int col) -- cash value
• boolean isReadOnly(int col) -- read only
• etc.