tj_state_query.java

1.员工添加 2.人事调动 3.在职人员管理 4.部门（分厂）管理

package pmsys.wyj;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.DynaActionForm;
import org.apache.struts.actions.DispatchAction;

import javax.servlet.http.HttpSession;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import pmsys.*;

public class TJ_State_query {
	/**
	 * 查询
	 */
	public ArrayList state_query(DataSource ds,HttpServletRequest request,String tjdept,String states) {
		ArrayList dlist1 = new ArrayList();
		Connection conn = null;
		Statement stmt = null;
		try{
			//链接池连接数据库
			conn = ds.getConnection();
			
			//查找数据库中全部职称类型的记录
			int k = 0;
			int n = 0;
			int h = 0;
			String n1 = "";
			String state = states;
				n = 0;
				h = 0;
				n1 = "";
				//查找数据库中匹配职称类型的记录        
				String sql2 = "SELECT * FROM employees WHERE states_id=? AND dep_dep_id=?";
				PreparedStatement pstmt = conn.prepareStatement(sql2);
				pstmt.setString(1,state);
				pstmt.setString(2,tjdept);
				ResultSet rs2 = pstmt.executeQuery();
					
				while(rs2.next()){
					
					UserForm dept1 = new UserForm();
					dept1.setDeptno(rs2.getInt("emp_id")+"");
					dept1.setDname(rs2.getString("emp_name"));
					dlist1.add(dept1);
					n++;
					k++;
				}
				n1 = n + "";
				ArrayList dlist2 = new ArrayList();
				UserForm dept3 = new UserForm();
				dept3.setDeptno(n1);
				dlist2.add(dept3);
				request.setAttribute("j0",dlist2);
				String dept = "dept0";
				request.setAttribute(dept,dlist1);
				request.setAttribute("count",k + "");
				//查找全厂该职称总数
				String sql4 = "SELECT * FROM employees WHERE states_id=?";
				//stmt = conn.createStatement();
				PreparedStatement pstmt4 = conn.prepareStatement(sql4);
				pstmt4.setString(1,state);
				ResultSet rs4 = pstmt4.executeQuery();
				while(rs4.next()){
					h++;
				}
				request.setAttribute("count0",h + "");
			
			//查找当前部门名称
			String sql3 = "SELECT * FROM departments where dep_id=?";
			//stmt = conn.createStatement();
			PreparedStatement pstmt3 = conn.prepareStatement(sql3);
			pstmt3.setString(1,tjdept);
			ResultSet rs3 = pstmt3.executeQuery();
			rs3.next();
			request.setAttribute("dept",rs3.getString("dep_name"));
			request.setAttribute("dep_id",rs3.getString("dep_id"));
			//某部门有职称人数
			request.setAttribute("dept_count",k + "");  
		
		} catch(Exception e1) {
			e1.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		return dlist1;
	}
}