java - EJB 3.0托管bean注入(inject)和数据库连接关闭

在ejb 3.0 jboss 6环境中，我有一个称为DBInterface的bean，该bean被注入许多dao类中以执行sql查询。 DBInterface bean将数据源作为字段变量注入。 DBInterface Bean中的所有方法都从注入的数据源获取数据库连接，并在处理db-calls之后关闭该连接。在运行应用程序时，一段时间后，我得到sql异常，说无法创建数据库连接。我正在finally块中的每个方法调用上关闭连接。我在哪里出错？我在jboss中使用ejb 3.0。
问候
V

public class DBInterface {
   @Resource(mappedName = "java:ora.ds", type = DataSource.class)
    private static DataSource dataSource;
    protected Connection getConnection(){
        try {
        return dataSource.getConnection();
       } catch (SQLException e) {
        e.printstacktrace();
       }
    }
    public void method1() {
      Connection connection = null;
         try {
               connection = getConnection();
                ...............
                 db codes
                .................
         } catch (SQLException e) {
        logger.error(e);
        throw new DBException("sql exception ", e);
        } finally {
             try {
                    closeResources(rs, statement, connection);
             } catch (SQLException e) {
                  logger.error(e);
                    throw new DBException("sql exception ", e);
           }
       }
    }
    public void closeResources(ResultSet rs, Statement st, Connection con)
        throws SQLException {
           if (rs != null) {
             rs.close();
           }
          if (st != null) {
                 st.close();
           }
          if (con != null) {
           con.close();
         }

       }
}

最佳答案

您应该使用try-catch块关闭所有资源。

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

应替换为：

if (rs != null) {
    try {
        rs.close();
    } catch (Exception exception) {
        logger.log("Failed to close ResultSet", exception);
    }
}
if (st != null) {
    try {
        st.close();
    } catch (Exception exception) {
        logger.log("Failed to close Statement", exception);
    }
}
if (con != null) {
    try {
        con.close();
    } catch (Exception exception) {
        logger.log("Failed to close Connection", exception);
    }
}

可以使用AbstractDAO类将其重构为更易于阅读的内容：

public class DAOException extends RuntimeException {
    public DAOException(Throwable cause) {
        super(cause);
    }
}

public abstract class AbstractDAO {
    private static Logger logger = ...;
    private DataSource dataSource;

    protected void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException exception) {
            // There's nothing we can do
            throw new DAOException(exception);
        }
    }

    public void close(Connection connection) {
        try {
            connection.close();
        } catch (Exception exception) {
            // Log the exception
            logger.log("Failed to close Connection", exception);
        }
    }

    public void close(Statement statement) {
        try {
            statement.close();
        } catch (Exception exception) {
            // Log the exception
            logger.log("Failed to close Statement", exception);
        }
    }

    public void close(ResultSet resultSet) {
        try {
            resultSet.close();
        } catch (Exception exception) {
            // Log the exception
            logger.log("Failed to close ResultSet", exception);
        }
    }
}

public class MyDAO extends AbstractDAO {
    @Override
    @Resource("jdbc/myDS")
    protected void setDataSource(DataSource dataSource) {
        super.setDataSource(dataSource);
    }

    public void insert(MyObject myObject) {
        Connection connection = getConnection();
        try {
            PreparedStatement query = connection.createPreparedStatement("INSERT INTO MYOBJECT (ID, VALUE) VALUES (?, ?)");
            try {
                query.setLong(1, myObject.getID());
                query.setString(2, myObject.getValue());
                if (query.executeUpdate() != 1) {
                    throw new DAOException("ExecuteUpdate did not return expected result");
                }
            } finally {
                close(query);
            }
        } catch (SQLException exception) {
            // There's nothing we can do
            throw new DAOException(exception);
        } finally {
            close(connection);
        }
    }
}

我想知道的是，为什么不使用JPA？我会考虑仅将JDBC用于无法从缓存中受益的对性能至关重要的应用程序。