数据库信息的查询,无非就是查询表拥有的列,列的备注以及表的主键,实现起来十分简单。
这里主要考虑的就是,如何能有一个好的扩展,在更换数据库的时候可以方便的切换而不用修改源代码。
我们先来定义一个接口,这个接口只有一个方法,就是查询表的列信息:
/** * 数据库操作接口 * * User: liyd * Date: 13-12-6 * Time: 上午10:30 */ public interface DatabaseProvider { /** * 获取数据库表的信息 * * @param tableName 表名 * @return meta data */ public List<Column> getTableMetaData(String tableName); }
Column类代码如下:
/** * 列信息 * * User: liyd * Date: 13-11-28 * Time: 下午5:19 */ public class Column { /** 列名 */ private String name; /** 是否主键 */ private boolean isPrimaryKey; /** 列备注 */ private String comment; /** 数据库类型 */ private String dbType; /** jdbc类型 */ private String jdbcType; /** java类型 例:String */ private String javaType; /** java类型class名称例:java.lang.String */ private String javaClass; getter and setter... }
定义了接口,接下来就是要实现它了,但是如果我们直接进行实现,那么数据库连接的获取关闭等操作都要在这个方法里面完成,而且每实现一个数据查询类都需要这些,将会比较麻烦。因此,我们先来一个抽象的实现:
/** * 数据库操作抽象类 * * User: liyd * Date: 13-12-6 * Time: 上午11:13 */ public abstract class AbstractDatabaseProvider implements DatabaseProvider { /** * 获取数据库表的信息 * * @param tableName 表名 * @return meta data */ @Override public List<Column> getTableMetaData(String tableName) { Connection connection = DBUtils.getDefaultConnection(); return getMetaData(tableName, connection); } /** * 获取数据库表元信息 * * @param tableName the table name * @param connection the connection * @return meta data */ public abstract List<Column> getMetaData(String tableName, Connection connection); }
在这个抽象类中,我们统一对数据库连接进行了获取,然后再调用getMetaData(String tableName, Connection connection)方法将表名和连接对象作为参数传入,由子类来实现。
这里获取的连接即是前面在代码生成组织者GenerationOrganizer中创建打开的连接,这里只进行获取,由外围负责打开和关闭。这样做的好处是一次性生成多张表的时候,连接只打开和关闭一次。还有就是具体的实现者不用关心这个连接是哪里来的,不会迷惑于连接到底是自己创建还是从哪里获取,清晰明了。
下面贴上具体的mysql实现类:
/** * mysql操作类 * * User: liyd * Date: 14-1-13 * Time: 上午11:40 */ public class MysqlProvider extends AbstractDatabaseProvider { /** * 获取数据库表元信息 * * @param tableName the table name * @param connection the connection * @return meta data */ @Override public List<Column> getMetaData(String tableName, Connection connection) { List<Column> columnList = new ArrayList<Column>(); PreparedStatement pst = null; ResultSet rs = null; ResultSetMetaData rsd = null; try { //查询时没有数据,只返回表头信息 pst = connection.prepareStatement("select * from " + tableName + " where 1=2"); rsd = pst.executeQuery().getMetaData(); //查询主键 String primaryKey = null; pst = connection .prepareStatement("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME='PRIMARY' and TABLE_NAME = ?"); pst.setString(1, tableName.toUpperCase()); rs = pst.executeQuery(); if (rs.next()) { primaryKey = rs.getString(1); } //查询列备注 pst = connection .prepareStatement("select column_name, column_comment from information_schema.columns where table_name = ?"); pst.setString(1, tableName.toUpperCase()); rs = pst.executeQuery(); //先将注释放入到map再获取,防止有些列没有注释获取不对应的问题 Map<String, String> commentMap = new HashMap<String, String>(); while (rs.next()) { commentMap.put(rs.getString("COLUMN_NAME"), rs.getString("column_comment")); } for (int i = 1; i <= rsd.getColumnCount(); i++) { String name = rsd.getColumnName(i); String dbType = rsd.getColumnTypeName(i); String javaClass = rsd.getColumnClassName(i); String comment = commentMap.get(name); boolean b = StringUtils.equalsIgnoreCase(primaryKey, name) ? true : false; Column column = new Column(); column.setName(name); column.setDbType(dbType); column.setJdbcType(dbType); column.setJavaClass(javaClass); column.setComment(comment); column.setIsPrimaryKey(b); columnList.add(column); } } catch (SQLException e) { throw new EasyCodeException(e); } finally { try { if (pst != null) { pst.close(); } if (rs != null) { rs.close(); } } catch (SQLException e) { //ignore } } return columnList; } }
oracle的实现类:
/** * Oracle 操作类 * * User: liyd * Date: 13-12-6 * Time: 上午11:11 */ public class OracleProvider extends AbstractDatabaseProvider { /** * 获取数据库表元信息 * * @param tableName the table name * @param connection the connection * @return meta data */ @Override public List<Column> getMetaData(String tableName, Connection connection) { List<Column> columnList = new ArrayList<Column>(); PreparedStatement pst = null; ResultSet rs = null; ResultSetMetaData rsd = null; try { //查询时没有数据,只返回表头信息 pst = connection.prepareStatement("select * from " + tableName + " where 1=2"); rsd = pst.executeQuery().getMetaData(); //查询主键 String primaryKey = null; pst = connection .prepareStatement("select col.column_name from user_constraints con, user_cons_columns col where con.constraint_name = col.constraint_name and con.constraint_type = 'P' and col.table_name = ?"); pst.setString(1, tableName.toUpperCase()); rs = pst.executeQuery(); if (rs.next()) { primaryKey = rs.getString(1); } //查询列备注 pst = connection .prepareStatement("SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = ?"); pst.setString(1, tableName.toUpperCase()); rs = pst.executeQuery(); //先将注释放入到map再获取,防止有些列没有注释获取不对应的问题 Map<String, String> commentMap = new HashMap<String, String>(); while (rs.next()) { commentMap.put(rs.getString("COLUMN_NAME"), rs.getString("COMMENTS")); } for (int i = 1; i <= rsd.getColumnCount(); i++) { String name = rsd.getColumnName(i); String dbType = rsd.getColumnTypeName(i); String javaClass = rsd.getColumnClassName(i); String comment = commentMap.get(name); boolean b = StringUtils.equalsIgnoreCase(primaryKey, name) ? true : false; Column column = new Column(); column.setName(name); column.setDbType(dbType); column.setJdbcType(dbType); column.setJavaClass(javaClass); column.setComment(comment); column.setIsPrimaryKey(b); columnList.add(column); } } catch (SQLException e) { throw new EasyCodeException(e); } finally { try { if (pst != null) { pst.close(); } if (rs != null) { rs.close(); } } catch (SQLException e) { } } return columnList; }