userdao.java

在线购物 商品管理 用户管理 购物车 订单管理 权限控制

package com.qrsx.shop.dao;

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

import com.qrsx.shop.model.User;

/**
 *@Author:李世海	
 *@Address:青岛软件园
 *@Date: Mar 31, 2009
 */
public class UserDAO extends BaseDAO{
	/**
	 * 创建新用户信息
	 * @param user
	 * @throws SQLException
	 */
	public void create (User user) throws SQLException{
		String sql = "Insert Into user (name,password,sex,age,address,postcard,telephone) values (?,?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, user.getName());
		ps.setString(2, user.getPassword());
		ps.setString(3, user.getSex());
		Integer age = 0;
		if(user.getAge()!=null){
			age = Integer.valueOf(user.getAge());
		}
		ps.setInt(4, age);
		ps.setString(5, user.getAddress());
		ps.setString(6, user.getPostcard());
		ps.setString(7, user.getTelephone());
		
		ps.executeUpdate();
	}
	
	/**
	 * 注销删除用户信息
	 * @id
	 * @throws SQLException 
	 */
	public void delete(Integer id) throws SQLException{
		String sql = "Delete from user where id=?";
		ps = conn.prepareStatement(sql);
		//设置预备参数
		ps.setInt(1, id);
		ps.executeUpdate();
	}
	
	/**
	 * 修改用户信息
	 * @param id
	 * @throws SQLException 
	 */
	public void update(User user) throws SQLException{
		String sql = "update user set name=?,password=?,sex=?,age=?,address=?,postcard=?,telephone=? where id=?";
		ps = conn.prepareStatement(sql);
		//设置预备参数
		ps.setString(1, user.getName());
		ps.setString(2, user.getPassword());
		ps.setString(3, user.getSex());
		ps.setInt(4, Integer.valueOf(user.getAge()));
		ps.setString(5, user.getAddress());
		ps.setString(6, user.getPostcard());
		ps.setString(7, user.getTelephone());
		ps.setInt(8, user.getId());
		
		ps.executeUpdate();
		
	}
	/**
	 * 根据用户编号查询
	 * @throws SQLException 
	 */
	
	public User findById(Integer id) throws SQLException{
		String sql = "select * from user where id=?";
		
		ps=conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		User u = null;
		if(rs.next()){
			u = new User();
			u.setId(rs.getInt("id"));
			u.setName(rs.getString("name"));
			u.setPassword(rs.getString("password"));
			u.setSex(rs.getString("sex"));
			u.setAge(rs.getInt("age"));
			u.setAddress(rs.getString("address"));
			u.setPostcard(rs.getString("postcard"));
			u.setTelephone(rs.getString("telephone"));
		}
		return u;
	} 
	/**
	 * 管理员查询所有用户信息
	 * @throws SQLException 
	 */
	public List<User> findAll() throws SQLException{
		String sql = "Select * from user";
		ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		List<User> list = new ArrayList<User>();
		while(rs.next()){
			User user = new User();
			user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setSex(rs.getString("sex"));
			user.setAge(Integer.valueOf(rs.getInt("age")));
			user.setAddress(rs.getString("address"));
			user.setPostcard(rs.getString("postcard"));
			user.setTelephone(rs.getString("telephone"));
			list.add(user);
		}
		return list;
	}
	
	/**
	 * 动态检索用户信息
	 * @param user
	 * @throws SQLException 
	 */
	public List<User> list(User user) throws SQLException{
		String sql = "select * from user where 1=1";
		//根据用户编号来检索
		String name = user.getName();
		if(name != null && name.trim().length()>0){
			sql = sql + " and name like '%" + name + "%'";
		}
		//根据住址检索
		String address = user.getAddress();
		if(address != null && address.trim().length() > 0){
			sql = sql + " and address like '%" + address + "%'";
		}
		//根据电话检索
		String telephone = user.getTelephone();
		if(telephone != null && telephone.trim().length() > 0){
			sql = sql + " and telephone like '%" + telephone + "%'";
		}
		
		ps = conn.prepareStatement(sql); 
		ResultSet rs = ps.executeQuery();
		List<User> list = new ArrayList<User>();
		while(rs.next()){
			User u = new User();
			u.setId(rs.getInt("id"));
			u.setName(rs.getString("name"));
			u.setPassword(rs.getString("password"));
			u.setSex(rs.getString("sex"));
			u.setAge(rs.getInt("age"));
			u.setAddress(rs.getString("address"));
			u.setPostcard(rs.getString("postcard"));
			u.setTelephone(rs.getString("telephone"));
			list.add(u);
		}
		return list;
	}
	/**
	 * 用户登陆
	 * @throws SQLException 
	 */
	public  User login(User user) throws SQLException{
		String sql = "select * from user where name=? and password=?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, user.getName());
		ps.setString(2, user.getPassword());
		ResultSet rs = ps.executeQuery();
		User use = null;
		if(rs.next()){
			use = new User();
			use.setId(Integer.valueOf(rs.getInt("id")));
			use.setName(rs.getString("name"));
			use.setPassword(rs.getString("password"));
			use.setSex(rs.getString("sex"));
			use.setAge(rs.getInt("age"));
			use.setAddress(rs.getString("address"));
			use.setPostcard(rs.getString("postcard"));
			use.setTelephone(rs.getString("telephone"));
			
		}
		return use;
	}
	/**
	 * 根据用户名检索用户信息
	 * @throws SQLException 
	 */
	public User findByName(String name) throws SQLException{
		String sql = "select * from user where name=?";
		ps = conn.prepareStatement(sql);
		if(name!=null){
		ps.setString(1, name);
		}
		ResultSet rs = ps.executeQuery();
		User us = null;
		if(rs.next()){
			us =  new User();
			us.setId(Integer.valueOf(rs.getInt("id")));
			us.setName(rs.getString("name"));
			us.setPassword(rs.getString("password"));
			us.setSex(rs.getString("sex"));
			us.setAge(Integer.valueOf(rs.getInt("age")));
			us.setAddress(rs.getString("address"));
			us.setPostcard(rs.getString("postcard"));
			us.setTelephone(rs.getString("telephone"));
		}
		return us;
	}
	
	public User findByUserId( Integer id ) throws SQLException{
		String sql = "select * from user where id=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		User us = new User();
		if(rs.next()){
			us.setId(Integer.valueOf(rs.getInt("id")));
			us.setName(rs.getString("name"));
			us.setPassword(rs.getString("password"));
			us.setSex(rs.getString("sex"));
			us.setAge(Integer.valueOf(rs.getInt("age")));
			us.setAddress(rs.getString("address"));
			us.setPostcard(rs.getString("postcard"));
			us.setTelephone(rs.getString("telephone"));
		}
		return us;
	}
}