JDBC (@ PostgreSQL )

1. JDBC (@PostgreSQL)

2. 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 • databasesoptimized for searching/indexing • objects optimized for engineering/flexibility

3. JDBC Architecture Network These are Java classes Oracle Driver Oracle Java Application DB2 Driver JDBC DB2 We will use this one… Postgres Driver Postgres

4. 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)

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

6. Registering the Driver • To use a specific driver, instantiate and register it within the driver manager: • Driver driver = new org.postgresql.Driver(); • DriverManager.registerDriver(driver);

7. A Modular Alternative • We can register the driver indirectly using • Class.forName loads the given class dynamically • When the driver is loaded, it automatically • creates an instance of itself • registers this instance within DriverManager • Hence, the driver class can be given as an argument of the application Class.forName("org.postgresql.Driver");

8. An Example // A driver for imaginary1 Class.forName("ORG.img.imgSQL1.imaginary1Driver"); // A driver for imaginary2 Driver driver = new ORG.img.imgSQL2.imaginary2Driver(); DriverManager.registerDriver(driver); //A driver for PostgreSQL Class.forName("org.postgresql.Driver"); imaginary1 Postgres imaginary2 Registered Drivers

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

10. Connecting to the Database Connection con =  DriverManager.getConnection("jdbc:imaginaryDB1"); acceptsURL("jdbc:imaginaryDB1")? a r r imaginary1 Postgres imaginary2 Registered Drivers

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

12. 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, PreparedStatement, CallableStatement • All are interfaces, hence cannot be instantiated • They are created by the Connection

13. Querying with Statement String queryStr = "SELECT * FROM Member "+ "WHERE Lower(Name) = 'harry potter'"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(queryStr); executeQuery returns a ResultSet object representing the query result (discussed later…)

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

15. 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 setType methods • Hence, prepared statements can be thought of as statements that contain placeholders to be substituted later with actual values

16. Querying with PreparedStatement String q = "SELECT * FROM Items "+ "WHERE Name = ? and Cost < ?"; PreparedStatement pstmt=con.prepareStatement(q); pstmt.setString(1,"t-shirt"); pstmt.setInt(2, 1000); ResultSet rs = pstmt.executeQuery();

17. Updating with PreparedStatement String dq = "DELETE FROM Items "+ "WHERE Name = ? and Cost > ?"; PreparedStatement pstmt = con.prepareStatement(dq); pstmt.setString(1, "t-shirt"); pstmt.setInt(2, 1000); int delnum = pstmt.executeUpdate();

18. Statement vs. PreparedStatement: Be Careful! Are these the same? What do they do? String val ="abc"; PreparedStatement pstmt = con.prepareStatement("select * from R where A=?"); pstmt.setString(1, val); ResultSet rs = pstmt.executeQuery(); String val ="abc"; Statement stmt = con.createStatement( ); ResultSet rs = stmt.executeQuery("select * from R where A=" + val);

19. What can be Assigned to “?” • Will this work? • No!!!“?” can only represent a column value (to enable pre-compilation) PreparedStatement pstmt = con.prepareStatement("select * from ?"); pstmt.setString(1, myFavoriteTableString);

20. PreparedStatement and Security • Suppose Google was implemented in JDBC without a PreparedStatement. The main DB query might have been implemented like this:Statement s;s.executeQuery("SELECT URL,Title from Internet " +"WHERE Content LIKE ‘%" + searchString + "%’"); • What would happen if a hacker searched for:aaaaa’ UNION SELECT Company AS URL, CreditCardNum AS Title FROM AdvertisingClients WHERE Company LIKE ‘ • This technique is known as SQL Injection and is the main reason for using PreparedStatements

21. Timeout • Use setQueryTimeOut(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 SQLExceptionis thrown • What is it good for?

22. ResultSet • ResultSet objects provide access to the tables generated as results of executing Statement queries • Only one ResultSet per Statement or PreparedStatement can be open at a given time! • The table rows are retrieved in sequence • A ResultSetmaintains a cursor pointing to its current row • next() moves the cursor to the next row

23. ResultSet 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 ResultSet • Allows to re-use the Statement that created it • Automatically called by most Statement methods

24. ResultSet Methods (cont’d) • Type getType(int columnIndex) • 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 getType(String columnName) • Same, but uses name of field • Less efficient (but may not be your bottleneck anyway) • Examples: getString(5), getInt(“salary”), getTime(…), getBoolean(…), ... • int findColumn(String columnName) • Looks up column index given column name

25. ResultSet Example Statement stmt = con.createStatement(); ResultSet rs = stmt. executeQuery("select name,age from Employees"); // Print the result while(rs.next()) {  System.out.print(rs.getString(1) + ":");  System.out.println(rs.getShort("age")); }

26. Mapping Java Types to SQL Types

27. 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 • ResultSet.wasNull(column) • For example, getInt(column) will return 0 if the value is either 0 or NULL!

28. Null Values When inserting null values into placeholders of a PreparedStatement: • Use setNull(index, Types.sqlType) for primitive types (e.g. INTEGER, REAL); • For object types (e.g. STRING, DATE) you may also use setType(index, null)

29. Result-Set Meta-Data A ResultSetMetaDatais an object that can be used to get information about the properties of the columns in aResultSetobject An example: Write the columns of the result set ResultSetMetaData rsmd = rs.getMetaData(); int numcols = rsmd.getColumnCount(); for (int i = 1 ; i <= numcols; i++) System.out.print(rsmd.getColumnLabel(i)+" ");

30. 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

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

32. Dealing With Exceptions An SQLException is actually a list of exceptions catch (SQLException e) { while (e != null) { System.out.println(e.getSQLState()); System.out.println(e.getMessage()); System.out.println(e.getErrorCode()); e = e.getNextException(); }}

33. 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

34. Transaction Management

35. 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

36. An Example Suppose that we want to transfer money from bank account 13 to account 72: PreparedStatement pstmt = con.prepareStatement("update BankAccount set amount = amount + ? where accountId = ?"); pstmt.setInt(1,-100); pstmt.setInt(2, 13); pstmt.executeUpdate(); pstmt.setInt(1, 100); pstmt.setInt(2, 72); pstmt.executeUpdate(); What happens if this update fails?

37. Transaction Lifetime • Transactions are not opened and closed explicitly • A transaction starts on 1st (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)

38. Committing a Transaction How do we commit? • Explicitly invoking Connection.commit() • Implicitly • After every query execution, if AutoCommit 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, …)

39. Automatic Commitment • A Connection object has a boolean AutoCommit • If AutoCommitis true (default), then every statement is automatically committed • If AutoCommit is false, then each statement is added to an ongoing transaction • Change using setAutoCommit(boolean) • If AutoCommit is false, need to explicitly commit or rollback the transaction using Connection.commit() and Connection.rollback()

40. 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)

41. Fixed Example con.setAutoCommit(false); try { PreparedStatement pstmt = con.prepareStatement("update BankAccount set amount = amount + ? where accountId = ?"); pstmt.setInt(1,-100); pstmt.setInt(2, 13); pstmt.executeUpdate(); pstmt.setInt(1, 100); pstmt.setInt(2, 72); pstmt.executeUpdate(); con.commit(); catch (SQLException e) { con.rollback(); …; }

42. 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)

43. Managing Large Objects In PosgreSQL-JDBC

44. 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

45. LOBs in PostgreSQL Get an in-stream to Obj. 24 Get an out-stream Obj. 53 Create a new lob LOB Manager LOBs 911 53 24 113

46. Storing LOBs (1) • Given: A binary source (file, socket, etc.), readable through an InputStream object • Goal: Store the content of the source in the table MyImages CREATE TABLE MyImages ( name VARCHAR(20) , content OID);

47. Storing LOBs (2) • Disable auto-commit (why???): • Obtain the LOB manager: • Downcast con into org.postgresql.PGConnection • Create a new LOB con.setAutoCommit(false) LargeObjectManager lobm = con.getLargeObjectAPI() int oid = lobm.create(LargeObjectManager.READ | LargeObjectManager.WRITE)

48. Storing LOBs (3) • Insert a new row, with the LOB’s oid • PreparedStatement pstmt = con.prepareStatement • ("INSERT INTO MyImages VALUES (?, ?)"); • pstmt.setString(1, imageName); • pstmt.setInt(2, oid); • pstmt.executeUpdate(); • pstmt.close();

49. Storing LOBs (4) • Fill the LOB with the input bytes • Finalize • LargeObject obj = lobm. • open(oid, LargeObjectManager.WRITE); • int bytesRead = 0; byte[] data = new byte[4096]; • while ((bytesRead = iStream.read(data)) >= 0) obj.write(data,0,bytesRead); • obj.close(); iStream.close(); con.commit();

50. Retrieving LOBs (1) • Obtain the LOB ID from the relevant row: PreparedStatement pstmt = con.prepareStatement ("select bytes from MyImages where name = ?"); pstmt.setString(1, filename); ResultSet rs = pstmt.executeQuery(); rs.next(); int oid = rs.getInt(1); rs.close(); pstmt.close();