mssqluserdao.java

来自「一个用struts tiles的在线影院web系统」· Java 代码 · 共 226 行

package com.eline.vod.security.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import com.blue.web.common.exceptions.DAOSysException;
import com.blue.web.common.model.Page;
import com.blue.web.common.util.StringUtils;
import com.eline.vod.security.model.CreateUserStatus;
import com.eline.vod.security.model.User;

public class MSSqlUserDAO extends DBSqlAuthBase implements UserDAO {

	public boolean validateUserExists(User user) throws DAOSysException, SQLException {
		return null != getUser(0, user.getUserName(), false, "");
	}
	public int createUser(User user) throws DAOSysException, SQLException {
		if (validateUserExists(user))
			return CreateUserStatus.DuplicateUsername;
		System.out.println("createUser().STEP:1");

		if (user.isAnonymous())
			return CreateUserStatus.UnknownFailure;
		System.out.println("createUser().STEP:2");

		Connection conn = null;
		CallableStatement stmt = null;
		System.out.println("createUser().STEP:3");

		String sql = "{ ? = call dbo.users_CreateUser(?, ?, ?, ?, ?) }";
		try {
			conn = super.getDBConnection();
			stmt = conn.prepareCall(sql);

			stmt.registerOutParameter(1, Types.INTEGER);	// The output parameter.
			int index = 2;
			stmt.registerOutParameter(index++, Types.INTEGER);
			stmt.setString(index++, user.getUserName());
			stmt.setString(index++, user.getPassword());
			stmt.setInt(index++, user.getAccountStatus());
			stmt.setString(index++, user.getMobilePIN());

			// stmt.executeUpdate();
			stmt.execute();
			System.out.println("createUser().STEP:4");

			int returnCode =stmt.getInt(1);
			System.out.println("MSSqlUserDAO.createUser().returnCode=" + returnCode);
			if (returnCode == CreateUserStatus.Created) {
				user.setUserId(stmt.getInt(2));
				System.out.println("createUser().userId=" + user.getUserId());
			}
			System.out.println("return=" + returnCode);
			return returnCode;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOSysException(e.getMessage());
		} finally {
			stmt.close();
			conn.close();
		}
	}

	public User getAnonymousUser() throws DAOSysException, SQLException {
		Connection conn = null;
		CallableStatement stmt = null;
		String sql = "{ call dbo.users_GetAnonymousUserID(?) }";

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareCall(sql);
			stmt.registerOutParameter(1, Types.INTEGER);	// The output parameter.
			boolean status = stmt.execute();
			int userId = stmt.getInt(1);
			System.out.println("getAnonymousUser().userId=" + userId);
			System.out.println("status=" + status);

			User user = getUser(userId, "Anonymous", true, "");
			System.out.println("MSSqlUserDAO.getAnonymousUser().user=" + user);
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOSysException(e.getMessage());
		} finally {
			stmt.close();
			conn.close();
		}
	}

	public User getUser(int userID, String userName, boolean isOnline,
			String lastAction) throws DAOSysException, SQLException {
		User user = null;
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rst = null;
		String sql = "{ call dbo.users_GetUser(?, ?, ?, ?) }";

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareCall(sql);
			stmt.setInt(1, userID);
			stmt.setString(2, userName);
			stmt.setBoolean(3, isOnline);
			stmt.setString(4, lastAction);

			rst = stmt.executeQuery();
			if (rst.next()) {
				user = new User();
				user.setUserId(rst.getInt("UserID"));
				user.setUserName(rst.getString("UserName"));
				user.setPassword(rst.getString("Password"));
				user.setEmail(rst.getString("Email"));
				user.setAnonymous(rst.getBoolean("IsAnonymous"));
				user.setDateCreated(StringUtils.getDate(rst.getString("DateCreated")));
				user.setDateLastActive(StringUtils.getDate(rst.getString("DateLastActivity")));
				user.setAccountStatus(rst.getInt("UserAccountStatus"));
			}
		} catch (SQLException e) {
			// e.printStackTrace();
			throw new DAOSysException(e.getMessage());
		} finally {
			rst.close();
			stmt.close();
			conn.close();
		}
		System.out.println("user=" + user);
		return user;
	}

	public Page getUsers(int pageIndex, int pageSize, int sortUsersBy,
			int sortOrder, String usernameFilter, boolean includeEmailInFilter,
			int accountStatus) throws DAOSysException, SQLException {
		Page page = new Page();
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rst = null;
		String sql = "{ call dbo.users_GetUsers(?, ?, ?, ?, ?, ?, ?) }";

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareCall(sql);
			int index = 1;
			stmt.setInt(index++, pageIndex);
			stmt.setInt(index++, pageSize);
			stmt.setInt(index++, sortUsersBy);
			stmt.setInt(index++, sortOrder);
			stmt.setString(index++, usernameFilter);
			stmt.setBoolean(index++, includeEmailInFilter);
			stmt.setInt(index++, accountStatus);

			//rst = stmt.executeQuery();
			stmt.execute();
			// Populate the collection of users
			rst = stmt.getResultSet();
			while (rst.next()) {
				User user = new User();
				user.setUserId(rst.getInt("UserID"));
				user.setUserName(rst.getString("UserName"));
				user.setPassword(rst.getString("Password"));
				user.setEmail(rst.getString("Email"));
				user.setAnonymous(rst.getBoolean("IsAnonymous"));
				user.setDateCreated(StringUtils.getDate(rst.getString("DateCreated")));
				user.setDateLastActive(StringUtils.getDate(rst.getString("DateLastActivity")));
				user.setAccountStatus(rst.getInt("UserAccountStatus"));
				page.getItems().add(user);
			}
			// Are we expecting the total records?
			if (stmt.getMoreResults()) {
				stmt.getResultSet();
				if (rst.next()) {
					rst.getInt(1);
					page.setTotalRecords(rst.getInt(1));
				}
			}

		} catch (SQLException e) {
			throw new DAOSysException(e.getMessage());
		} finally {
			rst.close();
			stmt.close();
			conn.close();
		}
		return page;
	}
	public User getUserByMobilePIN(String mobilePIN) throws DAOSysException, SQLException {
		User user = null;
		Connection conn = null;
		CallableStatement stmt = null;
		ResultSet rst = null;
		String sql = "{ call dbo.users_GetUserByMobilePIN(?) }";

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareCall(sql);
			stmt.setString(1, mobilePIN);

			rst = stmt.executeQuery();
			if (rst.next()) {
				user = new User();
				user.setUserId(rst.getInt("UserID"));
				user.setUserName(rst.getString("UserName"));
				user.setPassword(rst.getString("Password"));
				user.setEmail(rst.getString("Email"));
				user.setAnonymous(rst.getBoolean("IsAnonymous"));
				user.setDateCreated(StringUtils.getDate(rst.getString("DateCreated")));
				user.setDateLastActive(StringUtils.getDate(rst.getString("DateLastActivity")));
				user.setAccountStatus(rst.getInt("UserAccountStatus"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DAOSysException(e.getMessage());
		} finally {
			rst.close();
			stmt.close();
			conn.close();
		}
		System.out.println("user=" + user);
		return user;
	}

}