jdbctest1.java

用jsp,servlet实现人力资源管理系统

package cn.com.search;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

public class JDBCTest1 {
	Statement stmt = null;
	Connection conn = null;
	String sql=null;
	ResultSet set=null;
		public List getInfo(String emp_name,String emp_sex,String emp_state,String emp_degree,String emp_birth,String emp_department,String emp_work,String emp_school){
			List list = new ArrayList();
		try {
			//注册
			 Class.forName("oracle.jdbc.driver.OracleDriver");
			//取得连接
			 conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:WOLF","system","system");
			//创建Statement
			 stmt = conn.createStatement();
			if(emp_name==""&&emp_sex==""&&emp_state==""&&emp_degree==""
					&&emp_birth==""&&emp_department==""&&emp_work==""&&emp_school==""){
				 sql="SELECT * from emp_info";
			 }else{
			 sql="SELECT * from emp_info where ";
			 if(emp_name!=""){
				 sql = sql+"EMP_name = '" + emp_name +"'";
				 System.out.println("name");

			 }

			 if(emp_name=="" && emp_sex!=""){
				 System.out.println("sex");
				 sql = sql+" EMP_SEX = '" + emp_sex +"'";
			 }else if(emp_name!=""&&emp_sex!=""){
				 sql = sql+" and EMP_SEX = '" + emp_sex +"'";
			 }
			 
			 if(emp_name == ""&&emp_sex == ""&&emp_state!=""){
				 sql = sql+" EMP_STATE = '" + emp_state +"'";
			 }else if(!(emp_name == ""&&emp_sex == "")&&emp_state!=""){
				 sql = sql+" and EMP_STATE = '" + emp_state +"'";
			 }
			 
			 if(emp_name == ""&&emp_sex == ""&&emp_state==""&&emp_degree!=""){
				 sql = sql+" EMP_DEGREE = '" + emp_degree +"'";
			 }else if(!(emp_name == ""&&emp_sex == ""&&emp_state=="")&&emp_degree!=""){
				 sql = sql+" and EMP_DEGREE = '" + emp_degree +"'";
			 }
			 
			 if(emp_name == ""&&emp_sex == ""&&emp_state==""&&emp_degree==""&&emp_birth!=""){
				 sql = sql+" EMP_BIRTH = '" + emp_birth +"'";
			 }else if(!(emp_name == ""&&emp_sex == ""&&emp_state==""&&emp_degree=="")&&emp_birth!=""){
				 sql = sql+" and EMP_BIRTH = '" + emp_birth +"'";
			 }
			 
			 if(emp_name == ""&&emp_sex == ""&&emp_state==""&&emp_degree==""&&emp_birth==""&&emp_department!=""){
				 sql = sql+" EMP_DEPARTMENT = '" + emp_department +"'";
			 }else if(!(emp_name == ""&&emp_sex == ""&&emp_state==""&&emp_degree==""&&emp_birth=="")&&emp_department!=""){
				 sql = sql+" and EMP_DEPARTMENT = '" + emp_department +"'";
			 }
			 
			 if(emp_name == ""&&emp_sex == ""&&emp_state==""&&emp_degree==""&&emp_birth==""&&emp_department==""&&emp_work!=""){
				 sql = sql+" EMP_WORK = '" + emp_work +"'";
			 }else if(!(emp_name == ""&&emp_sex == ""&&emp_state==""&&emp_degree==""&&emp_birth==""&&emp_department=="")&&emp_work!=""){
				 sql = sql+" and EMP_WORK = '" + emp_work +"'";
			 }
			 
			 if(emp_name == ""&&emp_sex == ""&&emp_state==""&&emp_degree==""&&emp_birth==""&&emp_department==""&&emp_work==""&&emp_school==""){
				 sql = sql+" EMP_SCHOOL = '" + emp_school +"'";
			 }else if(!(emp_name == ""&&emp_sex == ""&&emp_state==""&&emp_degree==""&&emp_birth==""&&emp_department==""&&emp_work=="")&&emp_school!=""){
				 sql = sql+" and EMP_SCHOOL = '" + emp_school +"'";
			 }
			 
			 }
			 System.out.println(sql);
			 set=stmt.executeQuery(sql);

			 
			 UserInfo user = null;
			 while(set.next()){
				//
				 user = new UserInfo();
				 user.setEmp_id(set.getString("EMP_ID"));
				 user.setEmp_name(set.getString("EMP_NAME"));
				 user.setEmp_sex(set.getString("EMP_SEX"));
				 user.setEmp_birth(set.getString("EMP_BIRTH"));
				 user.setEmp_idenfy(set.getString("EMP_IDENFY"));
				 
				 
				//System.out.println(rs.getString("username"));
				list.add(user);
				
			}
			 
			} catch (SQLException e) {
				e.printStackTrace();
			}catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
			return list;
		}
		public void connClose(){

			try {
				set.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		
	}