JDBC

1
JDBC

• CS-328

2
JDBC

• Java API for accessing RDBMS
• Allows use of SQL for RDBMS programming
• Can be used for
• embedded SQL
• execution of stored queries

3
SQL Statement Execution

• Four primary classes used to
• load a driver
• (java.sql.DriverManager)
• connect to the DB
• (java.sql.Connection)
• create a SQL statement
• (java.sql.Statement)
• execute a SQL statement
• (java.sql.ResultSet)

4
Example
try Class.forName (sun.jdbc.odbc.JdbcOdbcDri
ver) String url jdbcodbcmsaccessdb
Connection con DriverManager.getConnection(url
,,) // create sql statement String qs
select from loadtest Statement stmt
con.createStatement() // execute the
statement ResultSet rs stmt.executeQuery(qs)
// process the result set boolean more
rs.next while (more)
System.out.println ............
5
Driver Manager Object

• Loads the proper driver into your java object
• multiple drivers may be loaded to allow
  connections to multiple databases
• Provides a common interface to RDBMSs for JDBC
  Drivers

6
Driver Manager Object
JAVA Application
Driver Manager
Driver A
Driver B
Connection
Connection
Statement
Statement
Result Set
Result Set
7
Driver Manager Class Methods

• getConnection(String url)
• getDriver
• registerDriver
• deregisterDriver
• getDrivers
• setLoginTimeout
• getLoginTimeout
• setLogStream
• getLogStream

8
Connection Object

• Establish link between the JAVA application and
  RDBMS
• allows application to select proper driver when
  it needs to
• uses the database URL to make the connection
• jdbcltsubprotocolgtltsubname)
• jdbcodbcMydatabase
• jdbcdb2157.287.43.11/Mydatabse

9
Connection Class Methods

• createStatement
• prepareStatement
• prepareCall
• NativeSQL
• setAutoCommit
• GetAutoCommit
• commit
• rollback
• close
• isclosed

10
Statement Object

• Wrapper of a string containing a SQL statement
• allows JDBC to decompose the SQL into a set of
  steps to present to the database via the driver
• the connection object forwards the statement
  object to the database to obtain a results set
  object

11
Statement Class Methods

• executeQuery
• executeUpdate
• insert, update, delete
• close
• getResultSet

12
ResultSet Object

• A container for the rows and columns (a table)
  acquired as a result of presenting a statement
  object to the RDBMs using the executeQuery
  statement method

13
ResultSet Class Methods

• next
• close
• wasNull
• getString
• getBoolean
• getByte
• getShort

14
ResultSet Class Methods(cont.)

• getInt
• getLong
• getFloat
• getDouble
• getNumeric
• getBytes
• getDate

15
ResultSet Class Methods (cont.)

• getTime
• getTimeStamp
• getAsciiStream
• getUnicodeStream
• getBinaryStream
• getMetaData
• etc...

16
JDBC Type Mapping

• Char - String
• Varchar - String
• Longvarchar - String
• Numeric - java.sql.Numeric
• Decimal - java.sql.Numeric
• Bit - boolean

• Tinyint - byte
• Smallint short
• Integer - int
• Bigint - long
• Real - float
• Float - double
• Double - double
• Binary - byte

17
JDBC Type Mapping (cont.)

• Varbinary - byte
• Longvarbinary - byte
• Date - java.sql.Date
• Time - java.sql.Time
• Timestamp - java.sql.Timestamp