0

I am trying to package a jar file containing a sqlite database for my application to run on another computer. After packaging the the application i tried running it in another computer and it throws this error :

I want to either create an exe file or a jar file so that i can give other users the file to run on there computers.

I created my database externally using sql DBbrowser and i added it to the resources folder within my application.

SQL error or missing database (no such table: users)

These are the tables in my database:

enter image description here

This is my code :

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javafx.scene.control.Alert;
import javafx.scene.control.Alert.AlertType;

public class DBConnection {
    private static Connection conn;
    private static String url = "jdbc:sqlite:mydb.db";
    private static String user = "root";//Username of database
    private static String pass = "yes";//Password of database

    public static Connection connect() throws SQLException {
        try {
            Class.forName("org.sqlite.JDBC").newInstance();
        } catch (ClassNotFoundException cnfe) {
            System.err.println("Error: " + cnfe.getMessage());
        } catch (InstantiationException ie) {
            System.err.println("Error: " + ie.getMessage());
        } catch (IllegalAccessException iae) {
            System.err.println("Error: " + iae.getMessage());
        }
        conn = DriverManager.getConnection(url);
        System.out.println("CONNECTED!!");
        return conn;
    }

    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        if (conn != null && !conn.isClosed())
            return conn;
        connect();
        return conn;
    }

    public static void infoBox(String infoMessage, String titleBar, String headerMessage) {

        Alert alert = new Alert(AlertType.INFORMATION);
        alert.setTitle(titleBar);
        alert.setHeaderText(headerMessage);
        alert.setContentText(infoMessage);
        alert.showAndWait();
    }
}

This is where i access the table called users:

import java.sql.ResultSet;
import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.fxml.Initializable;
import javafx.scene.Node;
import javafx.scene.Scene;
import javafx.scene.control.Alert;
import javafx.scene.control.Alert.AlertType;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.PasswordField;
import javafx.scene.control.TextField;
import javafx.stage.Stage;
import static pkg3treka.DBConnection.getConnection;

public class FXMLDocumentController implements Initializable {


@FXML
private Button button;

@FXML
private Label label;

@FXML
private TextField textField;

@FXML
private TextField textPassword;


Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;



public FXMLDocumentController() throws SQLException {
    try {

       connection = DBConnection.getConnection();
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);
    }
}


@FXML
void handleButtonAction(ActionEvent event) {
      boolean isAdmin = false;
    String username = textField.getText();
    String password = textPassword.getText();

    if( username.length()>=6 && "admin/".equals(username.substring(0,6))){
        isAdmin = true;
        username = textField.getText().substring(6);
        System.out.println("---->"+username);
    }


    String sql = "SELECT * FROM users WHERE U_username = ? and U_password = ?";

try{

    preparedStatement = connection.prepareStatement(sql);
    preparedStatement.setString(1, username);
    preparedStatement.setString(2, password);
    resultSet = (ResultSet) preparedStatement.executeQuery();

    if(!resultSet.next()){
    DBConnection.infoBox("Enter Correct Email and Password", "Failed", null);
      }else{
    try { 


    Stage stage = (Stage) button.getScene().getWindow();
    stage.close();
    FXMLLoader fxmlLoader = new FXMLLoader();  

    if(isAdmin){

        fxmlLoader.setLocation(getClass().getResource("ChooseRouteToOpen.fxml"));
    }else{

        fxmlLoader.setLocation(getClass().getResource("UserTimeSheetsView.fxml"));
        }
    Scene scene = new Scene(fxmlLoader.load());//next page size
    stage.setScene(scene);
    stage.show();

} catch (IOException e) {
    Logger logger = Logger.getLogger(getClass().getName());
    logger.log(Level.SEVERE, "Failed to create new Window.", e);
}
}



}catch(Exception e){
e.printStackTrace();
}

}




@Override
public void initialize(URL url, ResourceBundle rb) {
    try {
        // TODO
        if(DBConnection.getConnection() == null){
            label.setText("Status: DATABASE NOT CONNECTED!");
        }
        else {
            label.setText("Status: DATABASE CONNECTED!");
        }
    } catch (SQLException ex) {
        Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);
    } catch (ClassNotFoundException ex) {
        Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);
    }
    ;

}    

}

This is the error log :

Jan 14, 2018 12:31:04 PM javafx.fxml.FXMLLoader$ValueElement 

processValue
WARNING: Loading FXML document with JavaFX API of version 8.0.111 by JavaFX runtime of version 8.0.66
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: users)
    at org.sqlite.core.DB.newSQLException(DB.java:909)
    at org.sqlite.core.DB.newSQLException(DB.java:921)
    at org.sqlite.core.DB.throwex(DB.java:886)
    at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
    at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
    at org.sqlite.core.DB.prepare(DB.java:227)
    at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:41)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
    at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19)
    at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:263)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:235)
    at pkg3treka.FXMLDocumentController.handleButtonAction(FXMLDocumentController.java:117)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at sun.reflect.misc.Trampoline.invoke(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at sun.reflect.misc.MethodUtil.invoke(Unknown Source)
    at javafx.fxml.FXMLLoader$MethodHandler.invoke(Unknown Source)
    at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(Unknown Source)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEventImpl(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEvent(Unknown Source)
    at javafx.event.Event.fireEvent(Unknown Source)
    at javafx.scene.Node.fireEvent(Unknown Source)
    at javafx.scene.control.Button.fire(Unknown Source)
    at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(Unknown Source)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(Unknown Source)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(Unknown Source)
    at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(Unknown Source)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEventImpl(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEvent(Unknown Source)
    at javafx.event.Event.fireEvent(Unknown Source)
    at javafx.scene.Scene$MouseHandler.process(Unknown Source)
    at javafx.scene.Scene$MouseHandler.access$1500(Unknown Source)
    at javafx.scene.Scene.impl_processMouseEvent(Unknown Source)
    at javafx.scene.Scene$ScenePeerListener.mouseEvent(Unknown Source)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(Unknown Source)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$354(Unknown Source)
    at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(Unknown Source)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(Unknown Source)
    at com.sun.glass.ui.View.handleMouseEvent(Unknown Source)
    at com.sun.glass.ui.View.notifyMouse(Unknown Source)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: users)
    at org.sqlite.core.DB.newSQLException(DB.java:909)
    at org.sqlite.core.DB.newSQLException(DB.java:921)
    at org.sqlite.core.DB.throwex(DB.java:886)
    at org.sqlite.core.NativeDB.prepare_utf8(Native Method)
    at org.sqlite.core.NativeDB.prepare(NativeDB.java:127)
    at org.sqlite.core.DB.prepare(DB.java:227)
    at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:41)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30)
    at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19)
    at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:263)
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:235)
    at pkg3treka.FXMLDocumentController.handleButtonAction(FXMLDocumentController.java:117)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at sun.reflect.misc.Trampoline.invoke(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at sun.reflect.misc.MethodUtil.invoke(Unknown Source)
    at javafx.fxml.FXMLLoader$MethodHandler.invoke(Unknown Source)
    at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(Unknown Source)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEventImpl(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEvent(Unknown Source)
    at javafx.event.Event.fireEvent(Unknown Source)
    at javafx.scene.Node.fireEvent(Unknown Source)
    at javafx.scene.control.Button.fire(Unknown Source)
    at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(Unknown Source)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(Unknown Source)
    at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(Unknown Source)
    at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(Unknown Source)
    at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEventImpl(Unknown Source)
    at com.sun.javafx.event.EventUtil.fireEvent(Unknown Source)
    at javafx.event.Event.fireEvent(Unknown Source)
    at javafx.scene.Scene$MouseHandler.process(Unknown Source)
    at javafx.scene.Scene$MouseHandler.access$1500(Unknown Source)
    at javafx.scene.Scene.impl_processMouseEvent(Unknown Source)
    at javafx.scene.Scene$ScenePeerListener.mouseEvent(Unknown Source)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(Unknown Source)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$354(Unknown Source)
    at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(Unknown Source)
    at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(Unknown Source)
    at com.sun.glass.ui.View.handleMouseEvent(Unknown Source)
    at com.sun.glass.ui.View.notifyMouse(Unknown Source)
    at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at com.sun.glass.ui.win.WinApplication.lambda$null$148(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
kaddie
  • 233
  • 1
  • 5
  • 27

1 Answers1

0

In your FXMLDocumentController.java file at line 117, you are running a query againt a SQL table named "users". This table is missing in your database.

BluEOS
  • 576
  • 6
  • 13
  • The database has the table i have checked it – kaddie Jan 14 '18 at 11:12
  • No, you are using "mydb.db" as database without setting a complete path, SQLite will create an empty database if it doesn't exist. That's why the table is missing. Check in the patu you are running your app, you will find the empty database. PS: when SQLite says "no such table: users" , the table users is missing. There is no magic. – BluEOS Jan 14 '18 at 11:19
  • i created a database using sqlDB Browser and i added it to the resource file. – kaddie Jan 14 '18 at 11:24
  • Using "jdbc:sqlite:mydb.db" will open/create the "mydb.db" file in the directory you run "java", it will not magically loads the database from somewhere else. – BluEOS Jan 14 '18 at 11:29
  • Makes sense then how do i reference my database in the resource folder. – kaddie Jan 14 '18 at 11:31
  • Just set the file path in the url, ex on windows : "jdbc:sqlite:C:/sqlite/mydb.db" , on linux, "jdbc:sqlite:/sqlite/mydb.db". See https://stackoverflow.com/questions/6499218/how-to-use-sqlite-database-inside-jar-file if you want to load the database from the jar. – BluEOS Jan 14 '18 at 11:35
  • My problem regarding using a file path like C:/sqlite/mydb.db is that wont that cause problems when i run the jar file on a different PC. – kaddie Jan 14 '18 at 11:37