dbbeancarinfo.java

实现网络GPS定位车辆的位置

package jspreg;
//[e-mail]
import java.sql.*;

//import oracle.jdbc.driver.OracleDriver;
//import oracle.sql.CharacterSet.*;
import java.util.*;
import java.util.Date;
import java.net.*;
import java.io.*;
import java.text.*;
import java.util.Vector;
//import oracle.jdbc.driver.*;


public class dbBeanCarInfo {
//  static final String DBDriver  ="sun.jdbc.odbc.JdbcOdbcDriver";
//  static final String DBDriver  ="sun.jdbc.odbc.JdbcOdbcDriver";
//  static final String strConn   ="jdbc:odbc:JspDB";
//  static final String DBusername="sa";
//  static final String DBpassword="djava";
  private Connection dbCon;
  private Statement  sqlStatement;
  private PreparedStatement pstmt;
  private UserInfo userInfo;
  private com_UserInfo com_userInfo;
  //private String userName=null;

  float centerLati=39.907638f;
  float centerLong=116.35295f;

  public dbBeanCarInfo(){
    try {
//          DriverManager.registerDriver((Driver)(Class.forName(DBDriver).newInstance()));
//          dbCon=DriverManager.getConnection(strConn , DBusername, DBpassword);

         //DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//         DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//         dbCon = DriverManager.getConnection ("jdbc:oracle:thin:@192.168.0.163:1521:GPSDATA",
//                           "test", "test");
         //sqlStatement=dbCon.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
         //sqlStatement=dbCon.createStatement();
         //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         //dbCon=DriverManager.getConnection("jdbc:odbc:Gps","sa","");
         Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
         String url="jdbc:microsoft:sqlserver://192.110.0.20:1433;DatabaseName=SubGPS";
        //mydb为数据库
         String user="gps";
         String password="topeak";
         dbCon= DriverManager.getConnection(url,user,password);

        sqlStatement=dbCon.createStatement();
    }
    catch (Exception e) {
        e.printStackTrace();
    }

  }


  public void setMessage(UserInfo msg)
          {
                  this.userInfo=msg;
          }
   public void setCom_UserInfo(com_UserInfo msg)
         {
           this.com_userInfo=msg;
         }


  /**
   * 修改密码
   * @param ID
   * @param pwd
   * @return
   * @throws java.sql.SQLException
   */
  //修改密码
  public int changePwd(String cardid,String pwd) throws java.sql.SQLException
  {
    int flag=0;
    try{
        String sql="update CARMASTER set PASSWORD = '"+pwd+"' where CARDID='"+cardid+"'";
        flag=sqlStatement.executeUpdate(sql);
    }catch (Exception e) {
      System.out.println("修改密码失败："+e.getMessage());
    }
    return flag;
  }
  //用cardid取得公司名字
  public String getCompanyName(String cardid,String companyid){
       ResultSet rs;
        rs = null;
        String companyName=null;
        if(!cardid.equals("")){
          try {
            String sql;
            sql = "select COMPANYNAME from COMPANY where COMPANYID=(select companyid from carmaster where cardid='" +
                cardid + "')";
            System.out.println(sql);
            rs = sqlStatement.executeQuery(sql);
            if (rs.next()) {
              companyName = rs.getString("COMPANYNAME");
              System.out.println("this is the companyName: " + companyName);
            }
          }
          catch (Exception e) {
            System.out.println("无此公司" + e);
          }
        }
        else if(!companyid.equals("")){
          try {
            String sql;
            sql = "select COMPANYNAME from COMPANY where COMPANYID='"+companyid + "'";
            System.out.println(sql);
            rs = sqlStatement.executeQuery(sql);
            if (rs.next()) {
              companyName = rs.getString("COMPANYNAME");
              System.out.println("this is the companyName: " + companyName);
            }
          }
          catch (Exception e) {
            System.out.println("无此公司" + e);
          }

        }

    return companyName;
  }



  //取得所有公司信息
  public ResultSet getAll()
  {
    ResultSet rs=null;
    try
    {
    String sql="select company.companyid,company.companyname,carmaster.username from COMPANY  left join CARMASTER  on carmaster.companyid=company.companyid and carmaster.ismanager=1 where company.companyid!='0000' order by companyid"; //USERNAME='"+user+"' and PASSWORD='"+password+"' and WEBTYPE='1'";
    rs=sqlStatement.executeQuery(sql);
    }catch(Exception e){
      e.printStackTrace();
    }
    return rs;
  }
  //取得公司信息
  public ResultSet getCompanyInfo(String companyid)
  {
    ResultSet rs=null;
    try
    {
      String sql="select companyid,companyname,description from company where companyid='"+companyid+"'";
      rs=sqlStatement.executeQuery(sql);
    }catch(Exception e){
      e.printStackTrace();
    }
    return rs;
  }
  //验证

  public ResultSet checkLogin(String user_SIMNum,String password) {
    ResultSet rs=null;
    try{
   String sql="select * from  customer c,driver d,vehicle v where c.UserID=v.UserID and d.CarPlate=v.CarPlate and v.SIMNum = '"+user_SIMNum+"'"+
    "AND ( d.password = '"+password+"' OR c.password='"+password+"')";
//      String sql= "select Password from Customer ,Driver where Customer.SIMNum=Driver.SIMNum and SIMNum='user_SIMNum'";
//        String sql="select * from CARMASTER where USERNAME='"+user+"' and PASSWORD='"+password+"' and WEBTYPE='1'";
        //String sql="select * from CARMASTER where USERNAME='"+user+"' and PASSWORD='"+password+"'";
        System.out.println(sql);
        //int message_count=0;
        rs = sqlStatement.executeQuery(sql);
        System.out.println("after execute checklogin!!!");
    }catch (Exception e) {
        System.out.println("CHECK LOGIN："+e.getMessage());
    }
    return rs;
  }

  //列出个人信息
  public ResultSet listPerson(String user_SIMNum){
    ResultSet rs =null;
    try{
        System.out.println(user_SIMNum);
//        String sql="select * from CARMASTER where CARDID='"+cardid+"'";
        String sql= "SELECT VEHICLE.SIMNum, VEHICLE.CarPlate,VEHICLE.Style, VEHICLE.Color, CUSTOMER.Name, CUSTOMER.Type, NEWDATA.Msg FROM VEHICLE INNER JOIN "+
        " CUSTOMER ON VEHICLE.UserID = CUSTOMER.UserID LEFT OUTER JOIN NEWDATA ON VEHICLE.SIMNum = NEWDATA.SIMNum WHERE VEHICLE.SIMNum = '"+user_SIMNum+"'";
        System.out.println(sql);
        rs = sqlStatement.executeQuery(sql);
    }catch (Exception e) {
        System.out.println("listPerson执行SQL失败："+e.getMessage());
    }
    return rs;
  }

  //列出公司信息
    public ResultSet listCompany(String companyid){
        ResultSet rs=null;
        try{
            String sql="select * from CARMASTER where COMPANYID='"+companyid+"'";
            rs=sqlStatement.executeQuery(sql);
        }catch(Exception e){
            System.out.println("listCompany执行失败："+e.getMessage());
        }
        return rs;
    }
    //增加公司
    public boolean addCompany(String companyid,String companyname,String des)throws Exception
    {
      String comName=convertToChinese(companyname);
      String comDes=convertToChinese(des);
      try
      {
        String sql="insert into COMPANY values('"+companyid+"','"+comName+"','"+comDes+"')";
        System.out.println(sql);
        sqlStatement.execute(sql);
      }catch(Exception e){
        System.out.println("addcompany 执行失败: "+e.getMessage());
        return false;
      }
      return true;
    }
    //修改公司
    public boolean alterCompany(String companyid,String companyname,String des)throws Exception
    {
      try
      {
        System.out.println(" fucking companyname: in bean "+companyname);
        String companyName=convertToChinese(companyname);
         System.out.println(" after convert fucking companyname: in bean "+companyName);
        String desc=convertToChinese(des);
        String sql="update COMPANY set COMPANYNAME='"+companyName+"',DESCRIPTION='"+desc+"' where companyid='"+companyid+"'";
        System.out.println(sql);
        sqlStatement.execute(sql);
      }catch(Exception e){
        System.out.println("altercompany 执行失败: "+e.getMessage());
        return false;
      }
      return true;
    }

    //删除公司
    public boolean delCompany(String companyid)throws Exception
    {
      try
      {
        String sql="delete from COMPANY where companyid='"+companyid+"'";
        System.out.println(sql);
        sqlStatement.execute(sql);
      }catch(Exception e){
        System.out.println("delete company 执行失败! "+e.getStackTrace());
        return false;
      }