JSP+MySQL脱裤脚本

时间:2014-8-11    作者:admin    分类: 摘自网络


导出路径最好写绝对路径

<%@ page language="java" import="java.util.*,java.sql.*,java.io.*"
  pageEncoding="utf-8"%>

<%
  String path = request.getContextPath();
  String basePath = request.getScheme() + "://"
      + request.getServerName() + ":" + request.getServerPort()
      + path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>

    <title>Jsp + MySQL</title>
  </head>

  <body>

    <%!public static class Tool {
    public static String strTab(String str, int num) {//补齐24位
      int j = str.length();
      if (str.length() < num) {

        for (int x = j; x < num; x++, j++) {
          str = str + "&nbsp;";

        }

      }
      return str;
    }
  }

  public class Column {

    private String name;
    private String data;
    private String type;

    public Column() {
    }

    public Column(String name, String type) {
      this.name = name;
      this.type = type;
    }

    public void setName(String name) {
      this.name = name;
    }

    public void setType(String type) {
      this.type = type;
    }

    public void setDate(String date) {
      this.data = date;
    }

    public String getName() {
      return this.name;
    }

    public String getType() {
      return this.type;
    }

    public String getDate() {
      return this.data;
    }

  }

  public class Table {
    private String name;
    private List<Column> column = new ArrayList<Column>();
    private String count;

    public void setCount(String count) {
      this.count = count;
    }

    public String getCount() {
      return this.count;
    }

    public Table() {

    }

    public Table(String name, String count) {
      this.name = name;
      this.count = count;
    }

    public void setName(String name) {
      this.name = name;
    }

    public String getName() {
      return this.name;
    }

    public void setColumn(List<Column> column) {
      this.column = column;
    }

    public List<Column> getColumn() {
      return this.column;
    }
  }

  /*
   数据库连接类
   */
  public class DatabaseConnection {

    private Connection connection;
    private Statement statement;
    private ResultSet res;

    public DatabaseConnection(String url, String username, String password) {
      this.getConnection(url, username, password);
    }

    public Connection getConnection(String url, String username,
        String password) {
      try {
        Class.forName("org.gjt.mm.mysql.Driver");
        this.connection = DriverManager.getConnection(url, username,
            password);
      } catch (Exception e) {
        e.printStackTrace();
      }
      return this.connection;
    }

    public ResultSet execut(String sql) throws Exception {
      this.statement = this.connection.createStatement();

      this.res = this.statement.executeQuery(sql);
      return this.res;
    }

    public void close() {
      if (this.connection != null) {
        try {
          this.connection.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
  }%>

    <%
      String submit = request.getParameter("submit");

      String b = request.getParameter("b"); //标志位,是否是导出数据

      if (submit == null || "".equals(submit)) {
    %>

    <form method="post" action="#">
      database:
      <input type="text" value="" name="db" />
      <br />
      username:
      <input type="text" value="" name="username" />
      <br />
      password:
      <input type="text" value="" name="password" />
      <br />
      &nbsp;&nbsp;&nbsp;&nbsp;port:
      <input type="text" value="3306" name="port" />
      <br />
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
      <input type="submit" value="submit" name="submit" />
      <br />

    </form>

    <%
      } else if (b == null || "".equals(b)) {
        String db = request.getParameter("db");
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        String port = request.getParameter("port");
        String url = "jdbc:mysql://localhost:" + port + "/" + db; //连接字符串

        request.getSession().setAttribute("username", username);
        request.getSession().setAttribute("password", password);
        request.getSession().setAttribute("url", url);

        String sql = "select table_name from information_schema.TABLES where table_schema=(select DATABASE());"; //查出当前库所有表
        DatabaseConnection dbc = new DatabaseConnection(url, username,
            password);
        ResultSet res = dbc.execut(sql);
        List<Table> table = new ArrayList<Table>(); //存放所有的表
        while (res.next()) {

          String tablename = res.getString(1);
          String sqlcount = "select count(*) from " + tablename;
          ResultSet temp = dbc.execut(sqlcount);
          if (temp.next()) {
            sqlcount = temp.getString(1);
          }

          table.add(new Table(res.getString(1), sqlcount));
          temp.close();
        }
        res.close(); //关闭资源  
        for (Table t : table) {

          String tsql = "select COLUMN_NAME ,DATA_TYPE from information_schema.COLUMNS where TABLE_NAME='"
              + t.getName() + "'"; // 查询表结构
          ResultSet tres = dbc.execut(tsql);

          while (tres.next()) {
            t.getColumn()
                .add(
                    new Column(tres.getString(1), tres
                        .getString(2)));
          }

        }
        request.setAttribute("table", table);
        res.close();
        dbc.close();

        for (Table t : table) {
          out.print("<div style=\"float:left;padding-left:30px\">");
          out.print("+------------------------+<br/>");
          out.print("|"
              + Tool.strTab(
                  t.getName() + " ---> " + t.getCount(), 24)
              + "|<br/>");
          out.print("+------------------------+<br/>");
          out.print("| " + Tool.strTab("Column", 10) + "| "
              + Tool.strTab("Type", 11) + "|<br/>");
          out.print("+-----------+------------+<br/>");
          for (Column c : t.column) {

            out.print("| " + Tool.strTab(c.getName(), 10) + "| "
                + Tool.strTab(c.getType(), 11) + "|<br/>");
            out.print("+-----------+------------+<br/>");

          }
          out.print("</div>");

        }
    %>

    <!-- 开始进行数据导出的HTML数据 -->
    <form method="post" action="#">
      <br style="clear: left" />
      <hr style="float: none" />

      表名:&nbsp;&nbsp;&nbsp;&nbsp;
      <input type="text" name="table" />
      <br />
      列名:&nbsp;&nbsp;&nbsp;&nbsp;
      <input type="text" value="*" name="colums" />
      以逗号隔开(英文),默认为*
      <br />
      分行:&nbsp;&nbsp;&nbsp;&nbsp;
      <input type="text" value="5000" name="row" />
      每次查询多少条?默认每次5000条
      <br />

      <input type="hidden" name="b" value="b" />

      保存路径:
      <input type="text" value="" name="path" />
      <br />
      &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
      <input type="submit" value="开始导出" name="submit" />

    </form>
    <%
      } else {

        String table = request.getParameter("table").trim();
        String colums = request.getParameter("colums").trim();
        String row = request.getParameter("row").trim();
        String filePath = request.getParameter("path").trim();
        String sql = "select " + colums + " from " + table;
        int index = 0;
        if (row == null || "".equals(row)) {
          index = 10000; //默认10000条每次
        } else {
          index = Integer.parseInt(row);
        }

        String url = (String) request.getSession().getAttribute("url");
        String username = (String) request.getSession().getAttribute(
            "username");
        String password = (String) request.getSession().getAttribute(
            "password");
        DatabaseConnection dbc = new DatabaseConnection(url, username,
            password);

        String sqlcount = "select count(*) from " + table;
        ResultSet res = dbc.execut(sqlcount);
        int count = 0;
        if (res.next()) {
          count = res.getInt(1);
        }
        res.close();

        //开始执行SQL语句
        //1、先进行判断是否是* ,如果是的话就进行查询表结构..

        if (colums.trim().length() == 1) {

          String temp = "select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME='"
              + table + "'";
          ResultSet tempr = dbc.execut(temp);
          colums ="";
          while (tempr.next()) {
            colums += tempr.getString(1) + ",";
          }
          tempr.close();
          colums = colums.substring(0, colums.length() - 1);
        }
        String c[] = colums.split(",");  //或去所有列

        if(new File(filePath).exists()){
          new File(filePath).delete();  //如果文件存在就删除..
        }

        FileWriter fw = new FileWriter(filePath,true);

         for(String t:c){
           fw.write(t+"\t\t");
         }
         fw.write("\r\n");
         fw.flush();
         out.print("开始导出数据:共"+count+"条<br/>");
         int j=0;
        for (int x = 0; x < count; x += index) {

          String rsql = "select " + colums + " from " + table + " limit "
              + x + "," + index;
          ResultSet rs = dbc.execut(rsql);

          while (rs.next()) {
            String tempdate = "";

            for(String it : c){

              // tempdate +=  rs.getString(it)+"\t";  //得到每条数据
              try{
                tempdate +=rs.getString(it)+"\t\t";

              }catch(Exception e){

              }
            }
             fw.write(tempdate+"\r\n");
             j++;
          }

          if(j%10000==0){
            out.print("正在导出 :"+j+"条<br/>");
            out.flush();
          }

          fw.flush();
        }
        out.print("已导出 :"+j+"条<br/>");
        out.print("导出完毕...Good !!");
        fw.close();
        dbc.close();
      }
    %>
  </body>
</html>

标签: mysql jsp 脱裤