This example illustrates a simple use case: connect to an external database and show a simple bar chart. In this example, we are using the Jira database, but you can use any database.
- Goal: Collect all Labels in Jira and count the associated issues
- Prerequisite: MySQL JDBC Driver
Chart preview
Layout Script
c3.js Example
// disable the c3 legend chartData.legend = { show: false }; // change the default color behavior to add a color for each label chartData.data.color = function(color, d){ if(d.index != undefined) return chartData.colors[d.index]; return color; } // let c3 do the rest c3.generate(chartData) // chartData - this variable contains the aggregated data for display and is generated by the script
Data Script
import java.sql.*; import java.util.*; import java.util.Map; import java.util.Map.Entry; import groovy.sql.Sql import java.math.BigDecimal; import com.atlassian.jira.util.json.JSONArray; import com.atlassian.jira.util.json.JSONObject; import com.atlassian.jira.component.ComponentAccessor; import com.decadis.jira.xchart.utils.PastellColorUtil; // JDBC Driver - here MySQL String driverName = "com.mysql.jdbc.Driver"; Driver driver = Class.forName(driverName).newInstance() as Driver // JDBC URL String url = "jdbc:mysql://localhost/jira?useUnicode=yes&characterEncoding=UTF-8"; // SQL Authentication Properties props = new Properties(); props.setProperty("user", "user"); props.setProperty("password", "password"); Connection c = driver.connect(url,props); Sql sql = new Sql(c); // SQL Statement def sqlString = """ select * from label """; Map map = new TreeMap<String,BigDecimal>(); // Iterate over each row, select rows by it.{rowName} sql.eachRow(sqlString) { // handle the data input if(map.get(it.label)) { map.put(it.label,map.get(it.label).add(BigDecimal.ONE)) } else { map.put(it.label,BigDecimal.ONE) } } c.close(); // create the c3 data JSONObject chartData = new JSONObject(); JSONObject data = new JSONObject(); JSONArray colors = new JSONArray(); JSONArray columns = new JSONArray(); JSONArray column = new JSONArray(); JSONArray categories = new JSONArray(); // first colum entry must be the name of the data row - here labels column.put(ComponentAccessor.getJiraAuthenticationContext().getI18nHelper().getText("admin.customfield.type.labels.name")); int i = 0; // add the collected values to the json array for ( Entry<String,BigDecimal> entry : map.entrySet() ) { // add the value column.put(entry.getValue().intValue()); //add the key categories.put(entry.getKey()); // add a custom color colors.put(PastellColorUtil.toHex(PastellColorUtil.getNiceColor(i++ * 0.23))); } // combine the json objects columns.put(column); data.put("columns", columns); data.put("type", "bar"); JSONObject axis = new JSONObject(); JSONObject x = new JSONObject(); x.put("type", "category"); x.put("categories", categories); axis.put("x", x); axis.put("rotated", "true"); chartData.put("data", data); chartData.put("axis", axis); chartData.put("colors",colors); // return the jsonobject as string return chartData.toString()