绘制jfreechart折线图
1、概述：
1.导入jfreechart-1.0.14.jar包
2.实时插入数据
3.获取where条件子句
4.自定义SQL语句
5.拼接SQL 并执行遍历
6.获取jfreechart数据源
7.制做jfreechart折线图
8.获取json{共几页,当前页码,共几条,起始条数,终止条数,数据list}
2、oracle数据库设计
ELECTR_EQU表
java
ELECTR_DATA表sql
3、代码实例数据库
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(",
");
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": [
%s
]" +
"}",
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();
}
}
}
}
4、效果展现json
5、代码知识拓展oracle
使用结构体方式获取数据库数据app
public class SensorInfo {
public String eqid = "";
public String sensorType = "";
public String unit = "";
public String alarmMin = "";
public String alarmMax = "";
public String value = "";
public SensorInfo(String eqid, String sensorType, String unit, String alarmMin, String alarmMax, String value) {
this.eqid = eqid;
this.sensorType = sensorType;
this.unit = unit;
this.alarmMin = alarmMin;
this.alarmMax = alarmMax;
this.value = value;
}
}
public SensorInfo getSensorInfo(String eqid,String dtStart,String dtEnd) {
try {
String sql = String.format(
"select sensorType, unit, alarmMin, alarmMax,value from cddy where id='%s' ", eqid
);
JSONArray arr = new JSONArray(queryNoPage(sql));
if (arr.length() > 0) {
String row = arr.getString(0);
String values[] = row.split(",");
return new SensorInfo(eqid, values[0], values[1], values[2], values[3], values[4]);
}
sql = String.format(
"select sensorType,value from cddy1 where id='%s' ",
eqid
);
arr = new JSONArray(queryNoPage(sql));
if (arr.length() > 0) {
String row = arr.getString(0);
String values[] = row.split(",");
return new SensorInfo(eqid, values[0], "", "", "",values[1]
);
}
}
catch (Exception e) {
e.printStackTrace();
}
return null;
}
绘制freechart折线图数据库设计
private HashMap<String, Vector<TimeValue>> hash = new HashMap<String, Vector<TimeValue>>();
public void drawChart(String eqid,String dtStart,String dtEnd,OutputStream os) {
SensorInfo sensorInfo = getSensorInfo(eqid, dtStart, dtEnd);
String unit= "";
String max = "";
String min = "";
String sensortype="";
String value = "";
String valueSSSJ = "";
if (sensorInfo!=null){
unit= sensorInfo.unit;
max = sensorInfo.alarmMax;
min = sensorInfo.alarmMin;
sensortype=sensorInfo.sensorType;
value = sensorInfo.value;
}
String sql = String.format(
"select id,value,d2c(nowtime) from sssj where id='%s'", eqid
);
boolean startNull = dtStart.equals("");
boolean endNull = dtEnd.equals("");
if (startNull && endNull) {
sql += " and nowtime <= sysdate";
sql += " and nowtime >= sysdate-8.0/24.0";
}
else if (startNull && (!endNull)) {
sql += " and nowtime >= c2d('"+dtEnd+"') - 8.0/24.0";
sql += " and nowtime <= c2d('"+dtEnd+"')";
}
else if ((!startNull) && endNull) {
sql += " and nowtime >= c2d('"+dtStart+"')";
}
else {
sql += " and nowtime >= c2d('"+dtStart+"')";
sql += " and nowtime <= c2d('"+dtEnd+"')";
}
try {
String str = queryNoPage(sql);
System.out.println("drawChart:\n" + str);
JSONArray jarr = new JSONArray(str);
for(int i = 0; i < jarr.length(); i ++){
String row = jarr.getString(i);
String[] cols = row.split(",");
valueSSSJ = cols[1];
String time = cols[2];
Vector<TimeValue> v = hash.get(eqid);
if(v == null) {
v = new Vector<TimeValue>();
v = hash.put(eqid,v);
}else{
v.add(new TimeValue(time, valueSSSJ));
}
if (!max.equals("")&&!min.equals("")){
v = hash.get("max");
if(v == null) {
v = new Vector<TimeValue>();
v = hash.put("max",v);
}else{
v.add(new TimeValue(time, max));
}
v = hash.get("min");
if(v == null) {
v = new Vector<TimeValue>();
v = hash.put("min", v);
}else{
v.add(new TimeValue(time, min));
}
}
}
if (str.equals("[]")) unit="";
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轴标题
unit, //Y轴标题
dataset, //绘图数据集
true, //显示图例
true, //采用标准生成器
false //是否生成超连接
);
//设置主标题
String titleName = "";
if (str.equals("[]"))titleName="此区间无数据";
boolean flag=sensorTypeHash.containsKey(sensortype);
if (flag) {
titleName = sensorTypeHash.get(sensortype);
}
timeSeriesChart.setTitle(new TextTitle(titleName, 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);
}
NumberAxis numAxis = (NumberAxis)plot.getRangeAxis();
if (max.equals("")||min.equals("")){
//设置y显示方式
numAxis.setAutoTickUnitSelection(false);//数据轴的数据标签是否自动肯定
double rangetick = 1D;
numAxis.setTickUnit(new NumberTickUnit(rangetick)); //y轴单位间隔为0.1
numAxis.setAutoRangeIncludesZero(true);
}
if (!valueSSSJ.equals("0")&&!valueSSSJ.equals("00")&&!valueSSSJ.equals("")){
valueSSSJ = valueSSSJ.replace("\"","");
double orValue = Double.parseDouble(valueSSSJ);
numAxis.setAutoTickUnitSelection(false);//数据轴的数据标签是否自动肯定
double rangetick = 1D;
if (orValue>30&&orValue<=100){
rangetick = 5D;
}else if (orValue>100){
rangetick = 10D;
}
numAxis.setTickUnit(new NumberTickUnit(rangetick));
numAxis.setAutoRangeIncludesZero(true);
}
if (!max.equals("null")&&!max.equals("")){
if (Double.parseDouble(max)>30&&Double.parseDouble(max)<=100){
numAxis.setAutoTickUnitSelection(false);//数据轴的数据标签是否自动肯定
double rangetick = 5D;
numAxis.setTickUnit(new NumberTickUnit(rangetick));
numAxis.setAutoRangeIncludesZero(true);
}else if (Double.parseDouble(max)>100){
numAxis.setAutoTickUnitSelection(false);//数据轴的数据标签是否自动肯定
double rangetick = 10D;
numAxis.setTickUnit(new NumberTickUnit(rangetick));
numAxis.setAutoRangeIncludesZero(true);
}
}
//设置x轴显示方式
DateAxis dateaxis = (DateAxis)plot.getDomainAxis();
SimpleDateFormat format = new SimpleDateFormat("MM-dd HH:mm");
dateaxis.setDateFormatOverride(format);
ChartUtilities.writeChartAsPNG(os, timeSeriesChart, 1200, 300);
} catch (Exception e) {
e.printStackTrace();
}
}
private TimeSeriesCollection GetCollection(HashMap<String, Vector<TimeValue>> hash){
TimeSeriesCollection dataset = new TimeSeriesCollection();
Iterator<Map.Entry<String, Vector<TimeValue>>> iter = hash.entrySet().iterator();
while (iter.hasNext()){
Map.Entry<String, Vector<TimeValue>> entry = iter.next();
String key = entry.getKey();
Vector<TimeValue> vect = entry.getValue();
TimeSeries timeSeries;
if(key.equals("max")){
timeSeries = new TimeSeries("最大值 ");
} else if(key.equals("min")){
timeSeries = new TimeSeries("最小值 ");
} else{
timeSeries = new TimeSeries("编号："+key+" ");
}
for (int i = 0; i < vect.size(); i ++) {
TimeValue tv = vect.get(i);
String sDate = tv.time;
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,(tv.value).equals("null")?
Double.parseDouble("0"):Double.parseDouble(tv.value));
}
dataset.addSeries(timeSeries);
}
hash.clear();
return dataset;
}