Oracle資料庫中欄位定義為Char類型，Hibernate用該欄位進行動態綁定參數查詢，擷取不到結果的問題

一、問題背景 產生環境：oracle資料庫，hibernate操作 定義了一個表

 create table STORE_INFORMATION (   id            CHAR(32) not null,   name          VARCHAR2(254) default '',   content       CLOB default '',   create_time   VARCHAR2(26) default '',   cindex        NUMBER default 0,   status        VARCHAR2(4) default '0' not null,   nav_text      VARCHAR2(254) default '',   nav_image     VARCHAR2(254) default '',   note          VARCHAR2(1000) default '',   type          VARCHAR2(60) default '',   url           VARCHAR2(254) default '',   filename      VARCHAR2(254) default '',   update_time   VARCHAR2(26) default '',   filesize      VARCHAR2(60) default '',   flat          VARCHAR2(60) default '',   categoryid    VARCHAR2(40) default '0',   viewnumber    NUMBER default 0,   tag           VARCHAR2(254) default '',   sid           VARCHAR2(60) default '1',   creator       VARCHAR2(120) default '',   author        VARCHAR2(120) default '',   news_editor   VARCHAR2(120) default '',   news_from     VARCHAR2(120) default '',   pop_type      CHAR(32) default '',   app_usercount NUMBER default 0,   orgid         VARCHAR2(32),   isnew         NUMBER,   flag          NUMBER,   isupdate      NUMBER,   check_status  VARCHAR2(40),   check_time    VARCHAR2(26),   check_man     VARCHAR2(60),   checked_note  VARCHAR2(500),   store_id      CHAR(32),   store_name    VARCHAR2(254) )

其中store_id定義為CHAR（32）
程式中用Hibernate操作，新增和列表如下

/** * 新增 需要持續化的臨時對象 *  * @param po * @throws HibernateException */public void addBean(Object po) throws Exception {covertSpace(po);try {session = sessionFactory.openSession();transaction = session.beginTransaction();session.save(po);// session.flush();transaction.commit();} catch (Exception e) {transaction.rollback();PubLogs.dbLogError(new StringBuffer("新增操作失敗!").append("PubHibernate.addBean()"), e);throw e;} finally {if (session != null && session.isOpen()) {session.close();}}}

/** * 根據查詢條件得到查詢結果 *  * @param querySQL * @param map *            查詢條件 * @return 查詢結果 * @throws HibernateException */public List list(String querySQL, Map<String, Object> map, Pager page)throws Exception {List list = null;try {session = sessionFactory.openSession();String sql = rebuildOrgSql(querySQL, orgId, listAllOrgs);Query query = session.createQuery(sql);if (map != null) {for (String key : map.keySet()) {if (sql.indexOf(":" + key) != -1) {query.setParameter(key, map.get(key));System.out.println("param[" + key + "]==="+ map.get(key));}}}if (page != null) {query.setFirstResult(page.getFromRow());query.setMaxResults(page.getRowsPerPage());} else {query.setFirstResult(0);query.setMaxResults(20);}list = query.list();if (page != null) {Query countQuery = session.createQuery(countSql(sql));if (map != null) {for (String key : map.keySet()) {if (sql.indexOf(":" + key) != -1) {countQuery.setParameter(key, map.get(key));System.out.println("param[" + key + "]==="+ map.get(key));}}}if (countQuery != null) {List countlist = countQuery.list();if (countlist != null && countlist.size() > 0) {page.setTotalRow(((Number) countlist.get(0)).intValue());}}}} catch (Exception e) {e.printStackTrace();PubLogs.dbLogError(new StringBuffer("擷取查詢列表失敗!").append("PubHibernate.list(querySQL)").append("querySql=" + querySQL), e);throw e;} finally {if (session != null && session.isOpen()) {session.close();}}if (list != null) {covertNullToSpace(list);}return list;}

當添加資訊後，用如下語句查詢，沒有任何結果

sql.append("  from StoreInformation as si where 1=1");//按商戶搜尋sql.append(" and si.storeId= :storeId");paramMap.put("storeId", store.getId());sql.append(" order by si.updateTime desc");

二、問題分析 1）去掉按商戶搜尋的條件是可以的 2）拼接的形式是可以查詢到資訊的

sql.append(" and si.storeId='").append(store.getId()).append(",");//paramMap.put("storeId", store.getId());

3)用其他Varchar2類型的欄位動態綁定參數查詢是可以的

sql.append(" and si.checkStatus=:checkStatus");paramMap.put("checkStatus", checkStatus);

分析：該問題可能和欄位類型和hibernate的動態綁定有關係
對於該問題，oracle中給出了合理的解釋，摘抄一部分 https://community.oracle.com/message/506702

Bug in PreparedStatement with CHAR fields 396190Newbie396190 2003-6-5 上午10:57Select * From table Where column = ?setObject(1, "compValue")will never return anything if the type of column would be e.g. CHAR(20)This behaviour is inconsistent to executing the same select as statement in the following formStatement.executeQuery(Select * From table Where column = "compValue")which will return all rows, where the value matches.The difference in the behaviour lies in the fact, that for a PreparedStatment the number of characters must match.

use setFixedCHAR(....).,quote from Oracle9i JDBC API Docspublic void setFixedCHAR(int paramIndex,java.lang.String x)throws java.sql.SQLExceptionSets the disignated parameter to a String and executes a non-padded comparison with a SQL CHAR.CHAR data in the database is padded to the column width. This leads to a limitation in using the setCHAR() method to bind character data into the WHERE clause of a SELECT statement--the character data in the WHERE clause must also be padded to the column width to produce a match in the SELECT statement. This is especially troublesome if you do not know the column width.setFixedCHAR() remedies this. This method executes a non-padded comparison.Notes:* Remember to cast your prepared statement object to OraclePreparedStatement to use the setFixedCHAR() method.* There is no need to use setFixedCHAR() for an INSERT statement. The database always automatically pads the data to the column width as it inserts it.The following example demonstrates the difference between the setString(), setCHAR() and setFixedCHAR() methods.// Schema is : create table my_table (col1 char(10));// insert into my_table values ('JDBC');PreparedStatement pstmt = conn.prepareStatement("select count() from my_table where col1 = ?");ResultSet rs;pstmt.setString (1, "JDBC"); // Set the Bind Valuers = pstmt.executeQuery(); // This does not match any row// ... do something with rsCHAR ch = new CHAR("JDB ", null);((OraclePreparedStatement)pstmt).setCHAR(1, ch); // Pad it to 10 bytesrs = pstmt.executeQuery(); // This matches one row// ... do something with rs((OraclePreparedStatement)pstmt).setFixedCHAR(1, "JDBC");rs = pstmt.executeQuery(); // This matches one row// ... do something with rsParameters:paramIndex - index of the bind variable (first is 1)x - the literal/variable to be bound.hope this helpsElango.

大體意思就是說採用CHAR類型，是固定類型，如果長度不夠會用空格補齊，因此採用PreparedStatement動態參數綁定查詢時，要採用 OraclePreparedStatement 的setFixedCHAR() 設定char類型的欄位。
三、問題解決 Hibernate底層是基於PrepardStatement的，但是設定參數是沒辦法指定setFixedCHAR()，常用的時採用如下方式

query.setParameter(key, map.get(key));

因此使用setFixedCHAR()的方式行不太通。說下我的解決方式
1）將欄位trim化，去除空格（不推薦，如果在該欄位上有索引的化，索引會不起作用）

sql.append(" and trim(si.storeId)= :storeId");paramMap.put("storeId", store.getId());

2）將欄位類型改為varchar2類型 （項目中使用，推薦使用），另外建議優先使用varchar2類型
3）採用拼接的方式（不推薦，避免引起sql注入）


四、關於Oracle資料庫中是使用char還是varchar2可參考如下 https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:123212348063

摘抄如下：

A CHAR datatype and VARCHAR2 datatype are stored identically (eg: the word 'WORD' stored in a CHAR(4) and a varchar2(4) consume exactly the same amount of space on disk, both have leading byte counts). The difference between a CHAR and a VARCHAR is that a CHAR(n) will ALWAYS be N bytes long, it will be blank padded upon insert to ensure this. A varchar2(n) on the other hand will be 1 to N bytes long, it will NOT be blank padded. Using a CHAR on a varying width field can be a pain due to the search semantics of CHAR. Consider the following examples: ops$tkyte@8i> create table t ( x char(10) ); Table created. ops$tkyte@8i> insert into t values ( 'Hello' ); 1 row created. ops$tkyte@8i> select * from t where x = 'Hello'; X ---------- Hello ops$tkyte@8i> variable y varchar2(25) ops$tkyte@8i> exec :y := 'Hello' PL/SQL procedure successfully completed. ops$tkyte@8i> select * from t where x = :y; no rows selected ops$tkyte@8i> select * from t where x = rpad(:y,10); X ---------- Hello Notice how when doing the search with a varchar2 variable (almost every tool in the world uses this type), we have to rpad() it to get a hit. If the field is in fact ALWAYS 10 bytes long, using a CHAR will not hurt -- HOWEVER, it will not help either. The only time I personally use a CHAR type is for CHAR(1). And that is only because its faster to type char(1) then varchar2(1) -- it offers no advantages. 

<quote> The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot find their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed. ...... </quote> 

事實上，一個char/nchar實際上只是一個偽裝的VARCHAR2/NVARCHAR2，這使我認為只有兩個字串類型是可以考慮的，即VARCHAR2和NVARCHAR2。我從來沒有在任何應用程式中找到過CHAR類型的用法。由於CHAR類型始終是空白的，因此產生的字串是固定寬度的，因此我們很快就會發現，它在表段和任何索引段中都消耗了最大的儲存空間。這已經夠糟糕了，但是還有另外一個重要的原因要避免使用char/nchar類型:它們在需要檢索此資訊的應用程式中造成混亂(許多在儲存後無法找到它們的資料)。其原因與字串比較的規則和它們執行的嚴格性有關。