JDBC

1. JDBC • Java online Training By Garudatrainings Register for a Free DEMO www.garudatrainings.com garudatrainings@gmail.com • Phone: +1 508-841-6144

2. JDBC • JDBC • JDBC is an alternative to ODBC and ADO that provides database access to programs written in Java. • JDBC drivers are available for most DBMS products: http://java.sun.com/products/jdbc

3. JDBC • JDBC • Java API for connecting programs written in Java to the data in relational databases • The standard defined by Sun Microsystems, allowing individual providers to implement and extend the standard with their own JDBC drivers. • Tasks of JDBC: 1) establishes a connection with a database 2) sends SQL statements 3) processes the results

4. JDBC • JDBC • The JDBC API supports both two-tier and three-tier models for database access. • Two-tier model - a Java applet or application interacts directly with the database. • Three-tier model - introduces a middle-level server for execution of business logic: The middle tier to maintain control over data access. The user can employ an easy-to-use higher-level API which is translated by the middle tier into the appropriate low-level calls.

6. JDBC • JDBC provides API or Protocol to interactwith different databases. • With the help of JDBC driver we canconnect with different types of databases. • Driver is must needed for connectionestablishment with any database. • A driver works as an interface between theclient and a database server.

7. JDBC • JDBC have so many classes and interfaces that allow a Java application to send request made by user to any specific DBMS(Data Base Management System). • JDBC supports a wide level of portability. • JDBC provides interfaces that are compatible with java application

8. JDBC Components

9. Database Programming Steps • Establish a connection • Begin transaction • Create a statement object • Associate SQL with the statement object • Provide values for statement parameters • Execute the statement object • Process the results • End transaction • Release resources

10. Using JDBC (Continued) • Load the driver: • The driver class libraries need to be in the CLASSPATH for the Java compiler and for the Java virtual machine. • The most reliable way to load the driver into the program is: Class.forName(string).newInstance();

11. Using JDBC (Continued) • Establish a connection to the database: • A connection URL string includes the literal jdbc:, followed by the name of the driver and a URL to the database String url = "jdbc:oracle:thin:@localhost:1521:csodb"; jdbc “subprotocol”“subname” host port database • Create a Connection object: Connection con = DriverManager.getConnection(url, dbUser,dbPassword);

12. Using JDBC (Continued) • Begin the transaction con.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE ); con.setAutoCommit( false ); • Create a statement object Statement stmt = conn.createStatement(); • Associate SQL with the statement object String queryString = "create table students " + "(name varchar(30), id int, phone char(9))";

13. Using JDBC (Continued) • Process the statement: Example statements: ResultSet rs = stmt.executeQuery(querystring); Int result = stmt.executeUpdate(updatestring); ResultSetMetaData rsMeta = rs.getMetaData(); • Compiled queries can be processed via a PreparedStatement object • Stored procedures can be processed via a CallableStatement object

14. Using JDBC (Continued) • End transaction con.commit(); con.rollback(); • Release resources con.close();

15. JDBC Component Interaction Driver Manager Creates Creates Creates Connection Statement ResultSet SQL Driver Establish Link to DB Result (tuples) Database

16. Two-Tier Database Access Model • Java Application talks directly to the database • Accomplished through the JDBC driver which sends commands directly to the database • Results sent back directly to the application Application Space Java Application JDBC Driver SQL Command Result Set Database

17. Three-Tier Database Access Model • JDBC driver sends commands to a middle tier, which in turn sends commands to database. • Results are sent back to the middle tier, which communicates them back to the application Application Space Java Application JDBC Driver SQL Command Result Set Application Server (middle-tier) Proprietary Protocol Database

18. Type 1 Driver: JDBC-ODBC Bridge • The JDBC type 1 driver which is also known as a JDBC-ODBC Bridge is a convert JDBC methods into ODBC function calls. • Sun provides a JDBC-ODBC Bridge driver by "sun.jdbc.odbc.JdbcOdbcDriver".

19. Type 1 Driver: JDBC-ODBC Bridge • The driver is a platform dependent because it uses ODBC which is depends on native libraries of the operating system and also the driver needs otherinstallation for example, ODBC must be installed on the computer and the database must support ODBC Driver

20. Type 1 Driver: JDBC-ODBC Bridge • Type 1 is the simplest compare to all other driver but it's a platform specific i.e. only on Microsoft platform. • The JDBC-ODBC Bridge is use only when there is no PURE-JAVA driver available for a particular database.

21. Architecture Diagram

22. Type 1 Driver: JDBC-ODBC Bridge • Process: Java Application -> JDBC APIs JDBCDriver Manager —» Type 1 Driver —» ODBC Driver —> Database library APIs —> Database • Advantage: (1) Connect to almost any database on any system, for which ODBC driver is installed. (2) It's an easy for installation as well aseasy(simplest) to use as compare the all other driver.

23. Type 1 Driver: JDBC-ODBC Bridge • Disadvantage: (1) The ODBC Driver needs to be installed on the client machine. (2) It's a not a purely platform independentbecause its use ODBC which is depends on native libraries of the operating system on client machine. (3) Not suitable for applets because the ODBC driver needs to be installed on the client machine.

24. Type 1 Driver: JDBC-ODBC Bridge • JDBC:ODBC ( mainly for Desktop Applications) Use bridging technology Requires installation/configuration on client machines Not good for Web

25. Type 2 Driver: Native-API Driver (Partly Java driver) • The JDBC type 2 driver is uses the libraries of the database which is available at client side and this driver converts the JDBC method calls into native calls of the database so this driver is also known as aNative-API driver.

26. Architecture Diagram

27. Type 2 Driver: Native-API Driver (Partly Java driver) • Process: Java Application -> JDBC APIs JDBCDriver Manager —» Type 2 Driver —» VendorClient Database library APIs —> Database • Advantage: (1) There is no implantation of JDBC-ODBC Bridge so it's faster than a type 1 driver; hence the performance is better as compare the type 1 driver (JDBC-ODBC Bridge).

28. Type 2 Driver: Native-API Driver (Partly Java driver) • Disadvantage (1) On the client machine require the extrainstallation because this driver uses the vendor client libraries. (2) The Client side software needed so cannot use such type of driver in the web-based application. (3) Not all databases have the client sidelibrary. (4) This driver supports all JAVA applications • eXCept applets.

29. Type 2 Driver: Native-API Driver (Partly Java driver) • Native API Drivers (Vendor Specific drivers) Requires installation/configuration on client machines Used to leverage existing CLI libraries Usually not thread-safe Mostly obsolete now

30. Type 3 Driver: Network-Protocol Driver (Pure Java driver for database Middleware) • The JDBC type 3 driver uses the middletier(application server) between the calling program and the database and this middle tier converts JDBC method calls into the vendor specific database protocol and the same driver can be used for multipledatabases also so it's also known as a Network-Protocol driver as well as a JAVA driver for database middleware.

31. Architecture Diagram

32. Type 3 Driver: Network-Protocol Driver(Pure Java driver for database Middleware) • Process Java Application —> JDBC APIs -> JDBCDriver Manager —> Type 3 Driver —> Middleware(Server)—> any Database • Advantage: (1) There is no need for the vendor database library on the client machine because the middleware is database independent and it communicates withclient.

33. Type 3 Driver: Network-Protocol Driver(Pure Java driver for database Middleware) (2) Type 3 driver can be used in any webapplication as well as on internet also because there is no anv software require at client side. (3) A single driver can handle any database at client side so there is no need a Separate driver for each database. (4) The middleware server can also provide the typical services such as connections, auditing, load balancing, logging etc.

34. Type 3 Driver: Network-Protocol Driver(Pure Java driver for database Middleware) • Disadvantage: (1) An Extra layer added, may be timeconsuming. (2) At the middleware develop the databasespecific coding, may be increase complexity.

35. Type 3 Driver: Network-Protocol Driver(Pure Java driver for database Middleware) • Network API Calls middleware server, usually on database host Very flexible & allows access to multiple databases using one driver Only need to download one driver But it's another server application to install and maintain.

36. Type 4 Driver: Native-Protocol Driver (Pure Java driver directly connected to database) • The JDBC type 4 driver converts JDBC method calls directly into the vendor specific database protocol and in between do not need to be converted any other formatted system so this is the fastest way to communicate quires to DBMS and it is completely written in JAVA because of that this is also known as the "direct to database Pure JAVA driver".

37. Architecture Diagram

38. Type 4 Driver: Native-Protocol Driver (Pure Java driver directly connected to database) • Disadvantage: (1) There is a separate driver needed for each database at the client side. (2) Drivers are Database dependent, as different database vendors use different network protocols.

39. Type 4 Driver: Native-Protocol Driver (Pure Java driver directly connected to database) • Network Protocol Driver (used for Network based Applications) Pure Java Drivers Use Java networking libraries to talk directly to database engines need to download a new driver for each database engine.

40. All JDBC Types Diagram JDBC Type I “Bridge” ODBC ODBC Driver Type II “Native” CLI (.lib) Middleware Server Type III “Middleware” Type IV “Pure” Alex Chaffee

41. JDBC Steps DriverManager Driver Connection Statement ResultSet Garudatrainings.com +1 508-841-6144

42. JDBC URLs jdbc:subprotocol:source • each driver has its own subprotocol • each subprotocol has its own syntax for the source jdbc:odbc:DataSource • e.g. jdbc:odbc:Northwind jdbc:msql://host[:port]/database • e.g. jdbc:msql://foo.nowhere.com:4333/accounting Garudatrainings.com +1 508-841-6144

43. DriverManager Loads database drivers, and manages the connection between the application and the driver Connection getConnection (String url, String user, String password) • Connects to given JDBC URL with given user name and password • Throws java.sql.SQLException • returns a Connection object Garudatrainings.com +1 508-841-6144

44. Driver Translates API calls into operations for a specific data source Garudatrainings.com +1 508-841-6144

45. Connection • A Connection represents a session with a specific database or a session between an application and a database • Within the context of a Connection, SQL statements are executed and results are returned. • Can have multiple connections to a database • Also provides “metadata” -- information about the database, tables, and fields • Also methods to deal with transactions

46. Obtaining a Connection String url = "jdbc:odbc:Northwind"; try { Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con = DriverManager.getConnection(url); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } Garudatrainings.com +1 508-841-6144

47. Connection Methods Statement createStatement() • returns a new Statement object PreparedStatement prepareStatement(String sql) • returns a new PreparedStatement object CallableStatement prepareCall(String sql) • returns a new CallableStatement object • Why all these different kinds of statements? Optimization. Garudatrainings.com +1 508-841-6144

48. Statement • A Statement object is used for executing a static SQL statement and obtaining the results produced by it. • An SQL Statement to perform a query or update operation. Garudatrainings.com +1 508-841-6144

49. 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. • Why all these different kinds of queries? Optimization. Garudatrainings.com +1 508-841-6144

50. ResultSet • A ResultSet provides access to a table of data generated by executing a Statement. • Logical set of columns and rows returned by executing an SQL 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. • you can’t rewind