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

     最近在使用公司内部框架生成代码的时候，在思考框架是怎么判断表是否存在，怎样得到表的信息，利用中午的时间找了点资料，现在可以实现判断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);
	}

    代码完。

  

    本博文是我自己亲自调试并测试，转载请注明出处，另外如果对我的博文有不同意见，欢迎留言。

    说明：本人不接受任何涉及人格辱骂之类的留言，留言请文明用语。谢谢。

    全文完。