SQL_LITE BUSY after trying to commit a Insert into SQLITE 3 Table Javafx

Question

After looking at Trouble inserting data in a SQLite databae, I tried to add 'c.commit();' before I leave the stage and closing the connection c. However it keeps giving me busy for some reason. Don't know if the dynamically added buttons 'addClass' are causing problems with the on action event handlers or not.

the controller file:

import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.scene.Node;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.scene.control.Alert;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.layout.GridPane;
import javafx.scene.text.TextAlignment;
import javafx.stage.Stage;
import userGroups.Student;

import java.io.IOException;
import java.sql.*;

public class ClassesController{
private Student user;
@FXML
private GridPane grid;
private int currentId;
private Connection c;
public void initialize(){
    Statement stmt;
    try{
        c = DriverManager.getConnection("jdbc:sqlite:users.db");
        //c.setAutoCommit(false);
        stmt = c.createStatement();

        int currentCol = 0;
        int currentRow = 1;
        currentId = 1;
        ResultSet rs = stmt.executeQuery("SELECT * FROM Classes;");
        //assuming all columns are NOT NULL
        while(rs.next()){
            Label label1 = new Label();
            label1.setText(rs.getString("CLASSNAME"));
            label1.setAccessibleText(rs.getString("CLASSNAME"));
            grid.add(label1, currentCol++, currentRow);

            Label label2 = new Label();
            label2.setText(rs.getString("SIZE"));
            grid.add(label2, currentCol++, currentRow);

            Label label3 = new Label();
            label3.setText(rs.getString("SPOTSTAKEN"));
            grid.add(label3, currentCol++, currentRow);

            Label label4 = new Label();
            label4.setText(rs.getString("INSTRUCTOR"));
            grid.add(label4, currentCol++, currentRow);

            Label label5 = new Label();
            label5.setText(rs.getString("CRN"));
            label5.setTextAlignment(TextAlignment.CENTER);
            grid.add(label5, currentCol++, currentRow);

            Label label6 = new Label();
            label6.setText(rs.getString("DAYS"));
            grid.add(label6, currentCol++, currentRow);

            Label label7 = new Label();
            label7.setText(militaryToRegularTime(rs.getString("STARTTIME")) + "-"
                    + militaryToRegularTime(rs.getString("ENDTIME")));
            grid.add(label7, currentCol++, currentRow);

            Label label8 = new Label();
            label8.setText(rs.getString("AREA"));
            grid.add(label8, currentCol++, currentRow);

            Button addClass = new Button();
            addClass.setMaxWidth(Double.MAX_VALUE);
            addClass.setText("Add Class");
            grid.add(addClass, currentCol, currentRow);
            addClass.setOnAction(new EventHandler<ActionEvent>() {
                @Override public void handle(ActionEvent e){
                    Object[] a = addClass.getProperties().values().toArray();//array of row and col values of addClass
                    int classIndex = Integer.parseInt(a[1].toString());//index of class corresponding to the button
                    String className = grid.getChildren().get(classIndex * 9).getAccessibleText();

                    if(studentHasClass(classIndex))//finds if student has the class at specific row of button
                        User.showAlert("You have already added this class!");
                    else
                        addClassToUser(classIndex, className);

                    addClass.setDisable(true);
                }
            });
            currentCol = 0;
            currentRow++;
            currentId++;
        }
        rs.close();
        stmt.close();
    } catch ( Exception e ){
        e.printStackTrace();
        User.showAlert("Please contact the admin.");
        System.exit(0);
    }
}
//adds class at classIndex to student user in database in table CLASSES_HAVE_STUDENTS
private void addClassToUser(int classIndex, String className){
    try{
        PreparedStatement statement = c.prepareStatement(
                "INSERT INTO CLASSES_HAVE_STUDENTS (CLASS_ID, CLASS_NAME, STUDENT_ID, S_USERNAME) VALUES(?,?,?,?)");
        statement.setInt(1, classIndex);
        statement.setString(2, className);
        statement.setInt(3, user.getId());
        statement.setString(4, user.getUserName());
        statement.executeUpdate();
        c.commit();
        statement.close();
    } catch ( Exception e ){
        e.printStackTrace();
        User.showAlert("Please contact the admin.");
        System.exit(0);
    }
}
//Return a boolean where if student user has a class at id
private boolean studentHasClass(int classIndex){
    Statement stmt;
    try{
        stmt = c.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT S_USERNAME FROM CLASSES_HAVE_STUDENTS WHERE CLASS_ID= " + classIndex + ";");

        while(rs.next()){
            if(rs.getString("S_USERNAME").equals(user.getUserName())){
                rs.close();
                stmt.close();
                return true;
            }
        }
        rs.close();
        stmt.close();
    } catch ( Exception e ){
        e.printStackTrace();
        User.showAlert("Please contact an admin.");
        System.exit(0);
    }
    return false;
}
//assumes militaryTime is in '00:00:00' format. returns regular time in '00:00' format
private String militaryToRegularTime(String militaryTime)
{
    Integer hour = Integer.parseInt(militaryTime.substring(0, 2));

    if(hour > 12){
        hour = hour - 12;
        return "0" + hour.toString() + militaryTime.substring(2, 5) + " pm";
    }
    return militaryTime.substring(0, 5) + " am";
}
//Changes stage to the previous stage
public void pressBack(ActionEvent event) throws IOException{
    FXMLLoader loader = new FXMLLoader();
    loader.setLocation(getClass().getResource("studentOptions.fxml"));
    loader.load();

    StudentOptionsController display = loader.getController();
    display.setUser(user);
    try{
        //c.commit();
        c.close();
    } catch (Exception e){
        e.printStackTrace();
        User.showAlert("Please contact the admin.");
        System.exit(0);
    }

    Parent userOption = loader.getRoot();
    Scene classesToAddScene = new Scene(userOption);
    Stage app_stage = (Stage) ((Node) event.getSource()).getScene().getWindow();
    app_stage.setScene(classesToAddScene);
    app_stage.show();
}

public void setUser(Student user){
    this.user = user;
}

}

stacktrace:

org.sqlite.SQLiteException: [SQLITE_BUSY]  The database file is locked (database is locked)
at org.sqlite.core.DB.newSQLException(DB.java:909)
at org.sqlite.core.DB.newSQLException(DB.java:921)
at org.sqlite.core.DB.execute(DB.java:822)
at org.sqlite.core.DB.executeUpdate(DB.java:863)
at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:99)
at login.ClassesController.addClassToUser(ClassesController.java:123)
at login.ClassesController.access$200(ClassesController.java:21)
at login.ClassesController$1.handle(ClassesController.java:94)
at login.ClassesController$1.handle(ClassesController.java:80)
at javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
at javafx.base/javafx.event.Event.fireEvent(Event.java:198)
at javafx.graphics/javafx.scene.Node.fireEvent(Node.java:8863)
at javafx.controls/javafx.scene.control.Button.fire(Button.java:200)
at javafx.controls/com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:206)
at javafx.controls/com.sun.javafx.scene.control.inputmap.InputMap.handle(InputMap.java:274)
at javafx.base/com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
at javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
at javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
at javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
at javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
at javafx.base/javafx.event.Event.fireEvent(Event.java:198)
at javafx.graphics/javafx.scene.Scene$MouseHandler.process(Scene.java:3876)
at javafx.graphics/javafx.scene.Scene$MouseHandler.access$1300(Scene.java:3604)
at javafx.graphics/javafx.scene.Scene.processMouseEvent(Scene.java:1874)
at javafx.graphics/javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2613)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:397)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
at java.base/java.security.AccessController.doPrivileged(Native Method)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$2(GlassViewEventHandler.java:434)
at javafx.graphics/com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:389)
at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:433)
at javafx.graphics/com.sun.glass.ui.View.handleMouseEvent(View.java:556)
at javafx.graphics/com.sun.glass.ui.View.notifyMouse(View.java:942)
at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:175)
at java.base/java.lang.Thread.run(Thread.java:844)

This normally happens to me when I have another program like `DB Browser for SQLite` open with changes that were never saved while trying to run my program. — SedJ601, Mar 30 '18 at 13:45
I don't understand why you have this twice `ResultSet rs = stmt.executeQuery("SELECT * FROM Classes WHERE IDCLASSES= " + currentId + ";");`? — SedJ601, Mar 30 '18 at 13:48
You need to let a different class handle all your DB operations. Your code is very confusing and does not seem to follow a logical flow. — SedJ601, Mar 30 '18 at 13:50
@Sedrick 1. Are you talking about other instances in the project where I opened the same database and updated it without committing? If so, I will have to go through the controllers to see which one I missed. 2. The 2nd rs is to update the first one, this time it increments 'currentID' by 1 after rs is set to the updated query. — Michael Lin, Mar 30 '18 at 14:05
i.e. it grabs another row at the 'classID' Probably a better way to do it but I didnt spend time trying to figure out. 3. First time integrating DB operations of any kind into a project so I wouldn't know lol. A guide/link would be nice. Lastly, I don't think select effects this much because they don't update the db. If I remove 'c.commit()' and add in 'c.setAutoCommit(false);', the code will run fine, however the insert will not add to the db. — Michael Lin, Mar 30 '18 at 14:15
In your `initialize` method you do `ResultSet rs = ...`. Then at the bottom of your `initialize` method in the `while(rs.next())` your do `ResultSet rs = ...` again. This code makes no sense. Like I said you should create a Database Handler class that only handle database communications. — SedJ601, Mar 30 '18 at 14:15
I'm trying to grab the rows from classes row by row instead of grabbing them all at once. i.e. ResultSet rs = stmt.executeQuery("SELECT * FROM Classes WHERE IDCLASSES= " + currentId + ";"); only grabs one row. — Michael Lin, Mar 30 '18 at 14:27
This does not make any sense. You either `SELECT * FROM Classes ` to get everything or `SELECT * FROM Classes WHERE IDCLASSES= " + currentId + ";"` to get a single row. — SedJ601, Mar 30 '18 at 14:31
Either way, your best bet is to do like I said earlier. So that your code is clear and more understandable. — SedJ601, Mar 30 '18 at 14:32
Ok updated the controller class. Do you have a link on how to setup a db handler class? As I said before, first time integrating db in a project. — Michael Lin, Mar 30 '18 at 14:35
I created [this](https://github.com/sedj601/SQLitePersonTableViewExample) project for you. — SedJ601, Mar 30 '18 at 16:21
Do you need to close resultset, statement(preparedstatement), and connection? Didn't seem to find any of those in your code, however there is a close method in the dbhandler. — Michael Lin, Mar 31 '18 at 07:36
Yea you can close the connection when the program ends or after you complete a task. If you do it after each task, you dbhandlers needs to be local to each task. — SedJ601, Mar 31 '18 at 13:57

SQL_LITE BUSY after trying to commit a Insert into SQLITE 3 Table Javafx

0 Answers0