categorydao.java~49~

对2000元以上的资产管理

package com.cdaccp.dao;

import com.util.DBAccess;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;
import com.cdaccp.entity.Category;

public class CategoryDAO {

  /**
   * 获取所有大类别信息
   * @return 返回包含大类信息的List 集合对象
   */
  public List getAllBigCategory() {
    List list = new ArrayList();
    Connection conn = DBAccess.getConnection();
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    String sql =
        "select cateid,catename,parentid from category where parentid=0";
    try {
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery();
      while (rs.next()) {
        Category cate = new Category();
        cate.setCateId(rs.getInt("cateid"));
        cate.setCateName(rs.getString("catename"));
        cate.setParentId(rs.getInt("parentid"));
        list.add(cate);
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    return list;
  }

  /**
   * 根据大类的编号获取该类的所有小类的集合
   * @param bigCateId int 大类编号
   * @return 返回该大类下小类的 List 集合
   */
  public List getSmallCategoryByBigCateId(int bigCateId) {
    List list = new ArrayList();
    Connection conn = DBAccess.getConnection(); //定义连接对象
    PreparedStatement pstmt = null; //定义SQL语句执行对象
    ResultSet rs = null; //定义结果集对象
    String sql = "select cateid,catename from category where parentid=" +
        bigCateId;
    try {
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery(); //执行查询
      while (rs.next()) {
        Category cate = new Category(); //每个Category对象承载着一条记录(即一个对象代表一行记录)
        cate.setCateId(rs.getInt("cateid"));
        cate.setCateName(rs.getString("catename"));
        cate.setParentId(bigCateId);
        list.add(cate); //将cate对象加入到集合中
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pstmt);
      DBAccess.closeConnection(conn);
    }
    return list; //返回集合对象
  }

  public static Category loadById(int cateId) {
    Category cate = null;
    Connection conn = DBAccess.getConnection(); //定义连接对象
    PreparedStatement pstmt = null; //定义SQL语句执行对象
    ResultSet rs = null; //定义结果集对象
    String sql = "select * from category where cateId=" + cateId;
    try {
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery(); //执行查询
      while (rs.next()) {
        cate = new Category(); //每个Category对象承载着一条记录(即一个对象代表一行记录)
        cate.setCateId(rs.getInt("cateid"));
        cate.setCateName(rs.getString("catename"));
        cate.setParentId(rs.getInt("parentId"));
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pstmt);
      DBAccess.closeConnection(conn);
    }

    return cate;
  }
  //添加一个大类
  public static boolean addCate(String name){
    boolean b = false;
    String sql = "insert category (catename ,parentid) values (?,0)";
    Category cate = null;
    Connection conn = DBAccess.getConnection(); //定义连接对象
    PreparedStatement pstmt = null; //定义SQL语句执行对象
    try {
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1,name);
      int i = pstmt.executeUpdate();
      if(i > 0){
        b = true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }finally{
      DBAccess.closeStatement(pstmt);
      DBAccess.closeConnection(conn);
    }
    return b;
  }
  //在某大类下添加一个小类
  public static boolean addsubCate(String name ,int daId){
    boolean b = false;
    String sql = "insert category (catename ,parentid) values (?,?)";
    Category cate = null;
    Connection conn = DBAccess.getConnection(); //定义连接对象
    PreparedStatement pstmt = null; //定义SQL语句执行对象
    try {
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1,name);
      pstmt.setInt(2,daId);
      int i = pstmt.executeUpdate();
      if(i > 0){
        b = true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }finally{
      DBAccess.closeStatement(pstmt);
      DBAccess.closeConnection(conn);
    }
    return b;
  }
  public static boolean updateName(Category category){
    boolean resurt = false;
    String sql = "update category set catename = ? where cateid = ? and parentid = ?";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    try {
      pst = con.prepareStatement(sql);
      pst.setString(1,category.getCateName());
      pst.setInt(2,category.getCateId());
      pst.setInt(3,category.getParentId());
      int i = pst.executeUpdate();
      if (i > 0) {
        resurt = true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }finally{
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return resurt ;
  }
}