2023.4.26每日总结

private int tea_GetNextId(String faculty_id) throws Exception
    {
        int fid = Integer.valueOf(faculty_id);
        int MinId=fid*1000,MaxId=(fid+1)*1000;
        String sql="select tea_id from teacher where tea_id between ? and ?";
        PreparedStatement pre = connect.prepareStatement(sql);
        pre.setInt(1,MinId);
        pre.setInt(2,MaxId);
        ResultSet rs = pre.executeQuery();
        int max = MinId;
        while(rs.next())
        {
            int n=rs.getInt(1);
            if(n>max)
                max=n;
        }
        pre.close();
        return (max+1);
    }

    /*teacher query
     * 教师查询，将教师信息从数据库中查询出来，返回Pd_tea
     * 输入参数为：工号，即id(String)
     * 若有记录，则返回Pd_tea，若无记录，则返回null
     * Pd_tea包含所有tea信息，int id,String name,String major,String faculty,String job,String pas,String phone
     * */
    public Pd_tea tea_query(String id) throws Exception
    {
        String sql="select * from teacher where tea_id = ?";
        PreparedStatement pre = connect.prepareStatement(sql);
        pre.setString(1,id);
        ResultSet rs = pre.executeQuery();
        Pd_tea pdd = new Pd_tea();
        while(rs.next())
        {
            pdd.setId(rs.getInt(1));
            pdd.setName(rs.getString(2));
            pdd.setMajor(rs.getString(3));
            pdd.setFaculty(rs.getString(4));
            pdd.setJob(rs.getString(5));
            pdd.setPas(rs.getString(6));
            pdd.setPhone(rs.getString(7));
        }
        pre.close();
        if(pdd.getId()!=0)
            return pdd;
        else
            return null;
    }

    /* teacher Dim Query
     * 教师信息模糊查询，输入工号，姓名，专业，学院，职称中的一项或多项，实现模糊查询，5项都不输入则为全部显示
     * 输入参数：工号id(String)，姓名name(String)，专业major(String)，学院faulty(String)，职称job_title(String)
     * 返回参数类型为 wangzhan.Pd_tea[] ,储存所有符合条件的 wangzhan.Pd_tea
     * */
    //    sql="select stu_id from teacher where tea_id like ? and tea_name like ? and tea_major like ? and tea_faculty like ? and tea_job_title like ?";
    public Pd_tea[] tea_DimQuery(String id,String name,String major,String faculty,String job_title) throws Exception
    {
        String sql="select tea_id from teacher where tea_id like ? and tea_name like ? and tea_major like ? and tea_faculty like ? and tea_job_title like ?";
        PreparedStatement pre = connect.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
        pre.setString(1,"%"+id+"%");
        pre.setString(2,"%"+name+"%");
        pre.setString(3,"%"+major+"%");
        pre.setString(4,"%"+faculty+"%");
        pre.setString(5,"%"+job_title+"%");
        ResultSet rs = pre.executeQuery();
        rs.last();
        int rowCount=rs.getRow();
        rs.beforeFirst();
        Pd_tea[] pdTeas = new Pd_tea[rowCount];
        int i=0;
        while(rs.next())
        {
            pdTeas[i]=tea_query(rs.getInt(1)+"");
            i++;
        }
        return pdTeas;
    }