informix的char字段类型通过hibernate的sql查询仅返回第一个字符之本人解决方法

刚刚开始一个项目，后台数据库用informix，准备采用ssh的框架，框架搭建完在今天测试的过程中，发现表字段为char类型的时候，通过下面的查询语句，获取到的值总是只有第一个字符：

SQLQuery sqlQuery = session.createSQLQuery(querySql);
sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
result = sqlQuery.list();

网上搜了下原来这还是一个普遍的问题，原因如下：

char类型映射出Java对象是Character，说明char在Java中只有两个字节（一个字符）

 网上普遍的解决方法如下：

1、改表字段类型，改为varchar；

 2、改查询语句，显示的指明返回STRING类型：

SQLQuery query = session.createSQLQuery(); 
query.addScalar(columnAlias,Type);

但是这两中方法都不能解决我的实际问题，主要有以下几点原因：

 1、不能改表字段类型的，所以方法1否定；

 2、我的查询方法不是针对某个具体的表，我的这个是个公共的查询方法，通过参数传入表名称，然后查询出对应数据，

       而方法二的前提是要查询的表已经确定，且还要把要查询的参数都通过addScalar添加进来才可以返回；

在网上搜了半天无助的时候，发现该篇文章，给了我很大的启发，真感谢这位作者，文章地址：

http://hi.baidu.com/suofang/item/79e10913d762b9f99c778aeb

该文章具体方法如下：

public class Oracle9iDialect extends org.hibernate.dialect.Oracle9iDialect

       {
public Oracle9iDialect() 
{
  super(); 
  registerHibernateType(Types.CHAR, Hibernate.STRING.getName()); 
 
}
}     

文章是oracle的问题，通过实现自定义的dialect来解决了问题，在dialect初始化的时候，将char的类型注册为string，然后在spring配置自定义的dialect；

赶快自己也继承InformixDialect实现一个自定义的dialect，按照上面的方式写好代码运行，突然眼前一黑，发现该方法在informix行不通，取出来的还是一个字符。

找到InformixDialect实现的源码，发现InformixDialect是继承自Dialect（abstract），然后在Dialect的构造函数里面还是将char类型注册为Character，无奈之下

只好从头到脚都来做一次变成：

首先实现自己的Dialect，继承自原有的Dialect，修改构造方法里面将char类型注册为String，如下所示：

public class SkiteDialect extends Dialect
{
public SkiteDialect()
{
super();
registerHibernateType(1, StandardBasicTypes.STRING.getName());
registerHibernateType(1, 1, StandardBasicTypes.STRING.getName());
registerHibernateType(1, 255, StandardBasicTypes.STRING.getName());
}
}

完了后再定义自己的InformixDialect继承自SkiteDialect，仅在原有的InformixDialect上修改构造方法，如下所示：

package com.ssh.base.hibernate;


import java.sql.SQLException;
import org.hibernate.MappingException;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.exception.JDBCExceptionHelper;
import org.hibernate.exception.TemplatedViolatedConstraintNameExtracter;
import org.hibernate.exception.ViolatedConstraintNameExtracter;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.util.StringHelper;


/**
*
* @ClassName: InformixDialect
* @Description: TODO 重新定义自己的infomixDialect，在构造方法里改变char类型的字段映射为string
* @author skite
* @date 2014-6-27 上午10:28:55
*/
public class SkiteInformixDialect extends SkiteDialect
{
private static ViolatedConstraintNameExtracter EXTRACTER = new TemplatedViolatedConstraintNameExtracter()
{
public String extractConstraintName(SQLException sqle)
{
String constraintName = null;


int errorCode = JDBCExceptionHelper.extractErrorCode(sqle);
if (errorCode == -268)
{
constraintName = extractUsingTemplate("Unique constraint (", ") violated.", sqle.getMessage());
}
else if (errorCode == -691)
{
constraintName = extractUsingTemplate("Missing key in referenced table for referential constraint (", ").", sqle.getMessage());
}
else if (errorCode == -692)
{
constraintName = extractUsingTemplate("Key value for constraint (", ") is still being referenced.", sqle.getMessage());
}


if (constraintName != null)
{
int i = constraintName.indexOf(46);
if (i != -1)
constraintName = constraintName.substring(i + 1);


}


return constraintName;
}
};


public SkiteInformixDialect()
{
registerColumnType(-5, "int8");
registerColumnType(-2, "byte");
registerColumnType(-7, "smallint");
registerColumnType(1, StandardBasicTypes.STRING.getName());
registerColumnType(91, "date");
registerColumnType(3, "decimal");
registerColumnType(8, "float");
registerColumnType(6, "smallfloat");
registerColumnType(4, "integer");
registerColumnType(-4, "blob");
registerColumnType(-1, "clob");
registerColumnType(2, "decimal");
registerColumnType(7, "smallfloat");
registerColumnType(5, "smallint");
registerColumnType(93, "datetime year to fraction(5)");
registerColumnType(92, "datetime hour to second");
registerColumnType(-6, "smallint");
registerColumnType(-3, "byte");
registerColumnType(12, "varchar($l)");
registerColumnType(12, 255, "varchar($l)");
registerColumnType(12, 32739, "lvarchar($l)");


registerFunction("concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "(", "||", ")"));
}


public String getAddColumnString()
{
return "add";
}


public boolean supportsIdentityColumns()
{
return true;
}


public String getIdentitySelectString(String table, String column, int type) throws MappingException
{
return ((type == -5) ? "select dbinfo('serial8') from informix.systables where tabid=1"
: "select dbinfo('sqlca.sqlerrd1') from informix.systables where tabid=1");
}


public String getIdentityColumnString(int type) throws MappingException
{
return ((type == -5) ? "serial8 not null" : "serial not null");
}


public boolean hasDataTypeInIdentityColumn()
{
return false;
}


public String getAddForeignKeyConstraintString(String constraintName, String[] foreignKey, String referencedTable, String[] primaryKey,
boolean referencesPrimaryKey)
{
StringBuffer result = new StringBuffer(30).append(" add constraint ").append(" foreign key (").append(StringHelper.join(", ", foreignKey))
.append(") references ").append(referencedTable);


if (!(referencesPrimaryKey))
{
result.append(" (").append(StringHelper.join(", ", primaryKey)).append(')');
}


result.append(" constraint ").append(constraintName);


return result.toString();
}


public String getAddPrimaryKeyConstraintString(String constraintName)
{
return " add constraint primary key constraint " + constraintName + " ";
}


public String getCreateSequenceString(String sequenceName)
{
return "create sequence " + sequenceName;
}


public String getDropSequenceString(String sequenceName)
{
return "drop sequence " + sequenceName + " restrict";
}


public String getSequenceNextValString(String sequenceName)
{
return "select " + getSelectSequenceNextValString(sequenceName) + " from informix.systables where tabid=1";
}


public String getSelectSequenceNextValString(String sequenceName)
{
return sequenceName + ".nextval";
}


public boolean supportsSequences()
{
return true;
}


public boolean supportsPooledSequences()
{
return true;
}


public String getQuerySequencesString()
{
return "select tabname from informix.systables where tabtype='Q'";
}


public boolean supportsLimit()
{
return true;
}


public boolean useMaxForLimit()
{
return true;
}


public boolean supportsLimitOffset()
{
return false;
}


public String getLimitString(String querySelect, int offset, int limit)
{
if (offset > 0)
throw new UnsupportedOperationException("query result offset is not supported");


return new StringBuffer(querySelect.length() + 8).append(querySelect).insert(querySelect.toLowerCase().indexOf("select") + 6, " first " + limit)
.toString();
}


public boolean supportsVariableLimit()
{
return false;
}


public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter()
{
return EXTRACTER;
}


public boolean supportsCurrentTimestampSelection()
{
return true;
}


public boolean isCurrentTimestampSelectStringCallable()
{
return false;
}


public String getCurrentTimestampSelectString()
{
return "select distinct current timestamp from informix.systables";
}
}

完了后再Spring的配置文件里配置上自己改写后的informixDialect，如下所示：

hibernate.dialect=com.ssh.base.hibernate.SkiteInformixDialect

重启服务，查询库表，一下子感觉世界又精彩了，终于查询出来了完整的数据。目前按此修改还没有遇到其他什么问题，后续有问题再补充。。。。。