LineChart Using Database Multiple Column separate values

[vikram masurkar]

<%
String COUNT = “”;
String CE = “”;
try {

Class.forName(“com.mysql.jdbc.Driver”);

connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/c”, “root”, “root”);

connection.setAutoCommit(false);

String sql = “SELECT * FROM ” + tableName + “;”;

PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
%>

<table border=”10″ width=”900px” cellspacing=”50″ id=”tab”>
<tr>
<th>COLUMN NAME</th>
<th>COUNT</th>
<th>AVERAGE</th>
<th>STDEV</th>
<th>MINIMUM</th>
<th>MAXIMUM</th>
<!– <th>CONTROL ERROR</th> –>
</tr>
<%

for (int i = 2; i <=rsmd.getColumnCount(); i++)
{

rs.beforeFirst();
%>

<tr>
<td bgcolor=”#D4AF37″>
<% out.print(rsmd.getColumnLabel(i));%>
</td>

<%
String sql1 = “SELECT COUNT(” + rsmd.getColumnName(i) + “) FROM ” + tableName + “;”;
PreparedStatement ps1 = connection.prepareStatement(sql1);
ResultSet rs1 = ps1.executeQuery();
while (rs1.next())
{
COUNT = rs1.getString(1);

}
%>

<td bgcolor=”#C0C0C0″>
<%out.print(COUNT);%>
<!– <td>SUM</td> –>
</td>

<%
String avg = “”;
String sql2 = “SELECT ROUND(AVG(” + rsmd.getColumnName(i) + “),2) FROM ” + tableName + “;”;
PreparedStatement ps2 = connection.prepareStatement(sql2);
ResultSet rs2 = ps2.executeQuery();
while (rs2.next())
{
avg = rs2.getString(1);
}
%>

<td bgcolor=”#C0C0C0″>
<%out.print(avg);%>
<!– <td>AVG</td> –>
</td>

<%
String sdtev = “”;
String sql3 = “SELECT ROUND(STDDEV(” + rsmd.getColumnName(i) + “),2) FROM ” + tableName + “;”;
PreparedStatement ps3 = connection.prepareStatement(sql3);
ResultSet rs3 = ps3.executeQuery();
while (rs3.next())
{
sdtev = rs3.getString(1);
}
%>

<td bgcolor=”#C0C0C0″>
<%out.print(sdtev);%>
<!– <td>AVG</td> –>
</td>

<%
String min = “”;
String sql4 = “SELECT ROUND(MIN(” + rsmd.getColumnName(i) + “),2) FROM ” + tableName + “;”;
PreparedStatement ps4 = connection.prepareStatement(sql4);
ResultSet rs4 = ps4.executeQuery();
while (rs4.next())
{
min = rs4.getString(1);
}
%>

<td bgcolor=”#C0C0C0″>
<%out.print(min);%>
<!– <td>AVG</td> –>
</td>

<%
String max = “”;
String sql5= “SELECT ROUND(MAX(” + rsmd.getColumnName(i) + “),2)FROM ” + tableName + “;”;
PreparedStatement ps5 = connection.prepareStatement(sql5);
ResultSet rs5 = ps5.executeQuery();
while (rs5.next())
{
max = rs5.getString(1);
}
%>

<td bgcolor=”#C0C0C0″>
<%out.print(max);%>
<!– <td>AVG</td> –>
</td>

<%
}
%>

<%

String sql6 = “SELECT ROUND(AVG((ABS(” + rsmd.getColumnName(3) + ” – ” + rsmd.getColumnName(2)+ “)” + “/” + rsmd.getColumnName(2) + “) * “+ 100 +”),2) FROM ” + tableName + “;”;
PreparedStatement ps6 = connection.prepareStatement(sql6);
ResultSet rs6 = ps6.executeQuery();
while (rs6.next())
{
CE = rs6.getString(1);
}
%>

<%– <td bgcolor=”#C0C0C0″ rowspan=”15″>
<%out.print(CE);%>
<!– <td>AVG</td> –>
</td>
–%>

</tr>

</table>

–%>
<%

} catch (Exception e) {
connection.close();
e.printStackTrace();
} finally {
connection.close();
}
%>

<!–**************************************** –>
// i want to plot chart on this..with column name on x axis and other average,count,min,max an y axis.please help

[Indranil]

@vikram-masurkar,

Can you please create a working sample project with a sample database, host it in the google drive/one drive and share it with us so that we can understand your scenario better and help you out.

___________
Indranil Deo,
Team CanvasJS