JDBC Postgre SQL Useful JDBC Links Getting Started

JDBC (@Postgre. SQL)

Useful JDBC Links • Getting Started Guide: http: //java. sun. com/j 2 se/1. 5. 0/docs/guide/jdbc/g etstart/Getting. Started. TOC. fm. html • java. sql Package API: http: //java. sun. com/j 2 se/1. 5. 0/docs/api/java/sql/ package-summary. html 2

Why Access a Database from within a Program? • Some queries can’t be computed in SQL. • Why not keep all the data in Java objects? • “Separation of concerns”: DBMS-s good on data storage and access; programs concentrate on algorithms, networking, etc. 3

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 databases from Java applications • Information is transferred from relations to objects and vice-versa – databases optimized for searching/indexing – objects optimized for engineering/flexibility DB HUJI-CS 4

Hello World Example import java. sql. *; public class Hello. World { public static void main(String[] str) throws Exception { Class. for. Name("org. postgresql. Driver"); Connection con = Driver. Manager. get. Connection("jdbc: postgresql: //pgserver/public? use r=me"); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select 'hello world'"); while(rs. next()) { System. out. println(rs. get. String(1)); } stmt. close(); rs. close(); con. close(); } } DB HUJI-CS 5

Packages to Import • In order to connect to a database from java, import the following packages: – java. sql. *; (usually enough) – javax. sql. * (for advanced features, such as scrollable result sets) 6 DB HUJI-CS

access • You will need to add an appropriate jar file to your classpath. The jar file for the Postgres driver is available at: /usr/share/java/postgresql. jar 7 DB HUJI-CS

Hello World Example import java. sql. *; public class Hello. World { public static void main(String[] str) throws Exception { Class. for. Name("org. postgresql. Driver"); Connection con = Driver. Manager. get. Connection("jdbc: postgresql: //pgserver/public? us er=me"); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select 'hello world'"); while(rs. next()) { System. out. println(rs. get. String(1)); } stmt. close(); rs. close(); con. close(); } } DB HUJI-CS 8

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… DB HUJI-CS Postgres 9

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) DB HUJI-CS 10

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 DB HUJI-CS 11

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); DB HUJI-CS 12

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 DB HUJI-CS 13

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 DB HUJI-CS 14

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? DB HUJI-CS 15

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 DB HUJI-CS 16

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

Hello World Example import java. sql. *; public class Hello. World { public static void main(String[] str) throws Exception { Class. for. Name("org. postgresql. Driver"); Connection con = Driver. Manager. get. Connection("jdbc: postgresql: //pgserver/public? us er=me"); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select 'hello world'"); while(rs. next()) { System. out. println(rs. get. String(1)); } stmt. close(); rs. close(); con. close(); } } DB HUJI-CS 18

Hello World Example import java. sql. *; public class Hello. World { public static void main(String[] str) throws Exception { Class. for. Name("org. postgresql. Driver"); Connection con = Driver. Manager. get. Connection("jdbc: postgresql: //pgserver/public? use r=me"); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select 'hello world'"); while(rs. next()) { System. out. println(rs. get. String(1)); } stmt. close(); rs. close(); con. close(); } } DB HUJI-CS 19

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 DB HUJI-CS 20

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

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) DB HUJI-CS 22

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 DB HUJI-CS 23

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(); DB HUJI-CS 24

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(); DB HUJI-CS 25

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 DBA=" + val); 26

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) DB HUJI-CS 27

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 ‘ DB HUJI-CS 28

Prepared. Statement and Security • We would get: Statement s; s. execute. Query( "SELECT URL, Title from Internet WHERE Content LIKE ‘%aaaaa’ UNION SELECT Company AS URL, Credit. Card. Num AS Title FROM Advertising. Clients WHERE Company LIKE ‘%’ ”) • This technique is known as SQL Injection and is SQL Injection the main reason for using Prepared. Statements HUJI-CS DB 29

Some Famous SQL-Injections • 2009, the United States Justice Department charged an American citizen Albert Gonzalez and two unnamed Russians with theft of 130 million credit card numbers using an SQL injection attack • 2010 the British Royal Navy website was compromised by Tin. Kode using SQL injection. • 2011, mysql. com, the official homepage for My. SQL, was compromised by Tin. Kode using SQL blind injection • 2011, "hacktivists" of the group Lulzsec were accused of using SQLI to steal coupons, download keys, and passwords that were stored in plaintext on Sony's website, accessing the personal information of a million users DB HUJI-CS 30

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? DB HUJI-CS 31

Hello World Example import java. sql. *; public class Hello. World { public static void main(String[] str) throws Exception { Class. for. Name("org. postgresql. Driver"); Connection con = Driver. Manager. get. Connection("jdbc: postgresql: //pgserver/public? use r=me"); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select 'hello world'"); while(rs. next()) { System. out. println(rs. get. String(1)); } stmt. close(); rs. close(); con. close(); } } DB HUJI-CS 32

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 DB HUJI-CS 33

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 DB HUJI-CS 34

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 DB HUJI-CS 35

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")); } DB HUJI-CS 36

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 TIME HUJI-CS DBTIMESTAMP Java Type String java. math. Big. Decim al boolean byte short int long float double byte[] java. sql. Date java. sql. Timestamp 37

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! DB HUJI-CS 38

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) DB HUJI-CS 39

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 DB 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 l(i)+" "); 40

Database Time • Java defines three classes to help process time • 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 DB HUJI-CS 41

Hello World Example import java. sql. *; public class Hello. World { public static void main(String[] str) throws Exception { Class. for. Name("org. postgresql. Driver"); Connection con = Driver. Manager. get. Connection("jdbc: postgresql: //pgserver/public? use r=me"); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("select 'hello world'"); while(rs. next()) { System. out. println(rs. get. String(1)); } stmt. close(); rs. close(); con. close(); } } DB HUJI-CS 42

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

Dealing With Exceptions An SQLException is actually a list of exceptions DB 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 44

Transaction Management

Transactions and JDBC • Transaction: more than one statement that must Transaction: 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 DB HUJI-CS 46

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); DB HUJI-CS What happens if this update fails? 47

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) DB HUJI-CS 48

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, …) DB HUJI-CS 49

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() DB HUJI-CS 50

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) DB HUJI-CS 51

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(); DB HUJI-CS 52

Be Careful DB • Suppose we have private functions deposit and withdraw (doing the expected) • Would the following function do what we want? public void transfer (int value, int acc 1, int acc 2) try { deposit(value, acc 1); withdraw(value, acc 2); con. commit(); HUJI-CS 53

Transaction Isolation • There is still a lot more to discuss about transactions: – How do different transactions interact? – Does a running transaction see uncommitted changes? – Does it see committed changes? • Details later in the course… DB HUJI-CS 54