JDBC "Java Database Connectivity"

1. JDBC"Java Database Connectivity"

2. Useful JDBC Links • Getting Started Guide: http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/GettingStartedTOC.fm.html • java.sql Package API: http://java.sun.com/j2se/1.5.0/docs/api/java/sql/package-summary.html

3. Introduction to JDBC • JDBC allows for convenient database access from Java applications • Data is transferred from relations to objects and vice-versa • databases optimized for searching/indexing • objects optimized for engineering/flexibility

4. Why Access a Database from within a Program? • As we saw last week, some queries can’t be computed in SQL. • PL/pgSQL includes more programming tools than SQL • However, sometimes using PL/pgSQL will not be suitable: • If we require object-oriented programming • If accessing the database is a small part of a large Java application • Etc. • Why not keep all the data in Java objects? • “Separation of concerns”: DBMSes concentrate on data storage and access; programs concentrate on algorithms, networking, etc.

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. access • Add the following line to your .classpath file (located in your home directory): setenv CLASSPATH ${CLASSPATH}:/usr/share/java/postgresql.jar • And then open a new shell

7. Six Steps • Load the driver • Establish the Connection • Create a Statement object • Execute a query • Process the result • Close the connection

8. JDBC Architecture (1) Driver Manager • DriverManager is provided by Java Software as part of the Java 2 Platform. • Drivers are provided by DBMS vendors. Application Driver DBMS

9. JDBC Architecture (2) • The application creates a driver instance and registers it with the DriverManager. • The DriverManager tells the driver to connect to the DB • The DriverManager keeps track of registered driver instances and their connections to DB’s. • The Driver “talks” to a particular DB through the connection

10. Connecting • Initializing a driver and registering it with the DriverManager: Class.forName(“org.postgresql.Driver"); • Getting a connection: DriverManager.getConnection(URL) • The URL is: “jdbc:postgresql://dbserver/public”,”YOUR-LOGIN”,null

11. For example Class.forName(“org.postgresql.Driver"); DriverManager.getConnection (“jdbc:postgresql://dbserver/public”,”gidi”,null);

12. Interacting with the DB • Once you have established a connection, your would like to interact with the DB • Interaction are done by sending Statements and PreparedStatements to the DB • These are used for 2 things (using different methods): • Querying the DB (executeQuery) • Changing the DB (executeUpdate)

13. Statement Statement createStatement() • returns a new Statement object • Used to send SQL commands to the DB • Created via the connection object

14. Statement query methods • stmt.executeQuery(String query): for queries that return a single ResultSet object (typically select) • stmt.executeUpdate(String query): for INSERT, UPDATE, DELETE, and SQL DDL directives

15. Compilation • When executing an SQL statement via JDBC, it is not checked for errors until it is run (Not checked during compilation)

16. executeQuery No semi-colon(;) String queryStr = "SELECT * FROM Sailors " + "WHERE Name = 'joe smith'"; Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(queryStr); • The executeQuery method returns a ResultSet object representing the query result.

17. executeUpdate String deleteStr = “DELETE FROM Sailors " + "WHERE sid = 15"; Statement stmt = con.createStatement(); int delnum = stmt.executeUpdate(deleteStr); No semi-colon(;) • executeUpdate returns the number of rows modified

18. PreparedStatement motivation • Suppose we would like to run the query SELECT * FROM Emp where name=‘moshe’; • But we would like to run this for all employees (separately), not only ‘moshe’… • Could we create a variable instead of ‘moshe’ which would get a different name every time??..

19. PreparedStatement PreparedStatement prepareStatement(String) • returns a new PreparedStatement object

20. Prepared Statements • Prepared Statements are used for queries that are executed many times with possibly different contents. • A PreparedStatement object includes the query and is prepared for execution (precompiled). • Question marks can be inserted as variables. -setString(i, value) -setInt(i, value) The i-th question mark is set to the given value.

21. PreparedStatement.executeQuery() String queryStr = "SELECT * FROM Sailors " + "WHERE Name = ? and Rating < ?”; PreparedStatement pstmt = con.prepareStatement(queryStr); pstmt.setString(1, “Joe”); pstmt.setInt(2, 8); ResultSet rs = pstmt.executeQuery(); Value to insert 1st question mark

22. PreparedStatement.executeUpdate() String deleteStr = “DELETE FROM Boats " + "WHERE Name = ? and Color = ?”; PreparedStatement pstmt = con.prepareStatement(deleteStr); pstmt.setString(1, “Fluffy”); pstmt.setString(2, "red"); int delnum = pstmt.executeUpdate();

23. Will this work? PreparedStatement pstmt = con.prepareStatement(“select * from ?”); pstmt.setString(1, "Sailors"); No! We may put ? only instead of values

24. Why use preparedStatement? • In most cases, you can use a regular statement and just change the string you send to executeQuery each time • You have to be careful

25. Why use preparedStatement? • Suppose google worked without PreparedStatements, they would implement search queries as something like: Statement s; s.executeQuery(‘select URL,Title from internet where content like ‘%”+searchString+”%’”); • What would happen if a hacker searched for: bla bla’ UNION select company as URL, CreditCardNumber AS title from advertisingClients where company like ‘ Example taken from dbi course

26. ResultSet (1) • A ResultSet is an object which contains the result of a query - a “table”. • At most one ResultSet per Statement can be open at the same time(!!). • A ResultSet maintains a cursor pointing to its current row of data. • The 'next' method moves the cursor to the next row • As of JDBC 2.0, scrollable ResultSets are available, which also include ‘previous’, ’first’, ‘last’, etc..

27. ResultSet (2) • resultSet methods work on the current row. • The cursor is positioned before the first row upon creation.

28. ResultSet (3) Statement stmt= con.createStatement(); ResultSet rs = stmt.executeQuery ( "SELECT * FROM Table1"); while (rs.next()) { //something… }

29. ResultSet methods • Getting the value in some column (for the current row): • getString(int columnNum); • getString(String columnName); • getInt(int columnNum); • getInt(String columnName); • Etc… • To check if NULL was returned, you have to use wasNull() on the ResultSet after getting the value. String s = rs.getString(“column1");

30. Example revisited Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM Table1"); // retrieve and print the values for the current row while (rs.next()) { int i = rs.getInt("a"); String s = rs.getString("b"); float f = rs.getFloat("c"); System.out.println("ROW = " + i + " " + s + " " + f); }

31. ResultSetMetaData An object created by the ResultSet which holds information about its columns ResultSetMetaData rsmd = rs.getMetaData(); int numcols = rsmd.getColumnCount(); for (int i = 1 ; i <= numcols; i++) { if (i > 1) System.out.print(","); System.out.print(rsmd.getColumnLabel(i)); }

32. Printing Query Output: Result Set (2) while (rs.next()) { for (int i = 1 ; i <= numcols; i++) { if (i > 1) System.out.print(","); System.out.print(rs.getString(i)); } System.out.println(""); } • getString() is allowed to access all simple JDBC types

33. Cleaning Up After Yourself • Remember to close the Connections, Statements, PreparedStatements and ResultSets con.close(); stmt.close(); pstmt.close(); rs.close();

34. Dealing With Exceptions catch (SQLException e) { //human readable message about the exception System.out.println(e.getMessage()); //String describing the reason of the exception System.out.println(e.getSQLState()); //driver-dependent code for the exception System.out.println(e.getErrorCode()); }

35. Mapping SQL and Java Types • SQL and Java data types are not identical • There are significant variations between the SQL types supported by different database products • JDBC defines a set of generic SQL type identifiers in the class java.sql.Types • The driver is responsible for mapping between the DB SQL types and JDBC SQL types

36. Transactions in JDBC

37. Transactions • Transaction = 2 or more statements which must all succeed (or all fail) together • If one fails, the system must reverse all previous actions • Aim: don’t leave DB in inconsistent state halfway through a transaction • COMMIT = complete transaction • ROLLBACK = abort

38. Example • Suppose 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?

39. Transaction Management • The connection has a state called AutoCommit mode • if AutoCommit is true, then every statement is automatically committed • if AutoCommit is false, then every statement is added to an ongoing transaction • Default: true

40. AutoCommit • If you set AutoCommit to false, you must explicitly commit or rollback the transaction using Connection.commit() and Connection.rollback() con.setAutoCommit(boolean val)

41. 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(); }