CS636 Homework 1 Review, Setup, and Intro to JDBC

Due Monday, Sept. 18, in class, on paper. Be sure to get a UNIX/Linux account for cs636 by Friday, 9/8, to list your username for the needed Oracle and mysql accounts, and access to topcat.cs.umb.edu.

1.  Basic HTML and URLs, intro to web apps.

--Learn about absolute and relative URLs, say by looking at this tutorial on URLs and looking at the examples in Murach, pg. 101.
--Read Murach, Chap. 1, through pg. 17 for an intro to web apps.
--Read Murach, Chap. 4 through pg. 105. We will go on to CSS and HTML forms in the next homework.

Use a plain editor like emacs or Notepad to compose a web page test1.html with page title "Mytitle", contents entitled "Important links", and a relative link to a copy of this file (hw1.html), and an absolute link to the root of our departmental website, with appropriate descriptive text for the user to see.  Include the text of test1.html in your homework submission.

2. Review SQL. Read Murach pp. 364-373. Go to sqlzoo.net,  Pull down the Tutorials menu, and select the following tutorials:

Show your answers (copy and paste the SQL into your homework file). If an error message seems useless, try another database for the query--it might give better error messages.

3. Review Java OO principles, specifically the idea that all Java classes are subclasses of the Object class, and thus must have all the methods of the Object class. You can see the Object class API (and any other standard class) by following the link "JDK API" on the class web page under Resources Java (6th line down under Resources). Choose “Object” in the pane titled All Classes, and the API will be displayed on the right.
a. What are the three most important Object methods?  (Hint: their names start with e, h, and t). Because all objects have these methods, we can use HashSet to contain any set of objects (we'll stick to sets of same-type objects in our work).
b. Show equals in use by writing one if (…) that checks whether s String s is equal to a String t, character by character.  Explain what this comparison is doing if s = “abc” and t = “abx”.  Compare this to what happens in if (s == t).
c.  Find the hashCode and toString values for String s = “abc”.  Also for the Integer of value 6.

4. Java Beans. a. Write a small Java class Box1.java that has private int fields x, y for the dimensions of the (2-dimensional) box in mm. Give it getters and setters for x and y, and a simple constructor that takes x and y values, but no other methods. This is a simple “Java Bean”.  For Box1 objects b and c, separately created but both describing 10x12 boxes, what is b.equals(c)? What is being compared here?
b. For Java class Box2.java, start from the Box1.java code and add an equals method that compares the x’s and y’s of the two Box2 objects to see if they are both the same or not.  Make sure your equals method overrides Object.equals by using the annotation @Override (it’s easy to go wrong here). For an example, see http://www.geeksforgeeks.org/overriding-equals-method-in-java/.Write a hashCode method that is consistent with your equals method.  Consistent here means satisfying the second bulleted requirement under the Object.hashCode documentation, namely “If two objects are equal according to the equals(Object) method, then calling the hashCode method on each of the two objects must produce the same integer result.”
c. Finally, write a toString method that returns “10x12” for a 10 by 12 box.  For Box2 objects b and c, separately created but both describing 10x12 boxes, what is b.equals(c)?

5. Review Java Collection classes.  Look at the Collection Framework home page at Oracle, and from there the Collections Framework Overview and Collections Tutorial, for Java 8.  .
a.  What are the two most important concrete classes that are available for the Set interface?  the Map interface?
b.  Explain how you can find all the elements of a given Set object.  Does your answer also apply to Lists?  other things?  what class of objects?
c.  Explain how you can find all the keys of a given Map.
d.  Write a Java fragment that creates a Map from String to Integer. Add the association "x" -> 6, that is, key "x" maps to Integer 6.

6.  Start learning (or review) JDBC. Read Murach, Chap. 12 through pg. 387, and the Oracle tutorial on basic JDBC, specifically the sections named “Setting Up Tables” and “Retrieving and Modifying Values from Result Sets”, mainly the first example.
a. What Java classes (include package name) support JDBC and are used in the Oracle tutorial section “Setting Up Tables”?  For each, briefly explain what it provides. Note that con is assumed to be an available field of type Connection.
b. Do you need a new Connection for each SQL statement?  Explain.
c.  Find the JDBC API documentation among the usual JSE Javadocs at http://docs.oracle.com/javase/8/docs/api/.  In particular, read Connection.close() and explain what it means.  The "resources" here involve the TCP/IP connection to the database, an OS resource not directly managed the the Java vitual machine (JVM.)
d.  Explain how to use the "getXXX" methods.  What getXXX methods would you normally use with a VARCHAR(100) column?  a FLOAT column?  a DECIMAL(7,2) column? a TIMESTAMP column? Explain what these SQL datatypes mean, and give two examples of valid SQL literal values for each. For detailed info on JDBC and SQL datatypes, see  https://docs.oracle.com/javase/1.5.0/docs/guide/jdbc/getstart/mapping.html.

Oracle.  After you have verified your Oracle account is working by logging in to system topcat.cs.umb.edu and using sqlplus as shown in DatabaseSetup.html, try accessing it with the supplied program JdbcCheckup. See Access to cs.umb.edu systems from offsite for instructions on logging in from offsite. Note that you can run JdbcCheckup on any of our UNIX/Linux hosts, or on your home PC, and reach Oracle on dbs3. From outside cs.umb.edu, you need to set up a "tunnel" to dbs3, as described in Database Setup. Once JdbcCheckup works, log into topcat and run sqlplus and record what tables have been created by the JDBC program by using the catalog query "select * from user_tables" or just "select table_name from user_tables" to get a less verbose report. Note that the jars supplied with JdbcCheckup work equally well on Windows/Linux/MacOS as on UNIX/Linux. Try it out on both your home PC and your departmental Linux account.

8. Mysql.
User ronghui, for example, will have an account on mysql on topcat with username ronghui, password ronghui, and a database named ronghuidb.  To access it, login to topcat.cs.umb.edu and use the command:

mysql –u ronghui –D ronghuidb –p

and enter ronghui at the password prompt.  Another way is shown on pg. 351 of Murach:

mysql –u ronghui –p
Enter password: ronghui
use ronghuidb

Similarly run JdbcCheckup on your mysql account.  You can log in on topcat and use the mysql command to look at the database. Also try out hsqldb (see header comment on JdbcCheckup.java for directions). Record your success or problems. 

9.a. Try out the Data Source Explorer view in eclipse. Click on the "New Connection Profile" icon (it has a little plus on it) in the Data Source Explorer view, and add a new Connection to localhost (and have the tunnel working), with driver MySQL (with driver jar from the jdbc directory), Database xxxxdb, URL jdbc:mysql://localhost/xxxxdb, and User name xxxx, where xxxx is your username.  See your welcome table there. Record your success or problems.
b. Similarly set up an eclipse Connection to Oracle and view your welcome table there, and similarly for hsqldb (you need to run the hsqldb server separately as detailed in the header comment to JdbcCheckup.java.)
c. Report on your DevelopmentSetup progress and/or problems.  Show your final "env" and "set" output, or the relevant subset of it. You may use Netbeans rather than eclipse if you want, but class demos and directions will be using eclipse. Also, Netbeans does not support multi-directory source comparison, which we will occasionally use.