Register Login





|

Top Links: >> 80. Technology >> Internet Technology Summit Program >> 2. Java and Databases
Current Topic: 2.6. Java and Databases Project
You have a privilege to create a quiz (QnA) related to this subject and obtain creativity score...
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? Highlight the text in question Or


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.


We invite you to create your own questions and answers (QnA) to increase your rank and win the Top Creativity Prize!

Topic Graph | Check Your Progress | Propose QnA | Have a question or comments for open discussion?
<br/>  public void init(String appName, String dsName, String pathToConfigFile, String pathToCsvFile) {
<br/>    // store appName and dsName as class data
<br/>    this.appName = appName;
<br/>    this.dsName = dsName;
<br/>    // read the configuration file and init a connection pool
<br/>DataService.init(appName, pathToConfigFile);
<br/>    // create the UserProfile table
<br/>    // use try/catch to allow program continue when running not first time and table already exists
<br/>    DataService.setPrepDataBySqlName({provide the name of the create statement}, null, dsName, null); // no params, no types
<br/>    // read CSV file and split the file into lines
<br/>    String csvText = IOMaster.readTextFile(pathToCsvFile);
<br/>    String[] lines = csvText.split(System.lineSeparator());
<br/>    // arrange the loop for each line and split each line into the fields
<br/>    // skip the very first line, because the first line includes the names of the columns
<br/>    for(int i=1; i < lines.length; i++) {
<br/>        String line = lines[i];
<br/>        // split the line with comma separated values into the fields
<br/>        String[] fields = line.split(",");
<br/>        // use the insert statement to insert the fields
<br/>        DataService.setPrepDataBySqlName({the name of the insert statement}, fields, dsName, null);
<br/>    }
<br/>
<br/>12.2.	The next method:
<br/>
<br/>  public List<String[]> getUserProfileByEmail(String email) {
<br/>     List<String[]> records = 
<br/>     DataService.getPrepDataBySqlName("selectUserProfileByEmail", new String[] {email}, dsName);
<br/>     return records;
<br/>  }
<br/>
<br/>12.3.	the next method:
<br/>
<br/>  public List<String[]> getUserProfileByLastName(String lastName) {
<br/>     List<String[]> records = 
<br/>     DataService.getPrepDataBySqlName("selectUserProfileByLastName", new String[] {lastName}, dsName);
<br/>     return records;
<br/>  }
<br/>
<br/>12.4.	Create the main() method to:
<br/>
<br/>  // instantiate the object of the <b>UserProfileHandler</b> class
<br/>  UserProfileHandler handler = new UserProfileHandler();
<br/>  
<br/>  // call init() to create the table and fill it in with the records from the CSV file
<br/>
<br/>  handler.init("jdbcProject", "its", "c:/ITS/jdbcProject/config/its-ds.xml" , "c:/its-resources/userProfile.csv");
<br/>
<br/>  // create the Scanner object
<br/>  Scanner sc = new Scanner(System.in);
<br/>
<br/>  // ask a user to enter an email
<br/>  System.out.println("Enter email:");
<br/>  // get the response from the keyboard with the Scanner
<br/>  String email = sc.nextLine();
<br/>
<br/>  // get the records from UserProfile by email
<br/>  List<String[]> records = handler.getUserProfileByEmail(email);
<br/>
<br/>  // display the records if not found, display NOT FOUND
<br/>  if(records == null) {
<br/>      System.out.println("Not Found!");
<br/>  }
<br/>  for(String[] fields : records) {
<br/>      for(String field : fields) {
<br/>	 System.out.print(" | " + field);
<br/>      }
<br/>      System.out.println(" |"); // end of record - new line
<br/>  }
<br/>
<br/>  // ask a user to enter a last name
<br/>  System.out.println("Enter last name:");
<br/>
<br/>  // get the response from the keyboard with the Scanner
<br/>
<br/>  // get the records from UserProfile by last name
<br/>
<br/>  // display the records
<br/>






Was it clear so far? Highlight the text in question

Or



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.



We invite you to create your own questions and answers (QnA) to increase your rank and win the Top Creativity Prize!


Topic Graph | Check Your Progress | Propose QnA | Have a question or comments for open discussion?

Have a suggestion? - shoot an email
Looking for something special? - Talk to AI
Read: IT of the future: AI and Semantic Cloud Architecture | Fixing Education
Do you want to move from theory to practice and become a magician? Learn and work with us at Internet Technology University (ITU) - JavaSchool.com.

Technology that we offer and How this works: English | Spanish | Russian | French

Internet Technology University | JavaSchool.com | Copyrights © Since 1997 | All Rights Reserved
Patents: US10956676, US7032006, US7774751, US7966093, US8051026, US8863234
Including conversational semantic decision support systems (CSDS) and bringing us closer to The message from 2040
Privacy Policy