/*
 * Copyright 1998-2010 Linux.org.ru
 *    Licensed under the Apache License, Version 2.0 (the "License");
 *    you may not use this file except in compliance with the License.
 *    You may obtain a copy of the License at
 *
 *        http://www.apache.org/licenses/LICENSE-2.0
 *
 *    Unless required by applicable law or agreed to in writing, software
 *    distributed under the License is distributed on an "AS IS" BASIS,
 *    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *    See the License for the specific language governing permissions and
 *    limitations under the License.
 */

package ru.org.linux.spring.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.springframework.stereotype.Component;
import ru.org.linux.site.LorDataSource;
import ru.org.linux.site.Section;
import ru.org.linux.site.SectionNotFoundException;

import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;

@Component
public class SectionDao {
  private final ImmutableMap<Integer, Section> sections;
  private final ImmutableList<Section> sectionsList;

  public SectionDao() throws SQLException {
    Connection db = LorDataSource.getConnection();

    try {
      ImmutableMap.Builder<Integer, Section> sections = ImmutableMap.builder();
      ImmutableList.Builder<Section> sectionsList = ImmutableList.builder();

      Statement st = db.createStatement();

      ResultSet rs = st.executeQuery("SELECT id, name, imagepost, vote, moderate FROM sections ORDER BY id");

      while (rs.next()) {
        Section section = new Section(rs);

        sections.put(section.getId(), section);
        sectionsList.add(section);
      }

      this.sections = sections.build();
      this.sectionsList = sectionsList.build();
    } finally {
      db.close();
    }
  }

  public Section getSection(int id) throws SectionNotFoundException {
    Section section = sections.get(id);

    if (section==null) {
      throw new SectionNotFoundException(id);
    }

    return section;
  }

  public ImmutableList<Section> getSectionsList() {
    return sectionsList;
  }
}

@@ -664,113 +664,266 @@
 ort 
-java.sql.Connection;%0Aimport java.sql.ResultSet;%0Aimport java.sql.SQLException;%0Aimport java.sql.Statement;%0A
+com.google.common.collect.ImmutableList;%0Aimport com.google.common.collect.ImmutableMap;%0Aimport org.springframework.beans.factory.annotation.Autowired;%0Aimport org.springframework.jdbc.core.JdbcTemplate;%0Aimport org.springframework.jdbc.core.RowCallbackHandler;
 %0Aimp
@@ -972,48 +972,8 @@
 nt;%0A
-import ru.org.linux.site.LorDataSource;%0A
 impo
@@ -1065,94 +1065,85 @@
 ort 
-com.google.common.collect.ImmutableList;%0Aimport com.google.common.collect.ImmutableMap
+javax.sql.DataSource;%0Aimport java.sql.ResultSet;%0Aimport java.sql.SQLException
 ;%0A%0A@
@@ -1289,16 +1289,29 @@
 sList;%0A%0A
+  @Autowired%0A
   public
@@ -1322,16 +1322,29 @@
 tionDao(
+DataSource ds
 ) throws
@@ -1367,71 +1367,68 @@
     
-Connection db = LorDataSource.getConnection();%0A%0A    try %7B%0A     
+JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);%0A%0A    final
  Imm
@@ -1502,17 +1502,21 @@
 ();%0A    
- 
+final
  Immutab
@@ -1588,80 +1588,22 @@
     
-  
-Statement st = db.createStatement();%0A%0A      ResultSet rs = st.executeQ
+jdbcTemplate.q
 uery
@@ -1677,35 +1677,115 @@
  id%22
-);%0A%0A      while (rs.next())
+, new RowCallbackHandler() %7B%0A      @Override%0A      public void processRow(ResultSet rs) throws SQLException
  %7B%0A 
@@ -1918,23 +1918,29 @@
       %7D%0A
-%0A
+ 
    
+%7D);%0A%0A
    
+ 
 this.sec
@@ -1965,18 +1965,16 @@
 uild();%0A
-  
     this
@@ -2015,48 +2015,8 @@
 ();%0A
-    %7D finally %7B%0A      db.close();%0A    %7D%0A
   %7D%0A

use jdbcTemplate