CODEWITHSUNDEEP
javasqlitejsp
Reina297
Reina297

Reputation: 17

Why do I get "[SQLITE_BUSY] The database file is locked"?

I keep getting:

Error: org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked)

whenever I check if a username is unique and subsequently add them into my database. I'm using Java, JSP and JDBC.

Connection and queries:

public class UserDao {
    private static Connection conn = null;
    public static Connection connect() throws Exception {
        if(conn == null) {
            conn = (Connection) DriverManager.getConnection("jdbc:sqlite:c:/Users/ryo/database.db");
        } else {
            conn.close();
            conn = (Connection) DriverManager.getConnection("jdbc:sqlite:c:/Users/ryo/database.db");
        }
        return conn;
    }
    private String table;

    public UserDao(String table) {
        this.table = table;
    }

    public User findUser(String username) throws SQLException {
        PreparedStatement pStmt = conn.prepareStatement("SELECT * FROM " + table + " WHERE Username=?");
        pStmt.setString(1, username);

        try (
        ResultSet rs = pStmt.executeQuery()) {

            if(rs.next()) {
                return new User(
                        rs.getString("Name"),
                        rs.getString("Username"),
                        rs.getString("Password"),
                        rs.getBoolean("Admin")
                );
            }
        } catch (Exception e) {
                System.out.println("ERROR FINDING USER");
                e.printStackTrace();
                return null;
        }
        return null;
    }

    public long addUser(User user, String password) throws SQLException {
        PreparedStatement pStmt = conn.prepareStatement("INSERT INTO " + table + " (Username, Name, Password, Admin) VALUES(?,?,?,?)");
        pStmt.setString(1, user.getUsername());
        pStmt.setString(2, user.getName());
        pStmt.setString(3, password);
        pStmt.setBoolean(4, user.isAdmin());
        int rowsAdded = pStmt.executeUpdate();
        long allocatedId = 0L;
        if (rowsAdded == 1) {
            ResultSet rs = pStmt.getGeneratedKeys();
            if(rs.next()) {
                allocatedId = rs.getLong(1);
            }
        }
        return allocatedId;
    }

}

Adding and finding users:

<body>
        <%
            String name = request.getParameter("name");
            String username = request.getParameter("username");
            String password = request.getParameter("password");
            User toAdd = new User(name, username, password, false);
            User existingUser = null;
            boolean userCreated = false;
            try {
                Class.forName("org.sqlite.JDBC");
                UserDao Dao = new UserDao("Users");
                UserDao.connect();
                existingUser = Dao.findUser(username);
                if (existingUser == null) {
                    Dao.addUser(toAdd, password);
                    userCreated = true;
                }
            }
            catch (SQLException sqlException) {
                out.println("Error: " + sqlException + "\n");
            }
            finally {

            if(userCreated) {
            out.println("User created succesfully!\nNow try logging in!\n");
        %>
        <a href="./login.html">
            <input type="button" value="Login!" />
        </a>
        <% }
        else {
            out.println("User failed to be created! Already exists.\n Please try again.\n");
            %>
            <a href="./signup.html">
                <input type="button" value="Signup!" />
            </a>
        <%
            }
        }
        %>
    </body>

Any database browsers are closed as well.

Upvotes: -1

Views: 64

Answers (1)

Abdelhamed Abdin
Abdelhamed Abdin

Reputation: 594

The [SQLITE_BUSY] The database file is locked error in general (whether in Java or any other programming language) occurs when SQLite cannot acquire a lock on the database file, typically because another connection or process is using it.

  1. if you are opening sqlite GUI and altering any column at the same time you need to modify the DB through your terminal you will get this error. so, try to close the GUI you are using now and try to run your program. and that because there are multiple threads or processes are accessing the database simultaneously.

  2. the first point often happens but sometimes the issue occurs if the previous database connection wasn't closed properly which you have to use "try, catch" statements to open/close the DB properly.

  3. If your application uses multiple threads, ensure they share the same connection rather than creating a new one for each thread.

Upvotes: 0

Related Questions