基于组织架构在Jira中筛选经办人列表的技术实现问询

需求背景 #

组织架构包含TeamA、TeamB、TeamC、TeamD四个团队，每个团队设负责人和成员，需实现以下经办人分配规则：

• 负责人：可分配任务给自己的团队成员 + 其他所有团队的负责人
• 团队成员：仅可分配任务给自己的团队负责人 + 同团队成员

当前问题：现有Script Runner自定义选择器代码仅能通过固定组（如B2B）筛选，无法实现上述组织架构规则。

现有代码 #

import com.atlassian.jira.avatar.Avatar;
import com.atlassian.jira.bc.user.search.UserSearchParams;
import com.atlassian.jira.bc.user.search.UserSearchService;
import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.user.ApplicationUser;
import com.atlassian.jira.user.UserFilter;
import com.onresolve.scriptrunner.canned.jira.fields.model.PickerOption;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import groovy.sql.Sql;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
import com.atlassian.core.util.ClassLoaderUtils;
import com.atlassian.jira.issue.CustomFieldManager;
import com.atlassian.jira.issue.Issue;
import com.atlassian.jira.issue.fields.CustomField;
import com.atlassian.jira.event.type.EventDispatchOption

//Query connection
def driver = "org.postgresql.Driver";
def url = "jdbc:postgresql://ip:5432/namedb";
def username = "sample";
def password = "sample";
Class dbClass = ClassLoaderUtils.loadClass(driver, this.class);
Driver driverInstance = (Driver) dbClass.newInstance();
DriverManager.registerDriver(driverInstance);
Properties info = new Properties();
info.put("user", username);
info.put("password", password);
Connection connection = driverInstance.connect(url, info);
def sql = new Sql(connection)
def String m_assignee = '';
def String tgm_assignee = '';

//get username login
def userinfousername = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser().getUsername()

//define paramters for get user object
def issueManager = ComponentAccessor.getIssueManager()
def issueService = ComponentAccessor.getIssueService()

//sql query 
def sqlstr = "select managername,generalid  from mcigeneralname l where l.generalid in (select   generalid  from mcigeneralname where jiraaccount="+userinfousername+')'
 
def userSearchService = ComponentAccessor.getComponent(UserSearchService)
def userManager = ComponentAccessor.userManager
def avatarService = ComponentAccessor.avatarService
def authenticationContext = ComponentAccessor.jiraAuthenticationContext
def groupManager = ComponentAccessor.groupManager
 
def userFilter = new UserFilter(true, null, ['B2B', 'B2B'])
def userSearchParams = new UserSearchParams.Builder().allowEmptyQuery(true).filter(userFilter).maxResults(30).build()

search = { String inputValue ->
    userSearchService.findUsers(inputValue, userSearchParams)
}
 
/*
 * Retrieve the user from internal storage (we store the key). Return them if they still exist, regardless of
 * their group membership.
 */
getItemFromId = { String id ->
    userManager.getUserByKey(id)
}
 
/*
 * The user is still "valid" if they are a member of either of the below-mentioned groups, otherwise they become "disabled".
 */
validate = { ApplicationUser user ->
    groupManager.getGroupNamesForUser(user).intersect(['B2B', 'B2B'])
}
 
renderItemViewHtml = { ApplicationUser user ->
    user.displayName
}
 
renderItemTextOnlyValue = renderItemViewHtml
 
toOption = { ApplicationUser user, Closure<String> highlight ->
    def remoteUser = authenticationContext.getLoggedInUser()
 
    new PickerOption(
        value: user.key,
        label: user.displayName,
        html: highlight(user.displayName, false),
        icon: avatarService.getAvatarURL(remoteUser, user, Avatar.Size.SMALL)?.toString()
    )
}

解决方案 #

核心思路 #

1. 从数据库获取当前登录用户的团队ID和角色（负责人/成员）
2. 根据角色生成符合规则的允许用户集合
3. 修改筛选逻辑，仅返回允许集合内的用户
4. 完善资源释放，避免数据库连接泄漏

修改后的完整代码 #

import com.atlassian.jira.avatar.Avatar;
import com.atlassian.jira.bc.user.search.UserSearchParams;
import com.atlassian.jira.bc.user.search.UserSearchService;
import com.atlassian.jira.component.ComponentAccessor;
import com.atlassian.jira.user.ApplicationUser;
import com.atlassian.jira.user.UserFilter;
import com.onresolve.scriptrunner.canned.jira.fields.model.PickerOption;
import groovy.sql.Sql;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.util.Properties;
import com.atlassian.core.util.ClassLoaderUtils;
import java.util.HashSet;
import java.util.Set;

// 数据库连接配置
def driver = "org.postgresql.Driver";
def url = "jdbc:postgresql://ip:5432/namedb";
def username = "sample";
def password = "sample";
Connection connection = null;
Sql sql = null;

try {
    // 初始化数据库连接
    Class dbClass = ClassLoaderUtils.loadClass(driver, this.class);
    Driver driverInstance = (Driver) dbClass.newInstance();
    DriverManager.registerDriver(driverInstance);
    Properties info = new Properties();
    info.put("user", username);
    info.put("password", password);
    connection = driverInstance.connect(url, info);
    sql = new Sql(connection);

    // 获取当前登录用户的Jira账号
    def currentUser = ComponentAccessor.getJiraAuthenticationContext().getLoggedInUser();
    def currentUserAccount = currentUser.getUsername();

    // 1. 查询当前用户所属团队及角色（是否为负责人）
    def userTeamInfo = sql.firstRow("SELECT generalid, managername FROM mcigeneralname WHERE jiraaccount = ?", [currentUserAccount])
    if (!userTeamInfo) {
        // 若用户无团队信息，返回空列表
        search = { input -> [] }
        getItemFromId = { id -> null }
        validate = { user -> false }
        return
    }
    def userTeamId = userTeamInfo.generalid;
    def isManager = currentUserAccount.equals(userTeamInfo.managername);

    // 2. 根据角色生成允许的用户账号集合
    Set<String> allowedUserAccounts = new HashSet<>();
    if (isManager) {
        // 负责人：添加自己团队所有成员
        def teamMembers = sql.rows("SELECT jiraaccount FROM mcigeneralname WHERE generalid = ?", [userTeamId])
        teamMembers.each { allowedUserAccounts.add(it.jiraaccount) }
        // 添加其他所有团队的负责人
        def otherManagers = sql.rows("SELECT DISTINCT managername FROM mcigeneralname WHERE generalid != ? AND managername IS NOT NULL", [userTeamId])
        otherManagers.each { allowedUserAccounts.add(it.managername) }
    } else {
        // 团队成员：添加自己团队的负责人
        def teamManager = sql.firstRow("SELECT managername FROM mcigeneralname WHERE generalid = ?", [userTeamId])
        if (teamManager?.managername) {
            allowedUserAccounts.add(teamManager.managername)
        }
        // 添加自己团队所有成员
        def teamMembers = sql.rows("SELECT jiraaccount FROM mcigeneralname WHERE generalid = ?", [userTeamId])
        teamMembers.each { allowedUserAccounts.add(it.jiraaccount) }
    }
    // 添加当前用户自己（可选，确保用户能分配给自己）
    allowedUserAccounts.add(currentUserAccount);

    // 3. 初始化Jira服务组件
    def userSearchService = ComponentAccessor.getComponent(UserSearchService);
    def userManager = ComponentAccessor.userManager;
    def avatarService = ComponentAccessor.avatarService;
    def authenticationContext = ComponentAccessor.jiraAuthenticationContext;

    // 4. 定义搜索逻辑：只返回允许集合内的用户
    search = { String inputValue ->
        // 先全局搜索用户，再过滤到允许集合中
        def allUsers = userSearchService.findUsers(inputValue, new UserSearchParams.Builder().allowEmptyQuery(true).maxResults(100).build())
        allUsers.findAll { allowedUserAccounts.contains(it.getUsername()) }
    }

    // 5. 根据ID获取用户
    getItemFromId = { String id ->
        def user = userManager.getUserByKey(id)
        allowedUserAccounts.contains(user?.getUsername()) ? user : null
    }

    // 6. 验证用户是否在允许列表中
    validate = { ApplicationUser user ->
        allowedUserAccounts.contains(user.getUsername())
    }

    // 7. 渲染用户显示内容
    renderItemViewHtml = { ApplicationUser user ->
        user.displayName
    }
    renderItemTextOnlyValue = renderItemViewHtml

    // 8. 转换为选择器选项
    toOption = { ApplicationUser user, Closure<String> highlight ->
        def remoteUser = authenticationContext.getLoggedInUser()
        new PickerOption(
            value: user.key,
            label: user.displayName,
            html: highlight(user.displayName, false),
            icon: avatarService.getAvatarURL(remoteUser, user, Avatar.Size.SMALL)?.toString()
        )
    }

} finally {
    // 确保关闭数据库连接，避免资源泄漏
    if (sql != null) {
        sql.close()
    }
    if (connection != null) {
        connection.close()
    }
}

关键修改点说明 #

• 数据库查询优化：使用参数化查询避免SQL注入，同时准确获取用户的团队和角色信息
• 角色分支逻辑：明确区分负责人和成员的允许用户范围，严格匹配需求规则
• 用户过滤逻辑：将原固定组筛选改为动态允许集合筛选，确保只返回符合规则的用户
• 资源释放：添加try-finally块保证数据库连接关闭，避免Jira资源泄漏

注意事项 #

1. 确认数据库表mcigeneralname的字段定义：generalid为团队唯一标识，managername存储的是负责人的Jira账号，jiraaccount存储团队成员的Jira账号
2. 确保Script Runner插件拥有数据库访问权限，以及Jira用户管理的相关权限
3. 可根据实际需求调整maxResults参数，控制搜索返回的用户数量上限

内容的提问来源于stack exchange，提问作者MohammadReza moeini