一、概述：
1.导入jfreechart-1.0.14.jar包
2.实时插入数据
3.获取where条件子句
4.自定义SQL语句
5.拼接SQL 并执行遍历
6.获取jfreechart数据源
7.制作jfreechart折线图
8.获取json{共几页,当前页码,共几条,起始条数,终止条数,数据list}
二、oracle数据库设计
ELECTR_EQU表
ELECTR_DATA表
三、代码实例
package electricityMonitor;
import java.awt.*;
import java.sql.*;
import org.jfree.chart.renderer.xy.XYItemRenderer;
import org.jfree.chart.renderer.xy.XYLineAndShapeRenderer;
import org.jfree.chart.title.TextTitle;
import org.jfree.data.time.*;
import org.json.*;
import java.text.*;
import java.util.*;
import java.util.Date;
import java.util.Timer;
import org.jfree.chart.*;
import org.jfree.chart.plot.*;
public class ElectricityMonitor {
private static String USERNAMR = "mine";
private static String PASSWORD = "mine";
private static String DRVIER = "oracle.jdbc.OracleDriver";
private static String URL = "jdbc:oracle:thin:@192.168.100.57:1521:orcl";
Connection connection = null;
PreparedStatement pstm = null;
ResultSet rs = null;
static String equidRs;
private static ElectricityMonitor instance = null;
public static ElectricityMonitor getInstance() {
if (instance == null) instance = new ElectricityMonitor();
return instance;
}
//实时插入数据
private ElectricityMonitor() {
connection = getConnection();
TimerTask task = new TimerTask() {
public void run() {
insertData();
}
};
Timer timer = new Timer();
long delay = 0;
long intevalPeriod = 1 * 1000;
timer.scheduleAtFixedRate(task, delay, intevalPeriod);
}
//获取where条件子句
private String getWhereStr(String condition){
String ret = "";
try {
JSONObject obj = new JSONObject(condition);
String areaname = obj.getString("areaname");
String equid = obj.getString("equid");
String equname = obj.getString("equname");
if (! areaname.trim().equals("")) {
ret += String.format(" areaname = '%s' ", areaname);
}
if (! equid.trim().equals("")) {
if (!ret.equals("")) ret += " and ";
ret += String.format(" equid = '%s' ", equid);
}
if (! equname.trim().equals("")) {
if (!ret.equals("")) ret += " and ";
ret += String.format(" equname = '%s'", equname);
}
}
catch (Exception e) {
e.printStackTrace();
}
return ret;
}
private boolean containField(String fieldList, String field) {
if (fieldList.equals("*")) return true;
fieldList = fieldList.toUpperCase();
field = field.toUpperCase();
return (fieldList.indexOf(field) > -1);
}
//获取json{共几页,当前页码,共几条,起始条数,终止条数,数据list}
public String query(String qSubstation, String condition, String fields, int pageNum) {
String whereStr = getWhereStr(condition);
String sql = String.format(
" select %s from electr_equ where substation = '%s' %s ",
fields, qSubstation, (whereStr.equals(""))? "" : " and " + whereStr
);
try {
int totalRecords = 0;
pstm = connection.prepareStatement(sql);
rs = pstm.executeQuery();
StringBuffer buff = new StringBuffer();
boolean firstRow = true;
int recordStart = (pageNum - 1) * 10 + 1;
int recordEnd = (pageNum - 1) * 10 + 1;
while (rs.next()) {
totalRecords ++;
if (totalRecords < recordStart) continue;
if (totalRecords > recordEnd) continue;
String row = "";
if (containField(fields, "substation")) {
String substation = rs.getString("substation");
if (!row.equals("")) row = ", ";
row += String.format("\"%s\"", substation);
}
if (containField(fields, "areaname")) {
String areaname = rs.getString("areaname");
if (!row.equals("")) row = ", ";
row += String.format("\"%s\"", areaname);
}
if (containField(fields, "i_a")) {
String i_a = rs.getString("i_a");
if (!row.equals("")) row = ", ";
row += String.format("%.2f", i_a);
}
if (containField(fields, "u_a")) {
String u_a = rs.getString("u_a");
if (!row.equals("")) row = ", ";
row += String.format("%.2f", u_a);
}
if (containField(fields, "w_a")) {
String w_a = rs.getString("w_a");
if (!row.equals("")) row = ", ";
row += String.format("%.2f", w_a);
}
if (firstRow) buff.append(",\n");
else firstRow = false;
buff.append(row);
}
int totalPages = (totalRecords % 10 == 0)? (totalRecords/10) : ((totalRecords/10) + 1);
if (recordEnd > totalRecords) recordEnd = totalRecords;
if (pageNum > totalPages) pageNum = totalPages;
String dataStr = buff.toString();
String dataJson=String.format(
"{\"totalRecords\": %d, " +
"\"recordStart\": %d, " +
"\"recordEnd\": %d, " +
"\"totalPages\": %d, " +
"\"pageNumber\": %d, " +
"\"data\": [\n%s\n]" +
"}",
totalRecords,
recordStart,
recordEnd,
totalPages,
pageNum,
dataStr
);
return dataJson;
} catch (SQLException e) {
e.printStackTrace();
} finally {
ReleaseResource();
}
return null;
}
//自定义SQL语句
private void insertData(){
String sql ="insert into electr_data(substation,equid,equname,areaname,i_a,u_a,w_a,data_time) " +
"select substation,equid,equname,areaname,i_a,u_a,w_a,sysdate from electr_equ";
try {
pstm = connection.prepareStatement(sql);
pstm.executeQuery();
} catch (SQLException e){
e.printStackTrace();
}finally {
ReleaseResource();
}
}
//定义HashMap
public HashMap<String, Vector> hash = new HashMap<String, Vector>();
//拼接SQL 并执行遍历获取jfreechart数据源
public void drawChart(String substation, String condition, String col, String dtStart, String dtEnd) {
Second sec = null;
Statement stat = null;
String whereStr = getWhereStr(condition);
String sql = String.format(
"select equid , equname , %s, d2c(data_time) from electr_data where substation = '%s' %s ",
col, substation, (whereStr.equals("")) ? "" : " and " + whereStr
);
boolean startNull = dtStart.equals("");
boolean endNull = dtEnd.equals("");
if (startNull && endNull) {
sql += " and data_time <= sysdate";
sql += " and data_time >= sysdate-8.0/24.0";
}
else if (startNull && (!endNull)) {
sql += " and data_time >= c2d('" + dtEnd + ":00') - 8.0/24.0";
sql += " and data_time <= c2d('" + dtEnd + ":00')";
}
else if ((!startNull) && endNull) {
sql += " and data_time >= c2d('" + dtStart + ":00')";
}
else {
sql += " and data_time >= c2d('" + dtStart + ":00')";
sql += " and data_time <= c2d('" + dtEnd + ":00')";
}
try {
stat = connection.createStatement();
rs = stat.executeQuery(sql);
while (rs.next()){
equidRs=rs.getString(1);
String name = rs.getString(2);
float colValue = rs.getFloat(3);
String time = rs.getString(4);
int year = Integer.parseInt(time.substring(0,4));
int mont = Integer.parseInt(time.substring(5,7));
int iday = Integer.parseInt(time.substring(8,10));
Day day = new Day(iday, mont, year);
int iHour = Integer.parseInt(time.substring(11,13));
Hour hour = new Hour(iHour, day);
int iMinu = Integer.parseInt(time.substring(14,16));
Minute minu = new Minute(iMinu, hour);
int iSec = Integer.parseInt(time.substring(17,19));
sec = new Second(iSec, minu);
Vector v=hash.get(equidRs);
if(v == null) {
v = hash.put(equidRs, new Vector());
}else{
v.add(sec);
v.add(colValue);
}
}
rs.close();
String rangeAxis;
if(col.equals("i_a")){
rangeAxis="电流";
}else if(col.equals("u_a")){
rangeAxis="电压";
}else{
rangeAxis="功率";
}
StandardChartTheme mChartTheme = new StandardChartTheme("CN");
mChartTheme.setLargeFont(new Font("黑体", Font.PLAIN, 15));
mChartTheme.setExtraLargeFont(new Font("黑体", Font.PLAIN, 15));
mChartTheme.setRegularFont(new Font("黑体", Font.PLAIN, 15));
ChartFactory.setChartTheme(mChartTheme);
TimeSeriesCollection dataset=GetCollection(hash);
JFreeChart timeSeriesChart = ChartFactory.createTimeSeriesChart(
"折线图", //图表标题
"时间", //X轴标题
rangeAxis, //Y轴标题
dataset, //绘图数据集
true, //显示图例
true, //采用标准生成器
false); //是否生成超链接
//设置主标题
timeSeriesChart.setTitle(new TextTitle(substation, new Font("宋体", Font.PLAIN, 18)));
//获取图表区域对象
XYPlot plot = timeSeriesChart.getXYPlot();
XYItemRenderer r = plot.getRenderer();
if(r instanceof XYLineAndShapeRenderer) {
XYLineAndShapeRenderer axis = (XYLineAndShapeRenderer)r;
axis.setBaseShapesVisible(true);
axis.setBaseShapesFilled(true);
axis.setDrawSeriesLineAsPath(true);
}
ChartFrame mChartFrame = new ChartFrame("折线图", timeSeriesChart);
mChartFrame.pack();
mChartFrame.setVisible(true);
} catch (SQLException e) {
e.printStackTrace();
}finally{
if (stat!=null){
try {
stat.close();
stat=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
ReleaseResource();
}
}
//获取绘图数据集
public static TimeSeriesCollection GetCollection(HashMap<String, Vector> hash){
TimeSeriesCollection dataset = new TimeSeriesCollection();
Iterator iter = hash.entrySet().iterator();
while (iter.hasNext()){
Map.Entry entry = (Map.Entry) iter.next();
Object key = entry.getKey();
Object val = entry.getValue();
String[] arr = val.toString().split(",");
TimeSeries timeSeries = new TimeSeries("ID:"+(Comparable) key);
for (int i = 0; i < arr.length; i++) {
if ((2 * i + 1) < arr.length) {
String t = arr[2 * i].replace('[', ' ').replace(']', ' ').trim();
String a = arr[2 * i + 1].replace('[', ' ').replace(']', ' ').trim();
Date date1 = new Date(t);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String sDate = sdf.format(date1);
int year = Integer.parseInt(sDate.substring(0,4));
int mont = Integer.parseInt(sDate.substring(5,7));
int iday = Integer.parseInt(sDate.substring(8, 10));
Day day = new Day(iday, mont, year);
int iHour = Integer.parseInt(sDate.substring(11,13));
Hour hour = new Hour(iHour, day);
int iMinu = Integer.parseInt(sDate.substring(14,16));
Minute minu = new Minute(iMinu, hour);
int iSec = Integer.parseInt(sDate.substring(17,19));
Second sec1 = new Second(iSec, minu);
timeSeries.addOrUpdate(sec1, Double.parseDouble(a));
}
}
dataset.addSeries(timeSeries);
}
return dataset;
}
public static void main(String[] args){
ElectricityMonitor em=new ElectricityMonitor();
em.drawChart("变电所1","{\"equid\": \"\",\"equname\": \"\",\"areaname\": \"\"}","i_a","","2018-1-8 15:00");
}
public Connection getConnection(){
try {
Class.forName(DRVIER);
connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
System.out.println("成功连接数据库");
} catch (ClassNotFoundException e) {
throw new RuntimeException("class not find !", e);
} catch (SQLException e) {
throw new RuntimeException("get connection error!", e);
}
return connection;
}
public void ReleaseResource(){
if (rs != null) {
try {
rs.close();
rs=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstm != null) {
try {
pstm.close();
pstm=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
随时随地看视频
热门评论
-
JimmyYangMJ2019-04-03 0
查看全部评论代码有报错