Java Database Connectivity (JDBC)

1
Java Database Connectivity (JDBC)

• Francisco Pajaro
• Saul Acosta
• Nahum Quezada
• Manuel Rubio

2
Lecture Outline

• The JDBC API
• The JDBC Driver Interface
• The Most Common Use of JDBC
• Structured Query Language
• How to Construct SQL Statements
• The JDBC-ODBC Bridge
• Register your Database as an ODBC Data Source
• Things to Do in your Java Program
• Load the Drivers
• Make the connection
• Create JDBC Statements
• Execute the SQL Statement
• Retrieve Values from the Result Set

3

• JDBC Introduction
• JDBC provides a standard library for accessing
• relational databases
• API standardizes
• Way to establish connection to database
• Approach to initiating queries
• Method to create stored (parameterized)
  queries
• The data structure of query result (table)
• Determining the number of columns
• Looking up metadata, etc.
• API does not standardize SQL syntax
• JDBC is not embedded SQL
• JDBC classes are in the java.sql package
• Note JDBC is not officially an acronym
• unofficially, Java DataBase Connectivity is
• commonly used

4

• The JDBC API is expressed as a series of abstract
  Java interfaces that allow an application
  programmer to open connections to particular
  databases, execute SQL statements, and process
  the results. The most important interfaces are
• java.sql.DriverManager which handles loading of
  drivers and provides support for creating new
  database connections
• java.sql.Connection which represents a connection
  to a particular database
• java.sql.Statement which acts as a container for
  executing a SQL statement on a given connection
• java.sql.ResultSet which controls access to the
  row results of a given Statement

5
The JDBC Driver Interface

• JDBC consists of two parts
• JDBC API, a purely
• Java-based API
• JDBC Driver Manager,which
• communicates with
• vendor-specific drivers that
• perform the real communication
• with the database.
• Point translation to vendor
• format is performed on
• the client
• No changes needed
• to server
• Driver (translator) needed
• on client

6
Application in Java
odbc standard API
jdbc API
Application in Java
DriverManager
Sybase driver
mSQL driver
Informix driver
7
The Most Common Use of JDBC

• Perhaps called the Intranet scenario. For
  example, a company the most common use of these
  Java applications will be within a company or on
  an "Intranet," so this might be called the
  Intranet scenario. For example, a company might
  implement all of its corporate applications in
  Java using GUI building tools that generate Java
  code for forms based on corporate data schemas.
  These applications would access corporate
  database servers on a local or wide area network.
  However, Java applications could also access
  databases through the Internet. The JDBC Guide

8
Structured Query Language
SQL Keyword Description
SELECT Select fields from one or more tables
FROM Tables from which to get fields
WHERE Criteria for selection that determine the rows to be retrieved
GROUP BY How to group records
HAVING Used by the GROUP BY clause to specify criteria for grouping records in the results
ORDER BY Criteria for ordering of records
9
JDBC Data Types
10
Executing a SELECT query

• First create a Statement object
• Then create a query string. For example
• Execute the query
• The ResultSet object returned contains the
  results of the query.

Statement stmt conn.createStatement()
String query "SELECT FROM table"
ResultSet rs stmt.executeQuery(query)
11
Getting query results (1)

• Column names and number of columns. If rs is a
  ResultSet object then number of columns in table
  is
• The name of column i (i1,2,3,...) is

ResultSetMetaData rsmd rs.getMetaData()int
numberOfColumns rsmd.getColumnCount()
String colName rsmd.getColumnName(i)
12
Getting query results (2)

• Iterating over the rows of a table

while (rs.next()) for (int i 1 i lt
numberOfColumns i) String
columnValue rs.getString(i) // do
something with columnValue
There are lots of get methods for retriving
column values asintegers, doubles, etc.
13
Executing a simple command

• First create a Statement object
• Then create a command string. For example
• Execute the command

Statement stmt conn.createStatement()
String cmd "DROP TABLE IF EXISTS test"
stmt.execute(cmd)
14
The JDBC-ODBC Bridge

• To connect to an MS Access Database you will use
  JDBC-ODBC bridge, which is included as a standard
  part of the JDK. You connect using the ODBC
  Administrator, to connect to the appropriate data
  source name.
• Because ODBC has been around for quite a while
  (longer than the Java language), ODBC drivers are
  rather ubiquitous. This makes this type of JDBC
  driver a good choice for learning how to connect
  Java programs to databases. The extra level of
  indirection, however, can result in a performance
  penalty as the JDBC is transferred into ODBC,
  which is then transferred into the
  database-specific protocol.

15
ODBC
ODBC
DB Server
Client Front-end Application
Started as a PC-standard. Now its an industry
standard.
16
JDBC-ODBC Bridge

• A layer between the Java front-end application
  and the database server
• Client talks to JDBC
• JDBC communicates with underlying ODBC
• ODBC communicates with database server
• Results passed in reverse order to above

17
Necessary classes

• Connection Establishes a connection with the SQL
  database driver manager
• Statement Allows developer to frame fixed SQL
  queries
• PreparedStatement Allows developer to frame SQL
  queries with parameters
• ResultSet Used to collect the results of an SQL
  query

18
What is a data source?

• A data source is a source of data and the
  connection information needed to access that
  data.
• Examples
• Microsoft Access
• Microsoft SQL Server
• Oracle
• spreadsheet
• text file

19

• Examples of connection information
• Server location
• Database name
• Logon ID
• password
• options that describe how to connect to the data
  source

20
To connect to these data sources, you must do the
following

• Install the appropriate ODBC driver on the
  computer that contains the data source Microsoft
  SQL Server
• Define a data source name (DSN) by using either
  the ODBC Data Source Administrator spreadsheet

21
How to register your Database as an ODBC Data
Source?

• Open up your ODBC Data Sources Administrator.
• Click on the System DSN tab and click on the
  Add button.

22

• The Create a New Data Source dialog will appear
• Select the driver and click on Finish.

23

• The ODBC Microsoft Access Setup dialog will
  appear.
• In the Data Source Name box, enter a name.
• Click the Select Directory button.

24

• Locate the data base.
• Click on Ok to exit the dialog.

25

• And finally, you should now be able to access
  your data source using ODBC.

26
Things to Do in your Java Program

• Load the Drivers
• Establish the Connection
• Create JDBC Statement
• Execute the SQL Statement
• Retrieve Values from the Result Set

27
Load the Drivers

• Loading the driver or drivers you want to use is
  very simple and involves just one line of code.
  If, for example, you want to use the JDBC-ODBC
  Bridge driver, the following code will load it
• Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")

28
Overview of Database Querying with JDBC
Connect
Query
Processresults
Close
29
Key Classes and Methods in java.sql
30
Stage 1 Connect
Connect
Register the driver
Connect to the database
Query
Processresults
Close
31
Make the connection

• The second step in establishing a connection is
  to have the appropriate driver connect to the
  DBMS. The following lines of code illustrates the
  general idea
• String dataSourceName "mdbTEST"
• String dbURL "jdbcodbc" dataSourceName
• Connection con DriverManager.getConnection
• (dbURL, "admin","students")

32
Stage 2 Query
Connect
Query
Create a statement
Query the database
Processresults
Close
33
Create JDBC Statements

• A Statement object is what sends your SQL
  statement to the DBMS. You simply create a
  Statement object and then execute it, supplying
  the appropriate execute method with the SQL
  statement you want to send. For a SELECT
  statement, the method to use is executeQuery .
  For statements that create or modify tables, the
  method to use is executeUpdate .
• It takes an instance of an active connection to
  create a Statement object. In the following
  example, we use our Connection object con to
  create the Statement object stmt
• Statement s con.createStatement( )

34
Execute the SQL Statement

• At this point s exists, but it does not have an
  SQL statement to pass on to the DBMS. We need to
  supply that to the method we use to execute s .
  For example, in the following code fragment, we
  supply executeQuery with the SQL statement from
  the example above
• s.execute("CREATE TABLE Students
• (ID_Number integer,
• FirstName VARCHAR(35),
• LastName VARCHAR(35),
• EMail VARCHAR(35),
• University VARCHAR(35))")
• ResultSet ResultSet s.executeQuery(query)

35
Stage 3 Process the Results
Connect
Query
Step through the results
Assign results to Java variables
Processresults
Close
36
Retrieve Values from the Result Set

• We now show how you send the SELECT statements
  from a program written in the Java programming
  language and how you get the results we showed.
• JDBC returns results in a ResultSet object, so we
  need to declare an instance of the class
  ResultSet to hold our results. The following code
  demonstrates declaring the ResultSet object rs
  and assigning the results of our earlier query to
  it
• String query "SELECT FROM
  Students"
• ResultSet ResultSet s.executeQuery(query)

37
Inserting/Updating/Deleting Records

• Statement.executeUpdate
• Executes an SQL INSERT, UPDATE or DELETE
  statement.
• Parameters
• sql - an SQL INSERT, UPDATE or DELETE statement
  or an SQL statement that returns nothing
• Returns Either the row count for INSERT, UPDATE
  or DELETE statements, or 0 for SQL statements
  that return nothing
• s.execute("INSERT INTO Students VALUES
• (890124532 ,
• ' Manuel ',
• ' Rubio ',
• ' marubio_at_utep.edu',
• ' UTEP')")

38
Inserting/Updating/Deleting Records

• s.execute("UPDATE Students
• SET FirstName ' Alonso '
• WHERE LastName 'Rubio '")
• s.execute("DELETE FROM Students
• WHERE LastName ' Pajaro'")

39
Stage 4 Close
Connect
Query
Close the result set
Processresults
Close the statement
Close
Close the connection
40
How to Close the Connection
1. Close the ResultSet object.2.
Close the Statement object.3. Close the
connection (not necessary for a
server-side driver).
ResultSet.close()
s.close()
con.close()