slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
JDBC PowerPoint Presentation
Download Presentation
JDBC

Loading in 2 Seconds...

play fullscreen
1 / 23

JDBC - PowerPoint PPT Presentation


  • 173 Views
  • Uploaded on

JDBC. Introduction to JDBC Programming. Mohamed Grida. JDBC Programming. After completing this lesson, you should be able to do the following: Connect to a database using Java Database Connectivity (JDBC) Create and execute a query using JDBC Invoke prepared statements

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'JDBC' - wyanet


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide2

Introduction to

JDBC Programming

Mohamed Grida

jdbc programming
JDBC Programming
  • After completing this lesson, you should be able to do the following:
    • Connect to a database using Java Database Connectivity (JDBC)
    • Create and execute a query using JDBC
    • Invoke prepared statements
    • Commit and roll back transactions
slide4
JDBC
  • JDBC is a standard interface for connecting to relational databases from Java.
  • The JDBC classes and interfaces are in the java.sql package.
  • JDBC 1.22 is part of JDK 1.1; JDBC 2.0 is part of Java 2
overview of querying a database with jdbc
Overview of Querying a Database With JDBC

Connect

Query

Processresults

Close

stage 1 connect
Stage 1: Connect

Connect

Register the driver

Connect to the database

Query

Processresults

Close

a jdbc driver
A JDBC Driver
  • Is an interpreter that translatesJDBC method calls to vendor-specific database commands
  • Implements interfaces in java.sql
  • Can also provide a vendor’s extensions to the JDBC standard

Database commands

JDBC calls

Driver

Database

oracle jdbc drivers
Oracle JDBC Drivers
  • Thin driver
    • a 100% Java driver for client-side use without an Database installation, particularly with applets
  • OCI drivers (Database Specific)
    • for client-side use with an Oracle client installation
  • JDBC-ODBC Bridge (Built in JAVA)
    • Call the DSN created in ODBC which specify the DB driver and the DB Location
    • Translates JDBC into open database connectivity (ODBC) calls
about jdbc urls
About JDBC URLs
  • JDBC uses a URL to identify the database connection.

jdbc:<subprotocol>:<subname>

DSN

Protocol

Subprotocol

jdbc:odbc:<driver>:@<database>

how to make the connection
How to Make the Connection

1. Register the driver.

Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

2. Connect to the database.

Connection conn = DriverManager.getConnection (URL, userid, password);

Connection con = DriverManager.getConnection ("jdbc:odbc:thin:dsnname",“Mohamed",“ww");

using connection
Using Connection

java.sql.Connection

Creating Statement

createStatment()

prepareStatment(String)

prepareCall(String)

Transaction Management

commit()

rollback()

Get database metadata

getMetaData()

Connection related

close()

isClosed()

stage 2 query
Stage 2: Query

Connect

Create a statement

Query

Query the database

Processresults

Close

the statement object
The Statement Object
  • A Statement object sends your SQL statement to the database.
  • You need an active connection to create a JDBC statement.
  • Statement has three methods to execute a SQL statement:
    • executeQuery() for QUERY statements
    • executeUpdate() for INSERT, UPDATE, DELETE, or DDL statements
    • execute() for either type of statement
how to query the database
How to Query the Database

1. Create an empty statement object.

2. Execute the statement.

Statement stmt = conn.createStatement();

ResultSet rset = stmt.executeQuery(statement);

int count = stmt.executeUpdate(statement);

boolean isquery = stmt.execute(statement);

querying the database examples
Querying the Database: Examples
  • Execute a select statement.

Statement stmt = conn.createStatement();

ResultSet rset = stmt.executeQuery ("select RENTAL_ID, STATUS from ACME_RENTALS");

  • Execute a delete statement.

Statement stmt = conn.createStatement();

int rowcount = stmt.executeUpdate ("delete from ACME_RENTAL_ITEMS

where rental_id = 1011");

stage 3 process the results
Stage 3: Process the Results

Connect

Query

Step through the results

Assign results to Java variables

Processresults

Close

the resultset object
The ResultSet Object
  • JDBC returns the results of a query in a ResultSet object.
  • A ResultSet maintains a cursor pointing to its current row of data.
  • Use next() to step through the result set row by row.
  • getString(), getInt(), and so on assign each value to a Java variable.
how to process the results
How to Process the Results
  • 1. Step through the result set.
  • 2. Use getXXX() to get each column value.

while (rset.next()) { … }

String val = rset.getString(colname);

String val = rset.getString(colIndex);

boolean more = rset.next();

while ( more )

{ String title = rset.getString("TITLE"); String year = rset.getString("YEAR"); … // Process or display the data}

mapping database types to java types
ResultSet maps database types to Java types.Mapping Database Types to Java Types

ResultSet rset = stmt.executeQuery

("select RENTAL_ID, RENTAL_DATE, STATUS from ACME_RENTALS");

int id = rset.getInt(1);

Date rentaldate = rset.getDate(2);

String status = rset.getString(3);

Col Name

RENTAL_ID

RENTAL_DATE

STATUS

Type

NUMBER

DATE

VARCHAR2

stage 4 close
Stage 4: Close

Connect

Query

Close the result set

Processresults

Close the statement

Close

Close the connection

how to close the connection
How to Close the Connection

1. Close the ResultSet object.

2. Close the Statement object.

3. Close the connection (not necessary for server-side driver).

rset.close();

stmt.close();

conn.close();

how to create a prepared statement
How to Create a Prepared Statement

1.Register the driver and create the database connection.

2.Create the prepared statement, identifying variables with a question mark (?).

PreparedStatement pstmt =

conn.prepareStatement("update ACME_RENTALS

set STATUS = ? where RENTAL_ID = ?");

PreparedStatement pstmt =

conn.prepareStatement("select STATUS from ACME_RENTALS where RENTAL_ID = ?");

how to execute a prepared statement
How to Execute a Prepared Statement

1. Supply values for the variables.

2. Execute the statement.

pstmt.setXXX(index, value);

pstmt.executeQuery();

pstmt.executeUpdate();

PreparedStatement pstmt =

conn.prepareStatement("update ACME_RENTALS

set STATUS = ? where RENTAL_ID = ?");

pstmt.setString(1, "OUT");

pstmt.setInt(2, rentalid);

pstmt.executeUpdate();