cs6320 jdbc n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CS6320 - JDBC PowerPoint Presentation
Download Presentation
CS6320 - JDBC

Loading in 2 Seconds...

play fullscreen
1 / 30

CS6320 - JDBC - PowerPoint PPT Presentation


  • 104 Views
  • Uploaded on

CS6320 - JDBC. Introducing JDBC. JDBC: is an API that provides “universal data access for the Java2 platform” Allows you to connect to a known data source using common OO semantics using Java Allows you to issue standard SQL commands on that data source

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 'CS6320 - JDBC' - nassor


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
introducing jdbc
Introducing JDBC
  • JDBC: is an API that provides “universal data access for the Java2 platform”Allows you to connect to a known data source using common OO semantics using Java
  • Allows you to issue standard SQL commands on that data source
  • Provides you with classes to facilitate access to and manipulation of:
    • returned data and,
    • generated exceptions
jdbc driver types
JDBC Driver Types
  • Type 1 (JDBC-ODBC Bridge Technology)
  • Type 2 (JNI drivers for native connection libraries)
  • Type 3 (Socket-level Middleware Translator)
  • Type 4 (Pure Java-DBMS driver)
driver types
Driver Types

Type 2

Type 1

Type 3

Type 4

type 1 drivers jdbc odbc bridges
Type 1 Drivers: JDBC-ODBC Bridges
  • JDBC driver translates call into ODBC and redirects ODBC call to an ODBC driver on the DBMS
  • ODBC binary code must exist on every client
  • Translation layer compromises execution speed to small degree
type 2 drivers native api java driver
Type 2 Drivers: Native-API + Java Driver
  • Java driver makes JNI calls on the client API (usually written in C or C++)
    • eg: Sybase dblib or ctlib
    • eg: Oracle Call Interface libs (OCI)
  • Requires client-side code to be installed
  • Often the fastest solution available
  • Native drivers are usually delivered by DBMS vendor
  • Bug in driver can crash JVMs
type 3 drivers jdbc middleware pure java driver
Type 3 Drivers:JDBC-Middleware Pure Java Driver
  • JDBC driver translates JDBC calls into a DBMS-independent protocol
  • Then, communicates over a socket with a middleware server that translates Java code into native API DBMS calls
  • No client code need be installed
  • Single driver provides access to multiple DBMSs, eg. WebLogic, Tengah drivers
  • Type 3 drivers auto-download for applets.
  • Communication is indirect via a middleware server
type 4 drivers pure java drivers
Type 4 Drivers:Pure Java Drivers
  • Java drivers talk directly to the DBMS using Java sockets
  • No Middleware layer needed, access is direct.
  • Simplest solution available.
  • No client code need be installed.
  • Type 4 drivers auto-download for applets
jdbc drivers
JDBC Drivers
  • JDBC drivers exist for every major database including: Oracle, SQL Server, Sybase, and MySQL.
six steps to using jdbc simple program no connection pooling middleware
Six Steps to Using JDBC (simple program…no connection pooling/middleware)
  • Load the JDBC Driver
  • Establish the Database Connection
  • Create a Statement Object
  • Execute a Query
  • Process the Results
  • Close the Connection
a standard simple jdbc application
A standard simple JDBC application

// Load the JDBC driver

Class.forName("oracle.jdbc.OracleDriver").newInstance();

// Connect to the database

Connection conn = DriverManager.getConnection

(connect-string,user, pass);

// Create a statement

Statement stmt = conn.createStatement ();

// Execute the statement: select data from the emp table

boolean results = stmt.execute("select * from emp");

ResultSet rset = null;

if (results) rset = stmt.getResultSet();

// Process results: walk through the result set

while (rset.next ()) {

System.out.println (rset.getString (1) + rset.getString(2));

….

}

overview connecting to a database
Overview: Connecting to a Database

// Load the Oracle JDBC driver

Class.forName("oracle.jdbc.OracleDriver").newInstance();

// Connect to the database

Connection conn = DriverManager.getConnection

(connect-string,user, pass);

// Create a statement

Statement stmt = conn.createStatement ();

// Select data from the emp table

boolean results = stmt.execute("select * from emp");

ResultSet rset = null;

if (results) rset = stmt.getResultSet();

// Walk through the result set

while (rset.next ()) {

System.out.println (rset.getString (1) + rset.getString(2));

….

}

the jdbc driver manager
The JDBC Driver Manager
  • Management layer of JDBC, interfaces between the client and the driver.
    • Keeps a list of available drivers
    • Manages driver login time limits and printing of log and tracing messages
  • Secure because manager will only allow drivers that come from local file system or the same initial class loader requesting a connection
  • Most popular use:
    • Connection getConnection(url, id, passwd);
create a connection to the database
Create a Connection to the database
  • Call the getConnection method on the DriverManager.

Connection conn =

DriverManager.getConnection(url, login, password)

  • URLs:
    • “jdbc:sybase:Tds:skunk:4100/myDB”
    • "jdbc:oracle:thin:@limani.cs.uchicago.edu:1521:cs51024";
  • Only one requirement: the relevant Drivers must be able to recognize their own URL
overview statements
Overview: Statements

// Load the Oracle JDBC driver

Class.forName("oracle.jdbc.OracleDriver").newInstance();

// Connect to the database

Connection conn = DriverManager.getConnection

(connect-string,user, pass);

// Create a statement

Statement stmt = conn.createStatement ();

// Select data from the emp table

boolean results = stmt.execute("select * from emp");

ResultSet rset = null;

if (results) rset = stmt.getResultSet();

// Walk through the result set

while (rset.next ()) {

System.out.println (rset.getString (1) + rset.getString(2));

….

}

sql statements
SQL Statements

Types of statements:

  • Class Statement
    • Represents a basic SQL statement
    • Statement stmt = conn.createStatement();
  • Class PreparedStatement
    • A precompiled SQL statement, which can offer improved performance, especially for large/complex SQL statements
  • Class CallableStatement
    • Allows JDBC programs access to stored procedures

Can be used for both DDL and DML commands

execute an sql statement
Execute an SQL Statement
  • executeQuery(): execute a query and get a ResultSet back
  • executeUpdate(): execute an update and get back an int specifying number of rows acted on
    • UPDATE [table] set [column_name] = value where […]
    • DELETE from [table] where [column_name] = 5
  • execute(): exec. unknown SQL, returns true if a resultSet is available:

Statement genericStmt = conn.createStatement();

if( genericStmt.execute(SQLString)) {

ResultSet rs = genericStmt.getResultSet();

process(); }

else {

int updated = genericStmt.getUpdateCount();

processCount();

}

prepared statements
Prepared Statements
  • Use for complex queries or repeated queries
  • Features:
    • precompiled at database (statement usually sent to database immediately on creation for compilation)
    • supply with new variables each time you call it
  • Example:
    • PreparedStatement ps = conn.prepareStatement(“update table set sales = ? Where custName = ?”);
  • Set with values (use setXXX() methods on PreparedStatement:
    • ps.setInt(1, 400000);
    • ps.setString(2, “United Airlines”);
  • Then execute:
    • int count = ps.executeUpdate();
overview resultsets and cursors
Overview: ResultSets and Cursors

// Load the Oracle JDBC driver

Class.forName("oracle.jdbc.OracleDriver").newInstance();

// Connect to the database

Connection conn = DriverManager.getConnection

(connect-string,user, pass);

// Create a statement

Statement stmt = conn.createStatement ();

// Select data from the emp table

boolean results = stmt.execute("select * from emp");

ResultSet rset = null;

if (results) rset = stmt.getResultSet();

// Walk through the result set

while (rset.next ()) {

System.out.println (rset.getString (1) + rset.getString(2));

….

}

result sets and cursors
Result Sets and Cursors
  • Result Sets are returned from queries.
  • Possible number of rows: zero, one, or more
  • Cursors are ‘iterators’ that can be user to ‘walk’ through a result set
  • JDBC 2.0 allows for backward as well as forward cursors, including the ability to go to a specific row or a relative row
result sets
Result Sets
  • Iterate over all rows:
    • ResultSet rs = stmt.executeQuery(“select id, price from inventory”);
    • rs.next(), rs.previous(), rs.first(), …
      • call once to access first row: while(rs.next()) {}
  • Extract data from the ResultSet
    • getXXX(columnName/indexVal)
      • getInt()
      • getDouble()
      • getString() (highly versatile, inclusive of others; automatic conversion to String for most types)
      • getObject() (returns a generic Java Object)
    • rs.wasNull() - returns true if last get was Null
slide23

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 = "jdbc:mysql://localhost/cerami";

Connection con;

Statement stmt;

try {

Class.forName("org.gjt.mm.mysql.Driver");

} catch(java.lang.ClassNotFoundException e) {

System.err.print("ClassNotFoundException: ");

System.err.println(e.getMessage());

}

1

slide24

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

slide26

import java.sql.*;

public class SelectCoffees {

public static void main(String args[]) throws SQLException {

ResultSet rs = null;

PreparedStatement ps = null;

String url = "jdbc:mysql://localhost/cerami";

Connection con;

Statement stmt;

try {

Class.forName("org.gjt.mm.mysql.Driver");

} catch(java.lang.ClassNotFoundException e) {

System.err.print("ClassNotFoundException: ");

System.err.println(e.getMessage());

}

try {

con = DriverManager.getConnection(url);

stmt = con.createStatement();

1

2

3

slide27

4

ResultSet uprs = stmt.executeQuery("SELECT * 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

exception handling
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
sql exception example
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();

}

}

using the jdbc metadata interface
Using the JDBC MetaData Interface
  • ResultSet: ResultSetMetaData m = rs.getMetaData()
  • ResultSetMetaData provides information about the types and properties of the DDL properties of a ResultSet object
  • ResultSetMetaData provides various methods for finding out information about the structure of a ResultSet:
    • getColumnClassName(int col): gets fully-qualified Java class name to which a column value will be mapped; eg. Java.lang.Integer, etc.
    • getColumnCount(): gets the number of columns in the ResultSet
    • getColumnName(int col): gets the name of column
    • int getColumnType(int col): gets the JDBC type (java.sql.Types) for the value stored in col; eg. Value 12 = JDBC VARCHAR, etc.
    • getPrecision(int col): for numbers, gets the mantissa length, for others, gets the number of bytes for column