代码如下:
[code="java"]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class Bak {
public static String shengchanku = "aaa";//生产库登录名
public static String shengchanku_pw = "1";//生产库密码
public static String shengchanku_serviceName = "127.0.0.1";//生产库服务名
public static String ceshiku = "aaatest";//测试库登录名
public static String ceshiku_pw = "1";//测试库密码
public static String ceshiku_serviceName = "127.0.0.1";//测试库服务名
/**
* @param args
* @throws SQLException
*/
public static void main(String[] args){
Date d = new Date();
long start = d.getTime();
String msg = "";
Connection conn = null;
try {
conn = getConnection();
} catch (Exception e1) {
msg = "获取生产库链接失败";
}
Connection connTest = null;
try {
connTest = getTestConnection();
} catch (Exception e1) {
msg = "获取测试库链接失败";
}
if("".equals(msg)){
try {
//开启事务
conn.setAutoCommit(false);
//查询出生产库所有表的名称
List<String> list_table_names = new ArrayList<String>();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select table_name from user_tables");
while(rs.next()){
list_table_names.add(rs.getString(1));
}
rs.close();
stmt.close();
//查询测试库所有表的名称
List<String> list_table_names_test = new ArrayList<String>();
stmt = connTest.createStatement();
rs = stmt.executeQuery("select table_name from user_tables");
while(rs.next()){
list_table_names_test.add(rs.getString(1));
}
//拼接往测试库每个表插数据的sql
String table_name = "";
List<String> sql_create = new ArrayList<String>();
for(int i =0;i<list_table_names.size();i++){
table_name = list_table_names.get(i);
sql_create.add("create table "+ceshiku+"."+table_name+" AS SELECT * FROM "+shengchanku+"."+table_name+"");
}
//拼接删除测试库表的sql
List<String> sql_drop = new ArrayList<String>();
for(int i =0;i<list_table_names_test.size();i++){
table_name = list_table_names_test.get(i);
sql_drop.add("drop table "+ceshiku+"." + table_name);
}
//删除测试库的数据
stmt = conn.createStatement();
for(int i = 0 ;i<sql_drop.size();i++){
stmt.addBatch(sql_drop.get(i));
}
//执行删除sql
int[] result = stmt.executeBatch();
System.out.println("执行删除表:" + result.length);
stmt.close();
//重新创建测试库的数据
stmt = connTest.createStatement();
for(int i = 0 ;i<sql_create.size();i++){
stmt.addBatch(sql_create.get(i));
}
//执行创建sql
result = stmt.executeBatch();
System.out.println("执行创建表:" + result.length);
stmt.close();
//提交事务
conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
conn.close();
connTest.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}else{
System.out.println(msg);
}
d = new Date();
long end = d.getTime();
System.out.println("执行时间:" + (end-start) + "毫秒");
}
//获取生产库链接
public static Connection getConnection() throws Exception{
Connection ct = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
ct = DriverManager.getConnection("jdbc:oracle:thin:"+shengchanku+"/"+shengchanku_pw+"@"+shengchanku_serviceName+":1521:orcl");
return ct;
}
//获取测试库链接
public static Connection getTestConnection() throws Exception{
Connection ct = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
ct = DriverManager.getConnection("jdbc:oracle:thin:"+ceshiku+"/"+ceshiku_pw+"@"+ceshiku_serviceName+":1521:orcl");
return ct;
}
}
[/code]
大伙来看看这样写有没有什么问题,性能如何,我执行时间大概10秒多,数据量不是很大,不过有个附件表,表空间数据全部导出来大概有300多M