Access to Hibernate connection

[Claus Andersen]

Hello

I know it is not recommended, bad practice, dangerous, and so on, but I need to execute direct SQL statements to the database used by Confluence from my plugin. :)

I found http://forums.atlassian.com/thread.jspa?threadID=28983 , saying:

* Use Spring injection to get an instance of the net.sf.hibernate.SessionFactory (the bean is called sessionFactory)

* Call org.springframework.orm.hibernate.SessionFactoryUtils.getSession(sessionFactory, true) to get a reference to the current Hibernate sesion (or create a new one if there is no current session)

* Call session.connection() to get the currently open java.sql.Connection

I am able to get an instance of SessionFactory, by adding a component-import to atlassian-plugin.xml:

<component-import key="sessionFactory" interface="net.sf.hibernate.SessionFactory" />

I my IDE (Netbeans) I am able to import (resolve) and use

org.springframework.orm.hibernate.SessionFactoryUtils.getSession(sessionFactory, true);

without compile errors. Futhermore, atlas-compile does not complain.

But, when I install my plugin and the line above is executed, I get an NoClassDefFoundError for the SessionFactoryUtils class.

I have tried to use various combinations of <Import-Package> in atlassian-plugin.xml, e.g. <Import-Package>org.springframework.orm.hibernate</Import-Package>

This results in the following error (plugin key replaced by [KEY]), when enabling the plugin:

[INFO] [talledLocalContainer] 2012-04-25 16:13:23,381 ERROR [http-1990-6] [plugin.osgi.factory.OsgiPlugin] enableInternal Detected an error (BundleException) enabling the plugin '[KEY]' : Unresolved constraint in bundle [KEY] [117]: Unable to resolve 117.0: missing requirement [117.0] package; (package=org.springframework.orm.hibernate). This error usually occurs when your plugin imports a package from another bundle with a specific version constraint and either the bundle providing that package doesn't meet those version constraints, or there is no bundle available that provides the specified package. For more details on how to fix this, see http://confluence.atlassian.com/x/1xy6D

This is followed by a long nested exception stack trace.

The funny thing is that org.springframework.orm.hibernate.SessionFactoryUtils can be used in a Groovy script, using the {groovy} page macro from the Script Plugin.

Do you have idea why, or another way to get an instance of java.sql.Connection pointing the Confluence database?

I have noticed that org.springframework.orm.hibernate3.SessionFactoryUtils (note the "3") can be resolved, by it requires an instance of SessionFactory from another unavailable package.

Thank you in advance.

Regards, Claus

[Deleted user · Accepted solution]

I’ve recently written a simple plugin that extends Scroll Office’s page selection module that runs a SQL query against the Confluence database, I was also getting nowhere until I stumbled upon an article that recommended using Confluence’s own class PluginHibernateSessionFactory (import com.atlassian.hibernate.*;)

I have attached my code below. I’m not a java programmer and this is my first attempt at it so apologies if I’ve broken a few conventions.

package com.k15t.scroll.confluence.plugins.pageselection.impl;


import com.atlassian.confluence.pages.AbstractPage;
import com.atlassian.confluence.pages.Page;
import com.atlassian.confluence.pages.PageManager;
import com.atlassian.confluence.security.PermissionManager;
import com.k15t.scroll.confluence.core.builder.ConfluenceSource;
import com.k15t.scroll.core.builder.Source;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.util.*;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import com.atlassian.confluence.util.SQLUtils;

import com.atlassian.confluence.status.service.systeminfo.*;
import com.atlassian.hibernate.*;

import net.sf.hibernate.Session;
import net.sf.hibernate.SessionFactory;
import net.sf.hibernate.HibernateException;


public class FilterByPageIDPageSelectionStrategy extends
	BasePageSelectionStrategy {

    private static final Logger log = LoggerFactory.getLogger(FilterByPageIDPageSelectionStrategy.class);

    private PluginHibernateSessionFactory sessionFactory;

    /**
     * The configuration property receiving the exportID to pass
     * to the database query.
     */
    private String exportID;

     public FilterByPageIDPageSelectionStrategy(PageManager pageManager,
	    PermissionManager permissionManager,
	    PluginHibernateSessionFactory sessionFactory) {
	super(pageManager, permissionManager);
	this.sessionFactory = sessionFactory;
    }

    @Override
    protected Source getPageTreeImpl(Source rootSource) {

	return buildSourceTree(rootSource);
    }

    /**
     * Builds the source tree by adding those child pages obtained from the db
     * table SCROLL_EXPORT which were previously added by the PRIME program
     * using the passed ExportID
     *
     * @param rootSource
     *            The root source add the filtered children.
     * @return The processed subtree for the current source.
     */
    protected Source buildSourceTree(Source rootSource) {

	List<Long> exportPageIDs = new ArrayList<Long>();

	try {
	    // Get User Selected Pages from the database
	    exportPageIDs = getPageIDs(Long.parseLong(exportID));

	    // Add returned pageIDs to the rootSource
	    for (long pageID : exportPageIDs) {

		AbstractPage page = pageManager.getAbstractPage(pageID);
		Source pageSource = new ConfluenceSource(page);
		rootSource.addChild(pageSource);
	    }

	} catch (Exception ex) {
	    log.error("Unable to buildSourceTree for exportID " + exportID + ": " + ex.getMessage(), ex);
	}

	return rootSource;
    }

    // === Getter and Setter =======
    public String exportID() {
	return exportID;
    }

    public void setExportID(String exportID) {
	this.exportID = exportID;
    }


    /**
     * Runs a SQL statement against the database for the passed exportID to
     * obtain the Pages IDs to Export
     *
     * @param exportID
     *            The root source add the filtered children.
     * @return List containing all page IDs
     */
    public List getPageIDs(Long exportID) throws HibernateException,
	    SQLException {

	Session session = sessionFactory.getSession();
	Connection conn = session.connection();

	List<Long> exportPageIDs = new ArrayList<Long>();
	Statement ps = null;
	ResultSet rs = null;

	try {
	    ps = conn.createStatement();

	    rs = ps.executeQuery("SELECT [CONTENTID] FROM [SCROLL_EXPORT] WHERE EXPORTID ="
		    + exportID);
	    while (rs.next()) {
		exportPageIDs.add(rs.getLong(1));
	    }
	} finally {
	    SQLUtils.closeResultSetQuietly(rs);
	}

	return exportPageIDs;

    }

}

[Claus Andersen]

Hi Brian

Thank you very much. It worked for me.

Best regards, Claus.