## Solutions to CS636 hw1

1-

HTML mytest.html:

<html>

<title> Mytitle </title>

<body>

<ul>

<!-- A relative link to a copy of this file called copyOfTest1.html -->

<li><a href="copyOfTest1.html"> Link to a copy of test1.html </a>

<!-- An absolute link to the root of CS department of UMB -->

<li><a HREF="http://www.cs.umb.edu/"> Computer Department at UMass/Boston </a>

</ul>

</body>

</html>

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

• Select from nobel:  4, 7  (number them  1-4, 1-7)  For 7, use LIKE
• Select within select:  2, 4. 6  (2-2, etc.)
• Sum and count: 2, 4, 6
• More Joins: 3, 6, 7, 9

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.

Answer to Question 2: Note that SQL is truly caseless for keywords, so “select” is fine here.

1--4

Give the name of the 'Peace' winners since the year 2000, including 2000.

SELECT WINNER

FROM nobel

WHERE subject= 'Peace'

AND yr>=2000

1--7

Show the winners with first name John

SELECT *

FROM nobel

WHERE winner like 'John%'

2--2

Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.

select name from world

where continent = 'Europe' and gdp/population > (select gdp/population from world where name = 'United Kingdom')

2--4

Which country has a population that is more than Canada but less than Poland? Show the name and the

population.

select name, population

from world

where population>(select population from world where name='Canada')

and

population<(select population from world where name= 'Poland')

2--6

Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

select name from world

where gdp > all (select gdp from world where continent = 'Europe' and gdp is not null)

3--2

List all the continents - just once each.

SELECT DISTINCT (continent) from world

3--4

How many countries have an area of at least 1000000

SELECT COUNT(name) FROM world WHERE area>=1000000

3--6

For each continent show the continent and number of countries.

SELECT continent, COUNT(name)

FROM world

GROUP BY continent

4-3

select distinct m.id, title, yr from actor a, movie m, casting

where a.id=casting.actorid and

m.id = casting.movieid and m.title like '%Star Trek%'

4--6

Obtain the cast list for 'Casablanca'. Use the id value that you obtained in the previous question.

SELECT name FROM actor JOIN casting

ON (actor.id = casting.actorid)

WHERE movieid =(SELECT id FROM movie WHERE title='Casablanca')

or

SELECT name FROM actor JOIN casting

ON (actor.id = casting.actorid)

WHERE movieid=11768

using a separate query for the id (question 5)
SELECT id FROM movie WHERE title='Casablanca'

4--9

List the films in which 'Harrison Ford' has appeared, not in the starring role [Note: the ord field of casting gives the position of the actor. If ord=1 then this actor is in the starring role]

SELECT m.title

FROM movie m, casting c, actor a

WHERE a.name='Harrison Ford'

AND c.movieid=m.id

AND c.actorid=a.id AND c.ord > 1

3.

a.  equals, hashCode, and toString

b.  String s = “abc”;

String t = “abx”;

if (s.equals(t)) … checks first chars a vs a, same, then second chars b vs b, same, then checks third chars c vs x, different, so equals is false.

if (s == t)…  This checks the refs s and t, which must be different because they are different Strings, so this evaluates to false, unless the Java system has detected that they have the same contents and optimized by pointing both refs to one place in memory. You should never depend on == for checking String equality.

c.  (Code is not required, but here is some to prove the values)

String s = "abc";

Integer i = new Integer(6);

System.out.println("s hashcode is " + s.hashCode());

System.out.println("s toString is " + s.toString());

System.out.println("i hashcode is " + i.hashCode());

System.out.println("i toString is " + i.toString());

Outputs:

s hashcode is 96354

s toString is abc

i hashcode is 6

i toString is 6

4. a.

public class Box1 {

private int x;

private int y;

// getters and setters created by eclipse:

// select fields, right-click Source>Generate Getters and Setters

public int getX() {

return x;

}

public void setX(int x) {

this.x = x;

}

public int getY() {

return y;

}

public void setY(int y) {

this.y = y;

}

public Box1(int x, int y) {

this.x = x;

this.y = y;

}

}

For Box1 b and Box1 c, separately created, b.equals(c) is false, since the refs are addresses of different memory locations.

b.

public class Box2 {

private int x;

private int y;

// getters and setters created by eclipse:

// select fields, right-click Source>Generate Getters and Setters

public int getX() {

return x;

}

public void setX(int x) {

this.x = x;

}

public int getY() {

return y;

}

public void setY(int y) {

this.y = y;

}

public Box2(int x, int y) {

this.x = x;

this.y = y;

}

// generated by eclipse, then cleaned out useless comments

// right-click>Source> generate hashCode and equals

// don't select "use instance of", that's not really correct

// hashCode: this is just one way to do it.

// another: return x&y; or even: return x+y;

@Override

public int hashCode() {

final int prime = 31;

int result = 1;

result = prime * result + x;

result = prime * result + y;

return result;

}

@Override

public boolean equals(Object obj) {

if (this == obj)

return true;

if (obj == null)

return false;

if (getClass() != obj.getClass())

return false;   // added comment: only same-class objects can be equals

Box2 other = (Box2) obj;

if (x != other.x)

return false;

if (y != other.y)

return false;

return true;

}

@Override

public String toString() {

return x + "x" + y;

}

}

c.  @Override

public toString() {

return "" + x + "x" + y;

}

For Box2 b and Box1 c, separately created and both 10x12, b.equals(c) is true, since they are of the of the same class and the x and y fields are the same.

----------------------------------------------------------------------

5. Review Java Collection classes.  Look at the Collections Framework
Overview and Collections Framework Annotated Outline docs at Sun,
for Java 8. Also the tutorial. Note that Sets are particularly
important for us, since database tables are sets of rows.  Maps
are also very useful.

a. What two most important concrete classes that are available for the Set interface?  the Map
interface?
For Set: HashSet, TreeSet
For Map: HashMap, TreeMap

b. Explain how you can find all the elements of a given Set object.
of objects?

Get an Iterator from the Set and use it to travese through the elements in the Set. Or take

advantage of the fact that the Set ISA Iterable and use the enhanced for loop.  Same for List. Collections classes that implement Collection all must provide iterators and are Iterable.

Enhanced for loop syntax:

for (ElementType e: setVariable)

c. Explain how you can find all the keys of a given Map.

By calling the keySet method of the map, a Set of all the keys for the map are returned.  The user can then use an iterator to traverse through it and get the individual keys, or an enhanced for loop. Note that Map itself is not a Collection: it's more complex than that.

d. Map<String, Integer> mymap = new HashMap<String, Integer>();

mymap.put(“x”,1);

6. Covering the Java Tutorial sections on Setting Up Tables and Retrieving and Modifying Values from Result Sets.

a.      The main package used is java.sql

Classes— in order of first use in Setting Up Tables:

•     SQLException - SQLException objects provides information on a database access    error or other errors.
•  Statement - A Statement object is what sends the SQL statement to the DBMS.    (variable “stmt”)
•  Connection – Connection object is used to connect to the database. (variable “con”)

b. No we don’t need a new connection for each SQL statement. If the connection is open (i.e. not explicitly closed), we can use it to pass SQL statements to the database. We could create a new connection for each SQL statement but this will lead to I/O overhead and poor performance, since creating a connection is a big job.

c. The JDBC objects  are automatically closed when they are garbage collected, but cautious programmers always close the `objects` explicitly to directly determine that and when this occurs and to conserve resources. Note that while the API specifically says that closing a `Connection` "Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released," the JDBC recommendation is to explicitly close ```JDBC objects, especially Connections```. Under high load, JDBC objects may not be garbage collected soon enough to avoid exceeding database resources.

d. `ResultSet`'s getXXX() method of the appropriate type is used to retrieve the value in each column. JDBC defines types to match the SQL data types and there is a getXXX() method for each.

For  varchar(100), we commonly use getString. For SQL float, we use getDouble, for decimal(7,2), getBigDecimal (or getDouble, but that leads to floating-point fuzziness) and for timestamp, getTimestamp (for java.sql.Timestamp), or getDate (for java.util.Date) if millisecond precision is good enough. A SQL float is a 64-bit floating-point number like Java double, not like Java float, a 16-bit number. Decimal(7,2) is an exact decimal number with 7 digits, two after the decimal point. A SQL timestamp contains both date and time down to seconds and fractional seconds with typically millisecond or nanosecond precision (nanosecond precision may need TIMESTAMP(9) type).

7.  Oracle. a. 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.

ORACLE FROM UNIX (any Linux system at CS.UMB will work): Here we are assuming user btang.

\$ javac JdbcCheckup.java
\$ java -classpath ojdbc6.jar:. JdbcCheckup   (note colon, then dot)
Please enter information to test connection to the database
Using Oracle (o), MySql (m) or HSQLDB (h)? o
user: btang
use canned Oracle connection string (y/n): y
host: dbs3.cs.umb.edu
sid (site id): dbs3
port: 1521
using connection string: jdbc:oracle:thin:@dbs3.cs.umb.edu:1521:dbs2
Connecting to the database...connected.
Hello World!

On home PC:  For Linux/Mac, use colon as above, and localhost to tunnel

C:\cs636>javac JdbcCheckup.java
C:\cs636>java -classpath ojdbc6.jar;. JdbcCheckup     (note semicolon, then dot)
Please enter information to test connection to the database
Using Oracle (o), MySql (m) or HSQLDB (h)? o
user: btang
use canned Oracle connection string (y/n): y
host: localhost                    (connecting to tunnel)
sid (site id): dbs3
port: 1521
using connection string: jdbc:oracle:thin:@localhost:1521:dbs3
Connecting to the database...connected.
Hello World!

8. Similarly run JdbcCheckup on your mysql account.

topcat\$ java -classpath mysql-connector-java-5.1.39-bin.jar:. JdbcCheckup

Please enter information to test connection to the database

Using Oracle (o), MySql (m) or HSQLDB (h)? m

user: btang

use canned MySql connection string (y/n): y

host: localhost

port (often 3306): 3306

using connection string: jdbc:mysql://localhost:3306/btangdb

Connecting to the database...connected.

Hello World!

from home (Windows) with tunnel to topcat port 3306 from local port 3333 (not required on homework)

topcat\$ java -classpath mysql-connector-java-5.1.39-bin.jar:. JdbcCheckup

Please enter information to test connection to the database

Using Oracle (o), MySql (m) or HSQLDB (h)? m

user: btang

use canned MySql connection string (y/n): y

host: localhost

port (often 3306): 3333

using connection string: jdbc:mysql://localhost:3333/btangdb

Connecting to the database...connected.

Hello World!

9. Try out the Data Source Explorer view in eclipse. Set up tunnels to dbs3 and topcat. 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 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. One way, or just supply the whole output.

Linux at cs.umb.edu:

topcat\$ javac -version

javac 1.7.0_79

topcat \$ env|grep JAVA

JAVA_HOME=/usr

topcat\$ ant -version

Apache Ant(TM) version 1.9.3 compiled on April 8 2014

topcat\$ env|grep CLASSPATH

Home  PC: of course the directory in use is your choice, and details will vary:

C:\cs636>java -version

java version "1.8.0_65"

Java(TM) SE Runtime Environment (build 1.8.0_25-b18)

Java HotSpot(TM) Client VM (build 25.25-b02, mixed mode)

C:\cs636>javac -version

javac 1.8.0_65

C:\cs636>set|find "JAVA"

JAVA_HOME=C:\Program Files\Java\jdk1.8.0_65

C:\cs636>set|find "ANT"

ANT_HOME=c:\apache-ant-1.9.3

C:\cs636>set|find "CLASSPATH"

(nothing output, as expected)