userdao.java

系统分为用户模块和管理员模块两大部分。 普通用户只能查看、搜索和购买图书。搜索有按书籍的类别、书籍名称、出版社名称、上架时间等条件的查询。购买方式有选择邮递和特快等方式。购物车的设计有记录某用户购买

/**
 * 
 */
package com.jstu.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import bookshop.base.BaseDTO;
import bookshop.tools.exception.MyException;

import com.jstu.dto.UserDTO;
import com.jstu.util.DBUtil;

/**
 * @描述: 数据访问对象，把数据持久化到数据库中，调用JDBC API。
 * @单位: 江苏技术师范学院
 * @邮箱: [e-mail]
 * @作者: 林群群
 * 
 */
public class UserDAO {

	Connection conn = DBUtil.getConnection();

	PreparedStatement ps = null;

	ResultSet rs = null;

	List al=new ArrayList();
	
	public BaseDTO login(UserDTO userDTO) throws MyException {

		try {
			String sql="select * from user where loginName='"
				+ userDTO.getLoginName() + "' and  password='"
				+ userDTO.getPassword()+"'";
			
			System.out.print(sql);
			
			ps = conn.prepareStatement(sql);		
			rs = ps.executeQuery();
			if(!rs.next()){
				throw new MyException("用户名或密码错误");
			}
		} catch (SQLException e) {

			e.printStackTrace();
			throw new MyException("数据库验证用户时失败");
		} catch (MyException e) {

			e.printStackTrace();
			throw new MyException("数据库验证用户时失败");
		}
		return userDTO;
	}

	/**
	 * 从数据库中顺序遍历所有的用户
	 */
	public List query() throws MyException {
		UserDTO user = new UserDTO();
		try {

			ps = conn.prepareStatement("select * from user");
			rs = ps.executeQuery();

			while (rs.next()) {

				user.setId(rs.getInt(1));
				user.setLoginName(rs.getString(2));
				user.setPassword(rs.getString(3));
				user.setEmail(rs.getString(4));
				user.setUserName(rs.getString(5));
				user.setAddress(rs.getString(6));
				user.setPost(rs.getInt(7));			
				user.setAddressKind(rs.getString(8));		
				user.setTelephone(rs.getString(9));
				user.setMobilephone(rs.getInt(10));
				user.setSex(rs.getString(11));
				user.setMypay(rs.getDouble(12));

				al.add(user);
			}
		} catch (SQLException e) {
			throw new MyException("查询用户数据库时失败");
		}
		DBUtil.close();
		return al;
	}

	/**
	 * 增加用户
	 */
	public void add(BaseDTO baseDTO) throws MyException {
		UserDTO user = (UserDTO) baseDTO;

		String sql = "insert into user(loginName,password,Email) values(?,?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getLoginName());
			ps.setString(2, user.getPassword());
			ps.setString(3,user.getEmail());
			ps.executeUpdate();

		} catch (SQLException e) {
			throw new MyException("注册新用户时，数据库添加用户信息失败");
		}
		DBUtil.close();
	}

	/**
	 * 删除用户
	 */
	public void delete(BaseDTO baseDTO) throws MyException {
		UserDTO user = (UserDTO) baseDTO;

		String sql = "delete from user where id=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, user.getId());
			ps.executeUpdate();
		} catch (SQLException e) {
			throw new MyException("数据库删除该用户失败");
		}
		DBUtil.close();
	}

	/**
	 * 修改用户信息
	 */
	public void addInformation(BaseDTO baseDTO) throws MyException {
		UserDTO user = (UserDTO) baseDTO;
		String sql = "insert into user(Email,userName,address,post,addressKind,telephone,mobilephone,sex)"
				+ "values(?,?,?,?,?,?,?,?)";

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user.getEmail());
			ps.setString(2, user.getUserName());
			ps.setString(3, user.getAddress());
			ps.setInt(4, user.getPost());
			ps.setString(5, user.getAddressKind());
			ps.setString(6, user.getTelephone());
			ps.setInt(7, user.getMobilephone());
			ps.setString(8, user.getSex());

			ps.executeUpdate();
		} catch (SQLException e) {
			throw new MyException("填写订货人信息时，数据库添加信息失败！");
		}
		DBUtil.close();
	}

	/**
	 * 搜索相关用户信息
	 */
	public void search() {
		String sql = "select * from user where user_name";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBUtil.close();
	}
}