4，3+3人力资源网系统（重要代码分析公司信息和职位信息的模糊查询）

//根据多个条件进行查询得到公司信息的列表

    public Vector getCompanyJobs(String tempPosition, String jobsName,

                                 String lastDate) {

        Vector vt = new Vector();

        String sql = "";

        int day = 0;

        if (!lastDate.equals("")) {

            day = Integer.parseInt(lastDate);

        }

        Connection conn = DB.getConn(); //连接对象

        if (tempPosition.equals("0")) {

            if (!jobsName.equals("")) {

                if (!lastDate.equals("")) { //根据有效时间和职位名称二者连接进行模糊查询

                    sql = "SELECT JobInfo.jobId, CompanyInfo.compId, CompanyInfo.companyName, JobInfo.jobsname, JobInfo.tempposition," +

                          " JobInfo.lastdate,  JobInfo.number FROM JobInfo , CompanyInfo where  JobInfo.compId = CompanyInfo.compId and  DATEDIFF(day,getdate(),lastdate) Between 0  and "+

                          day+" and jobsName like '%" +  jobsName + "%'";

                } else { //根据工作名称来进行查询======= 计算两个日期间的间隔。DATEDIFF(day,getdate(),lastdate) > 5

                    sql = "SELECT JobInfo.jobId, CompanyInfo.compId, CompanyInfo.companyName, JobInfo.jobsname, JobInfo.tempposition," +

                          " JobInfo.lastdate,  JobInfo.number FROM JobInfo , CompanyInfo where  JobInfo.compId = CompanyInfo.compId and jobsName like '%" +

                          jobsName + "%'";

                }

            } else { //根据有效时间进行查询

                if (!lastDate.equals("")) {

                    sql = "SELECT JobInfo.jobId, CompanyInfo.compId, CompanyInfo.companyName, JobInfo.jobsname, JobInfo.tempposition," +

                          " JobInfo.lastdate,  JobInfo.number FROM JobInfo , CompanyInfo where  JobInfo.compId = CompanyInfo.compId and DATEDIFF(day,getdate(),lastdate) Between 0  and "+ day;

                } else { //无条件的全部查询

                    sql = "SELECT JobInfo.jobId, CompanyInfo.compId, CompanyInfo.companyName, JobInfo.jobsname, JobInfo.tempposition, JobInfo.lastdate,  JobInfo.number FROM JobInfo , CompanyInfo where  JobInfo.compId = CompanyInfo.compId";

                } //根据行业类别和职位名称（***工程师***）来模糊查询

            }

        } else { //

            if (!jobsName.equals("")) {

                if (!lastDate.equals("")) { //根据行业名称和有效时间和职位名称三者连接进行模糊查询

                    sql = "SELECT JobInfo.jobId, CompanyInfo.compId, CompanyInfo.companyName, JobInfo.jobsname, JobInfo.tempposition," +

                          " JobInfo.lastdate,  JobInfo.number FROM JobInfo , CompanyInfo where  JobInfo.compId = CompanyInfo.compId and jobsName like '%" +

                          jobsName + "%' and tempPosition like '%" +

                          tempPosition +

                          "%' and DATEDIFF(day,getdate(),lastdate) Between 0  and "+ day;

                } else { //根据工作名称和行业名称来进行模糊查询===========根据行业类别和职位名称（***工程师***）来模糊查询

                    sql = "SELECT JobInfo.jobId, CompanyInfo.compId, CompanyInfo.companyName, JobInfo.jobsname, JobInfo.tempposition," +

                          " JobInfo.lastdate,  JobInfo.number FROM JobInfo , CompanyInfo where  JobInfo.compId = CompanyInfo.compId and jobsName like '%" +

                          jobsName + "%' and tempPosition like '%" +

                          tempPosition + "%'";

                }

            } else {

                if (!lastDate.equals("")) { //根据行业名称和有效时间进行查询

                    sql = "SELECT JobInfo.jobId, CompanyInfo.compId, CompanyInfo.companyName, JobInfo.jobsname, JobInfo.tempposition," +

                          " JobInfo.lastdate,  JobInfo.number FROM JobInfo , CompanyInfo where  JobInfo.compId = CompanyInfo.compId and tempPosition like '%" +

                          tempPosition +

                          "%' and DATEDIFF(day,getdate(),lastdate) Between 0  and "+ day;

                } else { //根据行业名称条件的来进行模糊查询

                    sql = "SELECT JobInfo.jobId, CompanyInfo.compId, CompanyInfo.companyName, JobInfo.jobsname, JobInfo.tempposition," +

                          " JobInfo.lastdate,  JobInfo.number FROM JobInfo , CompanyInfo where  JobInfo.compId = CompanyInfo.compId and tempPosition like '%" +

                          tempPosition + "%'";

                }

            }

        }

        try {

            PreparedStatement ps = conn.prepareStatement(sql);

            ResultSet rs = ps.executeQuery(); //获得记录集

            while (rs.next()) {

                Vector v = new Vector(); //一个单独的一行

                v.add(rs.getInt("jobId") + ""); //工作编号0

                v.add(rs.getInt("compId") + ""); //公司编号1

                v.add(rs.getString("companyName")); //公司名称2

                v.add(rs.getString("jobsname")); //工作名称3

                v.add(rs.getString("tempposition")); //岗位类别属于哪一个行业4

                v.add(rs.getString("lastdate")); //有效时间5

                v.add(rs.getInt("number") + ""); //招聘人数6

                vt.add(v); //添加到集合中去

            }

        } catch (SQLException ex) {

            ex.printStackTrace();

        }

        return vt; //返回一个像表一样的集合

    }

//根据当前行业//学历要求//性别要求来查询人才的集合

    public Vector getPersonJobs(String currentway, String degree, String sex2) {

        Vector vt = new Vector(); //

        String sql = ""; //

        Connection conn = DB.getConn(); //连接对象

        if (currentway.equals("0")) {   //WorkExperience  workId  Education educationId

            if (!degree.equals("10")) {

                if (!sex2.equals("不限")) { //根据学历和性别二者连接进行模糊查询

                   sql = "SELECT PersonInfo.personId, name, personType, currentWay, degree, sex ,workId,educationId FROM PersonInfo,WorkExperience,Education where Education.personId = PersonInfo.personId and PersonInfo.personId = WorkExperience.personId and  degree ='" + degree + "' and sex ='"+ sex2+"'";

                } else { //根据学历来进行查询======= 计算两个日期间的间隔。

                  DATEDIFF(day,getdate(),lastdate) > 5

                    sql = "SELECT PersonInfo.personId, name, personType, currentWay, degree, sex,workId,educationId FROM PersonInfo,WorkExperience,Education where Education.personId = PersonInfo.personId and PersonInfo.personId = WorkExperience.personId and degree ='" + degree + "'";

                }

            } else {

                if (!sex2.equals("不限")) {//根据性别进行查询

                    sql = "SELECT PersonInfo.personId, name, personType, currentWay, degree, sex,workId,educationId FROM PersonInfo,WorkExperience,Education where Education.personId = PersonInfo.personId and PersonInfo.personId = WorkExperience.personId and sex = '" + sex2 + "'";

                } else { //无条件的全部查询

                    sql = "SELECT PersonInfo.personId, name, personType, currentWay, degree, sex,workId,educationId FROM PersonInfo,WorkExperience,Education where Education.personId = PersonInfo.personId and PersonInfo.personId = WorkExperience.personId ";

                }

            }

        } else {

            if (!degree.equals("10")) {

                if (!sex2.equals("不限")) { //根据行业名称和学历和性别三者连接进行模糊查询

                     sql = "SELECT PersonInfo.personId, name, personType, currentWay, degree, sex ,workId,educationId FROM PersonInfo,WorkExperience,Education where Education.personId = PersonInfo.personId and PersonInfo.personId = WorkExperience.personId and  degree ='" + degree + "' and sex ='"+ sex2+"' and currentway like '%" + currentway+ "%'";

                } else { //根据学历和行业名称来进行模糊查询===========

                    sql = "SELECT PersonInfo.personId, name, personType, currentWay, degree, sex ,workId,educationId FROM PersonInfo,WorkExperience,Education where Education.personId = PersonInfo.personId and PersonInfo.personId = WorkExperience.personId and degree ='" + degree + "'  and currentway like '%" + currentway+ "%'";

            }

            } else {

                if (!sex2.equals("不限")) { //根据行业名称和性别进行查询

                    sql = "SELECT PersonInfo.personId, name, personType, currentWay, degree, sex ,workId,educationId FROM PersonInfo,WorkExperience,Education where Education.personId = PersonInfo.personId and PersonInfo.personId = WorkExperience.personId and  sex ='"+ sex2+"' and currentway like '%" + currentway+ "%'";

               } else { //根据行业名称条件的来进行模糊查询

                    sql = "SELECT PersonInfo.personId, name, personType, currentWay, degree, sex ,workId,educationId FROM PersonInfo,WorkExperience,Education where Education.personId = PersonInfo.personId and PersonInfo.personId = WorkExperience.personId and  currentway like '%" + currentway+ "%'";

               }

            }

        }

        try {

            PreparedStatement ps = conn.prepareStatement(sql);

            ResultSet rs = ps.executeQuery(); //获得记录集

            while (rs.next()) {

                Vector v = new Vector(); //一个单独的一行

                v.add(rs.getInt("personId")+"");  // //====== 0====personId

                v.add(rs.getString("name"));//==========1======== name

                v.add(rs.getString("personType"));//====2====personType

                v.add(rs.getString("currentWay"));//==3==currentWay

                v.add(rs.getString("degree"));//4degree

                v.add(rs.getString("sex"));//5sex

                v.add(rs.getInt("workId")+"");  //6

                v.add(rs.getInt("educationId")+"");  //7educationId

                vt.add(v); //添加到集合中去

            }

        } catch (SQLException ex) {

            ex.printStackTrace();

        }

        return vt; //返回一个像表一样的集合

    }