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();
}
}
}