EJB 3.0托管bean注入和数据库连接关闭

Question

在ejb 3.0 jboss 6环境中，我有一个名为DBInterface的bean，它被注入到许多dao类中以执行sql查询。DBInterface bean将数据源作为字段变量注入。DBInterface bean中的所有方法都从注入的数据源获取数据库连接，并在处理完db调用后关闭连接。在运行应用程序时，经过一段时间后，我得到sql异常，说不能创建db连接。我关闭了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();
         }
       }
}

Answer 1

你应该使用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用于不能从缓存中获益的性能关键型应用程序。