usersdao.java

java写的一个简单的bbs系统

package com.ibm.dao;

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

import com.ibm.common.DBConnection;
import com.ibm.dto.UsersDTO;
import com.ibm.vo.UsersVO;

public class UsersDAO {
    
	private PreparedStatement ps;
	private ResultSet rs;
	
    
   
    
    /**
     * 注册用户功能
     * @param con
     * @param udto
     * @return
     */
    public boolean insertUser(Connection con,UsersDTO udto)
    {
    	boolean flag=false;
    	String sql="insert into users(username,password,sex,headimg,regtime) values(?,?,?,?,?)";
    	
    	try {
			ps=con.prepareStatement(sql);
			ps.setString(1, udto.getUsername());
			ps.setString(2, udto.getPassword());
			ps.setString(3, udto.getSex());
			ps.setString(4, udto.getHeadimg());
			ps.setTimestamp(5, udto.getRegtime());
			
			ps.executeUpdate();
			flag=true;
		} catch (SQLException e) {
			
			e.printStackTrace();
			System.out.println("UsersDAO.insertUser");
		}finally{
			DBConnection.getInstance().close(null, ps, null, null, con);
		}
    	
    	return flag;
    }
    
    /**
     * 更新用户表
     * @param con
     * @param udto
     * @return
     */
    public boolean updateUser(Connection con,UsersDTO udto)
    {
    	boolean flag=false;
    	String sql="update users set username=?,password=?,sex=?,headimg=?,regtime=? where uid=?";
    	try {
			ps=con.prepareStatement(sql);
			ps.setString(1, udto.getUsername());
			ps.setString(2, udto.getPassword());
			ps.setString(3, udto.getSex());
			ps.setString(4, udto.getHeadimg());
			ps.setTimestamp(5,udto.getRegtime());
			ps.setInt(6, udto.getUid());
			
			ps.executeUpdate();
			flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("UsersDAO.updateUser");
		}finally{
			DBConnection.getInstance().close(null, ps, null, null, con);
		}
		
		return flag;
    }
    
    /**
     * 用户删除功能
     * @param con
     * @param uid
     * @return
     */
    public boolean deleteUser(Connection con,int uid)
    {
    	boolean flag=false;
    	String sql="delete from users where uid=?";
    	try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, uid);
			
			ps.executeUpdate();
			flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("UsersDAO.deleteUser");
		}finally{
			DBConnection.getInstance().close(null, ps, null, null, con);
		}
		
		return flag;
    }
    
   /**
    * 实现登录功能
    * @param con
    * @param username
    * @param password
    * @return
    */
    public UsersVO queryUser(Connection con,String username,String password)
    {
    	String sql="select * from users where username=? and password=?";
    	UsersVO uvo=null;
    	try {
			ps=con.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			
			rs=ps.executeQuery();
			
			while(rs.next())
			{
	            uvo=new UsersVO();			
//				uvo=UsersVO.getInstance();
				uvo.setUsername(rs.getString("username"));
				uvo.setPassword(rs.getString("password"));
				uvo.setSex(rs.getString("sex"));
				uvo.setRegtime(rs.getTimestamp("regtime"));
				uvo.setHeadimg(rs.getString("headimg"));
				uvo.setUid(rs.getInt("uid"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBConnection.getInstance().close(null, ps, null, rs, con);
		}
		
		return uvo;
    }
    
    /**
     * 根据用户id查询用户
     * @param con
     * @param uid
     * @return
     */
    public UsersVO queryByUser(Connection con,int uid)
    {
    	String sql="select * from users where uid=?";
    	UsersVO uvo=null;
    	try {
			ps=con.prepareStatement(sql);
			ps.setInt(1, uid);
			
			rs=ps.executeQuery();
			
			while(rs.next())
			{
                uvo=new UsersVO();
//				uvo=UsersVO.getInstance();
				uvo.setUsername(rs.getString("username"));
				uvo.setPassword(rs.getString("password"));
				uvo.setSex(rs.getString("sex"));
				uvo.setRegtime(rs.getTimestamp("regtime"));
				uvo.setHeadimg(rs.getString("headimg"));
				uvo.setUid(rs.getInt("uid"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		finally{
//			DBConnection.getInstance().close(null, ps, null, rs, con);
//		}
		
		return uvo;
    }
    
    /**
     * 查询所有用户的信息
     * @param con
     * @return
     */
    public List queryAllUser(Connection con)
    {
    	String sql="select * from users";
    	List list=new ArrayList();
    	UsersVO uvo=null;
    	try {
			ps=con.prepareStatement(sql);
			
			rs=ps.executeQuery();
			
			while(rs.next())
			{
                uvo=new UsersVO();
//				uvo=UsersVO.getInstance();
				uvo.setUsername(rs.getString("username"));
				uvo.setPassword(rs.getString("password"));
				uvo.setSex(rs.getString("sex"));
				uvo.setRegtime(rs.getTimestamp("regtime"));
				uvo.setHeadimg(rs.getString("headimg"));
				uvo.setUid(rs.getInt("uid"));
				
				list.add(uvo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBConnection.getInstance().close(null, ps, null, rs, con);
		}
		return list;
    }
}