Java Data Base Connectivity Lecturer Dr Pavle Mogin

1. Java Data Base ConnectivityLecturer Dr Pavle Mogin

2. Plan for Java Database Connectivity (JDBC) • Motives for using JDBC • Architecture • JDBC Classes and Interfaces • JDBC Driver Management • Connections • Executing SQL statements • Result Set • Matching Data Types • Exceptions • Reading: • The JavaTM Tutorial: http://www.mcs.vuw.ac.nz/technical/java/tutorial/index.html

3. Motives for Using JDBC • Real database applications in industry often need DML (Data Manipulation Language) features of SQL and a greater flexibility of a general purpose programming language (GPPL), also called ‘host’ language. • SQL is needed because of it’s: • Simplicity and • Efficiency of data manipulation • A GPPL is needed for it’s support of: • Complex computation, • Complex structures of large programs, • GUI, and • Integration with other applications

4. Embedding SQL into a GPPL • A database application is written in GPPL with SQL statements embedded in it. • When an application establishes a connection with a database, SQL statements are used to retrieve data and manipulate the database. • But SQL queries retrieve sets and multisets of tuples, while GPPL often do not support set and multiset data types. • This mismatch is resolved by providing a handle on the set. Application iterates over the set using the handle.

5. Different SQL Embedding Techniques • Techniques that provide for source code to DBMS independence only: • Embedded SQL (static SQL statements), • Dynamic SQL (allows creating SQL statements at the run time), • Techniques that provide for both source and object code to DBMS independence: • ODBC: Open Data Base Connectivity (C++) • JDBC: Java Data Base Connectivity

6. O & J Database Connectivity • The goal of ODBC and JDBC software systems is to allow connecting an application program to almost any database system • Applications using ODBC or JDBC are DBMS independent on both source code and executable code level (providing that the SQL standard is satisfied) • This independence is achieved by introducing an extra level of indirection • All program interactions with a specific DBMS are accomplished through a DBMS specific driver

7. O & J DBC Application Program Driver Manager O or J DBC Call (SQL) Driver (Informix ) Select a Driver Driver SQL translated Into DBMS specific commands Driver (SQL Server ) Driver (Oracle ) Driver Loading … Driver (PostgreSQL) SQL Server IDS Oracle PostgreSQL …

8. JDBC Architecture • An application (program) • A driver manager • Several database specific drivers, and • Database systems

9. 1. JDBC Application • An application program: • Dynamically loads the drivers needed, • Initiates a connection with a database, • Sets transaction boundaries (BEGIN,…, {COMMIT | ROLLBACK}), • Acquires locks, • Submits SQL statements, • Receives data, • Process data, • Process error messages, • Decides whether to commit or roll-back a transaction, and • Disconnects from the database to terminate a session

10. 2. JDBC Driver Manager • The driver manager: • Loads the drivers needed, • Passes JDBC function calls from the application to the correct driver, • Handles JDBC initialization from the application, and • Performs some rudimentary error checking

11. 3. JDBC Driver and the DBMS • A database specific driver: • Establishes connection with a database, • Submits data manipulation requests, • Accepts returning results, • Translates DBMS specific data types into Java data types, • Translates error messages • A DBMS: • Processes data manipulation commands and • Returns results

12. JDBC Classes and Interfaces • JDBC is a collection of Java classes and interfaces • It contains methods for: • Connecting to a remote database, • Executing SQL statements, • Iterating over sets of results from SQL statements, • Transaction management, and • Exception handling • All these are contained in the java.sql package

13. JDBC DriverManagemer class • The package java.sql contains the DriverManager class • The DriverManager class has methods: • registerDriver(), • deregisterDriver() to enable dynamic addition and deletion of drivers • The 1st step in connecting to a database is to load an appropriate JDBC driver.

14. Registering a Driver • The method public static native Class.forName(String s) throws ClassNotFoundException in thejava.lang.Classclass will, forname="driver_name"return the requested Driverclass end execute its static constructor. • This Driverobject will register itself with the DriverManagerclass • Example: A PostgreSQL driver is loaded using: Class.forName("org.postgresql.Driver");

15. Establishing a Connection • A session with a database is started through creation of a Connection object Connection con = DriverManager.getConnection(url, userId, password); where url is a object of URL class.

16. The url Argument of getConnection() • Theurlargument is a URL of the format jdbc:[drivertype]:[database], • jdbc is a constant, • [drivertype]is the type of the database we want to connect (e.g. postrgresql), and • [database]is the address of the actual database of the form //hostname[:portnumber]/database_name • Finally, a possible URL would be String s = "jdbc:postgresql://db.mcs.vuw.ac.nz/” + userId + "_jdbc";

17. Connection Interface • The interfacejava.sql.Connectionhas a number of classes and methods that are used: • To control transactional behavior of a Connection object, • To create and execute SQL statements, • To iterate over the result returned by a DBMS, and • To finish interaction with a database by closing the connection • After acquiring a connection (with the name say con) and before it is closed, the same connection can be used for executing several transactions

18. Controlling Transaction Behavior-Start • By default, aConnectionautomatically commitschanges after executing each SQL statement • The method public abstract void setAutoCommit( boolean autoCommit) throws SQLException is applied onto a Connection object to designate the start of a transaction (BEGIN point) by assigning a value false to autoCommit: con.setAutoCommit(false); A commit operation makes permanent all changes made under commitment control since the previous commit or rollback operation. A rollback operation removes all changes made since the previous commit or rollback operation.

19. Controlling Transaction Behavior-End • A transaction is terminated using: • Either public abstract void commit() throws SQLException or public abstract void rollback() throws SQLException and (after any of them) con.setAutoCommit(true) on theConnectionobject

20. Executing SQL Statements • JDBC supports three different ways of executing SQL statements: • Statement, • PreparedStatement, and • CallableStatement

21. Statement Class and It’s Subclasses • The Statement class is the base class of the three classes used to submit queries to a DBMS • Its objects are used to forward to a DBMS SQL queries that are executed without repetition within a transaction and that have no parameters, • The PreparedStatementobjects are used for SQL statements with parameters or for those that are executed multiple times (in a loop) • SQL statements of PreparedStatement objects may be precompiled yielding better performance • CallableStatement objects are used with stored procedures and are out of the scope of the course

22. Submitting a SQL Query to a DBMS • The following steps should be performed in order to submit a SQL statement to a DBMS either using a Statement (S) or PreparedStatement (PS) object: • Define a SQL query as a String • Create a S or PS object If the SQL statement is one of CREATE, INSERT, DELETE, UPDATE, or SET type: • Apply executeUpdate() method onto a S or PS object Else (the SQL statement is of the SELECT type): • Create a ResultSet object • Feed into the ResultSet object the return value of applying executeQuery() method onto S or PS object

23. Statement Objects With executeUpdate String sql="INSERT INTO Grades " + "VALUES (007007,’C305’,’A+’)"; Statement stmt = con.createStatement(); int return_value = stmt.executeUpdate(sql); • ForINSERT, DELETE, and UPDATEqueries, the return value will be the number of tuples affected • For CREATE or SET, the return value should be 0

24. ResultSet Object • The executeQuery() method returns an object of the type set (or superset) • This set object should be assigned to an object of the ResultSet class • The ResultSet class has the next() method that allows traversing the set in a tuple at a time fashion • Initially, the ResultSet object is positioned before the first tuple of the result • The method next() returns true if there is a next tuple in the result, otherwise false • After executing next(), the ResultSet object contains the pointer to the current tuple

25. Statement Objects With executeQuery String sql="SELECT * FROM Grades" + "WHERE StudentId=007007"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()){ // extracting data from rs tuples // data processing }

26. Extracting Data from the Result • To match Java and database data types, JDBC specifies mappings and provides accessor methods in the ResultSet class ... ResultSet rs = stmt.executeQuery(sql); int j_studId; String j_courseId; String j_grade; while (rs.next()){ j_studId=rs.getInt("StudentId"); j_courseId=rs.getString("CourseId"); j_grade=rs.getString(3) // 3 is the column number in the result }

27. PreparedStatement With executeUpdate String sql="INSERT INTO Grades VALUES (?,?,?)"; PreparedStatement prstmt = con.prepareStatement(sql); boolean end=false; while(!end){ // suppose j_studId, j_courseId, j_grade, and end are // dynamically initialized to desired values prstmt.setInt(1, j_studId); prstmt.setString(2, j_courseId); prstmt.setString(3, j_grade); int return_value = prstmt.exectuteUpdate();}

28. (PreparedStatement with executeQuery) String select= "SELECT * FROM Grades WHERE StudentId = ?"; PreparedStatement prstmt = con.prepareStatement(select); // suppose j_studId is initialized on the // desired value prstmt.setInt(1, j_studId); ResultSet rs = prstmt.exectuteQuery(); while(rs.next()){ }

29. Finishing a Transaction • Suppose a transaction is initiated by a con.setAutoCommit(false); command • If it finishes successfully, then public abstract void commit() throws SQLException method should be applied on the Connection object • Otherwise public abstract void rollback() throws SQLException • After any of con.commit() and con.rollback(), you issue con.setAutoCommit(true);

30. Closing a Connection • Before exiting from an application program all connections acquired should be closed by applying public abstract void close() throws SQLException method on each of them

31. Exceptions • Most of the methods in java.sqlcan throw an exception of the type SQLException if an error occurs • In addition to inhereted getMessage() method, SQLException class has two additional methods for providing error information: • public String getSQLState() that returns an SQL state identifier according to SQL:1999 standard, and • public int getErrorCode() that retrieves a vendor specific error code • Each JDBC method that throws an exception has to be placed inside a try block followed by a catch block

32. Code to See Exceptions try{ /* Code that could generate an exception goes here. If an exception is generated, the catch block below will print out information about it*/ } catch (SQLException ex){ System.println(ex.getMessage()); System.println(ex.getSQLState()); System.println(ex.getErrorCode()); }

33. Summary • JDBC architecture: • An application program, • A Driver Manager, • Database specific Drivers, and • Databases • Scenario of a JDBC session: • Drivers needed are dynamically loaded, • Connections with databases are established, • Each connection has transaction semantics, • Transactions are executed by: • Submitting SQL statements, • Retrieving results, • Processing errors, • Committing or roll-backing transactions, and • Disconnecting from databases to terminate interaction