productsdao.java

来自「Java企业级开发项目实践,这是他的附光盘」· Java 代码 · 共 216 行

//---------------------------------------------------------
// Application: eShop of Network
// Author     : eSingle
// File       : ProductsDAO.java
//
// Copyright 2002 LandSoft Corp.
// Generated at Mon Nov 18 20:15:26 CST 2002
// Created by caoguangxin
// mailto:[e-mail]
//---------------------------------------------------------
//定义包名
package com.landsoft.eshop.dao;
//引用类包
import java.io.*;
import java.sql.*;
import java.util.*;
import javax.sql.*;

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

public class ProductsDAO extends DAO {

  public ProductsDAO(DataSource ds) {
    super(ds);
  }
//定义增加商品方法
  public void insert(Products products) throws SQLException {
    String sql;
    sql = "INSERT INTO products (productname, typesname, salename, martprice, favoprice, content) VALUES (?, ?, ?, ?, ?, ?)";//增加SQL
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, products.getProductname());
      pstmt.setString(2, products.getTypesname());
      pstmt.setString(3, products.getSalename());
      pstmt.setFloat(4, products.getMartprice());
      pstmt.setFloat(5, products.getFavoprice());
      pstmt.setString(6, products.getContent());
      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(Products products) throws SQLException {
  	Connection conn = null;
  	PreparedStatement pstmt = null;
    try {
      conn = ds.getConnection();
      String sql = "UPDATE products SET martprice=?, favoprice=?, content=? WHERE productname=? AND typesname=? AND salename=?";//更新SQL
      pstmt = conn.prepareStatement(sql);
      pstmt.setFloat(1, products.getMartprice());
      pstmt.setFloat(2, products.getFavoprice());
      pstmt.setString(3, products.getContent());
      pstmt.setString(4, products.getProductname());
      pstmt.setString(5, products.getTypesname());
      pstmt.setString(6, products.getSalename());
      pstmt.executeUpdate();
      close(pstmt);
      conn.commit();
    } catch (SQLException e) {
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
  }
//定义删除商品的方法
  public void delete(String productname, String typesname, String salename) throws SQLException {
  	Connection conn = null;
  	PreparedStatement pstmt = null;
    try {
      conn = ds.getConnection();
      String sql = "DELETE FROM products WHERE productname=? AND typesname=? AND salename=?";//定义删除SQL
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, productname);
      pstmt.setString(2, typesname);
      pstmt.setString(3, salename);
      pstmt.executeUpdate();
      close(pstmt);
      conn.commit();
    } catch (SQLException e) {
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
    String[] objKeys = {"Products", String.valueOf(productname), String.valueOf(typesname), String.valueOf(salename)};
    String objKey = CacheManager.createKey(objKeys);
    DAOCacheManager.invalidate(objKey);
  }
//定义检索商品的方法
  public Products retrieve(String productname, String typesname, String salename) throws SQLException {
    String[] objKeys = {"Products", String.valueOf(productname), String.valueOf(typesname), String.valueOf(salename)};
    String objKey = CacheManager.createKey(objKeys);
    Products products = (Products) DAOCacheManager.getCache(objKey);
    if (products != null)
      return products;
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      conn = ds.getConnection();
      String sql = "SELECT * FROM products WHERE productname=? AND typesname=? AND salename=?";//定义检索SQL
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, productname);
      pstmt.setString(2, typesname);
      pstmt.setString(3, salename);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        products = new Products();
        populate(products, rs);
      }
      close(rs);
      close(pstmt);
    } catch (SQLException e) {
      close(rs);
      close(pstmt);
      rollback(conn);
      e.printStackTrace();
    } finally {
    	close(conn);
    }
    DAOCacheManager.putCache(products, objKey, 1);
    return products;
  }

//取出所有商品记录
  public List list() throws SQLException {
    String[] objKeys = {"Products", "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 martprice, favoprice, content, productname, typesname, salename FROM products";//检索SQL
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while(rs.next()) {
        Products products = new Products();
        populate(products, rs);
        list.add(products);
      }
      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 = {"Products", "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 martprice, favoprice, content, productname, typesname, salename FROM products";//检索SQL
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      //分页控制
      if(offset > 0) rs.absolute(offset);
      int recCount = 0;
      while((recCount++ < limit) && rs.next()) {
        Products products = new Products();
        populate(products, rs);
        list.add(products);
      }
      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;
  }

}