JDBC Postgre SQL Java Database Connectivity JDBC Java

JDBC (@Postgre. SQL)

Java Database Connectivity • JDBC (Java Database Connectiveity) is an API (Application Programming Interface), – That is, a collection of classes and interfaces • JDBC is used for accessing (mainly) databases from Java applications • Information is transferred from relations to objects and vice-versa – databases optimized for searching/indexing – objects optimized for engineering/flexibility DBI 2008 HUJI-CS 2

JDBC Architecture Network These are Java classes Oracle Driver Oracle Java Application DB 2 Driver JDBC DB 2 Postgres Driver We will use this one… DBI 2008 HUJI-CS Postgres 3

JDBC Architecture (cont. ) Application JDBC Driver • Java code calls JDBC library • JDBC loads a driver • The driver talks to a particular database • An application can work with several databases by using all corresponding drivers • Ideal: change database engines w/o changing any application code (not always in practice) DBI 2008 HUJI-CS 4

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 DBI 2008 HUJI-CS 5

Registering the Driver • To use a specific driver, instantiate and register it within the driver manager: Driver driver = new org. postgresql. Driver(); Driver. Manager. register. Driver(dri ver); DBI 2008 HUJI-CS 6

A Modular Alternative • We can register the driver indirectly using Class. for. Name("org. postgresql. Dri ver"); • Class. for. Name loads the given class dynamically • When the driver is loaded, it automatically – creates an instance of itself – registers this instance within Driver. Manager • Hence, the driver class can be given as an argument of the application DBI 2008 HUJI-CS 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. imaginar y 2 Driver(); Driver. Manager. register. Driver(driver); //A driver for Postgre. SQL Class. for. Name("org. postgresql. Driver"); imaginary 1 imaginary 2 Postgres Registered Drivers DBI 2008 HUJI-CS 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 • How is this done? DBI 2008 HUJI-CS 9

Connecting to the Database Connection con = Driver. Manager. get. Connection("jdbc: imagin ary. DB 1"); accepts. URL("jdbc: imaginary. DB 1")? a r imaginary 1 imaginary 2 r Postgres Registered Drivers DBI 2008 HUJI-CS 10

The URLs in HUJI-CS In CS, a URL has the following structure: jdbc: postgresql: //dbserver/public? user=? ? Your login The machine running Postgr. SQL You can only access your own account! DBI 2008 HUJI-CS 11

Interaction with the Database • We use Statement objects in order to – Query the db – Update the db (insert, update, create, drop, …) • Three different interfaces are used: Statement, Prepared. Statement, Callable. Statement • All are interfaces, hence cannot be instantiated • They are created by the Connection DBI 2008 HUJI-CS 12

Querying with Statement String query. Str = "SELECT * FROM Member " + "WHERE Lower(Name) = 'harry potter'"; Statement stmt = con. create. Statement(); Result. Set rs =returns a Result. Set object execute. Query representing the query result (discussed later…) stmt. execute. Query(query. Str); DBI 2008 HUJI-CS 13

Changing DB with Statement String delete. Str = "DELETE FROM Member " + "WHERE Lower(Name) = 'harry potter'"; Statement stmt = con. create. Statement(); • execute. Update is for data manipulation: insert, int delnum = delete, update, create table, etc. stmt. execute. Update(delete. Str); – Anything other than querying! • execute. Update returns the number of rows modified (or 0 for DDL commands) DBI 2008 HUJI-CS 14

About Prepared Statements • Prepared statements are used for queries that are executed many times • Parsed (compiled) by the DBMS only once • Values of some columns are set after compilation • Instead of values, use ‘? ’ and set. Type methods • Hence, prepared statements can be thought of as statements that contain placeholders to be substituted later with actual values DBI 2008 HUJI-CS 15

Querying with Prepared. Statement String q = "SELECT * FROM Items " + "WHERE Name = ? and Cost < ? "; Prepared. Statement pstmt=con. prepare. Statement(q); pstmt. set. String(1, "t-shirt"); pstmt. set. Int(2, 1000); Result. Set rs = pstmt. execute. Query(); DBI 2008 HUJI-CS 16

Updating with Prepared. Statement String dq = "DELETE FROM Items " + "WHERE Name = ? and Cost > ? "; Prepared. Statement pstmt = con. prepare. Statement(dq); pstmt. set. String(1, "t-shirt"); pstmt. set. Int(2, 1000); int delnum = pstmt. execute. Update(); DBI 2008 HUJI-CS 17

Statement vs. Prepared. Statement: 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 HUJI-CS DBIA=" 2008 + val); 18

What can be Assigned to “? ” • Will this work? Prepared. Statement pstmt = con. prepare. Statement("select * from ? "); pstmt. set. String(1, my. Favorite. Table. String); • No!!! “? ” can only represent a column value (to enable pre-compilation) DBI 2008 HUJI-CS 19

Prepared. Statement and Security • Suppose Google was implemented in JDBC without a Prepared. Statement. The main DB query might have been implemented like this: Statement s; s. execute. Query("SELECT URL, Title from Internet "+ "WHERE Content LIKE ‘%" + search. String + "%’"); • What would happen if a hacker searched for: aaaaa’ UNION SELECT Company AS URL, Credit. Card. Num AS Title FROM Advertising. Clients WHERE Company LIKE ‘ DBI 2008 HUJI-CS 20

Timeout • Use set. Query. Time. Out(int seconds) of Statement to set a timeout for the driver to wait for a query to be completed • If the operation is not completed in the given time, an SQLException is thrown • What is it good for? DBI 2008 HUJI-CS 21

Result. Set • Result. Set objects provide access to the tables generated as results of executing Statement queries • Only one Result. Set per Statement or Prepared. Statement can be open at a given time! • The table rows are retrieved in sequence – A Result. Set maintains a cursor pointing to its current row – next() moves the cursor to the next row DBI 2008 HUJI-CS 22

Result. Set Methods • boolean next() – Activates the next row – First call to next() activates the first row – Returns false if there are no more rows – Not all of the next calls actually involve the DB • void close() – Disposes of the Result. Set – Allows to re-use the Statement that created it – Automatically called by most Statement methods DBI 2008 HUJI-CS 23

Result. Set Methods (cont’d) • Type get. Type(int column. Index) – Returns the given field as the given type – Indices start at 1 and not 0! – Add the column name as a comment if it is known! • Type get. Type(String column. Name) – Same, but uses name of field – Less efficient (but may not be your bottleneck anyway) • Examples: get. String(5), get. Int(“salary”), get. Time(…), get. Boolean(…), . . . • int find. Column(String column. Name) – Looks up column index given column name DBI 2008 HUJI-CS 24

Result. Set Example Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select name, age from Empl oyees"); // Print the result while(rs. next()) { System. out. print(rs. get. String(1) + ": "); System. out. println(rs. get. Short("age")); } DBI 2008 HUJI-CS 25

Mapping Java Types to SQL Types SQL Type CHAR, VARCHAR, LONGVARCHAR NUMERIC, DECIMAL BIT TINYINT SMALLINT INTEGER BIGINT REAL FLOAT, DOUBLE BINARY, VARBINARY, BYTEA DATE TIMESTAMP HUJI-CS DBI 2008 Java Type String java. math. Big. Decim al boolean byte short int long float double byte[] java. sql. Date java. sql. Timestamp 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 lastread field was null – Result. Set. was. Null(column) • For example, get. Int(column) will return 0 if the value is either 0 or NULL! DBI 2008 HUJI-CS 27

Null Values When inserting null values into placeholders of a Prepared. Statement: – Use set. Null(index, Types. sql. Type) for primitive types (e. g. INTEGER, REAL); – For object types (e. g. STRING, DATE) you may also use set. Type(index, null) DBI 2008 HUJI-CS 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. Labe HUJI-CS DBI 2008 l(i)+" "); 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 DBI 2008 HUJI-CS 30

Cleaning Up After Yourself Remember: close Connections, Statements, Prepared Statements and Result Sets con. close(); stmt. close(); pstmt. close( ); rs. close() DBI 2008 HUJI-CS 31

Dealing With Exceptions An SQLException is actually a list of exceptions DBI 2008 catch (SQLException e) { while (e != null) { System. out. println(e. get. SQLSt ate()); System. out. println(e. get. Messa ge()); System. out. println(e. get. Error. C ode()); e = e. get. Next. Exception(); HUJI-CS 32

General SQL Advise • Take the time to carefully design your database before you start coding – this will save you time and frustration • The same data can be organized in very different ways – When designing your DB and when deciding what your primary keys should be, always think about typical use cases and deduce from them which queries are most common and which queries must run fastest • Add as many constraints as possible (NOT NULL, UNIQUE, etc…) – this will help you debug prevent data corruption even if you missed a bug HUJI-CS DBI 2008 33

Transaction Management

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 • Failure− 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 DBI 2008 HUJI-CS 35

An Example Suppose that 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(); pstmt. set. Int(1, 100); pstmt. set. Int(2, 72); DBI 2008 HUJI-CS What happens if this update fails? 36

Transaction Lifetime • Transactions are not opened and closed explicitly • A transaction starts on 1 st (successful) command – After a connection is established – After the previous transaction ends • A transaction ends when COMMIT or ROLLBACK are applied – Either explicitly or implicitly (see next 4 slides) DBI 2008 HUJI-CS 37

Committing a Transaction How do we commit? • Explicitly invoking Connection. commit() • Implicitly – After every query execution, if Auto. Commit is true – When the user normally disconnects (i. e. , appropriately closes the connection) – In some DBs: After invoking a DDL command (CREATE, DROP, RENAME, ALTER, …) DBI 2008 HUJI-CS 38

Automatic Commitment • A Connection object has a boolean Auto. Commit • If Auto. Commit is true (default), then every statement is automatically committed • If Auto. Commit is false, then each statement is added to an ongoing transaction • Change using set. Auto. Commit(boolean) • If Auto. Commit is false, need to explicitly commit or rollback the transaction using Connection. commit() and Connection. rollback() DBI 2008 HUJI-CS 39

Rolling Back • Rolling Back: Undoing any change to data within the current transaction • The ROLLBACK command explicitly rolls back (and ends) the current transaction • ROLLBACK is implicitly applied when the user abnormally disconnects (i. e. , without appropriately closing the connection) DBI 2008 HUJI-CS 40

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(); DBI 2008 HUJI-CS 41

Transaction Isolation • How do different transactions interact? • Does a running transaction see uncommitted changes? • Does it see committed changes? • Don’t worry about this now – we will discuss it in future lessons (see the Appendix if you really can’t wait) DBI 2008 HUJI-CS 42

Managing Large Objects In Posgre. SQL-JDBC

LOBs: Large OBjects • A database can store large pieces of data – e. g. , images or other files • Sometimes, two distinguished types: – CLOB: Character large object (a large number of characters) – BLOB: Binary large object (a lot of bytes) • Actual data is not stored in the table, only a pointer (Object ID) to the data storage DBI 2008 HUJI-CS 44

LOBs in Postgre. SQL Create a new lob Get an in-stream to Obj. 24 Get an out-stream Obj. 53 LOB Manager 911 My. Images Name image 1 image 2 DBI 2008 53 Description content Human face Rabbit 53 113 HUJI-CS 24 113 LOBs 45

Storing LOBs (1) CREATE TABLE My. Images ( name VARCHAR(20) , content OID); • Given: A binary source (file, socket, etc. ), readable through an Input. Stream object • Goal: Store the content of the source in the table My. Images DBI 2008 HUJI-CS 46

Storing LOBs (2) 1. Disable auto-commit (why? ? ? ): con. set. Auto. Commit(false) 2. Obtain the LOB manager: • Downcast con into org. postgresql. PGConnection arge. Object. Manager lobm = con. get. Large. Object. API 3. Create a new LOB int oid = DBI 2008 lobm. create(Large. Object. Manager. REA D | HUJI-CS 47

Storing LOBs (3) 4. Insert a new row, with the LOB’s oid Prepared. Statement pstmt = con. prepare. Statement ("INSERT INTO My. Images VALUES (? , ? )"); pstmt. set. String(1, image. Name); pstmt. set. Int(2, oid); pstmt. execute. Update(); pstmt. close(); DBI 2008 HUJI-CS 48

Storing LOBs (4) 5. Fill the LOB with the input bytes Large. Object obj = lobm. open(oid, Large. Object. Manager. WRITE); int bytes. Read = 0; byte[] data = new byte[4096]; while ((bytes. Read = i. Stream. read(data)) 6. Finalize >= 0) obj. close(); i. Stream. close(); con. commit(); obj. write(data, 0, bytes. Read); DBI 2008 HUJI-CS 49

Retrieving LOBs (1) 1. Obtain the LOB ID from the relevant row: Prepared. Statement pstmt = con. prepare. Statement ("select bytes from My. Images where name = ? "); pstmt. set. String(1, filename); Result. Set rs = pstmt. execute. Query(); rs. next(); int oid = rs. get. Int(1); DBI 2008 HUJI-CS 50

Retrieving LOBs (2) 2. Using oid, obtain a the corresponding Large. Object instance from the LOB Manager • Downcast con into org. postgresql. PGConnection Large. Object. Manager lobm = con. get. Large. Object. API(); Large. Object obj = lobm. open(oid, Large. Object. Manager. REA D); DBI 2008 HUJI-CS 51

Retrieving LOBs (3) 3. Read the content of the large object (that acts as an input/output stream) while ((bytes. Read = obj. read(data, 0, data. length)) > 0) o. Stream. write(data, 0, bytes. Read); obj. close(); o. Stream. close(); DBI 2008 HUJI-CS 52

Appendix: Postgre. SQL Command-Line Interface

Entering the Command-Line UI psql -hdbserver public Server running Postgre. SQL Database Name Automatically enters your own part of the database (based on your login) Recommendation: > echo ”alias psql ’psql –hdbserver public’” >> ~/. aliases DBI 2008 HUJI-CS 54

A Screenshot You can now write SQL queries DBI 2008 HUJI-CS 55

Additional Useful Commands dt d table-name d. T i sql-file dl lo_export oid file lo_import file q DBI 2008 List all tables Show table definition List all types Include SQL from file List all large objects Export a large object Import a large object Quit HUJI-CS 56

Transactions in Command Line • By default, the command-line session runs in AUTOCOMMIT mode – That is, commit is implicitly applied after every command • To use transactions, you need to explicitly specify the beginning and end of a transaction by the commands BEGIN and • At the end of the transaction, you either apply COMMIT, ROLLBACK or END DBI 2008 HUJI-CS 57

Appendix: Byte-Arrays

Representing Byte Arrays Use the SQL type BYTEA to hold byte arrays CREATE TABLE My. BStrings ( name VARCHAR(20) , content BYTEA); (works with Postgre. SQL, may not work with other DBMSs) DBI 2008 HUJI-CS 59

Storing Byte Arrays Store array b[] of bytes: 1. Prepare a row-inserting statement Prepared. Statement pstmt =con. prepare. Statement ("INSERT INTO My. BStrings VALUES (? , ? )"); 2. Set the statement values: pstmt. set. String(1, desired. Name); pstmt. set. Bytes(2, b); 3. Execute and close pstmt. execute. Upda te(); pstmt. close(); HUJI-CS DBI 2008 60

Retrieving Byte Arrays Retrieve bytes with the name “my. Bytes”: 1. Apply a selection query Prepared. Statement pstmt =con. prepare. Statement (“SELECT content from My. BStrings where name=? "); pstmt. set. String(1, "my. Bytes"); 2. Obtain the byte-array from the result Result. Set rs. next(); rs = ps. execute. Query(); byte[] the. Bytes = rs. get. Bytes(1); 3. Close resources DBI 2008 rs. close(); pstmt. close(); HUJI-CS 61

Appendix: Transaction Isolation

Transaction Isolation • How do different transactions interact? • Does a running transaction see uncommitted changes? • Does it see committed changes? DBI 2008 HUJI-CS 63

Transaction Isolation Levels • The isolation level determines the capabilities of a transaction to read/write data that is accessed by other transactions • In Postgre. SQL, two levels of isolation: 1. READ COMMITTED (default) 2. SERIALIZABLE • Each transaction determines its isolation level – Connection. set. Transaction. Isolation(int level) DBI 2008 HUJI-CS 64

Isolation Level in the Command-Line UI Changing to read committed: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED; Changing to serializable: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE; DBI 2008 HUJI-CS 65

READ COMMITED & SERIALIZABLE • In principle, a query in Postgre. SQL never reads uncommitted (i. e. dirty) data • SERIALIZABLE: During the whole transaction, statements read only the changes that were committed by the time the transaction begun (and the changes made by the transaction itself) • READ COMMITTED: A statement reads the data that was committed by the time the statement (not the transaction) begun DBI 2008 HUJI-CS 66

Update: Row-Level Locking • Postgre. SQL disables one from updating a row that is updated by an uncommitted transaction – The second updating transaction is blocked until the first one commits or rolls back • If 2 nd update is within READ COMMITTED, then 2 nd update is done after 1 st transaction commits or rolls back • If 2 nd update is within SERIALIZABLE, then 2 nd update fails if 1 st transaction commits; otherwise, it succeeds DBI 2008 HUJI-CS 67

Some Definitions • Dirty reads: A transaction reads data that is written by another, uncommitted transaction • Non-repeatable reads: A transaction rereads data it previously read and finds that a committed transaction has modified or deleted that data • Phantom reads: A transaction re-executes a query returning a set of rows satisfying a search condition and finds that a committed transaction inserted additional rows that satisfy the condition DBI 2008 HUJI-CS 68

READ COMMITED vs. SERIALIZABLE Dirty Reads Non-repeatable Reads Phantom Reads DBI 2008 READ COMMITED SERIALIZABLE Impossible Possible Impossible HUJI-CS 69

What Happens Here (1)? 1. CREATE TABLE pairs (x INTEGER, y INTEGER); 2. select * from pairs 3. insert into pairs values(1, 1) 6. select * from pairs 8. COMMIT T. 1: R. COMMITTED DBI 2008 4. select * from pairs 5. insert into pairs values(1, 2) 7. select * from pairs 9. COMMIT HUJI-CS T. 2: SERIALIZABLE 70

What Happens Here (2)? 1. CREATE TABLE pairs (x INTEGER, y INTEGER); 2. insert into pairs values(1, 1) 3. select * from pairs 4. COMMIT 5. select * from pairs 6. select * from pairs 7. insert into pairs values(1, 2) 8. COMMIT 9. select * from pairs 10. COMMIT T. 2: SERIALIZABLE T. 1: R. COMMITTED DBI 2008 HUJI-CS 71

What Happens Here (3)? 1. CREATE TABLE pairs (x INTEGER, y INTEGER); 2. insert into pairs values(1, 1) 3. select * from pairs 4. COMMIT 5. select * from pairs 6. select * from pairs 7. insert into pairs values(1, 2) 8. COMMIT 9. select * from pairs 10. COMMIT T. 2: SERIALIZABLE T. 1: SERIALIZABLE Is it equivalent to any truly serial execution of the transactions? DBI 2008 HUJI-CS 72

What Happens Here (4)? 1. CREATE TABLE pairs (x INTEGER, y INTEGER); 2. insert into pairs values(1, 1) 3. COMMIT 4. update pairs set y=2 where x=1 5. update pairs set y=3 where x=1 6. select * from pairs 7. COMMIT 8. select * from pairs 9. COMMIT T. 2: SERIALIZABLE T. 1: SERIALIZABLE DBI 2008 HUJI-CS 73

What Happens Here (5)? 1. CREATE TABLE pairs (x INTEGER, y INTEGER); 2. insert into pairs values(1, 1) 3. COMMIT 4. update pairs set y=2 where x=1 5. update pairs set y=3 where x=1 6. select * from pairs 7. COMMIT 8. select * from pairs 9. COMMIT T. 2: SERIALIZABLE T. 1: R. COMMITTED DBI 2008 HUJI-CS 74

Appendix: Changing Passwords

Changing Your Password Suppose that snoopy wants to change his password from “snoopy” to “snoopass”: ALTER USER snoopy WITH PASSWORD 'snoopass'; DBI 2008 HUJI-CS 76