CSI%203317%20(LAB%203)

1
CSI 3317 (LAB 3)

• JDBC API

2
What is JDBC API?

• Trademark name - Not an acronym
• Java Database Connectivity
• Java API for tabular data access
• Easy to send SQL statements to RDBMS

3
What Does JDBC API Do?

• Establish a connection with a data source
• Send queries and update statements to data source
• Process the results

4
What Does JDBC API Do?

• Loading Drivers
• import java.sql.
• Class.forName(sun.jdbc.odbc.JdbcOdbcDriver)
• More information on Oracle/JDBC Drivers at
• http//www.csee.umbc.edu/help/oracle8/java.815/a64
  685/getsta1.htm1002361

5
What Does JDBC API Do?

• Establish a Connection
• Connection con
• DriverManager.getConnection(
• "jdbcodbcSITEACAD", USERNAME,
  PASSWORD)
• This will get a connection from the connection
  factory.
• Also notice the ODBC (open database connectivity)
  as a part of connection string. Another example
  could be jdbcmyDrivermyurl

6
What Does JDBC API Do?

• Send Queries and Process Results
• Statement stmt con.createStatement()
• Create a Statement object so we can submit SQL
  statements to the driver.
• ResultSet rs stmt.executeQuery(Select from
  Location)
• Create a Result set to handle the returned data
  from the query pushed.

7
JDBC versus ODBC

• ODBC not appropriate for Java
• Java to C calls security , implementation,
• robustness and automatic portability.
• No pointer for Java No need to literal ODBC
  translation.
• JDBC a pure java solution.

8
Two tier Model
Java Application
Client Machine
JDBC
DBMS-proprietary protocol
Database Server
DBMS
9
Three tier Model
Java applet or HTML browser
Client Machine (GUI)
HTTP, RMI, CORBA or Other calls
Application Server
Server Machine (business logic)
JDBC
DBMS-proprietary protocol
Database Server
DBMS
10
Moving Cursor in Scrollable Result set

• Creating a Scrollable Result Set
• Statement stmt con.createStatement(
  ResultSet.TYPE_SCROLL_INSENSITIVE,
• ResultSet.CONCUR_READ_ONLY)
• ResultSet srs
• stmt.executeQuery(Select from
• Location)

11
Moving Cursor in Scrollable Result

• Moving the cursor Forward and Backward
• Forward
• Statement stmt con.createStatement(
  ResultSet.TYPE_SCROLL_INSENSITIVE,
  ResultSet.CONCUR_READ_ONLY)
• ResultSet srs
• stmt.executeQuery(Select from Student)
• while (srs.next())
• String FirstName srs.getString(SFNAME)
• String LastName srs.getString(SLNAME)
• System.out.println(FirstName LastName)
• Backward
• srs.afterLast()
• while (srs.previous())
• String FirstName srs.getString(SFNAME)
• String LastName srs.getString(SLNAME)
• System.out.println(FirstName LastName)

12
Making Updates(to updatable Result Set)

• Creating an Updatable Result Set
• Connection con DriverManager.getConnection("jdbc
  odbcSITEACAD",USERNAME,PASSWORD)
• Statement stmt con.createStatement(ResultSet.TYP
  E_SCROLL_SENSITIVE,
• ResultSet.CONCUR_UPDATABLE)
• ResultSet uprs stmt.executeQuery(Select from
  Student)
• Updating Programmatically
• stmt.executeUpdate(UPDATE Student SET FID 2
  Where SFName Brian)
• OR
• uprs.last()
• uprs.updateFloat(FID, 2F)
• To make the change reflect in database use
• uprs.updateRow()

13
import java.sql. public class
InsertRows public static void main(String
args) String url "jdbcodbcSITEACAD"
Connection con Statement stmt tryClass.forN
ame("oracle.jdbc.driver.OracleDriver") catch
(java.lang.ClassNotFoundException
e) System.err.print("ClassNotFoundException
") System.err.println(e.getMessage()) t
ry con DriverManager.getConnection("jdb
coraclethin_at_oracle-prod1521OPROD",USERNAME,
PASSWORD) stmt con.createStatement(Resu
ltSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDA
TABLE) ResultSet uprs
stmt.executeQuery("Select from
Location") uprs.moveToInsertRows()
uprs.updateInt("locid", 58)
uprs.updateString("BLDG_CODE", "CBY")
uprs.updateInt("ROOM", 531)
uprs.updateInt("capacity", 58)
uprs.insertRow() uprs.close()
stmt.close() con.close()
catch(SQLException ex) System.err.println("SQ
LException " ex.getMessage())