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

/**
	 * 新增 需要持续化的临时对象
	 * 
	 * @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");

二、问题分析

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

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

Bug in PreparedStatement with CHAR fields
 396190
Newbie
396190 2003-6-5 上午10:57
Select * 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 form

Statement.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 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 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 Value
rs = pstmt.executeQuery(); // This does not match any row
// ... do something with rs
CHAR ch = new CHAR("JDB ",null);
((OraclePreparedStatement)pstmt).setCHAR(1,ch); // Pad it to 10 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 (first is 1)x - the literal/variable to be bound.

hope this helps

Elango.

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

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

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类型:它们在需要检索此信息的应用程序中造成混乱(许多在存储后无法找到它们的数据)。其原因与字符串比较的规则和它们执行的严格性有关。