2.6. Java and Databases Project

Assignments:
1. Business goals for jdbcProject (this is just the plan, look at the hints below for implementations):
1.1. Create an Excel spreadsheet file with several columns
Email, First Name, Last Name, Role (member, admin, or editor)
1.2. Enter several records into the file and Save as CSV (Comma Separated Values) in the c:/its-resources/userProfile.csv
1.3. Create a program with the method that can read this file into a database in the UserProfile table (see the hints below)
1.4. This program will use SQL statements stored in the files:
C:/ITS/jdbcProject/config/its-sql/createUserProfile.sql
C:/ITS/jdbcProject/config/its-sql/insertUserProfile.sql
1.5. Add to this program another method getUserProfileByEmail that can retrieve a record from the UserProfile table with the email as an argument to this method (should use C:/ITS/jdbcProject/config/its-sql/selectUserProfileByEmail.sql)
1.6. Add to this program another method getUserProfileByLastName that can retrieve a record from the UserProfile table with the lastName as an argument to this method (should use C:/ITS/jdbcProject/config/its-sql/selectUserProfileByLastName.sql)
1.7. Add to this program a main() method to initiate the table (create, then read file into the table)
1.8. Add to the main() method a line using java.util.Scanner to ask for an email and use the answer as an argument to retrieve and display a User Profile record
1.9. Add to the main() method a line using java.util.Scanner to ask for a Last Name and use the answer as an argument to retrieve and display a User Profile record

2. Use Oracle Express database, which is already installed on your PC, with the default connection URL and username and password equals its.

3. Use Data Service Framework in your jdbcProject.

Hints:
3.1. Create a new project jdbcProject
3.2. Under the src folder create a new package its.day12.db
3.3. Copy all classes from the package its.day11.db in the project week4db into the package its.day12.db in the jdbcProject.

4. Under the jdbcProject create a new config - folder.
5. Under the config folder create a new its-sql - folder.
6. Under the config folder create a new file its-ds.xml
7. Copy to this file an example of its-ds.xml content from the DataService class header.
8. Under the folder config/its-sql create new SQL files and provide the following content:
8.1. createUserProfile.sql
CREATE TABLE UserProfile (email varchar(60), FirstName varchar(60), LastName varchar(60), Role varchar(60) )

8.2. insertUserProfile.sql
INSERT into UserProfile values(?,?,?,?)
8.3. selectUserProfileByEmail.sql
SELECT * from UserProfile where email = ?
8.4. selectUserProfileByLastName.sql
SELECT * from UserProfile where LastName = ?

9. Under the new package its.day12.db create a new class UserProfileHandler.
10. Provide the header for the class describing the goals and methods
11. Provide class data:
private String appName, dsName;
12. Provide the following methods with proper header-comments:
12.1. The init method to read and set configuration; read and insert the records from the CSV file


  public void init(String appName, String dsName, String pathToConfigFile, String pathToCsvFile) {

    // store appName and dsName as class data

    this.appName = appName;

    this.dsName = dsName;

    // read the configuration file and init a connection pool

DataService.init(appName, pathToConfigFile);

    // create the UserProfile table

    // use try/catch to allow program continue when running not first time and table already exists

    DataService.setPrepDataBySqlName({provide the name of the create statement}, null, dsName, null); // no params, no types

    // read CSV file and split the file into lines

    String csvText = IOMaster.readTextFile(pathToCsvFile);

    String[] lines = csvText.split(System.lineSeparator());

    // arrange the loop for each line and split each line into the fields

    // skip the very first line, because the first line includes the names of the columns

    for(int i=1; i < lines.length; i++) {

        String line = lines[i];

        // split the line with comma separated values into the fields

        String[] fields = line.split(",");

        // use the insert statement to insert the fields

        DataService.setPrepDataBySqlName({the name of the insert statement}, fields, dsName, null);

    }



12.2.	The next method:



  public List getUserProfileByEmail(String email) {

     List records = 

     DataService.getPrepDataBySqlName("selectUserProfileByEmail", new String[] {email}, dsName);

     return records;

  }



12.3.	the next method:



  public List getUserProfileByLastName(String lastName) {

     List records = 

     DataService.getPrepDataBySqlName("selectUserProfileByLastName", new String[] {lastName}, dsName);

     return records;

  }



12.4.	Create the main() method to:



  // instantiate the object of the UserProfileHandler class

  UserProfileHandler handler = new UserProfileHandler();

  

  // call init() to create the table and fill it in with the records from the CSV file



  handler.init("jdbcProject", "its", "c:/ITS/jdbcProject/config/its-ds.xml" , "c:/its-resources/userProfile.csv");



  // create the Scanner object

  Scanner sc = new Scanner(System.in);



  // ask a user to enter an email

  System.out.println("Enter email:");

  // get the response from the keyboard with the Scanner

  String email = sc.nextLine();



  // get the records from UserProfile by email

  List records = handler.getUserProfileByEmail(email);



  // display the records if not found, display NOT FOUND

  if(records == null) {

      System.out.println("Not Found!");

  }

  for(String[] fields : records) {

      for(String field : fields) {

	 System.out.print(" | " + field);

      }

      System.out.println(" |"); // end of record - new line

  }



  // ask a user to enter a last name

  System.out.println("Enter last name:");



  // get the response from the keyboard with the Scanner



  // get the records from UserProfile by last name



  // display the records

Was it clear so far?

13. Run As Java Application, then email the SQL statements and Java source (with good header-comments) to dean@ituniversity.us

13. Run As Java Application, then email the SQL statements and Java source (with good header-comments) to dean@ituniversity.us

14. Describe a similar project which requires two tables: UserProfile and Accounts

14.1 Create a Business Requirements as MS Word document describing high level goals.

14.2 Create a Design Specification document describing the solution similar to the description above in points 1-13

While describing necessary SQL statements include those with LIKE conditions and
make sure that you have proper business goals that requires these statements.

14.3 Create another class in Eclipse AccountHandler, make it work and email to dean@ituniversity.us together with SQL Statements and two documents.