java数据备份_Java 数据库备份

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 tables = new ArrayList();

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 + "...
");

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("

finished

");

%>

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 + "...
");

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();

}

%>

备份目录：/>

table_name:

url:"/>

driver:"/>

username:"/>

password:"/>

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 tables = new ArrayList();

while (rs.next()) {

tables.add(rs.getString(1));

}

rs.close();

for(int i=0;i

String table=tables.get(i);

out.println("Dumping data for table " + table + "...
");

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("

finished

");

%>