favoritedao.java

广州物价系统开发部分模块

package gzwj.dao;

import gzwj.bean.FavoriteBeanExtends;
import gzwj.iface.DaoCommon;
import gzwj.iface.FormBean;
import gzwj.sql.Dbpool;

import java.sql.ResultSet;
import java.util.ArrayList;

public class FavoriteDAO extends Dao implements DaoCommon {

	public FavoriteDAO() throws Exception {
		this.db = new Dbpool();
		db.getPool(this.Constant.connectName);
	}

	public Object queryExecute() throws Exception {

		ArrayList list = new ArrayList();
		// ArrayList myFamily = new ArrayList();
		// sql 加载到收藏文件夹时，默认查找数据
		String sql = "select f.FavoriteFile_Name,f.FavoriteFile_Url,c.Catelory_Name,fo.User_ID from  O_FavoriteFile_Master as f,O_Catelory_Master as c,O_FavoriteFile_Catelory as fo where fo.FavoriteFile_ID=f.FavoriteFile_ID and fo.Catelory_ID=c.CateloryParent_ID";
		ResultSet set;
		set = db.getRst(sql);
		while (set.next()) {
			FavoriteBeanExtends favoriteBeanExtends = new FavoriteBeanExtends();
			favoriteBeanExtends.setUser_Id(set.getInt("User_ID")); // 获得 用户ID
			favoriteBeanExtends.setFavoriteFile_Name(set.getString("FavoriteFile_Name")); // 获得
			// 收藏网页名称
			favoriteBeanExtends.setFavoriteFile_Url(set.getString("FavoriteFile_Url")); // 获得
			// 收藏网页地址
			favoriteBeanExtends.setCatelory_Name(set.getString("Catelory_Name")); // 获得
			// 收藏网页名类型名称
			list.add(favoriteBeanExtends);
		}
		return list;

	}

	public Object queryAllCatelory(int CateloryParent_ID, int CateloryChild_ID) {
		ArrayList list = new ArrayList();
		String sql = "SELECT CateloryParent_ID , CateloryChild_ID ,  Catelory_Name , Catelory_Type FROM O_Catelory_Master  ";
		if (CateloryParent_ID == -99) {
			sql = sql + "where  CateloryChild_ID<" + CateloryChild_ID;
		} else {
			sql = sql + " where  CateloryChild_ID>0" + CateloryChild_ID + " and CateloryParent_ID="
					+ CateloryParent_ID;
		}
		System.out.println(sql);
		ResultSet set;
		try {
			set = db.getRst(sql);
			while (set.next()) {
				FavoriteBeanExtends favoriteBeanExtends = new FavoriteBeanExtends();
				favoriteBeanExtends.setCateloryParent_ID(set.getInt("CateloryParent_ID"));
				String getFavoriteFileSQL="select FavoriteFile_ID from O_FavoriteFile_Catelory where CateloryParent_ID="+set.getInt("CateloryParent_ID");
				System.out.println("getFavoriteFileSQL---:"+getFavoriteFileSQL);
				ResultSet frs=db.executeQuery(getFavoriteFileSQL);
				ArrayList listFavoriteFile=new ArrayList();
				while(frs.next()){
			//		System.out.println("FavoriteFile_ID-----:"+frs.getInt("FavoriteFile_ID"));
					listFavoriteFile.add(frs.getInt("FavoriteFile_ID"));
					favoriteBeanExtends.setList(listFavoriteFile);
				}
				favoriteBeanExtends.setCateloryChild_ID(set.getInt("CateloryChild_ID"));
				favoriteBeanExtends.setCatelory_Name(set.getString("Catelory_Name"));
				favoriteBeanExtends.setCateloryType(set.getInt("Catelory_Type"));
				list.add(favoriteBeanExtends);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public void delFavorite(String[] cateloryParentIDs) {
		try {
			for (int i = 0; i < cateloryParentIDs.length; i++) {
				String delSql = "delete O_Catelory_Master where CateloryParent_ID="
						+ cateloryParentIDs[i];

				if (db.Update(delSql) > 0) {
					System.out.println("删除O_Catelory_Master成功---sql:" + delSql);
				}
				// 根据cateloryParentID查找出所有的FavoriteFile_Id。
				String queryFavoriteFileID = "select FavoriteFile_ID from O_FavoriteFile_Catelory where CateloryParent_ID="
						+ cateloryParentIDs[i];
				ResultSet qrs=db.executeQuery(queryFavoriteFileID);
				while(qrs.next()){
					//					 删除该类别下的所有网页
					String delFSql = "delete O_FavoriteFile_Master where FavoriteFile_ID="+qrs.getInt("FavoriteFile_ID");
					if(db.Update(delFSql)>0){
						System.out.println("删除O_FavoriteFile_Master中的记录:"+qrs.getInt("FavoriteFile_ID")+"成功！");
					}
				}
				// 删除关系表
				String delRSql = "delete O_FavoriteFile_Catelory where CateloryParent_ID="
						+ cateloryParentIDs[i];
				if(db.Update(delRSql)>0){
					System.out.println("删除O_FavoriteFile_Catelory关系表成功！");
				}
			}
		} catch (Exception e) {
			System.out.println("删除失败---sql:");
			db.rollBack(); // 混滚操作
			e.printStackTrace();
		}

	}

	public int addFavoriteFile(FavoriteBeanExtends favoriteBeanExtends) {
		int NextID = 0;
		int rsinsert = -90, crinsert = -98, cinsert = -97;
		String NextIDSQL = "select max(ID) as ID  from  O_FavoriteFile_Master ";
		String sql = "";
		try {
			System.out.println("NextIDSQL，sql：" + NextIDSQL);
			// 获得唯一主键
			ResultSet set = db.executeQuery(NextIDSQL);
			while (set.next()) {
				NextID = set.getInt("ID");
			}
			NextID = NextID + 1;
			// 插入喜欢的网页信息
			sql = "insert into O_FavoriteFile_Master (FavoriteFile_ID,FavoriteFile_Name,FavoriteFile_Url)values ("
					+ NextID
					+ ",'"
					+ favoriteBeanExtends.getFavoriteFile_Name()
					+ "','"
					+ favoriteBeanExtends.FavoriteFile_Url + "')";
			// System.out.println("失败，sql：" + sql);
			rsinsert = db.Update(sql);
			System.out.println("getCateloryType失败，sql：" + favoriteBeanExtends.getCateloryType());
			// 为该网页新增分类
			if (favoriteBeanExtends.getCateloryType() == 2) {// 2表示新增一个1级目录
				System.out.println("come here!");
				String CSql = "insert into O_Catelory_Master(CateloryParent_ID,CateloryChild_ID,Catelory_Name,Catelory_Type)values ("
						+ NextID
						+ ",0,'"
						+ favoriteBeanExtends.getCatelory_Name()
						+ "',"
						+ favoriteBeanExtends.getCateloryType() + ")";
				cinsert = db.Update(CSql);

				// 把网页信息和分类信息关联起来
				String sqlCatelory = "insert into O_FavoriteFile_Catelory ("
						+ "FavoriteFileCatelory_ID," + "FavoriteFile_ID," + "CateloryParent_ID,"
						+ "CateloryChild_ID," + "FavoriteFileCatelory_State," + "User_ID)values("
						+ NextID + "," + NextID + "," + NextID
						+ ",5,1," + favoriteBeanExtends.getUser_Id() + ")";
				crinsert = db.Update(sqlCatelory);
			} else {
				// 把网页信息和分类信息关联起来
				String sqlCatelory = "insert into O_FavoriteFile_Catelory ("
						+ "FavoriteFileCatelory_ID," + "FavoriteFile_ID," + "CateloryParent_ID,"
						+ "CateloryChild_ID," + "FavoriteFileCatelory_State," + "User_ID)values("
						+ NextID + "," + NextID + ","
						+ favoriteBeanExtends.getCateloryParent_ID() + ",5,1,"
						+ favoriteBeanExtends.getUser_Id() + ")";
				crinsert = db.Update(sqlCatelory);
			}

		} catch (Exception e) {
			System.out.println("插入失败，sql：" + sql);
			db.rollBack(); // 混滚操作
		}

		return rsinsert;
	}

	public Object queryExecute(DateBean context) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

}