JDBC-MySQL代码例子-1

来源:互联网 发布:淘宝客开通步骤 编辑:程序博客网 时间:2024/06/16 00:54

//我们组负责Database,所以PBSS System Database全部由我们开发.Here's for Control Center

/* Author: Chua Kian Siong
 * Date last updated: 12/04/10
 * Company/Subsystem: MSI/DB/RMI
 *
 * Title: Implementation of ccInterface. Contains full methods that cc will call from
 *    db.
 *
 */

   import java.io.*;
   import java.rmi.*;
   import java.rmi.server.UnicastRemoteObject;
   import java.util.*;
   import java.sql.*;
   import java.io.*;
   import java.lang.*;
   import java.io.File;
import java.util.logging.Level;
import java.util.logging.Logger;
   import javax.xml.parsers.*;
   import javax.xml.transform.*;
   import javax.xml.transform.dom.*;
   import javax.xml.transform.stream.*;
   import org.w3c.dom.*;


    public class cc_dbImpl extends UnicastRemoteObject implements cc_dbInterface
   {
      static  PreparedStatement psmnt=null;
      static Connection con=null;

      public cc_dbImpl() throws RemoteException{
          getConnection();
      } //constructor
      
      public byte[] getInitialData() throws RemoteException, TransformerConfigurationException, TransformerException, SQLException, ParserConfigurationException
      {
         String row, data, element1, element2;
         Element rw0, rw1, rw2, rw3, em1, em2;
         Statement s=con.createStatement();
     
         ResultSet rs=s.executeQuery("select * from setup");
         rs.next();
         String root = "Display";
         DocumentBuilderFactory documentBuilderFactory =
                                       DocumentBuilderFactory.newInstance();
         DocumentBuilder documentBuilder =
                                  documentBuilderFactory.newDocumentBuilder();
         Document document = documentBuilder.newDocument();
         Element rootElement = document.createElement(root);
         document.appendChild(rootElement);
     
         row = "row";
         rw0 = document.createElement(row);
     
         element1 = "tableName";
         em1 = document.createElement(element1);
         data = "Passenger";
         em1.appendChild(document.createTextNode(data));
         rw0.appendChild(em1);
     
         element2 = "NoOfPassenger";
         em2 = document.createElement(element2);
         data = Integer.toString(rs.getInt("passenger"));
         em2.appendChild(document.createTextNode(data));
         rw0.appendChild(em2);
     
         rootElement.appendChild(rw0);
     
         row = "row";
         rw1 = document.createElement(row);
     
         element1 = "tableName";
         em1 = document.createElement(element1);
         data = "timeOfDay";
         em1.appendChild(document.createTextNode(data));
         rw1.appendChild(em1);
     
         element2 = "timeOfDay";
         em2 = document.createElement(element2);
         data = rs.getString("timeOfDay");
         em2.appendChild(document.createTextNode(data));
         rw1.appendChild(em2);
     
         rootElement.appendChild(rw1);
     
         row = "row";
         rw2 = document.createElement(row);
     
         element1 = "tableName";
         em1 = document.createElement(element1);
         data = "Bus";
         em1.appendChild(document.createTextNode(data));
         rw2.appendChild(em1);
     
         element2 = "noOfBus";
         em2 = document.createElement(element2);
         data = Integer.toString(rs.getInt("noOfBus"));
         em2.appendChild(document.createTextNode(data));
         rw2.appendChild(em2);
     
         rootElement.appendChild(rw2);
     
         row = "row";
         rw3 = document.createElement(row);
     
         element1 = "tableName";
         em1 = document.createElement(element1);
         data = "FreqOfBusDispatch";
         em1.appendChild(document.createTextNode(data));
         rw3.appendChild(em1);
     
         element2 = "FreqOfBusDispatch";
         em2 = document.createElement(element2);
         data = Integer.toString(rs.getInt("FreqDispatchday"));
         em2.appendChild(document.createTextNode(data));
         rw3.appendChild(em2);
     
         rootElement.appendChild(rw3);

         row = "row";
         rw3 = document.createElement(row);

         element1 = "tableName";
         em1 = document.createElement(element1);
         data = "fareprice";
         em1.appendChild(document.createTextNode(data));
         rw3.appendChild(em1);

         element2 = "fareprice";
         em2 = document.createElement(element2);
         data = Double.toString(rs.getFloat("fareprice"));
         em2.appendChild(document.createTextNode(data));
         rw3.appendChild(em2);

         rootElement.appendChild(rw3);

         row = "row";
         rw3 = document.createElement(row);

         element1 = "tableName";
         em1 = document.createElement(element1);
         data = "gasprice";
         em1.appendChild(document.createTextNode(data));
         rw3.appendChild(em1);

         element2 = "gasprice";
         em2 = document.createElement(element2);
         data = Double.toString(rs.getFloat("gasprice"));
         em2.appendChild(document.createTextNode(data));
         rw3.appendChild(em2);

         rootElement.appendChild(rw3);
     
         TransformerFactory transformerFactory = TransformerFactory.newInstance();
         Transformer transformer = transformerFactory.newTransformer();
         DOMSource source = new DOMSource(document);
         File file = new File("D://CPE207//cc//DBtoCC.xml");
         StreamResult result =  new StreamResult(file);
         transformer.transform(source, result);
         //createXMLFileForCC(rs.getInt("noOfPassenger"),rs.getString("timeOfDay"),rs.getInt("noOfBus"),rs.getInt("FreqOfBusDispatch")); // xml file created at DB side to send to CC
         System.out.println("OK");
         try{
            FileInputStream fin = new FileInputStream(file);
            byte[] sendData = new byte[fin.available()];
            fin.read(sendData);
           
           
           
            return sendData;
         }
             catch(Exception e)
            {
               e.printStackTrace();
               return null;
            }
    
      }
  
       public String transferIp(String subSystemIp)throws RemoteException, SQLException
      {
         StringTokenizer str = new StringTokenizer(subSystemIp, " ");
         String from, address;
       
         for (int i=0;i<7;i++)
         {
            psmnt=con.prepareStatement
                        ("insert into cc(nameOfSubsystem,ipaddress) values(?,?)" );
            from = str.nextToken();
            System.out.print(from + " = ");
            psmnt.setString(1,from);
            address = str.nextToken();
            psmnt.setString(2,address);
            System.out.println(address);
            psmnt.executeUpdate();
            psmnt.close();
         }

      
         return "true";
      }
       public boolean storeIpAdd(byte[] ipAddB) throws RemoteException
      {
         try
         {
            FileOutputStream ipAdd=new FileOutputStream("D://CPE207//cc//cc.xml");
            ipAdd.write(ipAddB);
            File file=new File("D://CPE207//cc//cc.xml");
            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            DocumentBuilder db = dbf.newDocumentBuilder();
            Document document = db.parse(file);
            document.getDocumentElement().normalize();
            System.out.println("Root element "+document.getDocumentElement().getNodeName());
            NodeList node = document.getElementsByTagName("row");
            System.out.println("Information of the CC");
        
            for (int i = 0; i < node.getLength(); i++)
            {
               psmnt=con.prepareStatement
                        ("insert into cc(nameOfSubsystem,ipaddress) values(?,?)" );
               Node firstNode = node.item(i);
           
               if (firstNode.getNodeType() == Node.ELEMENT_NODE)
               {
                  Element element = (Element) firstNode;
                  NodeList fromList = element.getElementsByTagName("from");
                  Element fromElement = (Element) fromList.item(0);
                  NodeList  from =  fromElement.getChildNodes();
                  String str1=from.item(0).getNodeValue();
                  System.out.print("From: "+ str1  + "; ");
              
                  psmnt.setString(1,str1 );
              
                  NodeList addressList = element.getElementsByTagName("address");
                  Element addressElement = (Element)addressList.item(0);
                  NodeList  address =  addressElement.getChildNodes();
                  System.out.println("Address: "+  address.item(0).getNodeValue());
                  psmnt.setString(2,address.item(0).getNodeValue());
               }
               int s=psmnt.executeUpdate();
           
               if(s>0){
                  System.out.println("Uploaded successfully!");
               }
               else{
                  System.out.println("Unsuccessfully to upload");
               }
           
               psmnt.close();
            }
            return true;
         }
         catch (Exception e) {
               e.printStackTrace();
               return false;
         }
      }

       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("CC Connected successfully!/n");
        }
        catch(Exception ex){
            System.out.println("Found some error in method getConnection: "+ex);
        }
    }

       public byte[] system_Stop(String timeElapsed) throws RemoteException
       {
           //HH:MM
           Report report= null;
           FileInputStream fin = null;
           byte[] reportData = null;
        try {
            report = new Report(timeElapsed);
        } catch (SQLException ex) {
            Logger.getLogger(cc_dbImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        String fileName =  report.GenerateReport();

        if (fileName != null)
        {
            try
         {
             MailClient client = new MailClient();
             String[] filenames = {fileName};
             //{"d://MSI Report.pdf"};

             client.sendMail(filenames);

            
             fin = new FileInputStream(fileName);
             reportData = new byte[fin.available()];
             fin.read(reportData);
         }
         catch(Exception e)
         {
             e.printStackTrace(System.out);
             return null;
         }
        }
        return reportData;
   }

       public void clearAllTables() throws SQLException{
        Statement statement=null;
        String TrafficLight = "delete from TrafficLight";
        statement.execute(TrafficLight);

        String BusStop = "delete from busStop";
        statement.execute(BusStop);

        String report="delete from reportgeneration";
        statement.execute(report);

        String Bus = "delete from  Bus";
        statement.execute(Bus);
        String SetUp = "delete from SetUp";
        statement.execute(SetUp);
        String TrafficLightlog = "delete from TrafficLightlog";
        statement.execute(TrafficLightlog);
        String RequestGolog = "delete from RequestGolog";
        statement.execute(RequestGolog);
        String Map = "delete from Map";
        statement.execute(Map);
        String Navi = "delete from navi";
        statement.execute(Navi);
        String cc = "delete from cc";
        statement.execute(cc);
        String passtravelledonroute = "delete from passgtravelledonroute";
        statement.execute(passtravelledonroute);
        String route = "delete from route0";
        statement.execute(route);
        String route0 = "delete from route1";
        statement.execute(route0);
        String route1 = "delete from route2";
        statement.execute(route1);
        String route2 = "delete from route3";
        statement.execute(route2);
        String route3 = "delete from route4";
        statement.execute(route3);
        String route4 = "delete from route5";
        statement.execute(route4);
        String route5 = "delete from route6";
        statement.execute(route5);
        String route6 = "delete from route7";
        statement.execute(route6);
        String route7 = "delete from route8";
        statement.execute(route7);
        String route8 = "delete from route9";
        statement.execute(route8);
        String route9 = "delete from route10";
        statement.execute(route9);
        String route10 = "delete from route11";
        statement.execute(route10);
        String xml = "delete from xml";
        statement.execute(BusStop);

     }

    }