java - 在大数据库上使用 Hibernate Criteria 时出现 I/O 错误

Question

在我们的生产数据库(45.000 个条目)的函数中使用 Hibernate 标准时。查询因 I/O 错误而失败，并且与 postgresql 服务器的连接被重置。

使用这个https://github.com/schuergi/semtix我为另一个组织 fork 的软件。

public List<AntragIndex> getAntragIndexListe(Filter filter) {

        String buchstaben = filter.getBuchstaben();

        List<AntragIndex> indexListe = new ArrayList<>();

        Session session = HibernateUtil.getSessionFactory().openSession();

        StringBuilder queryString = new StringBuilder("SELECT * FROM person where uni=" + (UniConf.aktuelleUni.getID() - 1));

        if (!filter.getArchiviert().equals(Status.EGAL)) {
            queryString.append(" and archiviert=" + filter.getArchiviert().equals(Status.JA));
        }

        if (!buchstaben.equals("alle")) {
            queryString.append(" and (upper(unaccent(nachname)) like '" + buchstaben.charAt(0) + "%'");
            for (int i = 1; i < buchstaben.length(); i++) {
                queryString.append(" or upper(unaccent(nachname)) like '" + buchstaben.charAt(i) + "%'");
            }
            queryString.append(")");
        }

        SQLQuery query = session.createSQLQuery(queryString.toString()).addEntity(Person.class);
        List<Object> persons = query.list();
        HashMap<Integer, String> idnachnamemap = new HashMap<>();
        for (Object o : persons) {
            Person p = (Person) o;
            idnachnamemap.put(p.getPersonID(), p.getNachname());
        }


        Criteria crit = session.createCriteria(Antrag.class);

        crit.add(Restrictions.in("personID", idnachnamemap.keySet()));

        if (filter.getSemesterID() > 0) {
            crit.add(Restrictions.eq("semesterID", filter.getSemesterID()));
        }

        if (!filter.getErstsemester().equals(Status.EGAL)) {
            crit.add(Restrictions.eq("erstsemester", filter.getErstsemester().equals(Status.JA)));
        }

        if (!filter.getKulanz().equals(Status.EGAL)) {
            crit.add(Restrictions.eq("kulanz", filter.getKulanz().equals(Status.JA)));
        }

        if (!filter.getNothilfe().equals(Status.EGAL)) {
            crit.add(Restrictions.eq("nothilfe", filter.getNothilfe().equals(Status.JA)));
        }

        if (!filter.getRatenzahlung().equals(Status.EGAL)) {
            crit.add(Restrictions.eq("raten", filter.getRatenzahlung().equals(Status.JA)));
        }

        if (!filter.getBarauszahler().equals(Status.EGAL)) {
            crit.add(Restrictions.eq("manAuszahlen", filter.getBarauszahler().equals(Status.JA)));
        }

        if (!filter.getTeilzuschuss().equals(Status.EGAL)) {
            crit.add(Restrictions.eq("teilzuschuss", filter.getTeilzuschuss().equals(Status.JA)));
        }

        FilterArt filterArt = filter.getFilterArt();
        if (null != filterArt) {
            switch (filterArt) {
                case ABGELEHNT:
                    crit.add(Restrictions.eq("antragStatus", AntragStatus.ABGELEHNT));
                    break;
                case ANGENOMMEN:
                    crit.add(Restrictions.eq("antragStatus", AntragStatus.GENEHMIGT));
                    break;
                case UNENTSCHIEDEN:
                    crit.add(Restrictions.eq("antragStatus", AntragStatus.NICHTENTSCHIEDEN));
                    break;
                default:
                    //beides True
                    break;
            }
        }

        crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
        List<Antrag> antraege = crit.list();


        session.close();

失败于 List<Antrag> antraege = crit.list();出现以下错误:

Hibernate: SELECT * FROM person where uni=0
Hibernate: select this_.antragID as antragID1_1_1_, this_.antragStatus as antragSt2_1_1_, this_.anzahlMonate as anzahlMo3_1_1_, this_.auszahlung as auszahlu4_1_1_, this_.begruendung as begruend5_1_1_, this_.charite as charite6_1_1_, this_.datumAngelegt as datumAng7_1_1_, this_.datumGeaendert as datumGea8_1_1_, this_.entwertungsDatum as entwertu9_1_1_, this_.erstattung as erstatt10_1_1_, this_.erstsemester as erstsem11_1_1_, this_.gedruckt as gedruck12_1_1_, this_.gesendet as gesende13_1_1_, this_.isBef as isBef14_1_1_, this_.kulanz as kulanz15_1_1_, this_.manAuszahlen as manAusz16_1_1_, this_.nothilfe as nothilf17_1_1_, this_.personID as personI18_1_1_, this_.punkteEinkommen as punkteE19_1_1_, this_.punkteHaerte as punkteH20_1_1_, this_.raten as raten21_1_1_, this_.semesterID as semeste22_1_1_, this_.teilzuschuss as teilzus23_1_1_, this_.userAngelegt as userAng24_1_1_, this_.userGeaendert as userGea25_1_1_, haertelist2_.antragID as antragID7_1_3_, haertelist2_.antragHaerteID as antragHa1_2_3_, haertelist2_.antragHaerteID as antragHa1_2_0_, haertelist2_.abgelehnt as abgelehn2_2_0_, haertelist2_.ablehnungsID as ablehnun3_2_0_, haertelist2_.anerkannt as anerkann4_2_0_, haertelist2_.anerkanntHigh as anerkann5_2_0_, haertelist2_.angegeben as angegebe6_2_0_, haertelist2_.antragID as antragID7_2_0_, haertelist2_.customName as customNa8_2_0_, haertelist2_.customPoints as customPo9_2_0_, haertelist2_.customText as customT10_2_0_, haertelist2_.haertegrund as haerteg11_2_0_ from Antrag this_ left outer join AntragHaerte haertelist2_ on this_.antragID=haertelist2_.antragID where this_.personID in () and this_.semesterID=? order by haertelist2_.antragHaerteID asc
2019-08-26 12:10:25,745 [AWT-EventQueue-0] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper  - SQL Error: 0, SQLState: 42601
2019-08-26 12:10:25,745 [AWT-EventQueue-0] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper  - ERROR: syntax error at or near ")"
  Position: 1603
Exception in thread "AWT-EventQueue-0" org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2066)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1863)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
    at org.hibernate.loader.Loader.doQuery(Loader.java:910)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
    at org.hibernate.loader.Loader.doList(Loader.java:2554)
    at org.hibernate.loader.Loader.doList(Loader.java:2540)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
    at org.hibernate.loader.Loader.list(Loader.java:2365)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1682)
    at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380)
    at org.semtix.db.DBHandlerAntrag.getAntragIndexListe(DBHandlerAntrag.java:228)
    at org.semtix.gui.tabs.ActionNewTab.actionPerformed(ActionNewTab.java:57)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2348)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:289)
    at java.awt.Component.processMouseEvent(Component.java:6539)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3324)
    at java.awt.Component.processEvent(Component.java:6304)
    at java.awt.Container.processEvent(Container.java:2239)
    at java.awt.Component.dispatchEventImpl(Component.java:4889)
    at java.awt.Container.dispatchEventImpl(Container.java:2297)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4904)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4535)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4476)
    at java.awt.Container.dispatchEventImpl(Container.java:2283)
    at java.awt.Window.dispatchEventImpl(Window.java:2746)
    at java.awt.Component.dispatchEvent(Component.java:4711)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:760)
    at java.awt.EventQueue.access$500(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:709)
    at java.awt.EventQueue$3.run(EventQueue.java:703)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:84)
    at java.awt.EventQueue$4.run(EventQueue.java:733)
    at java.awt.EventQueue$4.run(EventQueue.java:731)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:74)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:730)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:205)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
  Position: 1603
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:118)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:116)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
    ... 51 more

这是来自服务器的日志:

db_1  | 2019-08-26 10:11:42.470 UTC [276] ERROR:  syntax error at or near ")" at character 1603
db_1  | 2019-08-26 10:11:42.470 UTC [276] STATEMENT:  select this_.antragID as antragID1_1_1_, this_.antragStatus as antragSt2_1_1_, this_.anzahlMonate as anzahlMo3_1_1_, this_.auszahlung as auszahlu4_1_1_, this_.begruendung as begruend5_1_1_, this_.charite as charite6_1_1_, this_.datumAngelegt as datumAng7_1_1_, this_.datumGeaendert as datumGea8_1_1_, this_.entwertungsDatum as entwertu9_1_1_, this_.erstattung as erstatt10_1_1_, this_.erstsemester as erstsem11_1_1_, this_.gedruckt as gedruck12_1_1_, this_.gesendet as gesende13_1_1_, this_.isBef as isBef14_1_1_, this_.kulanz as kulanz15_1_1_, this_.manAuszahlen as manAusz16_1_1_, this_.nothilfe as nothilf17_1_1_, this_.personID as personI18_1_1_, this_.punkteEinkommen as punkteE19_1_1_, this_.punkteHaerte as punkteH20_1_1_, this_.raten as raten21_1_1_, this_.semesterID as semeste22_1_1_, this_.teilzuschuss as teilzus23_1_1_, this_.userAngelegt as userAng24_1_1_, this_.userGeaendert as userGea25_1_1_, haertelist2_.antragID as antragID7_1_3_, haertelist2_.antragHaerteID as antragHa1_2_3_, haertelist2_.antragHaerteID as antragHa1_2_0_, haertelist2_.abgelehnt as abgelehn2_2_0_, haertelist2_.ablehnungsID as ablehnun3_2_0_, haertelist2_.anerkannt as anerkann4_2_0_, haertelist2_.anerkanntHigh as anerkann5_2_0_, haertelist2_.angegeben as angegebe6_2_0_, haertelist2_.antragID as antragID7_2_0_, haertelist2_.customName as customNa8_2_0_, haertelist2_.customPoints as customPo9_2_0_, haertelist2_.customText as customT10_2_0_, haertelist2_.haertegrund as haerteg11_2_0_ from Antrag this_ left outer join AntragHaerte haertelist2_ on this_.antragID=haertelist2_.antragID where this_.personID in () and this_.semesterID=$1 order by haertelist2_.antragHaerteID asc
db_1  | 2019-08-26 10:11:42.471 UTC [276] ERROR:  current transaction is aborted, commands ignored until end of transaction block
db_1  | 2019-08-26 10:11:42.471 UTC [276] STATEMENT:  SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME,  CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema'  WHEN true THEN CASE  WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TABLE'   WHEN 'v' THEN 'SYSTEM VIEW'   WHEN 'i' THEN 'SYSTEM INDEX'   ELSE NULL   END  WHEN n.nspname = 'pg_toast' THEN CASE c.relkind   WHEN 'r' THEN 'SYSTEM TOAST TABLE'   WHEN 'i' THEN 'SYSTEM TOAST INDEX'   ELSE NULL   END  ELSE CASE c.relkind   WHEN 'r' THEN 'TEMPORARY TABLE'   WHEN 'p' THEN 'TEMPORARY TABLE'   WHEN 'i' THEN 'TEMPORARY INDEX'   WHEN 'S' THEN 'TEMPORARY SEQUENCE'   WHEN 'v' THEN 'TEMPORARY VIEW'   ELSE NULL   END  END  WHEN false THEN CASE c.relkind  WHEN 'r' THEN 'TABLE'  WHEN 'p' THEN 'TABLE'  WHEN 'i' THEN 'INDEX'  WHEN 'S' THEN 'SEQUENCE'  WHEN 'v' THEN 'VIEW'  WHEN 'c' THEN 'TYPE'  WHEN 'f' THEN 'FOREIGN TABLE'  WHEN 'm' THEN 'MATERIALIZED VIEW'  ELSE NULL  END  ELSE NULL  END  AS TABLE_TYPE, d.description AS REMARKS  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c  LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0)  LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog')  WHERE c.relnamespace = n.oid  AND c.relname LIKE 'PROBABLYNOT' AND (false  OR ( c.relkind IN ('r','p') AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) )  ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME 

然而，它在小型开发测试数据集上运行得非常好。

Answer 1

Postgres 错误日志告诉您出了什么问题:字符 1603 处的“)”处或附近有语法错误。这就是这个 SQL:where this_.personID in ()。

在您的 Java 代码中，您有

crit.add(Restrictions.in("personID", idnachnamemap.keySet()));

看起来您的集合是空的，因此该限制无法转换为有效的 SQL。