categorydao.java

实现了从Google

package com.ct.hotweb.dao;

import java.util.*;

import org.apache.commons.dbutils.*;
import org.apache.commons.dbutils.handlers.*;

import com.ct.hotweb.bean.*;
import com.ct.hotweb.db.*;
import com.ct.hotweb.util.*;

public class CategoryDAO extends DAO {

	public final static String sqlGetCategory =
		"select * from category where id = ?";
	public final static String sqlGetSubCategory =
		"select * from category where parentId = ?";

	//删除自身和该目录下面所有子目录,由于有外键on delete触发联系,删除目录会删除相应的关键字
	public final static String sqlDelCategory =
		"delete from category where path like ?";

	public final static String sqlInsertCategory =
		"insert into category (name, parentID, path, createdTime, description) values (?, ?, ?, ?, ?)";

	public final static String sqlSetPath =
		"update category set path = ? where id = ?";

	//获取与某个目录的SearchKey
	public final static String sqlGetSubSearchKey =
		"select sk.id, sk.name, sk.description, "
			+ "sk.parentID, sk.createdTime, sk.lastGenerateTime, sk.generatedFileName "
			+ "from searchKey as sk where sk.parentId = ?";

	//获取某个目录下面所有SearchKey,包括子目录下面的SearchKey,在关键字生成的时候使用
	public final static String sqlGetAllSearchKey =
		"select sk.id, sk.name, sk.description, "
			+ "sk.parentID, sk.createdTime, sk.lastGenerateTime, sk.generatedFileName "
			+ "from searchKey as sk, category cat "
			+ "where cat.id = sk.parentID and cat.path like ?";
	
	//获取任务,存入错误数据表
	public final static String sqlUpdateAllSearchKey =
		"replace searchkeyerrors (select sk.id, " + genErrorType 
			+ " from searchKey as sk, category cat "
			+ " where cat.id = sk.parentID and cat.path like ?)";
			
	//获取某个目录下面所有的Category
	public final static String sqlGetAllCategory =
		"select * from category where path like ?";

	public final static String sqlRefreshCategoryError =
		"replace categoryerrors (select id," + genErrorType + " from category where path like ?)";
		
	//产生网页错误,存入数据库
	public final static String sqlGenErrorCategory =
		"insert into categoryerrors(id, errorType) values(?, ?)";
		
	public final static String sqlGetAllGenErrorCategory =
		"select c.id, c.name, c.Description, c.ParentID, c.Path, c.CreatedTime" +
		" from category c, categoryerrors ce where c.id = ce.id ";
		
	public final static String sqlGetCountGenErrorCategory = 
		"select count(*) from category c, categoryerrors ce where c.id = ce.id";
		
	public final static String sqlClearGenErrors =
		"delete from categoryerrors";

	public final static String sqlUpdateErrors =
		"REPLACE categoryerrors (select id, " + genErrorType + " from category where path like ?)";
		
	public final static String sqlRemoveErrors =
		"delete from categoryerrors where id = ?";
	
	public final static String sqlExistsSameKey =
		"select count(*) from category where parentid = ? and name = ?";

	/**
	 * 根据ID获取一个目录
	 * @param id String
	 * @throws Exception
	 * @return Category
	 */
	public Category getCategory(String id) throws Exception {
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		ResultSetHandler h = new BeanHandler(Category.class);
		return (Category) (run.query(CategoryDAO.sqlGetCategory, id, h));
	}

	/**
	 * 根据ID获取子目录
	 * @param id String
	 * @throws Exception
	 * @return List Category列表
	 */
	public List getSubCategory(String id) throws Exception {
		if (id == null || id.trim().equals(""))
			id = "0";
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		ResultSetHandler h = new BeanListHandler(Category.class);
		return (List) run.query(CategoryDAO.sqlGetSubCategory, id, h);

	}

	/**
	 * 删除一个指定的目录
	 * @param cat Category
	 * @throws Exception
	 */
	public void deleteCategory(Category cat) throws Exception {
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		String likeStr = "" + cat.getPath();
		if (!likeStr.endsWith("/")) {
			likeStr += "/%";
		} else {
			likeStr += "%";
		}
		run.update(sqlDelCategory, likeStr);

	}

	/**
	 * 在某一个目录下面添加子目录, 如果是根目录下面添加,那么 cat = null
	 * @param cat Category
	 * @param childCatName String
	 * @param childCatDesc String
	 * @return Category
	 */
	public Category createSubCategory(
		Category cat,
		String childCatName,
		String childCatDesc)
		throws Exception {
		//创建category实例
		Category childCat = new Category();
		childCat.setName(childCatName);
		childCat.setParentID((cat == null) ? 0 : cat.getId());
		childCat.setPath("");
		childCat.setCreatedTime(
			Utils.dateFormat(new java.util.Date(System.currentTimeMillis())));
		childCat.setDescription(childCatDesc);

		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		ResultSetHandler h = new ArrayHandler();
		//查询是否在同一目录下有相同的category名字
		String[] argExists = {"" + childCat.getParentID(), childCat.getName()};
		Object[] exists = (Object[])run.query(sqlExistsSameKey, argExists, h);
		String count = "" + exists[0];
		if (!"0".equals(count)) return null; 		

		String[] args =
			{
				childCat.getName(),
				"" + childCat.getParentID(),
				childCat.getPath(),
				childCat.getCreatedTime(),
				childCat.getDescription(),
				};
		//插入Category
		run.update(sqlInsertCategory, args);
		//获取ID
		Object[] lastId = (Object[]) (run.query(sqlGetID, null, h));
		String id = "" + lastId[0];
		childCat.setId(Integer.parseInt(id, 10));
		//更改Path
		String parentPath = "";
		if (null != cat)
			parentPath = cat.getPath();
		if (cat != null) parentPath = cat.getPath();
		childCat.setPath(parentPath + childCat.getId() + "/");
		run.update(sqlSetPath, new String[] { childCat.getPath(), id });

		return childCat;
	}

	/**
	 * 根据ID获取改目录下的子关键字
	 * @param id String
	 * @throws Exception
	 * @return List SearchKey列表
	 */
	public List getSubSearchKey(String id) throws Exception {
		if (id == null || id.trim().equals("")) return new ArrayList();
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		ResultSetHandler h = new BeanListHandler(SearchKey.class);
		return (List) run.query(CategoryDAO.sqlGetSubSearchKey, id, h);
	}

	/**
	 * 根据ID获取该目录下所有关键字,包括子目录
	 * @param id String
	 * @throws Exception
	 * @return List SearchKey列表
	 */
	public List getAllInnerSearchKey(Category cat) throws Exception {
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		ResultSetHandler h = new BeanListHandler(SearchKey.class);
		String path = (cat == null) ? "%" : cat.getPath() + "%";
		return (List) run.query(CategoryDAO.sqlGetAllSearchKey, path, h);
	}
	
	/**
	 * 根据ID获取该目录下所有子目录
	 * @author Administrator
	 *
	 * 更改所生成类型注释的模板为
	 * 窗口 > 首选项 > Java > 代码生成 > 代码和注释
	 */
	public List getAllInnerCategory(Category cat) throws Exception {
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		ResultSetHandler h = new BeanListHandler(Category.class);
		String path = (cat == null) ? "%" : cat.getPath() + "%";
		return (List) run.query(CategoryDAO.sqlGetAllCategory, path, h);
	}
	
	public void refreshCategoryError(Category cat) throws Exception {
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		String path = (cat == null) ? "%" : cat.getPath() + "%";
		run.update(CategoryDAO.sqlRefreshCategoryError, path);
	}	

	public void saveGenCategoryError(String id) throws Exception {
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		String[] args = {id, super.genErrorType};
		run.update(CategoryDAO.sqlGenErrorCategory, args);
	}
	
	public List getAllGenErrorCategory() throws Exception {
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		ResultSetHandler h = new BeanListHandler(Category.class);
		return (List) run.query(CategoryDAO.sqlGetAllGenErrorCategory, null, h);
	}
	
	public int getCountGenErrorSearchKey() throws Exception {
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		ResultSetHandler h = new ArrayHandler();
		Object[] o = (Object[])run.query(CategoryDAO.sqlGetCountGenErrorCategory, null, h);
		String count = "" + o[0];
		return Integer.parseInt(count);
	}	
	
	public void clearGenCategoryError() throws Exception {
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		run.update(CategoryDAO.sqlClearGenErrors, null);		
	}
	
	public void refreshSearchKeyError(Category cat) throws Exception {
		String path = (cat == null) ? "%" : cat.getPath() + "%";
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		run.update(CategoryDAO.sqlUpdateAllSearchKey, path);				
	}
	
	public void removeError(String id) throws Exception {
		QueryRunner run = new QueryRunner(DataSourceManager.getDataSource());
		run.update(CategoryDAO.sqlRemoveErrors, id);		
	}
	
}