userdao.java

07年做得人力资源管理系统

package com.buat.hr.users;

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

import com.buat.hr.share.DBConnection;



public class UserDao implements IUserDao {
	DBConnection db=new DBConnection(); //保存sql语句

	public static void main(String[] args) {
		//new UserDao().getDate();
	}
                                                                     //新增 记录
	public boolean addUser(User user) {
		boolean flag=false;
		Connection con=null;
		PreparedStatement sql=null;
		String str=null;     

		con=db.getConnection();
		str="insert into users(username,passwords,gender,birthday,createtime,isadmin,pic,introduce) values(?,?,?,?,?,?,?,?)";
		try {
			sql=con.prepareStatement(str,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			sql.setString(1,user.getUsername());
			sql.setString(2,user.getPasswords());
			sql.setString(3,user.getGender());
			sql.setDate(4,Date.valueOf(user.getBirthday()));
			
			sql.setDate(5, Date.valueOf(new UserDao().getDate()));
			sql.setString(6,user.getIsadmin());
			sql.setString(7,user.getPic());
			sql.setString(8,user.getIntroduce());
			
			int row=sql.executeUpdate();
			if(row>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}finally{
			try {
				if(sql!=null){
					sql.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
			}
		}
		return flag;
	}

	public boolean deleteUserByid(int id) {         //删除记录
		boolean flag=false;
		Connection con=null;
		PreparedStatement sql=null;
		String str=null;
		
		con=db.getConnection();
		str="delete from users where id=?";
		try {
			sql=con.prepareStatement(str);
			sql.setInt(1,id);
			int row=sql.executeUpdate();
			if(row>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}finally{
			try {
				if(sql!=null){
					sql.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
			}
		}
		return flag;
		
	}

	public ArrayList queryUser(int startNo,int perCount) {    //查询记录
		ArrayList list=new ArrayList();
		Connection con=null;
		PreparedStatement sql=null;
		ResultSet rs=null;
		String str=null;
		
		con=db.getConnection();
		str="select * from users limit ?,?";
		try {
			sql=con.prepareStatement(str);
			sql.setInt(1,startNo);
			sql.setInt(2,perCount);
			
			rs=sql.executeQuery();
			
			while(rs.next()){
				User user=new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPasswords(rs.getString("passwords"));
				user.setGender(rs.getString("gender"));
				user.setBirthday(rs.getString("birthday"));
				user.setCreatetime(rs.getString("createtime"));
				user.setIsadmin(rs.getString("isadmin"));
				list.add(user);
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(sql!=null){
					sql.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
			}
		}
		return list;
	}

	public boolean updateUserByid(int id, User user) {    //修改记录
		boolean flag=false;
		Connection con=null;
		PreparedStatement sql=null;
		ResultSet rs=null;
		String str=null;
		
		con=db.getConnection();
		str="update users set username=?,passwords=?,gender=?,birthday=?,isadmin=?,pic=?,introduce=? where id=?";
		try {
			sql=con.prepareStatement(str);
			sql.setString(1,user.getUsername());
			sql.setString(2,user.getPasswords());
			sql.setString(3,user.getGender());
			sql.setDate(4,Date.valueOf(user.getBirthday()));
			sql.setString(5,user.getIsadmin());
			sql.setString(6,user.getPic());
			sql.setString(7,user.getIntroduce());
			sql.setInt(8,id);
			int row=sql.executeUpdate();
			if(row>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(sql!=null){
					sql.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}

	public User queryUserById(int id) {            //按id查询
		User user=new User();
		Connection con=null;
		PreparedStatement sql=null;
		ResultSet rs=null;
		String str=null;
		
		con=db.getConnection();
		str="select * from users where id=?";
		try {
			sql=con.prepareStatement(str);
			sql.setInt(1,id);
			rs=sql.executeQuery();
			
			while(rs.next()){
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setPasswords(rs.getString("passwords"));
				user.setGender(rs.getString("gender"));
				user.setBirthday(rs.getString("birthday"));
				user.setCreatetime(rs.getString("createtime"));
				user.setIsadmin(rs.getString("isadmin"));
				user.setPic(rs.getString("pic"));
				user.setIntroduce(rs.getString("introduce"));
			}
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(sql!=null){
					sql.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return user;
	}

	public int getUserCount() {                    //查询总记录
		int count=0;
		Connection con=null;
		PreparedStatement sql=null;
		ResultSet rs=null;
		String str=null;
		
		con=db.getConnection();
		str="select count(*) as count from users";
		try {
			sql=con.prepareStatement(str);
	
			rs=sql.executeQuery();
			while(rs.next()){
				count=rs.getShort("count");
			}
			
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(sql!=null){
					sql.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
			}
		}
		return count;
	}

	public User queryUser(String username,User use) {           //判断登陆
		User user=new User();
		Connection con=null;
		PreparedStatement sql=null;
		ResultSet rs=null;
		String str=null;
		
		con=db.getConnection();
		str="select * from users where username=?";
		try {
			sql=con.prepareStatement(str);
			sql.setString(1,username);
			rs=sql.executeQuery();
			
			if(rs.next()){
				if(rs.getString("passwords").equals(use.getPasswords())){
					user.setMassage("登录成功！！！");
					user.setIsadmin(rs.getString("isadmin"));
				}
				else{
					user.setMassage("密码错误！！！");
				}
			}else{
				user.setMassage("用户名或密码错误！！！");
			}
		} catch (SQLException e) {
			//e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(sql!=null){
					sql.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
			}
		}
		return user;
	}
	/*public ArrayList queryUserByName() {         //判断用户名是否重复
		ArrayList list=new ArrayList();
		Connection con=null;
		PreparedStatement sql=null;
		ResultSet rs=null;
		String str=null;
		
		con=db.getConnection();
		str="select username from users";
		try {
			sql=con.prepareStatement(str);
			rs=sql.executeQuery();
			
			while(rs.next()){
				User user=new User();
				user.setUsername(rs.getString("username"));
				list.add(user);
				//user.setMassage("此用户名"+name+"已存在!请重新输入");
			}
		
				//user.setMassage("你申请的用户名为"+name);
	
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(sql!=null){
					sql.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
			}
		}
		return list;
	}*/
	public String getDate() {                 //获取系统时间
		String date=null;
		Connection con=null;
		PreparedStatement sql=null;
		ResultSet rs=null;
		String str=null;
		
		con=db.getConnection();
		str="select current_date()";
		try {
			sql=con.prepareStatement(str);
	
			rs=sql.executeQuery();
			while(rs.next()){
				date=rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(sql!=null){
					sql.close();
				}
				if(con!=null){
					con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				//e.printStackTrace();
			}
		}
		//System.out.println(date);
		return date;
	}
}