Technologies for an Information Age: . opennet JDBC Tutorial

1. Technologies for an Information Age:.opennetJDBC Tutorial Computer Science, Informatics, Physics Indiana University Bloomington IN 47404 gcf@indiana.edu Fall Semester 2001 MW 5:00 pm - 6:20 pm CENTRAL (not Indiana) Time Bryan Carpenter and Geoffrey Fox PTLIU Laboratory for Community Grids

2. Introduction • JDBC—usually interpreted as an acronym for Java Database Connectivity—was introduced in 1996, and revamped in 1998 for Java 2. • It is an API definition intended for implementation as a Java interface to an SQL database. • SQL (“sequel”) is the Structured Query Language, originally defined by IBM in the 70’s, standardized by ANSI/ISO in 1992. • SQL is in turn based on the relational database model. It is implemented (with much variation) by many vendors of RDBMS (Relational Database Management System) software. • First commercial implementation of SQL: Oracle, 1979.

3. These Lectures • Start with a naïve introduction to SQL. • Followed by an introduction to basics of JDBC. • Continue with an example invoking SQL commands through a JSP-based Web application. • To run the examples exactly as presented here you will need to have MySQL installed. • An earlier version of these lectures was based on Oracle instead, and that will show through—responses of the MySQL interpreter may not always be formatted exactly as shown in these slides.

4. Installing MySQL (circa 9/5/01) • Go to www.mysql.com (notmysql.org!) • Download the Zip file, MySQL 3.23.44 for Windows. File is mysql-3.23.44-win.zip. • If you don’t have Winzip archiving program or equivalent, you will need to download that as well, e.g. from download.com, keyword Winzip. • From Windows Explorer, double-click the zip-file to unpack it. • Double-click the command \mysql\bin\winmysqladmin.exe to start the MySQL administration tool. • Click on the traffic-light icon to start the database server itself (“Win NT” → “Start the server standalone”). • Execute the command \mysql\bin\mysql to start the SQL interpreter. • Before issuing SQL commands, you will have to create and select a named database in this server (c.f. end of SQL section in these lectures).

5. SQL

6. Relations • Mathematically, a relation is a subset of a product space. Equivalently, it is a set of “tuples” with entries from some fixed domains. • In mathematics, the most important relations tend to be binary relations between entities of the same type: LessThan, on {0, 1, 2, 3}  {0, 1, 2, 3}: { (0, 1), (0, 2), (0, 3), (1, 2), (1, 3), (2, 3) } Contains, on {sunder, sun, sundry, dry}  {sunder, sun, sundry, dry}: { (sunder, sunder), (sunder, sun), (sun, sun), (sundry, sundry), (sundry, sun), (sundry, dry), (dry, dry) } SameColor, on {tomato, cherry, banana}  {tomato, cherry, banana}: { (tomato, tomato), (tomato, cherry), (cherry, tomato), (cherry, cherry), (banana, banana) } Examples respectively of total order, partial order and equivalence relation.

7. General Relations • For databases, we are nearly always interested in n-ary relations between distinct domains, e.g. assume: Login = {wcao, Flora, Fulay, zyr98210, jin0328} LastName = {Cao, Flora, Fulay, Zhang} Dept = {CSIT, EE, CS} then we may define the relation: Students, on Login  LastName  Dept : { (wcao, Cao, CSIT), (Flora, Flora, EE ), (Fulay, Fulay, CS ), (zyr98210, Zhang, CS ), (jin0328, Zhang, CS ) }

8. SQL Tables • SQL is inspired by this mathematical model. • Names are changed, and for pragmatic reasons the SQL model does not try to be mathematically pure. • A relation is called a table. • The individual tuples in the relation are called rows. • The domain sets of the relation are called by columnsorattributes. • Typing is not as strict as the mathematical model suggests. We define the allowed values of a column in terms of a limited set of predefined types, rather than actual sets.

9. Creating a Table • A possible SQL command to create a table for our Students relation is: CREATE TABLE students ( login VARCHAR(10), lastname VARCHAR(20), dept VARCHAR(5) ) • Things to note: • Case is not significant here. By convention we use upper case for SQL keywords. • White-space, including line-breaks, is not significant. • The CREATE TABLE statement is syntactically like a class definition, defining columns (c.f. fields) and their types. • In this analogy, rows of the table would be like class instances.

10. Column Types • There are a limited set of types for column data. • Unfortunately (although there is supposed to be a standard) in practice the available types are completely dependent on the vendor. • In JDBC the types are standardized and include: INTEGER Typically 32-bit integer. FLOAT(N) Floating point with N bits of precision. CHARACTER(N) Fixed length string, N characters. VARCHAR(N) Variable length string, maximum N. BLOB A large binary object.

11. MySQL • To create a table using the MySQL interpreter, type then: mysql> create table students ( 2 login varchar(10), 3 lastname varchar(20), 4 dept varchar(5) 5 ) ; Table created • In this interpreter commands are terminated by a semicolon.

12. Inserting a Row • The SQL command for adding a row to a table is, e.g.: INSERT INTO students VALUES (‘wcao’, ‘Cao’, ‘CSIT’) • In mysql I enter: mysql> insert into students values ( 2 ‘wcao’, 3 ‘Cao’, 4 ‘CSIT’) ; 1 row created. • The following examples assume the other tuples from the Student relation are entered in the same way.

13. The SELECT command • To view the Students table I can use the SELECT command: SELECT * FROM students The asterisk is a wildcard that causes all columns to be display. • In mysql: mysql> select * from students ; LOGIN LASTNAME DEPT ----------------- ----------------------------- ------------ wcao Cao CSIT Flora Flora EE Fulay Fulay CS zyr98210 Zhang CS jin0328 Zhang CS 5 rows selected

14. Displaying Chosen Columns • To limit the set of columns printed, specify them in the SELECT command, e.g.: SELECT login, lastname FROM students • In mysql: mysql> select login, lastname from students ; LOGIN LASTNAME ----------------- ----------------------------- wcao Cao Flora Flora Fulay Fulay zyr98210 Zhang jin0328 Zhang 5 rows selected

15. Displaying Chosen Rows • To limit the set of rows printed, I add a WHERE clause: SELECT * FROM students WHERE dept=‘CS’ Other kinds of tests that can appear in the WHERE clause will be described later. • In mysql: mysql> select * from students where dept=‘CS’ ; LOGIN LASTNAME DEPT ----------------- ----------------------------- ------------ Fulay Fulay CS zyr98210 Zhang CS jin0328 Zhang CS 3 rows selected

16. A Second Table • The following examples assume I define a second table by: CREATE TABLE departments ( abbrev VARCHAR(5), name VARCHAR(50) ) and add the rows: { (‘CSIT’, ‘Computational Science and Information Technology’), (‘EE’, ‘Electrical Engineering’), (‘CS’, ‘Computer Science’) }

17. Selecting from Multiple Tables • A SELECT command can display data from more than one tables: SELECT * FROM students, departments • By itself this just produces a mess. In mysql: mysql> select * from students, departments ; LOGIN LASTNAME DEPT ABBRE ----------------- ----------------------------- ------------ ---------- NAME -------------------------------------------------------------------- wcao Cao CSIT CSIT Computational Science and Information Technology Flora Flora EE CSIT Computational Science and Information Technology Fulay Fulay CS CSIT Computational Science and Information Technology . . . 15 rows selected

18. Joins • The previous query returned 15rows. • It simply yielded a “Cartesian product” of the two relations, with every row of students being combined with every row of departments. • In itself this is not a useful result. But is a basis from which to add a WHERE clause to select out some meaningful combinations of values from the two tables. • If two tables appearing in the same statementsharesome identical column names, can disambiguate by using qualified names, e.g.: students.login, students.lastname, students.dept, etc.

19. References Between Tables • Here is a meaningful query involving the two tables: SELECT login, name FROM students, departments WHERE dept=abbrev • In mysql: mysql> select login, name from students, departments 2 where dept=abbrev ; LOGIN NAME ----------------- -------------------------------------------------------------------------- Fulay Computer Science zyr98210 Computer Science jin0328 Computer Science wcao Computational Science and Information Technology Flora Electrical Engineering 5 rows selected

20. Primary Keys and Foreign Keys • This kind of cross-reference is so important that SQL provides syntax to allow automatic “integrity checks” and allow optimizations. • We can change the abbrev column to be a primary key of the departments table by the following SQL command: ALTER TABLE departments ADD PRIMARY KEY (abbrev) • We can also add a constraint that any allowed value in the dept column of students must be a valid primary key in the departments table by the SQL command: ALTER TABLE students ADD FOREIGN KEY (dept) REFERENCES departments (abbrev)

21. Integrity Checks • The system will now forbid addition of values to the dept column of students that do not correspond to values in the abbrev column of departments. • For example, I can try to change the dept column of the row describing wcao by the SQL UPDATE command: UPDATE students SET dept=‘IE’ WHERE login=‘wcao’ • Because of the constraints, Oracle (for example) will refuse to make this update. In sqlplus: SQL> update students set dept=‘IE’ where login=‘wcao’ ; ERROR at line 1 : ORA-02291: integrity constraint violated - parent key not found. • Incidentally this example illustrates the use of UPDATE command, which changes attributes of existing rows.

22. MySQL Idiosynchrasies • Last two slides will work as described for Oracle. • MySQL seems to be more pernickety about adding a primary key to an existing table. I first had to specify: alter table departments modify abbrev varchar(5) not null ; to redundantly assure the system that the primary key would never go null. • Note SQL also allows you to specify these column constraints in the CREATE TABLE command. • Also note, MySQL does not currently implement the integrity checks for foreign keys, described in the preceding slide. • The system accepts the FOREIGN KEY constraint, but ignores it. • The documentation says such checks will be implemented in the version 4.1

23. Other Useful Commands • . . . in addition to CREATE, SELECT and UPDATE, which were illustrated earlier: DELETE FROM table_name WHERE condition Deletes selected rows. DROP TABLE table_name Removes a table. DESCRIBE table_name Describes columns of a table. COMMIT Save changes made in this transaction. ROLLBACK Undo changes made in this transaction.

24. Conditions • Conditions in WHERE clauses are Boolean expressions built from: • Comparision operators =, <>, <, <=, >, >= • Boolean operators AND, OR, NOT • The LIKE operator. • The LIKE operator compares a column value to a pattern. • In the pattern the wildcard % represents zero or more characters. • The wildcard _ represents a single character.

25. MySQL Administrivia • To create a database called dbname: mysql> create database dbname ; • To start using that database: mysql> use dbname ; • To get a list of all current databases: mysql> show databases ; • To get a list of all current tables: mysql> show tables ; • To show the column characteristics of a table called tablename: mysql> describe tablename ;

26. JDBC

27. A Simple Example import java.sql.* ; public class ShowStudents { public static void main(String args[]) throws Exception { String url = “jdbc:mysql://localhost/opennet_fall01” ; System.setProperty(“jdbc.drivers”, “org.gjt.mm.mysql.Driver”) ; Connection conn = DriverManager.getConnection(url) ; Statement stat = conn.createStatement() ; ResultSet rs = stat.executeQuery(“SELECT * FROM students”) ; while(rs.next()) System.out.println(rs.getString(1) + “ ” + rs.getString(2) + “ ” + rs.getString(3)) ; conn.close() ; } }

28. Remarks • To compile and run this example you must have a JDBC driver installed for the database, and it must be on your class path. • For MySQL, go to http://mmmysql.sourceforge.net/ and download the file mm.mysql-2.0.7-you-must-unjar-me.jar. • On Windows, save it in the folder C:\, for example. • In this folder, run the command jar xvf mm.mysql-2.0.7-you-must-unjar-me.jar • You should find the class file for org.gjt.mm.mysql.Driverinside the C:\mm.mysql-2.0.7\ folder. • Create a CLASSPATH environment variable with value: .;c:\mm.mysql-2.0.7

29. Running ShowStudents • If we run ShowStudents we may see something like: C:\ptliuegs> java ShowStudents wcao Cao CSIT Flora Flora EE Fulay Fulay CS zyr98210 Zhang CS jin0328 Zhang CS • Effect is essentially like typing the command select * from students ; directly into the MySQL interpreter.

30. Classes in the Example • The example introduces the following classes and interfaces from java.sql: DriverManager Manages a set of JDBC drivers. Connection A connection or session with a specific database. Context in which SQL statements are executed and results returned. Statement Object used for executing an SQL statement. ResultSet Provides access to a table of data generated by executing a statement.

31. The Driver Manager • The driver manager sits between the JDBC application and one or more JDBC drivers. • A JDBC driver contains vendor-specific code to interface to a particular back-end database. • On initialization, the class DriverManager will try to load driver classes referenced in the jdbc.drivers property. • One can also load JDBC drivers explicitly, e.g.: Class.forName(“org.gjt.mm.mysql.Driver”) ;

32. Making a Connection • There are several getConnection() methods on DriverManager with different argument lists. The one we used is: static Connection getConnection(String url) If you are using a non-default DB account and password, use instead: static Connection getConnection(String url, String username, String password)

33. Database URLs • Generally speaking the syntax of the URL should follow the normal conventions for Internet URLs: protocol // host : port / name • protocol will be a sub-protocol of jdbc:, e.g. jdbc:mysql: • hostand portare self-explanatory. • nameis the name of the database on the host. • The precise format depends on the driver. Oracle JDBC URLs follow the general pattern, but they use different separators. • @ and: in place of//and/.

34. The Connection Interface • The Connection interface includes many methods. • Our first example only uses two simple ones: Statement createStatement() Creates a Statement object for sending SQL statements to the database. void close() Releases database connection and associated JDBC resources.

35. The Statement Interface • Object used for executing an SQL statement. • The most important methods for us will be: ResultSet executeQuery(String sql) throws SQLException Executes an SQL statement that returns a single result set. Typically sql will be a SELECT statement. int executeUpdate(String sql) throws SQLException Executes an SQL statement that returns nothing. Typically sql will be an INSERT, UPDATE or DELETE statement. Result is number rows modified. • Note only one ResultSet can exist per Statement. If you need to interleave queries, use multiple Statement objects. • Other useful methods: • addBatch(), executeBatch() to batch several SQL commands.

36. The ResultSet Interface • A result set behaves something like an Enumeration or Iterator. This allows to iterate over the set of rows returned by a query. • The next() method is used to move to the first row, then all subsequent rows. • There is a large number of methods of the form: XXX getXXX(int columnIndex) that extract the contents of a column in the current row. • The one we will use most is getString(), which interprets the column as a String. • Note columnIndex starts at 1, not zero. • ResultSet has many other methods. Many were added in JDBC 2.0.

37. A Web Front-end to SQL

38. Example: Web Front End to SQL • We will go through a fairly substantial example that combines JDBC with JSP. • This Web application will allow a user to connect to a MySQL database on the server host, and submit arbitrary SQL commands and queries. • Besides introducing a couple of new JDBC features, it will illustrate (in more depth than the vending machine examples) the structure of a Web application involving multiple JSP pages.

39. Preliminaries • Before making your database accessible through a Web server, you should set up some password protection. • First, use the mysqladmin command in mysql\bin\ to set the database administrator password, e.g.: C:\mysql\bin> mysqladmin -u root password admin-password • Can still connect to the database from the local host without specifying a user name or password, but other computers can no longer connect to your database with dangerous privileges. • Example on following slides assumes a special account is created with free access to one specified database on the server. The account is created by issuing the command: mysql> GRANT ALL ON opennet_fall01.* TO tomcat@localhost IDENTIFIED BY “password” ; • The account name is tomcat, database is opennet_fall01. • Issue this command from within the mysql interpreter.

40. A First Screen • The introductory screen to our application is a static HTML form, sqllogin.html: <html> <head></head> <body> <h1>Connect to SQL database</h1> <form method=“post” action=“sqlconnect.jsp”> SQL password: <input type=“password” name=“password” size=“20”/> <p/> <input type=“submit” value=“Connect to database”/> </form> </body> </html>

41. Browser displays:

42. Remarks • The form prompts for a password • We will see that this is the password associated with a database account for a user called tomcat. • This is sent using the POST method (for limited privacy) to the JSP page sqlconnect.jsp.

43. The sqlconnect.jsp JSP page <%@ page errorPage=“sqlerror.jsp” %> <jsp:useBean id=“sql” class=“mybeans.SQLBean” scope=“session”/> <jsp:setProperty name=“sql” property=“password”/> <% sql.connect() ; %> <html><head></head> <body> <jsp:include page="sqlcommand.jsp"/> </body> </html> • This page establishes a session bean, sql, class mybeans.SQLBean. • We assume this bean has properties and methods: • A write-only property password, set from the form parameter of the same name. • A void, no-argument method called connect(), which attempts to establish the database connection. Further properties and methods will be introduced as needed. • After connection, bulk of document is generated by sqlcommand.jsp.

44. Aside: An Unfortunate Feature • If a form field is left empty, the parameter value sent to the server is the empty string, “”. • Unfortunately the jsp:setProperty action does nothing in this case. • In our example this has the odd effect that if you return to the login page and submit with an empty password while the old session is still valid, the connection succeeds because the old password is remembered by the bean (setPassword() is not called to change it.) • It’s not clear how serious a security risk this is, but if you are concerned, replace the use of the jsp:setProperty action with an explicit scriptlet: <% sql.setPassword(request.getParameter("password")) ; %>

45. The sqlerror.jsp JSP page • The page directive in sqlconnect.jsp established an error page sqlerror.jsp. • If any bean method or accessor used in the page throws an exception, control is transferred to the page sqlerror.jsp: <%@ page isErrorPage=“true” %> <html><head></head> <body> SQL error: <%= exception.getMessage() %> </body> </html> • If a page is declared to be an error page through the page directive, the scripting variable exception is available for use.

46. The sqlcommand.jsp JSP page <form action=“sqlquery.jsp”> <h1>SQL <em>query</em></h1> <textarea name=“query” cols=“60” rows=“3”></textarea><p> <input type=submit value=“Submit Query”/> </form> <form action=“sqlupdate.jsp”> <h1>SQL <em>update</em></h1> <textarea name=“update” cols=“60” rows=“3”></textarea><p> <input type=submit value=“Submit Update”> </form> <form action=“sqlclose.jsp”> Close database connection: <input type=submit value=“Close”/> </form>

47. Submitting a Command

48. Remarks • This pages just prints three forms, for SQL queries, SQL action commands, and disconnecting, respectively. • Clearly one could use a static HTMLpage to do this. There is a subtle reason why one may prefer a JSP page. • The JSP session tracking mechanism preferentially uses cookies. If this fails, because the user has configured his/her browser to reject cookies, the JSP engine can still use URL-rewriting for session tracking. • URLs in dynamically generated pages (in our example the relative URLs sqlquery.jsp, sqlupdate.jsp and sqlclose.jsp) are extended with a session ID, which is thereby returned to the server when the forms are submitted (this happens automatically, if necessary, for a JSP page). • The server can thus determine session context, even without cookies. • This is only possible if the HTML is dynamically generated; crossing a staticsqlcommand.htmlpage would lose the session identity. Without cookies, the application would fail.

49. Handling Queries • The most complicated page in our application is sqlquery.jsp. • This will execute an arbitrary SELECT command, and print out the results as an HTML table. • We assume the SQLBean has: • A write-only trigger property query. Setting this property runs the query, and initializes: • a read-only property numCols, representing the number of columns in the result, and • an indexed read-only property colNames, representing the labels for the columns in the result. Setting query also prepares the bean for a sequence of calls to: • a boolean, no-argument method called nextRow(), which initializes: • an indexed read-only property, colValues, representing the values of the columns in the next row of the result.

50. The sqlquery.jsp JSP page <%@ page errorPage=“sqlerror.jsp” %> <jsp:useBean id=“sql” class=“mybeans.SQLBean” scope=“session”> <jsp:forward page=“sessionerror.html”/> </jsp:useBean> <jsp:setProperty name=“sql” property=“query”/> <html><head></head> <body> <table border cellspacing=0 cellpadding=5> <% int numCols = sql.getNumCols() ; out.println(“<tr bgcolor=lightblue>”) ; for(int i = 0 ; i < numCols ; i++) out.println(“<td>” + sql.getColNames(i) + “</td>”) ; out.println(“</tr>”) ; while(sql.nextRow()) { out.println(“<tr>”) ; for(int i = 0 ; i < numCols ; i++) out.println(“<td>” + sql.getColValues(i) + “</td>”) ; out.println(“</tr>”) ; } %> </table> <jsp:include page=“sqlcommand.jsp”/> </body> </html>