CODEWITHSUNDEEP
javascriptmysqljsonjspd3.js
sam140
sam140

Reputation: 249

bar chart in d3 using mysql database

I have created a table by taking two columns from mysql database and converting it json object it was sucessfully working fine but i m not getting any idea how i can plot a bar chart using those two column.it will be great helpful if some one can guide me .. Here is my code:

<%@ page language="java" import="java.sql.*"%>
<%@ page language="java" import="org.json.JSONObject"%>
<html>
<head><title>Read from mySQL Database</title>
<!DOCTYPE html>
<script src="http://d3js.org/d3.v3.min.js"></script>
<meta charset="utf-8">

</head>

<body>

<div align="center" width="85%">
<center>
<table border="1" borderColor="#ffe9bf" cellPadding="0" cellSpacing="0" width="658" height="63">
<tbody>
<%
String DRIVER = "org.gjt.mm.mysql.Driver";
Class.forName(DRIVER).newInstance();


Connection con=null;
ResultSet rst=null;
Statement stmt=null;

try{
String url="jdbc:mysql://localhost/sales?user=root&password=root";

int i=1;
con=DriverManager.getConnection(url);
stmt=con.createStatement();
rst=stmt.executeQuery("select * from product limit 10");
JSONObject jsonObject = new JSONObject();

while(rst.next()){
jsonObject.put("sku"+i,rst.getString(2));

%>
<tr>

<td bgColor="#ffff98" vAlign="top" width="107" height="19"><%=rst.getString(2)%></td>
<td bgColor="#ffff98" vAlign="top" width="270" height="19"><%=rst.getString(4)%></td>

</tr>
<%


i++;
}
String outputString = jsonObject.toString();
rst.close();
stmt.close();
con.close();
}catch(Exception e){
System.out.println(e.getMessage());
}

%>

</tbody>
</table>
</center>
</div>
</body>
</html>

Upvotes: 0

Views: 2281

Answers (1)

Mark
Mark

Reputation: 108557

From the looks of your code, you are creating a single JavaScript object like:

jsonObject = {"sku1": "a string", "sku2": "another string"...

d3.js though really likes to consume an array of JavaScript objects:

jsonObject = [
  {
    sku: "a string",
    otherColumn: 3
  },
  {
    sku: "another string",
    otherColumn: 4
  }
  ...

Where "sku" is the column name and "otherColumn" is the value.

So, to get your data in that form should be as easy as:

JSONArray jsonArray = new JSONArray();

while(rst.next()){
  JSONObject jsonObject = new JSONObject();
  jsonObject.put("sku", rst.getString(2)); 
  jsonObject.put("otherColumn", rst.getInt(4));  // assuming these are numbers NOT STRINGS or the bar chart would have nothing to draw

  jsonArray.put(jsonObject);
}

// rest of java code here, close out connections, etc...

%>

<script>
    // switch over to JavaScript and d3
    // get java variable into JavaScript
    var javaScriptData = "<%out.print(jsonArray.toString());%>";

    // bar chart code goes here
    ....

To create the bar chart, take a look at this sample code:

var margin = {top: 20, right: 20, bottom: 30, left: 40},
    width = 960 - margin.left - margin.right,
    height = 500 - margin.top - margin.bottom;

var x = d3.scale.ordinal()
    .rangeRoundBands([0, width], .1);

var y = d3.scale.linear()
    .range([height, 0]);

var xAxis = d3.svg.axis()
    .scale(x)
    .orient("bottom");

var yAxis = d3.svg.axis()
    .scale(y)
    .orient("left")
    .ticks(10, "%");

var svg = d3.select("body").append("svg")
    .attr("width", width + margin.left + margin.right)
    .attr("height", height + margin.top + margin.bottom)
  .append("g")
    .attr("transform", "translate(" + margin.left + "," + margin.top + ")");

x.domain(javaScriptData.map(function(d) { return d.sku; }));
y.domain([0, d3.max(javaScriptData, function(d) { return d.otherColumn; })]);

svg.append("g")
    .attr("class", "x axis")
    .attr("transform", "translate(0," + height + ")")
    .call(xAxis);

svg.append("g")
    .attr("class", "y axis")
    .call(yAxis)
    .append("text")
    .attr("transform", "rotate(-90)")
    .attr("y", 6)
    .attr("dy", ".71em")
    .style("text-anchor", "end")
    .text("otherColumn");

svg.selectAll(".bar")
    .data(javaScriptData)
    .enter().append("rect")
    .attr("class", "bar")
    .attr("x", function(d) { return x(d.sku); })
    .attr("width", x.rangeBand())
    .attr("y", function(d) { return y(d.otherColumn); })
    .attr("height", function(d) { return height - y(d.otherColumn); });

Upvotes: 1

Related Questions