Course Projectdevry Universitycollege Of Engineering And Information S ✓ Solved
Course Project DeVry University College of Engineering and Information Sciences Course Number: CIS355A Module 6: Read and Write to Databases Objectives · Write data from the GUI to a database. · Read data from the database and display the data on the GUI application. · Use a three-tier architecture to handle large development projects. We can read and write to files. However, what if your client has multiple locations? For example, your client may have an office in Atlanta, GA, another office in Dallas, TX, and another office in Los Angeles, CA. If you write your data to a file, the different locations will not be able to share the data.
Even if your customer has a few locations in the same geographical area, the nearby locations cannot share information with each other if you write the data to a file. How is it possible for huge companies to share their information with all their world-wide locations? They write the information to databases. In fact, you can put your database in the cloud and have all of your locations read and write to that single database. Databases are so important and so popular that many people make an entire career out of databases.
Google “department of labor database administrator†and you will see that database administrators make a very good living. Most industry applications read and write to databases. As a software developer, it is important to know how to use databases and how to read and write to them. Let’s update our application so it can read and write to a database. Remember that we created our application using a three-tier architecture. · Presentation: GUI classes, which read info from the user and displays info to the user · Business rules: handles data processing and class organization for the application · Data storage: use a separate class to read and write to the file or database We get to benefit from this design style this week.
Most of our work will be in the data storage tier. You are going to love it. Steps 1. Before we can read or write to a database, we need to have the database driver in the root folder of our project. You can get the database driver from your instructor or you can Google for it and download it from the database company.
Because we are using MySQL, Google “mysql java driverâ€. It may change, but my top search result is “MySQL Connector/J†located at . Then, I click on the download link next to JDBC Driver for MySQL. I choose the operating system as Platform Independent and then click on the download for the Zip Archive. On the next page, I click the link “No thanks, just start my download.†Once the file downloads, I extract it and find the mysql-connector-java- xxxx .jar file (xxxx part will change).
This is the only file that you will need. 2. Open your project’s folder. Copy the mysql-connector-java- xxxx .jar file to the root folder (base folder). Once the file is there, open your NetBeans project.
Click the + sign next to the project name. Then, right-click on Libraries. Choose Add Jar/Folder. Browse and find your project’s folder. Once you find the folder, you will see the mysql-connector-java- xxxx .jar file listed.
Double-click on the file or select it and then click Open to add the MySQL driver to your project library. 3. Open your Visio Class diagram from our design phase. We need to update the DataIO class to match our Visio Class diagram. Remember, DataIO stands for data input and output.
Because we are connecting with a database, we will need to add our connection variables as constants to the top of the class. 4. If you made the password devry123 and called your schema cis355a, then your constants should be as follows. 5. private final String DATABASE_NAME = "cis355a"; private final String CONNECTION_STRING = "jdbc:mysql://localhost:3306/" + DATABASE_NAME; private final String USER_NAME = "root"; private final String PASSWORD = "devry123"; Once you finish updating the DataIO class, the top of the class should look something like this. 6.
The DataIO class is set up to read and write to files. Let’s update it so it reads and writes to the database. Delete the import statements at the top and replace them with import statements for database access. The asterisk (‘*’) tells Java to import all classes that are referenced from the java.sql package. We also need to return an ArrayList, so let’s import that class as well from the java.util package. import java.sql.*; import java.util.ArrayList; 7.
Delete the code in your methods so we can add the database input and output code, including the throws statements at the end of the method headers. When you finish, your DataIO border class should look something like this. 8. Now, we need to set up our MySQL database so it can hold our data. Open your MySQL workbench.
Click on the local instance MySQL80 icon. 9. Type in your password, which should be devry123 (without the quotes) if you set it up following the default directions. Otherwise, please enter your chosen password. Click on the schemas tab so you can see your databases, which are called Schemas in MySQL.
10. You should have a database (schema) called cis355a. If you do not have one, click the barrel icon (“Create new schema in the connected serverâ€) that is located on the Taskbar at the top to create it. Open the cis355a database by clicking the triangle next to it. Open the tables by clicking the triangle next to it so you can see the existing tables.
Right-click on tables and choose Create Table. Create the following table and call it landscape. Notice that CustomerID is a primary key and it has Auto Increment turned on. 11. Go back to your DataIO class.
In the add( ) method, write code to do the following. a. Check for the database driver. b. Connect to the database. c. Add the Customer record to the landscape table. d. Close the database connection.
Remember to throw the exceptions so the GUI class can report any possible issues. Also, remember to use a PreparedStatement to help prevent hacking. When you finish, your code should look something like this. public void add(Customer cust) throws ClassNotFoundException, SQLException { //check for driver Class.forName("com.mysql.cj.jdbc.Driver"); //connect to database Connection conn = DriverManager.getConnection(CONNECTION_STRING, USER_NAME, PASSWORD); //add record String strSQL = "INSERT INTO landscape (CustomerName, CustomerAddress, " + "LandscapeType, YardLength, YardWidth, LandscapeCost) " + "VALUES(?, ?, ?, ?, ?, ?)"; PreparedStatement pstmt = conn.prepareStatement(strSQL); pstmt.setString(1, cust.getName()); pstmt.setString(2, cust.getAddress()); pstmt.setString(3, cust.getYardType()); pstmt.setDouble(4, cust.getLength()); pstmt.setDouble(5, cust.getWidth()); pstmt.setDouble(6, cust.getTotalCost()); // execute the prepared statement pstmt.execute(); //close connection conn.close(); } 12.
Go to your LandscapeGUI.java file. Go to the top of your code window and replace the IOException import with an import for java.sql.*. When you finish, the top of your code should look like this. 13. Update you submitOrder( ) method so that it looks for SQLExceptions instead of IOExceptions.
In addition, we need to look for the ClassNotFoundException in case we do not have the database driver installed correctly. When you finish, the submitOrder( ) method should look something like this. private void submitOrder() { if (validateInputs() == false) { return; // end the method if validation failed } Customer cust = createCustomer(); customerList.addElement(cust); txaOrderInfo.setText(cust.getDetails()); try { DataIO data = new DataIO(); // create DataIO object data.add(cust); loadCustomers(); // load all customers // reset for the next customer reset(); //move to the client orders tab tabMain.setSelectedIndex(2); } catch (SQLException ex) { JOptionPane.showMessageDialog(this, "Error: " + ex.getMessage(), "Database Error", JOptionPane.ERROR_MESSAGE); } catch (ClassNotFoundException ex) { JOptionPane.showMessageDialog(this, "Driver Not Found Error: " + ex.getMessage(), "Database Driver Error", JOptionPane.ERROR_MESSAGE); } } 14.
If you use a block comment on your btnDeleteActionPerformed( ) method and your loadCustomers( ) method, you can test your submitOrder( ) method. For example, put a \* as the first line of your loadCustomers method and a *\ as the last line. private void loadCustomers() { /* // the code will be “commented out†and ignored by Java */ } 15. Go to your DataIO class. In order to read data from the database, we need to connect to the database, read the records, and then close the connection. The file reads the data using a while loop, and then close the file.
As we read the records, let’s create Customer objects and then add them to the ArrayList. Finally, we need to return the ArrayList. When you finish, your code should look like something like this (including the throws statement). public ArrayList<Customer> getList() throws SQLException { // create the ArrayList so we have something to return ArrayList<Customer> list = new ArrayList<Customer>(); //connect to database Connection conn = DriverManager.getConnection(CONNECTION_STRING, USER_NAME, PASSWORD); Statement statement = conn.createStatement(); String SQL = "Select * FROM landscape"; ResultSet rs = statement.executeQuery(SQL); while (rs.next()) { // create Customer object and load the attributes Customer client = new Customer(); client.setCustomerID(rs.getInt(1)); client.setName(rs.getString(2)); client.setAddress(rs.getString(3)); client.setYardType(rs.getString(4)); client.setLength(rs.getDouble(5)); client.setWidth(rs.getDouble(6)); client.setTotalCost(rs.getDouble(7)); // add the Customer object to our list list.add(client); } // close the database connection conn.close(); // return the ArrayList return list; } 16.
Save All and then go to your LandscapeGUI.java file. Update your loadCustomers( ) method so that it looks for SQLExceptions instead of IOExceptions. When you finish, the loadCustomers( ) method should look something like this. private void loadCustomers() { try { DataIO data = new DataIO(); // create DataIO object ArrayList<Customer> customers = data.getList(); // clear out the DefaultListModel and textarea customerList.clear(); txaOrderInfo.setText(""); // copy each object from the ArrayList over to the DefaultListModel for (int i = 0; i < customers.size(); i++) { customerList.addElement(customers.get(i)); } } catch (SQLException ex) { JOptionPane.showMessageDialog(this, "Error: " + ex.getMessage(), "Database Error", JOptionPane.ERROR_MESSAGE); } } 17.
Run your application. Click on the customer list tab. Click on the load list button. Does it work? So far, so good.
18. We need to be able to delete a customer. Go to your DataIO class. Change the method header so we can delete the customer based on the customer’s ID number. In your delete( ) method, connect to the database.
Then, issue the DELETE FROM command. Finally, close the connection to the database. When you finish, the delete( ) method should look something like this. public void delete(int customerID) throws SQLException { // connect to the database Connection conn = DriverManager.getConnection(CONNECTION_STRING, USER_NAME, PASSWORD); // delete the record String SQL = "DELETE FROM landScape WHERE CustomerID = ?"; PreparedStatement pstmt = conn.prepareStatement(SQL); pstmt.setInt(1, customerID); pstmt.execute(); // close the database connection conn.close(); } } 19. Let’s update our delete customer button event code in the GUI. Save All and then go to the code and get the selected customer object.
Then, create a DataIO object and delete the customer based on the customer’s ID number. Finally, load the current customers using the loadCustomers( ) method. When you finish, your code should look something like this. try { // get the selected object Customer old = lstCustomers.getSelectedValue(); // if something is selected, delete it and clear the details textarea if (old != null) { DataIO data = new DataIO(); data.delete(old.getCustomerID()); // get the name only txaCustomerInfo.setText(""); loadCustomers(); } } catch (SQLException ex) { JOptionPane.showMessageDialog(this, "Error: " + ex.getMessage(), "Database Error", JOptionPane.ERROR_MESSAGE); } 20. Run your application and test it, including the menu.
You can now read and write to databases. This is an amazing project. What projects will you build now that you can design a GUI and read/write to database? The sky is the limit. Course Project Deliverables for Week 6 · Close NetBeans so your zip file will have the latest code.
Then, go to the project folder. Right-click on the project folder and choose Send to Compressed (zipped) file. Then, rename the zipped file to include your name. Submit the zipped file on Canvas. · Week 6 Database IO – Your Name.zip image1.emf Microsoft_Visio_Drawing.vsdx DataIO <<Stereotype>> parameter - DATABASE_NAME : String - CONNECTION_STRING : String - USER_NAME : String - PASSWORD : String + add( cust : Customer ) : void + delete( customerID : int ) : void + getList( ) : ArrayList<Customer> image2.png image3.png image4.png image5.png image6.emf Microsoft_Visio_Drawing1.vsdx landscape CustomerID int, AI FK PK CustomerName varchar FK PK CustomerAddress varchar FK PK LandscapeType varchar FK PK LandscapeCost double FK PK YardLength double FK PK YardWidth double FK PK image7.png 1.
If a 180-day T-note with a face value of ,000 is purchased at a 6% requested yield, calculate the price. 2. When a 90-day note with a face value of 0,000 is first issued, Barry purchases it for a yield of 7% per year. He sells it at a yield of 7.4 percent per annum with 60 days to maturity. What is his return?
3. If the interest rate is 5% per year, what is the price of a 0 Zero Coupon Bond with a six-year maturity and a face value of 0?
Paper for above instructions
Introduction
In computing, data management is critical for applications that require extensive data sharing among multiple users, typically across different geographical locations. This project demonstrates the integration of a MySQL database with a Java GUI application built using a three-tier architecture. This approach enhances data accessibility, security, and efficiency, allowing for smoother operations within corporate environments equipped with various branch locations.
Three-Tier Architecture Overview
The three-tier architecture separates concerns into three distinct layers:
1. Presentation Layer: This layer handles the user interface (UI) and user input.
2. Business Logic Layer: Here, business rules and data processing take place.
3. Data Storage Layer: This layer is responsible for interacting with the database.
This separation facilitates easier maintenance and scalability (Sommerville, 2011).
Steps to Integrate MySQL with the Java Application
The following steps outline the procedures for connecting the MySQL database to the Java GUI application.
Step 1: Database Driver Setup
To enable the Java application to communicate with a MySQL database, the MySQL JDBC driver is indispensable. The driver, "mysql-connector-java-x.x.x.jar", must be downloaded and added to the project library (MySQL, 2023).
Step 2: DataIO Class Configuration
The DataIO class is central to the data storage layer:
- Connection constants are defined:
```java
private final String DATABASE_NAME = "cis355a";
private final String CONNECTION_STRING = "jdbc:mysql://localhost:3306/" + DATABASE_NAME;
private final String USER_NAME = "root";
private final String PASSWORD = "devry123";
```
- Necessary SQL imports were made:
```java
import java.sql.*;
import java.util.ArrayList;
```
Step 3: Implementing Database Operations
The primary database operations involve adding, retrieving, and deleting customer records.
Adding Records
The `add` method creates a record in the database using a `PreparedStatement` to protect against SQL injection:
```java
public void add(Customer cust) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(CONNECTION_STRING, USER_NAME, PASSWORD);
String strSQL = "INSERT INTO landscape (CustomerName, CustomerAddress, LandscapeType, YardLength, YardWidth, LandscapeCost) VALUES (?, ?, ?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, cust.getName());
pstmt.setString(2, cust.getAddress());
pstmt.setString(3, cust.getYardType());
pstmt.setDouble(4, cust.getLength());
pstmt.setDouble(5, cust.getWidth());
pstmt.setDouble(6, cust.getTotalCost());
pstmt.execute();
conn.close();
}
```
Retrieving Records
To retrieve records from the `landscape` table, the `getList` method is implemented:
```java
public ArrayList
ArrayList
Connection conn = DriverManager.getConnection(CONNECTION_STRING, USER_NAME, PASSWORD);
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM landscape");
while (rs.next()) {
Customer client = new Customer();
client.setCustomerID(rs.getInt(1));
client.setName(rs.getString(2));
client.setAddress(rs.getString(3));
client.setYardType(rs.getString(4));
client.setLength(rs.getDouble(5));
client.setWidth(rs.getDouble(6));
client.setTotalCost(rs.getDouble(7));
list.add(client);
}
conn.close();
return list;
}
```
Deleting Records
The `delete` method is straightforward, ensuring effective record removal:
```java
public void delete(int customerID) throws SQLException {
Connection conn = DriverManager.getConnection(CONNECTION_STRING, USER_NAME, PASSWORD);
String SQL = "DELETE FROM landscape WHERE CustomerID = ?";
PreparedStatement pstmt = conn.prepareStatement(SQL);
pstmt.setInt(1, customerID);
pstmt.execute();
conn.close();
}
```
Step 4: GUI Integration
The user interface must interact seamlessly with the modified DataIO class methods.
Submit Order Action
The action for submitting orders in the GUI is linked to the `add` method:
```java
private void submitOrder() {
if (!validateInputs()) return;
Customer cust = createCustomer();
try {
DataIO data = new DataIO();
data.add(cust);
loadCustomers(); // refresh the displayed customer list
reset(); // reset input fields
} catch (SQLException | ClassNotFoundException e) {
JOptionPane.showMessageDialog(this, "Error: " + e.getMessage(), "Database Error", JOptionPane.ERROR_MESSAGE);
}
}
```
Testing and Validation
The application should be extensively tested to ensure all operations - adding, retrieving, and deleting - function as intended. Each unit should maintain integrity, ensuring no data discrepancies occur as a result of user actions (Pressman & Maxim, 2014).
Conclusion
This project successfully illustrates how to connect a Java GUI application with a MySQL database using a three-tier architecture. The implementation of data reading and writing operations enhances the application's capabilities, rendering it suitable for use by organizations with multiple locations. Companies can now harness multiple data points efficiently, leading to improved data management practices.
References
1. MySQL. (2023). MySQL Connector/J. [Link]
2. Pressman, R. S., & Maxim, B. R. (2014). Software Engineering: A Practitioner’s Approach. McGraw-Hill.
3. Sommerville, I. (2011). Software Engineering. Addison-Wesley.
4. Oracle. (2020). Java Platform, Standard Edition. [Link]
5. Apache. (2021). Apache NetBeans. [Link]
6. W3Schools. (2023). SQL SELECT Statement. [Link]
7. Stanford University. (2011). Database System Concepts. [Link]
8. IBM Knowledge Center. (2022). JDBC and SQL. [Link]
9. Oracle. (2021). Java Database Connectivity (JDBC). [Link]
10. Javadoc. (2023). Java SE Documentation. [Link]
Note: Some references are placeholders; specific URLs and documents should be included based on the resources accessed during the project development.