Advance Java Programming (CM5I)5.Interacting with-database

Advance Java Programming(22517) Miss.P.S.Dungarwal Lecturer in CM Department. SHHJB Polytechnic, Chandwad.

ODBC stands for Open Database Connectivity A standard or open application programming interface (API) for accessing a database. ODBC provides a C interface for database access on Windows environment. 2Miss.P.S.Dungarwal

JDBC stands for Java Database Connectivity. It is a standard Java API for connecting programs written in Java to the data in relational databases. JDBC works with Java on a variety of platforms, such as Windows, Mac OS, and the various versions of UNIX. 3Miss.P.S.Dungarwal

 JDBC Driver is a software component that enables java application to interact with the database. There are 4 types of JDBC drivers:  JDBC-ODBC bridge driver  Native-API driver (partially java driver)  JDBC-Net pure Java/ Network-Protocol driver (fully java driver)  Pure Java Driver /Thin driver / Database-Protocol driver(fully java driver) 4Miss.P.S.Dungarwal

 The JDBC type 1 driver, also known as the JDBC- ODBC bridge driver.  The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls. 5Miss.P.S.Dungarwal

 The JDBC type 2 driver, also known as the Native-API driver  The Native API driver uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API. It is not written entirely in java. 6Miss.P.S.Dungarwal

 The JDBC type 3 driver, also known as the Pure Java driver for database middleware. It is a database driver implementation which makes use of a middle tier between the calling program and the database.  The middle-tier (application server) converts JDBC calls directly or indirectly into a vendor-specific database protocol. It is fully written in java. 7Miss.P.S.Dungarwal

 The JDBC type 4 driver, also known as the Direct to Database Pure Java Driver, is a database driver implementation that converts JDBC calls directly into a vendor specific database protocol.  That is why it is known as thin driver. It is fully written in Java language. 8Miss.P.S.Dungarwal

 In a two-tier model, a Java application communicates directly with the database, via the JDBC driver. 9Miss.P.S.Dungarwal

 In a three-tier model, a Java application communicates with a middle tier component that functions as an application server. The application server talks to a given database using JDBC. 10Miss.P.S.Dungarwal

 The JDBC API provides the following interfaces and classes −  DriverManager Class  Driver Interface  Connection Interface  Statement Interface  ResultSet Interface 11Miss.P.S.Dungarwal

DriverManager Class  The DriverManager class acts as an interface between user and drivers.  It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver.  The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver(). 12Miss.P.S.Dungarwal

Method Description public static void registerDriver( Driver driver); is used to register the given driver with DriverManager. public static void deregisterDriver( Driver driver); is used to deregister the given driver (drop the driver from the list) with DriverManager. public static Connection getConnection ( String url); is used to establish the connection with the specified url. public static Connection getConnection( String url, String userName, String password); is used to establish the connection with the specified url, username and password. Commonly used methods of DriverManager class 13Miss.P.S.Dungarwal

 This interface handles the communications with the database server.  You will very rarely interact directly with Driver objects.  Instead, you use DriverManager objects, which manages objects of this type. 14Miss.P.S.Dungarwal

 A Connection is the session between java application and database.  The Connection interface provide many methods for transaction management like commit(), rollback() etc.  When getConnection() method is called, it returns a connection object. Connection con=DriverManager.getConnection(URL); 15Miss.P.S.Dungarwal

Commonly used methods of Connection interface Method Description public Statement createStatement(); creates a statement object that can be used to execute SQL queries. public void setAutoCommit(bool ean status); It is used to set the commit status. By default it is true. public void commit(); It saves the changes made since the previous commit/rollback permanent. public void rollback(); Drops all changes made since the previous commit/rollback. public void close(); closes the connection and Releases a JDBC resources immediately. 16Miss.P.S.Dungarwal

 The Statement interface provides methods to execute queries with the database.  It provides factory method to get the object of ResultSet. 17Miss.P.S.Dungarwal

Method Description public ResultSet executeQuery(String sql); used to execute SELECT query. It returns the object of ResultSet. public int executeUpdate(String sql); used to execute specified query, it may be create, drop, insert, update, delete etc. public boolean execute(String sql); used to execute queries that may return multiple results. public int[] executeBatch(); used to execute batch of commands. void close() Close the statement object Commonly used methods of Statement interface 18Miss.P.S.Dungarwal

 A ResultSet object provides access to a table of data.  ResultSet object is usually generated by executing a statement.  The object of ResultSet maintains a cursor pointing to a particular row of data.  Initially, cursor points before the first row. 19Miss.P.S.Dungarwal

Method Description public boolean next(); is used to move the cursor to the one row next from the current position. public boolean previous(); is used to move the cursor to the one row previous from the current position. public boolean first(); is used to move the cursor to the first row in result set object. public boolean last(); is used to move the cursor to the last row in result set object. public boolean absolute(int row); is used to move the cursor to the specified row number in the ResultSet object. public int getInt(int columnIndex); is used to return the data of specified column index of the current row as int. public int getInt(String columnName); is used to return the data of specified column name of the current row as int. public String getString(int columnIndex); is used to return the data of specified column index of the current row as String. public String getString(String columnName); is used to return the data of specified column name of the current row as String. Commonly used methods of ResultSet interface 20Miss.P.S.Dungarwal

 There are 5 steps to connect any java application with the database in java using JDBC. They are as follows: 1. Register the driver class 2. Creating connection 3. Creating statement 4. Executing queries 5. Closing connection 21Miss.P.S.Dungarwal

 The Class.forName() method is used to register the driver class. This method is used to dynamically load the driver class.  Syntax of forName() method public static void forName(String className)throws ClassNotFoun dException  Example to register with JDBC-ODBC Driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 22Miss.P.S.Dungarwal

2. Creating connection  The DriverManager.getConnection() method is used to establish connection with the database. Example establish connection with Oracle Driver Connection con = DriverManager.getConnection ("jdbc:odbc:DemoDB","username","password"); 23Miss.P.S.Dungarwal

3. Creating statement  The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database.  Example to create the statement object Statement stmt=con.createStatement(); 24Miss.P.S.Dungarwal

4. Executing queries  The executeQuery() method of Statement interface is used to execute queries to the database.  This method returns the object of ResultSet that can be used to get all the records of a table.  Example to execute query ResultSet rs=stmt.executeQuery("select * from emp" ); while(rs.next()) { System.out.println(rs.getInt(1)+" "+rs.getString( 2)); } 25Miss.P.S.Dungarwal

5. Closing connection  By closing connection object statement and ResultSet will be closed automatically.  The close() method of Connection interface is used to close the connection.  Example to close connection con.close(); 26Miss.P.S.Dungarwal

Example to Connect Java Application with mysql database 27Miss.P.S.Dungarwal

import java.sql.*; class MysqlCon{ public static void main(String args[]) { Try{ Class.forName("com.mysql.jdbc.Driver"); Connection con=DriverManager.getConnection("jdbc:mysql://localhost:33 06/Emp","root","root"); Statement stmt=con.createStatement(); ResultSet rs=stmt.executeQuery("select * from emp"); while(rs.next()) System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)); con.close(); } catch(Exception e) { System.out.println(e); } } 28Miss.P.S.Dungarwal

import java.sql.*; public class JdbcAccessTest { public static void main(String[] args) { String databaseURL = "jdbc:ucanaccess://e://Contacts.accdb"; try ( Connection connection = DriverManager.getConnection(databaseURL)) { String sql = "INSERT INTO Contacts (Full_Name, Email, Phone) VALUES (?, ?, ?)"; PreparedStatement preparedStatement = connection.prepareStatement(sql); preparedStatement.setString(1, "Rohit"); preparedStatement.setString(2, "rohit@mi.com"); preparedStatement.setString(3, "0919989998"); int row = preparedStatement.executeUpdate(); if (row > 0) { System.out.println("A row has been inserted successfully."); } 30Miss.P.S.Dungarwal

sql = "SELECT * FROM Contacts"; Statement statement = connection.createStatement(); ResultSet result = statement.executeQuery(sql); while (result.next()) { int id = result.getInt("Contact_ID"); String fullname = result.getString("Full_Name"); String email = result.getString("Email"); String phone = result.getString("Phone"); System.out.println(id + ", " + fullname + ", " + email + ", " + phone); } } catch (SQLException ex) { ex.printStackTrace(); } } } 31Miss.P.S.Dungarwal

Advance Java Programming (CM5I)5.Interacting with-database

In this document

More Related Content

What's hot

Similar to Advance Java Programming (CM5I)5.Interacting with-database

Recently uploaded

Advance Java Programming (CM5I)5.Interacting with-database