loginda.java

医药供应链管理系统

package com.captainli.dboperation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.captainli.bean.UsersAllBean;
import com.captainli.bean.UsersBean;
import com.captainli.bean.VerManBean;
import com.captainli.struts.form.ChangPwdForm;
import com.captainli.struts.form.LoginForm;
import com.captainli.util.GetConnection;

/**
 * Login表数据操作
 * @author CaptainLi
 *
 */
public class LoginDA {
	private Connection conn = GetConnection.getConn();
	private Statement stmt = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	
	/**
	 * 关闭数据库对象
	 *
	 */
	public void closeDB(){
		try {
			if(rs != null){
				rs.close();
			}
			if(stmt != null){
				stmt.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
			if(conn != null){
				conn.close();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
	}
	/**
	 * 用户名和密码验证
	 * @param l_username
	 * @param l_password
	 * @return
	 */
	public boolean login(LoginForm form){
		boolean flag = false;
		String sql = "select * from login where l_username = ? and l_password = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getL_username());
			pstmt.setString(2, form.getL_password());
			rs = pstmt.executeQuery();
			if(rs.next()){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return flag;
	}
	/**
	 * 验证是否存在此用户
	 * @param form
	 * @return
	 */
	public boolean checkUsername(LoginForm form){
		boolean flag = false;
		String sql = "select * from login where l_username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getL_username());
			rs = pstmt.executeQuery();
			if(rs.next()){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return flag;
	}
	/**
	 * 检查登录名唯一
	 * @param l_username
	 * @return
	 */
	public boolean checkUsername(String l_username){
		boolean flag = false;
		String sql = "select * from login where l_username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, l_username);
			rs = pstmt.executeQuery();
			if(rs.next()){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return flag;
	}
	/**
	 * 检查用户是否被禁用
	 * @param form
	 * @return
	 */
	public boolean checkType(LoginForm form){
		boolean flag = false;
		String sql = "select l_type from login where l_username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getL_username());
			rs = pstmt.executeQuery();
			if(rs.next()){
				if(rs.getString("l_type").equals("1")){
					flag = true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return flag;
	}
	/**
	 * 返回登录用户的名字
	 * @param form
	 * @return
	 */
	public String readName(LoginForm form){
		String tmp = "";
		String sql = "select l_name from login where l_username = ? and l_password = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getL_username());
			pstmt.setString(2, form.getL_password());
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getString("l_name").toString();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 返回登录用户的部门
	 * @param form
	 * @return
	 */
	public String readSector(LoginForm form){
		String tmp = "";
		String sql = "SELECT sector.s_name FROM sector INNER JOIN login ON sector.s_id = login.l_s_id where l_username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getL_username());
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getString("s_name").toString();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 用户上线
	 */
	public void goLine(LoginForm form){
		String sql = "update login set l_online = ? where l_username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setBoolean(1, true);
			pstmt.setString(2, form.getL_username());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 用户下线
	 */
	public void outLine(LoginForm form){
		String sql = "update login set l_online = ? where l_username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setBoolean(1, false);
			pstmt.setString(2, form.getL_username());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 在线人数
	 * @return
	 */
	public String onLine(){
		String tmp = "";
		String sql = "select count(l_online) online from login where l_online = 1";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
				tmp = rs.getString("online");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
	/**
	 * 用户修改密码
	 * @param form
	 * @param l_username
	 */
	public void changPwd(ChangPwdForm form, String l_username){
		String sql = "update login set l_password = ? where l_username = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getPwd_new());
			pstmt.setString(2, l_username);
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 员工管理 全部显示
	 * @return
	 */
	public ArrayList selectUsers(){
		ArrayList arry = new ArrayList();
		UsersBean bean = null;
		String sql = "SELECT login.l_id, login.l_no, login.l_name, login.l_sex, sector.s_name FROM login INNER JOIN sector ON login.l_s_id = sector.s_id";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new UsersBean(rs.getInt("l_id"), rs.getString("l_no"), rs.getString("l_name"), rs.getString("l_sex"), rs.getString("s_name"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}