userdaoimpl.java

MyEclipse下写的基于JSP/Servlet的小型用户管理系统

package daopkg.impl;

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

import utils.JDBCUtils;
import vopkg.UserVO;
import daopkg.IUserDAO;
import exception.UserDAOImplExceptiom;

public class UserDAOImpl implements IUserDAO {
    private Connection conn = null;
    private PreparedStatement psmt= null;
    private ResultSet rs=null;
	public int checkLogin(UserVO userVO) {
		// TODO Auto-generated method stub
		int flag = 0;
		if(!this.checkUserName(userVO)){
			flag = 3;		//用户不存在
		}
		else{
		String pwd = null;
		conn = JDBCUtils.getConnection();
		String sql = "select password from user_0801 where name = ?";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1, userVO.getName());
			rs = psmt.executeQuery();
			while(rs.next()){
				pwd = rs.getString("password");
			}
			if(pwd.equals(userVO.getPassword())){
				flag = 1;	//密码正确
			}else{
				flag = 2;	//密码错误
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag = 0;
			new UserDAOImplExceptiom("checkLogin ERROR!",e);
		}finally{
			JDBCUtils.closeConnection(rs, psmt, conn);
		}
		}
		
		return flag;
	}

	public boolean checkUserName(UserVO userVO) {
		// TODO Auto-generated method stub
		boolean flag=false;
		conn = JDBCUtils.getConnection();
		String sql = "select name from user_0801 where name = ?";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1,userVO.getName());
			String temp=userVO.getName();
			System.out.println(temp);
			rs = psmt.executeQuery();
			if(rs.next()){
			flag = true;
			System.out.println("查找成功!");
			}
			else{
				flag = false;
			}
		} catch (SQLException e) {
			flag = false;
			new UserDAOImplExceptiom("checkUserName ERROR!",e);
		}finally{
			JDBCUtils.closeConnection(rs, psmt, conn);
		}
         return flag;
	}

	public boolean deleteAllUser() {
		// TODO Auto-generated method stub
		boolean flag = false;
		//首先获取连接
		conn = JDBCUtils.getConnection();
		//得到执行语句对象和要执行的SQL语句
		String sql = "delete from user_0801";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.executeUpdate();
			System.out.println("-------[Delete操作成功!]-------!");
			flag = true;
		} catch (SQLException e) {
			System.out.println("-------[Delete操作失败!]-------!");
			e.printStackTrace();
			flag = false;
		}finally{
			JDBCUtils.closeConnection(null,psmt,conn);
	}
		return flag;
	}

	public boolean deleteByID(int id) {
		// TODO Auto-generated method stub
		boolean flag=false;
		//首先获取连接
		conn = JDBCUtils.getConnection();
		//得到执行语句对象和要执行的SQL语句
		String sql = "delete from user_0801 where id = ?";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1,id);
			psmt.executeUpdate();
			System.out.println("-------[Delete操作成功!]-------!");
			flag = true;
		} catch (SQLException e) {
			System.out.println("-------[Delete操作失败!]-------!");
			e.printStackTrace();
			flag = false;
		}finally{
			JDBCUtils.closeConnection(null,psmt,conn);
	}
		return flag;
	}

//	public ArrayList queryAllUser() {
//		// TODO Auto-generated method stub
//		ArrayList userlist = new ArrayList();
//		return userlist;
//	}
	public ResultSet queryAllUser(){
		ResultSet rs = null;
		conn = JDBCUtils.getConnection();
		//创建执行SQL语句对象
		try {
			String sql = "select * from  user_0801";
			psmt = conn.prepareStatement(sql);
			rs = psmt.executeQuery();
		 	if(rs != null){
		 		return rs;
		  	}
		} catch (SQLException e) {
			System.out.println("-------[Select全部数据操作失败!]-------!");
			e.printStackTrace();
		}
		return rs;
	}


	public boolean regUser(UserVO userVO) {
		boolean flag = false;
		if(this.checkUserName(userVO)){
			flag = false;
		}else{
		System.out.println("注册用户");
		conn = JDBCUtils.getConnection();
		String sql = "insert into user_0801 (name,password,sex,dept,fav,tel)values (?,?,?,?,?,?)";
		try {
			psmt = conn.prepareStatement(sql);
			psmt.setString(1,userVO.getName());
			psmt.setString(2, userVO.getPassword());
			psmt.setString(3, userVO.getSex());
			psmt.setString(4, userVO.getDept());
			psmt.setString(5, userVO.getFav());
			psmt.setString(6, userVO.getTel());
			psmt.executeUpdate();
			flag = true;
			System.out.println("注册成功!");
		} catch (SQLException e) {
			flag = false;
			new UserDAOImplExceptiom("注册用户失败!",e);
		}finally{
			JDBCUtils.closeConnection(psmt, conn);
		}
		}
		return flag;
	}

	public boolean updateUserPwd(int id) {
		boolean flag = false;
		// TODO Auto-generated method stub
		conn = JDBCUtils.getConnection();
		//创建执行SQL语句对象
		try {
			String sql = "update user_0801 set password='888888' where id = ?";
			psmt = conn.prepareStatement(sql);
			psmt.setInt(1,id);
			psmt.executeUpdate();
			System.out.println("-------[Update操作成功!]-------!");
			flag = true;
		} catch (SQLException e) {
			System.out.println("-------[Update操作失败!]-------!");
			e.printStackTrace();
			flag = false;
		}finally{
			JDBCUtils.closeConnection(null,psmt,conn);
	}
		return flag;
	}
	   public ArrayList queryAll(){
		   ArrayList userList= new ArrayList();
		   conn=JDBCUtils.getConnection();
		   String sql="select * from user_0801";
		try {
			psmt = conn.prepareStatement(sql);
			rs=psmt.executeQuery();
			while(rs.next()){
				UserVO userVO=new UserVO();
				userVO.setId(rs.getInt("id"));
				userVO.setName(rs.getString("name"));
				userVO.setPassword(rs.getString("password"));
				userVO.setSex(rs.getString("sex"));
				userVO.setDept(rs.getString("dept"));
				userVO.setFav(rs.getString("fav"));
				userVO.setTel(rs.getString("tel"));
				userList.add(userVO);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}	   
		return userList;
	   }

}