最近使用JBolt极速开发平台在做一个项目，使用的是Sqlserver数据库，表注释和字段注释都做好了，在Mysql下生成一点问题没有，在Sqlserver下生成却发现列注释remarks获取不到，无法正确生成！
debug发现是JDBC-Sqlserver的坑，没有很好的去实现标准，获取remarks无效。
无奈只能靠Sql语句自行查询解决了。
这里给出了自定义扩展SqlServer的MetaBuilder去解决了这个问题。
主要是buildTabelColumnRemark实现通过sql查询每个表的字段注释。
具体代码：
package cn.jbolt.common.gen;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.List;
import javax.sql.DataSource;
import com.jfinal.kit.Kv;
import com.jfinal.plugin.activerecord.dialect.SqlServerDialect;
import com.jfinal.plugin.activerecord.generator.ColumnMeta;
import com.jfinal.plugin.activerecord.generator.TableMeta;
public class JBoltSqlServerMetaBuilder extends JBoltMetaBuilder{
private Connection columnRemarkConn;
public JBoltSqlServerMetaBuilder (DataSource dataSource) {
super(dataSource);
}
@Override
protected void buildColumnMetas(TableMeta tableMeta) throws SQLException {
String sql = dialect.forTableBuilderDoBuild(tableMeta.name);
Statement stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
Kv remarkKv=null;
if (generateRemarks) {
remarkKv=buildTabelColumnRemark(tableMeta.name);
}
ColumnMeta cm;
for (int i=1; i<=columnCount; i++) {
cm = new ColumnMeta();
cm.name = rsmd.getColumnName(i);
String colClassName = rsmd.getColumnClassName(i);
String typeStr = typeMapping.getType(colClassName);
if (typeStr != null) {
cm.javaType = typeStr;
}
else {
int type = rsmd.getColumnType(i);
if (type == Types.BINARY || type == Types.VARBINARY || type == Types.BLOB) {
cm.javaType = "byte[]";
}
else if (type == Types.SMALLINT) {
cm.javaType="java.lang.Short";
}
else if (type == Types.CLOB || type == Types.NCLOB) {
cm.javaType = "java.lang.String";
}
else {
cm.javaType = "java.lang.String";
}
}
//特殊处理char(1) to Boolean
if("java.lang.String".equals(cm.javaType)) {
int scale = rsmd.getScale(i); // 小数点右边的位数，值为 0 表示整数
int precision = rsmd.getPrecision(i); // 最大精度
if (scale == 0 && precision == 1&&JBoltProjectGenConfig.charToBoolean) {
cm.javaType = "java.lang.Boolean";
}
}
// 构造字段对应的属性名 attrName
cm.attrName = buildAttrName(cm.name);
// 备注字段赋值
if (generateRemarks) {
cm.remarks = remarkKv.getStr(cm.name);
}
tableMeta.columnMetas.add(cm);
}
rs.close();
stm.close();
}
@Override
protected ResultSet getTablesResultSet() throws SQLException {
setDialect(new SqlServerDialect());
ResultSet rs = dbMeta.getTables(conn.getCatalog(), null,"%", new String[]{"TABLE"});
return rs;
}
@Override
protected void buildTableNames(List<TableMeta> ret) throws SQLException {
ResultSet rs = getTablesResultSet();
while (rs.next()) {
String schem = rs.getString("TABLE_SCHEM");
String tableName = rs.getString("TABLE_Name");
if (schem.equals("sys")) {
JBoltConsoleUtil.printMessage(" Skip table :" + tableName + ",sys table");
continue;
}
if (excludedTables.contains(tableName)) {
JBoltConsoleUtil.printMessage(" Skip table :" + tableName);
continue;
}
if (isSkipTable(tableName)) {
JBoltConsoleUtil.printMessage(" Skip table :" + tableName);
continue;
}
TableMeta tableMeta = new TableMeta();
tableMeta.name = tableName;
if(generateRemarks) {
tableMeta.remarks =getTabelRemark(tableName);
}
tableMeta.modelName = buildModelName(tableName);
tableMeta.baseModelName = buildBaseModelName(tableMeta.modelName);
ret.add(tableMeta);
}
rs.close();
if(generateRemarks&&columnRemarkConn!=null&&columnRemarkConn.isClosed()==false) {
columnRemarkConn.close();
}
}
private String getTabelRemark(String table) throws SQLException {
Statement statement = conn.createStatement();
ResultSet rs = statement.executeQuery("SELECT cast(ds.value as varchar(200)) as remarks " +
" FROM sys.extended_properties ds" +
" LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id" +
" WHERE ds.minor_id=0 and tbs.name='"+table+"'");
String remark = null;
if(rs.next()) {
remark=rs.getString("remarks");
}
rs.close();
return remark;
}
private Kv buildTabelColumnRemark(String table) throws SQLException {
if(columnRemarkConn==null) {
columnRemarkConn=dataSource.getConnection();
}
Statement statement = columnRemarkConn.createStatement();
ResultSet rs = statement.executeQuery("SELECT cast(col.name as varchar(200)) AS name ," +
" cast(ISNULL(ep.[value], '') as varchar(200)) AS remarks" +
" FROM dbo.syscolumns col" +
" LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype" +
" inner JOIN dbo.sysobjects obj ON col.id = obj.id" +
" AND obj.xtype = 'U'" +
" AND obj.status >= 0" +
" LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id" +
" LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id" +
" AND col.colid = ep.minor_id" +
" AND ep.name = 'MS_Description'" +
" LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id" +
" AND epTwo.minor_id = 0" +
" AND epTwo.name = 'MS_Description'" +
" WHERE obj.name = '"+table+"'");
Kv kv=Kv.create();
while(rs.next()) {
kv.set(rs.getString("name"), rs.getString("remarks"));
}
rs.close();
return kv;
}
}这样再去执行代码生成就没有任何问题了！
表注释和字段注释都没问题了！
方案用的是笨办法，目前快速搞定，先用上，谁有更好的方法，可以交流。
咨询加入，加我微信：
mumengmeng
推荐
