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

 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

 

  1. 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>

 

  1. 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>

  1. 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:

Instructions Files

Java Programming Experts

expert
Neno S.
Java Programming

60 Answers

expert
Ray Bjork
Java Programming

90 Answers

View More Experts
Disclaimer

The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.

Get Free Quote!

265 Experts Online