Java应用之得到Oracle,Mysql数据库表信息

mzqeuohgor

　　最近在使用公司内部框架生成代码的时候，在思考框架是怎么判断表是否存在，怎样得到表的信息，利用中午的时间找了点资料，现在可以实现判断Oracle,Mysql表是否存在，表内是否存在某个字段，得到表的字段信息，如字段名称，字段类型，长度，以及注释，首先带上我参考的信息：
　　

http://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle
http://dev.mysql.com/doc/refman/5.0/en/columns-table.html
http://stackoverflow.com/questions/9016578/how-to-get-primary-key-column-in-oracle
　　数据库及版本：Oracle 10g,Mysql 5.6.14。
　　    废话不多说，上代码：
　　    首先是公共方法：
　　

public Connection getMysqlConnection() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";// 要操作的数据库名称
String username = "root";// 数据库用户名
String password = "123";// 密码
return getConnection(driver, url, username, password);
}
public Connection getOracleConnection() {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:xe";// 要操作的数据库名称
String username = "tmd";// 数据库用户名
String password = "tmd";// 密码
return getConnection(driver, url, username, password);
}
public Connection getConnection(String driver, String url, String userName,
String passwd) {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, userName, passwd);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public void closeConnection(ResultSet rs, Statement statement,
Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (statement != null) {
statement.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
　　得到Mysql指定数据库所有表名：
　　

//得到Mysql指定数据库所有表名
public void getMysqlDataBaseTables(String database) throws Exception {
Connection conn = getMysqlConnection();
ResultSet rs = null;
Statement statement = conn.createStatement();
String sql = "select table_name from information_schema.tables where table_schema='"
+ database + "'";
rs = statement.executeQuery(sql);
System.out.println("-------------DB[" + database
+ "] All Tables---------------");
while (rs.next()) {
System.out.println(rs.getString("table_name"));
}
closeConnection(rs, statement, conn);
}
　　 Oracle当前用户下所有表名:
　　

public void getOracleDataBaseTables() throws Exception {
Connection conn = getOracleConnection();
ResultSet rs = null;
Statement statement = conn.createStatement();
String sql = "select table_name from user_tables";
rs = statement.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("table_name"));
}
closeConnection(rs, statement, conn);
}
　　 检测Mysql表是否存在：
　　

//检查Mysql表是否存在
public boolean checkMysqlTableExist(String db, String tableName)
throws Exception {
Connection conn = getMysqlConnection();
DatabaseMetaData meta = conn.getMetaData();
boolean isExist = false;
// types -要包括的表类型组成的列表，可设为null，表示所有的
// ResultSet rs = meta.getTables(db, null, tableName,new String[] {
// "TABLE" });
ResultSet rs = meta.getTables(db, null, tableName, null);
System.out
.println("TABLE_CAT \t TABLE_SCHEM \t TABLE_NAME \t TABLE_TYPE");
while (rs.next()) {
System.out.println(rs.getString("TABLE_CAT") + "\t"
+ rs.getString("TABLE_SCHEM") + "\t"
+ rs.getString("TABLE_NAME") + "\t"
+ rs.getString("TABLE_TYPE"));
isExist = true;
}
closeConnection(rs, null, conn);
return isExist;
}
　　检查Oracle表是否存在
　　

//检查Oracle表是否存在
//经我测试只能检查当前连接用户下的是否存在
public boolean checkOracleTableExist(String userName, String tableName)
throws Exception {
Connection conn = getOracleConnection();
DatabaseMetaData meta = conn.getMetaData();
userName=userName.toUpperCase();
tableName=tableName.toUpperCase();
// ResultSet rs = meta.getTables(null, userName, tableName, new String[]
// {"TABLE" });
ResultSet rs = meta.getTables(null, userName, tableName, null);
boolean isExist = false;
System.out
.println("TABLE_CAT \t TABLE_SCHEM \t TABLE_NAME \t TABLE_TYPE");
while (rs.next()) {
System.out.println(rs.getString("TABLE_CAT") + "\t"
+ rs.getString("TABLE_SCHEM") + "\t"
+ rs.getString("TABLE_NAME") + "\t"
+ rs.getString("TABLE_TYPE"));
isExist = true;
}
closeConnection(rs, null, conn);
return isExist;
}
　　检查Mysql表是否存在指定的列：
　　

//检查Mysql表是否存在指定的列
public boolean checkMysqlTableColumnExist(String db, String tableName,String columnName)
throws Exception{
boolean isExist=false;
Connection conn = getMysqlConnection();
DatabaseMetaData meta = conn.getMetaData();
ResultSet rs = meta.getTables(db, null, tableName, null);
if (rs.next()) {
rs = meta.getColumns(null, null, tableName, columnName);
if (rs.next()) {
isExist=true;
}
}
closeConnection(rs, null, conn);
return isExist;
}
　　检查Oracle表是否存在指定的列
　　

//检查Oracle表是否存在指定的列
public boolean checkOracleTableColumnExist(String tableName,String columnName)
throws Exception{
boolean isExist=false;
Connection conn = getOracleConnection();
DatabaseMetaData meta = conn.getMetaData();
tableName=tableName.toUpperCase();
columnName=columnName.toUpperCase();
ResultSet rs = meta.getTables(null, null, tableName, null);
if (rs.next()) {
rs = meta.getColumns(null, null, tableName, columnName);
if (rs.next()) {
isExist=true;
}
}
closeConnection(rs, null, conn);
return isExist;
}
　　检查Oracle表是否存在指定的列方法2：
　　

public boolean checkOracleTableColumnExistTwo(String tableName,String columnName)
throws Exception{
boolean isExist=false;
Connection conn = getOracleConnection();
ResultSet rs = null;
Statement statement = conn.createStatement();
tableName=tableName.toUpperCase();
columnName=columnName.toUpperCase();
String sql = "select table_name, column_name from cols where  table_name ='"+tableName+"' and column_name ='"+columnName+"'";
rs = statement.executeQuery(sql);
while (rs.next()) {
isExist=true;
}
closeConnection(rs, null, conn);
return isExist;
}
　　  得到Mysql表信息,如列名，数据类型，长度，注释
　　

//得到Mysql表信息,如列名，数据类型，长度，注释
public void getMysqlTableInfo(String dbName,String tableName) throws Exception
{
Connection conn = getMysqlConnection();
ResultSet rs = null;
Statement statement = conn.createStatement();
tableName=tableName.toUpperCase();
String sql = "select column_name,column_type,column_key,column_comment from information_schema.columns where  table_name = '"+tableName+"' and table_schema = '"+dbName+"'";
rs = statement.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("column_name")+"\t"+rs.getString("column_type")+"\t"+rs.getString("column_key")+"\t"+rs.getString("column_comment"));
}
closeConnection(rs, statement, conn);
}
　　得到Oralce表信息,如列名，数据类型，长度，注释
　　

//得到Oralce表信息,如列名，数据类型，长度，注释
public void getOracleTableInfo(String tableName) throws Exception
{
Connection conn = getOracleConnection();
ResultSet rs = null;
Statement statement = conn.createStatement();
tableName=tableName.toUpperCase();
String sql = "select a.column_name,a.data_type,a.data_length,b.comments from cols a,user_col_comments b  where a.column_name=b.column_name and a.table_name=b.table_name and b.table_name='"+tableName+"'";
rs = statement.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("column_name")+"\t"+rs.getString("data_type")+"\t"+rs.getString("data_length")+"\t"+rs.getString("comments"));
}
closeConnection(rs, statement, conn);
}
　　得到Oracle表的主键
　　

public void getOracleTablePK(String tableName) throws Exception
{
Connection conn = getOracleConnection();
ResultSet rs = null;
Statement statement = conn.createStatement();
tableName=tableName.toUpperCase();
String sql = "select c.table_name, c.column_name, d.status, d.owner from all_constraints d, all_cons_columns c where d.constraint_type = 'P' and d.constraint_name = c.constraint_name and d.owner = c.owner and c.table_name = '"+tableName+"'";
rs = statement.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("table_name")+"\t"+rs.getString("column_name")+"\t"+rs.getString("status")+"\t"+rs.getString("owner"));
}
closeConnection(rs, statement, conn);
}
　　代码完。
　　  
　　    本博文是我自己亲自调试并测试，转载请注明出处，另外如果对我的博文有不同意见，欢迎留言。
　　    说明：本人不接受任何涉及人格辱骂之类的留言，留言请文明用语。谢谢。
　　    全文完。