usersdao.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 com.ibm.common.DBConnection;
import com.ibm.dto.UsersDTO;
import com.ibm.vo.UsersVO;

public class UsersDAO {

	private static UsersDAO usersDAO;
	private PreparedStatement ps;
	private UsersDAO()
	{
		
	}
	public static UsersDAO getInstance()
	{
		if(usersDAO == null)
		{
			usersDAO = new UsersDAO();
		}
		return usersDAO;
	}
	
	public boolean insertUser(Connection conn,UsersDTO udto)
	{
		boolean flag = false;
		String sql = "insert into users(username,password,sex,headimg,regtime) values(?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, udto.getUsername());
			ps.setString(2,udto.getPassword());
			ps.setString(3, udto.getSex());
			ps.setString(4, udto.getHeadimg());
			ps.setString(5, udto.getRegtime());
			ps.executeUpdate();
			
			flag = true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBConnection.getInstance().close(null, ps, null, conn);
		}
		return flag;
	}
	
	
	public boolean updateUser(Connection conn,UsersDTO udto)
	{
		boolean flag = false;
		String sql = "update username =?,password=?,sex=?,headimg=?,regtime=? where uid = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, udto.getUsername());
			ps.setString(2,udto.getPassword());
			ps.setString(3, udto.getSex());
			ps.setString(4, udto.getHeadimg());
			ps.setString(5, udto.getRegtime());
			ps.setInt(6,udto.getUid());
			ps.executeUpdate();
			
			flag = true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBConnection.getInstance().close(null, ps, null, conn);
		}
		return flag;
	}
	
	public boolean deleteUser(Connection conn,int uid)
	{
		boolean flag = false;
		String sql = "delete from users where uid = ?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, uid);
			ps.executeUpdate();
			flag = true;
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBConnection.getInstance().close(null, ps, null, conn);
		}
		return flag;
	}
	
	public UsersVO queryUser(Connection conn,UsersDTO udto)
	{
		UsersVO usersVO = null;

		String sql = "select * from users where username=? and password=?";
		try {
			ps = conn.prepareStatement(sql);
			System.out.println(udto.getUsername());
			System.out.println(udto.getPassword());
			ps.setString(1,udto.getUsername());
			ps.setString(2,udto.getPassword());
			ResultSet rs = (ResultSet)ps.executeQuery();
			System.out.println(rs);
			while(rs.next())
			{
				System.out.println("bbbbbbbbbbbbbbb");
				usersVO = UsersVO.getInstance();
				usersVO.setUid(rs.getInt("uid"));
				System.out.println(rs.getInt("uid"));
				usersVO.setUsername(rs.getString("username"));
				usersVO.setPassword(rs.getString("password"));
				usersVO.setSex(rs.getString("sex"));
				usersVO.setHeadimg(rs.getString("headimg"));
				usersVO.setRestime(rs.getString("regtime"));
				
				
			}
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBConnection.getInstance().close(null, ps, null, conn);
		}
		return usersVO;
		
	}
	
	public ArrayList<UsersVO> queryAllUser(Connection conn)
	{
		ArrayList<UsersVO> list = new ArrayList<UsersVO>();

		String sql = "select * from users";
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rs = (ResultSet)ps.executeQuery();
			while(rs.next())
			{
				UsersVO usersVO = UsersVO.getInstance();
				usersVO.setUid(rs.getInt("uid"));
				usersVO.setUsername(rs.getString("username"));
				usersVO.setPassword(rs.getString("password"));
				usersVO.setSex(rs.getString("sex"));
				usersVO.setHeadimg(rs.getString("headimg"));
				usersVO.setRestime(rs.getString("regtime"));
				list.add(usersVO);
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBConnection.getInstance().close(null, ps, null, conn);
		}
		return list;
		
	}
	
	
	public UsersVO queryById(Connection conn,int uid)
	{
		UsersVO usersVO = null;

		String sql = "select * from users where uid=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,uid);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				usersVO = UsersVO.getInstance();
				usersVO.setUid(rs.getInt("uid"));
				usersVO.setUsername(rs.getString("username"));
				usersVO.setPassword(rs.getString("password"));
				usersVO.setSex(rs.getString("sex"));
				usersVO.setHeadimg(rs.getString("headimg"));
				usersVO.setRestime(rs.getString("regtime"));
				
			}
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			DBConnection.getInstance().close(null, ps, null, conn);
		}
		return usersVO;
		
	}
}