informationdao.java

本例采用sql server数据库。基于JBuilder的tomcat上运行。访问数据库基于struts的datasources.里面有详细的操作和设置说明。

//---------------------------------------------------------
// Application: Gsm of Application
// Author     : esingle
// File       : InformationDAO.java
//
// Copyright 2004 landsoft corp
// Generated at Wed Mar 10 15:35:57 CST 2004
// created by 曹广鑫
// mailto:[e-mail]
//---------------------------------------------------------

package com.landsoft.gsm.dao;

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

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

public class InformationDAO extends DAO {

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

  public void insert(Information information) throws SQLException {
    String sql;
    sql = "INSERT INTO information (mobile, store, productcode, quantity, price) VALUES (?, ?, ?, ?, ?)";
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, information.getMobile());
      pstmt.setString(2, information.getStore());
      pstmt.setString(3, information.getProductcode());
      pstmt.setString(4, information.getQuantity());
      pstmt.setString(5, information.getPrice());
      pstmt.executeUpdate();
      pstmt.close();
      sql = "SELECT MAX(infoid) FROM information";
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        information.setInfoid(rs.getInt(1));
      }
      close(rs);
      close(pstmt);
      conn.commit();
    } catch (SQLException sqle) {
      close(rs);
      close(pstmt);
      rollback(conn);
      sqle.printStackTrace();
      throw sqle;
    } finally {
    	close(conn);
    }
  }

  public void update(Information information) throws SQLException {
  	Connection conn = null;
  	PreparedStatement pstmt = null;
    try {
      conn = ds.getConnection();
      String sql = "UPDATE information SET mobile=?, store=?, productcode=?, quantity=?, price=? WHERE infoid=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, information.getMobile());
      pstmt.setString(2, information.getStore());
      pstmt.setString(3, information.getProductcode());
      pstmt.setString(4, information.getQuantity());
      pstmt.setString(5, information.getPrice());
      pstmt.setLong(6, information.getInfoid());
      pstmt.executeUpdate();
      close(pstmt);
      conn.commit();
    } catch (SQLException e) {
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
  }

  public void delete(long infoid) throws SQLException {
  	Connection conn = null;
  	PreparedStatement pstmt = null;
    try {
      conn = ds.getConnection();
      String sql = "DELETE FROM information WHERE infoid=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setLong(1, infoid);
      pstmt.executeUpdate();
      close(pstmt);
      conn.commit();
    } catch (SQLException e) {
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
    String[] objKeys = {"Information", String.valueOf(infoid)};
    String objKey = CacheManager.createKey(objKeys);
    DAOCacheManager.invalidate(objKey);
  }

  public Information retrieve(long infoid) throws SQLException {
    String[] objKeys = {"Information", String.valueOf(infoid)};
    String objKey = CacheManager.createKey(objKeys);
    Information information = (Information) DAOCacheManager.getCache(objKey);
    if (information != null)
      return information;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      String sql = "SELECT * FROM information WHERE infoid=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setLong(1, infoid);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        information = new Information();
        populate(information, rs);
      }
      close(rs);
      close(pstmt);
    } catch (SQLException e) {
      close(rs);
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
    DAOCacheManager.putCache(information, objKey, 1);
    return information;
  }

  public List list() throws SQLException {
    String[] objKeys = {"Information", "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 mobile, store, productcode, quantity, price, infoid FROM information";
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while(rs.next()) {
        Information information = new Information();
        populate(information, rs);
        list.add(information);
      }
      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 = {"Information", "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 mobile, store, productcode, quantity, price, infoid FROM information";
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      if(offset > 0) rs.absolute(offset);
      int recCount = 0;
      while((recCount++ < limit) && rs.next()) {
        Information information = new Information();
        populate(information, rs);
        list.add(information);
      }
      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 newQuery(int offset, int limit, String mobile, String productcode) throws SQLException {
    String[] objKeys = {"Information", "newQuery", String.valueOf(offset), String.valueOf(limit), String.valueOf(mobile), String.valueOf(productcode)};
    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 mobile, store, productcode, quantity, price, infoid information";
      sql += " WHERE mobile =? AND productcode =?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, mobile);
      pstmt.setString(2, productcode);
      rs = pstmt.executeQuery();
      if(offset > 0) rs.absolute(offset);
      int recCount = 0;
      while((recCount++ < limit) && rs.next()) {
        Information information = new Information();
        populate(information, rs);
        list.add(information);
      }
      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;
  }

}