How to programmatically create a Jira issue link in Confluence that is mentioned in Jira?

[Johan Jonsson Nilsson]

Hi!

I have created some code that programmatically creates a Confluence page, with a formatted body from a Velocity template. This is working as expected and renders the page successfully. However, when you create a Jira Issue Macro in Confluence "normally" through the web interface, it is automatically added as a mentioned page in Jira? However, this is not happening when I create the page automatically.

Also, if I try to delete the macro and re-add it to the programmatically created page, it still doesn't work. This makes me suspect that something is wrong with the page creation. Have anyone encountered this issue before? Please see the code below:

Java Function that creates the Jira Macro:

public boolean createXrayReportInConfluence(String spaceKey, String pageTitle, String parentPageTitle, List<String> listOfJiraIssues) {
try {
ApplicationLink jiraApplicationLink = applicationLinkService.getPrimaryApplicationLink(JiraApplicationType.class);

Page page = new Page();
Page parentPage;

//if spaceParentPage equals "None", then set parentPage as the home page of the space
if (parentPageTitle.equals("None")) {
parentPage = spaceManager.getSpace(spaceKey).getHomePage();
} else {
parentPage = pageManager.getPage(spaceKey, parentPageTitle);
}

page.setSpace(spaceManager.getSpace(spaceKey));
page.setTitle(pageTitle);

Map context = MacroUtils.defaultVelocityContext();
context.put("jiraissues", listOfJiraIssues);
context.put("JiraPrimaryApplicationName", jiraApplicationLink.getName());
context.put("JiraPrimaryApplicationID", jiraApplicationLink.getId().toString());
String result = VelocityUtils.getRenderedTemplate("template/xraytestwizard.vm", context);

page.setBodyAsString(result.trim().replaceAll(" +", " "));
page.setVersion(1);
page.setParentPage(parentPage);

//Save the page
pageManager.saveContentEntity(page, new DefaultSaveContext.Builder().build());

//Add the newly created page as a child to the parentPage specified above 
parentPage.addChild(page);

//Work-around in order to make the page appear in the space instantly
confluenceIndexer.reIndex(page);

return true;

} catch (Exception ex) {
log.warn("Caught Exception!");
log.warn(ex.getMessage());
log.warn(ex.toString());
return false;
}
}

The Velocity template:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<h3>
<span style="color: rgb(0,0,128);">Requirements (Stories) tested in this report</span>
</h3>
<ol>
#foreach ($jiraIssueKey in $jiraissues)
<li>
<span style="color: rgb(0,0,128);">
<ac:structured-macro ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="columns">key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
<ac:parameter ac:name="key">$jiraIssueKey</ac:parameter>
</ac:structured-macro>
</span>
</li>
#end
</ol>
<h3>
<span style="color: rgb(0,0,128);">Open Defects</span>
</h3>
<p>
<ac:structured-macro ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="columns">key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution</ac:parameter>
<ac:parameter ac:name="maximumIssues">20</ac:parameter>
<ac:parameter ac:name="jqlQuery">(
#foreach( $jiraIssueKey in $jiraissues ) 
issue in defectsCreatedForRequirement("$jiraIssueKey") #if( $velocityHasNext ) OR #end 
#end 
) ORDER BY status ASC, priority ASC 
</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
</ac:structured-macro>
</p>
<h3>
<span style="color: rgb(0,0,128);">Test Summary</span>
</h3>
<table class="wrapped">
<colgroup>
<col/>
<col/>
</colgroup>
<tbody>
<tr>
<th colspan="1">Metrics</th>
<th colspan="1">Current value</th>
</tr>
<tr>
<td>Total no. of Test Cases</td>
<td>
<div class="content-wrapper">
<p>
<ac:structured-macro ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="jqlQuery">issuetype = 'Test' AND ( 
#foreach( $jiraIssueKey in $jiraissues ) 
key in requirementTests("$jiraIssueKey") #if( $velocityHasNext ) OR #end 
#end 
) 
</ac:parameter>
<ac:parameter ac:name="count">true</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
</tr>
<tr>
<td colspan="1">
<div class="content-wrapper">
<p>No. of <ac:structured-macro ac:name="status" ac:schema-version="1">
<ac:parameter ac:name="colour">Red</ac:parameter>
<ac:parameter ac:name="title">Fail</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
<td colspan="1">
<div class="content-wrapper">
<p>
<ac:structured-macro ac:macro-id="2e1db40d-6c34-4857-bfbb-9077107fed7f" ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="jqlQuery">issuetype = 'Test' AND ( 
#foreach( $jiraIssueKey in $jiraissues ) 
key in requirementTests("$jiraIssueKey") #if( $velocityHasNext ) OR #end 
#end 
) AND TestRunStatus = Fail </ac:parameter>
<ac:parameter ac:name="count">true</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
</tr>
<tr>
<td>
<div class="content-wrapper">
<p>No. of <ac:structured-macro ac:macro-id="64969a82-a16e-41a6-a9d4-62f16ee0a7a6" ac:name="status" ac:schema-version="1">
<ac:parameter ac:name="colour">Green</ac:parameter>
<ac:parameter ac:name="title">Pass</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
<td>
<div class="content-wrapper">
<p>
<ac:structured-macro ac:macro-id="a60d6eff-1484-4cd7-826e-722599bf587d" ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="jqlQuery">issuetype = 'Test' AND ( 
#foreach( $jiraIssueKey in $jiraissues ) 
key in requirementTests("$jiraIssueKey") #if( $velocityHasNext ) OR #end 
#end 
) AND TestRunStatus = Pass </ac:parameter>
<ac:parameter ac:name="count">true</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
</tr>
<tr>
<td colspan="1">
<div class="content-wrapper">
<p>No. of <ac:structured-macro ac:macro-id="a9c6d66b-031b-4cbe-871f-e5843a743c84" ac:name="status" ac:schema-version="1">
<ac:parameter ac:name="colour">Yellow</ac:parameter>
<ac:parameter ac:name="title">Executing</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
<td colspan="1">
<div class="content-wrapper">
<p>
<ac:structured-macro ac:macro-id="1e9a3aa6-ecae-42a9-9b4b-ac9ec78a8c8d" ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="jqlQuery">issuetype = 'Test' AND ( 
#foreach( $jiraIssueKey in $jiraissues ) 
key in requirementTests("$jiraIssueKey") #if( $velocityHasNext ) OR #end 
#end 
) AND TestRunStatus = Executing </ac:parameter>
<ac:parameter ac:name="count">true</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
</tr>
<tr>
<td>
<div class="content-wrapper">
<p>No. of <ac:structured-macro ac:macro-id="e148d1c5-b932-4599-908f-cbc1dc4ba8d5" ac:name="status" ac:schema-version="1">
<ac:parameter ac:name="subtle">true</ac:parameter>
<ac:parameter ac:name="title">Todo</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
<td>
<div class="content-wrapper">
<p>
<ac:structured-macro ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="jqlQuery">issuetype = 'Test' AND ( 
#foreach( $jiraIssueKey in $jiraissues ) 
key in requirementTests("$jiraIssueKey") #if( $velocityHasNext ) OR #end 
#end 
) AND TestRunStatus = Todo </ac:parameter>
<ac:parameter ac:name="count">true</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
</tr>
<tr>
<td colspan="1">
<div class="content-wrapper">
<p>No. of <ac:structured-macro ac:name="status" ac:schema-version="1">
<ac:parameter ac:name="title">Aborted</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
<td colspan="1">
<div class="content-wrapper">
<p>
<ac:structured-macro ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="jqlQuery">issuetype = 'Test' AND ( 
#foreach( $jiraIssueKey in $jiraissues ) 
key in requirementTests("$jiraIssueKey") #if( $velocityHasNext ) OR #end 
#end 
) AND TestRunStatus = Aborted </ac:parameter>
<ac:parameter ac:name="count">true</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
</tr>
<tr>
<td colspan="1">
<div class="content-wrapper">
<p>No. of <ac:structured-macro ac:name="status" ac:schema-version="1">
<ac:parameter ac:name="colour">Red</ac:parameter>
<ac:parameter ac:name="title">Blocked</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
<td colspan="1">
<div class="content-wrapper">
<p>
<ac:structured-macro ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="jqlQuery">issuetype = 'Test' AND ( 
#foreach( $jiraIssueKey in $jiraissues ) 
key in requirementTests("$jiraIssueKey") #if( $velocityHasNext ) OR #end 
#end 
) AND TestRunStatus = Blocked </ac:parameter>
<ac:parameter ac:name="count">true</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
</tr>
<tr>
<td colspan="1">
<div class="content-wrapper">
<p>No. of <ac:structured-macro ac:name="status" ac:schema-version="1">
<ac:parameter ac:name="colour">Blue</ac:parameter>
<ac:parameter ac:name="title">Excluded</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
<td colspan="1">
<div class="content-wrapper">
<p>
<ac:structured-macro ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="jqlQuery">issuetype = 'Test' AND ( 
#foreach( $jiraIssueKey in $jiraissues ) 
key in requirementTests("$jiraIssueKey") #if( $velocityHasNext ) OR #end 
#end 
) AND TestRunStatus = Excluded </ac:parameter>
<ac:parameter ac:name="count">true</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
</ac:structured-macro>
</p>
</div>
</td>
</tr>
</tbody>
</table>
#foreach ($jiraIssueKey in $jiraissues)
<h3>
<span style="color: rgb(0,0,128);">
<strong>Test Execution Status for </strong>
</span>
<ac:structured-macro ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="columns">key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution</ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
<ac:parameter ac:name="key">$jiraIssueKey</ac:parameter>
</ac:structured-macro>
</h3>
<p>
<ac:structured-macro ac:name="jira" ac:schema-version="1">
<ac:parameter ac:name="server">$JiraPrimaryApplicationName</ac:parameter>
<ac:parameter ac:name="columns">key,summary,assignee,priority,testrunstatus,fixversions</ac:parameter>
<ac:parameter ac:name="maximumIssues">100</ac:parameter>
<ac:parameter ac:name="jqlQuery">issuetype = 'Test' and key in requirementTests('$jiraIssueKey') ORDER BY priority DESC </ac:parameter>
<ac:parameter ac:name="serverId">$JiraPrimaryApplicationID</ac:parameter>
</ac:structured-macro>
</p>
#end

[Aleksandr Zuevich · Accepted solution]

Hi @Johan Jonsson Nilsson ,

It was really interesting question. :)

I found that during Jira issue viewing /rest/viewIssue/1/remoteIssueLink/render/{id} is invoked which results in 500 HTTP Internal Server Error. At the same time there is next ERROR in confluence log:

java.lang.NullPointerException
at com.google.common.base.Preconditions.checkNotNull(Preconditions.java:877)
at com.atlassian.confluence.api.model.content.History$HistoryBuilder.createdDate(History.java:231)

which points us to the page create date absence.

So the only thing you need to do is to set creation date:

page.setCreationDate(new Date()); 

[Johan Jonsson Nilsson]

Hi!

That seemed to help quite a bit, however now I get this error:

2019-12-19 10:55:20,511 WARN [Jira remote link executor:thread-54038] [plugins.jira.links.JiraRemoteLinkManager] lambda$executeRemoteLinkRequest$0 Failed to create a remote link to ISSUE-997 in Jira. Reason: 401 -

What can I do to fix this?

[Aleksandr Zuevich]

Have you seen https://confluence.atlassian.com/confkb/unable-to-link-jira-issues-to-confluence-pages-due-to-lack-of-permission-317196293.html ?

[Johan Jonsson Nilsson]

Hi!

Yes, I figured that it could be anything due to that. However, it seems that the user has the necessary permissions and I have also double checked so that it has approved access between Jira and Confluence.

However, I noticed this now:

 -- url: /confluence/rest/tss/testwizard/latest/resources/createxrayreport | traceId: 88e5d023782b8eb0 | userName: anonymous
2019-12-19 13:08:00,580 WARN [Jira remote link executor:thread-54310] [plugins.jira.links.JiraRemoteLinkManager] lambda$executeRemoteLinkRequest$0 Failed to create a remote link to ISSUE-595 in Jira. Reason: 401 -

Could it be that I need to do the request as that user, otherwise it will fail? If that is the case, how do I do that? 

[Aleksandr Zuevich]

First try to specify page creator:

page.setCreator(yourConfluenceUser);

If the first doesn't work, try to use AuthenticatedUserImpersonator:

AuthenticatedUserImpersonator.REQUEST_AGNOSTIC.asUser(() -> createXrayReportInConfluence()), yourUser);

[Johan Jonsson Nilsson]

The first one didn't work, but I will try the second one! 

I'll get back to you!

[Johan Jonsson Nilsson]

Thanks a lot! Now it works perfectly! :)

I noticed this in the log whenever I create a page, anything I should be alarmed about? The page creation seems to work fine..

2019-12-19 14:32:01,547 ERROR [com.atlassian.confluence.notifications.impl.DefaultDispatchService:thread-5] [persistence.dao.hibernate.HibernateContentPermissionSetDao] logAncestorsTableFailure Detected ancestors table corruption for pageId: 126182143. Access to this page is blocked for all users as inherited permissions cannot be determined. To resolve this, rebuild the ancestors table. See https://confluence.atlassian.com/display/DOC/Rebuilding+the+Ancestor+Table

[Aleksandr Zuevich]

Is 126182143 the id for your just created page?

Set the logging level of com.atlassian.confluence.pages.ancestors to INFO and run Repair the Ancestors Table job at  General Configuration > Scheduled Jobs.

Let's see the result of the job.

[Johan Jonsson Nilsson]

Yes, that is correct. I just ran the job and it said: 

2019-12-19 15:10:53,692 INFO [Caesium-1-3] [confluence.pages.ancestors.AncestorsRepairer] repairAncestors Ancestors have been repaired. Found and fixed 0 broken pages. It took 7 sec for 961 spaces, average space processing time 0 sec.

[Aleksandr Zuevich]

Unfortunately I can't tell exactly what's wrong here. It's seems that it's related to parent/child pages relationship. When is parent page created/last modified?

[Johan Jonsson Nilsson]

The parent page is created just before the actual page is created, in case it doesn't exist. However, the error seems to still happen when I create a new page with the parent page already created.

However, I searched on this issue and found this: https://community.atlassian.com/t5/Confluence-questions/logAncestorsTableFailure-after-create-spaces-and-pages-in/qaq-p/756363

By adding the following function right before saving the page, the error disappeared.

pageManager.updatePageInAncestorCollections(page, parentPage);

 Therefore, the final code looks like this:

public boolean createXrayReportInConfluence(String spaceKey, String pageTitle, String parentPageTitle, List<String> listOfJiraIssues) {
try {
ApplicationLink jiraApplicationLink = applicationLinkService.getPrimaryApplicationLink(JiraApplicationType.class);

Page page = new Page();
Page parentPage;

//if spaceParentPage equals "None", then set parentPage as the home page of the space
if (parentPageTitle.equals("None")) {
parentPage = spaceManager.getSpace(spaceKey).getHomePage();
} else {
parentPage = pageManager.getPage(spaceKey, parentPageTitle);
}

ConfluenceUser user = null;

if (userAccessor.exists("Support")) user = userAccessor.getUserByName("Support");

Date dateToUse = new Date();

page.setCreator(user);
page.setLastModifier(user);
page.setSpace(spaceManager.getSpace(spaceKey));
page.setTitle(pageTitle);
page.setCreationDate(dateToUse);
page.setLastModificationDate(dateToUse);

Map context = MacroUtils.defaultVelocityContext();
context.put("jiraissues", listOfJiraIssues);
context.put("JiraPrimaryApplicationName", jiraApplicationLink.getName());
context.put("JiraPrimaryApplicationID", jiraApplicationLink.getId().toString());
String result = VelocityUtils.getRenderedTemplate("template/xraytestwizard.vm", context);

page.setBodyAsString(result.trim().replaceAll(" +", " "));
page.setVersion(1);
page.setParentPage(parentPage);

pageManager.updatePageInAncestorCollections(page, parentPage);

//Save the page
pageManager.saveContentEntity(page, new DefaultSaveContext.Builder().build());

//Add the newly created page as a child to the parentPage specified above 
parentPage.addChild(page);

//Work-around in order to make the page appear in the space instantly
confluenceIndexer.reIndex(page);

return true;

} catch (Exception ex) {
log.warn("Caught Exception!");
log.warn(ex.getMessage());
log.warn(ex.toString());
return false;
}
}

Thank you so much @Aleksandr Zuevich for the help, really appreciate it! :)

Best regards,
Johan

[Aleksandr Zuevich]

Great! Thanks for sharing the final solution with us!