idaoimpl.java

上网计费系统,适用网吧或者自己学习用用JAVA写成

package com.briup.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.Session;

import com.briup.dao.IDao;
import com.briup.dao.bean.Admin;
import com.briup.dao.bean.Hv;
import com.briup.dao.bean.Product;
import com.briup.dao.bean.Radcheck;
import com.briup.dao.bean.Radreply;
import com.briup.dao.bean.Role;
import com.briup.dao.bean.User;
import com.briup.util.HibernateSessionFactory;
import com.briup.web.form.SearchAdminForm;

public class IDaoImpl implements IDao {

	public static final String GET_ADMIN_BY_LOGIN_NAME = "from Admin where loginName=?";

	public static final String GET_ALL_ADMINS = "from Admin ";

	public static final String GET_USER_BY_LOGIN_NAME = "from User where loginName=?";

	public static final String GET_USER_BY_ID = "from User where id=?";

	public static final String GET_USER_COUNT = "select count(*) from User";

	public static final String GET_USER_BY_NUM = "from User";

	public static final String GET_HIGH_VALUE = "from Hv";

	public static final String GET_ALL_ROLES = "from Role";

	public static final String GET_PRODUCT_BY_ID = "from Product where id=?";

	public static final String GET_ALL_PRODUCTS = "from Product"; 

	public static final String GET_ALL_PRODUCTTYPES = "from ProductType";

	private static final String GET_PRODUCT_COUNT = "select count(*) from Product";

	private static final String GET_PRODUCT_BY_NUM = "from Product";

	private static final String GET_RADACCT_BY_CONDITION = "from Radacct radacct where radacct.username=? and radacct.acctstoptime between ? and ?";

	private static final String GET_RADACCT_COUNT = "select count(*) from Radacct radacct where radacct.username=? and radacct.acctstoptime between ? and ?";

	private static final String GET_RADCHECK_BY_NAME = "from Radcheck where username=?";

	private static final String GET_RADREPLY_BY_USERNAME = "from Radreply where username=?";

	private static final String GET_BUSINESS_MONTH = "from Businessmonth where to_number(to_char(ondate,'MM'))=?";

	private static final String GET_BUSINESS_YEAR = "from Businessyear where to_number(to_char(ondate,'YYYY'))=?";

	private static final String GET_NAS_DAY = "from Nasday where to_char(ondate,'YYYYMMDD')=?";

	private static final String GET_NAS_MONTH = "from Nasmonth where to_char(ondate,'YYYYMM')=? order by ondate asc";

	private static final String GET_NAS_YEAR = "from Nasyear where to_char(ondate,'YYYY')=?";

	private static final String GET_USER_DAY = "from Userday where username=? and to_char(ondate,'YYYYMMDD')=?";

	private static final String GET_USER_MONTH = "from Usermonth where username=? and to_char(ondate,'YYYYMM')=?";

	private static final String GET_USER_YEAR = "from Useryear where username=? and to_char(ondate,'YYYY')=?";

	public IDaoImpl() {

	}
	public Admin getAdminByLoginName(String loginName) throws Exception {
		Admin admin = null;
		Session session = HibernateSessionFactory.getSession();
		Query query = session.createQuery(GET_ADMIN_BY_LOGIN_NAME);
		query.setString(0, loginName.trim());
		List list = query.list();
		Iterator it = list.iterator();
		if (it.hasNext())
			admin = (Admin) it.next();
		return admin;
	}

	public List getAllAdmins() throws Exception {
		Session session = HibernateSessionFactory.getSession();
		Query query = session.createQuery(GET_ALL_ADMINS);
		List admins = query.list();
		return admins;
	}

	public void saveOrUpdateAdmin(Admin admin) throws Exception {
		Session session = HibernateSessionFactory.getSession();
		session.saveOrUpdate(admin);
	}

	public void deleteAdmin(Long id) throws Exception {
		Session session = HibernateSessionFactory.getSession();
		Admin admin = (Admin) session.get(Admin.class, id);
		session.delete(admin);
	}

	public List getAdminByNum(SearchAdminForm searchAdminForm, int offset,
			int maxNumPerPage) throws Exception {
		String GET_ADMIN_BY_NUM = "from Admin admin where 1=1";
		String GET_ADMIN_COUNT = "select count(*) from Admin admin where 1=1";
		String tempsql = " ";
		Session session = HibernateSessionFactory.getSession();
		Query query = null;
		Date sdate = null;
		Date edate = null;
		if (searchAdminForm != null) {
			String realName = searchAdminForm.getRealName();
			String loginName = searchAdminForm.getLoginName();
			String address = searchAdminForm.getAddress();
			String startDate = searchAdminForm.getStartDate();
			String endDate = searchAdminForm.getEndDate();
			String orderBy = searchAdminForm.getOrderBy();
			String desc = searchAdminForm.getDesc();
			Long role = searchAdminForm.getRole();
			System.out.println("&&&&&&&&:" + realName + ":" + loginName + ":"
					+ address + ":" + startDate + ":" + endDate + ":" + orderBy
					+ ":" + desc + ":" + role);
			if (realName != null && realName.trim().length() > 0) {
				tempsql += " and admin.realName='" + realName + "'";
			}
			if (loginName != null && loginName.trim().length() > 0) {
				tempsql += " and admin.loginName='" + loginName + "'";
			}
			if (address != null && address.trim().length() > 0) {
				tempsql += " and admin.address='" + address + "'";
			}
			if (role != null) {
				tempsql += " and admin.role.id=" + role;
			}
			if (startDate != null && startDate.trim().length() > 0
					&& endDate != null && endDate.length() > 0) {
				SimpleDateFormat formatter = new SimpleDateFormat(
						"yyyy-MM-dd HH:mm:ss");
				sdate = formatter.parse(startDate);
				edate = formatter.parse(endDate);
				tempsql += " and admin.registerDate between ? and ?";
			}
			if (orderBy != null && orderBy.trim().length() > 0 && desc != null
					&& desc.trim().length() > 0) {
				tempsql += " order by " + orderBy + " " + desc;
			}
		}
		System.out.println("sql:" + GET_ADMIN_BY_NUM + tempsql);
		query = session.createQuery(GET_ADMIN_BY_NUM + tempsql);
		if (sdate != null && edate != null) {
			query.setDate(0, sdate);
			query.setDate(1, edate);
		}
		query.setFirstResult(offset);
		query.setMaxResults(maxNumPerPage);
		List admins = query.list();
		query = session.createQuery(GET_ADMIN_COUNT + tempsql);
		if (sdate != null && edate != null) {
			query.setDate(0, sdate);
			query.setDate(1, edate);
		}
		List lcount = query.list();
		Integer count = new Integer(0);
		if (lcount.size() > 0) {
			count = (Integer) lcount.get(0);
		}
		List lreturn = new ArrayList();
		lreturn.add(count);
		lreturn.add(admins);

		return lreturn;
	}

	public User getUserByLoginName(String loginName) {
		User buser = null;
		Session session = HibernateSessionFactory.getSession();
		Query query = session.createQuery(GET_USER_BY_LOGIN_NAME);
		query.setString(0, loginName);
		List list = query.list();
		Iterator it = list.iterator();
		if (it.hasNext())
			buser = (User) it.next();
		return buser;
	}

	public User getUsersById(Long userId) throws Exception {
		Session session = HibernateSessionFactory.getSession();
		Query query = session.createQuery(GET_USER_BY_ID);
		User user = null;
		query.setLong(0, userId);
		Iterator it = query.iterate();
		while (it.hasNext()) {
			user = (User) it.next();
		}

		return user;
	}

	public List getUserByNum(int offset, int maxNumPerPage) throws Exception {
		Session session = HibernateSessionFactory.getSession();
		Query query = session.createQuery(GET_USER_BY_NUM);
		query.setFirstResult(offset);
		query.setMaxResults(maxNumPerPage);
		List users = query.list();
		return users;
	}

	public Integer getUserCount() throws Exception {
		Session session = HibernateSessionFactory.getSession();
		Query query = session.createQuery(GET_USER_COUNT);
		Integer count = null;
		List list = query.list();
		if (list.size() > 0) {
			count = (Integer) list.get(0);
		}
		return count;
	}

	public void saveOrUpdateUser(User user) throws Exception {
		Session session = HibernateSessionFactory.getSession();
		session.saveOrUpdate(user);
	}

	public void deleteUser(Long id) throws Exception {
		Session session = HibernateSessionFactory.getSession();
		User user = (User) session.get(User.class, id);
		Set radchecks = user.getRadchecks();
		Iterator iter = radchecks.iterator();
		while (iter.hasNext()) {
			Radcheck radcheck = (Radcheck) iter.next();
			Query query = session.createQuery(GET_RADREPLY_BY_USERNAME);
			query.setString(0, radcheck.getUsername());
			List tempReplies = query.list();

			if (tempReplies != null && tempReplies.size() > 0) {
				Iterator iter2 = tempReplies.iterator();
				while (iter2.hasNext()) {
					Radreply radreply = (Radreply) iter2.next();
					session.delete(radreply);
				}
			}
		}
		session.delete(user);
	}

	public Integer getRadacctCount(String businessName, Date startDate,
			Date endDate) throws Exception {
		Session session = HibernateSessionFactory.getSession();
		Query query = session.createQuery(GET_RADACCT_COUNT);
		query.setString(0, businessName);
		query.setDate(1, startDate);
		query.setDate(2, endDate);
		List list = query.list();
		Integer count = 0;
		if (list.size() > 0) {
			count = (Integer) list.get(0);
		}
		return count;
	}

	public void saveOrUpdateRadcheckAndReply(Radcheck radcheck,Collection radreplies) throws Exception {
		Session session = HibernateSessionFactory.getSession();
		try {
			session.saveOrUpdate(radcheck);
			Query query = session.createQuery(GET_RADREPLY_BY_USERNAME);
			query.setString(0, radcheck.getUsername());
			List tempReplies = query.list();

			if (tempReplies != null && tempReplies.size() > 0) {
				Iterator iter = tempReplies.iterator();
				while (iter.hasNext()) {
					Radreply radreply = (Radreply) iter.next();
					session.delete(radreply);
				}
			}

			Iterator it = radreplies.iterator();
			while (it.hasNext()) {
				session.save(it.next());
				session.flush();
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception("保存失败");