package DB;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import JavaBean.mapAddress_Info;

/*
 * 查询  ”地址数据库“
 * 这个类主要功能是为了方便查询  mapaddress 中  addressNum 对于的  地址编码
 *
 * */

public class DataBase_map_address {
 
 public static final String  DB_driver = "com.mysql.jdbc.Driver";
 public static final String  DB_url    ="jdbc:mysql://localhost:3306/test?characterEncoding=utf8";
 public static final String  DB_user   ="root";
 public static final String  DB_pass   ="root";
 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs =null;
 
 public DataBase_map_address() throws ClassNotFoundException, SQLException{
  Class.forName(this.DB_driver);
  this.conn = DriverManager.getConnection(this.DB_url,this.DB_user,this.DB_pass);
 }
 
 /*
  * 根据地址编码 得到 地址信息
  * */
 public String getAddress(String addressNum) throws SQLException{
  String sql ="select * from mapaddress where addressNum=? ";
  pstmt=conn.prepareStatement(sql);
  this.pstmt.setString(1, addressNum);
  this.rs = this.pstmt.executeQuery();
  if(rs.next()){
   return rs.getString(3);
  }else{
   return null;
  }
 }
 /*
  * 检查 地址编码是否存在
  * */
 public boolean checkAddressNum(String addressNum) throws SQLException{
  String sql ="select * from mapaddress where addressNum=? ";
  pstmt=conn.prepareStatement(sql);
  this.pstmt.setString(1, addressNum);
  this.rs = this.pstmt.executeQuery();
  if(rs.next()){
   return true;
  }else{
   return false;
  }
 }
 /*
  * 获取得到mapaddress数据库中数据的全部  编号。名称。经度。纬度
  * */
 public List<mapAddress_Info> getMapAddress_Info() throws SQLException{
  List<mapAddress_Info> list = new ArrayList<mapAddress_Info>();
  String sql = "select * from mapaddress";
  this.pstmt=this.conn.prepareStatement(sql);

  this.rs = this.pstmt.executeQuery();
  while(rs.next()){
   mapAddress_Info mapAddress = new mapAddress_Info();
   mapAddress.setMapAddressNum(rs.getString(2));
   mapAddress.setMapAddressName(rs.getString(3));
   mapAddress.setLon(rs.getString(4));
   mapAddress.setLat(rs.getString(5));
   list.add(mapAddress);
  }
  return list;
 }
 /*
  * 添加地理数据
  * */
 public boolean addMapAddress(String mapAddressNum, String mapAddressName, String mapAddressLon, String mapAddressLat) throws SQLException{
  //String sql="insert into mapaddress (addressNum,address,longitude,latitude) values (mapAddressNum,mapAddressName,mapAddressLon,mapAddressLat)" ;
  String sql="insert into mapaddress (addressNum,address,longitude,latitude) values (?,?,?,?)" ;
  this.pstmt = this.conn.prepareStatement(sql);
  this.pstmt.setString(1, mapAddressNum);
  this.pstmt.setString(2, mapAddressName);
  this.pstmt.setString(3, mapAddressLon);
  this.pstmt.setString(4, mapAddressLat);
  this.pstmt.executeUpdate();
  //this.pstmt.executeQuery();
  return true;
 }
 /*
  * 删除数据
  * */
 public boolean deleteMapAddress(String mapAddressNum) throws SQLException{
  String sql = "delete from mapaddress where addressNum = ?";
  this.pstmt = this.conn.prepareStatement(sql);
  this.pstmt.setString(1, mapAddressNum);
  this.pstmt.executeUpdate();
  return false;
 }

}