数据库连接注意问题

public Long getSequenceByName(String sequenceName) throws DataAccessException {
  String sql = "select " + sequenceName + ".nextval  from dual";
  Long sequence;
  ResultSet rs = null;
  Statement state = null;
  Connection conn = null;
  try {
   Session session = this.getSession();
   conn = session.connection();
   state = conn.createStatement();
   rs = state.executeQuery(sql);
   if (rs.next()) {
    sequence = new Long(rs.getLong(1));
   } else {
    throw new RuntimeException("未找到Sequence: " + sql);
   }
  } catch (Exception e) {
   throw new UncheckedException("取Sequence: " + sql, e);
  } finally {
   try {
    if (rs != null) {
     rs.close();
    }
    if (state != null) {
     state.close();
    }
   } catch (SQLException e) {
    throw new UncheckedException("关闭数据库资源异常", e);
   }
  }
  return sequence;
 }

 

这种方式需要关闭资源但不需要关闭数据库连接

 

=====================================================

 

 public void updateByNameQuery(final String queryString, final String paramName,
   final Object value) {
  getHibernateTemplate().execute(new HibernateCallback() {
   public Object doInHibernate(Session session) throws HibernateException,
   SQLException {
    Query query = session.getNamedQuery(queryString);
    applyNamedParameterToQuery(query, paramName, value);
    query.executeUpdate();
    return null;
   }
  });
 }

 

这种不需要管连接和资源的释放

 

============================================================================

 

 /**
  * 监控数据库信息
  * @param tpLink
  * @throws Exception
  */
 public void searchDatabaseInfo(JdbcTemplate tpLink,List <DbMonitorStatus> dbMonitorStatusList,String aOrTP) throws Exception{

  Connection conn =null;
  Statement stmt =null;
  ResultSet rset = null;
  try{
       conn = tpLink.getDataSource().getConnection();//  tpLink>>>>>>>>>>JdbcTemplate

 

 

这种方式要关连接和资源

 

=======================================================================

 

 public void getPolicyState()throws Exception{

  List<InterfaceState> list=new ArrayList<InterfaceState>();
  InterfaceState ifs=null;
  
  Integer count=(Integer)this.interStateLink.execute(new StatementCallback(){//interStateLink>>>>>>>>>>>>>>>>>>>JdbcTemplate
   public Object doInStatement(Statement stmt) throws SQLException{
                ResultSet res=stmt.executeQuery("select count(1) from EUP.t_policy_effective_task t3 where t3.status='INIT'"); 
                int count=0;
                while(res.next()){
                 count=res.getInt(1);
                }
                return new Integer(count); 
            } 
        });
  
  if(null!=count){
   
   BasicDataSource ds=(BasicDataSource)this.interStateLink.getDataSource();
   String ip=ds.getUrl().split("\\:")[3];
   ifs=new InterfaceState();
   ifs.setIp(ip.replace("@",""));
   ifs.setValue(count);
   list.add(ifs);
   CoreServiceImpl.interfaceStateMap.put("policyStateList", list);
  }
 }

 

 

这种方式无需关数据库连接和资源

 

=============================================================================

 Spring DAO 对所有支持的数据访问技术框架都使用模板化技术进行了薄层的封装。只要您的程序都使用 Spring DAO 模板（如 JdbcTemplate、HibernateTemplate 等）进行数据访问，一定不会存在数据连接泄漏的问题 ―― 这是 Spring 给予我们郑重的承诺！因此，我们无需关注数据连接（Connection）及其衍生品（Hibernate 的 Session 等）的获取和释放的操作，模板类已经通过其内部流程替我们完成了，且对开发者是透明的

 

总结：通过JdbcTemplate得到连接时需要关闭连接和资源，通过 Session session = this.getSession();
   conn = session.connection();方式需要关闭资源但不需要关闭连接。通过JdbcTemplate.execute(...)方式无需关闭连接和资源。