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

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. 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

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme