Title: JDBC: Part I
1JDBC Part I
2Attribution
- These slides are based on three primary sources
- Sun JDBC Tutorial
- URL http//java.sun.com/docs/books/tutorial/
jdbc/TOC.html - George Reese, Database Programming with JDBC and
Java (OReilly Associates.) - Marty Hall, Core Web Programming (Prentice
Hall.)
3Road Map
- Introduction to JDBC/JDBC Drivers
- Overview Six Steps to using JDBC
- Example 1 Setting up Tables via JDBC
- Example 2 Inserting Data via JDBC
- Example 3 Querying Data via JDBC
- Exception Handling Overview
4Introduction to JDBC andJDBC Drivers
5Introduction to JDBC
- JDBC is a simple API for connecting from Java
applications to multiple databases. - Lets you smoothly translate between the world of
the database, and the world of the Java
application. - The idea of a universal database access API is
not a new one. - For example, Open Database Connectivity (ODBC)
was developed to create a single standard for
database access in the Windows environment. - JDBC API aims to be as simple as possible while
providing developers with maximum flexibility.
6Understanding JDBC Drivers
- To connect to a database
- , you first need a JDBC Driver.
- JDBC Driver
- set of classes that interface with a specific
database engine.
Java Application
JDBC Driver Manager
JDBC- ODBC Bridge
Vendor Specific JDBC Driver
Vendor Specific JDBC Driver
Database
Database
Diagram Source Marty Hall, Core Web
Programming (Prentice Hall.)
7JDBC Drivers
- JDBC drivers exist for every major database
including - Oracle, SQL Server, Sybase, and MySQL.
- For MySQL,
- we will be using the open source MySQL
Connector/J. - http//www.mysql.com/downloads/api-jdbc.html.
8Installing the MySQL Driver
- To use the MySQL Connector/J Driver,
- you need to download the complete distribution
- and
- Add the following JAR to your CLASSPATH (change
version with the actual version of connector you
downloaded such as 3.1.7 - mysql-connector-java-version-stable-bin.jar
- To use the driver within Tomcat, copy the jar
file above to - TOMCAT_HOME\ROOT\WEB-INF\lib
9Overview Six Steps to Using JDBC
10Six Steps to Using JDBC
- Load the JDBC Driver
- Establish the Database Connection
- Create a Statement Object
- Execute a Query
- Process the Results
- Close the Connection
111) Loading the JDBC Driver
- To use a JDBC driver,
- you must load the driver via the Class.forName()
method. - In general, the code looks like this
- Class.forName("jdbc.DriverXYZ")
- where jbdc.DriverXYZ is the JDBC Driver you want
to load. - To use MySQL and load jdbc driver
- Class.forName(com.mysql.odbc.driver")
- To use ORACLE and load jdbc driver
- Class.forName(oracle.odbc.driver.OracleDriver")
- If you are using SUN JDBC-ODBC Driver, your code
will look like this - Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
12Loading the MySQL Driver Class.forName("com.mysql
.jdbc.Driver")//use the above with project 5 to
load driver
- If you are using the MM MySQL Driver, your code
will look like this - try
- Class.forName("com.mysql.jdbc.Driver")
- catch(java.lang.ClassNotFoundException e)
- System.err.print("ClassNotFoundException
") - System.err.println(e.getMessage())
-
- Class.forName() will
- throw a ClassNotFoundException if your CLASSPATH
is not set up properly. - Hence, it's a good idea to surround the forName()
with a try/catch block.
132) Establish the Connection
- Once you have loaded your JDBC driver,
- the next step is to establish a database
connection. - The following line of code illustrates the basic
idea - Connection con DriverManager.getConnection(url)
14Creating a Connection URL
- The only difficulty in establishing a connection
is specifying the correct URL. - In general, the URL has the following format
jdbcsubprotocolsubname. - JDBC indicates that this is a JDBC Connection (no
mystery there!) - The subprotocol identifies the driver you want to
use. - The subname identifies the database
name/location.
15Connection URL ODBC
- For example, the following code uses a
- JDBC-ODBC bridge to connect to the local Fred
database - String url "jdbcodbcFred"
- Connection con DriverManager.getConnection(url,
username", "password")
16Connection URL MySQLuse this with project5
- Here's how you might connect to MySQL
- String url
- "jdbcmysql//localhost/databasename"
- Connection con DriverManager.getConnection(u
rl) - In this case,
- we are using the MySQL JDBC Driver
- to connect to the databasebasename,
- located on the localhost machine.
- If this code executes successfully, we will have
a Connection object for communicating directly
with the database.
173) Create a Statement Object
- The JDBC Statement object sends SQL statements to
the database. - Statement objects are created from active
Connection objects. - For example
- Statement stmt con.createStatement()
- With a Statement object, you can issue SQL calls
directly to the database.
184) Execute a Query
- executeQuery()
- Executes the SQL query and returns the data in a
table (ResultSet) - The resulting table may be empty but never null
-
- ResultSet results
- statement.executeQuery("SELECT a, b FROM
table") - executeUpdate()
- Used to execute for INSERT, UPDATE, or DELETE SQL
statements - The return is the number of rows that were
affected in the database - Supports Data Definition Language (DDL)
statements CREATE TABLE, DROP TABLE and ALTER
TABLE
19Useful Statement Methods
- getMaxRows/setMaxRows
- Determines the number of rows a ResultSet may
contain - Unless explicitly set, the number of rows are
unlimited (return value of 0) - getQueryTimeout/setQueryTimeout
- Specifies the amount of a time a driver will wait
for a - STATEMENT to complete before throwing a
- SQLException
205) Process the Results
- A ResultSet contains the results of the SQL
query. - Useful Methods
- All methods can throw a SQLException
- close
- Releases the JDBC and database resources
- The result set is automatically closed when the
associated Statement object executes a new query - getMetaDataObject
- Returns a ResultSetMetaData object containing
information about the columns in the ResultSet
21ResultSet (Continued)
- Useful Methods
- next
- Attempts to move to the next row in the ResultSet
- If successful true is returned
- otherwise, false
- The first call to next positions the cursor a the
first row
22ResultSet (Continued)
- Useful Methods
- findColumn
- Returns the corresponding integer value
corresponding to the specified column name - Column numbers in the result set do not
necessarily map to the same column numbers in the
database - getXxx
- Returns the value from the column specified by
column name or column index as an Xxx Java type - Returns 0 or null, if the value is a SQL NULL
- Legal getXxx types
double byte int Date String float short
long Time Object
236) Close the Connection
- To close the database connection
- stmt.close()
- connection.close()
- Note Some application servers, such as BEA
WebLogic maintain a pool of database connections. - This is much more efficient, as applications do
not have the overhead of constantly opening and
closing database connections.
24Example 1Setting Up Tables via JDBC
25The Coffee Tables
- To get started, we will first examine JDBC code
for creating new tables. - This java code creates a table for storing coffee
data - Heres the SQL Statement
- CREATE TABLE COFFEES
- (COF_NAME VARCHAR(32),
- SUP_ID INTEGER,
- PRICE FLOAT,
- SALES INTEGER,
- TOTAL INTEGER)
26The Coffees Tables
27The Coffee Table
- You could create this table via MySQL, but you
can also create it via JDBC. - A few things to note about the table
- The column named SUP_ID contains an integer value
indicating a Supplier ID. - Suppliers will be stored in a separate table.
- In this case, SUP_ID is referred to as a foreign
key. - The column named SALES stores values of SQL type
INTEGER and indicates the number of pounds of
coffee sold during the current week. - The final column, TOTAL, contains a SQL INTEGER
which gives the total number of pounds of coffee
sold to date.
28import java.sql. public class CreateCoffees
public static void main(String args)
String url "jdbcmysql//localhost/coffee"
Connection con String
createString createString "create
table COFFEES "
"(COF_NAME VARCHAR(32), "
"SUP_ID INTEGER, "
"PRICE FLOAT, "
"SALES INTEGER, "
"TOTAL INTEGER)" Statement stmt
29 try Class.forName("com.mysql
.jdbc.Driver") catch(java.lang.ClassNot
FoundException e)
System.err.print("ClassNotFoundException ")
System.err.println(e.getMessage())
try con
DriverManager.getConnection(url)
stmt con.createStatement()
stmt.executeUpdate(createString)
stmt.close() con.close()
catch(SQLException ex)
System.err.println("SQLException "
ex.getMessage())
1
2
3
4
6
30Example 2Inserting Data via JDBC
31import java.sql. public class InsertCoffees
public static void main(String args)
throws SQLException
System.out.println ("Adding Coffee Data")
ResultSet rs null
PreparedStatement ps null
String url "jdbcmysql//localhost/coffee"
Connection con
Statement stmt try
Class.forName("com.mysql.jdbc.Driver")
catch(java.lang.ClassNotFoundE
xception e)
System.err.print("ClassNotFoundException ")
System.err.println(e.getMessag
e())
1
32 try con
DriverManager.getConnection(url)
stmt con.createStatement()
stmt.executeUpdate ("INSERT INTO
COFFEES "
"VALUES('Amaretto', 49, 9.99, 0, 0)")
stmt.executeUpdate ("INSERT INTO
COFFEES "
"VALUES('Hazelnut', 49, 9.99, 0, 0)")
stmt.executeUpdate ("INSERT INTO
COFFEES "
"VALUES('Amaretto_decaf', 49, 10.99, 0, 0)")
stmt.executeUpdate ("INSERT
INTO COFFEES "
"VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)")
stmt.close()
con.close()
System.out.println ("Done")
catch(SQLException ex)
System.err.println("-----SQLException-----")
System.err.println("SQLState
" ex.getSQLState())
System.err.println("Message "
ex.getMessage())
System.err.println("Vendor "
ex.getErrorCode())
2
3
4
6
33Example 3Querying Data via JDBC
34import java.sql. public class SelectCoffees
public static void main(String args)
throws SQLException ResultSet
rs null PreparedStatement ps
null String url
"jdbcmysql//localhost/coffee"
Connection con Statement stmt
try Class.forName("com.mys
ql.jdbc.Driver")
catch(java.lang.ClassNotFoundException e)
System.err.print("ClassNotFoundE
xception ")
System.err.println(e.getMessage())
try con
DriverManager.getConnection(url)
stmt con.createStatement()
1
2
3
354
ResultSet uprs stmt.executeQuery("SELEC
T FROM COFFEES")
System.out.println("Table COFFEES")
while (uprs.next())
String name uprs.getString("COF_
NAME") int id
uprs.getInt("SUP_ID")
float price uprs.getFloat("PRICE")
int sales
uprs.getInt("SALES")
int total uprs.getInt("TOTAL")
System.out.print(name " "
id " " price)
System.out.println(" " sales " "
total)
uprs.close()
stmt.close()
con.close()
catch(SQLException ex)
System.err.println("-----SQLException-----")
System.err.println("SQLState
" ex.getSQLState())
System.err.println("Message "
ex.getMessage())
System.err.println("Vendor "
ex.getErrorCode())
5
6
36JDBC Exception Handling
37Exception Handling
- SQL Exceptions
- Nearly every JDBC method can throw a SQLException
in response to a data access error - If more than one error occurs, they are chained
together - SQL exceptions contain
- Description of the error, getMessage
- The SQLState (Open Group SQL specification)
identifying the exception, getSQLState - A vendor-specific integer, error code,
getErrorCode - A chain to the next SQLException,
getNextException
38SQL Exception Example
- try
- ... // JDBC statement.
- catch (SQLException sqle)
- while (sqle ! null)
- System.out.println("Message "
sqle.getMessage()) - System.out.println("SQLState "
sqle.getSQLState()) - System.out.println("Vendor Error "
- sqle.getErrorCode())
- sqle.printStrackTrace(System.out)
- sqle sqle.getNextException()
-
-
39Summary
- The JDBC Driver connections a Java application to
a specific database. - Six Steps to Using JDBC
- Load the Driver
- Establish the Database Connection
- Create a Statement Object
- Execute the Query
- Process the Result Set
- Close the Connection
- Make sure to wrap your JDBC calls within
try/catch blocks.