Transcript JDBC - Way2mca

JDBC
JDBC
• Lets programmers connect to a database, query it or
update through a Java application.
• JDBC library is implemented in java.sql package.
2
Java Database Connectivity (JDBC)
Java app
JDBC
calls
JDBC
JDBC
JDBC
driver
driver
driver
Database
commands
Database
Database
Database
A driver is a program that converts the Java method calls to the
corresponding method calls understandable by the database in use.
• JDBC loads a driver
• Driver talks to a particular database
• An application can work with several databases by using all
corresponding drivers
JDBC Driver
• A driver is a program that converts the Java method
calls to the corresponding method calls understandable
by the database in use.
JDBC Architecture
Java Application
JDBC API
JDBC Driver Manager
JDBC Driver API
JDBC-ODBC
Bridge
Vendor Specific
ODBC Driver
Database
Vendor Specific
JDBC Driver
Database
JDBC vendor specific Architecture
Oracle
Driver
Oracle
Java
Application
JDBC
DB2
Driver
DB2
Network
MySQL
Driver
MySQL
6
Types of JDBC(Drivers)
• JDBC-ODBC Bridge
(Type 1= Bridge)
• Native-API partly Java Driver
(Type 2= Native)
• Net-Protocol All-Java Driver
(Type 3= Middleware )
• Native Protocol All-Java Driver
(Type 4= Pure)
7
Type 1 JDBC Driver
JDBC-ODBC Bridge driver
• ODBC API is written C language & makes use of pointers and
the constructs that java does not support, java program can not
directly communicate with ODBCAPI.
• The Type 1 driver translates all JDBC calls into ODBC calls
and sends them to the ODBC driver.
Type 1 JDBC Driver
Advantage
• Access to almost any database, since the
database's ODBC drivers are already
available.
Disadvantages
1. Since the Bridge driver is not written
fully in Java, Type 1 drivers are not
portable.
2. Due to bridge, slowest of all driver types.
3. Not good for the Web.
Type 2 JDBC Driver
Native-API/partly Java driver
• Converts JDBC calls into database-specific calls.
• This driver is specific to a particular database.
• Example: Oracle will have oracle native api.
Type 2 JDBC Driver
• Advantage
1. Better performance than the JDBC-ODBC Bridge
2. it uses Native api which is Database specific.
• Disadvantage
1. Native API must be installed in the Client System hence
cannot be used for the Internet.
2. It’s not written in Java Language which forms a portability issue.
3. If we change the Database we have to change the native api as it
is specific to a database
Type 3 JDBC Driver
All Java/Net-protocol driver
• Type 3 database requests are
passed through the network to the
middle-tier server.
• The middle-tier then translates
the request to the database.
Type 3 JDBC Driver
• Advantage
1. This driver is server-based, so there is no need for any vendor
database library to be present on client machines.
2. Fully written in Java and hence Portable.
3. It is suitable for the web.
4. The net protocol can be designed to make the client JDBC
driver very small and fast to load.
Disadvantage
• It requires another server application to install and maintain.
Type 4 JDBC Driver
Native-protocol/all-Java driver
The Type 4 uses java networking libraries to communicate
directly with the database server.
Type 4 JDBC Driver
• Advantage
1. 1. Completely written in Java to achieve platform independence
2. It is most suitable for the web.
3. Number of translation layers is very less i.e. they don't have to
translate database requests to ODBC or a native connectivity
interface or to pass the request on to another server.
Disadvantage
With type 4 drivers, the user needs a different driver for each
database.
Seven Basic Steps in Using JDBC
1. Load the driver
2. Define the Connection URL
3. Establish the Connection
4. Create a Statement object
5. Execute a query
6. Process the results
7. Close the connection
JDBC: Details of Process: Step 1
1. Loading the driver
public static Class forName (String className)
throws ClassNotFoundException
Example:
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e) { }
JDBC: Details of Process : Step 2
2. Define the Connection URL
String URL= "jdbc:odbc:test“ (test is DSN)
JDBC: Details of Process : Step 3
3. Establish the Connection
public static Connection getConnection (String url,
String user, String password) throws SQLException
Connection con =
DriverManager.getConnection(URL);
JDBC: Details of Process : Step 4
4.
Create a Statement
public Statement createStatement() throws
SQLException
Statement stat = con.createStatement();
JDBC: Details of Process : Step 5
5. Execute a Query
public ResultSet executeQuery (String sql) throws
SQLException
String query = "SELECT * FROM sometable";
ResultSet rs= stat.executeQuery(query);
JDBC: Details of Process : Step 6
6. Process the Result
while (rs.next())
{
s.o.p(rs.getString(2);
}
–
Values of second column are retrieved using getString()
method.
JDBC: Details of Process : Step 7
7. Close the Connection
con.close();
JDBC Class Usage
DriverManager
Driver
Connection
Statement
ResultSet
The Driver Manager
• The driver manager sits between the JDBC application and one
or more JDBC drivers.
• DriverManager establishes connection & returns connection
object.
• Connection con =
DriverManager.getConnection(URL);
Making a Connection
• There are several getConnection() methods on DriverManager with
different argument lists.
• One of them is:
static Connection getConnection(String url)
• If you are using a non-default DB account and password, use instead:
static Connection getConnection(String url,
String username, String password)
Statement
• A Statement object is used for executing a SQL
statement and obtaining the results produced by it.
Statement Methods
ResultSet executeQuery(String)
– Execute a SQL statement that returns a single ResultSet.
int executeUpdate(String)
– Execute a SQL INSERT, UPDATE or DELETE
statement.
– Returns the number of rows changed.
boolean execute(String)
– Execute a SQL statement that may return multiple
results.
ResultSet
• A ResultSet provides access to a table of data generated by
executing a Statement.
• Only one ResultSet per Statement can be 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 to the next row.
ResultSet Methods
• boolean next()
– activates the next row
– the first call to next() activates the first row
– returns false if there are no more rows
• void close()
– disposes of the ResultSet
ResultSet Methods
• Type getType(int columnIndex)
– returns the given field as the given type
– fields indexed starting at 1 (not 0)
• Type getType(String columnName)
– same, but uses name of field
• int findColumn(String columnName)
– looks up column index given column name
ResultSet Methods
• String getString(int columnIndex)
• boolean getBoolean(int columnIndex)
• byte getByte(int columnIndex)
• short getShort(int columnIndex)
• int getInt(int columnIndex)
• long getLong(int columnIndex)
• float getFloat(int columnIndex)
• double getDouble(int columnIndex)
ResultSet Methods
• String getString(String columnName)
• boolean getBoolean(String columnName)
• byte getByte(String columnName)
• short getShort(String columnName)
• int getInt(String columnName)
• long getLong(String columnName)
• float getFloat(String columnName)
• double getDouble(String columnName)
PreparedStatement
• It has set of methods that can be used for sending queries with
input parameters.
• PreparedStatement stat = con. PreparedStatement(“ Select
* from publication where pub_id=?”)
• The symbol “ ? ” is replaced by input parameter at run time.
Simple JDBC example
import java.sql.*;
public class JdbcExample
{
public static void main(String[] args)
{
int i;
Connection con = null;
// register jdbc driver
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(ClassNotFoundException e)
{
System.out.println(e);
}
// connect to DB
try{
con = DriverManager.getConnection("jdbc:odbc:my_database");
} catch(SQLException se) {
System.out.println(se);
}
System.out.println("connection is successful!!!");
try{
String selectSQL = "select ID, NAME, ADDRESS from
tb_address";
Statement stat = conn.createStatement();
ResultSet rs = stmt.executeQuery(selectSQL);
while(rs.next()){
System.out.println("ID: " + rs.getString(1) + " NAME: " +
rs.getString(2) + " ADDRESS:" +rs.getString(3));
}
stat.close();
} catch(SQLException se) {
System.out.println(se);
}
}
}