import java.sql.*;

public class Main {
   //本地?cái)?shù)據(jù)庫(kù)
  // static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  // static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/aite?useSSL=false&serverTimezone=UTC";
  // static final String USER = "root";
  // static final String PASS = "root";

  //測(cè)試服務(wù)器數(shù)據(jù)庫(kù)
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB_URL = "jdbc:mysql://192.168.1.236:3306/aite?useSSL=false&serverTimezone=UTC";
  static final String USER = "root";
  static final String PASS = "c6f20omhhg";

  public static void main(String[] args) {
    // Connection conn = null;
    // Statement stmt = null;
    // Statement stmt2 = null;
    // try{
    //   Class.forName(JDBC_DRIVER);
    //   System.out.println("連接數(shù)據(jù)庫(kù)...");
    //   conn = DriverManager.getConnection(DB_URL,USER,PASS);
    //   System.out.println(" 實(shí)例化Statement對(duì)象...");
    //   stmt = conn.createStatement();
    //   String sql;
    //   sql = "SELECT id,id2 FROM sys_code_region where id2>736978";
    //   ResultSet rs = stmt.executeQuery(sql);
    //   int a=2;
    //   while(rs.next()){
    //     String id= rs.getString("id");
    //     //String name = rs.getString("id2");
    //     int b=rs.getInt("id2");
    //     String sql2;
    //     sql2= "update base_area set area_code="+id+" where id='"+b+"'";
    //     stmt2 = conn.createStatement();
    //     //stmt2.executeQuery(sql2);
    //     stmt2.execute(sql2);
    //     a++;
    //     System.out.print("ID: " + id);
    //     System.out.print(", id2: " + b);
    //     System.out.print("\n");
    //   }
    //   rs.close();
    //   stmt.close();
    //   conn.close();
    // }catch(SQLException se){
    //   se.printStackTrace();
    // }catch(Exception e){
    //   e.printStackTrace();
    // }finally{
    //  try{
    //     if(stmt!=null) stmt.close();
    //   }catch(SQLException se2){
    //   }
    //   try{
    //     if(conn!=null) conn.close();
    //   }catch(SQLException se){
    //     se.printStackTrace();
    //   }
    // }
    // System.out.println("Success!");

    //getPids();

    setPriority();
  }

  /**
   * pids查詢插入遞歸方法
   */
  public static void getPids(){
    Connection conn = null;
    Statement stmt = null;
    Statement stmt2 = null;
    String pids=null;
    try{
      Class.forName(JDBC_DRIVER);
      System.out.println("連接數(shù)據(jù)庫(kù)...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);
      System.out.println(" 實(shí)例化Statement對(duì)象...");
      stmt = conn.createStatement();
      String sql;
      sql = "SELECT id,pid,name FROM base_area where id=2210";
      ResultSet rs = stmt.executeQuery(sql);
      while(rs.next()){
        String str1=rs.getString("id");
        String str2=rs.getString("pid");
        String str3=rs.getString("name");
        int a =Integer.parseInt(str1);
        int b =Integer.parseInt(str2);
        pids=str2;
        int c=0;
        c=getPid(b);
        if (c>0){
          do{
            if (c >1) {
              pids = c + "," + pids;
              c = getPid(c);
            }
          }while (c>1);
          pids = c + "," + pids;
        }
        stmt2 = conn.createStatement();
        String sql2;
        sql2= "update base_area set pids='"+pids+"' where id="+a;
        stmt2.execute(sql2);
        System.out.println("id: "+a+",name:"+str3+", pids: "+pids);
      }
      rs.close();
      stmt.close();
      conn.close();
    }catch(SQLException se){
      se.printStackTrace();
    }catch(Exception e){
     e.printStackTrace();
    }finally{
      try{
        if(stmt!=null) stmt.close();
      }catch(SQLException se2){
      }
      try{
        if(conn!=null) conn.close();
      }catch(SQLException se){
        se.printStackTrace();
      }
    }
    System.out.println("Success!");
  }

  /**
   * pid 查詢方法
   * @param id
   * @return pid
   */
  public static Integer getPid(int id){
    Connection conn3 = null;
    Statement stmt3 = null;
    int pid =0;
    try{
    conn3= DriverManager.getConnection(DB_URL,USER,PASS);
    stmt3=conn3.createStatement();
    String sql3="select pid from base_area where id="+id;
    ResultSet rs3=stmt3.executeQuery(sql3);
    while (rs3.next()) {
      String str = rs3.getString("pid");
      pid = Integer.parseInt(str);
    }
      rs3.close();
      stmt3.close();
      conn3.close();
     }catch(SQLException se){
      se.printStackTrace();
    }catch(Exception e){
      e.printStackTrace();
    }finally{
      try{
        if(stmt3!=null) stmt3.close();
      }catch(SQLException se2){
      }
      try{
        if(conn3!=null) conn3.close();
      }catch(SQLException se){
        se.printStackTrace();
      }

    }
    return pid;
  }

  /**
   * 排序方法
   */
  public static void setPriority(){
    try {
      System.out.println("連接數(shù)據(jù)庫(kù)...");
      Connection conn4 = DriverManager.getConnection(DB_URL, USER, PASS);
      System.out.println(" 實(shí)例化Statement對(duì)象...");
      Statement stmt4 = conn4.createStatement();
      ResultSet rs4 = stmt4.executeQuery("select id from base_area ");
      while (rs4.next()){
        int id = rs4.getInt("id");
        Statement stmt5=conn4.createStatement();
        String sqlstr1="select id,pid from base_area where pid="+id+" ORDER BY id ";
        ResultSet rs5=stmt5.executeQuery(sqlstr1);
        rs5.last();
        int count=rs5.getRow();
        if(count>0) {
          rs5.beforeFirst();
          int i=1;
          while (rs5.next()) {
            int id2 = rs5.getInt("id");
            Statement stmt6 = conn4.createStatement();
            String sqlstr2="update base_area set priority=" + i + " where id=" + id2;
            stmt6.execute(sqlstr2);
            System.out.println("id:" + id2 + ", priority:" + i);
            i++;
          }
        }
        //System.out.println(id);
      }
    }
    catch (SQLException se){
      se.printStackTrace();
    }
    catch (Exception e){
       e.printStackTrace();
    }
     System.out.println("Complete!");
  }
}