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

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
Community showcase
Posted yesterday in United States

Administrators Cleanup

Good morning, After a long weekend cleaning up, I am still in the cleanup mode 😊. I am curious to know: When do you feel the urgency to cleanup your Atlassian Tools? How many times do y...

42 views 1 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