citydao.java

客户资料管理系统——来自人民邮电出版社《Java信息管理系统开发实例导航》的第六章

//---------------------------------------------------------
// Application: Crm of Enterprice
// Author     : eSingle
// File       : CityDAO.java
//
// Copyright 2004 LandSoft Corp.
// Writed at Thu Jun 24 15:45:10 CST 2004
// 作者：曹广鑫
// mailto:[e-mail]
//---------------------------------------------------------

package com.landsoft.crm.dao;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.sql.*;

import com.landsoft.crm.model.*;
import com.landsoft.crm.util.CacheManager;

public class CityDAO
    extends DAO {

  public CityDAO(DataSource ds) {
    super(ds);
  }

  public void insert(City city) throws SQLException {
    String sql;
    sql = "INSERT INTO city (citycode, nationcode, provincecode, cityname) VALUES (?, ?, ?, ?)";
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, city.getCitycode());
      pstmt.setString(2, city.getNationcode());
      pstmt.setString(3, city.getProvincecode());
      pstmt.setString(4, city.getCityname());
      pstmt.executeUpdate();
      pstmt.close();
      conn.commit();
    }
    catch (SQLException sqle) {
      close(rs);
      close(pstmt);
      rollback(conn);
      sqle.printStackTrace();
      throw sqle;
    }
    finally {
      close(conn);
    }
  }

  public void update(City city) throws SQLException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = ds.getConnection();
      String sql = "UPDATE city SET cityname=? WHERE citycode=? AND nationcode=? AND provincecode=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, city.getCityname());
      pstmt.setString(2, city.getCitycode());
      pstmt.setString(3, city.getNationcode());
      pstmt.setString(4, city.getProvincecode());
      pstmt.executeUpdate();
      close(pstmt);
      conn.commit();
    }
    catch (SQLException e) {
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    }
    finally {
      close(conn);
    }
  }

  public void delete(String citycode, String nationcode, String provincecode) throws
      SQLException {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = ds.getConnection();
      String sql =
          "DELETE FROM city WHERE citycode=? AND nationcode=? AND provincecode=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, citycode);
      pstmt.setString(2, nationcode);
      pstmt.setString(3, provincecode);
      pstmt.executeUpdate();
      close(pstmt);
      conn.commit();
    }
    catch (SQLException e) {
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    }
    finally {
      close(conn);
    }
    String[] objKeys = {
        "City", String.valueOf(citycode), String.valueOf(nationcode),
        String.valueOf(provincecode)};
    String objKey = CacheManager.createKey(objKeys);
    DAOCacheManager.invalidate(objKey);
  }

  public City retrieve(String citycode, String nationcode, String provincecode) throws
      SQLException {
    String[] objKeys = {
        "City", String.valueOf(citycode), String.valueOf(nationcode),
        String.valueOf(provincecode)};
    String objKey = CacheManager.createKey(objKeys);
    City city = (City) DAOCacheManager.getCache(objKey);
    if (city != null) {
      return city;
    }
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      String sql =
          "SELECT * FROM city WHERE citycode=? AND nationcode=? AND provincecode=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, citycode);
      pstmt.setString(2, nationcode);
      pstmt.setString(3, provincecode);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        city = new City();
        //populate(city, rs);
        city.setCitycode(rs.getString(1));
        city.setNationcode(rs.getString(2));
        city.setProvincecode(rs.getString(3));
        city.setCityname(rs.getString(4));
      }
      close(rs);
      close(pstmt);
    }
    catch (SQLException e) {
      close(rs);
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    }
    finally {
      close(conn);
    }
    DAOCacheManager.putCache(city, objKey, 1);
    return city;
  }

  public List list() throws SQLException {
    String[] objKeys = {
        "City", "list"};
    String objKey = CacheManager.createKey(objKeys);
    ArrayList list = (ArrayList) DAOCacheManager.getCache(objKey);
    if (list != null) {
      return list;
    }
    list = new ArrayList();
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      String sql =
          "SELECT cityname, citycode, nationcode, provincecode FROM city";
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        City city = new City();
        city.setCitycode(rs.getString(1));
        city.setNationcode(rs.getString(2));
        city.setProvincecode(rs.getString(3));
        city.setCityname(rs.getString(4));
        list.add(city);
      }
      close(rs);
      close(pstmt);
    }
    catch (SQLException e) {
      close(rs);
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    }
    finally {
      close(conn);
    }
    DAOCacheManager.putCache(list, objKey, 1);
    return list;
  }

  public List list(int offset, int limit) throws SQLException {
    String[] objKeys = {
        "City", "list", String.valueOf(offset), String.valueOf(limit)};
    String objKey = CacheManager.createKey(objKeys);
    ArrayList list = (ArrayList) DAOCacheManager.getCache(objKey);
    if (list != null) {
      return list;
    }
    list = new ArrayList();
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      String sql =
          "SELECT cityname, citycode, nationcode, provincecode FROM city";
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      if (offset > 0) {
        rs.absolute(offset);
      }
      int recCount = 0;
      while ( (recCount++ < limit) && rs.next()) {
        City city = new City();
        city.setCitycode(rs.getString(1));
        city.setNationcode(rs.getString(2));
        city.setProvincecode(rs.getString(3));
        city.setCityname(rs.getString(4));
        list.add(city);
      }
      close(rs);
      close(pstmt);
    }
    catch (SQLException e) {
      close(rs);
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    }
    finally {
      close(conn);
    }
    DAOCacheManager.putCache(list, objKey, 1);
    return list;
  }

}