Database Programming and JDBC

1
Lecture 21

• Database Programming and JDBC

2
Database Programming

• Objective to access a database from an
  application program
• As opposed to interactive interfaces like MySQLs
• Motivation Interactive interfaces are convenient
  but
• Not all queries can be expressed in SQL
  (recursive)
• Impedance mismatch problem (difference between
  database and programming language models)
• Different data types
• Sets/Multi-sets VS tuple-at-a-time (declarative
  VS non-declarative/procedural actions)
• E.g. display selected tuples in a GUI

3
Database Programming Approaches

• (1) Embedded commands DB commands are embedded
  in a general-purpose host programming language
• such as COBOL, C, Java, etc
• Embedded SQL commands are identified by a special
  prefix
• E.g. EXEC SQL
• A pre-compiler or a preprocessor first scans the
  code to identify database statements and extracts
  them for processing by the DBMS
• Replaced by function calls to the DBMS-generated
  code
• Static (queries must be known at compile time)

4
Embedded Commands Example
5
Database Programming Approaches

• (2) Library of database functions available to
  the host language for database calls
• known as an API (Application Programming
  Interface)
• Host language uses library functions to connect
  to a database, execute a query, execute an
  update, etc
• Database queries/update commands (along with
  other information) are sent as parameters to
  function calls
• Dynamic
• E.g. ODBC (Object DataBase Connectivity) and JDBC
  (used to stand for Java DataBase Connectivity)
• ODBC SQL Access Group, chiefly Microsoft, in
  1992
• JDBC Like ODBC but enables SQL function calls
  for Java programming

6
Database Programming Approaches

• Embedded SQL provides static database programming
• API dynamic database programming
• Advantage no preprocessor needed (thus more
  flexible)
• No compile-time restrictions
• Drawback SQL syntax checks to be done at
  run-time
• E.g. When executing an SQL statement via
  ODBC/JDBC, it is not checked for errors until it
  is run

7
Java Database Connectivity

• A Java program with JDBC functions
• can access any relational DBMS that has a JDBC
  driver (JDBC compliant)
• A JDBC driver is an implementation of the
  function calls specified in the JDBC API
  (java.sql.) for a particular RDBMS
• A JDBC driver translates JDBC function calls into
  DBMS-specific calls that are understood by DBMS
  and vice-versa
• can connect to several databases known as data
  sources
• All JDBC drivers loaded (dynamically) by an
  application are registered with a driver manager
• Driver manager is responsible to manage different
  drivers for different DBMSs used within the same
  program

8
JDBC Architecture
DBMS
Driver 1
Java Application
JDBC Driver manager
DBMS
Driver 2
Driver 3
DBMS
Application -Initiates and terminates connection
with a data source -Submits SQL statements and
retrieves results through the JDBC API Driver
Manager -Loads JDBC driver and passes JDBC
function calls from application to correct
driver Driver(s) -Establishes the connection with
the data source (thru Driver Manager) -Submits
requests from and return results to
application -Translates data and errors from data
source format to JDBC standard Data
source(s) -Processes commands from the driver and
returns results
9
The JDBC Steps

• Importing Packages
• Loading Registering the JDBC Drivers
• Registers driver with driver manager
• Opening a Connection to a Database
• Also ties driver and DBMS to driver manager
• Creating a Statement Object
• Executing a Query and Returning a Result Set
  Object
• Processing the Result Set
• Garbage collection
• Closing the Result Set, Statement and Connection
  Objects

10
(No Transcript)
11
Steps in JDBC Database Access

• (1) Import JDBC library (import java.sql.)
• javax.sql. (for advanced features, such as
  scrollable result sets)
• (2) Load register JDBC driver with manager
• Class.forName("com.mysql.jdbc.Driver")
• This will load the driver and register it with
  the driver manager in order to make it available
  to the program
• Can also be done in two steps
• Initializing/loading a driver (for oracle)
  drivernew com.mysql.jdbc.Driver()
• Registering it with the DriverManager
  DriverManager.registerDriver(driver)

12
Steps in JDBC Database Access

• (3) Create a connection object (via
  getConnection)
• DriverManager.getConnection(jdbcmysql
  //lthostgt ltportgt/ltdb-accountgt, ltusernamegt,
  ltpasswordgt)
• Connection myConnection DriverManager.
  getConnection("jdbcmysql// devsrv.cs.csbsju.edu
  3306/irahal_compnay","irahal",")
• (4) Create a statement object
• Statement
• Statement stmt myConnection.createStatement()
• String queryString "Select lname As LastName,
  fname As FirstName, salary from employee where
  fname like '"name"'"
• name is a variable of type string
• PreparedStatment (notice the question mark ?)
• String queryString "Select count() as COUNTER
  from employee where salarygt?"
• preparedStmt myConnection.prepareStatement
  (queryString)

13
Prepared Statement motivation

• Why use PreparedStatements
• prepared already compiled (querystring is a
  parameter)
• Used if the same query is to be executed multiple
  times since it would be compiled only once
• for queries that are executed many times with
  possibly different contents
• Suppose we would like to run the query
• SELECT FROM EMPLOYEE WHERE LNAMEMoon
• But we would like to run this for a number of
  employees (separately), not only Moon

14
Additional Steps for PreparedStatments in JDBC
Database Access

• (4-1) Identify statement parameters (designated
  by question marks (?) in the statement string)
• String queryString "Select count() as COUNTER
  from employee where salarygt?"
• preparedStmt myConnection.prepareStatement
  (queryString)
• (4-2) bind parameters to program variables
• pst.setString(1, var1) where pst is a
  PreparedStatement variable
• preparedStmt.setFloat(1,salary)
• Set 1st ? in my query to the value of salary
  which is a float

15
Statement
ResultSet
ExecuteQuery(String Q)
createStatement()
Statement
Connection
int
ExecuteUpdate(String Q)
PreparedStatement
setString(int num,String val)
ResultSet
ExecuteQuery()
Prepared Statement
prepareStatement (String Q)
int
Connection
ExecuteUpdate()
16
Querying with Prepared Statement
String queryStr "SELECT FROM EMPLOYEE WHERE
FName ? AND SALARY lt ? PreparedStatement
pstmt con.prepareStatement(queryStr) pstmt.set
String(1, Joe) pstmt.setFloat2,
100000) ResultSet rs pstmt.executeQuery()
17
Affecting the DB with PreparedStatement
String deleteStr DELETE FROM EMPLOYEE WHERE
LNAME ? and SALARY ? PreparedStatement
pstmt con.prepareStatement(deleteStr) pstmt.s
etString(1, RAHAL) pstmt.setFloat(2,
10000) int delnum pstmt.executeUpdate()
18
(No Transcript)
19
Steps in JDBC Database Access

• (5) Execute SQL statement (referenced by an
  object) via JDBCs
• executeQuery (SELECT statements)
• Returns a ResultSet
• resultset1 stmt.executeQuery(queryString)
  (regular statement)
• resultset2 preparedStmt.executeQuery()
  (prepared statement)
• executeUpdate (DDL, INSERT, UPDATE, and DELETE
  statements)
• Returns how many tuples have been affected (an
  integer)
• result1 stmt.executeUpdate(updateString)
  (regular statement)
• result2 preparedStmt.executeUpdate() (prepared
  statement)

20
Steps in JDBC Database Access

• (6) Process query results
• ResultSet is a 2-dimensional table
• Only one ResultSet per Statement can be opened at
  once
• ResultSet object maintains a cursor pointing to
  its current row of data
• The 'next' method moves the cursor to the next
  row
• As of JDBC 2.0, scrollable ResultSets are
  available, which also include previous,
  first, last, etc..
• Any methods on the ResultSet will occur on the
  current row

21
ResultSet

• The cursor is positioned before the 1st row upon
  creation
• Statement stmtcon.createStatement()
• ResultSet rs stmt.executeQuery ("SELECT FROM
  Table1")
• while (rs.next()) //something

22
ResultSet methods

• Getting the value in some column (for the current
  row) - depends on attribute data type
• getString(int columnNum)
• getString(String columnName)
• getInt(int columnNum)
• getInt(String columnName)
• Etc
• To check if null was returned, you have to use
  wasNull() on the ResultSet after getting the
  value

String s rs.getString(column1")
23
Mapping Java Types to SQL Types
SQL type
The driver maps SQL types (varchar, number,) to
the appropriate Java method (getString, getInt)
Java method
24
Cleaning Up After Yourself

• (7) Remember to close the Connections,
  Statements, PreparedStatements and ResultSets in
  the following order

25
Timeouts

• You can use Stmt.setQueryTimeOut(int secs) to set
  a timeout for the driver to wait for a statement
  to be completed
• If the operation is not completed in the given
  time, an SQLException is thrown

26
Dealing With Exceptions
catch (SQLException e) while (e ! null)
//human readable message about the
exception System.out.println(e.getMessage()) //
String describing the reason of the
exception System.out.println(e.getSQLState())
//driver-dependant error code for the
exception System.out.println(e.getErrorCode())
e e.getNextException()
27
What will this do?

• String table1create table table1(col1 integer,
  col2 integer)
• Statement stcon.createStatement()
• int resCreatest.executeUpdate(table1)
• PreparedStatement ps1 con.prepareStatement(inse
  rt into Table1 values(?,?))
• for(int i0ilt10i)
• ps1.setInt(1,i)
• ps1.setInt(2,ii)
• ps1.executeUpdate()
• Statement st2con.createStatement()
• ResultSet rsexecuteQuery(select col2 from
  Table1)
• while(rs.next())
• System.out.println(rs.getInt(1))

28
Useful JDBC links

• http//dev.mysql.com/doc/refman/5.0/en/java-connec
  tor.html
• http//java.sun.com/j2se/1.3/docs/guide/jdbc/getst
  art/GettingStartedTOC.fm.html
• http//notes.corewebprogramming.com/student/JDBC.p
  df