jdbc tutorial for m.sc(it)
TRANSCRIPT
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
1/22
JDBC Tutorial
M.Sc (IT) Sem-IV
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
2/22
What is JDBC
The JDBC API is a Java API that can access any
kind of tabular data, especially data stored in
a Relational Database.
JDBC stands for Java Database Connectivity.
JDBC works with Java on a variety of
platforms, such as Windows, Mac OS, and the
various versions of UNIX.
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
3/22
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
4/22
Common JDBC Components
The JDBC API provides the following interfaces and classes:
DriverManager: This interface manages a list of database drivers. Matchesconnection requests from the java application with the proper database
driver using communication subprotocol.
Driver: This interface handles the communications with the database
server. You will interact directly with Driver objects very rarely. Instead,
you use DriverManager objects, which manages objects of this type.
Connection : The connection object represents communication context,
i.e., all communication with database is through connection object only.
Statement : You use objects created from this interface to submit the SQL
statements to the database.
ResultSet: These objects hold data retrieved from a database after you
execute an SQL query using Statement objects.
SQLException: This class handles any errors that occur in a database
application.
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
5/22
The JDBC 4.0 Packages
The java.sql and javax.sql are
the primary packages for JDBC
4.0. It offers the main classes
for interacting with your data
sources.
JDBC Driver
JDBC drivers enable you toopen database connections
and to interact with it by
sending SQL or database
commands then receiving
results with Java.
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
6/22
JDBC Drivers Types:
Type 1: JDBC-ODBC Bridge
Driver:
In a Type 1 driver, a JDBC
bridge is used to accessODBC drivers installed oneach client machine. UsingODBC requires configuringon your system a Data
Source Name (DSN) thatrepresents the targetdatabase.
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
7/22
Type 2: JDBC-Native API:
In a Type 2 driver, JDBC APIcalls are converted intonative C/C++ API calls whichare unique to the database.
These drivers typicallyprovided by the databasevendors and used in thesame manner as the JDBC-
ODBC Bridge, the vendor-specific driver must beinstalled on each clientmachine.
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
8/22
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
9/22
Type 100: 100% pure Java:
In a Type 4 driver, a pure
Java-based driver that
communicates directly with
vendor's database throughsocket connection. This is
the highest performance
driver available for the
database and is usually
provided by the vendor
itself.
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
10/22
Which Driver Should I use?
If you are accessing one type of database, such asOracle, Sybase, or IBM, the preferred driver type is 4.
If your Java application is accessing multiple types of
databases at the same time, type 3 is the preferreddriver.
Type 2 drivers are useful in situations where a type 3 ortype 4 driver is not available yet for your database.
The type 1 driver is not considered a deployment-leveldriver and is typically used for development and testingpurposes only.
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
11/22
Steps for JDBC Connection
After you've installed the appropriate driver, it's time to establish adatabase connection using JDBC.
The programming involved to establish a JDBC connection is fairlysimple. Here are these simple four steps:
Import JDBC Packages: Add import statements to your Java
program to import required classes in your Java code.
Register JDBC Driver: This step causes the JVM to load the desired
driver implementation into memory so it can fulfill your JDBC
requests.
Database URL Formulation: This is to create a properly formatted
address that points to the database to which you wish to connect. Create Connection Object: Finally, code a call to the DriverManager
object's getConnection( ) method to establish actual database
connection.
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
12/22
Import JDBC Packages:
The Import statements tell the Java compiler
where to find the classes you reference in your
code and are placed at the very beginning of your
source code.
To use the standard JDBC package, which allows
you to select, insert, update, and delete data in
SQL tables, add the following imports to yoursource code:
import java.sql.* ; // for standard JDBC programs
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
13/22
Register JDBC Driver:
You must register the your driver in your program before you use it.
Registering the driver is the process by which the Oracle driver'sclass file is loaded into memory so it can be utilized as animplementation of the JDBC interfaces.
You need to do this registration only once in your program.
to register a driver use Java's Class.forName() method to
dynamically load the driver's class file into memory, whichautomatically registers it.
Prepared By Prof. Rakhi Budhrani
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
14/22
Database URL Formulation:
After you've loaded the driver, you can establish a connection using the
DriverManager.getConnection() method.getConnection(String url, String user, String password)
Here each form requires a database URL. A database URL is an address
that points to your database.
Prepared By Prof. Rakhi Budhrani
static String userid="scott",password = "tiger";
static String url = "jdbc:odbc:Rakhi";
Here Rakhi is the DNS which you have created in the control pannel with the help
of ODBC
con = DriverManager.getConnection(url, userid, password);
Create Connection Object:
Using a database URL with a username and password:DriverManager.getConnection() method is used to create a connection object.
The most commonly used form of getConnection() requires you to pass a database
URL, a username, and a password:
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
15/22
JDBC - Statements
Once a connection is obtained we can interact with the database. The
JDBC Statement, CallableStatement, and PreparedStatement interfacesdefine the methods and properties that enable you to send SQL or PL/SQL
commands and receive data from your database.
Prepared By Prof. Rakhi Budhrani
nterfaces Recommended Use
Statement
Use for general-purpose access to your database. Useful
when you are using static SQL statements at runtime.
The Statement interface cannot accept parameters.
PreparedStatement
Use when you plan to use the SQL statements many
times. The PreparedStatement interface accepts input
parameters at runtime.
CallableStatement
Use when you want to access database stored
procedures. The CallableStatement interface can also
accept runtime input parameters.
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
16/22
Creating Statement Object:
Statement stmt = null;
try {
stmt =conn.createStatement( );
. . .
} catch (SQLException e) {
. . .
} finally {
. . .
}
Prepared By Prof. Rakhi Budhrani
Statement stmt = cn.createStatement();
stmt.executeUpdate(insertString1);
stmt.executeUpdate(insertString2);
stmt.executeUpdate(insertString3);
stmt.executeUpdate(insertString4);
insertString1 = "insert into Master
values(100,'SAL',30.5,'1-Dec-09',40.5)";
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
17/22
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
18/22
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
19/22
JDBC - Result Sets
The SQL statements that read data from a databasequery return the data in a result set. The SELECTstatement is the standard way to select rows from adatabase and view them in a result set. Thejava.sql.ResultSetinterface represents the result set ofa database query.
The methods of the ResultSet interface can be brokendown into three categories:
Navigational methods: used to move the cursoraround.
Get methods: used to view the data in the columns of
the current row being pointed to by the cursor. Update methods: used to update the data in the
columns of the current row. The updates can then beupdated in the underlying database as well.
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
20/22
Select Query
Statement st=cn.createStatement();
ResultSet rs=st.executeQuery("Select * fromMaster");
while(rs.next()){
System.out.println(rs.getInt(1) + "\t" +
rs.getString(2)+ "\t\t" +rs.getF
loat(3)+"\t"+rs.getDate(4)+"\t"+rs.getFloat(5));
}
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
21/22
Prepared By Prof. Rakhi Budhrani
-
8/7/2019 JDBC Tutorial for M.Sc(IT)
22/22
Closing
At the end of your JDBC program, it is requiredexplicitly close all the connections, statements,recordsets to the database to end each database
session. rs.close();
st.close();
cn.close();
For more details visit:http://www.tutorialspoint.com/jdbc/index.htm
Prepared By Prof. Rakhi Budhrani