Java SQLite - how to close connection?

Question

I've seen many examples of closing database connections where people use finally{} in the DAO method, but in my case the DAO method (ex: insertUsers()) throws its exceptions to the method it's called. In this case, how can i close my connections?

I'm getting the "SQLiteException - Database is locked" error when trying to SELECT + INSERT.

Here's my code:

DAO

public static Connection con = null;
private static boolean hasData = false;

private void getConnection() throws ClassNotFoundException, SQLException {
    Class.forName("org.sqlite.JDBC");
    con = DriverManager.getConnection("jdbc:sqlite:ProjFarmacia.db");
    initialise();
}

private void initialise() throws SQLException {
   if( !hasData ){
       hasData = true;
       Statement state = con.createStatement();
       ResultSet res = state.executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='caixa'");
       if(!res.next()){
           Statement state2 = con.createStatement();
           state2.execute("CREATE TABLE caixa(id integer, timestamp integer, valorTotal double,  notas1 integer, notas2 integer,"
                   + " notas5 integer, notas10 integer, notas20 integer"
                   + "notas50 integer, notas100 integer, moedas1 integer, moedas5 integer, moedas10 integer, moedas25 integer"
                   + "moedas50 integer, moedas1R integer, primary key(id));");
       }   
   }
}




public ResultSet getCaixaByDate(long timestamp) throws ClassNotFoundException, SQLException{
    if(con == null){
        getConnection();
    }

    Statement state = con.createStatement();
    ResultSet res = state.executeQuery("SELECT * FROM caixa WHERE timestamp=" + "'" + timestamp + "'" + ";");
    return res;
}


public void createCaixa(Caixa caixa) throws ClassNotFoundException, SQLException{
    if(con == null){
        getConnection();
    }
    PreparedStatement prep = con.prepareStatement("INSERT INTO caixa VALUES(?,?);");
    prep.setLong(1, caixa.getTimestamp());
    prep.setDouble(2, caixa.getValorTotal());
    con.close();
}

MAIN APPLICATION

  try {
        ResultSet rs = caixaDAO.getCaixaByDate(timestamp);

        //If not exists in database
        if(!rs.next()){
            Caixa caixa = new Caixa();
            caixa.setTimestamp(timestamp);
            caixa.setValorTotal(venda.getValorDaVenda());

            //Inserting new Caixa
            caixaDAO.createCaixa(caixa);
        }else{
            System.out.println("Caixa already created!!!");
        }

    } catch (ClassNotFoundException | SQLException ex) {
        Logger.getLogger(VendaMedicamento.class.getName()).log(Level.SEVERE, null, ex);
 }

Your code is absolutely terrible. I suggest you read through a tutorial (or two) before attempting to write more code. You'll only run into trouble for writing non-standard code like that. — Kayaman, Dec 06 '16 at 10:03
@Kayaman thanks for sharing your point of view, but i will have to ignore it since it is a comment that doesn't show any help. — Henrique Giuliani, Dec 06 '16 at 11:01
Actually it's a very helpful comment. If you learn how to do things properly, you won't run into problems so often. If you ignore good advice and decide that you want to write bad code, you'll have to keep coming to SO for every single thing that goes wrong, *hoping* for advice. Don't be stupid, it's not a good quality in a programmer. — Kayaman, Dec 06 '16 at 11:09
No, actually is a very discouraging commentary. It could be very helpful if you have pointed the arguments to say that the code is "horrible". I respect your opinion, thats all. — Henrique Giuliani, Dec 06 '16 at 13:24

score 0 · Answer 1 · edited May 23 '17 at 12:24

0

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
    // Do stuff
    ...

} catch (SQLException ex) {
    // Exception handling stuff
    ...
} finally {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) { /* ignored */}
    }
    if (ps != null) {
        try {
            ps.close();
        } catch (SQLException e) { /* ignored */}
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) { /* ignored */}
    }
}

Source: Closing Database Connections in Java

edited May 23 '17 at 12:24

Community

1
1

answered Dec 06 '16 at 09:42

In Java7 this is possible with much less boilerplate code! Which Java version do you want to use @henrique – Niklas P Dec 06 '16 at 10:01
1.8, you can use `DbUtils` for non boilerplate code :-) – Dec 06 '16 at 10:13
I was thinking of try-with-resources – Niklas P Dec 06 '16 at 10:28
See my answer for the code with Java7 and try-with-resources – Niklas P Dec 06 '16 at 13:49

Niklas P · Answer 2 · 2016-12-06T17:10:45.813

-1

Example of @JaLe29 with Java7-try-with-resources

    try (Connection conn = DriverManager.getConnection("DB_URL","DB_USER","DB_PASSWORD");
            PreparedStatement ps = conn.prepareStatement("SQL");
            ResultSet rs = ps.executeQuery()) {

        // Do stuff with your ResultSet

    } catch (SQLException ex) {
        // Exception handling stuff
    }

Example of how I would implement a DAO method:

Caixa getCaixaByDate(long timestamp) {
    Caixa result = null;
    try(Connection con = getConnection();
            PreparedStatement statement = con.prepareStatement("SELECT * FROM caixa WHERE timestamp=?")) {
        statement.setLong(1, timestamp);

        try (ResultSet res = statement.executeQuery()) {
            result = new Caixa();
            result.setTimestamp(res.getLong("timestamp")); // this is just an example
            //TODO: mapping the other input from the ResultSet into the caixa object
        } catch (SQLException e) {
            result = null;
            Logger.getLogger("MyLogger").log(Level.SEVERE, "error while mapping ResultSet to Caixa: {0}", e.getMessage());
        }
    } catch (SQLException e) {
        Logger.getLogger("MyLogger").log(Level.SEVERE, "error while reading Caixa by date: {0}", e.getMessage());
    }
    return result;
}

This way your business logic would be this way:

public void createCaixaIfNotExist(long timestamp, double valorDaVenda) {
    if (caixaDao.getCaixaByDate(timestamp) == null) {
        Caixa newCaixa = new Caixa();
        newCaixa.setTimestamp(timestamp);
        newCaixa.setValorTotal(valorDaVenda);
        caixaDao.createCaixa(newCaixa);
    }
}

edited Dec 06 '16 at 17:10

answered Dec 06 '16 at 13:48

Niklas P

3,427
2
15
19

Thank you Niklas! But in my code, the try/catch isn't in the same class as the Connection object. Can i have your suggestion? – Henrique Giuliani Dec 06 '16 at 13:54
I don't know why your handling of a ResultSet is outside the DAO method/class. I wouldn't do that. I would handle the ResultSet within the DAO method and I would start every DAO method with `try (Connection conn = getConnection())` so that every DAO method uses it's own connection and ensures, that everything will be closed in the end. All such close-actions should be done within your DAO, otherwise you cannot be sure that your ResultSets, Statements or Connections will be closed at all. – Niklas P Dec 06 '16 at 14:02
very well observed, Niklas. Sincerely i don't have much experience with Java, so i was following a tutorial to create the DAOs. I will refactor to ensure that every method is closed. – Henrique Giuliani Dec 06 '16 at 16:28
Thank you very much, by the way – Henrique Giuliani Dec 06 '16 at 16:28
You're welcome. By the way, I've added an example for a possible way of designing a typical DAO method. – Niklas P Dec 06 '16 at 17:11
Thank you very much! I tried by my own and failed again... haha i will try to use your example!! – Henrique Giuliani Dec 06 '16 at 17:21
It's very annoying because it's the last part of the project... already done 95% of it – Henrique Giuliani Dec 06 '16 at 17:21
Niklas, just one more doubt: the rs.setTimestamp (for example) will update the value in the database? – Henrique Giuliani Dec 06 '16 at 17:33
No, the `ResultSet` is the result of a read operation (select-statement). I didn't use a set operation of the ResultSet. – Niklas P Dec 06 '16 at 17:45
Sorry, i realized that. I think the problem is in the getConnection() method, because everything is fine but it gets the same error – Henrique Giuliani Dec 06 '16 at 18:00
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/129937/discussion-between-niklas-p-and-henrique-giuliani). – Niklas P Dec 06 '16 at 18:43

Java SQLite - how to close connection?

2 Answers2

Linked

Related