clientsdao.java

struts客户管理系统,基于oracle9i,带有详细的数据库脚本 适合struts初学者学习建立好数据库后 可以直接拷贝srm 文件直接部署到tomcat中运行

//---------------------------------------------------------
// Application: Crm of Enterprice
// Author     : eSingle
// File       : ClientsDAO.java
//
// Copyright 2002 LandSoft Corp.
// Generated at Sun Nov 17 23:11:00 CST 2002
// Created by  曹广鑫
// mail to [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 ClientsDAO extends DAO {

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

  public void insert(Clients clients) throws SQLException {
    String sql;
    sql = "INSERT INTO clients (clientname, sex, phone, mobile, address, http, citycode) VALUES (?, ?, ?, ?, ?, ?, ?)";
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, clients.getClientname());
      pstmt.setString(2, clients.getSex());
      pstmt.setString(3, clients.getPhone());
      pstmt.setString(4, clients.getMobile());
      pstmt.setString(5, clients.getAddress());
      pstmt.setString(6, clients.getHttp());
      pstmt.setString(7, clients.getCitycode());
      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(Clients clients) throws SQLException {
  	Connection conn = null;
  	PreparedStatement pstmt = null;
    try {
      conn = ds.getConnection();
      String sql = "UPDATE clients SET sex=?, phone=?, mobile=?, address=?, http=?, citycode=? WHERE clientname=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, clients.getSex());
      pstmt.setString(2, clients.getPhone());
      pstmt.setString(3, clients.getMobile());
      pstmt.setString(4, clients.getAddress());
      pstmt.setString(5, clients.getHttp());
      pstmt.setString(6, clients.getCitycode());
      pstmt.setString(7, clients.getClientname());
      pstmt.executeUpdate();
      close(pstmt);
      conn.commit();
    } catch (SQLException e) {
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
  }

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

  public Clients retrieve(String clientname) throws SQLException {
    String[] objKeys = {"Clients", String.valueOf(clientname)};
    String objKey = CacheManager.createKey(objKeys);
    Clients clients = (Clients) DAOCacheManager.getCache(objKey);
    if (clients != null)
      return clients;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      String sql = "SELECT * FROM clients WHERE clientname=?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, clientname);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        clients = new Clients();
        clients.setClientname(rs.getString(1));
        clients.setSex(rs.getString(2));
        clients.setPhone(rs.getString(3));
        clients.setMobile(rs.getString(4));
        clients.setAddress(rs.getString(5));
        clients.setHttp(rs.getString(6));
        clients.setCitycode(rs.getString(7));
        populate(clients, rs);
      }
      close(rs);
      close(pstmt);
    } catch (SQLException e) {
      close(rs);
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
    DAOCacheManager.putCache(clients, objKey, 1);
    return clients;
  }

  public List list() throws SQLException {
    String[] objKeys = {"Clients", "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 clientname, sex, phone, mobile, address, http, citycode FROM clients";
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while(rs.next()) {
        Clients clients = new Clients();
        clients.setClientname(rs.getString(1));
        clients.setSex(rs.getString(2));
        clients.setPhone(rs.getString(3));
        clients.setMobile(rs.getString(4));
        clients.setAddress(rs.getString(5));
        clients.setHttp(rs.getString(6));
        clients.setCitycode(rs.getString(7));

        populate(clients, rs);
        list.add(clients);
      }
      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 = {"Clients", "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 clientname, sex, phone, mobile, address, http, citycode FROM clients";
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      if(offset > 0) rs.absolute(offset);
      int recCount = 0;
      while((recCount++ < limit) && rs.next()) {
        Clients clients = new Clients();
        clients.setClientname(rs.getString(1));
        clients.setSex(rs.getString(2));
        clients.setPhone(rs.getString(3));
        clients.setMobile(rs.getString(4));
        clients.setAddress(rs.getString(5));
        clients.setHttp(rs.getString(6));
        clients.setCitycode(rs.getString(7));

        populate(clients, rs);
        list.add(clients);
      }
      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;
  }

}