I'm new to Java and MySQL and I'm currently trying to finish my assignment, although I'm having a problem with the IF function. I'm wanting to print the query results from MySQL to the console based on Customer ID and display an error message if the Customer ID doesn't exist.
The data displays if the ID is found in the database, but if the ID does not exist, nothing happens. Any help would be greatly appreciated!
package cos106_assignment_april2015;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.io.*;
import java.sql.*;
import javax.swing.border.*;
public class ProductSearch {
JFrame f = new JFrame("Ray Sport Inc. | Product Search");
JTextField custIDF;
public ProductSearch() {
JLabel search1, customer, empty;
search1 = new JLabel("Enter Customer ID number to search for a customer's product records. Results print to console.");
customer = new JLabel("Customer ID:");
empty = new JLabel("");
custIDF = new JTextField();
JButton search, back;
search = new JButton("Search");
back = new JButton("Back to Menu");
search.addActionListener(new ActionListener() {
public void actionPerformed (ActionEvent e){
String custID = custIDF.getText();
try {
System.out.println("Establishing connection to the MySQL Database...");
Connection conn = null;
Statement stmt =null;
String url = "jdbc:mysql://localhost:3306/raysportdb";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "password";
Class.forName(driver).newInstance();
conn = DriverManager.getConnection(url,userName,password);
stmt = conn.createStatement();
System.out.println("Connection established. Connected to the customer and item records.");
String sql = "select a.Customer_ID, c.Order_ID, b.Product_ID, \n" +
"b.Product_Name, b.Product_Type, c.Order_Quantity \n" +
"from customer a, product b, orders c \n" +
"where a.Customer_ID = '"+custID+"' and b.Product_ID = c.Product_ID \n" +
"and b.Product_Type = c.Product_Type_FK and a.Customer_ID = c.Customer_ID \n" +
"group by c.Order_ID order by a.Customer_ID";
ResultSet result = stmt.executeQuery(sql);
while(result.next()){
String cid = result.getString("a.Customer_ID");
if (custID.equals (cid)) {
String f2 = result.getString("c.Order_ID");
String f3= result.getString("b.Product_ID");
String f4 = result.getString("b.Product_Name");
String f5 = result.getString("b.Product_Type");
String f6 = result.getString("c.Order_Quantity");
System.out.println("");
System.out.println("Customer ID\t:"+ cid);
System.out.println("Order ID\t:" + f2);
System.out.println("Product ID\t:" + f3);
System.out.println("Product Name\t:" + f4);
System.out.println("Product Type\t:" + f5);
System.out.println("Order Quantity\t:" + f6);
System.out.println("");
}
else {
JOptionPane.showMessageDialog(null, "Customer ID does not exist!", "Error", JOptionPane.ERROR_MESSAGE);
System.out.println("Customer ID does not exist in the database.");
}
}
conn.close();
}
catch (Exception err) {
System.err.println("Error:"+ err.getMessage());
}}});
back.addActionListener(new ActionListener() {
public void actionPerformed (ActionEvent e){
new Menu();
f.setVisible(false);}});
JPanel p = new JPanel();
p.setLayout(new GridLayout(6,2));
p.setBorder(new TitledBorder("Product Search"));
p.add(search1);
p.add(empty);
p.add(customer);
p.add(custIDF);
p.add(search);
p.add(back);
f.getContentPane().add(p);
f.pack();
f.setSize(585,284);
f.setLocationRelativeTo(null);
f.setVisible(true);
f.setResizable(false);
f.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
public static void main(String ar[]){
new ProductSearch();
}
}
Aucun commentaire:
Enregistrer un commentaire