persondaoimpl.java

能够实现学员的缴费查询

package cn.jiaofei.dao.impl;
import java.sql.* ;
import cn.jiaofei.vo.* ;

import java.util.* ;

import cn.jiaofei.dbc.* ;
import cn.jiaofei.dao.* ;
public class PersonDAOImpl implements PersonDAO {
	//用户登陆判断：
	public boolean login(PersonvoUser personuser) throws Exception
	{
		boolean flag=false;
		String sql="select name,type,tid,workno from user_table where username=? and password=?";
		PreparedStatement pstm = null;
		DateBaseConnection dbc = null;
		dbc = new DateBaseConnection();
		try
		{  
			pstm = dbc.getConnection().prepareStatement(sql);
			pstm.setString(1, personuser.getUsername());
			pstm.setString(2,personuser.getPassword());
			ResultSet rs = pstm.executeQuery();
			if(rs.next())
			{
				flag = true;
				personuser.setName(rs.getString(1));
				personuser.setType(rs.getString(2));
				personuser.setTid(rs.getInt(3));
				personuser.setWorkno(rs.getString(4));
			}
			rs.close() ;
			pstm.close() ;
		}
		catch (Exception e)
		{
			//throw new Exception("操作出现错误！！！") ;
			e.printStackTrace();
		}
		finally
		{
			dbc.close() ;
		}
		
		return flag ;
	}
	//插入用户数据（管理员AND老师）：
	public void insert(PersonvoUser personuser) throws Exception
	{
		String sql = "insert into user_table(tid,username,password,name,type,workno) values((select max(tid) from user_table)+1,?,?,?,?,?)" ;
		PreparedStatement pstmt = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		    try
			{ 
				pstmt = dbc.getConnection().prepareStatement(sql) ;
				pstmt.setString(1,personuser.getWorkno()) ;
				pstmt.setString(2,personuser.getWorkno()) ;
				pstmt.setString(3,personuser.getName()) ;
				pstmt.setString(4,personuser.getType()) ;
				pstmt.setString(5,personuser.getWorkno()) ;
				pstmt.executeUpdate() ;
				pstmt.close() ;
			}
			catch (Exception e)
			{
				//throw new Exception("操作中出现错误！！！") ;
				e.printStackTrace();
			}
      
		finally
		{
			dbc.close() ;
		}
	}
	//添加新班级：
	public void insertclass(PersonvoClass personclass) throws Exception
	{
		
		String sql = "insert into class_table values((select max(tid) from class_table)+1,?,?)" ;
		PreparedStatement pstmt = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		    try
			{ 
				pstmt = dbc.getConnection().prepareStatement(sql) ;
				pstmt.setString(1,personclass.getClassname()) ;
				pstmt.setString(2,personclass.getClassno()) ;
				pstmt.executeUpdate() ;
				pstmt.close() ;
			}
			catch (Exception e)
			{
				//throw new Exception("操作中出现错误！！！") ;
				e.printStackTrace();
			}
      
		finally
		{
			dbc.close() ;
		}
	}
	//添加新课程
	public void insertlesson(PersonvoLesson personlesson) throws Exception
	{
		String sql = "insert into lesson_table values((select max(tid) from lesson_table)+1,?,?,?)" ;
		PreparedStatement pstmt = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		    try
			{ 
				pstmt = dbc.getConnection().prepareStatement(sql) ;
				pstmt.setString(1,personlesson.getLessonno()) ;
				pstmt.setString(2,personlesson.getLessonname()) ;
				pstmt.setString(3,personlesson.getPrice()) ;
				pstmt.executeUpdate() ;
				pstmt.close() ;
			}
			catch (Exception e)
			{
				//throw new Exception("操作中出现错误！！！") ;
				e.printStackTrace();
			}
      
		finally
		{
			dbc.close() ;
		}
	}
//执行缴费时，给用户提示所缴费用的信息	：
	public boolean selectaddcost(PersonvoUser personuser) throws Exception
	{
		boolean flag=false;
		String sql = "select l.lessonname,c.classname ,u.name from lesson_table l,class_table c,user_table u where l.lessonno=(select lessonno from user_lesson where workno=?) and c.classno=(select classno from user_class where workno=?) and u.workno=?" ;
		String sql2 = "select sum(price)as prc from lesson_table where lessonno=(select lessonno from user_lesson where workno=?)";
		PreparedStatement pstm = null ;
		PreparedStatement pstm2 = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		    try
			{ 
		    	
		    	pstm = dbc.getConnection().prepareStatement(sql);
				pstm.setString(1,personuser.getWorkno());
				pstm.setString(2,personuser.getWorkno());
				pstm.setString(3,personuser.getWorkno());
				ResultSet rs = pstm.executeQuery();
				if(rs.next())
				{					
					personuser.setLessonname(rs.getString(1));
					personuser.setClassname(rs.getString(2));
					personuser.setName(rs.getString(3));
				}
				pstm2 = dbc.getConnection().prepareStatement(sql2);
				pstm2.setString(1,personuser.getWorkno());
				ResultSet rs2 = pstm2.executeQuery();
				if(rs2.next())
				{
					flag=true;
					personuser.setPrice(rs2.getString("prc"));
				}
				rs.close() ;
				rs2.close() ;
				pstm.close() ;
				pstm2.close() ;
			}
			catch (Exception e)
			{
				//throw new Exception("操作中出现错误！！！") ;
				e.printStackTrace();
			}
      
			finally
			{
				dbc.close() ;
			}
			return flag ;
	}
//用户缴费
	public void insertcost(PersonvoUser personuser) throws Exception
	{
		String sql = "insert into cost_table values((select max(tid) from cost_table)+1,?,to_date(?,'yyyy-mm-dd'),(select max(costno) from cost_table)+1)" ;
		String sql2 = "insert into user_cost values((select max(tid) from user_cost)+1,(select max(costno) from cost_table),?)";
		PreparedStatement pstmt = null ;
		PreparedStatement pstmt2 = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		    try
			{ 
				pstmt = dbc.getConnection().prepareStatement(sql) ;
				pstmt.setInt(1,personuser.getCost()) ;
				pstmt.setString(2,personuser.getTime()) ;
				pstmt.executeUpdate() ;
				pstmt.close() ;
				pstmt2 = dbc.getConnection().prepareStatement(sql2) ;
				pstmt2.setString(1,personuser.getWorkno()) ;
				pstmt2.executeUpdate() ;
				pstmt2.close() ;
			}
			catch (Exception e)
			{
				//throw new Exception("操作中出现错误！！！") ;
				e.printStackTrace();
			}
      
		finally
		{
			dbc.close() ;
		}
	}
//用户退费：
	public void insertrecost(PersonvoUser personuser) throws Exception
	{
		String sql = "insert into recost_table values((select max(tid) from recost_table)+1,?,to_date(?,'yyyy-mm-dd'),(select max(recostno) from recost_table)+1)" ;
		String sql2 = "insert into user_cost values((select max(tid) from user_recost)+1,(select max(recostno) from recost_table),?)";
		PreparedStatement pstmt = null ;
		PreparedStatement pstmt2 = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		    try
			{ 
				pstmt = dbc.getConnection().prepareStatement(sql) ;
				pstmt.setInt(1,personuser.getRecost()) ;
				pstmt.setString(2,personuser.getTime()) ;
				pstmt.executeUpdate() ;
				pstmt.close() ;
				pstmt2 = dbc.getConnection().prepareStatement(sql2) ;
				pstmt2.setString(1,personuser.getWorkno()) ;
				pstmt2.executeUpdate() ;
				pstmt2.close() ;
			}
			catch (Exception e)
			{
				//throw new Exception("操作中出现错误！！！") ;
				e.printStackTrace();
			}
      
		finally
		{
			dbc.close() ;
		}
	}
	//插入学生数据
	public void insertstuclass(PersonvoUser personuser) throws Exception
	{
		String sql = "insert into user_table(tid,username,password,name,type,workno) values((select max(tid) from user_table)+1,?,?,?,?,?)" ;
		String sql2 = "insert into user_class(tid,workno,classno) values((select max(tid) from user_class)+1,?,?) ";
		PreparedStatement pstmt = null ;
		PreparedStatement pstmt2 = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		    try
			{ 
				pstmt = dbc.getConnection().prepareStatement(sql) ;
				pstmt.setString(1,personuser.getWorkno()) ;
				pstmt.setString(2,personuser.getWorkno()) ;
				pstmt.setString(3,personuser.getName()) ;
				pstmt.setString(4,personuser.getType()) ;
				pstmt.setString(5,personuser.getWorkno()) ;
				pstmt.executeUpdate() ;
				pstmt.close() ;
				pstmt2 = dbc.getConnection().prepareStatement(sql2) ;
				pstmt2.setString(1,personuser.getWorkno()) ;
				pstmt2.setString(2,personuser.getClassno()) ;
				pstmt2.executeUpdate() ;
				pstmt2.close() ;
			}
			catch (Exception e)
			{
				//throw new Exception("操作中出现错误！！！") ;
				e.printStackTrace();
			}
      
		finally
		{
			dbc.close() ;
		}
	}
	//判断是否有输入的班级存在
	public boolean userstu(PersonvoUser personuser) throws Exception
	{
		boolean flag=false;
		String sql="select * from class_table where classno=?";
		PreparedStatement pstm = null;
		DateBaseConnection dbc = null;
		dbc = new DateBaseConnection();
		try
		{  
			pstm = dbc.getConnection().prepareStatement(sql);
			pstm.setString(1, personuser.getClassno());
			ResultSet rs = pstm.executeQuery();
			if(rs.next())
			{
				flag = true;
			}
			rs.close() ;
			pstm.close() ;
		}
		catch (Exception e)
		{
			//throw new Exception("操作出现错误！！！") ;
			e.printStackTrace();
		}
		finally
		{
			dbc.close() ;
		}
		
		return flag ;
	}
	//查询学生用户信息
	public List queryAll() throws Exception 
	{
		List all = new ArrayList() ;
		String sql = "select u.name,uc.workno,c.classname,u.tid from user_table u,class_table c,user_class uc where u.workno = uc.workno and c.classno=uc.classno";
		PreparedStatement pstm = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		try
		{  			   
		    pstm = dbc.getConnection().prepareStatement(sql);
			ResultSet rs = pstm.executeQuery();
			while(rs.next())
			{
			PersonvoUser personvouser =new PersonvoUser();				
				personvouser.setName(rs.getString(1));
				personvouser.setWorkno(rs.getString(2));
				personvouser.setClassname(rs.getString(3));
				personvouser.setTid(rs.getInt(4));
				all.add(personvouser) ;
			}
			rs.close() ;
			pstm.close();
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}
	//返回学生数据总数：
	public void countstu(PersonvoUser personuser) throws Exception
	{
		String sql = "SELECT count(*)as pagecount FROM user_table where type='学生'";
		PreparedStatement pstmt = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		try
		{  
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			ResultSet rs = pstmt.executeQuery() ;
	    while(rs.next())
			{
				//PersonvoUser person = new PersonvoUser() ;
	    	personuser.setPagecount(rs.getInt("pagecount")) ;
			}
	    rs.close() ;
		pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
	}
	//返回管理员数据总数
	public void countadm(PersonvoUser personuser) throws Exception
	{
		String sql = "SELECT count(*)as pagecount FROM user_table where type='管理员'";
		PreparedStatement pstmt = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		try
		{  
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			ResultSet rs = pstmt.executeQuery() ;
	    while(rs.next())
			{
				//PersonvoUser person = new PersonvoUser() ;
	    	personuser.setPagecount(rs.getInt("pagecount")) ;
			}
	    rs.close() ;
		pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
	}
	//返回老师数据总数
	public void counttch(PersonvoUser personuser) throws Exception
	{
		String sql = "SELECT count(*)as pagecount FROM user_table where type='老师'";
		PreparedStatement pstmt = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;
		try
		{  
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			ResultSet rs = pstmt.executeQuery() ;
	    while(rs.next())
			{
			
	    	personuser.setPagecount(rs.getInt("pagecount")) ;
			}
	    rs.close() ;
		pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			throw new Exception("操作中出现错误！！！") ;
		}
		finally
		{
			dbc.close() ;
		}
	}
	//查询管理员用户信息
	public List queryAlladm() throws Exception 
	{
		List all = new ArrayList() ;
		String sql = "select tid,name,workno from user_table where type='管理员'";
		PreparedStatement pstm = null ;
		DateBaseConnection dbc = null ;
		dbc = new DateBaseConnection() ;