Java Sqlite-如何关闭连接

Question

我已经看到了许多关闭数据库连接的示例，其中人们在dao方法中使用 finally{}，但是在我的情况下，dao方法（ex：insertusers（））将其异常抛出了该方法叫做。在这种情况下，我该如何关闭连接？

尝试使用SELECT INSERT时，我将获得" SQLiteException -Database锁定"错误。

这是我的代码：

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();
}

主要应用程序

  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);
 }

Answer 1

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 */}
    }
}

来源： java中关闭数据库连接

Answer 2

@jale29的示例with java7-try-with-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
    }

我将如何实现DAO方法的示例：

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;
}

这样，您的业务逻辑就是这样：

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);
    }
}