Connecting the plugin with database

[Ramiro Pointis]

I created two new tables in the Jira Database and now I want to make querys consulting the tables and showing results in a IssueTabPanel. I've been searching for more than a week the class that connects to the database but I haven't found it yet. Can anyone give me a hand?

Basically, I want to have a tasks checklist in the issuetabpanel according with the standards of my company. I've searched and tested a lot of plugins but none meets my expectations. So, I decided to make a plugin. I need a table in the database where I can write and read the checklist values, such as "description" and "done/not-applicable" radio button.

[Radek Kantor · Accepted solution]

Hi,

you must add your new tables into JIRA entity files, by editing files: entitygroup.xml and entitymodel.xml in ...\atlassian-jira\WEB-INF\classes\entitydefs directory. Table defined this way will be also backuped.

<entity entity-name="LinkType" table-name="linktype" package-name="">
        <field name="id" type="numeric"/>

		<field name="key" col-name="keyid" type="long-varchar"/>
		<field name="description" type="very-long"/>
		
        <prim-key field="id"/>
    </entity>

Than create class that will be mapped on new table - its colums. Example:

public class LinkType extends AbstractOfBizValueWrapper {
	
    public LinkType(GenericValue genericValue, List<Project> projects) {
    	 super(genericValue);
    }

    public Long getId() {
        return genericValue.getLong("id");
    }
    
    public String getKey() {
        return genericValue.getString("key");
    }
    
    public void setKey(String key) {
        genericValue.setString("key", key);
    }
    
    public String getDescription() {
        return genericValue.getString("description");
    }
    
    public void setDescription(String description) {
        genericValue.setString("description", description);
    }
}

Create some manager class (and its interface) to manipulate with data and execute business logic. To access use OfBizDelegator class.

OfBizDelegator genericDelegator = new DefaultOfBizDelegator(CoreFactory.getGenericDelegator());

This genericDelegator provides a lot off interesting method :)

public LinkType getLinkType(Long id) throws DataAccessException {
		GenericValue linkType = null;
		try {
			linkType = genericDelegator.findByPrimaryKey(
					"LinkType", EasyMap.build("id", id));
		} catch (DataAccessException e) {
			log.error("Unable to find a link type configuration for id: " + id, e);
			throw e;
		}
		return new LinkType(linkType);
	}

Other solution is used own database integrated into plugin (eg. hsqldb) and access it via jdbc, jpa, ... But this tables are separated, will not be backuped with JIRA.

Hope this help you.

[Ramiro Pointis]

Thanks for taking your time to answer. I'll give it a try and let you know.

[Ramiro Pointis]

Sorry, but where I should create the classes? And, other cuestion, how can I show in the browser "getLinkType"? With the .vm template archive?

[Radek Kantor]

All classes what you need are parts of your plugin. To show data in browser user actions, webwork plugin module.

https://developer.atlassian.com/display/JIRADEV/Webwork+plugin+module

Pseudo code:

public class ManageLinks extends JiraWebActionSupport {

	private Long id;
	private LinkType linkType;
	
	private MyLinkTypeManager myLinkTypeManager;

	public ManageLinks(MyLinkTypeManager myLinkTypeManager) {
		this.myLinkTypeManager = myLinkTypeManager;
    }
	
	@Override
	protected String doExecute() throws Exception {
		linkType = myLinkTypeManager.getLinkType(id);
		return INPUT;  
	}
	

	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}
	
	public LinkType getLinkType() {
		return linkType;
	}
}

In your .vm template access by

#set($lt = $action.getLinkType())
$lt.name

Try find some example, where is a lot off informations, questions, tutorials on forum, atlassian web and google.

[Ramiro Pointis]

Another thing, if I need to show the result in a IssueTabPanel would be the same way?

[Radek Kantor]

Should be very simmilar, only call manager (for reading DB data) from your tab panel implementation, by overwrite some AbstractIssueTabPanel method.

[JamieA]

I assume you're not using Active Objects or the entity engine?

If not, you can get a java.sql.Connection using the first part of the example here: https://studio.plugins.atlassian.com/wiki/display/GRV/Miscellaneous+Groovy+Scripts#MiscellaneousGroovyScripts-ExecuteaSQLquery

[Ramiro Pointis]

No, I wasn't using the entity engine, so this looks pretty good too.

[MattS]

Modifying the JIRA schema will make your upgrades harder. I recommend using Active Objects with JIRA 4.4. and later.

~Matt