JDBC: Part I - PowerPoint PPT Presentation

1 / 39
About This Presentation
Title:

JDBC: Part I

Description:

'jdbc:mysql://localhost/databasename'; Connection con = DriverManager.getConnection(url) ... located on the localhost machine. ... – PowerPoint PPT presentation

Number of Views:78
Avg rating:3.0/5.0
Slides: 40
Provided by: Cer8
Learn more at: https://cs.nyu.edu
Category:
Tags: jdbc | localhost | part

less

Transcript and Presenter's Notes

Title: JDBC: Part I


1
JDBC Part I
2
Attribution
  • 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.)

3
Road 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

4
Introduction to JDBC andJDBC Drivers
5
Introduction 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.

6
Understanding 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.)
7
JDBC 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.

8
Installing 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

9
Overview Six Steps to Using JDBC
10
Six 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

11
1) 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")

12
Loading 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.

13
2) 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)

14
Creating 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.

15
Connection 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")

16
Connection 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.

17
3) 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.

18
4) 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

19
Useful 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

20
5) 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

21
ResultSet (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

22
ResultSet (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
23
6) 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.

24
Example 1Setting Up Tables via JDBC
25
The 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)

26
The Coffees Tables
27
The 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.

28
import 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
30
Example 2Inserting Data via JDBC
31
import 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
33
Example 3Querying Data via JDBC
34
import 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
35
4
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
36
JDBC Exception Handling
37
Exception 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

38
SQL 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()

39
Summary
  • 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.
Write a Comment
User Comments (0)
About PowerShow.com