Transcript JDBC - Free

JDBC
Olivier de Pertat
IT GROUP
JDBC Overview
JDBC is specified as an Interface
API to manage and query RDBMS
New standard to access RDMS : single API to access envry
single product
JDBC Allows to :
Run SQL Queries (Static or dynamic, DML or DDL instructions)
Process the results
Specification based on the X/Open SQL CLI standard (like
ODBC
Alternative to proprietary solutions (Oracle, Sybase, DB2…)
Simplified DBMS Architecture
DBMS Goals
Efficient data Management (faster than files)
Larger amout of data
High reliability
Information sharing (multiple users)
DBMS Users :
Banks, Insurances, airlines, E-commerce
companies, transportation companies, corporate
DB, government agencies…
Everybody nowadays!
DBMS Concepts
Database modeling
E/R model, Relational model, ER to Relational
Model
Relational Algebra
SQL: Subqueries, Joins, Modifications, NULLs,
Constraints, Triggers, stored procedures
Embedded SQL
Views
Relational Design: BCNF, 2NF, 3NF, 4NF, 5NF…
JDBC & SQL
SQL-3 Entry Level Support
Dynamic SQL, most SQL types
Transactions, basic cursors
Meta-data (Database Dictionary access)
Extension mechanism
Syntax inspired from ODBC: { keywords...
parameters ... }
ODBC conversions functions, mathematics, etc.
Long terme:
Support de SQL-3 complet
JDBC Versions
JDBC 1.0 : Integrated to Java 1.1
JDBC 2.0 API :
JDBC 2.1 Core API : since J2SE 1.2 (java.sql)
JDBC 2.0 Optional Package : since J2EE 1.2 (javax.sql)
JDBC 3.0 :
JDBC 3.0 Core API : since J2SE 1.4
JDBC 3.0 Optional Package :
New in JDBC 2.0
JDBC 2.1 Core API
Scrollable ResultSets
Update through ResultSets is possible
Minimal support of BLOB & CLOB data types
User-defined types support
Bacth updates.
JDBC 2.0 Optional Package
DataSource
Distributed Transactions (JTA/XA)
Connection Pools
RowSet Technology
.
JDBC 3.0 Newbie
Statements Pools
SavePoints in transaction
MetaData for PreparedStatements
Enhancement of CallableStatements (types of
parameters, multiple ResultSet... )
Management of object types : BLOB, CLOB, ARRAY
et REF.
J2EE integration - 1
J2EE Integration - 2
Drivers Topology
JDBC interacts with the RDMS through a DriverManager
Drivers are available for most commercial & OpenSource
Databases :Oracle, DB2, Sybase, MySQL, PostgreSQL,
ODBC...
4 drivers topology:
1. Bridge ODBC (given with JDBC)
2. Native-API partly-Java driver
3. JDBC-Net all-Java driver
4. Native-protocol all-Java driver
1. et 2 are written with Native code.
.
JDBC Type 1 : JDBC-ODBC Brigde
JDBC Type 2: Native-API Partly-Java Driver
JDBC Type 3 : Net Protocol All-Java Driver
JDBC Type 4 : All Java Driver
JDBC : Architecture Objet
JDBC Components
Driver Manager: loads database drivers and manages the
connection between application & driver.
Driver: translates API class to operations for a specific data
source.
Connection: A session between an application and a driver.
Statement: A SQL statement to perform a query or an update
operation.
Metadata: Information about the returned data, driver and the
database.
ResultSet : logical set of columns and rows returned by
executing a statement.
Java.sql
JDBC is implemented via classes in the
java.sql package
Defines object for:
Remote connection to DB
Executing query
8 interfaces to define objects
Statement, CallableStatement,
PreparedStatement, DatabaseMetaData,
ResultSetMetaData, Connection, Driver
JDBC Classes
Java supports DB facilities b y providing classes and
interfaces for its components.
DriverManager : class
Connection : Abstract Class
Statement : Interface (to be instantiated with values
from the actual SQL Statement)
ResulSet : Interface
Seven steps to heaven (or a DBMS ;-))
Load the Driver
Define the Connection URL
Establish the Connection
Create a Statement object
Execute a query
Process the result
Close the connection
Loading the driver v1
Registering the driver directly
Use the method forName from the class: Class
Creates an instance of the Driver
Registers the Driver with the DriverManager
Class.forName("sun.jdbc.odbc.JdbcOrdbDriver");
Class.forName("oracle.jdbc.driver.OracleDriver");
Example :
Try
{
Class.forName(« oracle.jdbc.driver.OracleDriver ») ;
}
catch(ClassNotFoundException e)
{
e.printStackTrace(); ;
}
Loading the driver v2
Anther option is to creates an instance of the
driver then manually register it :
Driver driver = new
oracle.jdbc.driver.OracleDriver();
DriverManager.registerDriver(driver);
Identifying the Data Source
Gives the required information for making
the connection to the databases
URL :
<scheme>:<sub scheme>:<scheme-specific part>
Scheme : Protocol => JDBC
Sub Scheme : indicates driver & driver type
Scheme Specific part : Server Address & Database
name
Examples :
jdbc:odbc:my_database
jdbc:oracle:thin:@localhost:1521:ODP
Connection
A connection represents a session with a specific database
Within the context of a connection SQL statements are executed
and results returned (or an Integer if Query is not a SELECT
statement)
There can be multiple connections to a database
A connection provides ”metadata” i.e., information about the
database, tables, fields.
Connection object has methods to deal with the transactions.
Connection creation :
Connection conn =
DriverManager.getConnection(url, user, password);
.
Connection Metadata
getMetaData()
method returns a JDBC Metadata
connection
Returned type if DatabaseSetMetaData.
Information returned by this object:
What tables are available?
What's our user name as known to the database?
Is the database in read-only mode?
If table correlation names are supported (association of a
column with the table it comes from, when multiple columns
of the same name appear in the same query - multi-table
queries) , are they restricted to be different from the names
of the tables?
and so on…
Statements
A statement object is used for executing a static SQL
statement and obtaining the results produced by it.
3 kinds of statements:
statement
:
for general queries
<Connection>.createStatement() method
prepared statement
:
For a statement called multiple times with different values
(precompiled to reduce parsing time)
<Connection>.preparedStatement() method
callable statement
for stored procedures
<Connection>.preparedCall() method
Sample:
Statement stmt = conn.createStatement();
Executing Queries and updates
ResultSet executeQuery(String)
Execute a SQL statement that returns a single ResultSet
int executeUpdate(String)
Execute a SQL INSERT, UPDATE or DELETE statement
Used for CREATE TABLE, DROP TABLE and ALTER TABLE
Returns the number of rows changed
setQueryTimeOut
to set a timeout for the driver to wait for a statement to be
completed
If the operation is not compeleted in the given time, an
SQLException is thrown
Executing a request
Execution of a request:
String myQuery = "SELECT prenom, nom, email " +
"FROM employe " +
"WHERE (nom='Dupont') AND (email IS NOT NULL"+
"ORDER BY nom";
ResultSet rs = stmt.executeQuery(myQuery);
Result Set - 1
A Result Set provides access to a table of data
generated by executing a Statement.
Only one ResultSet per Statement can open at
once.
The table rows are retrieved in sequence.
A ResultSet maintains a cursor pointing to its
current row of data.
The next method moves the cursor forward (to the
next row).
Columns are indexed by name or number.
.
Result Set - 2
Data is retrieved from a column via the getXXX
methods where XXX is the type of the returned Java
object.
The content Cells containing a “large amount” of data
must be retrieved via streams.
Type getType(int columnIndex)
returns the given field as the given type
E.g., int getInt(5); string getString(3);
fields indexed starting at 1 (not 0)
Type getType(String columnName)
same, but uses name of field
less efficient
int findColumn(String columnName)
looks up column index given column name
Result Set – 3 – isNull
In SQL, NULL means the field is empty
Not the same as 0 or “”
In JDBC, you must explicitly ask if a field is null by
calling ResultSet.isNull(column)
Example
java.sql.Statement stmt = conn.createStatement();
ResultSet rs =
stmt.executeQuery("SELECT a, b, c FROM Table1");
while (rs.next())
{
// print the values for the current row.
int i = rs.getInt("a");
String s = rs.getString("b");
byte b[] = rs.getBytes("c");
System.out.println("ROW = " + i + " " + s + " " + b[0]);
}
ResultSet’s metadata
getMetaData()
method returns a ResultSetMetaData
object.
Information:
What's the number of columns in the ResultSet?
What's a column's name?
What's a column's SQL type?
What's the column's normal max width in chars?
What's the suggested column title for use in printouts and displays?
What's a column's number of decimal digits?
Does a column's case matter?
and so on...
Full example
public class TestJDBC {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn =
DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ODP", "user", "");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * from employe");
while (rs.next()) {
String nom = rs.getString("nom");
String prenom = rs.getString("prenom");
String email = rs.getString("email");
}
}
}
Mapping Java types to SQL Types
SQL type
Java Type
CHAR, VARCHAR, LONGVARCHAR
String
NUMERIC, DECIMAL
java.math.BigDecimal
BIT
boolean
TINYINT
byte
SMALLINT
short
INTEGER
int
BIGINT
long
REAL
float
FLOAT, DOUBLE
double
BINARY, VARBINARY, LONGVARBINARY
byte[]
DATE
java.sql.Date
TIME
java.sql.Time
TIMESTAMP
java.sql.Timestamp
Types Oracle – JDBC 1.0
Types Oracle – JDBC 2.0
Database time
Times in SQL are nonstandard
Java defines three classes to help
java.sql.Date
year, month, day
java.sql.Time
hours, minutes, seconds
java.sql.Timestamp
year, month, day, hours, minutes, seconds,
nanoseconds
usually use this one
Optimized Statements
Prepared Statements
SQL calls that you make again and again
allows driver to optimize (compile) queries
created with Connection.prepareStatement()
Stored Procedures
written in DB-specific language
stored inside database
accessed with Connection.prepareCall()
Prepared Statement example
PreparedStatement pstmt =
con.prepareStatement(
"UPDATE PEOPLE SET PHOTO_FILENAME=?,PHOTO_DATA=? WHERE ID=?",
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
pstmt.setString(1, myFile.getName());
pstmt.setBinaryStream(2, myFileInput,
myFileInput.available());
pstmt.setString(3, "27");
pstmt.execute();
pstmt.close();
JDBC Class Diagram
Transaction management
A transaction: a sequence of SQL statements
betwwen
BEGIN_TRANSACTION : début
Et COMMIT_TRANSACTION ou
ROLLBACK_TRANSACTION
Transactions are not explicitly opened and closed
Instead, the connection has a state called
AutoCommit mode
if AutoCommit is true, then every statement is
automatically committed
default case: true
Example :
CompteA += 100
CompteB -= 100
Auto commit
Connection.setAutoCommit(boolean)
if AutoCommit is false, then every statement
is added to an ongoing transaction
you must explicitly commit or rollback the
transaction using Connection.commit()
and Connection.rollback()
Transactions isolation levels
TRANSACTION_NONE
No transaction support
TRANSACTION_READ_UNCOMMITED
minimal support
Dirty read i.e. no warranty
TRANSACTION_READ_COMMITED
Dirty read impossible
TRANSACTION_REPEATABLE_READ
Repeatable read protected
And TRANSACTION_READ_COMMITED support.
TRANSACTION_SERIALIZABLE
Insertion protectection
And TRANSACTION_REPEATABLE_READ support.
Example
Transaction mode activated via setAutoCommit
method of Connection.
Example :
setTransactionIsolation.
Try {
con.setAutoCommit(false);
con.setTransactionIsolation(
Connection.TRANSACTION_READ_COMMITED);
//SQL stuff
con.commit();
}
Catch(Exception e)
con.rollback();
}
.
{
Transactional modes
Transactional support is database specific material.
DB2 UDB : all levels
Oracle : 2 levels supported
MySQL : only one level is supported
Transaction information relatives are specified in the
class DatabaseMetaData:
supportsTransactions()
supportsDataDefinitionAndDataManipulationTransactions()
getDefaultTransactionIsolation()
dataDefinitionCausesTransactionCommit()
supportsTransactionIsolationLevel(int)
o
Connection Manager
For a large threaded database server, create a
Connection Manager object
It is responsible for maintaining a certain number of
open connections to the database
When your applications need a connection, they ask
for one from the CM’s pool
Why? Because opening and closing connections
takes a long time
Warning: the CM should always
setAutoCommit(false) when a connection is returned
.
Thanks to…
O’reilly Editions
Eyrolles editions
A. Chambrey (BD2 - Eyrolles)
Professor Chen Li – University of California,
Irvine