script to query a database
- The query below list all space names in Confluence.
- Modify dataSource to correspond to your environment.
- Modify sql for different queries.
Beanshell
{beanshell}
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import javax.naming.NamingException;
import javax.naming.NameNotFoundException;
String dataSource = "confluenceDS";
String sql = "select * from spaces";
DataSource ds = null;
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet;
try {
ds = (DataSource) (new InitialContext()).lookup("java:comp/env/jdbc/" + dataSource);
connection = ds.getConnection();
statement = connection.prepareStatement(sql);
if (statement.execute()) { // result set is available
resultSet = statement.getResultSet();
if (resultSet != null) {
ResultSetMetaData rsmd = resultSet.getMetaData();
// out.println("<br>Columns in result set: " + rsmd.getColumnCount());
out.println("<br>Space names: ");
while (resultSet.next()) {
out.println("<br> " + resultSet.getString("spacename"));
}
}
} else { // only update count available
out.println("Update count: " + statement.getUpdateCount());
}
}
catch (NameNotFoundException exception) {
out.println(exception.toString());
}
catch (NamingException exception) {
out.println(exception.toString());
}
catch (SQLException exception) {
out.println(exception.toString());
}
finally {
try {
if (resultSet != null) {
resultSet.close();
}
if (connection != null) {
connection.close();
}
}
catch(Exception discard) {
}
}
{beanshell}
Groovy
{groovy}
import groovy.sql.Sql
import javax.naming.InitialContext
// e.g. using datasource
def ds = (new InitialContext()).lookup("java:comp/env/jdbc/confluenceDS")
def sql = new Sql(ds)
// e.g. not using datasource
//def sql = Sql.newInstance('jdbc:mysql://localhost/confluence?autoReconnect=true', "your_confluence_db_user", "your_confluence_db_pw", 'com.mysql.jdbc.Driver')
out.println "<br>Space names: "
sql.eachRow("select * from SPACES") { rs ->
out.println "<br> " + rs.spacename
}
{groovy}
Comments (3)
Dec 05, 2006
patrice saint-louis says:
Is it possible to pass parameters to the beanshell macro to include as parameter...Is it possible to pass parameters to the beanshell macro to include as parameters for the SQL?
Dec 05, 2006
Bob Swift says:
Yes, parameters are supported by the Beanshell Macro, see Macro parameters secti...Yes, parameters are supported by the Beanshell Macro, see Macro parameters section of Java Scripting Macros. For parameters coming from user input, you will need to use the Run macro
Dec 08
David Simpson says:
I'd reiterate that you should always use a datasource. You don't want to put da...I'd reiterate that you should always use a datasource. You don't want to put database connection strings in plain view (as in the commented out part of the groovy example).
This code and therefore the connection strings can easily be seen from the Tools | View Wiki Markup menu.