I/O Error when using Hibernate Criteria on big database

Hibernate ORM
1

When using Hibernate criteria in a function on our production database (45.000 entrys). The query fails with an I/O error and the connection to the postgresql server is reset.

Using this https://github.com/schuergi/semtix software that I forked for another organization.

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

It fails at List<Antrag> antraege = crit.list();with the following error:

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

And this is the log from the server:

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

It works perfectly fine with a small develpment test dataset however.

2

The reason for the error is because the SQL that gets executed has a predicate defined as:

this_.personID in ()

This translates to an in-clause in SQL and naturally when you define an in-clause with no values the database engine will throw an exception as it isn’t valid syntax.

The question is then why is the collection, idnachnamemap empty. My guess is whatever query the method is generating in queryString is likely returning an empty result-set and the code isn’t written to handle this use case.

What is in queryString?

3

Thank you very much for your response!
queryString creates a query that selects * from every person in the database (45.000 persons) and then just adds something wheter to include archived persons as well and the other if clause adds an and if you selected in the GUI that you only want to see persons whose surname starts with a specific letter.
(Because the workers in the institution each get assigned letters to divide the applicants in the database)
It should never be empty though.

4

I gathered that much from the code; however I meant actually verifying that the input provided by the user and therefore the generated query in queryString, does it actually return a non-empty result-set?

That is not a reason to avoid guarding against such a use case and guarantee fault tolerance. The first query has a chance to return an empty result-set, under that case you should not attempt to run the second query at all regardless.

5

In my test case queryString is SQLQueryImpl(SELECT * FROM person where uni=0)
and returns all the names in the database.

Of course you’re right that you should always check wheter the result is empty but thats not what I wanted to say.
Im sorry I dont know hibernate so well.
What else can I do now?

6

Can you check two things for me:

  1. Can you check if the map is empty or not when this error happens?
  2. If the map is not empty, can you check how many values are in the keySet?

As I suggested earlier, I believe the error you run into is because the map is empty, the in-clause with no bind parameters is very suspicious. Generally speaking, Hibernate will attempt to bind all values in that clause unless specific databases have an upper-limit on the number of bind values in such a clause like SQL Server, and then the bind parameters will be capped at that limit. But since the SQL does not show signs of any bound values, I’m inclined to think its all due to the empty key-set.

7

Its not empty.

System.out.println(idnachnamemap.size());prints 46249

8

I might then suggest you write the first query as a DetachedCriteria and then use it as a subquery for the second query. This should ultimately be a bit more efficient and avoids the bind parameter problem. The rendered SQL would effectively look something like:

SELECT <fields> 
  FROM <table>
 WHERE id IN (SELECT p.id FROM Person p WHERE ...)
   AND ...
9

I transformed the first query to a detachted criteria. How exactly would I now use it in the second query?
This is my Code so far.

public List<AntragIndex> getAntragIndexListe(Filter filter) {

		String buchstaben = filter.getBuchstaben();

		List<AntragIndex> indexListe = new ArrayList<>();
		
		//test new code from here:
		
		Session session = HibernateUtil.getSessionFactory().openSession();
		
		DetachedCriteria critP = DetachedCriteria.forClass(Person.class);
		int aktuelleUni = UniConf.aktuelleUni.getID() - 1;
		
		critP.add(Restrictions.eq("uni", aktuelleUni));
		if (!filter.getArchiviert().equals(Status.EGAL)) {
			critP.add(Restrictions.eq("archiviert", filter.getArchiviert().equals(Status.JA)));
		}
		
		if (!buchstaben.equals("alle")) {
			critP.add(Restrictions.ilike("nachname", Character.toString(buchstaben.charAt(0)), MatchMode.START));
			for (int i =1; i < buchstaben.length(); i++) {
				critP.add(Restrictions.disjunction());
				critP.add(Restrictions.ilike("nachname", Character.toString(buchstaben.charAt(i)), MatchMode.START));
			}
		}
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();
10

Something like:

DetachedCriteria detachedCriteria = ...
Criteria criteria = ...

criteria.add(Subqueries.propertyIn("personID", detachedCriteria));
// add other restrictions

List results = criteria.list();

I do want to stress that Hibernate Criteria is deprecated and will be removed in a future release. It’s likely best if you can to begin thinking about transforming these Hibernate Criteria-based queries to their JPA equivalents to make upgrading to newer Hibernate builds less cumbersome.

11

This gives me a NullPointerException.
Code: (I left the old query for testing because I need the HashMap later)

public List<AntragIndex> getAntragIndexListe(Filter filter) {

		String buchstaben = filter.getBuchstaben();

		List<AntragIndex> indexListe = new ArrayList<>();
		
		//test new code from here:
		
		Session session = HibernateUtil.getSessionFactory().openSession();
		
		DetachedCriteria critP = DetachedCriteria.forClass(Person.class);
		int aktuelleUni = UniConf.aktuelleUni.getID() - 1;
		
		critP.add(Restrictions.eq("uni", aktuelleUni));
		if (!filter.getArchiviert().equals(Status.EGAL)) {
			critP.add(Restrictions.eq("archiviert", filter.getArchiviert().equals(Status.JA)));
		}
		
		if (!buchstaben.equals("alle")) {
			critP.add(Restrictions.ilike("nachname", Character.toString(buchstaben.charAt(0)), MatchMode.START));
			for (int i =1; i < buchstaben.length(); i++) {
				critP.add(Restrictions.disjunction());
				critP.add(Restrictions.ilike("nachname", Character.toString(buchstaben.charAt(i)), MatchMode.START));
			}
		}
		
		
		

		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()));
		crit.add(Subqueries.propertyIn("personID", critP));
		
		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();

Exception:

Hibernate: SELECT * FROM person where uni=0
Exception in thread "AWT-EventQueue-0" java.lang.NullPointerException
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getProjectedTypes(CriteriaQueryTranslator.java:399)
	at org.hibernate.criterion.SubqueryExpression.createAndSetInnerQuery(SubqueryExpression.java:152)
	at org.hibernate.criterion.SubqueryExpression.toSqlString(SubqueryExpression.java:78)
	at org.hibernate.loader.criteria.CriteriaQueryTranslator.getWhereCondition(CriteriaQueryTranslator.java:417)
	at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:123)
	at org.hibernate.loader.criteria.CriteriaJoinWalker.<init>(CriteriaJoinWalker.java:92)
	at org.hibernate.loader.criteria.CriteriaLoader.<init>(CriteriaLoader.java:97)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1663)
	at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380)
	at org.semtix.db.DBHandlerAntrag.getAntragIndexListe(DBHandlerAntrag.java:256)
	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)
12

Might I suggest you check out Detached Criteria documentation. Essentially you need to specify what you want to select in the sub-query. If you look in the section I’ve linked, there are a few examples of how you specify a projection on a property.

13

hello,
Thankyou for this code. But I think this isnt working.