JDBC-MySQL代码例子-4

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

 

/* Author: Tay Xiang Ren
 * Date last updated: 02/04/10
 * Company/Subsystem: MSI/DB/RMI
 *
 * Title: Implementation of schInterface. Contains full methods that sch will call from
 *    db.
 *
 */

import java.rmi.*;
import java.rmi.server.UnicastRemoteObject;
import java.io.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.*;
import org.w3c.dom.*;
import java.sql.*;
import java.util.*;
import javax.swing.JEditorPane;
import javax.swing.JTable;

public class sch_dbImpl extends UnicastRemoteObject implements sch_dbInterface
{
    static PrintStream p=null;
    static Connection con=null;
    static JEditorPane editorPane =null;
    static boolean first=false;
    static JTable table=null;

    public sch_dbImpl() throws RemoteException{
        getConnection();
    }
    public sch_dbImpl(PrintStream p,JEditorPane editorPane,JTable table) throws RemoteException{
        this.p=p;
        this.editorPane=editorPane;
        this.table=table;
        getConnection();
    }

    public boolean storeRequestGoLog(String mes) throws RemoteException,SQLException {
        java.util.Date now=new java.util.Date();
        java.sql.Timestamp t=new java.sql.Timestamp(now.getTime());
        p.println(t+"     "+mes+"/n");
        p.println();
        editorPane.setText(editorPane.getText()+"/n"+t+"     "+mes+"/n");
        return true;
    }

    public boolean storeBusMoveLog(String mes) throws RemoteException,SQLException {
       java.util.Date now=new java.util.Date();
       java.sql.Timestamp t=new java.sql.Timestamp(now.getTime());
        p.println(t+"     "+mes+"/n");
        p.println();
        editorPane.setText(editorPane.getText()+"/n"+new java.sql.Timestamp(now.getTime())+"     "+mes+"/n");
        return true;
    }

    public boolean storeLightChangelog(String mes) throws RemoteException,SQLException {
       java.util.Date now=new java.util.Date();
       java.sql.Timestamp t=new java.sql.Timestamp(now.getTime());
        p.println(t+"     "+mes+"/n");
        p.println();
        editorPane.setText(editorPane.getText()+"/n"+new java.sql.Timestamp(now.getTime())+"     "+mes+"/n");
        return true;
    }

    public boolean storeAllBusInfo(String o) throws RemoteException,SQLException {
        try {
                StringTokenizer busStrToken = new StringTokenizer(o,",");
                while (busStrToken.hasMoreTokens()){
                    int busId = Integer.parseInt(busStrToken.nextToken());
                    String destinationNodeId = busStrToken.nextToken();
                    double busSpeed = Double.parseDouble(busStrToken.nextToken());
                    int numOfPassenger = Integer.parseInt(busStrToken.nextToken());
                    int routeId = Integer.parseInt(busStrToken.nextToken());
                    long timeStart = Long.parseLong(busStrToken.nextToken());
                    java.util.Date now=new java.util.Date();
                    java.sql.Timestamp t=new java.sql.Timestamp(now.getTime());

                    PreparedStatement  psmnt=con.prepareStatement("select id from Bus where busid="+busId);
                    System.out.println(busId);
                    ResultSet rs = psmnt.executeQuery();
                    rs.next();
                    if(rs.isLast()) {
                        psmnt=con.prepareStatement("update Bus set destination=/""+destinationNodeId+"/",speed="+busSpeed+",currentPassenger="+numOfPassenger+",route_id="+routeId+",timestart="+timeStart+" where busid="+busId);
                        boolean s1 = psmnt.execute();
                            System.out.println("Method storeAllBusInfo update one row successfully!");

                    }
                    else{
                        psmnt=con.prepareStatement("insert into Bus(busid,destination,speed,currentPassenger,route_id,timestart,timestamp) values(?,?,?,?,?,?,?)");
                        psmnt.setString(1,busId+"");
                        psmnt.setString(2,destinationNodeId);
                        psmnt.setDouble(3,busSpeed);
                        psmnt.setInt(4,numOfPassenger);
                        psmnt.setString(5,routeId+"");
                        psmnt.setString(6,timeStart+"");
                        psmnt.setTimestamp(7,t);

                        int s2 = psmnt.executeUpdate();

                        PreparedStatement psmnt1=con.prepareStatement("select max(id) from ");

                        if(s2 > 0){
                            System.out.println("Method storeAllBusInfo insert one row successfully!");
                        }
                        else{
                            System.out.println("Method storeAllBusInfo failed inserting one row successfully!");
                        }
                    }

                    psmnt.close();
                }
            return true;
        }
        catch (Exception e)
        {
            e.printStackTrace();
            return false;
        }
    }

    public boolean storeAllTLInfo(String o) throws RemoteException,SQLException {
        try {
            StringTokenizer TLStrToken = new StringTokenizer(o,",");
            while (TLStrToken.hasMoreTokens()) {

                int trafficLightId = Integer.parseInt(TLStrToken.nextToken());
                boolean status = Boolean.parseBoolean(TLStrToken.nextToken());
                java.util.Date now=new java.util.Date();
                java.sql.Timestamp t=new java.sql.Timestamp(now.getTime());

                PreparedStatement psmnt=con.prepareStatement("select id from TrafficLightlog where trafficLightid="+trafficLightId);
                ResultSet rs = psmnt.executeQuery();
                System.out.println(trafficLightId);
                rs.next();
                if(rs.isLast())
                {
                   psmnt=con.prepareStatement("update trafficlightlog set status=/'"+status+"/', timestamp=/'"+t+"/' where trafficlightid="+trafficLightId);
//update trafficlightlog set status='12'where trafficlightid=1;
                    boolean is=psmnt.execute();

                    System.out.println("Method storeAllTLInfo update one row successfully!");
                }
                else{
                    psmnt=con.prepareStatement("insert into TrafficLightlog(trafficLightid,status,timestamp) values(?,?,?)");
                    psmnt.setString(1,trafficLightId+"");
                    psmnt.setString(2,status+"");
                    psmnt.setTimestamp(3, t);

                    int s2=psmnt.executeUpdate();

                    if(s2>0){
                        System.out.println("Method storeAllTLInfo insert one row successfully!");
                    }
                    else{
                        System.out.println("Method storeAllTLInfo failed inserting one row successfully!");
                    }
                }

                psmnt.close();

            }
            return true;
        }
        catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean storeSystemcrashLog(String o) throws RemoteException,SQLException {
        java.util.Date now=new java.util.Date();
       java.sql.Timestamp t=new java.sql.Timestamp(now.getTime());
        p.println(t+"     "+o+"/n");
        p.println();
        editorPane.setText(editorPane.getText()+"/n"+new java.sql.Timestamp(now.getTime())+"      "+o+"/n");
        return true;
    }

    public boolean storeRouteInfo(String o) throws RemoteException,SQLException {
        try {
            StringTokenizer RouteStrToken = new StringTokenizer(o,",");
            while (RouteStrToken.hasMoreTokens()) {
                int routeId = Integer.parseInt(RouteStrToken.nextToken());
  double routeDis = Double.parseDouble(RouteStrToken.nextToken());

                PreparedStatement psmnt=con.prepareStatement("insert into ReportGeneration(route_id,route_dis) values(?,?)");
                psmnt.setString(1,routeId+"");
                psmnt.setDouble(2,routeDis);

                int s2=psmnt.executeUpdate();

                if(s2>0){
                    System.out.println("Method storeRouteInfo insert one row successfully!");
                }
                else{
                    System.out.println("Method storeRouteInfo failed inserting one row successfully!");
                }
                psmnt.close();
            }
            return true;
        }
        catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

 /*********************send methods**********************************/

 public byte[] getCmeRouteFile() throws RemoteException
 {
        try{
             ResultSet rs = null;
             PreparedStatement psmnt=null;

             psmnt=con.prepareStatement("select xml from xml where type=(select map_selecting from setup) and name='cme.xml'");

             rs=psmnt.executeQuery();

             rs.next();
                 if(rs.isLast()){
                         System.out.println("NOT READY YET");
                     Blob blob=rs.getBlob("xml");

                     InputStream inStream=blob.getBinaryStream();
                     int length=-1;
                     int size=(int)blob.length();
                     byte [] bytefile=new byte[size];

                     inStream.read(bytefile);

                     System.out.println("Method getCmeRouteFile() successfully!");
                     psmnt.close();
                     return bytefile;
             }
             psmnt.close();
             return null;
       }catch(Exception e) {
  e.printStackTrace();
  return null;
        }
 }
 
 public byte[] getCmeTLFile() throws RemoteException
 {
        try{
            //remember to edit the pathway
      ResultSet rs = null;
             PreparedStatement psmnt=null;

             psmnt=con.prepareStatement("select xml from xml where type=(select map_selecting from setup) and name='cmetraffic.xml'");

             rs=psmnt.executeQuery();

             rs.next();
             Blob blob=rs.getBlob("xml");

             InputStream inStream=blob.getBinaryStream();
             int length=-1;
             int size=(int)blob.length();
             byte [] bytefile=new byte[size];

             inStream.read(bytefile);

             System.out.println("Method getCmeRouteFile() successfully!");
             psmnt.close();
             return bytefile;
      }
      catch(Exception e) {
    e.printStackTrace();
    return null;
     }
 }

 public byte[] getSetUpData() throws RemoteException {
        try {
            ResultSet rs = null;
            PreparedStatement psmnt=con.prepareStatement
                        ("Select noofbus, Passenger,freqDispatchDay,timeIntervalDay,freqDispatchNight,timeIntervalNight,busSpeednormal,busMaxSpeed from setup ");

            rs=psmnt.executeQuery();
            rs.next();
            int numBus = rs.getInt("noofbus");
            int maxBusPass =rs.getInt("Passenger");
            int dpDayFreq=rs.getInt("freqDispatchDay");
            int tlDayInt=rs.getInt("timeIntervalDay");
            int dpNightFreq=rs.getInt("freqDispatchNight");
            int tlNightInt=rs.getInt("timeIntervalNight");
            Double busSpd=rs.getDouble("busSpeednormal");
            Double busMaxSpd=rs.getDouble("busMaxSpeed");

            String numberOfBus = Integer.toString(numBus);
            String busSpeed = Double.toString(busSpd);
            String busMaxSpeed = Double.toString(busMaxSpd);
            String tLDayInterval = Integer.toString(tlDayInt);
            String tLNightInterval = Integer.toString(tlNightInt);
            String dispatchDayFreq = Integer.toString(dpDayFreq);
            String dispatchNightFreq = Integer.toString(dpNightFreq);
            String maxBusPassenger = Integer.toString(maxBusPass);

            createSchSuXMLFile(numberOfBus, busSpeed, busMaxSpeed, tLDayInterval, tLNightInterval, dispatchDayFreq, dispatchNightFreq, maxBusPassenger);

            FileInputStream fin = new FileInputStream("D://CPE207//sch//schedulersu.xml");
            byte[] test = new byte[fin.available()];

            fin.read(test);
            return test;

        }
        catch(Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void createSchSuXMLFile(String numberOfBus, String busSpeed, String busMaxSpeed, String tLDayInterval, String tLNightInterval, String dispatchDayFreq, String dispatchNightFreq, String maxBusPassenger) throws Exception {
        try {
            String root = "scheduler";
            DocumentBuilderFactory documentBuilderFactory =
                                       DocumentBuilderFactory.newInstance();
            DocumentBuilder documentBuilder =
                                  documentBuilderFactory.newDocumentBuilder();
            Document document = documentBuilder.newDocument();
            Element rootElement = document.createElement(root);
            document.appendChild(rootElement);
          int numOfAttr = 8;
            String row = "row";
            Element rw = document.createElement(row);
          for (int j = 0; j < numOfAttr; j++) {
                if (j % numOfAttr == 0) {
                    String element = "num_bus";
                    Element em = document.createElement(element);
                    String data = numberOfBus;
                    em.appendChild(document.createTextNode(data));
                    rw.appendChild(em);
                }
                if (j % numOfAttr == 1) {
                    String element = "bus_speed";
                    Element em = document.createElement(element);
                    String data = busSpeed;
                    em.appendChild(document.createTextNode(data));
                    rw.appendChild(em);
                }
              if (j % numOfAttr == 2) {
                    String element = "bus_max_speed";
                    Element em = document.createElement(element);
                    String data = busMaxSpeed;
                    em.appendChild(document.createTextNode(data));
                    rw.appendChild(em);
              }
                if (j % numOfAttr == 3) {
                    String element = "tl_day_interval";
                    Element em = document.createElement(element);
                    String data = tLDayInterval;
                    em.appendChild(document.createTextNode(data));
                    rw.appendChild(em);
              }
              if (j % numOfAttr == 4) {
                    String element = "tl_night_interval";
                    Element em = document.createElement(element);
                    String data = tLNightInterval;
                    em.appendChild(document.createTextNode(data));
                    rw.appendChild(em);
              }
              if (j % numOfAttr == 5) {
                    String element = "dispatch_day_freq";
                    Element em = document.createElement(element);
                    String data = dispatchDayFreq;
                    em.appendChild(document.createTextNode(data));
                    rw.appendChild(em);
              }
              if (j % numOfAttr == 6) {
                    String element = "dispatch_night_freq";
                    Element em = document.createElement(element);
                    String data = dispatchNightFreq;
                    em.appendChild(document.createTextNode(data));
                    rw.appendChild(em);
              }
              if (j % numOfAttr == 7) {
                    String element = "max_bus_passenger";
                    Element em = document.createElement(element);
                    String data = maxBusPassenger;
                    em.appendChild(document.createTextNode(data));
                    rw.appendChild(em);
              }
                rootElement.appendChild(rw);
            }
            TransformerFactory transformerFactory = TransformerFactory.newInstance();
            Transformer transformer = transformerFactory.newTransformer();
            DOMSource source = new DOMSource(document);
       File file = new File("D://CPE207//sch//schedulersu.xml");
            StreamResult result =  new StreamResult(file);
            transformer.transform(source, result);

        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void getConnection()
    {
        String connectionURL="jdbc:mysql://127.0.0.1:3306/PBSS";
        try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            con=DriverManager.getConnection(connectionURL, "root","instance");
            System.out.println("SCH Connected successfully!/n");
        }
        catch(Exception ex){
            System.out.println("Found some error: "+ex);
        }
     }

   /**********************************************************************************/

   }