Database Connectivity For IST 410 Students only 1

Database Connectivity For IST 410 Students only 1

Objectives l l Overview of JDBC Executing selection and update queries Batched Transactions Meta-data For IST 410 Students only 2

Introduction l l l 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 For IST 410 Students only 3

Introduction l Two distinct tools are needed to connect to a database m m l Java can connect to three different types of databases m m m l l l JDBC - Java Database Connectivity drivers ANSI SQL support by the DBMS Relational 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 For IST 410 Students only 4

JDBC API l l l 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 For IST 410 Students only 5

JDBC API l l l JDBC API is defined in a package named java. sql package comprises a number of interfaces, classes, and exception types Interfaces m m m Array - Used to interact with the SQL array during a transaction Blob - Used to store/access Binary Large Objects Callable. Statement - Used to execute stored procedures Connection - Used to establish a connection session with a specific database Database. Meta. Data - Provides comprehensive information about the database as a whole Driver - Connection pipeline to a database For IST 410 Students only 6

JDBC API l List of interfaces (continued) m m m m m Prepared. Statement - A precompiled SQL statement object Ref - A reference value to a SQL structured type Result. Set - Table of data Result. Set. Meta. Data - 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 For IST 410 Students only 7

JDBC API l The JDBC classes are m m m Date - Wrapper object for SQL Date Driver. Manager - Manager of database connectivity drivers Driver. Property. Info - For advanced programming use only if need to interact with Driver exists Time - Wrapper class for SQL Timestamp - Wrapper class to identify SQL TIMESTAMP Types - Constants to identify generic SQL types For IST 410 Students only 8

JDBC API l JDBC Exception types are m m l l Batch. Update. Exception - thrown if error occurs during a batch update operation Data. Truncation - 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 For IST 410 Students only 9

JDBC Drivers l l l 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 m m 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 For IST 410 Students only 10

JDBC Drivers l Type 2 - A native-API, partly Java technology m m l Contains calls to native methods (may be in C or C++ ) developed by commercial DBMS vendors such as Oracle, DB 2 etc Usually delivers better performance and is needed in Client systems Type 3 - A net-protocol, fully Java technology-enabled m m m 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 For IST 410 Students only 11

JDBC Drivers l Type 4 - Drivers for databases that have built in networking capability m m l l l 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 For IST 410 Students only 12

JDBC Drivers For IST 410 Students only 13

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

Loading a Database Driver l l l A JDBC driver can be loaded by instantiating a driver object new sun. jdbc. odbc. Jdbc. Odbc. Driver(); Class. for. Name("sun. jdbc. odbc. Jdbc. Odbc. Driver"); The driver being loaded is sun. jdbc. odbc. Jdbc. Odbc. 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 For IST 410 Students only 15

Loading a Database Driver l l 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. Jdbc. Odbc. Driver: second. Driver l These drivers can then be loaded during run-time Properties p = new Properties(); File. Input. Stream fis = new File. Input. Stream(“jdbc. properties”); p. load(fis); String drivers = p. get. Property(“jdbc. drivers”); l The driver manager iterates through the list of drivers to select a driver that can interact with the chosen database For IST 410 Students only 16

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

Database Names l l Programs also need to access remote databases A complete URL for the database is needed for remote access jdbc: odbc: //www. myserver. com: 4000/Company. DB where m m 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 Company. DB For IST 410 Students only 18

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

Establishing a Connection Object l The Driver. Manager is called to establish a connection to the database String db. Name = "jdbc: odbc: Company. DB"; Connection con = Driver. Manager. get. Connection(db. Name, userid, password); l l l 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 Driver. Manager to look for the appropriate driver and establish a connection A SQLException error is thrown if the Driver. Manager fails to connect For IST 410 Students only 20

Creating a Statement Object l l l 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 Result. Set Only one Result. Set object can interact with a Statement object can be created by executing create. Statement method of the Connection object Statement stmt = con. create. Statement(); A number of methods exist to query Statement object For IST 410 Students only 21

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

Result. Set l Result. Set provides a number of methods through which the content of each returned record can be examined m m m Cursor positioning : absolute, aftter. Last, before. First, first, get. Row Extract columns - get. String, get. Double, get. Int, get. Date Manipulate rows - insert. Row, delete. Row, find. Column Test for things - is. Before. First, is. First Get metadata information - get. Meta. Data Move record by record - next(), previous() For IST 410 Students only 23

Executing a SQL Query l SQL queries are executed by embedding the SQL statement in a method of the statement object m m m Result. Set execute. Query(String selects) - Execute a SQL SELECT query int execute. Update(String action. Query) - Execute INSERT, DELETE and UPDATEs execute. Update can also execute other SQL DDL statements that do not return records For IST 410 Students only 24

Commiting to Changes l l 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 multiuser 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 For IST 410 Students only 25

Commiting to Changes l l 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. set. Auto. Commit(boolean) - Sets the automatic commit mode For IST 410 Students only 26

Basic Query Example l l We use a small database to show JDBC examples The tables are shown next EMPLOYEE For IST 410 Students only 27

Basic Query Example WORKS_ON For IST 410 Students only 28

SQL Basic Query Example // Extracts from Basic. Queries. java, please see the source for full code public class Basic. Queries { public static void main(String [] args) { Number. Format nf = Number. Format. get. Number. Instance(); nf. set. Maximum. Fraction. Digits(2); nf. set. Minimum. Fraction. Digits(2); Set up JDBC connection try { new sun. jdbc. odbc. Jdbc. Odbc. Driver(); String db. Name = "jdbc: odbc: Company. DB"; Connection con = Driver. Manager. get. Connection(db. Name, ""); con. set. Auto. Commit(true); Statement stmt = con. create. Statement(); For IST 410 Students only 29

SQL Basic Query Example // Query #1 - A simple SELECT query Result. Set rs = stmt. execute. Query( "SELECT fname, lname, dno from employee "+ "where dno = 5"); System. out. println("DNO Query completed, printing results"); while(rs. next()) { String fname = rs. get. String(1); String lname = rs. get. String(2); double dno = rs. get. Double("dno"); System. out. println(lname+", "+fname+" Dept: "+dno); } For IST 410 Students only 30

SQL Basic Query Example // Query #2 - A bit more involved SELECT query rs = stmt. execute. Query( "SELECT fname, lname, dno from employee "+ "where sex = 'M' and salary > 20000"); System. out. println("nn. Selected names Query completed, printing results"); while(rs. next()) { String fname = rs. get. String(1); String lname = rs. get. String(2); double dno = rs. get. Double("dno"); System. out. println(lname+", "+fname+" Dept: "+dno); } For IST 410 Students only 31

SQL Basic Query Example // Query #3 - an INSERT query, followed by verification System. out. println("nn. Executing Insert query"); stmt. execute. Update("INSERT into Works_on "+ "VALUES (123456789, 3, 20)"); System. out. println("nn. Verifying Insertion result"); rs = stmt. execute. Query( "SELECT * from works_on "+"where essn = 123456789"); while(rs. next()) { double essn = rs. get. Double(1); double pno = rs. get. Double(2); double hours = rs. get. Double(3); System. out. println("ESSN: "+(long)essn+", PNO: "+(int)pno+ " Hours: "+hours); } For IST 410 Students only 32

SQL Basic Query Example //Query #4 - a DELETE query System. out. println("nn. Executing a Delete query"); stmt. execute. Update("DELETE from Works_on "+ "WHERE essn = 123456789 and pno = 3"); //Query #5 - an UPDATE query and verification System. out. println("nn. Executing an UPDATE query"); stmt. execute. Update("UPDATE employee "+ "set salary = salary*1. 10 "+"WHERE fname = 'John'"); rs = stmt. execute. Query( "SELECT fname, lname, salary "+"from employee "+ "where fname = 'John'"); // rest of the code For IST 410 Students only 33

Batch Processing l l l Batch processing is a JDBC 2. 0 feature and needs a JDBC 2. 0 driver We can batch the queries and start a transaction We start with defining a Buffer to hold the query strings String sql. Line = ”. . . . ”; This buffer is then added to the batch using the add. Batch method of the Statement object stmt. add. Batch(sql. Line); We continue to add SQL statements as String arguments to the stmt object using add. Batch method Finally, stmt. execute. Batch() is executed to run the batch For IST 410 Students only 34

Batch Processing l l Since batch transaction can fail, it is generally a good idea to turn off the Auto. Commit A commit() statement can then be executed when the batch transaction successfully completes; otherwise rollback() undoes the effect of the transaction The execute. Batch 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 Batch. Trans. java is in the directory; parts of the file extracted in the several slides to highlight the important issues For IST 410 Students only 35

Batch Processing Batch. Trans. java For IST 410 Students only 36

Metadata l l l 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 For IST 410 Students only 37

Metadata l l l 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 Result. Set. Meta. Data - Finds metadata information from a result set Database. Meta. Data - Finds metadata information about the database as a whole l l The full example can be found in Meta. Test. java Extracts from this program are presented in the next few slides For IST 410 Students only 38

Metadata Result. Set rs = stmt. execute. Query ( "SELECT fname, lname, salary ” + "from employee ” + "where fname = 'John'"); Result. Set. Meta. Data rsm = rs. get. Meta. Data(); Full code in int col. Count = rsm. get. Column. Count(); Meta. Test. java String [] col. Names = new String[col. Count]; System. out. println("Number of columns retrieved = "+col. Count); System. out. println("Column specifications are: "); for (int i=0; i < col. Count; i++) { col. Names[i] = rsm. get. Column. Name(i+1); String col. Type = rsm. get. Column. Type. Name(i+1); int col. Size = rsm. get. Column. Display. Size(i+1); System. out. println("Name: "+col. Names[i]+" Type: "+col. Type+ " Size: "+col. Size); } For IST 410 Students only 39

Metadata // How many tables in the database System. out. println("Number of tables in the database"); Database. Meta. Data dmd = con. get. Meta. Data(); rs = dmd. get. Tables(null, new String[] {"TABLE"}); while (rs. next()) { System. out. println(rs. get. String(3)); } l Parameters of get. Tables m m 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. For IST 410 Students only 40