It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Execute SQL in Plugin

I've built my own REST Plugin.

Now I need to execute a SQL query inside the plugin. How do I do that?

Thanks, Simon

4 answers

1 accepted

0 votes
Answer accepted

That highly depends on database engine, but as an example - see https://answers.atlassian.com/questions/12868/third-party-dependency-in-jira-plugin for sql server

Thanks.

You mean there is no way to get the database connection trough the JIRA API?

No, but you can use regular java methods.

Also please look into https://answers.atlassian.com/questions/154862/use-jira-connection-pool-for-plugin - but as for my experience - I've used external methods...

Good catch.

I think you need mostly

ComponentManager componentManager = ComponentManager.getInstance()
delegator = (DelegatorInterface) componentManager.getComponentInstanceOfType(DelegatorInterface.class)
String helperName = delegator.getGroupHelperName("default");

Connection conn = ConnectionFactory.getConnection(helperName);

and then use connection with regular java methods - no need to use the groovy ones.

yeah, it was just an example of the relevant APIs. groovy.sql not available in java (of course).

You could follow this tutorial https://developer.atlassian.com/display/JIRADEV/JIRA+Issue+CRUD+Servlet+and+Issue+Search. The query will be inside REST module.

How would it help? It uses standart jira API, not the sql query (jql query is not sql query)

Executing SQL directly in a plugin is a bad idea, you should try to use the API whereever possible.

I know but I need the worklog incl. remaining estimate from change history.

This can be done in one SQL statement. When using the API I will have the n+1 problem. So the API is not an option.

What n+1 problem? Pulling worklogs from an issue is a standard function in the API.

SQL is slow and clunky and easy to break.

Using the API I must search for all Issues (1 query) then for every Issue I must get the History (n query).

For example if I get 1200 issues back using the API I will have 1201 queries and many objects created for no use.

You mean there is no way to get the database connection trough the JIRA API?

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Posted 5 hours ago in Industry Groups

Upcoming Team Tour Government Symposium in Washington D.C.

Calling all Community members from the Washington D.C. area! Atlassian’s Team Tour is heading to your city to host a Government Symposium on May 1st. Join our team and other government agencies to le...

10 views 0 1
View post

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you