JDBC-MySQL代码例子-1

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

//我们组负责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);

}