Reading data from sql database using java
I am trying to read data from my ORDER table in my sql database, but every time I try I get the error:
run-single:
Database connected
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "ORDER" at line 1, column 15.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
at glassfish.Neasdasda.initializeDB1(Neasdasda.java:1495)
at glassfish.Neasdasda.order(Neasdasda.java:1463)
at glassfish.Neasdasda$3.handle(Neasdasda.java:103)
at glassfish.Neasdasda$3.handle(Neasdasda.java:99)
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:8413)
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:381)
at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$354(GlassViewEventHandler.java:417)
at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:416)
at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
at com.sun.glass.ui.View.notifyMouse(View.java:937)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.derby.client.am.SqlException: Syntax error: Encountered "ORDER" at line 1, column 15.
at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
... 52 more
I have no problem connecting to the database and there is no problem with the query statement I am using as I have used the same query statement to read from different tables.
public void order(Stage currentScreen2){
GridPane pane2 = new GridPane();
Button btShowDetails = new Button("Show Details");
pane2.add(btShowDetails, 1, 4);
pane2.setAlignment(Pos.CENTER);
pane2.setPadding(new Insets(40.5,42.5,43.5,40.5));
pane2.setHgap(15.5);
pane2.setVgap(20.5);
Scene scene5 = new Scene(pane2, 1200, 400);
currentScreen2.setTitle("Employees");
currentScreen2.setScene(scene5);
currentScreen2.show();
initializeDB();
//@Override // Override the start method in the Application class
initializeDB2();
hBox1.getChildren().addAll(new Label("ID"), tfid, btShowDetails);
vBox1.getChildren().addAll(hBox1, lblordid, lblcustid, lblprice, lbldescrip, lblpay);
tfid.setPrefColumnCount(6);
//Set event listener
btShowDetails.setOnAction(e -> showDetail());
// Create a scene and place it in the stage
}
private void initializeDB2() {
try {
// Establish a connection
Connection connection = DriverManager.getConnection
//Test1 is the name of the database you created
("jdbc:derby://localhost:1527/SandwichDB");
System.out.println("Database connected");
//Next we want to write the SQL statement we want to run. Note that the fields such as firstName and lastName are named the same as in the Table. The Question mark is the input we will later put in
String queryString = "SELECT * FROM ORDER WHERE ORDERID = ?";
// Create a statement
//Set the prepared Statement to run the queryString we previously set up
preparedStatementss = connection.prepareStatement(queryString);
}
catch (Exception ex) {
ex.printStackTrace();
}
}
private void showDetail() {
//This takes the number entered in the text field and puts it into a string called id
String id = tfid.getText();
try {
//Next we want to replace the ? in the previous query with the input
//The 1 is notifying we want to replace the first ?
preparedStatementss.setString(1, id);
ResultSet rset = preparedStatementss.executeQuery();
//Now it goes through each field and assigns them to a variable
if (rset.next()) {
String ordered = rset.getString(1);
String custard = rset.getString(2);
String pricerd = rset.getString(3);
String descripered = rset.getString(4);
String payered = rset.getString(5);
// Display result in a label
lblordid.setText("Order ID: " + ordered);
lblcustid.setText("Customer ID: " + custard);
lblprice.setText("Price: " + pricerd);
lbldescrip.setText("Description: " + descripered);
lblpay.setText("Payment Type: " + payered);
} else {
lblordid.setText("Not found");
}
}
catch (Exception ex) {
ex.printStackTrace();
}
}
Here is the code I am using to try and read from the database. Please keep in mind that he reads it from all other tables apart from this one, any help would be greatly appreciated.
+3
source to share