甜咖啡

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));
 }
}