Write a method named query1 that uses JDBC to execute a SELECT query to List all Emp IDs, Names, together with their Dept names

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

 Database Management Systems

JDBC programming and XML

This lab asks you to write simple JDBC programs, and execute an XPath query. What you will need:

• A MySQL server installation

• A MySQL JDBC driver

• Create a database instance named “jdbclab” in MySQL

• Load the JDBCLabInit.sql into the jdbclab space in MySQL

 

Activity 1: Construct a JDBC program according to the following specifications:

1. Write a method named query1 that uses JDBC to execute a SELECT query to List all Emp IDs, Names, together with their Dept names

   1. Use a regular JDBC Statement object for

   2. Pretty-print the results to the console (One line per row in readable aligned columns)

   3. This would be invoked on the command line as

java ser322.JdbcLab <url> <user> <pwd> <driver> query1

 

2. Write a method named query2 that uses JDBC to execute a SELECT query to List a Dept Name together with the Names of Customers who have purchased a Product made by that Department and the amount the Customer spent on the

   1. Use a PreparedStatement with a parameter for the DeptNo and filter the query results to only return rows tied to that

   2. Pretty-print the results to the console (One line per row in readable aligned columns)

   3. This would be invoked on the command line as

java ser322.JdbcLab <url> <user> <pwd> <driver> query2 <DeptNo>

 

3. Write a method named dml1 that uses JDBC to add a new Customer to the database

   1. Use a PreparedStatement with parameters for the 4 values to INSERT

   2. Be sure to commit your results appropriately!

   3. If successful, indicate by printing out SUCCESS

   4. This would be invoked on the command line as

java ser322.JdbcLab <url> <user> <pwd> <driver> dml1

<customer id> <product id> <name> <quantity>

Note: you can run query2 to check on dml1, as the results should change!

 

Activity 2: XML-ize the database and execute XPath queries Using the same database instance, do the following:

1. Modify your JDBC program from Activity 1 to export the entire database to an XML file

   1. This would be invoked on the command line as

java ser322.JdbcLab <url> <user> <pwd> <driver> export <filename>

2. Construct a separate Java program to display the results of an XPath expression on an exported XML file that returns all Product descriptions of Products from a given Dept

   1. This would be invoked on the command line as

java ser322.JdbcLab2 <DeptNo>

 

Extra Credit:

For extra credit,

1. Create an XML schema for your database export, name it xsd

2. Write a standalone program to import an XML file that conforms to the schema to the

   1. This would be invoked on the command line as

java ser322.JdbcLabEC <url> <user> <pwd> <driver> <filename>

 

Make sure of the following for this lab:

1. You never leak database resources!

2. Fully handle all error situations. By “fully” we mean give directed feedback as to what is incorrect about the user’s query, or what the error executing the query

3. Your programs should use the package ser322. Note Activity 2, #2 changes the program name

4. Your code should be readable and documented, and exhibit coding practices appropriate for an upper- division programming course in Software

5. The command-line examples above do not show the use of the -classpath (-cp) option to java. We will add this as per our grading

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of