serproblemdao.java.svn-base

本例使用Java + Jsp 实现选择树的过程,主要使用Struts+JDBC/hibernate架构

/*
 * $Workfile: SerProblemDao.java$ 
 * Copyright (c) 2007 Jama, International.
 * All rights reserved.
 * Creator Jun
 * Last Changed by: $Author: Jun$ 
 * On: $Date: 2007-8-30 下午09:52:37$ 
 * $Revision: 1$ 
 */
package com.jama.rcrm.service.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;

import com.jama.common.base.BaseDAO;
import com.jama.common.taglib.QueryInfo;
import com.jama.common.util.Tools;
import com.jama.rcrm.service.business.dto.SerProblemDto;
import com.jama.rcrm.service.dao.pojo.TSerProblemDefine;

public class SerProblemDao extends BaseDAO
{
	
	/**覆盖BaseDAO的buildSQLQuery，
	 * 使返回的列表具备所有子节点都在下一个其他类型父节点之间这种关系
	 */
	protected SQLQuery buildSQLQuery(String sql, QueryInfo queryInfo)
	{
		SQLQuery sqlQuery = getSession().createSQLQuery(sql);
		if (queryInfo.getEndRecord() != 0)
		{
			sqlQuery.setFirstResult(queryInfo.getStartRecord());
			sqlQuery.setMaxResults(queryInfo.getPageSize());
		}
		return sqlQuery;
	}
	
	/**
	 * 传入SerProblemDto将SerProblemDto的属性对应赋值给TSerProblemDefine的
	 * 对象的属性保存到数据库中
	 * */
	public void addProblemType(SerProblemDto serProblemDto) throws Exception
	{
		TSerProblemDefine po = new TSerProblemDefine();
		
		BeanUtils.copyProperties(po, serProblemDto);
		
		po.setProblemGuid(Tools.getUUID());
		po.setInactive(0L);		
		getSession().save(po);
	}

	/**
	 * 传入String 类型的ID数组将数据库中对应记录的inactive字段值设为1，
	 * 表示不可用
	 * */
	public void deleteProblemType(String... ids) throws Exception
	{		
		String sql = "update T_SER_PROBLEM_DEFINE set inactive=1 where  problem_Guid in(:ids)";		
		Query q = getSession().createSQLQuery(sql);	
		q.setParameterList("ids", ids);
		q.executeUpdate();
	}
    
	/**
	 * 传入QueryInfo类型的对象将数据按需求查询出来封装到List内返回
	 * 
	 */
	public List getProblem(QueryInfo queryInfo) throws Exception
	{
		if (queryInfo.getRecordCount() == -1)
		{
			queryInfo.setRecordCount(getTypeTotal());
		}
		
		String selhql = "select  problem_Guid,problem_Name,description,inactive,parent_Problem_Guid,level_No  from t_ser_problem_define start with (problem_guid = 'root') connect by prior problem_guid = parent_problem_guid and t_ser_problem_define.inactive=0";
 
		List results = buildSQLQuery(selhql,queryInfo).list();
		
		ArrayList<SerProblemDto> dtoList = new ArrayList<SerProblemDto>();
		
		Object[]  obj = null;
		
		//循环将List迭代转换成数组对象，再将此对象字段的值赋值给SerProblemDto对相的属性
		//将SerProblemDto对象添加到List中返回
		for (int i = 0; i < results.size(); i ++)
		{
			obj = (Object[]) results.get(i);
			SerProblemDto dto=new SerProblemDto();	
			dto.setProblemGuid(String.valueOf(obj[0]!=null?obj[0]:""));		
			dto.setDescription(String.valueOf(obj[2]!=null?obj[2]:""));
			int inactive_i = Integer.valueOf(String.valueOf(obj[3]!=null?obj[3]:0));
			long inactive_l =inactive_i; 
			dto.setInactive(inactive_l);
			
			//未知原因无法直接将String转换成Long型
			//dto.setInactive(Long.valuef((String) (obj[3]!=null?obj[3]:"")));				
			dto.setParentProblemGuid(String.valueOf(obj[4]!=null?obj[4]:""));
			
			int levelNo_i = Integer.valueOf(String.valueOf(obj[5]!=null?obj[5]:0));
			long levelNo_l =levelNo_i; 
			dto.setLevelNo(levelNo_l);
            
			//在列表字段前加‘-- ’，用以区别问题类型的节点级数
			//每个‘-- ’的出现表示一级，累计标识的和就是该字段所处的问题类型级数
			String space="";
			for(int levlNo=0;levlNo<dto.getLevelNo();levlNo++)
      {
				space+="--";
      }
      dto.setProblemName(space+String.valueOf(obj[1]!=null?obj[1]:""));
            
			if(!"".equals(dto.getParentProblemGuid()))
			{   		
	      //循环将有父Id对应的problemName返回装入对应的SerProblemDto对象
				for(int n=0;n<results.size();n++)
				{
					String Guid = dto.getParentProblemGuid();
					String pGuid = null;
					Object[] obj1 = (Object[])results.get(n);
					pGuid = String.valueOf(obj1[0]!=null?obj1[0]:"");
					if(Guid.equals(pGuid)||Guid==pGuid)
					{
						dto.setParentName(String.valueOf(obj1[1]!=null?obj1[1]:""));
						break;
					}		
				}	
			}else
			{
			    dto.setParentName("");
			}	
      if(!"root".equals(dto.getProblemGuid()))
      {	
			    dtoList.add(dto);		 
      }
		}
		return dtoList;
	}
	
	/**
	 *查询数据库中T_SER_PROBLEM_DEFINE表内有多少记录
	 *返回整数（记录数）
	 */
	public int getTypeTotal() throws Exception
	{
		int total = 0;
		String selhql="select count(problem_Guid) from T_SER_PROBLEM_DEFINE";
		Object obj = (Object) getSession().createSQLQuery(selhql).uniqueResult();
		if (obj != null)
		{
			total = Integer.parseInt(obj.toString());
		}
		return total-1;
	}
	
	/**
	 *传入ID查找表中对应的记录封装成SerProblemDto返回
	 */
	public SerProblemDto getProblemTypeById(String id) throws Exception
	{	
		SerProblemDto dto = new SerProblemDto();		
		TSerProblemDefine po = (TSerProblemDefine)getSession()
		                             .get(TSerProblemDefine.class, id);
		BeanUtils.copyProperties(dto, po);
		return dto;		
	}

	/**
	 *传入SerProblemDto，将数据库表中对应的记录内容修改为该DTO属性的内容
	 */
	public void modifyProblemType(SerProblemDto serProblemDto) throws Exception
	{
		TSerProblemDefine po = new TSerProblemDefine();
		BeanUtils.copyProperties(po, serProblemDto);
		getSession().update(po);
	}
	
	/**
	 *返回具有父子关系的内容为SerProblemDto的列表
	 */
	public List getProblemList()
	{
		
		Query treeList = null;
		Query checkList = getSession().createQuery("from TSerProblemDefine");
		if(checkList.list().size()<=0)
		{
		   String insert = "insert into T_Ser_Problem_Define(problem_guid,level_no) values('root',0)"; 
	       Query insertSql = getSession().createSQLQuery(insert);
	       insertSql.executeUpdate();    
		}else
		{
		   String returnTreeSql = "select  problem_Guid,problem_Name,description," +
				         "inactive,parent_Problem_Guid,level_No  from t_ser_problem_define start with (problem_guid = 'root') connect by prior problem_guid = parent_problem_guid and t_ser_problem_define.inactive=0";
		   treeList = getSession().createSQLQuery(returnTreeSql);
		}	
	    return getDtoList(treeList.list());
	}
	
	/**
	 * 将Object<TSerProblemDefine>列表转换成Dto（SerProblemDto）列表返回
	 * */
	public List getDtoList(List objList)
	{
		
		List<SerProblemDto> dtoList = new ArrayList<SerProblemDto>(); 
		
	    Object[] obj = null;
		
		for (int i = 0; i < objList.size(); i++)
		{
			obj = (Object[]) objList.get(i);
			SerProblemDto dto=new SerProblemDto();
			
			dto.setProblemGuid(String.valueOf(obj[0]!=null?obj[0]:""));		
			dto.setProblemName(String.valueOf(obj[1]!=null?obj[1]:""));
			dto.setDescription(String.valueOf(obj[2]!=null?obj[2]:""));
			int inactive_i = Integer.valueOf(String.valueOf(obj[3]!=null?obj[3]:0));
			long inactive_l =inactive_i; 
			dto.setInactive(inactive_l);

			dto.setParentProblemGuid(String.valueOf(obj[4]!=null?obj[4]:""));
			
			int levelNo_i = Integer.valueOf(String.valueOf(obj[5]!=null?obj[5]:0));
			long levelNo_l =levelNo_i; 
			dto.setLevelNo(levelNo_l);

      //未知原因无法直接将String转换成Long型
	    //dto.setLevelNo(Long.valueOf((String) (obj[5]!=null?obj[5]:"")));
      if(!"root".equals(dto.getProblemGuid()))
      {	
			   dtoList.add(dto);		 
      }
		}		
		return dtoList;
	}
}