schooldao.java

包括所有开发文档以及数据库文件

package dao;
import java.sql.*;
import java.util.Vector;


import bo.*;
public class SchoolDAO {
	/**
	 * 学校查询
	 
	 * 创建时间:2009-03-17
	 */
	public Vector selectSchoolByName(String userName) {
		
		Vector<UserSchool> vResult = new Vector<UserSchool>(1, 1);
		Connection con=null;
		PreparedStatement pstmt1=null;
		PreparedStatement pstmt2=null;
		ResultSet rs1=null;
		ResultSet rs2=null;
		String sql1="";
		String sql2="";
		try
		{  
			//查询直接下级用户和所管辖学校
			sql1="select A.userName,C.* from user A,school_user B,school C where A.userId=B.userId and B.schoolId= C.schoolId and A.parentName=?";
			sql2="select A.userName,C.* from user A,school_user B,school C where A.userId=B.userId and B.schoolId= C.schoolId and A.userName=?";
            con = DB.getConnection();
			pstmt1 = con.prepareStatement(sql1);
			pstmt1.setString(1, userName);
			pstmt2 = con.prepareStatement(sql2);
			pstmt2.setString(1, userName);
			rs1=pstmt1.executeQuery();
			rs2=pstmt2.executeQuery();
			int i=0;
			while (rs1!= null && rs1.next()&& i<20){
				i++;
				UserSchool school=new UserSchool();
				school.setUserName(rs1.getString("userName"));
				school.setSchoolId(rs1.getInt("schoolId"));
				school.setSchoolName(rs1.getString("schoolName"));
				school.setSchoolAddress(rs1.getString("schoolAddress"));
				vResult.add(school);
			}
			int j=0;
			while (rs2!= null && rs2.next()&& j<20){
				j++;
					UserSchool school1=new UserSchool();
					school1.setUserName(rs2.getString("userName"));
					school1.setSchoolId(rs2.getInt("schoolId"));
					school1.setSchoolName(rs2.getString("schoolName"));
					school1.setSchoolAddress(rs2.getString("schoolAddress"));
					vResult.add(school1);
			}
			rs1.close();
			rs2.close();
			pstmt1.close();
			pstmt2.close();
			con.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return vResult;
	}
	/**
	 
	 * 2009-03-19
	 * @param schoolId
	 * @param schoolName
	 * @param schoolAddress
	 * @return
	 * @throws SQLException 
	 */

    public void addSchool(String schoolName,String schoolAddress) {
    	Connection con=null;
		PreparedStatement pstmt1=null;
		String sql1="";
		
		try
		{
			//新增学校
			sql1="insert into school(schoolName,schoolAddress) values(?,?)";
			con=DB.getConnection();
			pstmt1 = con.prepareStatement(sql1);
			pstmt1.setString(1,schoolName);
			pstmt1.setString(2, schoolAddress);
			pstmt1.executeUpdate();
			con.close();
			pstmt1.close();
			
		
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
    
/**
 * 根据schooName查询学校
 * @param schoolName
 * @return
 */
public School selectSchoolByschoolName(String schoolName) {
		
		
		Connection con=null;
		PreparedStatement pstmt1=null;
		
		ResultSet rs1=null;
	    School school=null;
		String sql1="";
		
		try
		{  
			//查询学校
			sql1="select * from school where schoolName=?";
		    con = DB.getConnection();
			pstmt1 = con.prepareStatement(sql1);
			pstmt1.setString(1, schoolName);
		    rs1=pstmt1.executeQuery();

			
		    int i=0;
			while (rs1!= null && rs1.next()&& i<20){
				i++;
				school=new School();
				
				school.setSchoolId(rs1.getInt("schoolId"));
				school.setSchoolName(rs1.getString("schoolName"));
				school.setSchoolAddress(rs1.getString("schoolAddress"));
				
			}
		    rs1.close();
			pstmt1.close();
			
			con.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}

		return school;
	}

/**
 * 将学校插入到school_user表中
 * @param userId
 * @param schoolId
 */
    public void assginSchool(int schoolId) {
    	Connection con=null;
		PreparedStatement pstmt1=null;
		String sql1="";
		
		try
		{
			//将学校插入到school_user表中
			sql1="insert into school_user(userId,schoolId) values(1,?)";
			con=DB.getConnection();
			pstmt1 = con.prepareStatement(sql1);

			pstmt1.setInt(1, schoolId);
			pstmt1.executeUpdate();
			con.close();
			pstmt1.close();
			
		
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
    }
    /**
     * 修改学校
	 * 2009-03-19
     * @param schoolName
     * @param schoolAddress
     */
	public void updateSchool(String schoolName,String schoolAddress,String schoolId) {
		Connection con=null;
		PreparedStatement pstmt=null;
		String sql="";
		try
		{
			//修改学校
			sql="update school set schoolName=?,schoolAddress=? where schoolId=?";
			con=DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1,schoolName);
			pstmt.setString(2, schoolAddress);
			pstmt.setString(3, schoolId);
			pstmt.executeUpdate();
			con.close();
			pstmt.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	/**
	 * 删除学校
	 * 2009-03-19
	 * @param schoolId
	 */
	public void deleteSchool(String schoolId) {
		Connection con=null;
		PreparedStatement pstmt1=null;
		PreparedStatement pstmt2=null;
		String sql1="";
		String sql2="";
		try
		{
			//删除学校
			sql1="delete from user where user.schoolName=(select schoolName from school where schoolId=?)";
			sql2="delete from school where schoolId=?";
			con=DB.getConnection();
			pstmt1 = con.prepareStatement(sql1);
			pstmt2 = con.prepareStatement(sql2);
			pstmt1.setString(1, schoolId);
			pstmt2.setString(1, schoolId);
			pstmt1.executeUpdate();
			pstmt2.executeUpdate();
			con.close();
			pstmt1.close();
			pstmt2.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	

}