The field in the Oracle database is defined as a char type, and Hibernate uses the field to dynamically bind the parameter query to get a problem with the result

I. Background of the problemProduction environment: Oracle database, hibernate operation defines a table

 CREATE TABLE Store_information (ID CHAR () not NULL, name VARCHAR2 (254) Default ", Conte NT CLOB default ', create_time VARCHAR2 default ', CIndex number default 0, status VA RCHAR2 (4) Default ' 0 ' NOT NULL, Nav_text VARCHAR2 (254) Default ', Nav_image VARCHAR2 (254) Default ', n OTE VARCHAR2 (1000) Default ', type VARCHAR2 default ', URL VARCHAR2 (254) Default ' ', filename VARCHAR2 (254) Default ', Update_time VARCHAR2 (+) Default ', FileSize VARCHAR2 Ult ', flat VARCHAR2 default ', CategoryID VARCHAR2 (+) Default ' 0 ', viewnumber number Defau  Lt 0, Tag VARCHAR2 (254) Default ', Sid VARCHAR2 (a) Default ' 1 ', creator VARCHAR2 (120) Default ', author VARCHAR2 default ', News_editor VARCHAR2 (=) Default ', News_from varcha R2 (=) default ', pOp_type CHAR Default ', App_usercount number default 0, OrgID VARCHAR2, IsNew Numbe R, flag number, isupdate number, Check_status VARCHAR2 (a), Check_time VARCHAR2 (), CHEC K_man VARCHAR2, Checked_note VARCHAR2 (+), store_id CHAR (+), Store_name VARCHAR2 (254))

Where store_id is defined as char (32)
Program with Hibernate action, add and list as follows

/**
	 * New Temporary object to be continued
	 * * 
	 @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 ("New operation failed!")
					. Append ("Pubhibernate.addbean ()"), e);
			throw e;
		} Finally {
			if (session!= null && Session.isopen ()) {
				session.close ();
			}
		}
	}

/** * Query results based on query criteria * * @param querysql * @param map * Query conditions * @return Query results * @throws HIBERNATEEXC Eption/Public List list (String querysql, map<string, object> Map, Pager page) throws Exception {List lis
		t = 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 ("Get query list failed!").
			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; }

When you add the information, query with the following statement without any results

Sql.append ("from  storeinformation as Si where 1=1");

		Search Sql.append by Merchant
		("and si.storeid=: StoreId");
		Parammap.put ("StoreId", Store.getid ());

		Sql.append ("ORDER by si.updatetime Desc");

Second, the problem analysis1) to remove the search according to the terms of the merchant can be 2 of the form of splicing can be queried to the information

Sql.append ("and si.storeid= '"). Append (Store.getid ()). Append (",");		parammap.put ("StoreId", Store.getid ());

3 It is possible to dynamically bind parameter queries with other VARCHAR2 types of fields

Sql.append ("and Si.checkstatus=:checkstatus");
			Parammap.put ("CheckStatus", checkstatus);

Analysis: This problem may be related to the dynamic binding of the field type and hibernate
For this issue, Oracle gives a reasonable explanation, excerpt a part of the https://community.oracle.com/message/506702

Bug in PreparedStatement with CHAR fields
 396190
newbie
396190 2003-6-5 a.m. 10:57
Select * FROM table Where Column =?
SetObject (1, "Compvalue") would

never return anything if the type of column would. CHAR e.g

I s inconsistent to executing the same select as statement in the following form

statement.executequery (select * FROM TA ble where column = "Compvalue") which'll return all

rows, where the value matches.

The difference in the behaviour lies in the fact, which for a preparedstatment the number of characters must match.

Use Setfixedchar (...), quote from Oracle9i JDBC API Docs public void Setfixedchar (int paramindex, java.lang.String x) Throws Java.sql.SQLException Sets the disignated parameter to a String and executes a non-padded comparison with a SQL C

HAR. CHAR data in the "database is padded to" column width. This is leads to a limitation by using the SetChar () method to bind character data into the WHERE clause of a SELECT statemen T--the character data in the WHERE clause must also is padded to the "column width to produce a" match in the SELECT Stateme Nt.

This is especially troublesome if does 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 a 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 Value rs = Pstmt.executequery ();
This does isn't match any row//... do something with rs char ch = new CHAR ("Jdb", null); ((oraclepreparedstatement) pstmt). SetChar (1, ch); Pad it to ten bytes rs = 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 Rs parameters:paramindex-index of the bind variable (1) x-t

He literal/variable to be bound. Hope this helps Elango.

The general meaning is the use of char type, is a fixed type, if the length is not enough to be padded with space, so the use of PreparedStatement dynamic parameter binding query, to use the Oraclepreparedstatement Setfixedchar () Sets the field for the char type.
iii. problem-solvingThe hibernate bottom is based on prepardstatement, but setting parameters is no way to specify Setfixedchar (), which is used in the following ways

Query.setparameter (Key, Map.get (key));

So using Setfixedchar () is not a good way to do it. The way I'm dealing with it.
1 to trim the field, remove the space (not recommended, if the index on the field, the index will not work)

Sql.append ("and trim (si.storeid) =: StoreId");
		Parammap.put ("StoreId", Store.getid ());

2 Change the field type to the VARCHAR2 type (used in the project, recommended), and recommend that the VARCHAR2 type be used first
3 Use splicing method (not recommended, avoid causing SQL injection)


whether to use char or VARCHAR2 in the Oracle database can refer to the followinghttps://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P 11_question_id:123212348063

Excerpt as follows:

A Char datatype and VARCHAR2 datatype are stored identically (eg:the word ' word ' stored in a char (4) and a VARCHAR2 (4) CO 

Nsume exactly the same amount of spaces on disk, both have leading byte counts). The difference between a char and a VARCHAR is that a char (n) would ALWAYS be n bytes long, it would be blank padded SERT to ensure this. 

A varchar2 (n) on the other hand'll be 1 to n bytes long, it won't be blank padded. 

Using a char on a varying width field can is 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 ops$tkyte@8i> exec:y: = ' Hello ' pl/sql procedure 

Cessfully 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 to doing the search with a VARCHAR2 variable (almost every tool into the world uses thi 

S type), we have to Rpad () it to get a hit. 

If The field is in fact ALWAYS bytes long, the using a CHAR would not hurt-HOWEVER, it won't help either. The only time I personally use a CHAR type are for char (1).  And that's only because its faster to type char (1) then VARCHAR2 (1)--it offers no advantages.

<quote> The 
fact that a char/nchar are really nothing more than a varchar2/nvarchar2 in disguise 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 maximu M storage both in the table segment and any index segments. That would was bad enough, but there is another important the reason to avoid char/nchar the create Types:they in confusion Ations that need to retrieve this information (many cannot find their data after storing it). The reason for this relates to the rules of the character string comparison and the strictness with which they are. ...... 

In fact, a char/nchar is actually just a disguised varchar2/nvarchar2, which makes me think that only two string types can be considered, namely VARCHAR2 and NVARCHAR2. I have never found the use of the char type in any application. Because the char type is always blank, the resulting string is fixed-width, so we quickly find that it consumes the largest storage space in the table segment and any index segments. This is bad enough, but there is another important reason to avoid using the Char/nchar type: they create confusion in applications that need to retrieve this information (many of the data that cannot be found after they are stored). The reason is related to the rules for string comparisons and the rigor with which they are enforced.