moduledaoimpl.java

这是一个用java三层框架做的ISS学员管理系统

package com.isoftstone.isscrmweb.web.dao.frame;

import com.isoftstone.isscrmweb.web.mapping.Menuinfo;
import com.isoftstone.isscrmweb.web.mapping.Roleinfo;

import org.pontifex.orm.hibernate3.DaoImpl;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.hibernate.*;
import org.hibernate.criterion.Expression;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Order;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

/**
 * Created by IntelliJ IDEA. User: Songzou Date: 2007-4-10 Time: 20:03:49
 */
public class ModuleDaoImpl extends DaoImpl implements ModuleDao {

	public Menuinfo findMenuByID(final String menuid) {
		return (Menuinfo) this.getHibernateTemplate().get(Menuinfo.class, menuid);
	}

	public void saveMenuinfo(Menuinfo menu) {
		this.getHibernateTemplate().save(menu);
	}

	public int findMenuByFatheridAndUrl(final String fatherid,
			final String menuname) {
		return (Integer) this.getHibernateTemplate().execute(
				new HibernateCallback() {

					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Criteria criteria = session
								.createCriteria(Menuinfo.class);
						criteria.add(Expression.eq("this.menuinfo.menuid",
								fatherid));
						if (menuname != null) {
							criteria.add(Expression.eq("menuname", menuname));
						}
						criteria.setProjection(Projections.rowCount());

						return criteria.uniqueResult();
					}
				});
	}

	/**
	 * 
	 */
	public List findMenuListByFatherID(final String fatherid) {
		return (List) this.getHibernateTemplate().execute(new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Connection conn= session.connection();
				List<Menuinfo> list = new ArrayList<Menuinfo>();
				Menuinfo menu = null;
				String sql = "";
				if (fatherid != null && !fatherid.equals("0")) {
					sql = "select m.*,m1.menuname as fathername,(case when m.menutype='1' then (case when exists (select 1 from menuinfo a where a.fatherid=m.menuid) then '1' else '0' end)"+
                    " else '0' end) as havesubmenu,"+
                    " (case when exists (select 1 from roleandmenu r where r.menuid=m.menuid) then '1' else '0' end) as menuused"+
                    " from menuinfo m inner join menuinfo m1 on m.fatherid = m1.menuid  where m.fatherid=? order by m.menuorder";
				}else{
					sql =  "select m.* ,(case when m.menutype='1' then (case when exists (select 1 from menuinfo a where a.fatherid=m.menuid) then '1' else '0' end)"+
                    " else '0' end) as havesubmenu,"+
                    " (case when exists (select 1 from roleandmenu r where r.menuid=m.menuid) then '1' else '0' end) as menuused"+
                    " from menuinfo m  where m.fatherid=? order by m.menuorder ";
				}
				PreparedStatement ptm = conn.prepareStatement(sql);
				ptm.setString(1, fatherid);
				ResultSet rs = ptm.executeQuery();
				while(rs.next()){
					menu = new Menuinfo();
					menu.setMenuid(rs.getString("menuid"));
					menu.setHavesubmenu(rs.getString("havesubmenu"));
					menu.setMenudescribe(rs.getString("Menudescribe"));
					menu.setMenuname(rs.getString("menuname"));
					menu.setMenuorder(rs.getLong("menuorder"));
					menu.setMenutype(rs.getString("menutype"));
					menu.setMenuused(rs.getString("menuused"));
					menu.setMenulevel(rs.getString("menulevel"));
					Menuinfo menuinfo = new Menuinfo();
					menuinfo.setMenuid(rs.getString("fatherid"));
					if(fatherid != null && !fatherid.equals("0")){						
						menuinfo.setMenuname(rs.getString("fathername"));						
					}
					menu.setMenuinfo(menuinfo);
					list.add(menu);
				}
				rs.close();
				ptm.close();
				return list;
			}
		});
	}

	public List findAllMenuNoLevel() {
		return (List) this.getHibernateTemplate().execute(
				new HibernateCallback() {

					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Criteria criteria = session
								.createCriteria(Menuinfo.class);
						criteria.add(Expression.not(Expression.eq(
								"this.menulevel", "3")));
						criteria.add(Expression.eq("menutype", "1"));
						criteria.addOrder(Order.asc("menuorder"));
						return criteria.list();
					}
				});
	}

	public List findAllMenu() {
		return (List) this.getHibernateTemplate().execute(
				new HibernateCallback() {

					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Criteria criteria = session
								.createCriteria(Menuinfo.class);
						criteria.addOrder(Order.asc("menulevel")).addOrder(
								Order.asc("menuorder"));
						return criteria.list();
					}
				});
	}

	public void deleteMenuinfo(Menuinfo menu) {
		this.getHibernateTemplate().delete(menu);
	}

	public int findMenuAndRoleByMenuid(final String menuid) {
		return (Integer) this.getHibernateTemplate().execute(
				new HibernateCallback() {

					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Criteria criteria = session
								.createCriteria(Menuinfo.class).createAlias("roleandmenus", "r");
						criteria.add(Expression.eq("menuid",
								menuid));
						criteria.setProjection(Projections.rowCount());
						return criteria.uniqueResult();
					}
				});
	}

	public void updateMenuinfoOrder(final Menuinfo menuinfo) {
		this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query =session.createQuery("update Menuinfo set menuorder=:menuorder where menuid=:menuid");
				query.setLong("menuorder", menuinfo.getMenuorder());
				query.setString("menuid", menuinfo.getMenuid());
				return query.executeUpdate();
			}
			
		});
	}

	

	public Menuinfo findFatherMenuinfoByID(final Menuinfo menu) {
		return (Menuinfo)this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				return session.createQuery("from Menuinfo as m inner join fetch m.menuinfo as m1 where m.menuid=:menuid").
				setString("menuid", menu.getMenuid()).uniqueResult();
			}
			
		});
	}

	public List findMenuByRoleID(final Roleinfo role) {
		return this.getHibernateTemplate().executeFind(new HibernateCallback(){

			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Query query = session.createQuery("select m.menuid from Menuinfo as m inner join m.roleandmenus as rm where rm.roleinfo.roleid=:roleid ");
				return query.setString("roleid", role.getRoleid()).list();
			}
			
		});
		
	}

	public void updateMenuinfo(final Menuinfo menu) {
		this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Menuinfo menu1 = (Menuinfo) session.load(Menuinfo.class, menu.getMenuid());
				if(menu.getMenuname()!=null){
					menu1.setMenuname(menu.getMenuname());
				}
				if(menu.getMenuurl()!=null){
					menu1.setMenuurl(menu.getMenuurl());
				}
				if(menu.getMenudescribe()!=null){
					menu1.setMenudescribe(menu.getMenudescribe());
				}
				if(menu.getMenutype()!=null){
					menu1.setMenutype(menu.getMenutype());
				}
				return null;
			}
			
		});	
	}

	public int findMenuByFatheridNotUrl(final Menuinfo menu) {
		return (Integer)this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				Criteria criteria = session.createCriteria(Menuinfo.class);
				criteria.add(Expression.eq("menuinfo.menuid", menu.getMenuinfo().getMenuid()));
				criteria.add(Expression.eq("menuname", menu.getMenuname()));
				criteria.add(Expression.not(Expression.eq("menuid", menu.getMenuid())));
				return criteria.setProjection(Projections.rowCount()).uniqueResult();
			}
			
		});
	}

	public List findMenuByRoleIDs(final Set roleidset) {
		return this.getHibernateTemplate().executeFind(new HibernateCallback(){

			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				StringBuffer string = new StringBuffer();
				string.append("select distinct new Menuinfo(m.menuid,m.menuname,m.menutype,mm.menuid as fatherid , m.menulevel,m.menuorder,m.menuurl) from Menuinfo as m left outer join m.menuinfo as mm inner join m.roleandmenus as ram inner join ram.roleinfo as r where r.roleid=:roleid ");				
				for(int i = 1 ; i< roleidset.size() ; i++){
					string.append(" or r.roleid=:roleid ");
				}
				string.append(" order by m.menulevel , m.menuorder");
				Query query =session.createQuery(string.toString());
				
				query.setParameterList("roleid", roleidset);
				return query.list();
			}
			
		});
	}
}