最近利用JFreeChart和MySQL数据库做了一个JSP网页,展现Android Martet全球10个国家的TOP800游戏排名的曲线走势
准备知识,请先阅读我先前写的博客
JFreeChart学习示例
需导入jar包如下:
<%@ page language="java" contentType="text/html" pageEncoding="utf-8" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page language="java" import="java.sql.*" %>
<%@ page language="java" import="java.io.*" %>
<%@ page language="java" import="java.io.*" %>
<%@ page language="java" import="java.awt.*" %>
<%@ page language="java" import="org.jfree.data.*" %>
<%@ page language="java" import="org.jfree.data.category.*" %>
<%@ page language="java" import="org.jfree.data.general.*" %>
<%@ page language="java" import="org.jfree.chart.*" %>
<%@ page language="java" import="org.jfree.chart.entity.*" %>
<%@ page language="java" import="org.jfree.chart.plot.*" %>
<%
// GLOBLE PARAMS
// MySQL 连接JDBC
String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
String MYSQL_URL = "jdbc:mysql://localhost:3306/top800";
// 数据库查询语句
String chartTitle = "GameName of Country";
String sql = "select * from (select name, id, dtime, top, country, category, rating, ratingcount, download, price, version, filesize, requireandroid, url from gametop800 where name like \"%3D Bowling%\" and country = \"usa\" order by dtime desc limit 0, 2147483647) as tbl order by dtime asc";
%>
<%@page import="org.jfree.chart.plot.PlotOrientation"%>
<%@page import="java.util.Date"%>
<%@page import="org.jfree.chart.servlet.ServletUtilities"%>
<%@page import="org.jfree.chart.axis.NumberAxis"%>
<%@page import="org.jfree.chart.axis.CategoryAxis"%>
<%@page import="org.jfree.chart.axis.CategoryLabelPositions"%><html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Top800 Game Free Chart Line</title>
</head>
<body>
<center>
<form method="post">
<div style="margin: auto 30px; width: 1000px" >
<select name="game_type">
<option value="游戏名称">游戏名称</option>
<option value="游戏包名">游戏包名</option>
</select>:
<input type="text" name="game_keyword" style="width:300px" />
所属国家:
<select name="countryname" style="width:100px">
<option value="usa">usa</option>
<option value="england">england</option>
<option value="france">france</option>
<option value="japan">japan</option>
<option value="italy">italy</option>
<option value="german">german</option>
<option value="india">india</option>
<option value="spain">spain</option>
<option value="russia">russia</option>
<option value="china">china</option>
<option value="eu">eu</option>
</select>
记录时间:
<select name="game_dtime">
<option value="全部">全部</option>
<option value="最近一周">最近一周</option>
<option value="最近一月">最近一月</option>
</select>
<input type="submit" value="查询" style="width:60px">
</div>
<hr style="width: 80%" />
<%
String game_type = new String("游戏名称");
String game_keyword = "3D Bowling";
String countryname = "usa";
String game_dtime = new String("全部");
request.setCharacterEncoding("utf-8");
game_type = request.getParameter("game_type");
game_keyword = request.getParameter("game_keyword");
countryname = request.getParameter("countryname");
game_dtime = request.getParameter("game_dtime");
int limitTop = Integer.MAX_VALUE;
game_type = game_type == null ? game_type = "游戏名称" : game_type.trim();
game_keyword = game_keyword == null ? game_keyword = "3D Bowling" : game_keyword.trim();
countryname = countryname == null ? countryname = "usa" : countryname.trim();
game_dtime = game_dtime == null ? game_dtime = "全部" : game_dtime.trim();
if (game_dtime != null) {
if (game_dtime.equals("全部")) {
limitTop = Integer.MAX_VALUE;
} else if (game_dtime.equals("最近一周")) {
limitTop = 7;
} else if (game_dtime.equals("最近一月")) {
limitTop = 30;
} else {
limitTop = Integer.MAX_VALUE;
}
}
if (game_keyword != null && game_keyword != "") {
chartTitle = game_keyword + " of " + countryname;
if (game_type.equals("游戏名称")) {
sql = "select * from (select name, id, dtime, top, country, category, rating, ratingcount, download, price, version, filesize, requireandroid, url from gametop800 where name like \"%"
+ game_keyword
+ "%\" and country = \""
+ countryname
+ "\" order by dtime desc limit 0, "
+ limitTop + ") as tbl order by dtime asc";
} else if (game_type.equals("游戏包名")) {
sql = "select * from (select name, id, dtime, top, country, category, rating, ratingcount, download, price, version, filesize, requireandroid, url from gametop800 where id = \""
+ game_keyword
+ "\" and country = \""
+ countryname
+ "\" order by dtime desc limit 0, "
+ limitTop + ") as tbl order by dtime asc";
} else {
sql = "select * from (select name, id, dtime, top, country, category, rating, ratingcount, download, price, version, filesize, requireandroid, url from gametop800 where name like \"%"
+ game_keyword
+ "%\" and country = \""
+ countryname
+ "\" order by dtime desc limit 0, "
+ limitTop + ") as tbl order by dtime asc";
}
} else {
game_type = "游戏名称";
game_keyword = "3D Bowling";
countryname = "usa";
game_dtime = "全部";
}
%>
<table border="2" width="40%">
<tr>
<td valign="middle"><b><%=game_type%>:</b></td>
<td><%=game_keyword%> </td>
</tr>
<tr>
<td valign="middle"><b>所属国家:</b></td>
<td><%=countryname%></td>
</tr>
<tr>
<td valign="middle"><b>记录时间:</b></td>
<td><%=game_dtime%></td>
</tr>
</table>
<hr style="width: 80%" />
<%
DefaultCategoryDataset dataset = new DefaultCategoryDataset();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 连接 MySQL 数据库
Class.forName(MYSQL_DRIVER).newInstance();
conn = DriverManager.getConnection(MYSQL_URL, "root", "");
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
// 遍历读取的数据集
while (rs.next()) {
String name = rs.getString(1);
String dtime = rs.getString(3);
int top = rs.getInt(4);
if (name != null && dtime != null) {
dataset.addValue(top, name, dtime); // 构造JFreeChart的数据集dataset
}
}
} catch (Exception e) {
System.err.println(e.getMessage());
}
JFreeChart chart = ChartFactory.createLineChart(
chartTitle, // 标题
"记录时间", // 横轴名称
"游戏排名", // 纵轴名称
dataset, // 数据集
PlotOrientation.VERTICAL, // 垂直视图
true,
true,
true
);
final CategoryPlot plot = (CategoryPlot) chart.getPlot();
plot.setBackgroundPaint(Color.lightGray); // 背景色
plot.setRangeGridlinePaint(Color.blue); // 横轴虚线
plot.setRangeGridlinesVisible(true); // 横轴虚线是否可见
final NumberAxis rangeAxis = (NumberAxis) plot.getRangeAxis();
rangeAxis.setAutoRangeMinimumSize(1); // 距离为1
rangeAxis.setAutoRangeIncludesZero(true); // 从零计算
rangeAxis.setInverted(true); // 纵轴逆序(原点到顶端,是从大到小)
final CategoryAxis categoryAxis = plot.getDomainAxis();
if (limitTop == 7) {
categoryAxis.setCategoryLabelPositions(CategoryLabelPositions.STANDARD); // 横轴标准显示(水平)
} else {
categoryAxis.setCategoryLabelPositions(CategoryLabelPositions.UP_45); // 横轴45度显示(倾斜)
}
FileOutputStream fos_jpg = null;
String file_jpg = null;
String url_jpg = null;
try {
final ChartRenderingInfo info = new ChartRenderingInfo(new StandardEntityCollection());
file_jpg = ServletUtilities.saveChartAsJPEG(chart, 1200, 600, info, null); // 生成图片
url_jpg = request.getContextPath() + "/servlet/DisplayChart?filename=" + file_jpg; // 图片路径
} catch (Exception e) {
out.println(e);
} finally {
try {
fos_jpg.close();
} catch (Exception e) {
e.printStackTrace();
}
}
%>
<img src=<%=url_jpg%> border="1" /> <!-- 显示图片(url_jpg为JFreeChart生成图片的路径) -->
<hr style="width: 80%" />
<table border="2" borderColor="#00000" cellPadding="0" cellSpacing="0" width="auto" >
<tbody>
<tr>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>name</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>id</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>dtime</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>top</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>country</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>category</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>rating</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>ratingcount</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>download</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>price</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>version</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>filesize</b></font></td>
<td bgColor="#008080" height="28" align="center" valign="middle">
<font color="#ffffff"><b>requireandroid</b></font></td>
</tr>
<%
try {
rs = stmt.executeQuery(sql);
while (rs.next()) {
%>
<tr>
<td height="18" vAlign="middle" align="center"><a href="<%=rs.getString(14)%>" target="_blank"><%=rs.getString(1)%></a></td>
<td height="18" valign="middle" align="center"><%=rs.getString(2)%></td>
<td height="18" valign="middle" align="center"><%=rs.getString(3)%></td>
<td height="18" valign="middle" align="center" bgColor="#ffcc68"><%=rs.getInt(4)%></td>
<td height="18" valign="middle" align="center"><%=rs.getString(5)%></td>
<td height="18" valign="middle" align="center"><%=rs.getString(6)%></td>
<td height="18" valign="middle" align="center"><%=rs.getString(7)%></td>
<td height="18" valign="middle" align="center"><%=rs.getString(8)%></td>
<td height="18" valign="middle" align="center"><%=rs.getString(9)%></td>
<td height="18" valign="middle" align="center"><%=rs.getString(10)%></td>
<td height="18" valign="middle" align="center"><%=rs.getString(11)%></td>
<td height="18" valign="middle" align="center"><%=rs.getString(12)%></td>
<td height="18" valign="middle" align="center"><%=rs.getString(13)%></td>
</tr>
<%
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
%>
</tbody>
</table>
<%
try {
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
%>
</form>
</center>
</body>
</html>
效果图:
这是我们公司出品的经典休闲游戏——3D Bowling,目前已经进入Android Market全球排名前10