Java SQLite - 如何关闭连接？ [英] Java SQLite - how to close connection?

问题描述

我见过许多关闭数据库连接的例子，其中人们在DAO方法中使用 finally {} ，但在我的情况下是DAO方法（例如：insertUsers（） ）抛出它被调用的方法的异常。在这种情况下，我如何关闭我的连接？

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?

我在尝试 SELECT <时遇到SQLiteException - 数据库被锁定错误/ code> + INSERT 。

这是我的代码：

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

推荐答案

@ JaLe29的示例< a href =https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html\"rel =nofollow noreferrer> Java7-try-with-resources

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
    }

我将如何实现DAO方法：

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

这篇关于Java SQLite - 如何关闭连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！