[FIXED] org.sqlite.SQLiteException: [SQLITE_NOTADB] File opened that is not a database file (file is encrypted or is not a database) [NETBEANS]

Issue

So i got this form that I'm using but I get that error whenever i submit, it says CONNECTION SUCCESSFUL but then it returns the error and never insert nor retrieves anything from the db. I checked the version of the sqlite and everything, can't figure it out.

public class databaseConnection {

public static Connection connection = null;

public static Connection getConnection() throws ClassNotFoundException {
    try {
        System.out.println("CONNECTING");
        Class.forName("org.sqlite.JDBC");
        connection = DriverManager.getConnection("jdbc:sqlite:SoftwareDB.db");
        System.out.println("CONNECTION SUCCESSFUL");
    } catch (SQLException e) {
        System.out.println("ERROR: Connection Failed!");
    }
    return connection;
}

public static void login(String username, String password, String login) throws ClassNotFoundException {
    try {
        System.out.println("INSERTING");
        try (Statement stmt = getConnection().createStatement()) {
            String sql = "INSERT INTO login (username, password) VALUES ('" + username + "', '" + password + "', '" + login + "');";
            stmt.execute(sql);
        }
        getConnection().close();
        System.out.println("INSERT SUCCESSFUL");
    } catch (SQLException ex) {
        Logger.getLogger(Connection.class.getName()).log(Level.SEVERE, null, ex);
    }
}

public static ResultSet getLoginDetails(String query) throws SQLException, ClassNotFoundException {
    ResultSet rs;
    try (PreparedStatement ps = getConnection().prepareStatement(query)) {
        rs = ps.executeQuery();
        ps.close();
        getConnection().close();
    }
    return rs;
}

public static ResultSet getExistentDetails(String query) throws SQLException, ClassNotFoundException {
    ResultSet rs;
    try (PreparedStatement ps = getConnection().prepareStatement(query)) {
        rs = ps.executeQuery();
        getConnection().close();
    }
    return rs;
}

}

private void loginBtnMouseClicked(java.awt.event.MouseEvent evt) {                                      

    if (username.getText().isEmpty() || password.getText().isEmpty()) {
        infoLabel.setVisible(true);
        username.setText("");
        password.setText("");
    } else {
        try {
            
            databaseConnection.getLoginDetails("SELECT * FROM register WHERE email = '?' AND password = '?'");
            String ts = new SimpleDateFormat("dd.MM.yyyy - HH.mm.ss").format(System.currentTimeMillis());
            databaseConnection.login(username.getText(), password.getText(), ts);
            JOptionPane.showMessageDialog(null, "Login succesful!");
            new login().setVisible(true);

            infoLabel.setVisible(true);
            username.setText("");
            password.setText("");
        } catch (HeadlessException ex) {
            JOptionPane.showMessageDialog(null, "Failed!");
        } catch (SQLException | ClassNotFoundException ex) {
            Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}     

Output

Solution

I believe you have forgotten an important thing: properly preparing your PreparedStatement and opening/closing connections correctly.

Would you try the following rewritten getLoginDetails() method and take inspiration from it for the other methods?

public static ResultSet getLoginDetails(String query, String email, String password) throws SQLException, ClassNotFoundException {
    ResultSet rs;
    try (Connection conn = getConnection()) {   
        try (PreparedStatement ps = conn.prepareStatement(query)) {
            ps.setString(1,email);
            ps.SetString(2,password);
            rs = ps.executeQuery();    
           // Do something with the ResultSet here or do not close the statement!   
        }
    }
    return rs; // should be something else! (as it could be already closed)
}

Then you certainly need to do something with the ResultSet! For example: check that the email/password combination exists in order to validate the login request.

Also, some important remarks and tips:

• better check that the connection is valid after initialization using isValid(timeout)
• think about a connection pool or at least some ways to reuse your connection(s)
• eventually use existing tools (libraries like Apache) for your ORM (Object-Relation Mapping) and DAO (Database Access Object) layers. Actually, that's highly recommended.
• closing a PreparedStatement will automatically close the associated ResultSet. Your code does not take that into account. Cf. https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html

Keep me posted!

Answered By - bsaverino
Answer Checked By - Katrina (JavaFixing Volunteer)