1 / 33

Java Data Base Connectivity Lecturer Dr Pavle Mogin

Understand motives, architecture, classes, and interfaces of JDBC. Discover embedding SQL in GPPL, driver management, connections, reading resources. Learn about JDBC Application, Driver Manager, Driver, and JDBC Classes and Interfaces.

deannak
Download Presentation

Java Data Base Connectivity Lecturer Dr Pavle Mogin

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related