如何设置Hibernate不使用PreparedStatement？

[ddpie]

我使用Hibernate查询数据库出现参数个数超过了2000个的问题，应该是JDBC驱动不支持超过2000个参数的SQL语句，请问如何设置Hibernate不使用PreparedStatement的查询方式，或者如何解决参数个数超过2000个的问题呢？
Hibernate版本：3.2
数据库：SQL Server 2000
JDBC驱动：jtds 1.2

日志如下：
2008-09-23 10:25:23,660 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [select this_.ID as ID18_0_, this_.GUID as GUID18_0_, this_.User_Name as User3_18_0_, this_.Log_Time as Log4_18_0_, this_.Host_IP as Host5_18_0_, this_.Policy_Name as Policy6_18_0_, this_.Policy_Status as Policy7_18_0_ from My_Feedback_log this_ where this_.User_Name in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, (帖子过长，省略若干问号)?, ?) and this_.Log_Time between ? and ?]
java.sql.SQLException: Prepared or callable statement has more than 2000 parameter markers.
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1139)
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:156)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.<init>(JtdsPreparedStatement.java:104)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2221)
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2179)
at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:213)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:497)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:415)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1538)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2211)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2095)
at org.hibernate.loader.Loader.list(Loader.java:2090)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1569)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)

[Landor2004]

直接把sql语句拼接起来，可否

比如String sql = "select id,......where User_Name in "+参数集合str+" and ......"

[ddpie]

现有代码使用的是好hibernate的条件查询方式（Criteria），不能直接对sql语句操作，关键就是有没有简单的方式让Hibernate不使用PreparedStatement方式检索数据，或者如何通过修改Hibernate源代码的形式实现也可以。

[deeplyloving]

lz相当的牛..两千个参数那是啥业务..

public List getListByCriteria(final DetachedCriteria detachedCriteria) {
return (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException {
Criteria criteria = detachedCriteria
.getExecutableCriteria(session);
return criteria.list();
}
}, true);
}

创建 DetachedCriteria

DetachedCriteria dc = DetachedCriteria.forClass(Brand.class)
.createAlias("barCodeRule", "barCodeRule",
DetachedCriteria.LEFT_JOIN);

ProjectionList projectionList = Projections.projectionList().add(
Projections.property("id").as("id")).add(
Projections.property("code").as("code")).add(
Projections.property("name").as("name")).add(
Projections.property("remark").as("remark")).add(
Projections.property("barCodeRule.id").as("barCodeRuleId"))
.add(
Projections.property("barCodeRule.name").as(
"barCodeRuleName")).add(
Projections.property("enName").as("enName"));
dc = dc.setProjection(projectionList).setResultTransformer(
Criteria.ALIAS_TO_ENTITY_MAP);

你试试这种方式能不能行

[Landor2004]

where this_.User_Name in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?

你是用的好的Criteria，但是in里面一个大参数，而不是一堆小参数！如果这么多参数就说明不能这么用
比如你可以这样
String str1 = "'a','b','c'";
criteria.add(Restrictions.sqlRestriction("userName in ("+str1+")"))

如果你硬要去搞hibernate内部代码，那么办法！

[Landor2004]

userName 是数据库的字段

[billwindows]

可以直接 使用 Hibernate 的 JDBC Session Factory , 配置文件利用 这个
<property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>

public class HibernateInit
    implements ServletContextListener {
  public void contextDestroyed(ServletContextEvent servletContextEvent) {
  }

  public void contextInitialized(ServletContextEvent servletContextEvent) {
    try {
      Configuration conf = new Configuration().configure();
      SessionFactory sf = conf.buildSessionFactory();
    }
    catch (Exception e) {
      e.printStackTrace();
    }
  }
}

public class HibernateSessionFactory {

    /** 
     * Location of hibernate.cfg.xml file.
     * NOTICE: Location should be on the classpath as Hibernate uses
     * #resourceAsStream style lookup for its configuration file. That
     * is place the config file in a Java package - the default location
     * is the default Java package.<br><br>
     * Examples: <br>
     * <code>CONFIG_FILE_LOCATION = "/hibernate.conf.xml". 
     * CONFIG_FILE_LOCATION = "/com/foo/bar/myhiberstuff.conf.xml".</code> 
     */
    private static String CONFIG_FILE_LOCATION = "/hibernate.cfg.xml";//ServerInfo.getWebLogicConfigPath()+"database"+System.getProperty("file.separator")+"hibernate.cfg.xml";

    /** Holds a single instance of Session */
	private static final ThreadLocal threadLocal = new ThreadLocal();

    /** The single instance of hibernate configuration */
    private static final Configuration cfg = new Configuration();

    /** The single instance of hibernate SessionFactory */
    private static SessionFactory sessionFactory;

    /**
     * Returns the ThreadLocal Session instance.  Lazy initialize
     * the <code>SessionFactory</code> if needed.
     *
     *  @return Session
     *  @throws HibernateException
     */
    public static Session currentSession() throws HibernateException {
        Session session = (Session) threadLocal.get();

		if (session == null || !session.isOpen()) {
			if (sessionFactory == null) {
				try {
					//cfg.configure(CONFIG_FILE_LOCATION);
					//sessionFactory = cfg.buildSessionFactory();
					Context ctx = new InitialContext();
		             sessionFactory = (SessionFactory) ctx.lookup("hibernate/session_factory");
		              
				} catch (Exception e) {
					System.err
							.println("%%%% Error Creating SessionFactory %%%%");
					e.printStackTrace();
				}
			}
			session = (sessionFactory != null) ? sessionFactory.openSession()
					: null;
			threadLocal.set(session);
		}

        return session;
    }

    /**
     *  Close the single hibernate session instance.
     *
     *  @throws HibernateException
     */
    public static void closeSession() throws HibernateException {
        Session session = (Session) threadLocal.get();
        threadLocal.set(null);

        if (session != null) {
            session.close();
        }
    }

    /**
     * Default constructor.
     */
    private HibernateSessionFactory() {
    }

}

[billwindows]

使用 ： 网上有很多关于 HibernateSession 工厂的使用，自己在好好查查看吧

public class ReportDaoImpl implements ReportDao {
    /**
     * queryTable
     *
     * @param sql QueryPageVO
     * @param isPage boolean 是否用分页
     * @return List
     * @todo Implement this com.aspire.omr.dao.ReportDao method
     * @throws OmrException
     */
    public List queryTable(String sql, boolean isPage) throws OmrException {
        OmrLog.sql(sql);
        long startTime = System.currentTimeMillis();
        if (sql == null)
            return new ArrayList();
        List list = new ArrayList();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Session session = null;
        try {
            session = HibernateSessionFactory.currentSession();
            conn = session.connection();
        } catch (OmrException oe) {
            throw oe;
        }
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            int size = rs.getMetaData().getColumnCount();
            if (isPage)
                --size;
            String[] col = null;
            while (rs.next()) {
                col = new String[size];
                for (int i = 0; i < size; ++i) {
                    col[i] = rs.getString(i + 1);
                }
                list.add(col);
            }
        } catch (SQLException ex) {
            OmrLog.error("查询数据库失败,SQLExcetion!", ex);
            throw new OmrException(ExceptionConstant.OMR_QUERY);
        } finally {
            DBConnection.close(conn, stmt, rs);
            HibernateSessionFactory.closeSession();
        }
        OmrLog.sql("time:" + (System.currentTimeMillis() - startTime));
        return list;
    }
}

[笨沙发]

[Quote=引用 3 楼 deeplyloving 的回复:]
lz相当的牛..两千个参数那是啥业务..

public List getListByCriteria(final DetachedCriteria detachedCriteria) {
return (List) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException {
Criteria criteria = detachedCriteria
.getExecutableCriteria(session);
return criteria.list();
}
}, true);
}

创建 Detache…
[/Quote]en

[zgxzowen]

这个是in参数不能有那么长，数据库不支持。跟hibernate没关系

[ddpie]

4楼的方法很变态，但很实用，谢谢啦^o^
我再考虑是不是分多次查询。

[zgxzowen]

你这样写超级慢，性能 不好，为什么不考虑换种实现方式呢 ？

[Landor2004]

[Quote=引用 10 楼 ddpie 的回复:]
4楼的方法很变态，但很实用，谢谢啦^o^
我再考虑是不是分多次查询。
[/Quote]
晕，竟然有变态来形容，呵呵！

[ddpie]

to 9楼：我试过了，在SQL Server 2000里面使用有4832个参数的in语句查询不会出错，只是我的SQL语句超长了，所以我只能写到4832个了。
我觉得正解还是分多次查询，因为sql语句还是有长度限制的。

[ddpie]

to 11楼，维护已有代码，没办法。。。

[wangmlabc]

使用原生的sql不久Ok了