Hibernate5.1+Sqlserver2000分页查询
前几天改到一个bug：从MS SQLserver上面同步表结构并且采集数据写入其他库。然后用的核心技术是用的Hibernate。
其中bug出在SQLServer2000版本上。排查下来发现2000版本真的是一个让人头疼的数据库。
驱动jar包不兼容；hibernate5.1分页查询也不能用。系统表也与其他版本的天差地别。
一、驱动问题
一开始上网查询，发现大家都推荐用JTDS驱动。但是JTDS貌似不能与官方的Hibernate兼容,需要使用第三方Hibernate。
不然Hibernate在建立连接时会抛出驱动不能转换的异常。因为要做其他版本兼容（代码不做大改动），
所以没换成jtds的驱动（net.sourceforge.jtds.jdbc.Driver）。
然后用了ms2000的三个驱动。测试通过。但是要注意区分驱动和数据库连接信息的写法
1 jdbc.drivers=com.microsoft.jdbc.sqlserver.SQLServerDriver 2 jdbc.url=jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=test
在查找分页问题时，偶然发现了一个更方便的方法。
资料链接：https://blog.csdn.net/hexin373/article/details/8260752
maven中央库里的sqljdbc4是不行的。这里我特地下载了 sqljdbc_3.0.1301.101_chs.tar.gz。
链接: https://pan.baidu.com/s/1CkZeHRYg5V-LxhhmVbGY8A 提取码: kwaj
用了这个jar以后，就可以把上面三个ms2000的jar包删掉了，而且驱动和数据库连接信息也可以和其他版本做统一。所以推荐这个方法
1 jdbc.drivers=com.microsoft.sqlserver.jdbc.SQLServerDriver 2 jdbc.url=jdbc:sqlserver://localhost:1433;DatabaseName=COREJAVA
二、系统表问题
ms2005版本以后系统表做了很多增删，比如：sys.extended_properties等。这些在ms2000都没有。
但是这个问题比较好改。
这边放出两段查询表结构的语句，不过语句还没来得及优化。
private static final String STRUCT_SQL_FORMAT = Joiner.on("\n").join(Arrays.asList( "SELECT convert(varchar(100), h.TABLE_CATALOG) AS TABLE_CATALOG, ", "upper(convert(varchar(100), h.TABLE_NAME)) AS TABLE_NAME, ", "convert(varchar(100), h.TABLE_TYPE) AS TABLE_TYPE, ", "convert(varchar(100), h.value ) AS TABLE_COMMENT, ", "upper(convert(varchar(100), a.name)) AS COLUMN_NAME,", "convert(varchar(100), b.name) AS COLUMN_TYPE,", "convert(varchar(100), COLUMNPROPERTY(a.id,a.name,'PRECISION')) AS COLUMN_LENGTH,", "convert(varchar(100), isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)) AS NUM_SCALE,", "convert(varchar(100), case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid ))) then 'YES' else 'NO' end) AS IS_PRIMARYKEY, ", "convert(varchar(100), case when a.isnullable=1 then 'YES'else 'NO' end) AS IS_NULLABLE, ", "convert(varchar(100), isnull(g.[value],'')) AS COLUMN_COMMENT, ", "dc.definition COLUMN_DEFAULT", "FROM syscolumns a ", "left join systypes b on a.xusertype = b.xusertype ", "inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name <> 'dtproperties' ", "left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id ", "left join (", " select a.TABLE_CATALOG, a.TABLE_NAME, a.TABLE_TYPE, b.value from information_schema.tables a ", " left join sys.extended_properties b on b.major_id = OBJECT_ID(a.TABLE_NAME) and b.minor_id = 0 ", ") h on h.TABLE_NAME = d.name ", "LEFT JOIN sys.default_constraints dc ON d.id=dc.parent_object_id AND a.colid=dc.parent_column_id AND a.cdefault=dc.[object_id]", "order by d.name " )); private static final String STRUCT_SQL_FORMAT_FOR_2000 = Joiner.on("\n").join(Arrays.asList( "select convert(varchar(100), h.TABLE_CATALOG) AS TABLE_CATALOG, ", "upper(convert(varchar(100), h.TABLE_NAME)) AS TABLE_NAME, ", "convert(varchar(100), h.TABLE_TYPE) AS TABLE_TYPE, ", "convert(varchar(100), ta.TABLE_COMMENT ) AS TABLE_COMMENT, ", "upper(convert(varchar(100), ta.NAME)) AS COLUMN_NAME, ", "convert(varchar(100), ta.COLUMN_TYPE) AS COLUMN_TYPE, ", "convert(varchar(100), ta.COLUMN_LENGTH) AS COLUMN_LENGTH, ", "convert(varchar(100), isnull(ta.NUM_SCALE,0)) AS NUM_SCALE, ", "convert(varchar(100), ta.IS_PRIMARYKEY) AS IS_PRIMARYKEY, ", "convert(varchar(100), ta.IS_NULLABLE) AS IS_NULLABLE, ", "convert(varchar(100), ta.COLUMN_COMMENT) AS COLUMN_COMMENT, ", "convert(varchar(100), ta.COLUMN_DEFAULT) AS COLUMN_DEFAULT ", " FROM ", "(SELECT ", " TABLE_NAME = d.name, ", " TABLE_COMMENT = isnull(f. VALUE, ''), ", " NAME = a.name, ", " IS_PRIMARYKEY = CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN ( SELECT name FROM sysindexes WHERE indid IN ( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid))) THEN 'YES' ELSE 'NO' END, ", " COLUMN_TYPE = b.name, ", " COLUMN_LENGTH = COLUMNPROPERTY(a.id, a.name, 'PRECISION'), ", " NUM_SCALE = isnull(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0), ", " IS_NULLABLE = CASE WHEN a.isnullable = 1 THEN 'YES' ELSE 'NO' END, ", " COLUMN_DEFAULT = isnull(e. TEXT, ''), ", " COLUMN_COMMENT = isnull(g.[value], '') ", "FROM ", "\tsyscolumns a ", "LEFT JOIN systypes b ON a.xusertype = b.xusertype ", "INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' ", "LEFT JOIN syscomments e ON a.cdefault = e.id ", "LEFT JOIN sysproperties g ON a.id = g.id AND a.colid = g.smallid ", "LEFT JOIN sysproperties f ON d.id = f.id AND f.smallid = 0 ", ") ta ", "left join information_schema.tables h on h.TABLE_NAME = ta.table_name" ));
三、Hibernate5.1分页问题
最头疼的问题。我不懂是Hibernate官方根本没测试过ms2000的分页，还是我的用法有问题。
1 public <T> List<T> executeQuery(final String sqlString, Integer current, Integer maxResult, Integer fetchSize, Class<T> t, Object... parameters) throws SqlExecutionException { 2 List<T> rowsList; 3 Session session = sessionLocal.get(); 4 try { 5 Query query = session.createSQLQuery(sqlString); 6 if (current != null) { 7 query.setFirstResult(current); 8 } 9 10 if (maxResult != null && maxResult > 0) { 11 query = query.setMaxResults(maxResult); 12 } 13 14 if (fetchSize != null && fetchSize > 0) { 15 query = query.setFetchSize(fetchSize); 16 } 17 18 if (parameters != null && parameters.length > 0) { 19 for (int i = 0; i < parameters.length; i++) { 20 query.setParameter(i, parameters[i]); 21 } 22 } 23 24 rowsList = query.setResultTransformer(Transformers.aliasToBean(t)).list(); 25 } catch (Exception e) { 26 throw new SqlExecutionException(sqlString, e.getCause()); 27 } finally { 28 session.close(); 29 session = null; 30 sessionLocal.remove(); 31 } 32 return rowsList; 33 }
上面代码是我做的Hibernate分页封装。maxResult实际上相当于pageSize。如果maxResult为空则运行正常。但一旦指定了maxResult，就会报错。
调用上面方法：
1 List<HashMap> data = session.executeQuery( 2 "select * from test", 3 0, 4 100, 5 100, 6 HashMap.class, 7 null 8 );
则会抛出异常：
1 com.syher.hibernate.jdbc.exception.SqlExecutionException: 第 1 行: '@P0' 附近有语法错误。
什么原因导致的？明明就一条简单的查询语句啊？
跟踪Hibernate源代码到Loader的executeQueryStatement方法。
1 protected SqlStatementWrapper executeQueryStatement( 2 String sqlStatement, 3 QueryParameters queryParameters, 4 boolean scroll, 5 List<AfterLoadAction> afterLoadActions, 6 SessionImplementor session) throws SQLException { 7 8 // Processing query filters. 9 queryParameters.processFilters( sqlStatement, session ); 10 11 // Applying LIMIT clause. 12 final LimitHandler limitHandler = getLimitHandler( 13 queryParameters.getRowSelection() 14 ); 15 String sql = limitHandler.processSql( queryParameters.getFilteredSQL(), queryParameters.getRowSelection() ); 16 17 // Adding locks and comments. 18 sql = preprocessSQL( sql, queryParameters, getFactory().getDialect(), afterLoadActions ); 19 20 final PreparedStatement st = prepareQueryStatement( sql, queryParameters, limitHandler, scroll, session ); 21 return new SqlStatementWrapper( 22 st, getResultSet( 23 st, 24 queryParameters.getRowSelection(), 25 limitHandler, 26 queryParameters.hasAutoDiscoverScalarTypes(), 27 session 28 ) 29 ); 30 }
LimitHandler类是把我们的sql语句加工成分页语句的类。
在这里，我们的sql语句select * from test 经过limitHandler.processSql方法处理后， 会变成 select top ? * from test;
1 @Override 2 public String processSql(String sql, RowSelection selection) { 3 if (LimitHelper.hasFirstRow( selection )) { 4 throw new UnsupportedOperationException( "query result offset is not supported" ); 5 } 6 7 final int selectIndex = sql.toLowerCase(Locale.ROOT).indexOf( "select" ); 8 final int selectDistinctIndex = sql.toLowerCase(Locale.ROOT).indexOf( "select distinct" ); 9 final int insertionPoint = selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6); 10 11 return new StringBuilder( sql.length() + 8 ) 12 .append( sql ) 13 .insert( insertionPoint, " TOP ? " ) 14 .toString(); 15 }
上网查了一下，jdbc prepareStatement预编译不支持top ?的写法。然后我特地写了个jdbc的demo验证，发现问题也确实出在jdbc。
@Test public void run() { try { String sql = "SELECT TOP ?* FROM test"; Connection conn = getJDBCConnection(); PreparedStatement pst = conn.prepareStatement(sql); pst.setInt(1, 10); ResultSet rs = pst.executeQuery(); } catch (Exception e) { e.printStackTrace(); } } public static Connection getJDBCConnection() throws IOException { Connection conn = null; String drivers = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //String drivers = "com.microsoft.jdbc.sqlserver.SQLServerDriver"; String url = "jdbc:sqlserver://192.168.2.173:1433;DatabaseName=dbtest"; //String url = "jdbc:microsoft:sqlserver://192.168.2.173:1433;DatabaseName=dbtest"; //String url = "jdbc:sqlserver://192.168.3.104:1433;DatabaseName=testdb"; String userName = "sa"; String password = "sa@173"; //String password = "sa@104"; if (drivers != null) { try { Class.forName(drivers).newInstance(); conn = DriverManager.getConnection(url, userName, password); } catch (Exception e) { e.printStackTrace(); System.out.println("数据库连接失败"); } } else { System.out.println("数据库驱动不存在"); } return conn; }
上面demo里，ms2008、ms2016都不会有问题。而只有ms2000抛出了“@P0′ 附近有语法错误。”的异常。
抓耳挠腮了两天之后，终于在一篇博客里面找到了灵感。
参考资料：https://gcy6164.iteye.com/blog/1160119
一开始看到这篇资料时，我是确实按着博客步骤改，结果没用。遂放弃。
然后某天在TopLimitHandler类中看到了博客中的supportsLimit方法，才恍然大悟。原来是自己改错了方向。
我没看过Hibernate3.2的源码，但是大致猜测Hibernate3.2中应该是没有LimitHandler类的。所以Hibernate3.2中判断
数据库是否支持分页是在SQLServerDialect中。而Hibernate5.1时为了更好的扩展，增加了LimitHandler专门处理分页语句的接口。
而判断数据库是否支持分页的方法也转移到了这个类中。
因此Hibernate3.2的修改教程不适合Hibernate5.1。但其实是同一个解决思路。
于是我自定义了一个Hibernate方言继承了SQLServerDialect，并重写了getLimitHandler方法。
1 public class SQLServer2000Dialect extends SQLServerDialect { 2 3 public SQLServer2000Dialect() { 4 super(); 5 } 6 7 @Override 8 public LimitHandler getLimitHandler() { 9 return new SQLServer2000LimitHandler(false, false); 10 } 11 }
自定义了SQLServer2000LimitHandler类，并修改了supportsLimit方法。
1 public class SQLServer2000LimitHandler extends TopLimitHandler { 2 public SQLServer2000LimitHandler(boolean supportsVariableLimit, boolean bindLimitParametersFirst) { 3 super(supportsVariableLimit, bindLimitParametersFirst); 4 } 5 6 @Override 7 public boolean supportsLimit() { 8 return false; 9 } 10 }
打包，调试。果然没问题了。
版权声明：本文为braska原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。