Search function to database in sqlite isn't working on Java project from javafx?

Question

I have a project for school I have coded using BlueJ, connecting to a database in sqlite and the visual side being from javafx for a pc application. I wanted to create a search bar that would search for the user ID of existing users. In the existing user controller class I have the following code linked to the search bar.

@FXML void searchReleased(){
        User.nameSearch(searchnamefield.getText());
    }

And in my model class for the user table in my database I have setup this code regarding the actual function of the search.

public static void nameSearch(String searchString)
{
    try 
    {

        PreparedStatement statement = Application.database.newStatement("SELECT FirstName, Surname, UserID FROM User WHERE Firstname LIKE '?%' OR Surname LIKE '?%'");             
        statement.setInt(1, UserID);

        if (statement != null)
        {
            Application.database.executeUpdate(statement);
        }
    }
    catch (SQLException resultsexception)
    {
        System.out.println("search result processing error: " + resultsexception.getMessage());
    }

}

Despite this it doesn't seem to work, the project compiles however when running and trying the search field, some error comes up in the log. can anyone see what's wrong and how I would test this working. Thanks.

Here's the error log

Exception in thread "JavaFX Application Thread" java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
    at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1774)
    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:54)
    at javafx.event.Event.fireEvent(Event.java:198)
    at javafx.scene.Scene$KeyHandler.process(Scene.java:3964)
    at javafx.scene.Scene$KeyHandler.access$1800(Scene.java:3910)
    at javafx.scene.Scene.impl_processKeyEvent(Scene.java:2040)
    at javafx.scene.Scene$ScenePeerListener.keyEvent(Scene.java:2501)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$KeyEventNotification.run(GlassViewEventHandler.java:197)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$KeyEventNotification.run(GlassViewEventHandler.java:147)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleKeyEvent$353(GlassViewEventHandler.java:228)
    at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleKeyEvent(GlassViewEventHandler.java:227)
    at com.sun.glass.ui.View.handleKeyEvent(View.java:546)
    at com.sun.glass.ui.View.notifyKey(View.java:966)
Caused by: java.lang.reflect.InvocationTargetException
    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:497)
    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:497)
    at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
    at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1771)
    ... 26 more
Caused by: java.lang.ArrayIndexOutOfBoundsException: 0
    at org.sqlite.core.CorePreparedStatement.batch(CorePreparedStatement.java:110)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.setInt(JDBC3PreparedStatement.java:291)
    at User.nameSearch(User.java:43)
    at ExistingUserScene.searchReleased(ExistingUserScene.java:147)
    ... 36 more

Answer 1

The immediate cause of the exception is that your parameter markers (?) in the SQL are inside quotes, so they are not interpreted as parameter markers at all. (The SQL is going to result in a search for names that literally begin with ?.) Consequently when you call statement.setInt(1, ...), it is looking for the first parameter marker: when it finds none it throws an ArrayIndexOutOfBoundsException. You probably intended something like

PreparedStatement statement = Application.database.newStatement("SELECT FirstName, Surname, UserID FROM User WHERE Firstname LIKE ? OR Surname LIKE ?");             

and then you would do

statement.setString(1, userID+"%");

Note that there are many more things wrong here, for example

• you don't search for the search string passed to the method
• you have two parameter markers but only set one
• you are setting an int for a parameter that needs to be a string
• you use executeUpdate(...) to execute a query
• you don't process the result of the query
• you check for null on a reference that cannot possibly be null

and maybe many others I haven't seen. So this change won't get rid of all the exceptions or make it work the way you want it, but it will fix the immediate exception about which you asked.