JDBC Java Database Connectivity 1 Introduction to JDBC

JDBC – Java Database Connectivity 1

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 2

JDBC Architecture We willare These use thisclasses one… Java Application JDBC Oracle Driver Oracle DB 2 Driver DB 2 Network Postgres Driver Postgres 3

JDBC Architecture (cont. ) Application JDBC Driver • Java code calls JDBC library • JDBC loads a driver • Driver talks to a particular database • An application can work with several databases by using all corresponding drivers • Ideal: can change database engines without changing any application code (not always in practice)

Seven Steps • Load the driver • Define the connection URL • Establish the connection • Create a Statement object • Execute a query using the Statement • Process the result • Close the connection 5

Registering the Driver • To use a specific driver, we need to instantiate it and register it within the driver manager: Driver driver = new oracle. jdbc. Oracle. Driver(); Driver. Manager. register. Driver(driver); 6

A Modular Alternative • We can register the driver indirectly using the statement Class. for. Name("oracle. jdbc. driver. Oracle. Driver"); • Class. for. Name loads the specified class • When Oracle. Driver is loaded, it automatically - creates an instance of itself - registers this instance with the Driver. Manager • Hence, the driver class can be given as an argument of the application 7

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 8

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

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 10

The URLs in CS In CS, a URL has the following structure: jdbc: oracle: thin: name/password@sol 4: 1521: stud Also, your login Your login The machine on port The standard which our of Oracle runs For example: jdbc: oracle: thin: snoopy/snoopy@sol 4: 1521: stud 11

Interaction with the Database • We use Statement objects in order to - Query the database - Update the database • Three different interfaces are used: Statement, Prepared. Statement, Callable. Statement • All are interfaces, hence 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… 13

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); • 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 14

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, Prepared Statements can be though of as statements that contain placeholders to be substituted later with actual values 15

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(); 16

Updating 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(); 17

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); 18

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 19

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? 20

Result. Set • Result. Set objects provide access to the tables generated as results of executing a Statement queries • Only one Result. Set per Statement can be open at the same time! • The table rows are retrieved in sequence - A Result. Set maintains a cursor pointing to its current row - 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 - indices start at 1 and not 0! • Type get. Type(String column. Name) - same, but uses name of field - less efficient • For example: get. String(column. Index), get. Int(column. Name), get. Time, get. Boolean, get. Type, . . . • int find. Column(String column. Name) - looks up column index given 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")); } 24

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

More Information A detailed overview of type mapping and type conversion can be found at http: //java. sun. com/j 2 se/1. 3/docs/guide/jdbc/getstart /mapping. html 26

Null Values • In SQL, NULL means the field is empty • Not the same as 0 or "" • In JDBC, you must explicitly ask if the last-read field was null - Result. Set. was. 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, Types. sql. Type) for primitive types (e. g. INTEGER, REAL); - You may also use the set. Type(index, null) for object types (e. g. STRING, DATE). 28

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 29

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() 31

Dealing With Exceptions • An SQLException is actually a list of exceptions 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(); } } 32

Transaction Management 33

Transactions and JDBC • Transaction: more than one statement that must all succeed (or all fail) together - e. g. , updating several tables due to customer purchase • 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 = cancel all actions 34

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(); 35

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 36

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() • Note: DDL statements (e. g. , creating/deleting tables) in a transaction may be ignored or may cause a commit to occur - The behavior is DBMS dependent 37

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 (SQLException e) { con. rollback(); } 38

Isolation Levels • How do different transactions interact? Do they see what another has written? • Possible problems: - Dirty Reads: one transaction reads data written by another uncommitted transaction - Unrepeatable Reads: two different results are seen when reading the same row twice in the same transaction - Phantom Reads: rows are added to (or deleted from) a table between two readings of this table in a single transaction 39

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

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 41

Level: READ_COMMITED • Transaction 1: • Transaction 2: insert into A values(1) select * from A insert into A values(2) select * from A commit 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? 42

Large Objects 43

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 • Oracle does not support these objects as in the specification, so a special treatment is required • We will see how BLOBs are managed - Handling CLOBs is similar 44

Storing BLOBs • Suppose that we have a binary source (e. g. , a file, a socket, etc. ) that is readable through a Java Input. Stream object istream • Suppose that we want to store the source content in a table My. Blobs(name varchar, content BLOB) 45

Storing BLOBs (cont) • First, we set Auto. Commit to false: con. set. Auto. Commit(false); • Next, we insert a row with an empty BLOB: Statement stmt = con. create. Statement(); stmt. execute. Update("insert into myblobs values('b 1', empty_blob()") • Now, retrieve the BLOB: Result. Set rs = stmt. execute. Query("select content from myblobs where name = 'b 1'"); rs. next(); BLOB bl = (BLOB) (rs. get. Blob(1)); 46

Storing BLOBs (cont) • We can now get the BLOB's output stream Output. Stream bl. Stream = bl. get. Binary. Output. Stream(); • Next, we write the content into the stream: int bytes. Read = 0; byte[] data = new byte[4096]; while ((bytes. Read = file. Stream. read(data)) >= 0) bl. Stream. write(data, 0, bytes. Read); • Finally, we close the resources and commit rs. close(); stmt. close(); bl. Stream. close(); con. commit(); 47

Retrieving BLOBs • BLOB retrieval is simpler that storage • Suppose that we want to write our BLOB to ostream • First, we get the BLOB: Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select content from binary. Files where name ='b 1'"); rs. next(); BLOB bl = (BLOB) (rs. get. Blob(1)); • Next, get the input stream of the BLOB: Input. Stream bl. Stream = bl. get. Binary. Stream(); 48

Retrieving BLOBs (cont) • Now, we read the BLOB content through the stream: int bytes. Read = 0; byte[] data = new byte[4096]; while ((bytes. Read = bl. Stream. read(data)) >= 0) ostream. write(data, 0, bytes. Read); • Finally, we close the resources and commit rs. close(); stmt. close(); bl. Stream. close(); con. commit(); 49