JDBC-MySQL代码例子-2

//Here's for City Map Editor

import java.io.*;
import java.rmi.*;
import java.rmi.server.*;
import java.net.*;
import java.io.OutputStream;
import java.io.File;
import java.io.InputStream;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
import java.util.StringTokenizer;

public class cme_dbImpl extends UnicastRemoteObject implements cme_dbInterface
{
    static Connection con=null;
    static PreparedStatement psmnt=null;

 public cme_dbImpl() throws RemoteException{
            getConnection();
        }

 public OutputStream getOutputStream(File f) throws IOException
 {
  return new RMIOutputStream(new RMIOutputStreamImpl(new
      FileOutputStream(f)));
 }

 public InputStream getInputStream(File f) throws IOException
 {
     return new RMIInputStream(new RMIInputStreamImpl(new
      FileInputStream(f)));
 }

      

 public String sayHello()
 {
            return "Hello world";
        }

         public void indicateTransDone() throws RemoteException, SQLException, FileNotFoundException, ParserConfigurationException, SAXException, IOException{
            getConnection();

            int numOfMaps;
            String nameOfMaps1,nameOfMaps2,nameOfMaps3,nameOfMaps4,nameOfMaps5,nameOfMaps6,nameOfMaps7,nameOfMaps8;
            String nameOfMaps9,nameOfMaps10;

            File file = new File("D://CPE207//cme//List.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("CME_Map_Info");
            System.out.println("Information of the CME");

            for (int i = 0; i < node.getLength(); i++) {
                Node firstNode = node.item(i);

                if (firstNode.getNodeType() == Node.ELEMENT_NODE) {

                    Element element = (Element) firstNode;

                    NodeList NumOfMapsElementList = element.getElementsByTagName("NumOfMaps");
                    Element NumOfMapsElement = (Element)NumOfMapsElementList.item(0);
                    NodeList NumOfMaps =NumOfMapsElement.getChildNodes();
                    System.out.println("NumOfMaps: "+ NumOfMaps.item(0).getNodeValue());
                    numOfMaps =  Integer.parseInt(NumOfMaps.item(0).getNodeValue());

                    for(int j = 1; j <= numOfMaps; j++){
                        switch(j){
                            case 1:
                                NodeList NameOfMaps1ElementList = element.getElementsByTagName("NameOfMaps1");
                                Element NameOfMaps1Element = (Element)NameOfMaps1ElementList.item(0);
                                NodeList NameOfMaps1 =NameOfMaps1Element.getChildNodes();

                                StringTokenizer st1 = new StringTokenizer((NameOfMaps1.item(0).getNodeValue()), ".");
                                String token1 = st1.nextToken();
                                nameOfMaps1 =  token1;
                                System.out.println("NameOfMaps1: "+ nameOfMaps1);

                                psmnt=con.prepareStatement("select id from Map where Map_Name=/""+nameOfMaps1+"/"");
                                ResultSet rs1=psmnt.executeQuery();

                                if(rs1.isLast()){
                                    System.out.println(nameOfMaps1+" is already stored.");
                                }
                                else{
                                    psmnt=con.prepareStatement("insert into Map(Map_Name, Map_Binary, Map_Screenshot) values(?,?,?)");

                                    psmnt.setString(1,nameOfMaps1);

                                    File image1=new File("D://cpe207//cme//"+nameOfMaps1+".udk");
                                    FileInputStream fis1=new FileInputStream(image1);
                                    psmnt.setBinaryStream(2,fis1);

                                    image1=new File("D://cpe207//cme//"+nameOfMaps1+".jpg");
                                    fis1=new FileInputStream(image1);
                                    psmnt.setBinaryStream(3, fis1);

                                    int s1=psmnt.executeUpdate();

                                    if(s1>0){
                                        System.out.println("Map1 uploaded successfully!");
                                    }
                                    else{
                                        System.out.println("Map1 failed uploading");
                                    }
                                    psmnt.close();
                                }

                                break;
                            case 2:
                                NodeList NameOfMaps2ElementList = element.getElementsByTagName("NameOfMaps2");
                                Element NameOfMaps2Element = (Element)NameOfMaps2ElementList.item(0);
                                NodeList NameOfMaps2 = NameOfMaps2Element.getChildNodes();

                                StringTokenizer st2 = new StringTokenizer((NameOfMaps2.item(0).getNodeValue()), ".");
                                String token2 = st2.nextToken();
                                nameOfMaps2 =  token2;
                                System.out.println("NameOfMaps2: "+ nameOfMaps2);

                                psmnt=con.prepareStatement("select id from Map where Map_Name=/""+nameOfMaps2+"/"");
                                ResultSet rs2=psmnt.executeQuery();

                                if(rs2.isLast()){
                                    System.out.println(nameOfMaps2+" is already stored.");
                                }
                                else{
                                    psmnt=con.prepareStatement("insert into Map(Map_Name, Map_Binary, Map_Screenshot) values(?,?,?)");

                                    psmnt.setString(1,nameOfMaps2);

                                    File image2=new File("D://cpe207//cme//"+nameOfMaps2+".udk");
                                    FileInputStream fis2=new FileInputStream(image2);
                                    psmnt.setBinaryStream(2,fis2);

                                    image2=new File("D://cpe207//cme//"+nameOfMaps2+".jpg");
                                    fis2=new FileInputStream(image2);
                                    psmnt.setBinaryStream(3, fis2);

                                    int s2=psmnt.executeUpdate();

                                    if(s2>0){
                                        System.out.println("Map3 uploaded successfully!");
                                    }
                                    else{
                                        System.out.println("Map3 failed uploading");
                                    }
                                    psmnt.close();
                                }
                                break;

                            case 3:
                                NodeList NameOfMaps3ElementList = element.getElementsByTagName("NameOfMaps3");
                                Element NameOfMaps3Element = (Element)NameOfMaps3ElementList.item(0);
                                NodeList NameOfMaps3 = NameOfMaps3Element.getChildNodes();

                                StringTokenizer st3 = new StringTokenizer((NameOfMaps3.item(0).getNodeValue()), ".");
                                String token3 = st3.nextToken();
                                nameOfMaps3 =  token3;
                                System.out.println("NameOfMaps3: "+ nameOfMaps3);

                                psmnt=con.prepareStatement("select id from Map where Map_Name=/""+nameOfMaps3+"/"");
                                ResultSet rs3=psmnt.executeQuery();

                                if(rs3.isLast()){
                                    System.out.println(nameOfMaps3+" is already stored.");
                                }
                                else{
                                    psmnt=con.prepareStatement("insert into Map(Map_Name, Map_Binary, Map_Screenshot) values(?,?,?)");

                                    psmnt.setString(1,nameOfMaps3);

                                    File image3=new File("D://cpe207//cme//"+nameOfMaps3+".udk");
                                    FileInputStream fis3=new FileInputStream(image3);
                                    psmnt.setBinaryStream(2,fis3);

                                    image3=new File("D://cpe207//cme//"+nameOfMaps3+".jpg");
                                    fis3=new FileInputStream(image3);
                                    psmnt.setBinaryStream(3, fis3);

                                   int s3=psmnt.executeUpdate();

                                    if(s3>0){
                                        System.out.println("Map3 uploaded successfully!");
                                    }
                                    else{
                                        System.out.println("Map3 failed uploading");
                                    }
                                    psmnt.close();
                                }

                                break;
                            case 4:
                                NodeList NameOfMaps4ElementList = element.getElementsByTagName("NameOfMaps4");
                                Element NameOfMaps4Element = (Element)NameOfMaps4ElementList.item(0);
                                NodeList NameOfMaps4 = NameOfMaps4Element.getChildNodes();

                                StringTokenizer st4 = new StringTokenizer((NameOfMaps4.item(0).getNodeValue()), ".");
                                String token4 = st4.nextToken();
                                nameOfMaps4 =  token4;
                                System.out.println("NameOfMaps4: "+ nameOfMaps4);

                                psmnt=con.prepareStatement("select id from Map where Map_Name=/""+nameOfMaps4+"/"");
                                ResultSet rs4=psmnt.executeQuery();

                                if(rs4.isLast()){
                                    System.out.println(nameOfMaps4+" is already stored.");
                                }
                                else{
                                    psmnt=con.prepareStatement("insert into Map(Map_Name, Map_Binary, Map_Screenshot) values(?,?,?)");

                                    psmnt.setString(1,nameOfMaps4);

                                    File image4=new File("D://cpe207//cme//"+nameOfMaps4+".udk");
                                    FileInputStream fis4=new FileInputStream(image4);
                                    psmnt.setBinaryStream(2,fis4);

                                    image4=new File("D://cpe207//cme//"+nameOfMaps4+".jpg");
                                    fis4=new FileInputStream(image4);
                                    psmnt.setBinaryStream(3, fis4);

                                    int s4=psmnt.executeUpdate();

                                    if(s4>0){
                                        System.out.println("Map4 uploaded successfully!");
                                    }
                                    else{
                                        System.out.println("Map4 failed uploading");
                                    }
                                    psmnt.close();
                                }

                                break;
                            case 5:
                                NodeList NameOfMaps5ElementList = element.getElementsByTagName("NameOfMaps5");
                                Element NameOfMaps5Element = (Element)NameOfMaps5ElementList.item(0);
                                NodeList NameOfMaps5 = NameOfMaps5Element.getChildNodes();

                                StringTokenizer st5 = new StringTokenizer((NameOfMaps5.item(0).getNodeValue()), ".");
                                String token5 = st5.nextToken();
                                nameOfMaps5 =  token5;
                                System.out.println("NameOfMaps5: "+ nameOfMaps5);

                                psmnt=con.prepareStatement("select id from Map where Map_Name=/""+nameOfMaps5+"/"");
                                ResultSet rs5=psmnt.executeQuery();

                                if(rs5.isLast()){
                                    System.out.println(nameOfMaps5+" is already stored.");
                                }
                                else{
                                    psmnt=con.prepareStatement("insert into Map(Map_Name, Map_Binary, Map_Screenshot) values(?,?,?)");

                                    psmnt.setString(1,nameOfMaps5);

                                    File image5=new File("D://cpe207//cme//"+nameOfMaps5+".udk");
                                    FileInputStream fis5=new FileInputStream(image5);
                                    psmnt.setBinaryStream(2,fis5);

                                    image5=new File("D://cpe207//cme//"+nameOfMaps5+".jpg");
                                    fis5=new FileInputStream(image5);
                                    psmnt.setBinaryStream(3, fis5);

                                    int s5=psmnt.executeUpdate();

                                    if(s5>0){
                                        System.out.println("Map5 uploaded successfully!");
                                    }
                                    else{
                                        System.out.println("Map5 failed uploading");
                                    }
                                    psmnt.close();
                                }

                                break;
                            case 6:
                                NodeList NameOfMaps6ElementList = element.getElementsByTagName("NameOfMaps6");
                                Element NameOfMaps6Element = (Element)NameOfMaps6ElementList.item(0);
                                NodeList NameOfMaps6 = NameOfMaps6Element.getChildNodes();

                                StringTokenizer st6 = new StringTokenizer((NameOfMaps6.item(0).getNodeValue()), ".");
                                String token6 = st6.nextToken();
                                nameOfMaps6 =  token6;
                                System.out.println("NameOfMaps6: "+ nameOfMaps6);

                                psmnt=con.prepareStatement("select id from Map where Map_Name=/""+nameOfMaps6+"/"");
                                ResultSet rs6=psmnt.executeQuery();

                                if(rs6.isLast()){
                                    System.out.println(nameOfMaps6+" is already stored.");
                                }
                                else{
                                    psmnt=con.prepareStatement("insert into Map(Map_Name, Map_Binary, Map_Screenshot) values(?,?,?)");

                                    psmnt.setString(1,nameOfMaps6);

                                    File image6=new File("D://cpe207//cme//"+nameOfMaps6+".udk");
                                    FileInputStream fis6=new FileInputStream(image6);
                                    psmnt.setBinaryStream(2,fis6);

                                    image6=new File("D://cpe207//cme//"+nameOfMaps6+".jpg");
                                    fis6=new FileInputStream(image6);
                                    psmnt.setBinaryStream(3, fis6);

                                    int s6=psmnt.executeUpdate();

                                    if(s6>0){
                                        System.out.println("Map6 uploaded successfully!");
                                    }
                                    else{
                                        System.out.println("Map6 failed uploading");
                                    }
                                    psmnt.close();
                                }

                                break;
                            case 7:
                                NodeList NameOfMaps7ElementList = element.getElementsByTagName("NameOfMaps7");
                                Element NameOfMaps7Element = (Element)NameOfMaps7ElementList.item(0);
                                NodeList NameOfMaps7 = NameOfMaps7Element.getChildNodes();

                                StringTokenizer st7 = new StringTokenizer((NameOfMaps7.item(0).getNodeValue()), ".");
                                String token7 = st7.nextToken();
                                nameOfMaps7 =  token7;
                                System.out.println("NameOfMaps7: "+ nameOfMaps7);

                                psmnt=con.prepareStatement("select id from Map where Map_Name=/""+nameOfMaps7+"/"");
                                ResultSet rs7=psmnt.executeQuery();

                                if(rs7.isLast()){
                                    System.out.println(nameOfMaps7+" is already stored.");
                                }
                                else{
                                    psmnt=con.prepareStatement("insert into Map(Map_Name, Map_Binary, Map_Screenshot) values(?,?,?)");

                                    psmnt.setString(1,nameOfMaps7);

                                    File image7=new File("D://cpe207//cme//"+nameOfMaps7+".udk");
                                    FileInputStream fis7=new FileInputStream(image7);
                                    psmnt.setBinaryStream(2,fis7);

                                    image7=new File("D://cpe207//cme//"+nameOfMaps7+".jpg");
                                    fis7=new FileInputStream(image7);
                                    psmnt.setBinaryStream(3, fis7);

                                    int s7=psmnt.executeUpdate();

                                    if(s7>0){
                                        System.out.println("Map7 uploaded successfully!");
                                    }
                                    else{
                                        System.out.println("Map7 failed uploading");
                                    }
                                    psmnt.close();
                                }

                                break;
                            case 8:
                                NodeList NameOfMaps8ElementList = element.getElementsByTagName("NameOfMaps8");
                                Element NameOfMaps8Element = (Element)NameOfMaps8ElementList.item(0);
                                NodeList NameOfMaps8 = NameOfMaps8Element.getChildNodes();

                                StringTokenizer st8 = new StringTokenizer((NameOfMaps8.item(0).getNodeValue()), ".");
                                String token8 = st8.nextToken();
                                nameOfMaps8 =  token8;
                                System.out.println("NameOfMaps8: "+ nameOfMaps8);

                                psmnt=con.prepareStatement("select id from Map where Map_Name=/""+nameOfMaps8+"/"");
                                ResultSet rs8=psmnt.executeQuery();

                                if(rs8.isLast()){
                                    System.out.println(nameOfMaps8+" is already stored.");
                                }
                                else{
                                    psmnt=con.prepareStatement("insert into Map(Map_Name, Map_Binary, Map_Screenshot) values(?,?,?)");

                                    psmnt.setString(1,nameOfMaps8);

                                    File image8=new File("D://cpe207//cme//"+nameOfMaps8+".udk");
                                    FileInputStream fis8=new FileInputStream(image8);
                                    psmnt.setBinaryStream(2,fis8);

                                    image8=new File("D://cpe207//cme//"+nameOfMaps8+".jpg");
                                    fis8=new FileInputStream(image8);
                                    psmnt.setBinaryStream(3, fis8);

                                    int s8=psmnt.executeUpdate();

                                    if(s8>0){
                                        System.out.println("Map8 uploaded successfully!");
                                    }
                                    else{
                                        System.out.println("Map8 failed uploading");
                                    }
                                    psmnt.close();
                                }

                                break;
                            case 9:
                                NodeList NameOfMaps9ElementList = element.getElementsByTagName("NameOfMaps9");
                                Element NameOfMaps9Element = (Element)NameOfMaps9ElementList.item(0);
                                NodeList NameOfMaps9 = NameOfMaps9Element.getChildNodes();

                                StringTokenizer st9 = new StringTokenizer((NameOfMaps9.item(0).getNodeValue()), ".");
                                String token9 = st9.nextToken();
                                nameOfMaps9 =  token9;
                                System.out.println("NameOfMaps9: "+ nameOfMaps9);

                                psmnt=con.prepareStatement("select id from Map where Map_Name=/""+nameOfMaps9+"/"");
                                ResultSet rs9=psmnt.executeQuery();

                                if(rs9.isLast()){
                                    System.out.println(nameOfMaps9+" is already stored.");
                                }
                                else{
                                    psmnt=con.prepareStatement("insert into Map(Map_Name, Map_Binary, Map_Screenshot) values(?,?,?)");

                                    psmnt.setString(1,nameOfMaps9);

                                    File image9=new File("D://cpe207//cme//"+nameOfMaps9+".udk");
                                    FileInputStream fis9=new FileInputStream(image9);
                                    psmnt.setBinaryStream(2,fis9);

                                    image9=new File("D://cpe207//cme//"+nameOfMaps9+".jpg");
                                    fis9=new FileInputStream(image9);
                                    psmnt.setBinaryStream(3, fis9);

                                    int s9=psmnt.executeUpdate();

                                    if(s9>0){
                                        System.out.println("Map9 uploaded successfully!");
                                    }
                                    else{
                                        System.out.println("Map9 failed uploading");
                                    }
                                    psmnt.close();
                                }

                                break;
                            case 10:
                                NodeList NameOfMaps10ElementList = element.getElementsByTagName("NameOfMaps10");
                                Element NameOfMaps10Element = (Element)NameOfMaps10ElementList.item(0);
                                NodeList NameOfMaps10 = NameOfMaps10Element.getChildNodes();

                                StringTokenizer st10 = new StringTokenizer((NameOfMaps10.item(0).getNodeValue()), ".");
                                String token10 = st10.nextToken();
                                nameOfMaps10 =  token10;
                                System.out.println("NameOfMaps10: "+ nameOfMaps10);

                                psmnt=con.prepareStatement("select id from Map where Map_Name=/""+nameOfMaps10+"/"");
                                ResultSet rs10=psmnt.executeQuery();

                                if(rs10.isLast()){
                                    System.out.println(nameOfMaps10+" is already stored.");
                                }
                                else{
                                    psmnt=con.prepareStatement("insert into Map(Map_Name, Map_Binary, Map_Screenshot) values(?,?,?)");

                                    psmnt.setString(1,nameOfMaps10);

                                    File image10=new File("D://cpe207//cme//"+nameOfMaps10+".udk");
                                    FileInputStream fis10=new FileInputStream(image10);
                                    psmnt.setBinaryStream(2,fis10);

                                    image10=new File("D://cpe207//cme//"+nameOfMaps10+".jpg");
                                    fis10=new FileInputStream(image10);
                                    psmnt.setBinaryStream(3, fis10);

                                    int s10=psmnt.executeUpdate();

                                    if(s10>0){
                                        System.out.println("Map10 uploaded successfully!");
                                    }
                                    else{
                                        System.out.println("Map10 failed uploading");
                                    }
                                    psmnt.close();
                                }

                                break;
                      }
                    }
                }
            }
  extractTrafficData();
  extractRouteData();
        }

 

        public void extractRouteData(){

            String nodeID;
            Double xcord, ycord;
            try{
                File file = new File("D://cpe207//cme//CME.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("Route");
                System.out.println("Information of the CME");

                for (int i = 0; i < node.getLength(); i++){
                Node firstNode = node.item(i);

                if (firstNode.getNodeType() == Node.ELEMENT_NODE) {
                    getConnection();

                    Element element = (Element) firstNode;
                    System.out.println("Route"+ i);
                    int j = 0;
                    int count = -1;
                    do{
                        psmnt=con.prepareStatement("insert into "+"route"+i+"(nodeid, xcord, ycord) values(?,?,?)");

                        NodeList NodeIDElementList = element.getElementsByTagName("NodeID");
                        Element NodeIDElement = (Element) NodeIDElementList.item(j);
                        NodeList NodeID = NodeIDElement.getChildNodes();
                        System.out.println("NodeID: "+ NodeID.item(0).getNodeValue());
                        nodeID =  NodeID.item(0).getNodeValue();
                        psmnt.setString(1, nodeID);
                                                      //store to db

                        NodeList XcordList = element.getElementsByTagName("Xcord");
                        Element XcordElement = (Element) XcordList.item(j);
                        NodeList Xcord = XcordElement.getChildNodes();
                        System.out.println("Xcord: "  +  Xcord.item(0).getNodeValue());
                        xcord = Double.parseDouble(Xcord.item(0).getNodeValue());

                        psmnt.setDouble(2, xcord);
                        //store to db
                        NodeList YcordList = element.getElementsByTagName("Ycord");
                        Element YcordElement = (Element) YcordList.item(j);
                        NodeList Ycord = YcordElement.getChildNodes();
                        System.out.println("Ycord: "  +  Ycord.item(0).getNodeValue());
                        ycord = Double.parseDouble(Ycord.item(0).getNodeValue());

                        psmnt.setDouble(3, ycord);
                        int s=psmnt.executeUpdate();
                        if(s>0){
                            System.out.println("insert node "+nodeID);
                        }
                        else{
                            System.out.println("failed inserting node "+nodeID);
                        }
                        psmnt.close();
                        //store to db

                                                      //check num of nodes in the route
                      if(count == -1){
                        count = 0;
                        while(NodeIDElementList.item(count) != null){
                                count++;
                        }
                      }
                      j++;
                       }while(j < count);//end of do while loop
                    con.close();
                 }
               }
            }catch (Exception e) {e.printStackTrace();}
        }

 

      public void extractTrafficData(){

            String trafficID;
            Double xcord, ycord;
            String group;
            try{
            File file = new File("D://cpe207//cme//CMEtraffic.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("TrafficLight");
            System.out.println("Information of the TrafficLight");

            for (int i = 0; i < node.getLength(); i++) {
                getConnection();

                Node firstNode = node.item(i);

                if (firstNode.getNodeType() == Node.ELEMENT_NODE) {
                    Element element = (Element) firstNode;
                    int j = 0;
                    int count = -1;
                    do{
                        psmnt=con.prepareStatement("insert into trafficlight(trafficlightid, xcord, ycord, type) values(?,?,?,?)");
                        NodeList TrafficIDElementList = element.getElementsByTagName("TrafficID");
                        Element TrafficIDElement = (Element) TrafficIDElementList.item(j);
                        NodeList TrafficID = TrafficIDElement.getChildNodes();
                        System.out.println("Traffic ID: "+ TrafficID.item(0).getNodeValue());
                        trafficID =  TrafficID.item(0).getNodeValue();
                                                      //store to db
                        psmnt.setString(1, trafficID);

                        NodeList XcordList = element.getElementsByTagName("Xcord");
                        Element XcordElement = (Element) XcordList.item(j);
                        NodeList Xcord = XcordElement.getChildNodes();
                        System.out.println("Xcord: "  +  Xcord.item(0).getNodeValue());
                        xcord = Double.parseDouble(Xcord.item(0).getNodeValue());

                        psmnt.setDouble(2,xcord);

                        //store to db
                        NodeList YcordList = element.getElementsByTagName("Ycord");
                        Element YcordElement = (Element) YcordList.item(j);
                        NodeList Ycord = YcordElement.getChildNodes();
                        System.out.println("Ycord: "  +  Ycord.item(0).getNodeValue());
                        ycord = Double.parseDouble(Ycord.item(0).getNodeValue());

                        psmnt.setDouble(3,ycord);
                        //store to db
                                                      NodeList GroupList = element.getElementsByTagName("Group");
                        Element GroupElement = (Element)GroupList.item(j);
                        NodeList Group = GroupElement.getChildNodes();
                        System.out.println("Group: "  +  Group.item(0).getNodeValue());
                        group = Group.item(0).getNodeValue();
                                                       //check num of nodes in the route

                        psmnt.setString(4, group);
                        int s=psmnt.executeUpdate();
                        if(s>0){
                            System.out.println("insert trafficlight");
                        }
                        else{
                            System.out.println("failed inserting trafficlight");
                        }
                        psmnt.close();

                        if(count == -1){
                           count = 0;
                           while(TrafficIDElementList.item(count) != null){
                              count++;
                           }
                        }
                        j++;
                    }while(j < count);//end of do while loop
                    con.close();
                 }
             }
         }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("CME Connected successfully!/n");
        }
        catch(Exception ex){
            System.out.println("Found some error: "+ex);
        }
     }

}