Java Database Connectivity JDBC Representation and Management of

Java Database Connectivity: JDBC Representation and Management of Data on the Internet

Database Connectivity �Relational databases ◦ Manage all information generated by businesses ◦ Centric to business applications ◦ Established and Mature �Application developers require standard interface to relational databases ◦ Change in database vendor should not require change in application programs

Relational Database Properties � Fundamental Properties ◦ Tables, Indexes, Triggers, Stored Procedures, Transactions ◦ ACID Properties – Atomicity, Consistency, Isolation, Durability � Data Definition (DDL) ◦ Manage Database Objects ◦ For example, Create and delete tables, indexes, stored procs � Queries (DML) ◦ Read, write, or delete information between one or more related tables ◦ SQL

ACID Properties � Atomic ◦ Updates in a transaction either occur or don’t occur � Consistent ◦ Transaction always preserves the system state � Isolated ◦ Concurrent transactions occur as if occurring in isolation (there is no partial view of another transaction) � Durable ◦ Committed transactions survive failures of any type (e. g. , communication, server). Transaction logging allows recovery.

SQL Statements (DML) �Insert INSERT into Course(Course. ID, Name, Professor. ID) values (162, ‘Java 101’, 12); �Update �UPDATE Course set Num. Students = 0; �Delete �DELETE from Course where (Professor. ID = 100 ); �Select �SELECT Name from Course where Course. ID = 162;

Relational DBs and Objects �Persistent objects must be mapped to relation tables Class Diagram Relation Tables Professor Prof. ID Name Student 0. . * teaches Professor 1 takes Course 1. . * 0. . * prereq Course. ID Prof. ID Name Location Time Student. ID Name Billing. Info 0. . * Prereq Course. ID Prereq. Course. ID Takes Student. ID Course. ID

What is JDBC? � A pure Java API for database communication similar to ODBC (JDBC created in 1995) ◦ JDBC and ODBC are based on the same standard: X/Open's SQL Call-Level Interface � A set of classes that perform database transactions ◦ Connect to relational databases ◦ Send SQL commands ◦ Process results � JDBC 2 has been defined (part of Java 2 in 1998) ◦ NOT universally supported ◦ Additional features such as scrollable cursors, batch updates

Benefits of JDBC �No proprietary DB code (although pass-through is supported) �Don’t have to rely on single vendor. . . ever �Don’t need to make DB vendor decision early �Easier for DB vendors ◦ Don’t need to provide a query language, only implement API ◦ Only low-level support

JDBC Architecture Java Application JDBC API JDBC Driver Manager JDBC Driver API (T 1) JDBCODBC Bridge ODBC Driver (T 2) Java Portion Native Portion (T 3) Java Client Server Component Proprietary, vendor-specific database access protocol (T 4) Java Driver to vendor's protocol JDBC Drivers Provided with Sun's JDK

JDBC Classes �java. sql. * provides classes used by an application <<Interface>> Statement <<Singleton>> Driver. Manager <<Interface>> Connection <<Interface>> Prepared. Statement <<Interface>> Result. Set <<Interface>> Database. Meta. Data <<Interface>> Callable. Statement <<Interface>> Result. Set. Meta. Data

JDBC Statement Types (2) � Callable Statements ◦ Vendor specific invocation of stored procedures String sql = “begin ? : = Class. register. Student(? , ? , ? ); Callable. Statement stmt = connection. prepare. Call(sql); stmt. register. Out. Parameter(1, Types. Integer); stmt. set. Int(2, student. ID); stmt. set. String(3, class. List); stmt. register. Out. Parameter(3, Oracle. Types. CURSOR); stmt. register. Out. Parameter(4, Oracle. Types. CURSOR); stmt. execute(); int num. Classes = stmt. get. Int(1); Result. Set class. List = stmt. get. Object(3); Result. Set unavailable. Class. List = stmt. get. Object(4); … class. List. close(); unavailable. Class. List. close(); stmt. close(); - Close ALL result sets !!!

Result Sets � Contain rows from query results ◦ Result. Set maintains the current row ◦ Can obtain an item from any column in the current row ◦ Can move forward, not backward � Greatly enhanced in JDBC 2. 0 (Java 2) ◦ Scrollable - move forward and backward ◦ Updatable - write through the Result. Set to the database ◦ Batch Updates - submit multiple update statements for performance improvement ◦ Row. Sets - disconnect then reconnect to Database using optimistic concurrency control ◦ Others Features - Connection Pooling, JNDI Naming

Statement SQL �Insert �String insert. Stmt = "INSERT into Course(Course. ID, Name, Professor. ID) " +"values (162, ‘Java 101’, 12)"; �stmt. execute. Update(insert. Stmt); �Update �String deposit. Stmt = "UPDATE Course set Num. Students = 0"; �stmt. execute. Update(deposit. Stmt); �Delete �String delete. Stmt = "DELETE from Course where (Professor. ID = " + terminated. Professor + ”)"; �stmt. execute. Update(delete. Stmt);

Example - Schema Assume the tables shown below. Write a program that displays all the students and the courses they take.

JDBC Summary � Three Statement Types ◦ Statement – simplest, slowest (for repeated calls), Difficult handling of quotes ◦ Prepared. Statement – Allows DB to cache parsed SQL ◦ Callable. Statement – Executes DB stored Procedure � Basic Steps ◦ Load Driver(s) (typically with Class. for. Name) ◦ Obtain a connection (Driver. Manager. get. Connection()) ◦ Get Statement from connection �connection. create. Statement - Statement �connection. prepare. Statement - Prepared. Statement �connection. prepare. Call - Callable. Statement ◦ stmt. execute. Query , stmt. execute. Update, or stmt. execute

What is ODBC? � ODBC is (Open Database Connectivity): � A standard or open application programming interface (API) for accessing a database. � SQL Access Group, chiefly Microsoft, in 1992 � By using ODBC statements in a program, you can access files in a number of different databases, including Access, d. Base, DB 2, Excel, and Text. � It allows programs to use SQL requests that will access databases without having to know the proprietary interfaces to the databases. � ODBC handles the SQL request and converts it into a request the individual database system understands.

More on ODBC �You need: ◦ the ODBC software, and ◦ a separate module or driver for each database to be accessed. Library that is dynamically connected to the application. �Driver masks the heterogeneity of DBMS operating system and network protocol. �E. g. (Sybase, Windows/NT, Novell driver)

ODBC Architecture Application ODBC driver manager Driver (DBMS/OS/network) Data Source

JDBC Architecture Oracle Driver Oracle Java Application JDBC DB 2 Driver DB 2 Network Postgres Driver Postgres

JDBC Architecture (cont. ) Application JDBC Driver �Java code calls JDBC library �JDBC loads a driver �Driver talks to a particular database �Can have more than one driver -> more than one database �Ideal: can change database engines without changing any application code

Introduction to JDBC �JDBC is used for accessing databases from Java applications �Information is transferred from relations to objects and vice-versa ◦ databases optimized for searching/indexing ◦ objects optimized for engineering/flexibility

What is JDBC? � JDBC is: Java Database Connectivity ◦ is a Java API for connecting programs written in Java to the data in relational databases. ◦ consists of a set of classes and interfaces written in the Java programming language. ◦ provides a standard API for tool/database developers and makes it possible to write database applications using a pure Java API. ◦ The standard defined by Sun Microsystems, allowing individual providers to implement and extend the standard with their own JDBC drivers. � JDBC: ◦ establishes a connection with a database ◦ sends SQL statements ◦ processes the results.

JDBC vs ODBC �ODBC is used between applications �JDBC is used by Java programmers to connect to databases �With a small "bridge" program, you can use the JDBC interface to access ODBC-accessible databases. �JDBC allows SQL-based database access for EJB persistence and for direct manipulation from CORBA, DJB or other server objects

JDBC API �The JDBC API supports both two-tier and three-tier models for database access. �Two-tier model -- a Java applet or application interacts directly with the database. �Three-tier model -- introduces a middle-level server for execution of business logic: ◦ the middle tier to maintain control over data access. ◦ the user can employ an easy-to-use higher-level API which is translated by the middle tier into the appropriate low-level calls.

JDBC Architectures Java Application JDBC driver manager JDBC/native bridge JDBC/ODBC bridge Native driver (DBMS specific) ODBC Driver JDBC Driver (DBMS Specific) DBMS JDBC middleware (various DBMS)

The JDBC Steps 1. Importing Packages 2. Registering the JDBC Drivers 3. Opening a Connection to a Database 4. Creating a Statement Object 5. Executing a Query and Returning a Result Set Object 6. Processing the Result Set 7. Closing the Result Set and Statement Objects 8. Closing the Connection

Loading the Driver �We can register the Driver indirectly using the Java statement: Class. for. Name(“oracle. jdbc. driver. Oracle. Driver"); �Calling Class. for. Name causes the Driver class to be loaded �When this class is loaded, it automatically ◦ creates an instance of itself ◦ registers this instance with the Driver. Manager

Another Option �Another option is to create an instance of the driver and register it with the Driver Manager: Driver driver = new oracle. jdbc. Oracle. Driver(); Driver. Manager. register. Driver(driver);

An Example // A driver for imaginary 1 Class. for. Name("ORG. img. SQL 1. imaginary 1 Driver"); // A driver for imaginary 2 Driver driver = new ORG. img. SQL 2. imaginary 2 Driver(); Driver. Manager. register. Driver(driver); //A driver for oracle Class. for. Name("oracle. jdbc. driver. Oracle. Driver"); imaginary 1 imaginary 2 Oracle Registered Drivers

1: Importing Packages // // Program name: Lec. Example_1 a. java // Purpose: Basic selection using prepared statement // //Import packages import java. sql. *; //JDBC packages import java. math. *; import java. io. *; import oracle. jdbc. driver. *;

2: Registering JDBC Drivers class Lec. Example_1 a { public static void main (String args []) throws SQLException { // Load Oracle driver Driver. Manager. register. Driver (new oracle. jdbc. driver. Oracle. Driver());

Connecting to the Database Every database is identified by a URL � Given a URL, Driver. Manager is asked to find the driver that can talk to the corresponding database � Driver. Manager tries all registered drivers, until a suitable one is found �

Connecting to the Database Connection con = Driver. Manager. get. Connection("jdbc. imaginary. DB 1"); accepts. URL(“jdbc. imaginary. DB 1”)? a r imaginary 1 imaginary 2 r Oracle Registered Drivers Read more in Driver. Manager API

3: Opening connection to a Database //Prompt user for username and password String user; String password; user = read. Entry("username: "); password = read. Entry("password: "); // Connect to the local database Connection conn = Driver. Manager. get. Connection ("jdbc: oracle: thin: @aardvark: 1526: teach ", user, password);

Interaction with the Database �We use Statement objects in order to ◦ Extract data from the database ◦ Update the database �Three different interfaces are used: Statement, Prepared. Statement, Callable. Statement �All are interfaces, thus cannot be instantiated �They are created by the Connection

Querying with Statement String query. Str = "SELECT * FROM Member " + "WHERE Lower(Name) = 'harry potter'"; Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query(query. Str); • The execute. Query method returns a Result. Set object representing the query result. • Will be discussed later…

Changing DB with Statement String delete. Str = “DELETE FROM Member " + "WHERE Lower(Name) = ‘harry potter’"; Statement stmt = con. create. Statement(); int delnum = stmt. execute. Update(delete. Str); n n execute. Update is used for data manipulation: insert, delete, update, create table, etc. (anything other than querying!) execute. Update returns the number of rows modified

4. Creating a Statement Object // Query the hotels table for resort = 'palma nova’ // Please notice the essential trim Prepared. Statement pstmt = conn. prepare. Statement ("SELECT hotelname, rating FROM hotels WHERE trim(resort) = ? "); pstmt. set. String(1, "palma nova");

About Prepared Statements �Prepared Statements are used for queries that are executed many times �They are parsed (compiled) by the DBMS only once �Column values can be set after compilation �Instead of values, use ‘? ’ �Hence, a Prepared Statement is statement that contains placeholders to be substituted later with actual values

Querying with Prepared. Statement String query. Str = "SELECT * FROM Items " + "WHERE Name = ? and Cost < ? ”; Prepared. Statement pstmt = con. prepare. Statement(query. Str); pstmt. set. String(1, “t-shirt”); pstmt. set. Int(2, 1000); Result. Set rs = pstmt. execute. Query();

Changing DB with Prepared. Statement String delete. Str = “DELETE FROM Items " + "WHERE Name = ? and Cost > ? ”; Prepared. Statement pstmt = con. prepare. Statement(delete. Str); pstmt. set. String(1, “t-shirt”); pstmt. set. Int(2, 1000); int delnum = pstmt. execute. Update();

Statements vs. Prepared. Statements: Be Careful! �Are these the same? What do they do? String val = “abc”; Prepared. Statement pstmt = con. prepare. Statement(“select * from R where A=? ”); pstmt. set. String(1, val); Result. Set rs = pstmt. execute. Query(); String val = “abc”; Statement stmt = con. create. Statement( ); Result. Set rs = stmt. execute. Query(“select * from R where A=” + val);

Statements vs. Prepared. Statements: Be Careful! �Will this work? Prepared. Statement pstmt = con. prepare. Statement(“select * from ? ”); pstmt. set. String(1, my. Favorite. Table. String); �No!!! A ‘? ’ can only be used to represent a column value �WHY?

Timeout �Use set. Query. Time. Out(int seconds) of Statement to set a timeout for the driver to wait for a statement to be completed �If the operation is not completed in the given time, an SQLException is thrown �What is it good for?

5. Executing a Query, Returning a Result Set Object & 6. Processing the Result Set Result. Set rset = pstmt. execute. Query(); // Print query results while (rset. next()) System. out. println (rset. get. String (1)+" "+ rset. get. String(2));

7. Closing the Result Set and Statement Objects 8. Closing the Connection // close the result set, statement, and the connection rset. close(); pstmt. close(); conn. close(); }

Mapping Data Types �There are data types specified to SQL that need to be mapped to Java data types if the user expects Java to be able to handle them. �Conversion falls into three categories: ◦ SQL type to Java direct equivalents SQL INTEGER direct equivalent of Java int data type. ◦ SQL type can be converted to a Java equivalent. SQL CHAR, VARCHAR, and LONGVARCHAR can all be converted to the Java String data type. ◦ SQL data type is unique and requires a special Java data class object to be created specifically for their SQL equivalent. SQL DATE converted to the Java Date object that is defined in java. Date especially for this purpose.

Result. Set �A Result. Set provides access to a table of data generated by executing a Statement �Only one Result. Set per Statement can be open at once �The table rows are retrieved in sequence ◦ A Result. Set maintains a cursor pointing to its current row of data ◦ The 'next' method moves the cursor to the next row

Result. Set Methods �boolean next() ◦ activates the next row ◦ the first call to next() activates the first row ◦ returns false if there are no more rows �void close() ◦ disposes of the Result. Set ◦ allows you to re-use the Statement that created it ◦ automatically called by most Statement methods

Result. Set Methods �Type get. Type(int column. Index) ◦ returns the given field as the given type ◦ fields indexed starting at 1 (not 0) �Type get. Type(String column. Name) ◦ same, but uses name of field ◦ less efficient �int find. Column(String column. Name) ◦ looks up column index given column name

Result. Set Methods String get. String(int column. Index) � boolean get. Boolean(int column. Index) � byte get. Byte(int column. Index) � short get. Short(int column. Index) � int get. Int(int column. Index) � long get. Long(int column. Index) � float get. Float(int column. Index) � double get. Double(int column. Index) � Date get. Date(int column. Index) � Time get. Time(int column. Index) � Timestamp get. Timestamp(int column. Index) �

Result. Set Methods � String get. String(String column. Name) � boolean get. Boolean(String column. Name) � byte get. Byte(String column. Name) � short get. Short(String column. Name) � int get. Int(String column. Name) � long get. Long(String column. Name) � float get. Float(String column. Name) � double get. Double(String column. Name) � Date get. Date(String column. Name) � Time get. Time(String column. Name) � Timestamp get. Timestamp(String column. Name)

Result. Set Example Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select name, age from Employees") ; // Print the result while(rs. next()) { System. out. print(rs. get. String(1) + ”: “); System. out. println(rs. get. Short(“age”)+”“); }

Null Values �In SQL, NULL means the field is empty �Not the same as 0 or “” �In JDBC, you must explicitly ask if a field is null by calling Result. Set. is. Null(column) �For example, get. Int(column) will return 0 if the value is either 0 or null!!

Null Values �When inserting null values into placeholders of Prepared Statements: ◦ Use the method set. Null(index, sql. Type) for primitive types (e. g. INTEGER, REAL); ◦ You may also use the set. XXX(index, null) for object types (e. g. STRING, DATE).

Result. Set Meta-Data A Result. Set. Meta. Data is an object that can be used to get information about the properties of the columns in a Result. Set object. An example: write the columns of the result set Result. Set. Meta. Data rsmd = rs. get. Meta. Data(); int numcols = rsmd. get. Column. Count(); for (int i = 1 ; i <= numcols; i++) { System. out. print(rsmd. get. Column. Label(i)+” “); } Many more methods in the Result. Set. Meta. Data API

Database Time �Times in SQL are notoriously non-standard �Java defines three classes to help �java. sql. Date ◦ year, month, day �java. sql. Time ◦ hours, minutes, seconds �java. sql. Timestamp ◦ year, month, day, hours, minutes, seconds, nanoseconds ◦ usually use this one

Cleaning Up After Yourself �Remember to close the Connections, Statements, Prepared. Statements and Result. Sets con. close(); stmt. close(); pstmt. close(); rs. close()

Dealing With Exceptions �An exception can have more exceptions in it. catch (SQLException e) { while (e != null) { System. out. println(e. get. SQLState()); System. out. println(e. get. Message()); System. out. println(e. get. Error. Code()); e = e. get. Next. Exception(); } }

LOBs: Large OBjects �Two types: ◦ CLOB: Character large object (a lot of characters) ◦ BLOB: Binary large object (a lot of bytes) �Actual data is not stored in the table with the CLOB/BLOB column. Only a pointer to the data is stored there �I will show to use a BLOB; CLOBs are similar

Retrieving a BLOB create table user. Images( user varchar(50), image BLOB ); Result. Set rs = stmt. execute. Query(“select image from user. Images”); while (rs. next) { Blob b = rs. get. Blob(“image”); Input. Stream stream = b. get. Binary. Stream(); do. Something(stream); }

Inserting a BLOB Prepared. Statement pstmt = con. prepare. Statement(“insert into user. Images values(‘snoopy’, ? )”); File file = new File(“snoopy. jpg”); Input. Stream fin = new File. Input. Stream(file); pstmt. set. Binary. Stream (1, fin, file. length()); pstmt. execute. Update();

Transactions and JDBC �Transaction = more than one statement which must all succeed (or all fail) together �If one fails, the system must reverse all previous actions �Also can’t leave DB in inconsistent state halfway through a transaction �COMMIT = complete transaction �ROLLBACK = abort

Example �Suppose we want to transfer money from bank account 13 to account 72: Prepared. Statement pstmt = con. prepare. Statement(“update Bank. Account set amount = amount + ? where account. Id = ? ”); pstmt. set. Int(1, -100); pstmt. set. Int(2, 13); pstmt. execute. Update(); What happens if this pstmt. set. Int(1, 100); update fails? pstmt. set. Int(2, 72); pstmt. execute. Update();

Transaction Management � Transactions are not explicitly opened and closed � The connection has a state called Auto. Commit mode � if Auto. Commit is true, then every statement is automatically committed � if Auto. Commit is false, then every statement is added to an ongoing transaction � Default: true

Auto. Commit set. Auto. Commit(boolean val) � If you set Auto. Commit to false, you must explicitly commit or rollback the transaction using Connection. commit() and Connection. rollback() � In order to work with LOBs, you usually have to set Auto. Commit to false, while retrieving the data � Note: DDL statements in a transaction may be ignored or may cause a commit to occur. The behavior is DBMS dependent

Fixed Example con. set. Auto. Commit(false); try { Prepared. Statement pstmt = con. prepare. Statement(“update Bank. Account set amount = amount + ? where account. Id = ? ”); pstmt. set. Int(1, -100); pstmt. set. Int(2, 13); pstmt. execute. Update(); pstmt. set. Int(1, 100); pstmt. set. Int(2, 72); pstmt. execute. Update(); con. commit(); catch (Exception e) { con. rollback(); }

Isolation Levels �How do different transactions interact? Do they see what another has written? �Possible problems: ◦ Dirty Reads: a transaction may read uncommitted data ◦ Unrepeatable Reads: two different results are seen when reading a tuple twice in the same transaction ◦ Phantom Reads: tuples are added to a table between two readings of this table in a single transaction

Isolation Levels JDBC defines four isolation modes: Level Dirty Read Unrepeatable Read Phantom Read Uncommited Yes Yes Read Commited No Yes Repeatable Read No No Yes Serializable No No No

Isolation Levels �Set the transaction mode using set. Transaction. Isolation() of class Connection �Oracle only implements: ◦ TRANSACTION_SERIALIZABLE �An exception may be thrown if serializability isn’t possible ◦ TRANSACTION_READ_COMMITED �This is the default

Level: READ_COMMITED �Transaction 1: �Transaction 2: insert into A values(1) insert into A values(2) commit select * from A Question: Is it possible for a transaction to see 1 in A, but not 2? 1 2 Table: A Question: Is it possible for the 2 queries to give different answers for level SERIALIZABLE?

Mapping Java Types to SQL Types SQL type Java Type CHAR, VARCHAR, LONGVARCHAR String NUMERIC, DECIMAL java. math. Big. Decimal BIT boolean TINYINT byte SMALLINT short INTEGER int BIGINT long REAL float FLOAT, DOUBLE double BINARY, VARBINARY, LONGVARBINARY byte[] DATE java. sql. Date TIME java. sql. Time TIMESTAMP java. sql. Timestamp