Execute SQL in Plugin

[Simon Martinelli]

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

[Alexey_Rjeutski__Polontech_ · Accepted solution]

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

[Simon Martinelli]

Thanks.

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

[Alexey_Rjeutski__Polontech_]

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

[JamieA]

There is an example of getting the database connection here: https://jamieechlin.atlassian.net/wiki/display/GRV/Miscellaneous+Groovy+Scripts#MiscellaneousGroovyScripts-ExecuteaSQLquery

[Alexey_Rjeutski__Polontech_]

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.

[JamieA]

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

[Marcin Urbański]

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.

[Alexey_Rjeutski__Polontech_]

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

[Nic Brough -Adaptavist-]

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

[Simon Martinelli]

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 -Adaptavist-]

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.

[Simon Martinelli]

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.

[Simon Martinelli]

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