Hibernate對substring和cast的支援問題

 這次徒弟又遇到了hibernate的問題。。。。。。

要比較使用者的生日。。。這是只取MM-dd格式的日期，而資料庫中記錄的當然是最詳細的yyyy-MM-dd的格式。。。。這使用SQL很簡單，substring(convert(varchar(10),birthday,120),6,5)

但是放到HQL中就出問題了。。。。

hibernate竟然把它截為了substring(convert(varchar(10),birthday,120))。。。。。然後執行的時候SQL就報錯，說substring需要三個參數，我暈阿，後來多處尋找問題，竟然無人提到過相關的內容。。。但是hibernate明明支援substring的方法的，在單獨處理字串欄位的時候一直都是可以的，後來仔細考慮了下，估計hibernate在處理substring+convert的時候可能處理不好，因為兩個都是3個參數的方法，估計hibernate把convert裡面的兩個參數當作substring的了，於是出了問題，這應該是hibernate的一個bug。

 

於是後來想用cast+month的方法：

cast(month(birthday) as varchar)。。。結果發現hibernate對cast支援也有問題，竟然報錯。。。多處找了下資料，才明白原來hibernate對cast的支援是特別處理的，cast(arg1 as arg2)中arg2的轉換類型應該是hibernate類型，而不是資料庫中的類型，，我暈倒。。把varchar換成string後就好了，不過發現依然不對。。1月份的話，出來的就是1，不是01。。這樣無法對+0的日期格式比較，結果想到了另外兩個方法。。。把月份+日期處理成浮點。。月份.日期。。。01-11處理成1.11，11-09處理成11.09，這樣就可以比較了，不過比較麻煩。。。但至少問題解決。

 

後來又想到了另一個方法：

把記錄中使用者生日的年份取出來，添加到參數的前面，這樣convert(varchar(10),birthday,120) >= cast(year(birthday) as string)+'-'+'11-01'，這種方法也可以，而且比上面的處理方法簡單。。。

 

再後來想到了一個在hibernate中使用substring+convert的方法，就是對錶對象po添加一個新屬性，shortbirthday，並在設定檔中如下定義：

<property name="shortbirthday" type="string" insert="false" update="false" length="5">
         <formula>substring(convert(varchar(10),dbirthday,120),6,5)</formula>
        </property>

這樣就可以在hql中直接使用shortbirthday屬性來與參數比較了。。而且hibernate對此是支援的。。。那當然這個又比上面的方法簡單了，但是唯一的問題就是，每次查詢使用者的時候，hibernate都會自動把這個屬性查出來，這就影響了查詢效能，我只有在查詢使用者生日的時候才會用到這個屬性阿。。。這個讓人比較頭大，但應該不會影響太大，不過對於要求比較高的我來說。。我不會讓這種問題發生的。。。所以我寧願選擇上面的那種方法，雖然稍微麻煩了點，但是不會對別的查詢產生影響。

 

最後，在我以為找到了最佳解決方案的時候，另一個同事找到了一個更好的方法，是目前為止我覺得最好的方法，而且我也覺得應該是這個問題解決得最佳方法，就是靈活運用sql的convert。。。

其實之前我們就一直在考慮如何使用一個sql函數來得到月份+日期的方法，當時大家都被yyyy-MM-dd的格式限定，所以都沒有想到，以為是沒有辦法的，而事實上covert的強大足以解決該問題了。。。

熟悉convert的應該都知道convert的第一個參數的長度也就是決定最終截取格式的長度，並且是從第一個位置開始截取的。。那麼我們一直都在convert第三個參數120中考慮問題，從沒有想到過別的參數能夠直接顯示月份+日期的，而事實上它是有參數提供位MM-dd-yyyy格式的。。那麼我們利用第一參數的長度和第三個參數的格式就能夠得到我們最終需要的月份+日期的格式了。。。這也就避免了hibernate中HQL的尷尬。。。

詳細如下：

CONVERT(char(5), GETDATE(), 110)

這裡的110換成5也可以，詳細地convert的參數可以參考本blog的前一篇文章