How to create a form in Confluence that displays data from database using Scripting for Confluence

This article explains how to create a form in Confluence that displays data from the database using a single app - Scripting for Confluence.

Instructions

1. On a new Confluence page, add Groovy macro of Scripting for the Confluence app.

2. Add the below given groovy script inside the groovy macro:

   /**
    * Run a simple SQL query using a direct JDBC connection
    * Assumes: Script Plugin for Confluence 4.x or above
    * Assumes: Confluence 4.1 or above
    * Requires: mySQL jdbc driver in <Confluence server install>/lib directory
    * Wiki markup:
    *   {groovy:output=wiki|script=#https://bitbucket.org/bob_swift/examples/raw/tip/confluence/groovy/sqlQuery.groovy}
    *   {groovy}
    *
    * @param dbServer - TCP address of mySQL DB server assumed to be on the default port: 3306
    */
   
   import java.sql.Driver
   import groovy.sql.Sql
   
   def connection = getConnection(
       location: '/opt/atlassian/confluence/lib/', // Confluence installation lib directory or use an absolute directory path
       url: "jdbc:mysql://your-server-url:50274/confluence", // example using mySql
       driver: 'com.mysql.jdbc.Driver',
       user: 'confuser',
       password: '****',
   )
   def sql = Sql.newInstance(connection)
   
   def searchtext = request.getParameter("searchtext")
   
   def form = sprintf("""
   <form class="aui" method="get" action="%s">
       <input type="hidden" name="searchTel" value="1">
       <input type="text" name="searchtext" value="%s">
       <input class="button submit" type="submit" value="Submit" id="comment-search-button">
   </form>
   """, contentEntityObject.getUrlPath(), searchtext==null ? "" : searchtext)
   
   out.println form
   
   if (searchtext != null) {
       out.println(sprintf("Suche nach \"%s\"", searchtext))
       out.println("""<table class=\"wrapped confluenceTable tablesorter tablesorter-default\" role=\"grid\"><thead>
         <tr role=\"row\">
           <th class=\"confluenceTh\">id</th>
         </tr>
       </thead><tbody>""")
       sql.execute(
           [nbstr: searchtext, name: '%'+searchtext+'%'],
           "SELECT * FROM big01 WHERE id =:nbstr", { _, result ->
               result.each { row ->
                   out.println( sprintf( """<tr role=\"row\">
                     <td class=\"confluenceTd\">%s</td>
                  </tr>""", row.id ) )
               }
           }
       )
       out.println("</tbody></table>");
   }
   
   
   /**
    * Get a direct JDBC connection from the connection parameters (see above)
    */
   def getConnection(final Map parameters) {
       def classLoader = getClassLoader(new File(parameters.location)) // get class loader with necessary DB driver jar files
       Driver driver = (Driver) Class.forName(parameters.driver, true, classLoader).newInstance()
       def properties = new Properties() // login parameters
       properties.setProperty('user', parameters.user)
       properties.setProperty('password', parameters.password)
       return driver.connect(parameters.url, properties)
   }
   
   /**
    * Get a class loader with jars loaded from the directory provided
    */
   def getClassLoader(final File directory) {
       URLClassLoader loader = null;
       String[] fileList = directory.list(new FilterByExtension("jar")); // get all jars in directory
       if (fileList.length > 0) {
           URL[] urlList = new URL[fileList.length];
           for (int i = 0; i < fileList.length; i++) {
               File jarFile = new File(directory, fileList[i]);
               urlList[i] = new URL("jar:file:" + (jarFile.getAbsolutePath().charAt(0) == '/' ? "" : '/') + jarFile.getAbsolutePath() + "!/");
               // out.println("urlList: " + urlList[i].toString());
           }
           loader = URLClassLoader.newInstance(urlList, ClassLoader.getSystemClassLoader());
       }
       return loader;
   }
   
   /**
    * Filter file list by extension.
    */
   public class FilterByExtension implements FilenameFilter {
       String extension;
   
       public FilterByExtension(final String extension) {
           this.extension = "." + extension;
       }
   
       @Override
       public boolean accept(File dir, String name) {
           return name.endsWith(extension);
       }
   }
   
   

   Make changes to the URL, user, password according to your database.

3. The above script provides users with a form (as shown in the screenshot below) that asks for user input to provide the ID value and pulls all the rows from big01 table from the database for that particular user input:
   
4. Publish the page.
5. Test the script by giving an input value.
6. The output from the user form looks as given below: