通过JDBC获取数据库表元数据

jdbc获取数据库表无数据,主要通过java.sql.DatabaseMetaData接口的方法实现。


    常用的方法有:


     1.获取表信息

      

      具体返回的信息描述如下:



      2.获取列信息

       

具体返回的信息描述如下

  


     3.获取主键信息

       

具体返回的信息描述如下


  有这些信息后,我们就可以自己试着用freemarker加mybatis做半自动化的代码生成工具。


  下面的示例代码,大家可以参考下

package com.petecc.foodsafe.generate.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

import com.petecc.foodsafe.generate.orm.ActualTableName;
import com.petecc.foodsafe.generate.orm.FullyQualifiedJavaType;
import com.petecc.foodsafe.generate.orm.JavaTypeResolverDefaultImpl;
import com.petecc.foodsafe.generate.orm.TbColumn;

/**   
 * @Description: 数据库表解析
 * @author tanw   
 * @date 2013-9-11 下午4:07:55
 */
public class InspectDB {
	
	/**
	 * 获取表信息
	 * @param tableName   表名
	 * @return
	 * @throws SQLException
	 */
	public static Map<String,Object> getDatabaseInfo(String tableName) throws SQLException {

		Connection conn = JdbcUtil.getConnection();

		DatabaseMetaData databaseMetaData = conn.getMetaData();
		ResultSet rs = databaseMetaData.getColumns(null, null,tableName, null);

		
		ActualTableName atn = null;
		List<TbColumn> columns = null;
		Set<String> importSet = null;
		
		JavaTypeResolverDefaultImpl javaTypeResolver=new JavaTypeResolverDefaultImpl();

		while (rs.next()) {
			TbColumn tbColumn = new TbColumn();

			tbColumn.setJdbcType(rs.getInt("DATA_TYPE")); 
			tbColumn.setLength(rs.getInt("COLUMN_SIZE")); 
			tbColumn.setActualColumnName(rs.getString("COLUMN_NAME")); 
			tbColumn.setNullable(rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable); 
			tbColumn.setScale(rs.getInt("DECIMAL_DIGITS")); 
			tbColumn.setRemarks(rs.getString("REMARKS")); 
			tbColumn.setDefaultValue(rs.getString("COLUMN_DEF")); 
			tbColumn.setAutoIncrement(rs.getString("IS_AUTOINCREMENT"));

			if (columns == null) {
				columns = new ArrayList<TbColumn>();
				atn = new ActualTableName(
						rs.getString("TABLE_CAT"), 
						rs.getString("TABLE_SCHEM"), 
						rs.getString("TABLE_NAME")); 
			}
			
			FullyQualifiedJavaType fullyQualifiedJavaType = javaTypeResolver.calculateJavaType(tbColumn);
			tbColumn.setFullyQualifiedJavaType(fullyQualifiedJavaType);
			tbColumn.setJdbcTypeName(javaTypeResolver.calculateJdbcTypeName(tbColumn));
			
			columns.add(tbColumn);
			
			if(importSet == null){
				importSet = new TreeSet<String>();
			}else{
				List<String> imps = tbColumn.getFullyQualifiedJavaType().getImportList();
				importSet.addAll(imps);
			}
		}
		
		//计算主键字段
		ResultSet rsPk = databaseMetaData.getPrimaryKeys(null, null, tableName);
		 Map<Short, String> keyColumns = new TreeMap<Short, String>();
         while (rsPk.next()) {
             String columnName = rsPk.getString("COLUMN_NAME"); 
             short keySeq = rsPk.getShort("KEY_SEQ"); 
             keyColumns.put(keySeq, columnName);
         }
         
         List<TbColumn> primaryKeyColumns = new ArrayList<TbColumn>();
         
         for(TbColumn col:columns){
        	 if(keyColumns.values().contains(col.getActualColumnName())){
        		 col.setIdentity(true);
        		 primaryKeyColumns.add(col);
        	 }
         }
         
        JdbcUtil.release(rsPk);
		JdbcUtil.release(rs, null, conn);
		
		HashMap<String,Object> ret = new HashMap<String,Object>();
		ret.put("atn", atn);                 //表信息
		ret.put("primaryKeyColumns", primaryKeyColumns);                 //主键信息		
		ret.put("columns", columns);		 //列信息
		ret.put("importSet", importSet);	 //导入的类信息
		
		return ret;
	}
	
	/**
	 * 打印表信息
	 * @param map
	 */
	@SuppressWarnings({ "unchecked", "unused" })
	public static void printDatabaseInfo(Map<String,Object> map){
		
		ActualTableName atn = (ActualTableName)map.get("atn");
		List<TbColumn> primaryKeyColumns = (List<TbColumn>)map.get("primaryKeyColumns");
		List<TbColumn> columns = (List<TbColumn>)map.get("columns");
		Set<String> importSet = (Set<String>)map.get("importSet");
		
		System.out.println("###########表信息############");
		System.out.println("TABLE_CAT="+atn.getCatalog());
		System.out.println("TABLE_SCHEM="+atn.getSchema());
		System.out.println("TABLE_NAME="+atn.getTableName());
		System.out.println("");
		
		System.out.println("###########主键信息############");
		for(TbColumn col:primaryKeyColumns){
			System.out.println(col);
		}
		System.out.println("");
		
		System.out.println("###########列信息############");
		for(TbColumn col:columns){
			System.out.println(col);
		}
	}
	
	
	
	public static void main(String[] args) {
		String tableName = "student";
		try {
			Map<String,Object> map = getDatabaseInfo(tableName);
			printDatabaseInfo(map);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}



评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值