usermanagedao.java

这是我们做的一个网上购物系统,包手用户和管理员两大功能模块,采用jsp+servlet+java bean技术.

package com.qyg.shop.user;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.qyg.database.DbOperatorHandle;
import com.qyg.tools.*;

public class UserManageDAO implements UserDAO {
	DbOperatorHandle doh=null;
	public UserManageDAO() {
		// TODO Auto-generated constructor stub
		doh=new DbOperatorHandle();
	}
	
	/**
	 * 添加用户
	 */
	public void add(User u) {
		// TODO Auto-generated method stub
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("insert user values(null,?,?,?,?,null,?,?)");
			ps.setString(1, u.getName());
			ps.setString(2, u.getPwd());
			ps.setString(3, u.getPhone());
			ps.setString(4, u.getAddr());
			ps.setString(5, u.getPower());
			ps.setDouble(6, u.getMoney());
			ps.execute();
			ps.close();
		}catch (Exception e) {			
			// TODO: handle exception
			System.out.print("添加用户错误");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
	}
	
	/*
	 *根据指定ID号添加用户
	 */
	public void addUser(User u) {
		// TODO Auto-generated method stub
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("insert user values(?,?,?,?,?,null,?,?)");
			ps.setInt(1, u.getId());
			ps.setString(2, u.getName());
			ps.setString(3, u.getPwd());
			ps.setString(4, u.getPhone());
			ps.setString(5, u.getAddr());
			ps.setString(6, u.getPower());
			ps.setDouble(7, u.getMoney());
			ps.execute();
			ps.close();
		}catch (Exception e) {			
			// TODO: handle exception
			System.out.println("添加用户错误");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
	}
	
	/**
	 * 根据id删除user数据
	 */
	public void delete(int id) {
		// TODO Auto-generated method stub
		try{
			Statement st=doh.getCon().createStatement();
			String sql="delete from user where id="+id;
			System.out.print(sql);
			st.executeUpdate(sql);
			st.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("根据id删除user数据wrong");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}

	}

	public User findUserByName(String name) {
		// TODO Auto-generated method stub
		User user=null;
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("select * from user where name=?");
			ps.setString(1, name);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				user=getUserByRs(rs);
			}
			rs.close();
			ps.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.print("根据用户名查用户错误");
		}
		finally{
			doh.closeCon();
		}
		return user;
	}
	
	/**
	 * 更新用户信息
	 */
	public void update(User user) {
		// TODO Auto-generated method stub
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("update user set name=?,pwd=?,phone=?," +
					"addr=?,power=?,money=? where id=?");
			ps.setString(1, user.getName());
			if(user.getPower().equals("admin")){
				ps.setString(2, new MD5().getMD5ofStr(user.getPwd()));
			}
			else{
				ps.setString(2, user.getPwd());
			}
			ps.setString(3, user.getPhone());
			ps.setString(4, user.getAddr());
			ps.setString(5, user.getPower());
			ps.setDouble(6, user.getMoney());
			ps.setInt(7, user.getId());
			ps.execute();
			ps.close();			
		}catch (Exception e) {
			// TODO: handle exception
			System.out.println("更新用户信息wrong");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
	}

	/**
	 * 把rs中的值返回到User中
	 */
	public User getUserByRs(ResultSet rs) {
		// TODO Auto-generated method stub
		User user=null;
		try{
			user=new User();
			user.setId(rs.getInt(1));
			user.setName(rs.getString(2));
			user.setPwd(rs.getString(3));
			user.setPhone(rs.getString(4));
			user.setAddr(rs.getString(5));
			user.setRdate(rs.getTimestamp(6));
			user.setPower(rs.getString(7));
			user.setMoney(rs.getDouble(8));
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.print("把rs中的值返回到User中");
		}
		return user;
	}

	/**
	 * 根据用户id给用户冲money元
	 */
	public void addMoneyById(int id, double money) {
		// TODO Auto-generated method stub
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("update user set money=money+? where id=?");
			ps.setDouble(1, money);
			ps.setInt(2, id);
			ps.executeUpdate();
			ps.close();			
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("根据用户id给用户冲money元");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
	}
	
	/**
	 * 得到用户总数
	 */
	public int getTotalUsers() {
		// TODO Auto-generated method stub
		int total=0;
		try{
			Statement st=doh.getCon().createStatement();
			String sql="select count(*) from user";
			ResultSet rs=st.executeQuery(sql);
			if(rs.next()){
				total=rs.getInt(1);
			}
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("得到用户总数wrong");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
		return total;
	}

	/**
	 * 得到所有用户
	 */
	public List<User> getAllUsers() {
		// TODO Auto-generated method stub
		List<User> users=null;
		try{
			users=new ArrayList<User>();
			Statement st=doh.getCon().createStatement();
			String sql="select * from user";
			ResultSet rs=st.executeQuery(sql);
			while(rs.next()){
				User user=this.getUserByRs(rs);
				users.add(user);
			}
			rs.close();
			st.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("得到所有用户");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
		return users;
	}
	
	/**
	 * 从RS中获取用户信息wrong
	 * @param rs用户数据集
	 * @return用户对象
	 */
	public User getUserFromRs(ResultSet rs){
		User user=null;
		try{
			user=new User();
			user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			user.setPwd(rs.getString("pwd"));
			user.setPhone(rs.getString("phone"));
			user.setAddr(rs.getString("addr"));
			user.setRdate(rs.getTimestamp("rdate"));
			user.setPower(rs.getString("power"));
			user.setMoney(rs.getDouble("money"));
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			System.out.println("从RS中获取用户信息wrong");
		}
		return user;
	}

	/**
	 * 根据name得到用户数
	 */
	public int getTotalUserLikeName(String name) {
		// TODO Auto-generated method stub
		int total=0;
		try{
			Statement st=doh.getCon().createStatement();
			String sql="select count(*) from user where name like '%"+name+"%'";
			ResultSet rs=st.executeQuery(sql);
			if(rs.next()){
				total=rs.getInt(1);
			}
			rs.close();
			st.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.print("根据name得到用户总数wrong");
			e.printStackTrace();
		}
		return total;
	}

	/**
	 * 根据name查询用户
	 */
	public List<User> getUsersLikeName(String name) {
		// TODO Auto-generated method stub
		List<User> users=null;
		try{
			users=new ArrayList<User>();
			Statement st=doh.getCon().createStatement();
			String sql="select * from user where name like '%"+name+"%'";
			ResultSet rs=st.executeQuery(sql);
			while(rs.next()){
				User user=this.getUserByRs(rs);
				users.add(user);
			}
			rs.close();
			st.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.print("根据name查询用户wrong");
			e.printStackTrace();
		}
		finally{
			doh.closeCon();
		}
		return users;
	}

	/**
	 * 根据Id获得user
	 */
	public User getUserById(int id) {
		// TODO Auto-generated method stub
		User user=null;
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("select * from user where id=?");
			ps.setInt(1, id);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				user=this.getUserByRs(rs);
			}
			rs.close();
			ps.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("根据Id获得user出错");
		}
		finally{
			doh.closeCon();
		}
		return user;
	}
	
	public ResultSet getUserRsById(int id) {
		// TODO Auto-generated method stub
		ResultSet rs = null;
		try{
			PreparedStatement ps=doh.getCon().prepareStatement("select * from user where id=?");
			ps.setInt(1, id);
			rs=ps.executeQuery();
			rs.close();
			ps.close();
		}
		catch (Exception e) {
			// TODO: handle exception
			System.out.println("根据Id获得user出错");
		}
		finally{
			doh.closeCon();
		}
		return rs;
	}
	
	public static void main(String[] args) {
		UserDAO dao = new UserManageDAO();
//		User u = new User();
//		u.setId(1236);
//		u.setName("qyg");
//		u.setPwd("nftdx");
//		u.setAddr("fdsfad");
//		u.setPower("admin");
//		dao.addUser(u);
//		System.out.println(dao.getUserById(140121));
//		System.out.println(new com.qyg.tools.MD5().getMD5ofStr("admin"));
//		ResultSet rs = dao.getUserRsById(177583);		
		System.out.println(dao.getUserById(2));
	}
	
}