mssqlroledao.java

一个用struts tiles的在线影院web系统

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.User;

public class MSSqlRoleDAO extends DBSqlAuthBase implements RoleDAO {

	public void addUsersToRoles(String[] userNames, String[] roleNames)
			throws DAOSysException, SQLException {
		if (userNames.length < 1 || roleNames.length < 1)
			return;

		Connection conn = null;
		CallableStatement stmt = null;
		String sql = "{ ? = call dbo.roles_AddUserToRole(?, ?) }";

		try {
			// Open database connection.
			conn = super.getDBConnection();

			for (int i = 0; i < userNames.length; i ++) {
				for (int j = 0; j < roleNames.length; j ++) {
					// Creates a CallableStatement object for calling database
					// stored procedures.
					stmt = conn.prepareCall(sql);
					// Set the parameters
					stmt.registerOutParameter(1, Types.INTEGER);
					stmt.setString(2, userNames[i]);
					stmt.setString(3, roleNames[j]);
					
					stmt.executeUpdate();
					int retCode = stmt.getInt(1);
					if (retCode != 0)
						System.out.println("ERROR: add users to roles: user="
								+ userNames[i] + ",role=" + roleNames[j] + ",retCode=" + retCode);
				}
			}
		} catch (SQLException e) {
			throw new DAOSysException(e.getMessage());
		} finally {
			stmt.close();
			conn.close();
		}
	}

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

		try {
			conn = super.getDBConnection();
			stmt = conn.prepareCall(sql);
			int index = 1;
			stmt.setInt(index++, roleID);
			stmt.setInt(index++, pageIndex);
			stmt.setInt(index++, pageSize);
			stmt.setInt(index++, sortUsersBy);
			stmt.setInt(index++, sortOrder);
			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;
	}

}