package com.wss;
import java.awt.BorderLayout;
public class SearchTest extends JFrame {
/**
*
*/
private static final long serialVersionUID = 1L;
private JPanel contentPane;
private JTextField latitude;
private JTextField longitude;
private JTextField max;
private JTextField min;
private JTextField center;
private JComboBox begin;
private JComboBox end;
private JTable table;
private DefaultTableModel dm;
/**
* Launch the application.
*/
public static void main(String[] args) {
EventQueue.invokeLater(new Runnable() {
public void run() {
try {
SearchTest frame = new SearchTest();
frame.setVisible(true);
} catch (Exception e) {
e.printStackTrace();
}
}
});
}
/**
* Create the frame.
*/
public SearchTest() {
setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
getToolkit();
setSize(Toolkit.getDefaultToolkit().getScreenSize());
contentPane = new JPanel();
contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
contentPane.setLayout(new BorderLayout(0, 0));
setContentPane(contentPane);
JPanel panel = new JPanel();
contentPane.add(panel, BorderLayout.NORTH);
JLabel label = new JLabel("纬度：");
panel.add(label);
latitude = new JTextField();
panel.add(latitude);
latitude.setColumns(10);
JLabel label_1 = new JLabel("经度：");
panel.add(label_1);
longitude = new JTextField();
panel.add(longitude);
longitude.setColumns(10);
JLabel label_2 = new JLabel("时间:");
panel.add(label_2);
//获取数据库里的时间正序输出
List<GPSBean> list_ = BusinessSelect.selectTime("select distinct to_char(obvdatetime,'YYYY-mm-dd HH24:MI:SS') t from gps order by t asc");
String[] time = new String[list_.size()];
for(int a=0; a<list_.size(); a++){
time[a] = list_.get(a).getTime();
}
begin = new JComboBox(time);
panel.add(begin);
JLabel label_3 = new JLabel("至");
panel.add(label_3);
end = new JComboBox(time);
panel.add(end);
JButton close = new DateChooseJButton();
panel.add(close);
//点击事件
search.addActionListener(new SearchTest_search_actionAdapter(this));
JPanel panel_1 = new JPanel();
contentPane.add(panel_1, BorderLayout.CENTER);
String name[] = {"ID","时间","纬度","经度","值"};
String sql = "select * from gps order by id desc";
List<GPSBean> list = BusinessSelect.selectGPS(sql);
Object gpsValue[][] = new Object[list.size()][5];
for(int i=0; i<list.size(); i++){
GPSBean gpsBean = list.get(i);
Object value[] =
{
gpsBean.getId(),
gpsBean.getObvdateTime(),
gpsBean.getLongitude(),
gpsBean.getLatitude(),
gpsBean.getTecvalue()
};
for(int j=0; j<value.length; j++){
gpsValue[i][j] = value[j];
}
}
dm = new DefaultTableModel(gpsValue, name);
table = new JTable(dm);
table.setPreferredScrollableViewportSize(new Dimension(800,600));
JScrollPane scrollPane = new JScrollPane(table);
panel_1.add(scrollPane);
JPanel panel_s = new JPanel();
contentPane.add(panel_s, BorderLayout.SOUTH);
JLabel max_ = new JLabel("最大值：");
panel_s.add(max_);
max = new JTextField();
panel_s.add(max);
max.setColumns(10);
JLabel min_ = new JLabel("最小值：");
panel_s.add(min_);
min = new JTextField();
panel_s.add(min);
min.setColumns(10);
JLabel center_ = new JLabel("中值：");
panel_s.add(center_);
center = new JTextField();
panel_s.add(center);
center.setColumns(10);
}
public void search_actionPerformed(ActionEvent e){
dm.setRowCount(0);
String latitude_value = latitude.getText();
String longitude_value = longitude.getText();
String begin_value = begin.getSelectedItem().toString();
String end_value = end.getSelectedItem().toString();
String sql = "";
if(latitude_value.equals("") && longitude_value.equals("")){
sql = "select * from (select id, to_char(obvdateTime,'YYYY-mm-dd HH24:MI:SS') as d, latitude, longitude, tecvalue, abs(latitude-0) a, abs(longitude-0) b " +
"from gps where obvdateTime>=TO_DATE('"+begin_value
+"','YYYY-mm-dd HH24:MI:SS') and obvdateTime<=TO_DATE('"+end_value
+"','YYYY-mm-dd HH24:MI:SS') order by a asc, b asc) where a<3 and b<5 order by d asc, a asc,b asc";
}else{
sql = "select * from (select id, to_char(obvdateTime,'YYYY-mm-dd HH24:MI:SS') as d, latitude, longitude, tecvalue, abs(latitude-"
+Double.parseDouble(latitude_value)
+") a, abs(longitude-"+Double.parseDouble(longitude_value)
+") b from gps where obvdateTime>=TO_DATE('"+begin_value
+"','YYYY-mm-dd HH24:MI:SS') and obvdateTime<=TO_DATE('"+end_value
+"','YYYY-mm-dd HH24:MI:SS') order by a asc, b asc) where a<3 and b<5 order by d asc, a asc,b asc";
}
ResultSet rs = CommontDAO.selectFile(sql);
Vector<Object> v;
String time = "2011-02-28 00:00:00";
List<GPSBean> gpsBeanList = new ArrayList<GPSBean>();
try{
while(rs.next()){
if(time.equals(rs.getString(2)) || time == rs.getString(2)){
}else{
GPSBean gpsBean = new GPSBean();
gpsBean.setId(Integer.parseInt(rs.getString(1)));
gpsBean.setObvdateTime(rs.getString(2));
gpsBean.setLatitude(Double.parseDouble(rs.getString(3)));
gpsBean.setLongitude(Double.parseDouble(rs.getString(4)));
gpsBean.setTecvalue(Double.parseDouble(rs.getString(5)));
gpsBeanList.add(gpsBean);
time = rs.getString(2);
v = new Vector<Object>();
v.add(rs.getString(1));
v.add(rs.getString(2));
v.add(rs.getString(3));
v.add(rs.getString(4));
v.add(rs.getString(5));
dm.addRow(v);
}
}
}catch(Exception ex){
ex.printStackTrace();
System.out.println(ex.getMessage());
}finally{
CommontDAO.cloes(rs);
Double tecValue[] = new Double[gpsBeanList.size()];
for(int i=0; i<gpsBeanList.size(); i++){
tecValue[i] = gpsBeanList.get(i).getTecvalue();
}
Double center_value = Sort.center(tecValue);
Double max_value = Sort.max(tecValue);
Double min_value = Sort.min(tecValue);
max.setText(max_value+"");
min.setText(min_value+"");
center.setText(center_value+"");
}
}
}
class SearchTest_search_actionAdapter implements ActionListener {
private SearchTest adaptee;
SearchTest_search_actionAdapter(SearchTest adaptee) {
this.adaptee = adaptee;
}
public void actionPerformed(ActionEvent e) {
adaptee.search_actionPerformed(e);
}
}
package com.wss;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.*;
import java.util.*;
public class BusinessSelect {
//根据sql查询记录
public static List<GPSBean> selectGPS(String sql){
ResultSet rs = null;
List<GPSBean> list = new ArrayList<GPSBean>();
try{
rs = CommontDAO.selectFile(sql);
while(rs.next()){
GPSBean gpsBean = new GPSBean();
gpsBean.setId(Integer.parseInt(rs.getString(1)));
gpsBean.setObvdateTime(rs.getString(2));
gpsBean.setLongitude(Double.parseDouble(rs.getString(3)));
gpsBean.setLatitude(Double.parseDouble(rs.getString(4)));
gpsBean.setTecvalue(Double.parseDouble(rs.getString(5)));
list.add(gpsBean);
// System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5));
}
return list;
}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
return null;
}finally{
CommontDAO.cloes(rs);
}
}
//搜索（根据经度和纬度查询最近一点的记录）
public static List<GPSBean> selectGPS(Double latitude_, Double longitude_){
ResultSet rs = null;
List<GPSBean> list = new ArrayList<GPSBean>();
try{
//获取比指定点大的最近的1个值
String sql = "select id, to_char(obvdateTime,'YYYY-mm-dd HH24:MI:SS'), latitude, longitude, tecvalue, abs(latitude-"+latitude_+") a from gps order by a desc";
rs = CommontDAO.selectFile(sql);
while(rs.next()){
System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5)+","+rs.getString(6));
}
System.out.println("---------------------------------------------------------------------------");
sql = "select id, to_char(obvdateTime,'YYYY-mm-dd HH24:MI:SS'), latitude, longitude, tecvalue, abs(longitude-"+longitude_+") b from gps order by b desc";
rs = CommontDAO.selectFile(sql);
while(rs.next()){
// System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5));
System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5)+","+rs.getString(6));
}
System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
sql = "select id, to_char(obvdateTime,'YYYY-mm-dd HH24:MI:SS'), latitude, longitude, tecvalue, abs(latitude-"+latitude_+") a, abs(longitude-"+longitude_+") b from gps order by a asc, b asc";
//重要
// sql = "select id, to_char(obvdateTime,'YYYY-mm-dd HH24:MI:SS'), latitude, longitude, tecvalue, abs(latitude-"+latitude_+") a, abs(longitude-"+longitude_+") b from gps where obvdateTime>=TO_DATE('2011-03-01 00:00:00','YYYY-mm-dd HH24:MI:SS') and obvdateTime<=TO_DATE('2011-03-01 02:00:00','YYYY-mm-dd HH24:MI:SS') order by a asc, b asc";
while(rs.next()){
// System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5));
System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5)+","+rs.getString(6)+","+rs.getString(7));
}
return list;
}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
return null;
}finally{
CommontDAO.cloes(rs);
}
}
//搜索（根据经度和纬度查询最近一点的记录）
public static List<GPSBean> selectGPS(Double latitude_, Double longitude_, String begin, String end){
ResultSet rs = null;
List<GPSBean> list = new ArrayList<GPSBean>();
String sql = "select id, to_char(obvdateTime,'YYYY-mm-dd HH24:MI:SS'), latitude, longitude, tecvalue, abs(latitude-"+latitude_+") a, abs(longitude-"+longitude_+") b from gps where obvdateTime>=TO_DATE('"+begin+"','YYYY-mm-dd HH24:MI:SS') and obvdateTime<=TO_DATE('"+end+"','YYYY-mm-dd HH24:MI:SS') order by a asc, b asc";
rs = CommontDAO.selectFile(sql);
String test = "";
try{
while(rs.next()){
GPSBean gpsBean = new GPSBean();
gpsBean.setId(Integer.parseInt(rs.getString(1)));
gpsBean.setObvdateTime(rs.getString(2));
gpsBean.setLatitude(Double.parseDouble(rs.getString(3)));
gpsBean.setLongitude(Double.parseDouble(rs.getString(4)));
gpsBean.setTecvalue(Double.parseDouble(rs.getString(5)));
list.add(gpsBean);
// System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5)+","+rs.getString(6)+","+rs.getString(7));
// System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5));
// test += rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getString(5)+","+rs.getString(6)+","+rs.getString(7)+"\r\n";
}
// File f = new File("D:/test.txt");
// OutputStream out = new FileOutputStream(f,true);
// out.write(test.getBytes());
return list;
}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
return null;
}finally{
CommontDAO.cloes(rs);
}
}
//获取时间
public static List<GPSBean> selectTime(String sql){
ResultSet rs = null;
List<GPSBean> list = new ArrayList<GPSBean>();
try{
rs = CommontDAO.selectFile(sql);
while(rs.next()){
GPSBean gpsBean = new GPSBean();
gpsBean.setTime(rs.getString(1));
list.add(gpsBean);
}
return list;
}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
return null;
}finally{
CommontDAO.cloes(rs);
}
}
}
package com.wss;
public class GPSBean {
private int id;
private String obvdateTime;
private double longitude;
private double latitude;
private double tecvalue;
private String time;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getObvdateTime() {
return obvdateTime;
}
public void setObvdateTime(String obvdateTime) {
this.obvdateTime = obvdateTime;
}
public double getLongitude() {
return longitude;
}
public void setLongitude(double longitude) {
this.longitude = longitude;
}
public double getLatitude() {
return latitude;
}
public void setLatitude(double latitude) {
this.latitude = latitude;
}
public double getTecvalue() {
return tecvalue;
}
public void setTecvalue(double tecvalue) {
this.tecvalue = tecvalue;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
}
package com.wss;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.db.DBConnectionManager;
public class CommontDAO {
private static DBConnectionManager dbcm = DBConnectionManager.getInstance();
//insert语句（支持多条sql）
public static boolean insertFile(String sql){
Connection con = null;
Statement st = null;
try{
con = dbcm.getConnection();
st = con.createStatement();
sql = sql.substring(0, sql.length()-1);
String[] num = sql.split(";");
for(int i=0; i<num.length; i++){
st.addBatch(num[i]);
}
st.executeBatch();
return true;
}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
return false;
}finally{
close(con, st);
}
}
//select语句（返回一个ResultSet）
public static ResultSet selectFile(String sql){
Connection con = null;
Statement st = null;
ResultSet rs = null;
try{
con = dbcm.getConnection();
st = con.createStatement();
rs = st.executeQuery(sql);
return rs;
}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
return rs;
}
}
public static void close(Connection con){
try{
if(con != null){
con.close();
}
}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
}
}
public static void close(Connection con, Statement st){
try{
if(st != null){
st.close();
}
}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
}
close(con);
}
public static void close(Connection con, Statement st, ResultSet rs){
try{
if(rs != null){
rs.close();
}
}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
}
close(con, st);
}
public static void cloes(ResultSet rs){
try{
if(rs != null){
rs.close();
}
}catch(Exception e){
e.printStackTrace();
System.out.println(e.getMessage());
}
}
}
package com.wss;
public class Sort {
//求最大值
public static Double max(Double[] value){
//把数组第一个值最为一个标志
Double num1 = value[0];
//中间变量
Double temp = 0.0;
for(int i=1; i<value.length; i++){
if(num1 < value[i]){
temp = num1;
num1 = value[i];
value[i] = temp;
}
}
return num1;
}
//求最小值
public static Double min(Double[] value){
//把数组第一个值最为一个标志
Double num1 = value[0];
//中间变量
Double temp = 0.0;
for(int i=0; i<value.length; i++){
if(num1 > value[i]){
temp = num1;
num1 = value[i];
value[i] = temp;
}
}
return num1;
}
//求中值
public static Double center(Double[] value){
Double temp = 0.0;
//排序
for(int i=0; i<value.length; i++){
for(int j=i+1; j<value.length; j++){
if(value[i] > value[j]){
temp = value[j];
value[j] = value[i];
value[i] = temp;
}
}
}
//求中值所在的下标位置
int num = value.length/2;
return value[num];
}
public static void main(String[] args){
Double value[] = {4.0, 1.0, 2.0, 3.0};
System.out.println(Sort.center(value));
}
}