customeroperation.java

该系统实现了网上购物及书店管理

package db;

import java.sql.*;

import bean.*;

import java.util.ArrayList;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class CustomerOperation {
	// Connection对象
	private Connection connection = null;

	// ResultSet对象
	private ResultSet resultSet = null;

	private java.sql.PreparedStatement preparedStatement = null;

	private boolean flag = false;

	private java.sql.CallableStatement callableStatement = null;

	private int pagecount = 0;

	private int pagedata = 0;

	/**
	 * 构造函数，构造与数据库的连接信息
	 */
	public CustomerOperation() {
		try {
			// 加载SQL Server的数据库驱动程序类
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			// 创建Connection对象
			connection = DriverManager
					.getConnection(
							"jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=MyShop",
							"sa", "");
		} catch (SQLException ex) {

		} catch (ClassNotFoundException ex) {

		}

	}

	/**
	 * 验证登陆用户的合法性
	 * 
	 * @param userName
	 * @param userPwd
	 * @return
	 */
	public UserBean checkUsersLogin(String userName, String userPwd) {
		UserBean useBean;
		flag = false;
		try {
			// 从users表中查询特定用户的SQL语句
			String sql = "select count(*) from users where userName=? and userPwd=?";
			// 创建PreparedStatement对象
			preparedStatement = connection.prepareStatement(sql);
			// 为PreparedStatement对象设置必要的参数
			preparedStatement.setString(1, userName);
			preparedStatement.setString(2, userPwd);
			// 执行数据库查询操作
			resultSet = preparedStatement.executeQuery();
			if (resultSet.next()) {
				// 判断查询结果是否为空
				if (resultSet.getInt(1) > 0) {
					useBean = this.getUser(userName);
				} else {
					useBean = null;
				}
			} else {
				useBean = null;
			}
		} catch (Exception e) {
			useBean = null;
			e.printStackTrace();
		}
		return useBean;
	}

	/**
	 * 根据用户名查询其全部的用户信息
	 * 
	 * @param userName
	 * @return
	 */
	public UserBean getUser(String userName) {
		UserBean useBean = new UserBean();
		// 查询特定用户信息的SQL语句
		String sql = "select * from users where userName=?";
		try {
			// 创建Statement对象
			preparedStatement = connection.prepareStatement(sql);
			// 设置Statement对象的相关参数
			preparedStatement.setString(1, userName);
			// 执行查询语句
			resultSet = preparedStatement.executeQuery();
			// 对查询到的结果集进行处理
			while (resultSet.next()) {
				useBean.setRealName(resultSet.getString("realName"));
				useBean.setUserAdds(resultSet.getString("UserAdds"));
				useBean.setUserAge(resultSet.getInt("UserAge"));
				useBean.setUserCard(resultSet.getString("Usercard"));
				useBean.setUserCity(resultSet.getInt("UserCity"));
				useBean.setUserCode(resultSet.getInt("UserCode"));
				useBean.setUserId(resultSet.getInt("userId"));
				useBean.setUserMail(resultSet.getString("UserMail"));
				useBean.setUserName(resultSet.getString("userName"));
				useBean.setUserPhone(resultSet.getString("UserPhone"));
				useBean.setUserPwd(resultSet.getString("UserPwd"));
				useBean.setUserSex(resultSet.getInt("UserSex"));
				useBean.setUserStrp(resultSet.getInt("UserStep"));
				useBean.setUserWork(resultSet.getString("UserWork"));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		return useBean;
	}

	/**
	 * 根据cityId信息查询城市名
	 * 
	 * @param cityId
	 * @return
	 */
	public String getCity(int cityId) {
		try {
			// 创建Statement对象
			preparedStatement = connection
					.prepareStatement("select Display from City where CityId=?");
			// 设置其对应的参数
			preparedStatement.setInt(1, cityId);
			// 执行查询
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getString("Display");
		} catch (SQLException ex) {

			return null;
		}

	}

	/**
	 * 根据stepId取得相应等级信息
	 * 
	 * @param stepId
	 * @return
	 */
	public String getStep(int stepId) {
		try {
			// 创建Statement对象
			preparedStatement = connection
					.prepareStatement("select Display from Step where StepId=?");
			// 设置其对应的参数
			preparedStatement.setInt(1, stepId);
			// 执行查询
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getString("Display");
		} catch (SQLException ex) {

			return null;
		}

	}

	public int getUserOrderCount(int userId) {
		// 查询某个用户定单数量的查询语句
		String sql = "select count(*) from OrderForm where userId=?";
		try {
			// 创建Statement对象
			preparedStatement = connection.prepareStatement(sql);
			// 设置其对应的参数
			preparedStatement.setInt(1, userId);
			// 执行查询
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt(1);
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return 0;
	}

	/**
	 * 根据userId取得其用户信息
	 * 
	 * @param userId
	 * @return
	 */
	public UserBean getUser(int userId) {
		UserBean useBean = new UserBean();
		// 查询特定用户信息的sql语句
		String sql = "select * from users where userId=?";
		try {
			// 创建Statement对象
			preparedStatement = connection.prepareStatement(sql);
			// 设置Statement对象相应的参数
			preparedStatement.setInt(1, userId);
			// 执行查询
			resultSet = preparedStatement.executeQuery();
			// 对查询结果的处理
			while (resultSet.next()) {
				useBean.setRealName(resultSet.getString("realName"));
				useBean.setUserAdds(resultSet.getString("UserAdds"));
				useBean.setUserAge(resultSet.getInt("UserAge"));
				useBean.setUserCard(resultSet.getString("Usercard"));
				useBean.setUserCity(resultSet.getInt("UserCity"));
				useBean.setUserCode(resultSet.getInt("UserCode"));
				useBean.setUserId(resultSet.getInt("userId"));
				useBean.setUserMail(resultSet.getString("UserMail"));
				useBean.setUserName(resultSet.getString("userName"));
				useBean.setUserPhone(resultSet.getString("UserPhone"));
				useBean.setUserPwd(resultSet.getString("UserPwd"));
				useBean.setUserSex(resultSet.getInt("UserSex"));
				useBean.setUserStrp(resultSet.getInt("UserStep"));
				useBean.setUserWork(resultSet.getString("UserWork"));
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		}

		return useBean;
	}

	/**
	 * 插入新用户操作
	 * 
	 * @param useBean
	 * @return
	 */
	public boolean insertUser(UserBean useBean) {
		boolean bool = false;
		// 判断该用户是否已经存在
		if (!this.hasUser(useBean.getUserName())) {

			bool = true;
			// 插入新用户的SQL语句
			String sql = "insert into users (userName,UserPwd,realName,UserSex,UserPhone,UserMail,UserCity,UserAdds,UserCode,UserWork,Usercard,UserAge) values(?,?,?,?,?,?,?,?,?,?,?,?)";
			try {
				// 创建Statement对象
				preparedStatement = connection.prepareStatement(sql);
				// 设置Statement对象的相应参数
				preparedStatement.setString(1, useBean.getUserName());
				preparedStatement.setString(2, useBean.getUserPwd());
				preparedStatement.setString(3, useBean.getRealName());
				preparedStatement.setInt(4, useBean.getUserSex());
				preparedStatement.setString(5, useBean.getUserPhone());
				preparedStatement.setString(6, useBean.getUserMail());
				preparedStatement.setInt(7, useBean.getUserCity());
				preparedStatement.setString(8, useBean.getUserAdds());
				preparedStatement.setInt(9, useBean.getUserCode());
				preparedStatement.setString(10, useBean.getUserWork());
				preparedStatement.setString(11, useBean.getUserCard());
				preparedStatement.setInt(12, useBean.getUserAge());
				// 执行更新操作
				int flag = preparedStatement.executeUpdate();
				if (flag == 0) {
					bool = false;
				}
			} catch (SQLException ex) {
				ex.printStackTrace();
				bool = false;
			}
		}
		return bool;
	}

	/**
	 * 更新特定用户信息
	 * 
	 * @param useBean
	 * @return
	 */
	public boolean updateUser(UserBean useBean) {
		boolean bool = false;

		bool = true;
		// 更新用户信息的sql语句
		String sql = "update users set userName=?,UserPwd=?,realName=?,UserSex=?,UserPhone=?,UserMail=?,UserCity=?,UserAdds=?,UserCode=?,UserWork=?,Usercard=?,UserAge=? where userId=?";
		try {
			// 创建Statement对象
			preparedStatement = connection.prepareStatement(sql);
			// 设置其相应参数
			preparedStatement.setString(1, useBean.getUserName());
			preparedStatement.setString(2, useBean.getUserPwd());
			preparedStatement.setString(3, useBean.getRealName());
			preparedStatement.setInt(4, useBean.getUserSex());
			preparedStatement.setString(5, useBean.getUserPhone());
			preparedStatement.setString(6, useBean.getUserMail());
			preparedStatement.setInt(7, useBean.getUserCity());