Database Connectivity

1. Database Connectivity

2. Objectives • Overview of JDBC • Executing selection and update queries • Batched Transactions • Meta-data

3. Introduction • Modern applications often connect to a database • With this need in view, Java provides the JDBC API through which an application program can establish database connectivity • The JDBC API supports ANSI SQL and provides a number of classes and interfaces through which SQL queries of various types can be executed from within a Java program • Writing a program that uses JDBC requires a working knowledge of a database management system and SQL • There are many excellent books available for those interested in a review of database technology

4. Introduction • Two distinct tools are needed to connect to a database • JDBC - Java Database Connectivity drivers • ANSI SQL support by the DBMS • Java can connect to three different types of databases • Relational Databases • Object Databases • Object-relational databases • Stored procedures can also be written using JDBC API • Stored procedures are precompiled SQL ‘code snippets’ stored on the database server • For our discussion, we focus only on the client side where a program issues a SQL query to a database and processes the result

5. JDBC API • JDBC is a SQL-level API • This API is used by java programs to send SQL statements to a DBMS; the result of the query is returned back to the program through the API • A JDBC driver is used as the pipeline through which queries are sent and results are received • The basic goal of Java developers, in designing JDBC technology, was to provide a bridge through which Java programs can interact with any SQL database • JDBC does build on successes of ODBC that defined industry-wide standards for accessing a database

6. JDBC API • JDBC API is defined in a package named java.sql • java.sql package comprises a number of interfaces, classes, and exception types • Interfaces • Array - Used to interact with the SQL array during a transaction • Blob - Used to store/access Binary Large Objects • CallableStatement - Used to execute stored procedures • Connection - Used to establish a connection session with a specific database • DatabaseMetaData - Provides comprehensive information about the database as a whole • Driver - Connection pipeline to a database

7. JDBC API • List of interfaces (continued) • PreparedStatement - A precompiled SQL statement object • Ref - A reference value to a SQL structured type • ResultSet - Table of data • ResultSetMetaData - Types of reference set can be determined • SQLData - Rarely called by programmer directly, used to custom map user-defined types • SQLInput - Never called by the programmer, used by the driver behind the scene • SQLOutput - Never called by the programmer, used by the driver • Statement - Object for executing a SQL statement • Struct - Values for SQL structured types

8. JDBC API • The JDBC classes are • Date - Wrapper object for SQL Date • DriverManager - Manager of database connectivity drivers • DriverPropertyInfo - For advanced programming use only if need to interact with Driver exists • Time - Wrapper class for SQL Time • Timestamp - Wrapper class to identify SQL TIMESTAMP • Types - Constants to identify generic SQL types

9. JDBC API • JDBC Exception types are • BatchUpdateException - thrown if error occurs during a batch update operation • DataTruncation - Data truncation warning • SQLException - Database access error • SQLWarning - Database access warning • All of these interfaces, classes and exceptions have many methods • A subset of JDBC API features are discussed in this module

10. JDBC Drivers • Key to establishing a database connection is the JDBC driver that knows about the database • These drivers can be classified into 4 types • Type 1 - JDBC-ODBC bridge • Uses ODBC to access a database. • Required in Client systems to be able to connect to a database • These drivers are generic in nature and therefore have some ‘give’ in performance; they have to be able to connect to different type of databases • Can be used with any database supported by ODBC

11. JDBC Drivers • Type 2 - A native-API, partly Java technology • Contains calls to native methods (may be in C or C++ ) developed by commercial DBMS vendors such as Oracle, DB2 etc • Usually delivers better performance and is needed in Client systems • Type 3 - A net-protocol, fully Java technology-enabled • Translates JDBC API calls to DBMS-independent net protocol • Java applications use sockets to connect to ‘middleware’ that translates the database request into API specific request at the server level • More flexible to use than other two since client machines do not require additional code support

12. JDBC Drivers • Type 4 - Drivers for databases that have built in networking capability • A native-protocol written fully in Java • Built in Java library translates requests to database-specific calls • Databases are called directly using Java sockets and usually propritary • Network protocol is built into the database engine • There is a very large number of drivers available today • Visit http://java.sun.com/products/jdbc/drivers.html • The next slide shows a partial list of the available drivers as of February, 2000

13. JDBC Drivers

14. Interacting with a Database • General steps for connecting and interacting with a database can be described as follows • Load a JDBC driver • Establish a connection object • Create a Statement object • If return table is expected, establish a Result object • Execute the query using statement object • Circle through the result table and use retrieved records in program processing steps • Specific application is likely to require variations to the above

15. Loading a Database Driver • A JDBC driver can be loaded by instantiating a driver object new sun.jdbc.odbc.JdbcOdbcDriver(); Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); • The driver being loaded is sun.jdbc.odbc.JdbcOdbc.Driver • A program can choose any of the above two alternatives • The second alternative provides better flexibility since the driver name can be supplied as a run time argument - it is a String • The name of the driver depends on the database being used

16. Loading a Database Driver • Java does not limit a program to one JDBC driver supplied at the run-time • A predefined set of drivers can be specified in a jdbc property file jdbc.drivers= sun.jdbc.odbc.JdbcOdbcDriver:secondDriver • These drivers can then be loaded during run-time Properties p = new Properties(); FileInputStream fis = new FileInputStream(“jdbc.properties”); p.load(fis); String drivers = p.getProperty(“jdbc.drivers”); • The driver manager iterates through the list of drivers to select a driver that can interact with the chosen database

17. Database Names • A database name is needed before a connection can be established • In WINTEL environment, a database name is created by using the 32bit ODBC connection interface accessible through the Control Panel • For example, a database name can be stated as jdbc:odbc:CompanyDB where a database named CompanyDB will be accessed using the type 1 driver: JDBC/ODBC bridge • The program, in above example, expects to find the CompanyDB in the host machine locally

18. Database Names • Programs also need to access remote databases • A complete URL for the database is needed for remote access jdbc:odbc://www.myserver.com:4000/CompanyDB where • driver used is jdbc/odbc bridge • the server has a URL of www.myserver.com • the database server is at the port number 4000 • the database name is CompanyDB

19. Database Names • The general syntax for remote access is jdbc:sub-protocol:database URL sub-protocol - specific to the driver used for the database database URL - the locator, recommended to be a standard URL format • For our discussion in this module, we will assume that we have set up a database name called CompanyDB in the local machine • Our database name is coded as a String "jdbc:odbc:CompanyDB"; • The database name needs to be modified for other connections

20. Establishing a Connection Object • The DriverManager is called to establish a connection to the database String dbName = "jdbc:odbc:CompanyDB"; Connection con = DriverManager.getConnection(dbName, userid, password); • The userid and password can be left as null strings if these attributes are unnecessary to log into the database server • It is the job of the DriverManager to look for the appropriate driver and establish a connection • A SQLException error is thrown if the DriverManager fails to connect

21. Creating a Statement Object • Statement is an Interface, i.e objects are created indirectly • Statement objects are used to execute SQL statements and retrieve results of the execution • Result is returned into an object of type ResultSet • Only one ResultSet object can interact with a Statement object • Statement object can be created by executing createStatement method of the Connection object Statement stmt = con.createStatement(); • A number of methods exist to query Statement object

22. ResultSet • ResultSet is an interface in the java.sql package • An object of type ResultSet receives the result, if any, of a query • Since it is an interfce, it is created indirectly by executing a method call for statement ResultSet rs = stmt.executeQuery( “SELECT fname, lname, dno from employee "+ "where dno = 5"); • There is one result set for a statement object

23. ResultSet • ResultSet provides a number of methods through which the content of each returned record can be examined • Cursor positioning : absolute, aftterLast, beforeFirst, first, getRow • Extract columns - getString, getDouble, getInt, getDate • Manipulate rows - insertRow, deleteRow, findColumn • Test for things - isBeforeFirst, isFirst • Get metadata information - getMetaData • Move record by record - next(), previous()

24. Executing a SQL Query • SQL queries are executed by embedding the SQL statement in a method of the statement object • ResultSet executeQuery(String selects) - Execute a SQL SELECT query • int executeUpdate(String actionQuery) - Execute INSERT, DELETE and UPDATEs • executeUpdate can also execute other SQL DDL statements that do not return records

25. Commiting to Changes • SELECT queries do not leave the database in an inconsistent state even though the retrieved result may not be the latest • However, update queries have the potential of not finishing or leaving the database inconsistent particularly in a multi-user environment • Database management systems provide the facility of Commit and Rollback to manage these possibilities • A commit request makes all changes made since the previous commit/rollback permanent and release any database locks currently held by the Connection

26. Commiting to Changes • Rollback, on the other hand, drops all changes made since the previous commit/rollback and release any database locks currently held by the Connection • One can also set the commit mode automatically and in that case, each individual SQL statement is executed as an independent transaction con.commit() - Commit the transaction con.rollback() - Undo changes made from last commit or rollback con.setAutoCommit(boolean) - Sets the automatic commit mode

27. Basic Query Example • We use a small database to show JDBC examples • The tables are shown next EMPLOYEE

28. Basic Query Example WORKS_ON

29. SQL Basic Query Example // Extracts fromBasicQueries.java, please see the source for full code public class BasicQueries { public static void main(String [] args) { NumberFormat nf = NumberFormat.getNumberInstance(); nf.setMaximumFractionDigits(2); nf.setMinimumFractionDigits(2); try { new sun.jdbc.odbc.JdbcOdbcDriver(); String dbName = "jdbc:odbc:CompanyDB"; Connection con = DriverManager.getConnection(dbName, "", ""); con.setAutoCommit(true); Statement stmt = con.createStatement(); Set up JDBC connection

30. SQL Basic Query Example // Query #1 - A simple SELECT query ResultSet rs = stmt.executeQuery( "SELECT fname, lname, dno from employee "+ "where dno = 5"); System.out.println("DNO Query completed, printing results"); while(rs.next()) { String fname = rs.getString(1); String lname = rs.getString(2); double dno = rs.getDouble("dno"); System.out.println(lname+", "+fname+" Dept: "+dno); }

31. SQL Basic Query Example // Query #2 - A bit more involved SELECT query rs = stmt.executeQuery( "SELECT fname, lname, dno from employee "+ "where sex = 'M' and salary > 20000"); System.out.println("\n\nSelected names Query completed, printing results"); while(rs.next()) { String fname = rs.getString(1); String lname = rs.getString(2); double dno = rs.getDouble("dno"); System.out.println(lname+", "+fname+" Dept: "+dno); }

32. SQL Basic Query Example // Query #3 - an INSERT query, followed by verification System.out.println("\n\nExecuting Insert query"); stmt.executeUpdate("INSERT into Works_on "+ "VALUES (123456789, 3, 20)"); System.out.println("\n\nVerifying Insertion result"); rs = stmt.executeQuery( "SELECT * from works_on "+"where essn = 123456789"); while(rs.next()) { double essn = rs.getDouble(1); double pno = rs.getDouble(2); double hours = rs.getDouble(3); System.out.println("ESSN: "+(long)essn+", PNO: "+(int)pno+ " Hours: "+hours); }

33. SQL Basic Query Example //Query #4 - a DELETE query System.out.println("\n\nExecuting a Delete query"); stmt.executeUpdate("DELETE from Works_on "+ "WHERE essn = 123456789 and pno = 3"); //Query #5 - an UPDATE query and verification System.out.println("\n\nExecuting an UPDATE query"); stmt.executeUpdate("UPDATE employee "+ "set salary = salary*1.10 "+"WHERE fname = 'John'"); rs = stmt.executeQuery( "SELECT fname, lname, salary "+"from employee "+ "where fname = 'John'"); // rest of the code

34. Batch Processing • Batch processing is a JDBC2.0 feature and needs a JDBC2.0 driver • We can batch the queries and start a transaction • We start with defining a Buffer to hold the query strings String sqlLine = ”.......”; • This buffer is then added to the batch using the addBatch method of the Statement object stmt.addBatch(sqlLine); • We continue to add SQL statements as String arguments to the stmt object using addBatch method • Finally, stmt.executeBatch() is executed to run the batch

35. Batch Processing • Since batch transaction can fail, it is generally a good idea to turn off the AutoCommit • A commit() statement can then be executed when the batch transaction successfully completes;otherwise rollback() undoes the effect of the transaction • The executeBatch method returns an array of integers, each element of the array has a value equal to number of elements used in the update • A complete example BatchTrans.java is in the directory; parts of the file extracted in the several slides to highlight the important issues

36. Batch Processing BatchTrans.java

37. Metadata • Normally, the user of a database would know names of tables, their attributes and data types • There are cases where this information may be unknown, or a highly portable program is desired where the program cannot be bound to fixed table and column names • Java provides a range of JDBC classes that allow the user to query a database about its type, name etc. • Metadata is ‘data about data’ • In the case of JDBC, metadata translates to information about the system properties of the database

38. Metadata • The details of metadata classes are too voluminous and too specialized to merit a complete discussion here • An example is presented to introduce the reader to metadata handling using JDBC • Specifically, the example presented uses 2 classes ResultSetMetaData - Finds metadata information from a result set DatabaseMetaData - Finds metadata information about the database as a whole • The full example can be found in MetaTest.java • Extracts from this program are presented in the next few slides

39. Metadata ResultSet rs = stmt.executeQuery ( "SELECT fname, lname, salary ” + "from employee ” + "where fname = 'John'"); ResultSetMetaData rsm = rs.getMetaData(); int colCount = rsm.getColumnCount(); String [] colNames = new String[colCount]; System.out.println("Number of columns retrieved = "+colCount); System.out.println("Column specifications are:"); for (int i=0; i < colCount; i++) { colNames[i] = rsm.getColumnName(i+1); String colType = rsm.getColumnTypeName(i+1); int colSize = rsm.getColumnDisplaySize(i+1); System.out.println("Name: "+colNames[i]+" Type: "+colType+ " Size: "+colSize); } Full code in MetaTest.java

40. Metadata // How many tables in the database System.out.println("Number of tables in the database"); DatabaseMetaData dmd = con.getMetaData(); rs = dmd.getTables(null,null,null,new String[] {"TABLE"}); while (rs.next()) { System.out.println(rs.getString(3)); } • Parameters of getTables • 1. Represents a table catalog - may be null • 2. Represents a table schema • 3. Represents a table name • 4. Represents a table type string with values such as TABLE, VIEW, SYSTEM TABLE etc.