Chapter 5 JDBC.pdf for stufent of computer andtudent It s

i need question choose

1. Chapter Five
DATABASE ACCESS WITH JDBC

2. Objectives
❖To become familiar with the JDBC API
❖To learn how to load a driver, connect to a database, execute statements, and process result
sets using JDBC
❖To use the prepared statements to execute precompiled SQL statements

3. Introduction of JDBC
JDBC is a Java database connectivity API.
Allows Java programs to contain database-independent code.
◦Provides a mechanism for Java code to be portable across databases.
Simplifies the creation and execution of SQL statements.
Uses java.sql package.
◦java.sql.DriverManager-Manages the loading and unloading of database drivers from the underlying system.
◦java.sql.Connection -Handles the connection to a specific database.
◦java.sql.Statement-Contains an SQL statement to be passed to the database.
◦java.sql.ResultSet-Contains the record result set from the SQL statement passed to the database.
To connect to a database and access its contents, JDBC driver that works with that particular
database is required.

4. General structure of JDBC
HowItWorks
▪JavacodecallsJDBClibrary
▪JDBCloadsadriver
▪Drivertalkstoaparticulardatabase
▪Anapplicationcanworkwithseveraldatabasesbyusingallcorresponding
drivers
▪Ideal:canchangedatabaseengineswithoutchanginganyapplicationcode
(notalwaysinpractice)
▪TheJDBCClassesandInterfacesareinthejava.sqlpackage
4
Application JDBC Driver

5. …
5
JDBC consists of two parts:
 The JDBC API, a purely Java-
based API
JDBC Driver Manager, which
communicates with vendor-
specific drivers that interact
with the database
translation to vendor format is
performed on the client
No changes needed to server
Driver (translator) needed on
client

6. Steps in Using JDBC
6

7. JDBC Class Usage
7
DriverManager
Driver
Connection
Statement
ResultSet

8. What are JDBC drivers?
JDBC drivers implement the defined interfaces in the JDBC API for interacting with
your database server.
For example, using JDBC drivers enable you to open database connections
and to interact with it by sending SQL or database commands then
receiving results with Java.
Database vendors and third parties can produce them.
Once you obtain a JDBC driver you should only have to worry about
registering it using DriverManager objects and creating the proper JDBC
URL in order to use it.
8

9. Java Model
9

10. JDBC driver types
JDBC driver implementations vary because of the wide variety of operating systems and
hardware platforms in which Java operates.
Thus, Sun has divided the implementation types into four categories, Types 1, 2, 3, and
4, whose characteristics vary greatly.
Types 1 and 2 rely heavily on additional software (typically C/C++ DLLs) installed
on the client computer to provide database connectivity. Java and JDBC use these
components to interact with the database.
Types 3 and 4 are pure Java implementations and require no additional software to be
installed on the client, except for the JDBC driver. Fortunately, packaging the JDBC
driver with your software distribution is trivial.
10

11. Type Description
1
JDBC-to-ODBCBridgeDriver–connectsJavatoaMicrosoftODBC(OpenDatabaseConnectivity)data
source.
2
Native-API,PartJavaDrivers–enableJDBCprogramstousedatabase-specificAPIs(normallywrittenin
CorC++)thatallowclientprogramstoaccessdatabasesviatheJavaNativeInterface.
3
JDBC-NetPureJavaDrivers–takeJDBCrequestsandtranslatethemintoanetworkprotocolthatisnot
databasespecific.Theserequestsaresenttoaserver,whichtranslatesthedatabaserequestsintoadatabase-
specificprotocol.
4
Native-protocolPureJavaDrivers–convertJDBCrequeststodatabase-specificnetworkprotocols,sothat
Javaprogramscanconnectdirectlytoadatabase.

12. JDBC Driver types
12

13. Some Popular JDBC Drivers
13
RDBMS JDBC Driver Name
MySQL
Driver Name
com.mysql.jdbc.Driver
Database URL format:
jdbc:mysql//hostname/databaseName
Oracle
Driver Name:
oracle.jdbc.driver.OracleDriver
Database URL format:
jdbc:oracle:thin@hostname:portnumber:databaseName
JavaDB
Driver Name:
org.apache.derby.jdbc.ClientDriver
Database URL format:
jdbc:derby://localhost:1527/databaseName
Access
Driver Name:
sun.jdbc.odbc.JdbcOdbcDriver
Database URL format:
jdbc:odbc:databaseName

14. Registering JDBC drivers
To use your JDBC driver you must first register it with the DriverManager object, which
has a driver−registration method.
However, two alternate techniques are available for registering JDBC drivers.
The following is a list of the three different ways to register a JDBC driver:
Class.forName(String driverName).newInstance()
--Class.forName("com.mysql.jdbc.Driver").newInstance();
DriverManager.registerDriver(Driver driverName)
--Driver driver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(driver);
jdbc.drivers property
--System.setProperty("jdbc.drivers", "com.mysql.jdbc.Driver,oracle.jdbc.driver.OracleDriver");
14

15. 15
Developing JDBCPrograms
Loading
drivers
Establishing
connections
Creatingand
executing
statements
Processing
ResultSet
Statement to load a driver:
Class.forName("JDBCDriverClass");
A driver is a class. Forexample:
Database
Access
MySQL
Oracle
Driver Class
sun.jdbc.odbc.JdbcOdbcDriver
com.mysql.jdbc.Driver
oracle.jdbc.driver.OracleDriver
Source
Already inJDK
Website
Website
The JDBC-ODBC driver for Access is bundled inJDK.
MySQL driver class is inmysqljdbc.jar
Oracle driver class is inclasses12.jar
To use the MySQL and Oracle drivers, you have to add mysqljdbc.jarand
classes12.jar in the classpath.

16. 16
Developing JDBCPrograms
Loading
drivers
Establishing
connections
Creatingand
executing
statements
Processing
ResultSet
Connection connection =DriverManager.getConnection(databaseURL);
Database:
Access :
MySQL:
Oracle:
URLPattern
jdbc:odbc:dataSource
jdbc:mysql://hostname:port# /dbname, username, password
jdbc:oracle:thin:@hostname:port#:oracleDBSID, username,pwd
Examples:
ForAccess:
Connection connection = DriverManager.getConnection
("jdbc:odbc:ExampleMDBDataSource");
ForMySQL:
Connection connection = DriverManager.getConnection
("jdbc:mysql://localhost/test");
ForOracle:
Connection connection = DriverManager.getConnection
("jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl", "scott","tiger");

17. 17
Developing JDBCPrograms
Loading
drivers
Establishing
connections
Creatingand
executing
statements
Processing
ResultSet
Executing statement (forselect):
// Select the columns from the Studenttable
ResultSet resultSet =stmt.executeQuery
("select firstName, mi, lastName from Student where lastName"
+ " = 'Smith'");
Processing ResultSet (forselect):
// Iterate through the result and print the studentnames
while(resultSet.next())
System.out.println(resultSet.getString(1) + " " +resultSet.getString(2)
+ ". " + resultSet.getString(3));

18. 18
Developing JDBCPrograms
Loading
drivers
Establishing
connections
Creatingand
executing
statements
Processing
ResultSet
Creatingstatement:
Statement statement =connection.createStatement();
Executing statement (for update, delete,insert):
statement.executeUpdate ("create table Temp (col1 char(5),col2
char(5))");
Executing statement (forselect):
// Select the columns from the Studenttable
ResultSet resultSet =statement.executeQuery
("select firstName, mi, lastName from Student where lastName"
+ " = 'Smith'");

19. 19
Developing JDBCPrograms
➢ThegetString(1),getString(2),andgetString(3)methods
retrievethe column valuesforfirstName,mi,and
lastName,respectively.
➢Alternatively,youcanusegetString("firstName"),
getString("mi"),andgetString("lastName")toretrieve
thesamethreecolumnvalues.
➢Thefirstexecutionofthenext()methodsetsthecurrent
rowtothefirstrowintheresultset,andsubsequent
invocationsofthenext()methodsetthecurrentrowtothe
secondrow,thirdrow,andsoon,tothelastrow.

20. importjava.sql.*;
public class SimpleJdbc{
public static void main(String[] args) throws SQLException, ClassNotFoundException
{
// Load the JDBCdriver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); //not necessary for JDBC 4.0
System.out.println("Driverloaded");
// Establish aconnection
Connection connection = DriverManager.getConnection("jdbc:odbc:exampleMDBDataSource");
System.out.println("Databaseconnected");
// Create astatement
Statement statement=connection.createStatement();
// Execute astatement
String query="select firstName, mi, lastNamefrom Student where lastName='Smith'";
ResultSetresultSet=statement.executeQuery(query);
// Iterate through the result and print the studentnames
while(resultSet.next()) {
System.out.print(resultSet.getString(1) + "\t“;
System.out.println(resultSet.getString(2) + "\t" + resultSet.getString(3));
}
connection.close();
}
}

21. JDBC -Statements
➢Onceaconnectionisobtainedwecaninteractwiththedatabase.
➢TheStatementinterfaceprovidesmethodstoexecutequerieswith
thedatabase.
➢TheJDBCStatement,CallableStatement,andPreparedStatement
interfacesdefinethemethodsandpropertiesthatenableyoutosend
SQLcommandsandreceivedatafromyourdatabase.
➢Theyalsodefinemethodsthathelpbridgedatatypedifferences
betweenJavaandSQLdatatypesusedinadatabase.
21

22. JDBC -Statements
Interfeces Recommendeduse
Statement
Used to implement SQL statements with no
parameters.
PreparedStatement
Used for precompiling SQL statements that
might contain input parameters.
Callable Statement
Used to contain stored procedures that may
contain both input and output parameters
22

23. The Statement Objects
CreatingStatementObject:
youneedtocreateastatementusingthe
Connectionobject'screateStatement()method,as
inthefollowingexample:
ClosingStatementObject:
JustasyoucloseaConnectionobjecttosave
databaseresources,forthesamereasonyou
shouldalsoclosetheStatementobject.
Asimplecalltotheclose()methodwilldothejob.
23

24. The Statement Objects:
Onceyou'vecreatedaStatementobject,youcanthenuseittoexecutea
SQLstatementwithoneofitsthreeexecutemethodswhichcanacceptsa
stringcontainingaSQLstatementasanargument.
1.booleanexecute(StringSQL):ReturnsabooleanvalueoftrueifaResultSetobjectcanbe
retrieved;otherwise,itreturnsfalse.
2.intexecuteUpdate(StringSQL):Returnsthenumbersofrowsaffectedbytheexecutionof
theSQLstatement.UsethismethodtoexecuteSQLstatementsforwhichyouexpecttogeta
numberofrowsaffected-forexample,anINSERT,UPDATE,orDELETEstatement.
3.ResultSetexecuteQuery(StringSQL):ReturnsaResultSetobject.Usethismethodwhen
youexpecttogetaresultset,asyouwouldwithaSELECTstatement.
24

25. The execute(), executeQuery(), and executeUpdate()Methods
▪Theexecute()methodshouldbeusediftheexecutionproducesmultipleresult
sets,multipleupdatecounts,oracombinationofresultsetsandupdatecounts
▪TheexecuteQuery()methodshouldbeusediftheexecutionproducesasingle
resultset,suchastheSQLselectstatement.
▪TheexecuteUpdate()methodshouldbeusedifthestatementresultsinasingle
updatecountornoupdatecount,suchasaSQLINSERT,DELETE,UPDATE,
orDDLstatement.
25

26. •PreparedStatement enables you to create parameterized SQLstatements.
•Once a connection to a particular database is established, it can be used to send SQL
statements from your program to thedatabase.
•The Statement interface is used to execute static SQL statements that don’t contain any
parameters.
•ThePreparedStatementinterface,extendingStatement,isusedto
execute a precompiled SQL statement with or withoutparameters.
•Since the SQL statements are precompiled, they are efficient for repeated executions.
•APreparedStatementobjectiscreatedusingthepreparedStatement
method in the Connectioninterface.
•Forexample,thefollowingcodecreatesaPreparedStatementforanSQL
insert statement:
•Statement preparedStatement = connection.prepareStatement ("insert into Student
(firstName, mi, lastName) " + "values (?, ?,?)");
PreparedStatement

27. 27
PreparedStatement
•This insert statement has three question marks as placeholders for parameters representing values for
firstName, mi , and lastName in a record of the Student table.
•As a sub-interface of Statement, the PreparedStatement interface inherits all the methods defined in
Statement.
•It also provides the methods for setting parameters in the object ofPreparedStatement.
•These methods are used to set the values for the parameters before executing statements or procedures.
In general, the set methods have the following name andsignature:
setX(int parameterIndex, Xvalue);
•whereXisthetypeoftheparameter,andparameterIndexistheindexoftheparameterin
the statement. The index starts from1.
•For example, the method setString(int parameterIndex, String value) sets a String value to the
specifiedparameter.

28. 28
PreparedStatement
•Thefollowingstatementspasstheparameters"Jack","A",and"Ryan”tothe
placeholders for firstName, mi , and lastName inpreparedStatement:
preparedStatement.setString(1, "Jack");
preparedStatement.setString(2, "A");
preparedStatement.setString(3,"Ryan");
•Aftersettingtheparameters,youcanexecutethepreparedstatementbyinvokingexecuteQuery()fora
SELECTstatementandexecuteUpdate()foraDDLorupdatestatement.
•TheexecuteQuery()andexecuteUpdate()methodsaresimilartotheonesdefinedintheStatement
interfaceexceptthattheydon’thaveanyparameters,becausetheSQLstatementsarealreadyspecified
inthepreparedStatementmethodwhentheobjectofPreparedStatementiscreated.

29. …cont’d

30. …cont’d
Code to update data
PreparedStatementstmt=con.prepareStatement("update empset name=?
where id=?");
stmt.setString(1,"Hana"); //1 specifies the first parameter in the query i.e. name
stmt.setInt(2,101);
inti=stmt.executeUpdate();
System.out.println(i+" records updated");

31. 31
JDBC -Result Sets
➢TheSQLstatementsthatreaddatafromadatabasequeryreturnthedatainaresultset.
➢Therowsthatsatisfyaparticularqueryarecalledtheresultset.
➢Ausercanaccessthedatainaresultsetusingacursoronerowatatimefromtoptobottom
➢Thejava.sql.ResultSetinterfacerepresentstheresultsetofadatabasequery.
➢AResultSetobjectmaintainsacursorthatpointstothecurrentrowintheresultset.
➢ResultSetinterfacemethodscanbebrokendownintothreecategories:
◦Navigationalmethods:usedtomovethecursoraround.
◦Getmethodsforviewingaresultset:usedtoviewthedatainthecolumnsofthecurrentrowbeingpointed
tobythecursor.
◦Updatemethods:usedtoupdatethedatainthecolumnsofthecurrentrow.Theupdatescanthenbe
updatedintheunderlyingdatabaseaswell.

32. 32
JDBC -Result Sets
Navigating a Result Set:
There are several methods in the ResultSetinterface that involve moving the cursor, including:
S.N. Methods & Description
1
public void beforeFirst() throws SQLException
Moves the cursor to just before the first row
2
public void afterLast() throws SQLException
Moves the cursor to just after the last row
3
public booleanfirst() throws SQLException
Moves the cursor to the first row
4
public void last() throws SQLException
Moves the cursor to the last row.
5
public booleanabsolute(introw) throws SQLException
Moves the cursor to the specified row

33. 33
JDBC -Result Sets
Navigating a Result Set:
S.N. Methods & Description
6
public booleanrelative(introw) throws SQLException
Moves the cursor the given number of rows forward or backwards from where it currently is pointing.
7
public booleanprevious() throws SQLException
Moves the cursor to the previous row. This method returns false if the previous row is off the result set
8
public booleannext() throws SQLException
Moves the cursor to the next row. This method returns false if there are no more rows in the result set
9
public intgetRow() throws SQLException
Returns the row number that the cursor is pointing to.
10
public void moveToInsertRow() throws SQLException
Moves the cursor to a special row in the result set that can be used to insert a new row into the database. The current cursor location is
remembered.
11
public void moveToCurrentRow() throws SQLException
Moves the cursor back to the current row if the cursor is currently at the insert row; otherwise, this method does nothing

34. 34
JDBC -Result Sets
Viewing a Result Set:
The ResultSetinterface contains dozens of methods for getting the data of the current row.
There is a get method for each of the possible data types, and each get method has two versions:
1.One that takes in a column name.
2.One that takes in a column index.
For example, if the column you are interested in viewing contains an int, you need to use one of the getInt()
methods of ResultSet:
S.N. Methods & Description
1
public intgetInt(String columnName) throws SQLException
Returns the intin the current row in the column named columnName
2
public intgetInt(intcolumnIndex) throws SQLException
Returns the intin the current row in the specified column index. The column index starts at 1,
meaning the first column of a row is 1, the second column of a row is 2, and so on.

35. 35
JDBC -Result Sets
➢Similarly there are get methods in the ResultSetinterface for each of the eight Java primitive types, as well as
common types such as java.lang.String, java.lang.Object, and java.net.URL
➢There are also methods for getting SQL data types java.sql.Date, java.sql.Time, java.sql.TimeStamp,
java.sql.Clob, and java.sql.Blob.
ResultSetMethods
String getString(intcolumnIndex)
booleangetBoolean(intcolumnIndex)
byte getByte(intcolumnIndex)
short getShort(intcolumnIndex)
intgetInt(intcolumnIndex)
long getLong(intcolumnIndex)
float getFloat(intcolumnIndex)
double getDouble(intcolumnIndex)
Date getDate(intcolumnIndex)
Time getTime(intcolumnIndex)
Timestamp getTimestamp(intcolumnIndex)

36. JDBC -Result Sets
ResultSetMethods
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)
getMaxRows/setMaxRows
◦Maximum number of rows a ResultSetmay contain
◦Unless explicitly set, the number of rows is unlimited
36

37. 37
JDBC -Result Sets
Updating a Result Set:
The ResultSetinterface contains a collection of update methods for updating the data of a result set.
As with the get methods, there are two update methods for each data type:
1.One that takes in a column name.
2.One that takes in a column index.
For example, to update a String column of the current row of a result set, you would use one of the
following updateString() methods:
S.N. Methods & Description
1
public void updateString(intcolumnIndex, String s) throws SQLException
Changes the String in the specified column to the value of s.
2
public void updateString(String columnName, String s) throws SQLException
Similar to the previous method, except that the column is specified by its name instead of its
index.

38. 38
JDBC -Result Sets
Updating a Result Set:
Thereareupdatemethodsfortheeightprimitivedatatypes,aswellasString,Object,URL,andtheSQLdatatypesinthe
java.sqlpackage.
UpdatingarowintheresultsetchangesthecolumnsofthecurrentrowintheResultSetobject,butnotintheunderlying
database.Toupdateyourchangestotherowinthedatabase,youneedtoinvokeoneofthefollowingmethods.
S.N. Methods & Description
1
public void updateRow()
Updates the current row by updating the corresponding row in the database.
2
public void deleteRow()
Deletes the current row from the database
3
public void refreshRow()
Refreshes the data in the result set to reflect any recent changes in the database.
4
public void cancelRowUpdates()
Cancels any updates made on the current row.
5
public void insertRow()
Inserts a row into the database. This method can only be invoked when the cursor is pointing to the insert row.

39. Example code for updating a resultsetand hence the database:
rs= st.executeQuery(“select * from emp”);
while(rs.next()){
if(rs.getString(“name”).equals(“Tom”)){
rs.updateString(“name”, “Tim”);
rs.updateRow();
}
}
//Example code for deleting a resultsetand hence the database:
rs= st.executeQuery(“select * from emp”);
while(rs.next())
{
if(rs.getString(“name”).equals(“Tom”))
{
rs.deleteRow();
}
}

40. 40
JDBC -Result Sets
➢JDBCprovidesfollowingconnectionmethodstocreatestatementswithdesired
ResultSet:
1.createStatement(int RSType, int RSConcurrency);
2.prepareStatement(String SQL, int RSType, int RSConcurrency);
3.prepareCall(String sql, int RSType, int RSConcurrency);
➢ThefirstargumentindicatethetypeofaResultSetobjectandthesecondargument
isoneoftwoResultSetconstantsforspecifyingwhetheraresultsetisread-onlyor
updatable.

41. JDBC -Result Sets
Type of ResultSet: The possible RSTypeare given below, If you do not specify any ResultSettype, you will
automatically get one that is TYPE_FORWARD_ONLY.
41
Type Description
ResultSet.TYPE_FORWARD_ONLY The cursor can only move forward in the result set.
ResultSet.TYPE_SCROLL_INSENSITIVE
The cursor can scroll forwards and backwards, and the
result set is not sensitive to changes made by others to
the database that occur after the result set was created.
ResultSet.TYPE_SCROLL_SENSITIVE.
The cursor can scroll forwards and backwards, and the
result set is sensitive to changes made by others to the
database that occur after the result set was created.

42. 42
JDBC -Result Sets
Concurrency of ResultSet:
The possible RSConcurrencyare given below, If you do not specify any Concurrency type, you will automatically get one
that is CONCUR_READ_ONLY .
Example:
try {
Statement stmt= conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
}
catch(Exception ex) { .... }
finally {....}
Concurrency Description
ResultSet.CONCUR_READ_ONLY Creates a read-only result set. This is the default
ResultSet.CONCUR_UPDATABLE Creates an updateable result set.

43. Batch Processing in JDBC
Instead of executing a single query, we can execute a batch (group) of queries.
It makes the performance fast. Allows you to group related statements into a batchand submit
them with one call to the database.
When you send several messages to the database at once, you reduce the amount of
communication overhead thereby improving performance.
The java.sql.Statementand java.sql.PreparedStatementinterfaces provide methods for batch
processing.
Advantage of Batch Processing : Fast Performance

44. Batch Processing in JDBC
Methods of Statement interface
The required methods for batch processing are given below:
void addBatch(String query) -It adds query into batch.
int[] executeBatch() -It executes the batch of queries.
Example
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
con.setAutoCommit(false);
Statement stmt=con.createStatement();
stmt.addBatch("insert into empvalues(190,'abhi',40000)");
stmt.addBatch("insert into empvalues(191,'umesh',50000)");
stmt.executeBatch(); //executing the batch
con.commit();
con.close();

45. CallableStatement
45
•The CallableStatement interface is designed to execute SQL-storedprocedures.
•The procedures may have IN, OUT or IN OUTparameters.
•An IN parameter receives a value passed to the procedure when it iscalled.
•AnOUTparameterreturnsavalueaftertheprocedureiscompleted, but it
doesn’t contain any value when the procedure iscalled.
•An IN OUT parameter contains a value passed to the procedure when it iscalled,
and returns a value after it iscompleted.
•For example, the following procedure in Oracle PL/SQL has IN parameter p1,
OUT parameter p2, and IN OUT parameterp3.
•create or replace procedure sampleProcedure (p1 in
varchar, p2 out number, p3 in out integer) is
begin
/* do something*/
endsampleProcedure;
/
CallableStatement callableStatement = connection.prepareCall(callString);

46. 46
≈ ∕∕ ≈