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

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

Like Milyausha likes this

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)

Nic Brough Community Leader Aug 18, 2014

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.

Nic Brough Community Leader Aug 18, 2014

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
Community showcase
Published in Off-topic

Atlassian Community Guide to A+ Articles

Stop: Articles and permission Article-writing permissions are given to Leaders, Marketplace Vendors, Solutions Partners, Atlassian Team members, and those who are Level 4 + above. But for any aspir...

89 views 9 10
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you