JavaFX GUI / MySQL retrieving records from a customers table

Question

Create a JavaFX GUI that allows the user to retrieve records from the customers table in the pcparts database.The GUI should allow the user to specify desired fields, desired order, and a where condition.Display only the desired fields in the desired order for the desired where condition.You may display the records in the GUI in any way you wish.

I fixed the initial problem with MySQL on my mac. However, now my actual program is not working properly. The interface comes up but fails to retrieve any information from the sql tables. It either fails to read my where condition or any of the fields from the checklists contain unknown fields.

Here is what I got so far for the java program:

import javafx.application.Application;
import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.layout.StackPane;
import javafx.stage.Stage;
import java.sql.*;
import javafx.geometry.Insets;
import javafx.geometry.Pos;
import javafx.scene.control.CheckBox;
import javafx.scene.control.Label;
import javafx.scene.control.TextArea;
import javafx.scene.control.TextField;
import javafx.scene.layout.BorderPane;
import javafx.scene.layout.HBox;
import javafx.scene.layout.VBox;



public class JavaGUI extends Application {

// @Override

private String query = " ";
private PreparedStatement preparedStatement;
private TextArea ta = new TextArea ();
private TextField where = new TextField ();

public void start (Stage primaryStage)
{
BorderPane pane = new BorderPane();
VBox vbox = new VBox(10);
CheckBox chkCustID = new CheckBox("customer ID");
CheckBox chkName = new CheckBox("name");
CheckBox chkAddress = new CheckBox("address");
CheckBox chkCity = new CheckBox("city");
CheckBox chkState = new CheckBox("state");
CheckBox chkZip = new CheckBox("zip");
vbox.setPadding(new Insets(10,10,10,10));
vbox.getChildren().addAll (chkCustID, chkName , chkAddress , chkCity , chkState , chkZip );
HBox hbox = new HBox(10);
Button btSumbit = new Button("submit");
hbox.setAlignment(Pos.CENTER);
hbox.setPadding(new Insets(10,10,10,10));
hbox.getChildren().add(btSumbit);

HBox hbox2 = new HBox(10);
Label label = new Label("Type the where condition: ");
where.setPrefColumnCount(40);
hbox2.setPadding(new Insets(10,10,10,10));
hbox2.getChildren().addAll(label,where);
pane.setPadding(new Insets(10,10,10,10));
pane.setTop(hbox2);
pane.setLeft(vbox);
pane.setBottom(hbox);
pane.setRight(ta);
Scene scene = new Scene(pane);
primaryStage.setTitle("PcParts");
primaryStage.setScene(scene);
primaryStage.show( );
EventHandler<ActionEvent> handler = e -> {
if (chkCustID.isSelected ( ) )
query = query + "custid, ";
if (chkName.isSelected ( ) )
query = query + "name, ";
if (chkAddress.isSelected ( ) )
query = query + "address, ";
if (chkCity.isSelected ( ) )
query = query + "city, ";
if (chkState.isSelected ( ) )
query = query + "state, ";
if (chkZip.isSelected ( ) )
query = query + "zip, ";
};
chkCustID.setOnAction(handler);
chkName.setOnAction(handler);
chkAddress.setOnAction(handler);
chkCity.setOnAction(handler);
chkState.setOnAction(handler);
chkZip.setOnAction(handler);
btSumbit.setOnAction ( e -> {
initializeDB ( ) ;
display();
} ) ;
}


private void initializeDB ( )
{
try
{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver Laoded");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/pcparts");
System.out.println("Database Connected");
String queryString = "select" + query + "where" + where.getText ();
preparedStatement = connection.prepareStatement (queryString);
}
catch (Exception ex)
{
ex.printStackTrace ();
}
}
private void display()
{
try
{
ResultSet rset = preparedStatement.executeQuery ( );
if (rset.next())
{
String custid = rset.getString("custid");
String name = rset.getString("name");
String address = rset.getString("address");
String city = rset.getString("city");
String state = rset.getString("state");
String zip = rset.getString("zip");

String finalstring = filter(custid) + filter(name) + filter(address) + filter(city) + filter(state) + filter(zip);

ta.setText(finalstring);
}
}
catch (SQLException e )
{
e.printStackTrace ( );
}
}


public static String filter ( String str)
{
String x = " ";
if(str != null)
{
return str;
}
else
return x;
}


public static void main(String[] args) {
launch(args);
}

}

Here is the pcparts.sql:

    -- phpMyAdmin SQL Dump
-- version 3.1.3.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 01, 2009 at 03:19 PM
-- Server version: 5.1.33
-- PHP Version: 5.2.9-2

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `pcparts`
--
CREATE DATABASE `pcparts` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `pcparts`;

-- --------------------------------------------------------

--
-- Table structure for table `customers`
--

CREATE TABLE IF NOT EXISTS `customers` (
  `custid` int(10) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  `address` char(20) NOT NULL,
  `city` char(16) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` char(5) NOT NULL,
  PRIMARY KEY (`custid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`custid`, `name`, `address`, `city`, `state`, `zip`) VALUES
(1, 'Smith, Tim', '12 Elm St', 'Tampa', 'FL', '34567'),
(2, 'Jones, Tom', '435 Oak Dr', 'Dunedin', 'FL', '33245'),
(3, 'Avery, Bill', '623 Ash Ave', 'Clearwater', 'FL', '33655'),
(4, 'Kerr, Debra', '1573 Yew Crt', 'Clearwater', 'FL', '33442');

-- --------------------------------------------------------

--
-- Table structure for table `orders`
--

CREATE TABLE IF NOT EXISTS `orders` (
  `orderid` int(10) NOT NULL AUTO_INCREMENT,
  `custid` int(10) NOT NULL,
  `sku` char(5) NOT NULL,
  `quantity` int(10) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`orderid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `orders`
--

INSERT INTO `orders` (`orderid`, `custid`, `sku`, `quantity`, `date`) VALUES
(1, 2, 'C7615', 2, '2005-08-24'),
(2, 2, 'K9907', 1, '2005-08-24'),
(3, 3, 'C7615', 1, '2005-08-25'),
(4, 3, 'F0993', 1, '2005-08-25'),
(5, 1, 'B2245', 1, '2005-08-26');

-- --------------------------------------------------------

--
-- Table structure for table `parts`
--

CREATE TABLE IF NOT EXISTS `parts` (
  `sku` char(5) NOT NULL,
  `descrip` char(16) NOT NULL,
  `manuf` char(16) NOT NULL,
  `price` float(5,2) DEFAULT NULL,
  PRIMARY KEY (`sku`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `parts`
--

INSERT INTO `parts` (`sku`, `descrip`, `manuf`, `price`) VALUES
('B2245', 'DVD Drive', 'Sony', 79.99),
('C7615', '1 GB Flash Dr', 'Sandisk', 49.95),
('F0993', 'Laptop Mouse', 'Logitech', 29.99),
('H2243', 'G router', 'Linksys', 59.99),
('U8872', 'G PC Card', 'Linksys', 54.95),
('K9907', '4 port USB Hub', 'Acer', 12.95);

Here is the error:

Driver Loaded
Database Connected
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where customers' at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    at com.mysql.jdbc.Util.getInstance(Util.java:387)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2550)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1962)
    at derienzo32.JavaGUI.display(JavaGUI.java:114)
    at derienzo32.JavaGUI.lambda$1(JavaGUI.java:86)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Node.fireEvent(Node.java:8411)
    at javafx.scene.control.Button.fire(Button.java:185)
    at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
    at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
    at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
    at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
    at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:352)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:275)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$355(GlassViewEventHandler.java:388)
    at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:387)
    at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
    at com.sun.glass.ui.View.notifyMouse(View.java:937)

Have you added the required libraries in your build path? If not then you may check this [**POST**](http://stackoverflow.com/questions/17484764/java-lang-classnotfoundexception-com-mysql-jdbc-driver-in-eclipse) — 1000111, May 03 '16 at 03:12
okay I added the mysql connected to the libraries build path, I am now getting this when it compiles along with some of the original errors: Driver Laoded com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure — goblin, May 03 '16 at 05:11
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. — goblin, May 03 '16 at 05:12
As an update, I figured out the connection error however now it will not access MySQL because of the password. I've tried to use the terminal on my mac to change it but anytime I try to use mysql the commands are not found. — goblin, May 03 '16 at 12:11

JavaFX GUI / MySQL retrieving records from a customers table

0 Answers0