CODEWITHSUNDEEP
javamysql
Ben Stone
Ben Stone

Reputation: 35

I have an appointment scheduling application I am trying to create

I am stopped at a point where I need to have a popup inform the user their appointment is in 15 minutes. When I run the code I get a sql error. Any help is appreciated.

I have put in real times in place of the variables and it works fine. When I put the variables back in I get the error listed below.

public static Appt apptIn15Min() {
Appt appointment;
LocalDateTime now = LocalDateTime.now();
ZoneId zid = ZoneId.systemDefault();
ZonedDateTime zdt = now.atZone(zid);
LocalDateTime ldt = zdt.withZoneSameInstant(ZoneId.of("UTC")).toLocalDateTime();
LocalDateTime ldt2 = ldt.plusMinutes(15);
String user = UserDB.getCurrentUser();
System.out.println(ldt);
System.out.println(ldt2);
System.out.println(user);
try {
    Statement statement = DBConnection.getConnection().createStatement();
    String query = "SELECT * FROM appointment WHERE start BETWEEN '" + ldt + "' AND '" + ldt2;
    ResultSet results = statement.executeQuery(query);
    System.out.println(query);
    System.out.println("Lookup started");
    if(results.next()) {
        appointment = new Appt(results.getInt("aptId"), results.getInt("customerId"), results.getString("start"),
            results.getString("end"), results.getString("contact"), results.getString("type"),
            results.getString("location"), results.getString("contact"));
        System.out.println("Lookup Completed");
        return appointment;
    }
} catch (SQLException e) {
    System.out.println("SQLException: " + e.getMessage());
}

Error: 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 ''2019-07-29T21:59:28.709' 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:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) at com.mysql.jdbc.Util.getInstance(Util.java:408) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2491) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2449) at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381) at Builds.ApptDB.apptIn15Min(ApptDB.java:216) at Interface.MainController.initialize(MainController.java:135) at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2548) at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2441) at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:3214) at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:3175) at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:3148) at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:3124) at javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:3104) at javafx.fxml.FXMLLoader.load(FXMLLoader.java:3097) at Interface.LoginController.tryLogin(LoginController.java:72) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71) at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275) at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1769) at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657) 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:394) 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$353(GlassViewEventHandler.java:432) at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389) at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:431) 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$147(WinApplication.java:177) at java.lang.Thread.run(Thread.java:748)

Upvotes: 0

Views: 1049

Answers (2)

Nexevis
Nexevis

Reputation: 4667

PreparedStatement

Just as an aside from Safeer Ansari's answer I will put an example of how to use PreparedStatement for this scenario and why it would be advantageous.

For LocalDateTime arguments, use PreparedStatement::setObject.

The Statement and String can be rewritten like this:

Connection con = DBConnection.getConnection();
String query = "SELECT * FROM appointment WHERE start BETWEEN ? AND ? ;";
PreparedStatement statement = con.prepareStatement(query)
statement.setObject(1,ldt);
statement.setObject(2,ldt2);
ResultSet rs = statement.executeQuery();

Notice how building the String query statement no longer requires any String concatenation and instead you use ? as the location to set the variables to. There are no downsides to using PreparedStatement over Statement when using a dynamic query.

Note: I have not included any closing of resources or exception handling to keep the example simple. Use try-with-resources for that chore.

Upvotes: 4

Safeer Ansari
Safeer Ansari

Reputation: 790

The error is in your query string as the there is no closing single-quotation after ldt2

This is how your query should look like:

String query = "SELECT * FROM appointment WHERE start BETWEEN '" + ldt + "' AND '" + ldt2 + "'";

Also, I would recommend to use String.format() for String-formatting purposes instead of trying to concatenate such complicated strings.

I hope this solves your problem

Upvotes: 2

Related Questions