Querying the Current Confluence Database using JNDI

You can execute a query against the current Confluence database. Here’s how:

import javax.naming.InitialContext
import javax.naming.NamingException
import javax.sql.DataSource
import java.sql.Connection
import java.sql.ResultSet
import java.sql.Statement

Logger log = Logger.getLogger("--Accessing Confluence Database--")
ClassLoader threadClassLoader = Thread.currentThread().getContextClassLoader()
Connection confluenceConnection = null

try {
    Thread.currentThread().setContextClassLoader(ContainerManager.class.getClassLoader())
    InitialContext ctx = new InitialContext()
    DataSource confluenceDs = (DataSource) ctx.lookup("java:comp/env/jdbc/ConfluenceDS")

    confluenceConnection = confluenceDs.getConnection()

    Statement stmnt = confluenceConnection.createStatement()
    ResultSet result = stmnt.executeQuery("SELECT count(*) FROM spaces")

    // Process result

} catch (NamingException e) {
    log.error("Connecting to Confluence database error: " + e.explanation)
} finally {
    Thread.currentThread().setContextClassLoader(threadClassLoader)

    if (confluenceConnection != null) {
        confluenceConnection.close()
    }
}

Querying the Current Confluence Database without JNDI

The next example shows how to connect to the default Confluence H2 database

import java.sql.*

String USER_NAME = "sa"
String PASSWORD = ""
String DB_CONN_STRING = "jdbc:hsqldb:/confluence/home/database/confluencedb;hsqldb.tx=MVCC;readonly=true"
String DRIVER_CLASS_NAME = "org.hsqldb.jdbcDriver"

Logger log = Logger.getLogger("--Accessing Confluence DB--")
Connection connection = null
try {
    Class.forName(DRIVER_CLASS_NAME)
} catch (Exception ex) {
    log.error("Check classpath. Cannot load db driver: " + DRIVER_CLASS_NAME)
}

try {
    connection = DriverManager.getConnection(DB_CONN_STRING, USER_NAME, PASSWORD)
    Statement stmnt = connection.createStatement()
    ResultSet result = stmnt.executeQuery("SELECT * FROM spaces")

    // Process result

} catch (SQLException e) {
    log.error("Driver loaded, but cannot connect to db: " + DB_CONN_STRING)
} finally {
    try {
        if (connection != null) {
            connection.close()
        }
    } catch (SQLException e) {
        e.printStackTrace()
    }
}

Your database connection configuration is stored in the server.xml or <confluence_home>/confluence.cfg.xml file. For more info have a look at the Atlassian documentation.

Direct database update queries are not recommended in Confluence. Instead, we recommend adding or modifying data using Confluence’s APIs (via ScriptRunner). If you absolutely must modify data in your database via direct database queries, always back up your data before performing any modification to the database.

For how-to questions please ask on Atlassian Answers where there is a very active community. Adaptavist staff are also likely to respond there.

Ask a question about ScriptRunner for JIRA, for for Bitbucket Server, or for Confluence.