1

updated

try
        {
            sqlite.setDbPath(dbPath);
            con = sqlite.connect();
            if(!con.isClosed()) 
            {
                String query="SELECT Username,Password FROM Apps WHERE Username ='"+username+"'"; // and Password='"+password+"'";
                ResultSet rs = con.createStatement().executeQuery(query);
                while(rs.next())
                {
                        if(rs.getString("Username").equals(username))//confronto se Username è gia esistente
                        {
                            trovato = true;
                            risultato = "gia' presente";
                        }
                }

                if(trovato==false) {
                    createUsers(username,password,name,surname,email,appname,ip,authorized,token);
                    risultato="inserito";
                }

                if(con!=null)
                {
                    con.close();
                }
                if(sqlite != null)
                {
                    sqlite.close();
                }
                if(rs != null)
                {
                    rs.close();
                }
            }

In try catch block I've open connection with database embedded but in first time i don't close all connection.. With if control the program close all open connection and works well

Improve this question
16
  • Do you have your database opened in some sql editor? That'll probably cause an error like that. Commented Jan 3, 2019 at 11:21
  • I try to close sqleditor but i've the same problem Commented Jan 3, 2019 at 11:26
  • IIRC SQLite only allows a single connection to the database, so if you have multiple connections it will fail. This may be a limitation of older versions though, so in that case you may need to upgrade the sqlite version you're using. That said, in my opinion, SQLite is not really a suitable database for a web application. Commented Jan 3, 2019 at 14:13
  • Your current code has a lot of potential resource leaks. I'd suggest that you rewrite your code to use try-with-resources, it will simplify things a lot and reduce the complexity of your code. Commented Jan 3, 2019 at 14:15
  • BTW: con.createStatement().close(); makes zero sense. You don't need to create a statement if you're going to immediately close it. Commented Jan 3, 2019 at 14:16

2 Answers 2

Reset to default
1

Update 2

Here is an abbreviated version using try-with-resource instead. Code is simpler and shorter

public String RegisterUser(... ) {
    boolean trovato = false;
    int authorized=0;

    SqliteConnection sqlite = new SqliteConnection();
    String dbPath="C:/Users/l.pellegrini/eclipse-workspace/ClayAPI_dbembedded/claydb.db";
    String query="SELECT Username,Password FROM Apps WHERE Username ='"+username+"' and Password='"+password+"'";

    try (java.sql.Connection con = sqlite.connect();
         Statement statement = con.createStatement();
         Statement updStatement = con.createStatement();
         ) {
        ResultSet rsActiveServices = con.createStatement().executeQuery(query);
        // handle result set as before
    } catch(SQLException e) {
        e.printStackTrace();
        System.out.println("errore" + e);
    }
    if(trovato==false) {
        createUser(username, password, name, surname, appname, email, ip)
    }
    return "username: " + username;
}


private createUser(String username, String password, String name, String surname, String appname, String email, String ip {
    String query1="INSERT INTO Apps (Username,Password,Name,Surname,Email,AppName,Ip,Authorized,Token) VALUES ('" + username + "'," + "'" +password +"','" + name + "','" + surname + "','" +email + "','" + appname + "','"+ip+"','"+authorized+"','"+token+"')";         
    SqliteConnection sqlite = new SqliteConnection();
    String dbPath="C:/Users/l.pellegrini/eclipse-workspace/ClayAPI_dbembedded/claydb.db";

    try (java.sql.Connection con = sqlite.connect();
         Statement statement = con.createStatement();) {
        updStatement.executeUpdate(query1);
    } catch(SQLException e) {
        e.printStackTrace();
        System.out.println("errore" + e);
    }
}

It could very well be that your prepared statement isn't properly closed. Change

ResultSet rsActiveServices = con.createStatement().executeQuery(query);

to

statement = con.createStatement();
ResultSet rsActiveServices = statement.executeQuery(query);

where statement is declared before try

java.sql.Connection con = null;
Statement statement = null;

and then close it in your finally clause

finally
{
    try
    {
        statement.close();
        con.close();
        sqlite.close();
    }

Update 1

I just noticed that your are trying to close your objects twice which is wrong, remove the first set of close calls and only close within finally {} at the end.

Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

finally { try { statement.close(); con.close(); sqlite.close(); } i can't put statement.close(); into finally
@lucapellegrini, simple mistake by me. You need to declare the variable outside the try clause. I'll update the answer
Using try-with-resources is probably better (and shorter).
@MarkRotteveel I know but this felt like the simplest way to move forward.
@MarkRotteveel link me the page
|
0

Resolved

I do errors with open and close connection in Main.. With SQLite the connection you have to open and close everytime it's carried out a query of all type(Insert, Delete,Update ecc..)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.