最近测试项目,自己的本机搭建环境总是删除表,用java写了一个实现
package oracle;
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.List;
/**
* @author
*
* 下午9:33:27
* 删除Mysql/oracle表中的所有表
*/
public class OracleTableDel {
public static void main(String[] args) throws SQLException {
dropTable("root", "123456", "jdbc:mysql://192.168.2.101", "foo");//删除mysql
dropTable("eos", "123456", "jdbc:oracle:thin:@192.168.2.111:1521:orcl", null);
}
/**
* 删除表 如果databaseName 为null则为oracle 否则为mysql
* @param username
* @param password
* @param url
* @param databaseName
*/
public static void dropTable(String username, String password, String url, String databaseName) {
Connection conn = null;
if(url.contains("oracle")) {
//conn = getConnection("eos", "123456", "jdbc:oracle:thin:@192.168.2.111:1521:orcl", null);
conn = getConnection(username, password, url, null);
} else if(url.contains("mysql")) {
// conn = getConnection("fan", "123456", "jdbc:mysql://192.168.2.101", "foo");
conn = getConnection(username, password, url, databaseName);
}
if(conn != null) {
List<String> tableList = getTableName(conn);
try {
Statement stat = conn.createStatement();
String sql;
for(String name : tableList) {
if(url.contains("oracle")) {
sql = " drop table " + name + " cascade constraints";//级联删除表
stat.execute(sql);
} else if(url.contains("mysql")) {
sql = " drop table " + name ;
stat.execute(sql);
}
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 查询所有的表名
* @param conn
* @return
*/
public static List<String> getTableName(Connection conn) {
List<String> nameList = new ArrayList<String>();
try {
String catalog = conn.getCatalog();
Statement stat = conn.createStatement();
String sql;
/**
* catalog 不为空则是 mysql 否则为oracle
*/
if(catalog != null) {
sql = "select table_name from information_schema.tables where table_schema='" + catalog + "'";
} else {
sql = "select table_name from user_tables ";
}
ResultSet rs = stat.executeQuery(sql);
while(rs.next()) {
nameList.add(rs.getString(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
return nameList;
}
/**
* 获取数据库连接
* @param username
* @param psword
* @param url
* @param databaseName
* @return
*/
public static Connection getConnection(String username, String psword, String url, String databaseName) {
Connection con = null;// 创建一个数据库连接
try {
if(url.contains("oracle")) {
Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
con = DriverManager.getConnection(url , username, psword);// 获取连接
} else if(url.contains("mysql")) {
Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
con = DriverManager.getConnection(url + "/" + databaseName + "?user=" + username + "&password=" + psword + "&useUnicode=true&characterEncoding=UTF8");// 获取连接
}
System.out.println("连接成功!");
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
}
如果有更好的方法请分享。