Advance Java: Database Access and JDBC Working

1. MCA-401: ADVANCE JAVA PREPARED BY : NAVEEN NAGPAL (SENIOR ASSISTANT PROFESSOR) DEPTT. OF COMP. SC & APPLICATIONS

2. Accessing a DataBase • Almost all the web applications that we see on the Internet access a database. • A specialized language called Structured Query Language (SQL) is used to access the data. SQL is an ANSI standard and is supported by all major database vendors. • To help programmers write code that's portable between database engines, the standard Java libraries include an API called the Java Database Connectivity (JDBC) API. JDBC defines a set of classes that can execute SQL statements the same way in any relational database. DEPTT. OF COMP. SC & APPLICATIONS

3. Accessing a Database from a JSP Page • JSP Standard Tag Library (JSTL) includes a number of actions for database access to make it easy to develop simple database-driven JSP applications. The actions provide the following features: • Using a connection pool for better performance and scalability Supporting queries, updates, and inserts. • Handling the most common data-type conversions. • Supporting a combination of database operations in one transaction DEPTT. OF COMP. SC & APPLICATIONS

4. The DataSource Interface and JDBC Drivers • JSP gets access to the database through an instance of a JDBC interface named javax.sql.DataSource. • The DataSource interface is part of the Java 2 Standard Edition (J2SE) 1.4, and for prior versions of the J2SE, it's available in the JDBC 2.0 Optional Package. To access a database, a connection to the database must first be established. Opening a database connection is very time-consuming. A nice thing with a DataSource is that it can represent something called a connection pool. • When a database action needs a connection, it gets it from the pool through the DataSourceobject and uses it to execute one or more SQL statements DEPTT. OF COMP. SC & APPLICATIONS

5. JDBC • In addition to the DataSource, the JDBC API contains other classes and interfaces that allow a Java application to process SQL statements in a database-independent way. For each specific database engine, an implementation of the interfaces defined by the JDBC API translates the generic calls to a format understood by the engine. This implementation is called a JDBC driver DEPTT. OF COMP. SC & APPLICATIONS

6. JDBC CONTI… • According to Sun Microsystems, JDBC is not an acronym. In particular, it does not stand for Java Database Connectivity. • JDBC is an application programming interface between Java programs and database management systems. Like Oracle’s Oracle Call Interface(OCI) or Microsoft’s Open Database Connectivity (ODBC), JDBC is a call-level interface. DEPTT. OF COMP. SC & APPLICATIONS

7. JDBC CONTI… • The great advantage of JDBC is it provides a standard interface to all database management systems. • JDBC queries that work on an Oracle database require little or no changes to work with DB2, or SQL Server, or any other database. • JDBC also eases the transition from legacy systems to Web-enabled applications. Embedded SQL products, which have been around since the early 1980s, for the most part use SQL statements and operations that can be duplicated by JDBC calls DEPTT. OF COMP. SC & APPLICATIONS

8. Basic JDBC Operations 1. Load a JDBC driver for your DBMS. This typically involves only a ClassforName() statement specifying the driver class name. 2. Use that driver to open a connection to a particular database. This is done with a call to a static getConnection(url) method to the DriverManager class. The url argument is in a specific form that indicates the driver type and the data source to use. 3. Issue SQL statements through the connection. Once the connection is established, it can be used to create Statement objects through which SQL commands can be made. 4. Process result sets returned by the SQL operations. The ResultSet interface provides methods to step through each row and get the values of each column. DEPTT. OF COMP. SC & APPLICATIONS

9. JDBC WORKING DEPTT. OF COMP. SC & APPLICATIONS

10. Essential JDBC Classes • The JDBC interface is contained in the java.sql and javax.sql packages. • It consists mainly of interfaces rather than concrete classes because each vendor’s implementation is specific to their particular database protocol • A smaller subset of these is more commonly used, as outlined in the following: DEPTT. OF COMP. SC & APPLICATIONS

11. Connection : An active link to a database through which a Java program can read and write data, as well as explore the database structure and capabilities. A Connection object is created either by a call to DriverManager.get Connection() or DataSource.getConnection(), in JDBC 2.0. DEPTT. OF COMP. SC & APPLICATIONS

12. Statement : An object that allows SQL statements to be sent through a connection and retrieves the result sets and update counts they produce. • ResultSet :An ordered set of table rows produced by an SQL query or a call to certain metadata functions. A ResultSet is most often encountered as the return value of a Statement.executeQuery(sqlstring)method call DEPTT. OF COMP. SC & APPLICATIONS

13. DriverManager : An interface that registers JDBC drivers and supplies connections that can handle specific JDBC URLs. The only method commonly used is the staticDriverManager.getConnection(),in one of its three forms, which returns an activeConnectionobject bound to the specified JDBC URL. DEPTT. OF COMP. SC & APPLICATIONS

14. SQLException : The base exception class used by the JDBC API.SQLExceptionhas methods that can supply theSQLStatevalue any vendor-specific error code. It can also be linked to anotherSQLException if more than one exception occurred. DEPTT. OF COMP. SC & APPLICATIONS

15. JDBC DRIVERS To insulate programs from the specifics of particular database protocols, JDBC uses a middle layer composed of DriverManager class and one or more JDBC drivers. A driver is Java class, usually supplied by the database vendor, which implements the java.sql.Driver interface. The primary function of the driver is to connect to a database and return a java.sql.Connection object. DEPTT. OF COMP. SC & APPLICATIONS

16. Driver Types • Type 1—JDBC-ODBC bridge Drivers of this type connect to databases through an intermediate ODBC driver. Several drawbacks are involved with this approach, so Sun describes it as being experimental and appropriate for use only where no other driver is available. Both Microsoft and Sun provide type 1 drivers. • Type 2—Native API, partly Java Similar to a JDBC-ODBC bridge, type 2 drivers use native methods to call vendor-specific API functions. These drivers are also subject to the same limitations as the JDBC-ODBC bridge, in that they require native library files to be installed on client systems, which must be configured to use them. • Type3—PureJava to database middleware Type 3 drivers communicate using a network protocol to a middleware server, which, in turn, communicates to one or more database management systems. • Type 4—Pure Java direct to database Drivers of this type call directly into the native protocol used by the database management system. DEPTT. OF COMP. SC & APPLICATIONS

17. DEPTT. OF COMP. SC & APPLICATIONS

18. JDBC-ODBC Bridge • the JDBC-ODBC bridge driver is limited to the capabilities of the underlying ODBC driver, which is single threaded and may, therefore, perform poorly under a heavy load. • It requires native code library JdbcOdbc.dll to be installed on the client system. • the JDBC-ODBC bridge driver requires an ODBC data source to be configured DEPTT. OF COMP. SC & APPLICATIONS