Java’s JDBC

1. Java’s JDBC • Allows access to any ANSI SQL-2 DBMS • Does its work in terms of SQL • The JDBC has classes that represent: • database connections • SQL Statements • Result sets • database metadata • Can be connected to ODBC • We’ll review some database terminology • And look over a simple stand alone JDBC Application 95-712 Object Oriented Programming Java

2. Database Terminology • Database: A shared collection of logically related data (and a description of this data) designed to meet the information needs of an organization • Relation: A table with columns and rows • Attribute: A named column of a relation • Tuple: A row in a relation Definitions from Database Systems by Connolly, Begg, and Strachan Gary Alperson helped developed these slides and the JDBC example. 95-712 Object Oriented Programming Java

3. Sample Table 95-712 Object Oriented Programming Java

4. Attribute 95-712 Object Oriented Programming Java

5. Tuple 95-712 Object Oriented Programming Java

6. SQL • Data Definition Language (DDL) • Create tables • Modify tables • Delete (drop) tables • Data Manipulation Language (DML) • Insert data • Update data • Select data 95-712 Object Oriented Programming Java

7. Select Statement We will use this data for our examples 95-712 Object Oriented Programming Java

8. From the broker table, select the contents of the last name attribute Query SELECT lname FROM broker; Results SQL is not case sensitive. Key SQL words are capitalized and line breaks are inserted by convention. 95-712 Object Oriented Programming Java

9. From the broker table, select all attributes Query SELECT * FROM broker; Results * Acts as a wildcard 95-712 Object Oriented Programming Java

10. From the broker table, select all attributes where the last name is Smith Query SELECT * FROM broker WHERE lname = ‘Smith’; Results • Note that the string is enclosed by single quotes • The contents of a string are case sensitive 95-712 Object Oriented Programming Java

11. Use AND or OR to connect multiple where clauses Query SELECT * FROM broker WHERE lname = ‘Smith’ AND fname = ‘John’; Results 95-712 Object Oriented Programming Java

12. Example with two Tables • One-to-many relationship • Each broker may have many customers • Each customer is only affiliated with one broker • The b_id joins both tables by identifying the unique broker that each customer is associated with 95-712 Object Oriented Programming Java

13. Cartesian Product When you do a query on multiple tables, SQL begins by creating the Cartesian product, which combines each tuple from one relation from every tuple of the other relation. (Actual SQL implementations are free to compute the resulting table efficiently,i.e., the actual Cartesian product may not be generated at all.) 95-712 Object Oriented Programming Java

14. Query SELECT * FROM customer, broker WHERE broker.b_id = 1; Results SQL does not realize that the b_id in the customer table is the same as the b_id in the broker table unless you join them in the where clause. 95-712 Object Oriented Programming Java

15. Cartesian Product Query SELECT * FROM customer, broker WHERE broker.b_id = 1 AND broker.b_id = customer.b_id; Results 95-712 Object Oriented Programming Java

16. ODBC ODBC is a programming interface that enables applications to access data in database systems that use Structured Query Language (SQL) as a data standard. 95-712 Object Oriented Programming Java

17. Creating an ODBC Connection • Click on the Start button. • Choose Settings, Control Panel • Double-click on ODBC Data Sources • Choose the System DSN tab • Click Add 95-712 Object Oriented Programming Java

18. Click on the desired driver (MSAccess) • Click on the Finish button 95-712 Object Oriented Programming Java

19. Enter a Data Source Name • Click on the Select button • Locate the desired file or directory • Click OK 95-712 Object Oriented Programming Java

20. SQL Query as a Java String From both tables select the last names of all customers whose broker’s last name is Smith but whose broker ID is not 1. The SQL SELECT customer.lname FROM customer, broker WHERE broker.lname = ‘Smith’ AND broker.b_id <> 1 AND broker.b_id = customer.b_id; 95-712 Object Oriented Programming Java

21. Executing a query in Java // Statement aStatement = statement got from connection String last = “Smith”; int nonID = 1; String q = “SELECT customer.lname FROM customer, broker” + “WHERE broker.lname = \’” + last + “\’ AND broker.b_id” + “<>” + nonID + “AND broker.b_id = customer.b_id;”); ResultSet rs = aStatement.executeQuery(q); • The slash (\) is the escape character. It precedes the single quote to tell Java to include that quote in the String • The String last is outside of the double quotes, because it must be concatonated with the String sent to the database, but it falls within the single quotes so that SQL treats it as a string • nonID does not go within single quotes since it is numeric • Since the String is an SQL statement, it uses = and <> rather than == and != 95-712 Object Oriented Programming Java

22. A Simple Application // This program makes use of a stock database // and the primary JDBC classes (Connection, Statement, // ResultSet and ResultSetMetaData) import java.util.*; import java.sql.*; import java.io.*; public class TestCoolStocksDB { public static void main(String args[]) { Connection con = null; Statement s = null; 95-712 Object Oriented Programming Java

23. ResultSet rs = null; ResultSetMetaData rsm = null; String answer = ""; try { DriverManager.registerDriver( new sun.jdbc.odbc.JdbcOdbcDriver()); con = DriverManager.getConnection("jdbc:odbc:CoolStocks"); s = con.createStatement(); rs = s.executeQuery("select * from customer"); rsm = rs.getMetaData(); 95-712 Object Oriented Programming Java

24. while(rs.next()) { for(int col = 1; col <= rsm.getColumnCount(); col++) answer += rs.getString(col); } con.close(); } catch (SQLException sqle) { System.err.println("Exception caught in main:" + sqle); } System.out.println(answer); } } 95-712 Object Oriented Programming Java

25. A Simple JSP/JDBC Example stocks.mdb database schema There are three tables. Both customer and stocks have a one-to-many relationship with portfolios. The database stocks.mdb was registered with the ODBC driver as “CoolStocks” 95-712 Object Oriented Programming Java

26. Register w/ODBC Create an ODBC data source. Click on the Start button. Choose Settings, Control Panel Double-click on ODBC Data Sources Choose the System DSN tab Click Add Click on the desired driver (MSAccess) Click on the Finish button Enter a Data Source Name (I called my database CoolStocks and that name appears in the java code below) Click on the Select button Locate the directory and file containing your database. This will be the “stock.mdb” file created by Microsoft Access. Click OK 95-712 Object Oriented Programming Java

27. A Simple JSP/JDBC Example <TITLE>JSP JDBC Example 1</TITLE> </HEAD> <BODY> <!– Adapted from James Goodwill’s Pure JSP  <!-- Set the scripting language to java and --> <!-- import the java.sql package --> <%@ page language="java" import="java.sql.*" %> <%@ page import= "java.io.*" %> 95-712 Object Oriented Programming Java

28. <% Connection con = null; try { // Load the Driver class file Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Make a connection to the ODBC datasource Movie Catalog con = DriverManager.getConnection("jdbc:odbc:CoolStocks"); // Create the statement Statement statement = con.createStatement(); // Use the created statement to SELECT the DATA // FROM the customer Table. ResultSet rs = statement.executeQuery("SELECT * " + "FROM customer"); // Iterate over the ResultSet %> 95-712 Object Oriented Programming Java

29. <!-- Add an HTML table to format the results --> <TABLE BORDER="1"> <TR> <TH> Customer - ID</TH><TH>Last Name</TH> <TH>First Name</TH> <% while ( rs.next() ) { // get the id, convert to String out.println("<TR>\n<TD>" + rs.getString("id") + "</TD>"); // get the last name out.println("<TD>" + rs.getString("lname") + "</TD>"); // get the first name out.println("<TD>" + rs.getString("fname") + "</TD>\n</TR"); } 95-712 Object Oriented Programming Java

30. // Close the ResultSet rs.close(); } catch (IOException ioe) { out.println(ioe.getMessage()); } catch (SQLException sqle) { out.println(sqle.getMessage()); } catch (ClassNotFoundException cnfe) { out.println(cnfe.getMessage()); } catch (Exception e) { out.println(e.getMessage()); } 95-712 Object Oriented Programming Java

31. finally { try { if ( con != null ) { // Close the connection no matter what con.close(); } } catch (SQLException sqle) { out.println(sqle.getMessage()); } } %> </BODY> </HTML> 95-712 Object Oriented Programming Java

32. It Works! 95-712 Object Oriented Programming Java