获取MySQL基本元信息

package com.wp.tool.sql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

public class MySQLTableMeta {
	
	private DataSource dataSource;
	
	public static final String SQL_DATABASES = 	"SELECT t.`SCHEMA_NAME` FROM `information_schema`.`SCHEMATA` t";

    public static final String SQL_TABLES = 	"SELECT 			" +
												"  b.`TABLE_NAME`,	" +
												"  b.`TABLE_TYPE` 	" +
												"FROM 											" +
												"  `information_schema`.`SCHEMATA` a 			" +
												"  LEFT JOIN `information_schema`.`TABLES` b	" + 
												"    ON a.`SCHEMA_NAME` = b.`TABLE_SCHEMA` 		" +
												"WHERE a.`SCHEMA_NAME` = ?						";
	
	public static final String SQL_COLUMNS =	"SELECT 			" +
									            "  c.`TABLE_NAME`,	" +
									            "  c.`COLUMN_NAME`,	" +
									            "  c.`COLUMN_TYPE`,	" +
									            "  c.`DATA_TYPE`	" +
									            "FROM                                           " +
									            "  `information_schema`.`SCHEMATA` a            " +
									            "  LEFT JOIN `information_schema`.`TABLES` b    " +
									            "    ON a.`SCHEMA_NAME` = b.`TABLE_SCHEMA`      " +
									            "  LEFT JOIN `information_schema`.`COLUMNS` c   " +
									            "    ON b.`TABLE_NAME` = c.`TABLE_NAME`         " +
									            "WHERE a.`SCHEMA_NAME` = ?                      " +
									            "  AND b.`TABLE_NAME` = ?						";
    
    public static final String COLUMN_SCHEMA_NAME = "SCHEMA_NAME";
	public static final String COLUMN_TABLE_NAME = "TABLE_NAME";
	public static final String COLUMN_TABLE_TYPE = "TABLE_TYPE";
	public static final String COLUMN_COLUMN_NAME = "COLUMN_NAME";
	public static final String COLUMN_COLUMN_TYPE = "COLUMN_TYPE";
	public static final String COLUMN_DATA_TYPE = "DATA_TYPE";

    /**
	 * 
	 * getMeta info：
	 * SQL_DATABASES:SCHEMA_NAME
	 * SQL_TABLES:TABLE_NAME,TABLE_TYPE PARAMS:SCHEMA_NAME
	 * SQL_COLUMNS:TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,DATA_TYPE PAEAMS:SCHEMA_NAME,TABLE_NAME
	 * 
	 * 
	 * @param sql
	 * @param params
	 * @return
	 */
	public List
 
  > getMeta(String sql, String... params) {
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List
  
   > arr = null; try { connection = dataSource.getConnection(); pstmt = connection.prepareStatement(sql); for (int i = 1; i <= params.length; i++) { pstmt.setObject(i, params[i - 1]); } rs = pstmt.executeQuery(); while (rs.next()) { if (arr == null) arr = new ArrayList
   
    >(); Map
    
      row = new HashMap
     
      (); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnName(i); row.put(columnName, rs.getString(i)); } arr.add(row); } } catch (SQLException e) { throw new RuntimeException(e); } finally { try { rs.close(); pstmt.close(); connection.close(); } catch (Exception e2) { throw new RuntimeException(e2); } } return arr; } public static void main(String[] args) { //将类放到spring中通过xml注入MySQL的dataSource就可以使用 MySQLTableMeta meta= null; //获取数据库列表 List
      
       > list1 = meta.getMeta(MySQLTableMeta.SQL_DATABASES); for (Map
       
         map : list1) { System.out.println(map.get(MySQLTableMeta.COLUMN_SCHEMA_NAME));//数据库名 } //获取"test"库的表或视图等 List
        
         > list2 = meta.getMeta(MySQLTableMeta.SQL_TABLES,"test"); for (Map
         
           map : list1) { System.out.println(map.get(MySQLTableMeta.COLUMN_TABLE_NAME));//表名 System.out.println(map.get(MySQLTableMeta.COLUMN_TABLE_TYPE));//表的类型 } //获取"test"库中"teacher"表的字段 List
          
           > list3 = meta.getMeta(MySQLTableMeta.SQL_COLUMNS,"test","teacher"); for (Map
           
             map : list1) { System.out.println(map.get(MySQLTableMeta.COLUMN_TABLE_NAME));//表名 System.out.println(map.get(MySQLTableMeta.COLUMN_COLUMN_NAME));//列名 System.out.println(map.get(MySQLTableMeta.COLUMN_COLUMN_TYPE));//字段类型 如：varchar(20) System.out.println(map.get(MySQLTableMeta.COLUMN_DATA_TYPE));//字段类型 如：varchar } } /** * via spring xml DI to this Object * @param dataSource */ public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } }