Preparation
Please prefer to pull 101repo in one piece from here:
https://github.com/101companies/101repo
(The code of the relevant contributions was slightly refactored past the lecture and the 101infrastructure needs about 1 day to rebuild the wiki-linked zips whereas GitHub provides real-time update for the repo zips for download that you find when going to the root of a repo.)
Starting point
http://101companies.org/wiki/Contribution:mySqlMany
http://101companies.org/wiki/Contribution:jdbc
http://101companies.org/wiki/Contribution:hibernate
Objective
Exercise database programming with JDBC and Hibernate.
Task
Pick any subtask you like. Doing only one of them is sufficient.
- Revise the "Transform" test case of Contribution:jdbc so that it modifies the database only temporarily. That is, the "Query" test case should not be broken.
- Revise the "TransformAndSave" test case of Contribution:hibernate so that it modifies the database only temporarily. That is, the "Query" test case should not be broken.
- Implement a constraint on top of Contribution:jdbc to see whether department names are unique per company. (They cannot be assumed to be unique over all companies.)
- Revise Contribution:hibernate so that it uses annotations as opposed to XML-based mapping files.
- Revise Contribution:jdbc so that it does use a basic object model, e.g., like the one of Contribution:javaComposition. Thus, you need to write JDBC code to load a company into objects (using "SELECT") and to save the possibly modified objects back into the database (using "UPDATE").
Hint
For subtasks 1 and 2, you need to think carefully about the meaning of modifying a database temporarily. It probably means that you need to modify the database twice, i.e., the second time is for returning to the original state. Thus, part of the challenge, if any, is to be able to know what the original state was.
Subtask 3 for the relaxed uniqueness constraints can be approached by means of an appropriate SELECT query that retrieves department names with company ids, thereby asking the Java code on the ResultSet to check for uniqueness. With more advanced SQL, one can also solve the problem pretty much with SQL only, but that's perhaps not so obvious.
Subtask 4 on annotations requires from you to read some documentation as linked. For each construct in the mapping file, there is supposed to be an annotation.
Subtask 5 does not ask for a general solution that would be able to deal with insertion and deletion. It only needs to deal with load and update. (Otherwise, it would get more complicated.) Basically, you need to write SELECT queries to retrieve all departments and employees of a company. Some care needs to be taken to link departments with their subdepartments and employees. (So you somehow need to be able to map database ids to object ids.)
Testing
Test cases of totaling salaries and cutting salaries (the latter with save) are required for all subtasks.