logdao.java

JSP+Struts+缓存的公司管理系统

//---------------------------------------------------------
// Application: Company Applcation
// Author     : Cao guangxin
// File       : LogDAO.java
//
// Copyright 2006 RelationInfo Software
// Writed at Wed Apr 12 08:58:55 CST 2006
// writed by Eclipse SDK
// Visit http://www.37signals.cn
//---------------------------------------------------------

package net.cn37signals.company.dao;

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

import net.cn37signals.company.model.*;
import net.cn37signals.company.util.CacheManager;

public class LogDAO extends DAO {

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

  public void insert(Log log) throws SQLException {
    String sql;
    sql = "INSERT INTO log (year, month, byte_in, byte_out, capacity, charge) VALUES (?, ?, ?, ?, ?, ?)";
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, log.getYear());
      pstmt.setInt(2, log.getMonth());
      pstmt.setLong(3, log.getByteIn());
      pstmt.setLong(4, log.getByteOut());
      pstmt.setFloat(5, log.getCapacity());
      pstmt.setDouble(6, log.getCharge());
      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(Log log) throws SQLException {
  	Connection conn = null;
  	PreparedStatement pstmt = null;
    try {
      conn = ds.getConnection();
      String sql = "UPDATE log SET byte_in=?, byte_out=?, capacity=?, charge=? WHERE year=? AND month=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setLong(1, log.getByteIn());
      pstmt.setLong(2, log.getByteOut());
      pstmt.setFloat(3, log.getCapacity());
      pstmt.setDouble(4, log.getCharge());
      pstmt.setInt(5, log.getYear());
      pstmt.setInt(6, log.getMonth());
      pstmt.executeUpdate();
      close(pstmt);
      conn.commit();
    } catch (SQLException e) {
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
  }

  public void delete(int year, int month) throws SQLException {
  	Connection conn = null;
  	PreparedStatement pstmt = null;
    try {
      conn = ds.getConnection();
      String sql = "DELETE FROM log WHERE year=? AND month=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, year);
      pstmt.setInt(2, month);
      pstmt.executeUpdate();
      close(pstmt);
      conn.commit();
    } catch (SQLException e) {
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
    String[] objKeys = {"Log", String.valueOf(year), String.valueOf(month)};
    String objKey = CacheManager.createKey(objKeys);
    DAOCacheManager.invalidate(objKey);
  }

  public Log retrieve(int year, int month) throws SQLException {
    String[] objKeys = {"Log", String.valueOf(year), String.valueOf(month)};
    String objKey = CacheManager.createKey(objKeys);
    Log log = (Log) DAOCacheManager.getCache(objKey);
    if (log != null)
      return log;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      String sql = "SELECT * FROM log WHERE year=? AND month=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, year);
      pstmt.setInt(2, month);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        log = new Log();
        populate(log, rs);
      }
      close(rs);
      close(pstmt);
    } catch (SQLException e) {
      close(rs);
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
    DAOCacheManager.putCache(log, objKey, 10);
    return log;
  }

  public List list() throws SQLException {
    String[] objKeys = {"Log", "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 byte_in, byte_out, capacity, charge, year, month FROM log";
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while(rs.next()) {
        Log log = new Log();
        populate(log, rs);
        list.add(log);
      }
      close(rs);
      close(pstmt);
    } catch (SQLException e) {
      close(rs);
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
    DAOCacheManager.putCache(list, objKey, 10);
    return list;
  }

  public List list(int offset, int limit) throws SQLException {
    String[] objKeys = {"Log", "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 byte_in, byte_out, capacity, charge, year, month FROM log";
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      if(offset > 0) rs.absolute(offset);
      int recCount = 0;
      while((recCount++ < limit) && rs.next()) {
        Log log = new Log();
        populate(log, rs);
        list.add(log);
      }
      close(rs);
      close(pstmt);
    } catch (SQLException e) {
      close(rs);
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
    DAOCacheManager.putCache(list, objKey, 10);
    return list;
  }

}