jdbc connectivity
TRANSCRIPT
JDBC - Java Database Connectivity
The objectives of this chapter are:
To describe the architecture of JDBC
Driver Types
JDBC Configuration
To outline the classes in the java.sql package
Executing SQL Statements
sTo understand the use of JDBC
JDBC provides Java applications with access to most database systems via SQL
The architecture and API closely resemble Microsoft's ODBC
JDBC 1.0 was originally introduced into Java 1.1JDBC 2.0 was added to Java 1.2
JDBC is based on SQL-92
JDBC classes are contained within the java.sql package
There are few classesThere are several interfaces
What is JDBC?
3
JDBC ArchitectureJDBC Architecture
Java Application JDBC
Oracle
DB2
MySQL
Oracle
Driver
DB2
Driver
MySQL
Driver
Network
We will
use this one…
JDBC Architecture (cont.)JDBC Architecture (cont.)
Application JDBC Driver
• Java code calls JDBC library
• JDBC loads a driver
• Driver talks to a particular database
• An application can work with several databases by using all
corresponding drivers
• Ideal: can change database engines without changing any
application code (not always in practice)
Before APIs like JDBC and ODBC, database connectivity was tedious
Each database vendor provided a function library for accessing their databaseThe connectivity library was proprietary.If the database vendor changed for the application, the data access portions had to be rewrittenIf the application was poorly structured, rewriting its data access might involve rewriting the majority of the applicationThe costs incurred generally meant that application developers were stuck with a particular database product for a given application
Database Connectivity History
With JDBC, the application programmer uses the JDBC API
The developer never uses any proprietary APIs
• Any proprietary APIs are implemented by a JDBC driver• There are 4 types of JDBC Drivers
JDBC Architecture
Java Application
JDBC API
JDBC DriverManager
JDBC Driver JDBC Driver
There are 4 types of JDBC DriversType 1 - JDBC-ODBC BridgeType 2 - JDBC-Native BridgeType 3 - JDBC-Net BridgeType 4 - Direct JDBC Driver
Type 1 only runs on platforms where ODBC is available
ODBC must be configured separately
Type 2 Drivers map between a proprietary Database API and the JDBC API
Type 3 Drivers are used with middleware products
Type 4 Drivers are written in JavaIn most cases, type 4 drivers are preferred
JDBC Drivers Types
JDBC Classes
DriverManagerManages JDBC DriversUsed to Obtain a connection to a Database
• TypesDefines constants which identify SQL types
DateUsed to Map between java.util.Date and the SQL DATE type
• TimeUsed to Map between java.util.Date and the SQL TIME type
TimeStampUsed to Map between java.util.Date and the SQL TIMESTAMP type
JDBC Interfaces
DriverAll JDBC Drivers must implement the Driver interface. Used to obtain a connection to a specific database type
• ConnectionRepresents a connection to a specific databaseUsed for creating statementsUsed for managing database transactionsUsed for accessing stored proceduresUsed for creating callable statements
StatementUsed for executing SQL statements against the database
JDBC Interfaces
ResultSetRepresents the result of an SQL statementProvides methods for navigating through the resulting data
• PreparedStatementSimilar to a stored procedureAn SQL statement (which can contain parameters) is compiled and stored in the database
CallableStatementUsed for executing stored procedures
DatabaseMetaDataProvides access to a database's system catalogue
ResultSetMetaDataProvides information about the data contained within a ResultSet
Using JDBC
To execute a statement against a database, the following flow is observed
Load the driver (Only performed once)Obtain a Connection to the database (Save for later use)Obtain a Statement object from the ConnectionUse the Statement object to execute SQL. Updates, inserts and deletes return Boolean. Selects return a ResultSetNavigate ResultSet, using data as requiredClose ResultSetClose Statement
Database URLs
• When connecting to a database, you must specify the
data source and you may need to specify additional
parameters.For example, network protocol drivers may
need a port, and ODBC drivers may need various
attributes.
• General Syntax :
- jdbc:subprotocol name:other stuff
• EX : jdbc:localhost:DB_NAME
Loading/Registering a Driver
• The first step to develop a JDBC application is to load
and register the required driver using the driver
manager .
• You can load and register a driver :
1. Programmatically :
. Using the forName() method
. Using the registerDriver () method
2. Manually:
. By setting system property
Using the forName () Method
• The forName() method is available in the
java.lang.Class class.
• The forName() method loads the JDBC driver and
registers the driver with the driver manager.
• The syntax to load a JDBC driver to access a
database is :
Class.forName (“driver_name”);
• You can load the JDBC-ODBC Bridge driver using the
following method call:
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”
);
Using the registerDriver () method
• You can create an instance of the Driver class to load a JDBC
driver.
• The syntax to declare an instance of the Driver class is :
Driver d = new <driver-name>;
• You can use the following statement to crate an instance of
the Driver class:
Driver d = new
sun.jdbc.odbc.JdbcOdbcDriver ();
• Once you have created the Driver object ,call the
registerDriver () method to register it with the DriverManager.
• You can register the JDBC-ODBC Bridge driver using the
following method call to registerDriver () method:
DriverManager.registerDriver (d) ;
Setting System Property
• Driver can also be loaded by setting system property for JDBC
drivers.
• You add the driver name to the jdbc.drivers system property
to load a JDBC driver.
• You use the –D command line option to set the system property
on the command line.
• The command to set the system property is :
java –Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver
Demo
• In the preceding command , jdbc.drivers is the property name
and sun.jdbc.odbc.JdbcOdbcDriver is the value that you
need to set for the property.
• After you load a driver , you need to establish the connection
with a database.
Even a good API can have problemsLoading drivers fits into this category
The DriverManager is a singleton
Each JDBC Driver is also a singleton
When a JDBC Driver class is loaded, it must create an instance of itself and register that instance with the JDBC DriverManager
How does one load a "class" into the Virtual machine?Use the static method Class.forName()
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Loading Drivers
Connecting to a Database
Once a Driver is loaded, a connection can be made to the database
The connection is defined by URLThe URL has the following form: jdbc:driver:databasename
• Examples:jdbc:odbc:MyOdbcDatabasejdbc:postgres:WebsiteDatabasejdbc:oracle:CustomerInfo
A connection is obtained in the following manner:
Connection aConnection = DriverManager.getConnection("jdbc:odbc:myDatabase");
• Overloaded versions of the getConnection method allow the specification of a username and password for authentication with the database.
Executing SQL Statments
• Managing Connections
• Statements
• Resultset
• SQL Exceptions
• Populating Database
19
Managing / Using a Connection
The Connection interface defines many methods for managing and using a connection to the database
public Statement createStatement()public PreparedStatement prepareStatement(String sql)public void setAutoCommit(boolean)public void commit()public void rollback()public void close()
• The most commonly used method is createStatement()
When an SQL statement is to be issued against the database, a Statement object must be created through the Connection
Statements
The Statement interface defines two methods for executing SQL against the database
public ResultSet executeQuery(String sql)public int executeUpdate(String sql)
• executeQuery returns a ResultSet• All rows and columns which match the query are contained
within the ResultSet• The developer navigates through the ResultSet and uses
the data as required.
• executeUpdate returns the number of rows changed by the update statement
This is used for insert statements, update statements and delete statements
Result Set
The ResultSet interface defines many navigation methods
public boolean first()public boolean last()public boolean next()public boolean previous()
The ResultSet interface also defines data access methods
public int getInt(int columnNumber) -- Note: Columns are numberedpublic int getInt(String columnName) -- from 1 (not 0)public long getLong(int columnNumber)public long getLong(String columnName)public String getString(int columnNumber)public String getString(String columnName)
There are MANY more methods. Check the API documentation for a complete list
SQL Types/Java Types Mapping
SQL Type Java Type
CHAR StringVARCHAR StringLONGVARCHAR StringNUMERIC java.Math.BigDecimalDECIMAL java.Math.BigDecimalBIT booleanTINYINT intSMALLINT intINTEGER intBIGINT longREAL floatFLOAT doubleDOUBLE doubleBINARY byte[]VARBINARY byte[]DATE java.sql.DateTIME java.sql.TimeTIMESTAMP java.sql.Timestamp
Connection aConnection;try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");}catch(ClassNotFoundException x){
System.out.println("Cannot find driver class. Check CLASSPATH");return;
}try{
aConnection = DriverManager.getConnection("jdbc:odbc:MyDatabase","Username", "Password");
}catch(SQLException x){
System.out.println("Exception connecting to database:" + x);return;
}
Example Code:
try{Statement aStmt = aConnection.createStatement();StringBuffer sb = new StringBuffer("SELECT Employee_id,Employee_Name");sb.append(" FROM Employee WHERE EmployeeId>100");ResultSet rs = aStmt.executeQuery(sb.toString());while(rs.next()){
int employeeId = rs.getInt(1);String employeeName = rs.getString(2);System.out.println("Id:" + employeeId + "\nName:" +
employeeName);}rs.close();aStmt.close();
}catch(SQLException x){
System.out.println("Exception while executing query:" + x);}
Example Code (continued):
PreparedStatement interface
• The PreparedStatement objects accepts the runtime
parameters.
• The PreparedStatement interface is derived from the
Statement interface and is available in the java.sql
package.
• The PreparedStatement objects are compiled and
prepared only once by JDBC.
• The future invocation of the PreparedStatement object
does not recompile the SQL statements.
• This helps in reducing the load on the database server
and thus improving the performance of the application .
• The PreparedStatement interface inherits the following methods to execute SQL statement s from the Statement interface:
1. ResultSet executeQuery () : - Executes a SELECT statement and returns the result in a
ResultSet object.1. int executeUpdate():
Executes an SQL statement, INSERT , UPDATE , or DELETE and returns the count of the rows
affected.1. boolean execute () : Executes an SQL statement and
returns a boolean value.
stat = con.prepareStatement(“SELECT * FROM authors WHERE au_id = ? ”) ;
The SQL statement can contain ‘ ? ‘ symbol as placeholder that can be replaced by input parameters at runtime.
Before executing the SQL statements specified in the PreparedStatement object , you must set the value of each ‘ ? ‘ parameter.
stat.setString (1, “123”);
ResultSet rs = stat.executeQuery ();
• The PreparedStatement interface provides various methods to set the value of the placeholders for the specific data types.
1. void setByte (int index , byte val) : Sets the java byte type value for the parameter corresponding to index passed as a parameter.
2. void setBytes (int index , byte[] val) : Sets the Java byte type array for the parameter corresponding to index passed as a parameter.
3. void setBoolean (int index , boolean val) : Sets the Java boolean type value for the parameter corresponding to index passed as a parameter.
4. void setDouble (int index, double val) : Sets the Java double type value value for the parameter corresponding to the index passed as a parameter.
5. void setInt (int index , int val) : Sets the Java int type value for the parameter corresponding to index passed as a parameter.
6. void setLong(int index , long val) : Sets the Java long type value for the parameter corresponding to index passed as a parameter.
7. void setFloat (int index , float val) : Sets the Java float type value for the parameter corresponding to index passed as a parameter.
8. void setShort (int index , short val) : Sets the Java short type value for the parameter corresponding to index passed as a parameter.
9. void setString(int index , String val) : Sets the Java String type value for the parameter corresponding to index passed as a parameter.
Retrieving Rows
String str = “SELECT * FROM titles WHERE au_id = ? ” ;
PreparedStatement ps = con.prepareStatement(str) ;
ps.setString(1 , “101”);
ResultSet rs = ps.executeQuery ();
Inserting Rows
String str = “ INSERT INTO authors (au_id , au_fname , au_lname) VALUES (? , ?
, ? ) ” ;
PreparedStatement ps = con.prepareStatement(str);
ps.setString (1, “101”); ps.setString (2, “ABC”); ps.setString (3 , “XYZ”);
int rt = ps.executeUpdate() ;
Updating & Deleting Row String str = “ UPDATE authors SET state = ? WHERE city = ? “ ;
PreparedStatement ps = con.prepareStatement(str);
ps.setString (1, “MH”); ps.setString (2, “Pune”); int rt = ps.executeUpdate() ;
String str = “ DELETE FROM authors WHERE au_fname = ? “ ;
PreparedStatement ps = con.prepareStatement(str);
ps.setString (1, “PPPP”); int rt = ps.executeUpdate() ;
Scrollable ResultSet• Statement createStatement( int resultSetType, int resultSetConcurrency)
• resultSetType:
• ResultSet.TYPE_FORWARD_ONLY
• -default; same as in JDBC 1.0
• -allows only forward movement of the cursor
• -when rset.next() returns false, the data is no longer available and the result set is closed.
• ResultSet.TYPE_SCROLL_INSENSITIVE
• -backwards, forwards, random cursor movement.
• -changes made in the database are not seen in the result set object in Java memory.
• ResultSetTYPE_SCROLL_SENSITIVE
• -backwards, forwards, random cursor movement.
• -changes made in the database are seen in the result set object in Java memory.
34
Scrollable ResultSet (cont’d)Scrollable ResultSet (cont’d)
• resultSetConcurrency:• ResultSet.CONCUR_READ_ONLY
• This is the default (and same as in JDBC 1.0) and allows only data to be read from the database.
• ResultSet.CONCUR_UPDATABLE
• This option allows for the Java program to make changes to the database based on new methods and positioning ability of the cursor.
• Example:
• Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
• ResultSetrset= stmt.executeQuery( “SHOW TABLES”);
35
Scrollable ResultSet (cont’d)Scrollable ResultSet (cont’d)
public boolean absolute(int row) throws SQLException
• -If the given row number is positive, this method moves the cursor to the given row number (with the first row numbered 1).
• -If the row number is negative, the cursor moves to a relative position from the last row.
• -If the row number is 0, an SQLException will be raised.
public boolean relative(int row) throws SQLException
• This method call moves the cursor a relative number of rows, either positive or negative.
• An attempt to move beyond the last row (or before the first row) in the result set positions the cursor after the last row (or before the first row).
public boolean first() throws SQLException
public boolean last() throws SQLException
public boolean previous() throws SQLException
public boolean next() throws SQLException 36
Scrollable ResultSet (cont’d)Scrollable ResultSet (cont’d)
public void beforeFirst() throws SQLException
public void afterLast() throws SQLException
public boolean isFirst() throws SQLException
public boolean isLast() throws SQLException
public boolean isAfterLast() throws SQLException
public boolean isBeforeFirst() throws SQLException
public int getRow() throws SQLException
• getRow() method retrieves the current row number: The first row is number 1, the second number 2, and so on.
37
38
Transactions and JDBCTransactions and JDBC
• Transaction: more than one statement that must all
succeed (or all fail) together
- e.g., updating several tables due to customer purchase
• If one fails, the system must reverse all previous actions
• Also can’t leave DB in inconsistent state halfway
through a transaction
• COMMIT = complete transaction
• ROLLBACK = cancel all actions
39
ExampleExample
• Suppose we want to transfer money from bank account
13 to account 72:
PreparedStatement pstmt = con.prepareStatement("update BankAccount
set amount = amount + ?
where accountId = ?");
pstmt.setInt(1,-100);
pstmt.setInt(2, 13);
pstmt.executeUpdate();
pstmt.setInt(1, 100);
pstmt.setInt(2, 72);
pstmt.executeUpdate();
What happens if this update fails?
40
Transaction ManagementTransaction Management
• Transactions are not explicitly opened and closed
• The connection has a state called AutoCommit mode
• if AutoCommit is true, then every statement is
automatically committed
• if AutoCommit is false, then every statement is added to
an ongoing transaction
• Default: true
41
AutoCommitAutoCommit
• If you set AutoCommit to false, you must explicitly commit or
rollback the transaction using Connection.commit() and Connection.rollback()
• Note: DDL statements (e.g., creating/deleting tables) in a
transaction may be ignored or may cause a commit to occur
- The behavior is DBMS dependent
setAutoCommit(boolean val)