0|1一、驅動問題

一開始上網查詢，發現大家都推薦用JTDS驅動。但是JTDS貌似不能與官方的Hibernate相容,需要使用第三方Hibernate。

不然Hibernate在建立連線時會丟擲驅動不能轉換的異常。因為要做其他版本相容（程式碼不做大改動），

所以沒換成jtds的驅動（net.sourceforge.jtds.jdbc.Driver）。

然後用了ms2000的三個驅動。測試透過。但是要注意區分驅動和資料庫連線資訊的寫法

1 jdbc.drivers=com.microsoft.jdbc.sqlserver.SQLServerDriver2 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。

連結: 提取碼: kwaj

用了這個jar以後，就可以把上面三個ms2000的jar包刪掉了，而且驅動和資料庫連線資訊也可以和其他版本做統一。所以推薦這個方法

1 jdbc.drivers=com.microsoft.sqlserver.jdbc.SQLServerDriver2 jdbc.url=jdbc:sqlserver://localhost:1433;DatabaseName=COREJAVA

0|1二、系統表問題

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"
    ));

0|1三、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                 null8         );

則會丟擲異常：

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                 session28         )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 }

打包，除錯。果然沒問題了。

我的hibernate測試程式碼：

__EOF__

作　　者：
出　　處：https://www.cnblogs.com/braska/p/10483002.html

版權宣告：本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段宣告，且在文章頁面明顯位置給出原文連線，否則保留追究法律責任的權利。

Hibernate5.1+Sqlserver2000分頁查詢

0|1一、驅動問題

0|1二、系統表問題

0|1三、Hibernate5.1分頁問題

相關文章