JDBC Objects

Data: Data is raw facts, which describes the properties of an object. Properties are also know as attributes. Object is
also know as Entity.
Ex. Student is one entity is has properties know as: Name, Age, AdharNo, PhoneNo, Address etc.

Database: It is a place or medium in which we stores the data in systematic and organized manner. In Database we are
performing CRUD operations.

DBMS: It is a software, which is used to maintain and manage the database. Security and authorization are the two
important features of the DBMS. Ex. MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access, DB2 etc

1. The Concept of JDBC

2. JDBC Driver Types

JDBC driver specification classifies JDBC drivers into four groups. Each group is referred to as a
JDBC driver type and addresses a specific need for communicating with various DBMS’s.
A. Type1 JDBC-to-ODBC Driver
 Microsoft was the first company to diverse a way to create a DBMS independent database program
when they created the Open Database Connection.
 ODBC and JDBC have similar driver specifications and an API. The JDBC-to-ODBC driver also called
as JDBC/ODBC Bridge used to translate DBMS calls between the JDBC specification and the ODBC
specification.
 To avoid using JDBC/ODBC Bridge in mission-critical application because the extra translation might
negatively impact the performance.

 It is the thick driver. Implemented using only Java. It is not platform independent. It is the database
independent.
B. Type 2 Java/Native code Driver
 The Java/Native code Driver uses java classes to generate platform-specific code that is, code only
understood by a specific DBMS. The manufacture of the DBMS provides both the Java/ Native Code
driver and API classes.
 It is implemented using both Java and Native libraries. It follows 2 tier architecture. It is platform
dependent and database dependent. It is thick driver.

C. Type 3 JDBC Driver

 Type 3 JDBC driver also referred to as the Java protocol and it is the most commonly used JDBC driver.
The type 3 JDBC driver converts SQL queries into JDBC formatted statements.
 The JDBC formatted statements are translated into the format required by DBMS. Developed using
java.
 It is platform independent and Database independent. It is thick driver.

D. Type 4 JDBC Driver

 Type 4 JDBC Driver also known as type 4 database protocol. It is similar to the type 3 JDBC Driver except SQL
queries are translated into the format required by the DBMS.
 SQL queries do not need to converted JDBC formatted systems.
 This is the fastest way to communicate SQL queries to the DBMS.
 It is the thin driver. It is the platform independent and database dependent. Follows two tier architecture.
Implemented using Java only.

Difference between ODBC and JDBC

ODBC JDBC
1. ODBC stand for Open Database Connection 1. JDBC stand for Java database connection

2. Introduced by Microsoft company in 1992 2. Introduced by Sun micro System in 1997

3. We can use ODBC for any languages like C, C++, 3. We can use JDBC for java languages
Java etc.

4. We can use ODBC for only Windows platform 4. We can use JDBC for any platforms
 5. ODBC drivers developed in native language like 5. Most of the time JDBC driver are developed in java
C/C++

6. For java application it is not recommended to use 6. For java application it is highly recommended to use
ODBC because performance will be down due to JDBC because there are no performance and platform
internal conversion and applications will become dependent problems.
platform dependent

5. JDBC Packages:
JDBC API is contained in two packages
1. [Link]
2. [Link]

[Link] [Link]
1. [Link] contains basic classes 1. [Link] java extension contains more
and interfaces. Which can be advanced classes and interfaces. These are
used to communicate with used to communicate with databases.
database.
2. Ex: [Link]
2. Ex: [Link];
[Link]
[Link]

3. Example of [Link] package 4. .Example of [Link] package Classes

Classes and Interfaces are: and Interfaces are:
Interfaces : Driver, Interface: DataSource, RowSet,
Connection, ResultSet, RowSetListener,ConnectionEventListener
Statement, PreparedStatement,
Classes: RowSetEvent, ConnectionEvent
CollableStatement
Class: DriverManager, Date,
Time, TimeStamp, Types

 Driver software is a collection of implementation classes of JDBC API, Which can be used to
communicate with a particular database.
Example: Driver software for Oracle means collection of classes of JDBC API, which can be used to
communicate with oracle database.
 Every driver software is identified with special class which is nothing but Driver class.
5. A Brief Overview of the JDBC process
JDBC process is divided into five routines. These included:
1. Loading the JDBC Driver
2. Connecting to the DBMS
3. Creating and executing statements

4. Processing data returned by the DBMS

5. Terminating the connection with DBMS

1. Loading the JDBC Driver

 JDBC driver must be loaded before the J2EE components can connect to the DBMS. The [Link]()
Method is used to load the JDBC driver. The driver is loading by calling the [Link]() method and passing it the
name of the driver.
Ex: [Link](“[Link]”); // this for MySQL database

2. Connecting to the DBMS

 Once the driver is loaded, the J2EE component must connect to the DBMS using the [Link]()
method.
 The [Link] class is the highest class in the [Link] hierarchy and it is responsible for managing Driver
information.

 [Link]() method is passed the URL of the database, and user ID and password if required by
the DBMS. The URL is the String object that contains the driver name and name of the database that is being accessed
by the java application.
 [Link]() method return Connection interface object, that is used through out the process to
reference the database. getConnection() method is the overloaded method.

String URL=”jdbc:mysql://localhost:3306/persondb”
String user=”root”;
String password=”tiger”
Private Connection con;

try{
[Link](“[Link]”);

con=[Link](URL,user,password);
}

3. Creating and executing statements

 After the JDBC driver is loaded and connection is successfully made with a particular database managed by the
DBMS. Next is to sent a SQL query to the DBMS for processing. SQL queries are a series of SQL commands that
direct the DBMS.

 The [Link]() method is used to create Statement object. Statement object is then used to execute a query
and return a ResultSet object that contains response from the DBMS.
 The executeQuery() method is used to execute the DQL commands. executeUpdate() is used to execute DML queries.
Ex: Statement st;
ResultSet results;
try{
String query=”select * from Customers”;
st=[Link]();
Results=[Link](query);
[Link]();
}

4. Processing data returned by the DBMS

 The ResultSet object contains the results received from the DBMS after the query is processed.
 ResultSet object contains methods used to interact with data that is returned by the DBMS to the java application.
 The next() of ResultSet interface returns boolean value(true/false). If the next data is present then only it returns true
otherwise it returns false.
 The getString() method of the ResultSet object is used to copy the value of the specified column in the current row of
the ResultSet to a String object.
ResultSet results;
String fname, lname, printrow;
boolean records=[Link]();
if(!records){
[Link](“No records found”);
return;
}
else{
do{
fname=[Link](fname);
lname=[Link](lname);
Printrow=fname + “ “ +lname;
[Link](printrow);
}while([Link]());
}

5. Terminating the connection with DBMS

 The connection to the DBMS is terminated by the close() method of the Connection object once the java application
is finished accessing the DBMS. The close() method throws an exception if a problem encountered.

 Ex: [Link]();

6. Connection Pool
 Connecting to a database is performed on per-client basis. That is each client must open its own connection
to database and connection cannot be shared with unrelated clients.(drawback)
 Ex: a client that needs to frequently interact with a database must either open a connection and leave the
connection during processing or reconnect each time the client needs to access the database.
 Leaving connection open might prevent another client from accessing the database. Connecting and
reconnecting simply time consuming and causes performance degradation.
 So JDBC 2.1 standard Extension API introduced Connection Pooling to address the problem.
 The connection pool is the collection of database connections that are open and loaded into memory so
these connection can be reused without having to reconnect to the database.
 There are two types of connections made to the database.
 A) The first is the Physical connection pool itself is implemented by the application server, using
PooledConnection objects. PooledConnection objects are cached and reused.
 B) The second is the Logical connection pool is made by client calling the [Link]()
method, which connects to the PooledConnection object that already has physical connection to the
database.
 Ex: Context ctext=new InitialContext();
DataSource pool=(DataSource)[Link](“java:comp/env/jdbc/pool”);

Connection db=[Link]();
// Database connection code
[Link]()

This code connects to a database using connection pooling via JNDI lookup. It’s efficient because
it reuses existing database connections rather than opening a new one each time.

7. Statement Objects
Once a connection to the database is opened, the J2EE component creates and sends a query to
access data contained in the database.
One of the three Statement Objects are used to execute the query.
1. Statement Object (used to execute query immediately)
2. PreparedStatement (used to execute compiled queries)
3. CollableStatement (used to execute stored procedures)

Statement Object:
 Statement object is used whenever a J2EE component needs to immediately execute a query
without first having the query compiled. The statement Object contains the executeQuery()
method, which is passed the query as an argument. The query is then transmitted to the DBMS
for processing.
 The executeQuery() method returns one ResultSet object that contains rows, columns and
metadata that represents data requested by query. The ResultSet object also contains methods
that are used to manipulate data in the ResultSet.
 The execute() method of the Statement object is used when there may be multiple results returned.
 The executeUpdate() method is used to execute queries that contains UPDATE and DELETE
SQL statements. Which changes values in a row and removes rows respectively. executeUpdate()
method returns an integer indicating the number of rows that were updated by the query.
 executeUpdate() is used to INSERT, UPDATE, DELETE and DDL satements.
 Methods Return type
1. public ResultSet ResultSet
executeQuery(query_to_execute) Output is group of records stores in
ResultSet object
2. public int executeUpdate(query) int
Represents number of row affected
3. public boolean execute(query) true - SELECT Query
false - Non_SELECT Query
Ex: boolean b=[Link](sql);
If(b==true) {
ResultSet rs=[Link]();
}
else {
int rowCount=[Link]();
}

Ex: String query= “SELECT * FROM Student”;

Statement st=[Link]();
ResultSet result=[Link](query);
[Link]();
Process the Results using suitable methods

Example:
public class JDBCDemo {
public static void main(String[] args) {

try {
[Link]("[Link]");

Connection
con=[Link]("jdbc:mysql://localhost:3306/acharyadatabase","root","pa
ssword");
[Link]("connection successfully established ");

Statement st= [Link]();

//boolean res =[Link]("select * from [Link]");

boolean res=[Link]("update student set name='Manohar' where id=101");

if(res==true) {

ResultSet rs=[Link]();
while([Link]())
[Link]("Id of the Student:"+
[Link](1)+"\nName of the
sudent"+[Link](2)+"\nMarks of the
student"+[Link](3));

}
else {
int rowCount=[Link]();
[Link](rowCount+" rows updatede");
}
 } catch (ClassNotFoundException | SQLException e) {

[Link]();
}
}

PreparedStatement:
 A SQL query must be compiled before the DBMS processes the query. Compiling occurs after
one of the Statement object’s execution methods is called.
 A SQL query can be precompiled and executed by using the PreparedStatement object and query
is constructed.
 A question mark is used as a placeholder for a value that is inserted into the query after the query
is compiled.
 prepareStatement() method of the Connection object is called to return the PreparedSatement
object. The prepareStatement() method is passed the query, which is then precompiled.
 The setXxx() methods of the PreparedSatement object is used to replace the question mark with
the value passed to the setXxx() method. There are number of setXxx() method available in the
PreparedStatement object.
 The setXxx() method requires two parameters, first parameter is an integer that identifies the
position of the question mark placeholder and second parameter is the value that replaces the
question mark placeholder.
 The executeQuery() statement does not require parameter because the query that is to be executed
is already associated with the PreparedStatement object.
 Advantages of using the PreparedStatement object is that the query is precompiled once the
setXxx() method called as needed to change the specified values of the query without having to
recompile the query.
 Example:

String query= “SELECT * FROM Customers where CustNumber= ? ”;

PreparedStatement pstatement=[Link](query);
Results=[Link]();
[Link]();

Example:
public class PreparedStatementDemo {
public static void main(String[] args) {
try {
[Link]("[Link]");
Connection con;
con=[Link]("jdbc:mysql://localhost:3306/acharyadatabase","r
oot","password");
[Link]("connection successfully established ");
 PreparedStatement pst=[Link]("select *from student where name=?");
[Link](1,"Ajit");
ResultSet rs=[Link]();
while([Link]()) {
[Link]([Link](1)+" student details is:\n Name:
"+[Link](2)+" Marks: "+[Link](3));
}

} catch (ClassNotFoundException | SQLException e) {

// TODO Auto-generated catch block
[Link]();
}
}
}

CollableSatement:
 CallableStatement object is used to call a stored procedure from within a J2EE object.
 CallableStatement is block of code and is identified by a unique name.
 CallableStatement uses three types of parameters when calling a stored procedure. These
parameters are IN, OUT and INOUT.
 The IN parameters contains any data that needs to be passed to the stored procedure and whose
value is assigned using the setXxx() method.
 The OUT parameter contains the value retured by the stored procedures, if any. The OUT
parameter must be registered using the registerOutParameter() method and then is later retrived
by the J2EE component using getXxx() method.
 INOUT parameter is a single parameter that is used to both pass information to the stored
procedure and retrieve information from a stored procedure.
 Example:
Stirng query= ”{ CALL LastOrderNumber(?) } “;
CallableStatement cstatement=[Link](query);
[Link](1,[Link]);
[Link]();
lastOrderNumber=[Link](1);
[Link]();
Example:

public class CallStmtDemo {

public static void main(String[] args) {

// Acharyadb.AIT_CSE
try {
[Link]("[Link]");

Connection
con=[Link]("jdbc:mysql://localhost:3306/Acharyadatabase","root","passw
ord");
 CallableStatement callStmt=(CallableStatement) [Link]("{CALL
GetAllStudentsByName()}");
ResultSet rs=[Link]();
while([Link]()) {
[Link]("Student Id: "+[Link](1));
[Link]("Student Name: "+[Link](2));
[Link]("Student Marks: "+[Link](3));
}

} catch (ClassNotFoundException | SQLException e) {

[Link]();
}
}

Stored Procedure in MySQL database:

Writing a stored procedure in MySQL involves creating a named block of SQL

statements that can be reused and executed with a single call. Here's a basic structure
and example to get you started.

Syntax:
DELIMITER //
CREATE PROCEDURE procedure_name (IN param1 datatype, OUT param2
datatype, ...)
BEGIN
-- SQL statements here
END //
DELIMITER ;

Example: DBA or database developer creates a stored procedure GetAllAtudents() and this
stored procedure is processed using prepareCall() method present in the Connection object.
prepareCall() return CallableStatement Object. Using this object we can execute the query
return in the procedure.

DELIMITER //
CREATE PROCEDURE GetAllAtudents()
BEGIN
SELECT * FROM [Link];
END //
DELIMITER ;
8. ResultSet
 The executeQuery() method is used to send the query to the DBMS for processing and returns
ResultSet object that contains data that was requested by the query.

 The ResultSet object contains methods that are used to copy data from the ResultSet into Java
collection object or variable for further processing.

 Data in ResultSet object is logically organized into virtual table consisting of rows and
columns.

 In addition to data, ResultSet object also contains metadata such as column names, column size
and column data type.
 The ResultSet uses a virtual cursor to point to a row of the virtual table. The J2EE component
must move the virtual cursor to each row and then use other method of the ResultSet object to
interact with the data stored in columns of that row.
 Virtual cursor is positioned above the first row of data when the ResultSet is returned by the
executeQuery() method.

 Always virtual cursor must be moved to the first row using the next() method

 The next() returns boolean true if the row contains data; otherwise a boolean false is returned
indicating that no more rows exist in the ResultSet.

 Once the virtual cursor points to a row, the getXxx() method is used to copy data from the row
to a collection object or to a variable.

 The getXxx() method data type specific. Ex: getString() method is used to copy string data
from column of the ResultSet().

 Example of Reading the ResultSet

Public class ResultSEtDemo
{
Public static void main(String[] args) throws SQL Exception
{
String URL=”jdbc:mysql://localhost:3306/acharyadatabase”;
String user=”root”;
String password=”tiger”
Connection con;
[Link](“[Link]”);
con=[Link](URL,user,password);

Con=[Link](url,user,password);
Statement st=[Link]();
ResultSet rs=[Link](“SELECT * FROM Employee where salary>50000”);
Boolean flag=false;
[Link](“\n\n --------Employee Data is--------\n\n”);
While([Link]()){
flag=true;
[Link]([Link](1)+”\t”+[Link](2)+”\t” +[Link](3));
}
If(flag==false)
[Link](“No Matched records found\n”);
}
[Link]();
 }
}

9. Transaction Processing
 A database transaction consist of a set of SQL statements, each of which must be successfully executed for
the transaction to be completed. If one fails, SQL statements that executed successfully up to that point in
the transaction must be rolled back.

 A database transaction is not completed until the J2EE component calls the commit() method of the
Connection object. All SQL statements executed prior to the call to the commit () method can be rolled
back.

 commit() method must be called regardless if the SQL statement is part of a transaction or not. The commit()
method automatically calls because DBMS has an AutoCommit feature that is default set to true.

 If a J2EE component is processing a transaction, the AutoCommit feature must be deactivated by calling
the setAutoCommit() method and passing it a false parameter. Once the transaction is completed, the
setAutoCommit() method is called again, this time passing it a true parameter, reactivating the
AutoCommit feature.

 Example:

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

[Link]("[Link]");
Connection con=
[Link]("jdbc:mysql://localhost:3306/acharyadatabase","root","passwo
rd");
try {
[Link](false);
[Link]("connection successfully established ");

Statement st= [Link]();

String sqlQ1="insert into student values(500,'Vishwa',65)";

[Link](sqlQ1);
[Link]();

String sqlQ2="insert in student values(201,'Vinut',89)";

[Link](sqlQ2);
[Link]();

} catch ( SQLException e) {
[Link]();
[Link]();
}
}
11. Metadata
 Metadata is data bout data. A J2EE component can access metadata by using the
DatabaseMetaData interface.
 The DatabaseMetaData interface is used to retrieve information about database, table, columns
and indexes among other information about the DBMS.
 A J2EE component retrieves metatdata about the database by calling the getMetaData() method
of the Connection object. The getMetaData() method returns a DatabaseMetaData object that
contains information about the database and its components.
 Once the DatabaseMetaData object is obtained, an assortment of methods contained in the
DatabaseMetaData object are called to retrieve specific metadata.
 Commonly used DatabasemetaData object methods:
 getDatabaseProductName(): returns the product name of the database
 getUserName() : returns the user name
 getURL() : return the database
 getSchemas() : returns the Schema names available in this database
 getPrimaryKeys() : return primary keys
 getProcudures() : returns stored procedure names.
 getTables() : returns names of the databases.

12. Data Types

 The setXxx() and getXxx() methods are used to set values of a specific data type and to retrieve
a value of specific data type. The xxx in the names of these methods is replaced with the name
of the data type.

 The below table shows list of SQL data types

SQL Type Java Type

1. CHAR String
2. VARCHAR String
3. LONGVARCHAR String
4. NUMERIC [Link]
5. DECIMAL [Link]
6. BIT Boolean
7. TINYINT Byte
8. SMALLINT Short
9. INTEGER Integer
10. BIGINT Long
11. REAL Float
12. FLOAT Float
13. DOUBLE double
14. BINARY Byte[]
 15. VARBINARY Byte[]
16. LONGVARBINARY byte[]
17. BLOB [Link]
18. CLOB [Link]
19. ARRAY [Link]
20. STRUCT [Link]
21. DATE [Link]
22. TIME [Link]
23. TIMESTAMP [Link]

13. Exceptions
 There are three kinds of exception that are thrown by JDBC methods.
 These are : a) SQLExceptions b) SQLWarnings c) DataTrucation
 SQLExceptions commonly reflect a SQL syntax error in the query and are thrown by many of the
methods contained in the [Link] package. This exception is most commonly caused by connectivity
issues with the database. It can also caused y subtle coding errors like trying to access an object that is
been closed.
 The getNextException() method of the SQLException object is used to return details about the SQL
error or null if the last exception was retrieved.
 The getErrorCode() method of the SQLException object is used to retrieve vendor-specific error codes.

 SQLWarning throws warnings received by the Connection from the DBMS. The getWarning() method
of the Connection object retrieves the warning and the getNextWarning() method of the Connection
object retrieves subsequent warnings
 Whenever data is lost due to transaction of the data value, a DataTrunction exception is thrown.

ODBC JDBC

1. ODBC Stands for Open Database 1. JDBC Stands for Java database
Connectivity. connectivity.

2. Introduced by SUN Micro Systems in

2. Introduced by Microsoft in 1992.
1997.

3. We can use ODBC for any language 3. We can use JDBC only for Java
like C, C++, Java etc. languages.

4. We can choose ODBC only Windows

4. We can use JDBC on any platform.
platform.

5. Mostly ODBC Driver is developed in 5. JDBC Stands for Java database

native languages like C, and C++. connectivity.

6. For Java applications it is highly

6. For Java applications it is not
recommended to use JDBC because there
recommended to use ODBC because
are no performance & platform dependent
performance will be down due to internal
problems.
 ODBC JDBC

conversion and applications will become

platform-dependent.

7. ODBC is procedural. 7. JDBC is object-oriented.