I can't get the Sqlite database to communicate properly with my JavaFX application IDE - Eclipse packaging with Maven

Question

I have been having the most difficult time trying to package my application. The code is complete, I ended up using a JavaFX archetype with the Eclipse Maven plug-in. I continue to get errors when using the database, it seems like it is not a complete version of the database. I can go to the sqlite database manager from firefox and see the 6 users from the users table but I can only log in with 3 of them and then it gives me errors that the other tables do not exist. I am currently getting other errors right now, but I believe they are stemming from the database issue. My code for the login model is:

public class LoginModel {

String username, userLevel;

Connection connection;

public LoginModel () {

    connection = SqliteConnection.Connector();

    if (connection == null) {

        System.exit(1);
    }
}

public boolean isDbConnected () {

    try {

        return !connection.isClosed();

    } catch (SQLException e) {

        e.printStackTrace();
        return false;

    }
}

public boolean isLogin (String user, String pass) throws SQLException {
    //setUsername(user);
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    String query = "select * from users where username = ? and password = ?";

    try {
        // This is where the error is occuring
        preparedStatement = connection.prepareStatement(query);

        preparedStatement.setString(1, user);
        preparedStatement.setString(2, pass);
        resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            System.out.println(resultSet);
            userLevel = resultSet.getString(7);
            return true;

        } else {

            return false;

        }
    } catch (SQLException e) {

        System.out.println("Error is an SQLException");
        return false;

    } finally {

        preparedStatement.close();
        resultSet.close();

    }
}

}

I have commented where the error is occurring, I have done a system out println before and after, the before shows up and the after does not. It also prints out the SQLException as well, "Error is an SQLException". I have been reading posts on what people have done for the past 5 hours and am getting to my wits end. It appears that this needs a static connection while none of the other parts of the program uses a static connection (I have 2 database connection classes). Is it possible to keep the DB with the program location, or will it need to be placed somewhere on the users hard drive? Packaging a program seems to be insanely difficult, I had a much easier time learning how to code. Please help, thanks!

PrintStackTrace:

Error is an SQLException
Exception in thread "JavaFX Application Thread" java.lang.RuntimeException:     java.lang.reflect.InvocationTargetException
    at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1774)

I tried to print the whole stackTrace but it says my code isn't properly formatted... EDIT: Complete stacktrace.

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(CompositeEventH    andler.java:86)
    at     com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManag    er.java:238)
    at     com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManag    er.java:191)
    at     com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEve    ntDispatcher.java:59)
    at     com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.jav    a:58)
    at     com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl    .java:114)
    at     com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.jav    a:56)
    at     com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl    .java:114)
    at     com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.jav    a: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(ButtonBehavio    r.java:182)
    at     com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.jav    a:96)
    at     com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.jav    a:89)
    at     com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubbli    ngEvent(CompositeEventHandler.java:218)
    at     com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventH    andler.java:80)
    at     com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManag    er.java:238)
    at     com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManag    er.java:191)
    at     com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEve    ntDispatcher.java:59)
    at     com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.jav    a:58)
    at     com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl    .java:114)
    at     com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.jav    a:56)
    at     com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl    .java:114)
    at     com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.jav    a: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(Glass    ViewEventHandler.java:352)
    at     com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(Glass    ViewEventHandler.java:275)
    at java.security.AccessController.doPrivileged(Native Method)
    at     com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$355(Glas    sViewEventHandler.java:388)
    at     com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.jav    a:389)
    at     com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventH    andler.java:387)
    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$149(WinApplication.java:191)
    at java.lang.Thread.run(Thread.java:745)
    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.jav    a: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.jav    a: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:1769)
    ... 48 more
    Caused by: java.lang.NullPointerException
    at     tolmar.inventory.app.tolmarinventoryapplication.LoginModel.isLogin(LoginModel.ja    va:68)
    at      tolmar.inventory.app.tolmarinventoryapplication.LoginController.Login(LoginContr    oller.java:70)
... 58 more

Please edit the question to include the full stack trace of the error. To get he stack trace, change `System.out.println("Error is an SQLException")` to `e.printStackTrace()`. Likely the Stack trace may include some SQL error code which helps you track down your issue. — jewelsea, Apr 20 '16 at 05:45
Explain what "keep the DB with the program location" means vs "will it need to be placed somewhere on the users hard drive". If it means that that you are packaging the database inside a jar, then you can't do that as files inside a jar are not writable and a database must be writable. A database needs to be accessed as a file on the local machine or through a database server over a network. Where exactly is your database file currently? — jewelsea, Apr 20 '16 at 05:48
Thank you for the response jewelsea, the sqlite file is located in the root directory of the program itself. i.e. - E:\Programming\workspace, I created the project stemming from a single folder and when I created a maven project and started transferring files from the original project to the maven project I had to rename quite a few things to get some of the functionality back to the program, the database seems to be the largest issue. The program itself will be going onto a shared network and people can take a shortcut and put it on their desktops. Where is the best place to put the database? — dubskiski, Apr 20 '16 at 21:11
Yes, that is from the preparedStatement.close(). Which I believe it can't close the preparedStatement because it was never really created because of the connection from line 45, is it possible to see the '58 more'? When I had a system.out.println before and after line 45, the one before it would show up in the console, but not the one after line 45. — dubskiski, Apr 20 '16 at 21:27
You just need to put a null check around `preparedStatement.close();` first, then deal with the actual error — OneCricketeer, Apr 20 '16 at 21:28
is that just an if statement (if(preparedStatement == null) {System.out.println("is null");} else {preparedStatement.close()}), if so, it is saying "is null" — dubskiski, Apr 20 '16 at 21:33
When the maven project is packaged, would storing the database in the Main folder which holds all of the subfolders for the program be a good place to store the database? I would like to keep it with the program and not have to hardcode the database location to be on the C:\ and have to go there and specifically save the database at that location. Does an installer help with this issue, can you use inno setup to set the location of where you want the database to be stored? What if it ends up on a computer that doesn't have a C:\? Thank you so much for the help everyone. — dubskiski, Apr 20 '16 at 21:47
I ended up changing the database location to be on the E:\ drive and hard coded that location into my sqliteDB.java file and it is pulling up the Main.class, but I am getting a java.lang.IllegalArgumentException: Invalid URL: Invalid URL or resource not found Exception. I will investigate this further, what is the best practice for handling databases in programs? I don't think it would be to hard code it to be on a certain drive and then have to go and physically place the db on that drive in that location. I have never had to do that with a program before (that I use, this is first I made). — dubskiski, Apr 20 '16 at 22:11
Is the database shared between users and machines or is it specific to a given user and machine? If shared then deploy a database server and access it either directly over jdbc using a network address or via an application server (e.g. tomcat) that is connected to the database over jdbc. If it is not shared, then install it in a sub-directory you create under the [home directory](http://stackoverflow.com/questions/585534/what-is-the-best-way-to-find-the-users-home-directory-in-java) e.g. `System.getProperty("user.home") + "/.myapp"`. — jewelsea, Apr 20 '16 at 23:13
It does not seem that you included the stack trace for the SQLException as originally requested. — jewelsea, Apr 20 '16 at 23:21
Ok, thank you for the reply. I will look into using a database server, the database will be shared between users and machines. I was thinking it would be easier to put the program on the shared drive and give everyone shortcuts to the program and they can all access the same instance of the program (and db) via shortcuts. There will also be a huge repository of images and I have created a menuItem for the user to set the primary and backup locations for the images, if different instances of the program are running, then they won't share images, correct? I would like 1 location for resources. — dubskiski, Apr 20 '16 at 23:23
Sorry, that is the only stackTrace info it gave me. I have messed around with the code a bit now and I am only getting. java.lang.ClassNotFoundException: org.sqlite.JDBC — dubskiski, Apr 20 '16 at 23:25
Honestly I am not 100% sure what solved it, I had spent about 20 hours straight on this problem. Eventually (today) I moved the application .jar file out of the target location and into the main directory folder where the settings, src, and target folder are located and it was working fine from that location, it still will not work from the target location. — dubskiski, Apr 22 '16 at 04:07

I can't get the Sqlite database to communicate properly with my JavaFX application IDE - Eclipse packaging with Maven

0 Answers0