string « Query « JPA Q&A

1. Unicode String in Hibernate Queries

In SQL one can write a query that searches for a name of a person like this:

SELECT * FROM Person P WHERE P.Name LIKE N'%ike%'
This query would run with unicode characters ...

2. SQL string from an HQL query?

HQL queries are converted to SQL. I would like my program to have the complete SQL string before it is sent to my database. Can I do this?

Notes: I can see the SQL ...

3. Querying on collection of strings with NHibernate

I have an "Item" class, and this class has a collection "Tags".

    IList<string> Tags;
my DB looks like this:

I am ...

4. NHibernate How do I query against an IList property?

I am trying to query against an IList<string> property on one of my domain classes using NHibernate. Here is a simple example to demonstrate:

public class Demo
    public Demo()

5. On String creation for queries in Hibernate and whether those queries would reduce object creation if they were final static

Given this Hibernate snippet

        query = "select from Cont ...

6. Print query string in hibernate with parameter values

Is it possible in hibernate to print generated sql queries with real values instead of question marks? How would you suggest to print queries with real values if its not possible with ...

7. Hibernate: Query without SQL-strings?

Is it possible (and if yes, is it advisable) to do queries of Hibernate-mapped data without using SQL-strings, but some OOP way instead?

8. sql order by numeric string

I am using oracle 10. I need to sort my result set according to two numeric string fields. one sort criterion field holds data like this: FIELD1:

the other :
I need to ...

9. How do I query for objects with a value in a String collection using Hibernate Criteria?

Let's say Student is the parent class in an inheritance hierarchy that includes ComputerScienceStudent and ITStudent. Student defines a field called favoriteColors, which is a value-typed collection of Strings. ...

10. How to NOT select empty string

We have the following JPQL:

Select distinct sys.ipAddress from SystemLog sys where sys.ipAddress is not null and sys.ipAddress is not empty
And this generates the following mysql statement.

11. Sophisticated JPQL String Query

I am trying to execute a pretty-sophisticated query on a string field in the database. I am not very experienced at JPQL, so I thought I would try to get some ...

12. JPQL: How to include Forward Slash in Query String?

How do you properly escape a '/' forward slash in a JPQL query string? If I do this:

I get:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
However, if I do this:

13. Using the JPA2 Criteria API to query for a string subset

I'm upgrading from the Hibernate Criteria API to the JPA2 Criteria API, and am running into the following problem, which I can't seem to resolve. In the Hibernate Criteria I was using ...

14. Get the named query string within JPA

I am trying to externalize all named queries for JPA in an orm.xml file. I would like to get the named query string in my Java program for some manipulation purposes ...

15. Can I get the SQL string from a JPA query object?

May I know how can I get the sql from a JPA query? or let's say, convert the JPA query to a SQL string? Thank you very much!

16. HQL Querying, finding by string idea

Two questions.

  1. Is there a way for us to query by substring/contains method or like a search style query or even regular expression matching? Without making it case sensitive?!!! Example.
Query for: Good Food: Table contains
  1. I ...

17. Nhibernate HQL query compare guid with string

I am using nhibernate to query some data. I have the following HQL query SELECT x FROM mytable AS x ,PageSetting AS ps , Page AS p WHERE p.Id = :pageId AND ps.Value ...

18. JPQL¬†distinct with string function

I have a JPQL like this one:

select distinct d 
from Department d
left join fetch d.employees
When I want to fetch one of the lazy property of my Department entity, the distinct is ...

19. Custom order by string parameter in HQL

I got HQL query like this:

Query query = session.createQuery("from User as user where user.joined!=null order by user.joined desc");
How to set User property as parameter to sort on every properties? My code ...

20. JPA: how do you get/print the JPQL query string behind a (typed) query after parameters have been set?

How do you get/print the JPQL query string behind a (typed) query, that is after parameters have been set? (e.g. for debugging purposes) A simple toString() doesn't seem to do the trick... Thanks ...

21. Using HibernateTemplate.find(...) with a String[]

I'm having troubles with HQL again :( My desired SQL is this:

select employee.idemployee as id, employee.age as age,     
employee.birthday as birthday, employee.firstName as firstName, ...

23. Native Query with long Query String

25. Query for a String that contains a String

I am creating a java program that plays music, it will have a database that contains the meta-data for the tracks. I have tried DB4O but i cant get it to query for all objects that has a string property that contains my search string. Im thinking that hibernate might be a better option. Can i query for: Object.String contains Substring? ...

26. [Newbie] How to query on a collection of Strings?

Hi folks, I have a class that looks like this: @Entity @Inheritance(strategy=InheritanceType.SINGLE_TABLE) abstract class Person { @Id private String id; @CollectionOfElements(fetch=FetchType.EAGER) private Set aliases = new TreeSet(); } Now, when Hibernate pushes that out to the DB, I end up with a table "PERSON" and the 'child table' (containing the aliases for each PERSON) of PERSON_ALIASES. The PERSON_ALIASES table contains the ...

27. limited string length in createSQLQuery

Does anybody knows if there is a limit to the string length that can be passed as argument to createSQLQuery if I print the string before calling query.list(); I get a 1320 long string and the error: org.hibernate.MappingException: No Dialect mapping for JDBC type: -1 at org.hibernate.dialect.TypeNames.get( at org.hibernate.dialect.TypeNames.get( at org.hibernate.dialect.Dialect.getHibernateTypeName( at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType( at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery( at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes( at org.hibernate.loader.Loader.getResultSet( at org.hibernate.loader.Loader.doQuery( ...

28. Query strings kept in threadlocal are eating heapspace

Hello all, I'm getting out-of-heap space errors. After examining the heap in visual-vm I found that a lot of (>500.000 after running a couple of days) String objects containing query strings are kept in threadLocals. Why? I don't use any threadlocals in my application. Setup: - mysql - tomcat - jdbc - dbcp - hibernate - ehcache - one static entityManagerFactor ...

29. Ordering by value(s) of collection Set

Hello, I have a query which I am trying to sort alphabetically by the String values in a (mapped) collection which is a member of a mapped persistent class. The collection is loaded using a named SQL-query with load-collection set, and the collection is defined as a set in the hibernate mapping definition. The class property is defined as Set. Now ...

30. Is it possible to use string functions like LEFT(name,1)

Starting from 2.1 beta 1 I added dialect functions where functions similar to this can be used by specific dialects. At this point the only dialect I have enhanced is the Oracle dialect. I was intending to do HSQL as well (when I get some time) it is easy to add the fucntions to the dialect. It allows: o Database specific ...

31. Query string mangling

Hi, I am running the quick start example. But a exception with description "column not found: x0_0_ ". I check the query string is mangled to "select cat0_.CAT_ID as x0_0_ from CAT cat0_" from "select cat from Cat as cat". What setting I make is wrong? The following is part of the exception: Hibernate: insert into CAT (NAME, sex, weight, CAT_ID) ...

32. HQL question - String literal in select

Hi, I am wondering if I can achieve this query using the HQL: select 'columnName1', sum(myTable.column_name) from table as myTable where ... I need this kind of query so that I can populate the value of a bean's property using things like apache beanutils. columnName1 is a String literal return in the resultset. It is also the property name in the ...

33. String concatenation in select clause

This is an interesting question as I've come across something similar. Instead of concatenation, I was trying to perform some mathematical functions on the properties from a queried Object: Code: select obj.a, obj.b, (obj.a + obj.b) from Object obj This fails from Hibernate, so what I've done is include the calculation in my DAO layer. So my Business Layer will see ...

34. Using createSQLQuery to return strings

I know. What I mean to do is to create a fake mapped class with a String attribute to contain the value, so I can use the database independent features of Hibernte for paging: query.setMaxResults(pageSize); query.setFirstResult(pageSize*pageNumber); That way I wouldn't be hardcoding the "LIMIT a,b" form MySQL. I know createSQLQuery wasn't created for doing this, but maybe there is a workaround. ...

35. Query over String arrays owned by an object

36. query strings in output console

how can I stop hibernate to write out the generated query strings while operating? I set the Appender File Logger to write to a text file and log4j.logger property to "DEBUG" to write the all output that hibernate generates into a text file. But I couldnt stop hibernate to write out queries on the output console.

37. Skip empty string properties for example queries

38. Update as query string

39. How to use HQL to query String type from db?

40. String concatenation in select redux

41. Second level cache and session.find(String hql)

Hi all. My question is kind of disturbing: I execute session.find() on a dinamic hql. Is it using the second level cache before hitting the DB? I am debugging my hibernate all day now, and maybe I missed it, but it doesn't seem like it is using the cache (not query cache). Can someone help me on this matter? [if you ...

42. delete(String query) vs. delete(Object object)

43. Escaping String in Session.createSQLquery

Hi, [skip that intro if you wish] I am currently using hibernate for the first time in a project, after seeing Gavin and Micheal speaking so dearly about it at Javapolis and JavaOne, I finally made the step, and I must say: GREAT! Thanks... Still, I hae to get back to good ol' SQL for one particular job that builds a ...

44. Help with LIKE string contained in sql-query eg '%strgin%'

Hibernate version:2.1.3 Currently have a sql-query using the LIKE keyword which appears to only search the beginning of the string as per 'string%' I have a sql-query but would like to search anywhere in the string. My current query is : ...

45. how to do a query on a collection of strings

You mean that your current mapping isn't working? Or that you want to change it so that criteria will do what you want them to do? germuska has confirmed that Restrictions.sqlRestriction will do what you want, but if you'd prefer to change your mapping so that plain vanilla queries/criteria work, then the solution is to create a new Keyword class, primary ...

46. add string in select

47. How do I select based on what a String starts with?

Hi I need to write an HQL query that selects an attribute based on the first letter of the name. For example, if I have an object w/ name and weight, I need to sum all the weights for all objects starting w/ A, then B, etc etc. What is the code for this in HQL? I'm pretty sure I ahve ...

48. String concatentation in query causes exception

I am trying to perform a query based on the concatenation of two string fields. The data I have at the time of the query is a single value which is the concatenation of these two fields. I have no way to know how to break up that value to perform the query of the two fields individually. I tried the ...

50. SQL query strings lead to *huge* footprint

Hi, I'm experimenting again with porting our ORM framework over to hibernate, but seem to have the luck finding the weaknesses of hibernate all the time... This time, as I've given up upon my "mixed mode mapping", I took one slightly larger project (400 classes) over to hibernate and ran into memory probs. First of all, switching off batching helped a ...

51. select this + ',' + csv + ',' + string from table

can you do this type of concatenation in a select statement using hql/sql? using session.createSQLQuery() for example? i can't figure out how to get a hibernate query to run and return a list of strings, which don't map to any tables/hbm/classes.... my example, the tables are all mapped, but the select statement is trying to create a new string which ...

53. Problem querying Set property

Author Message stevewickii Post subject: Problem querying Set property Posted: Tue Oct 23, 2007 4:01 pm Beginner Joined: Mon Apr 24, 2006 9:47 pm Posts: 33 Location: Kansas City, MO Hibernate version: 3.2.5ga Domain Objects: Code: package com.nkics.fmc.platform.domain; import java.util.Date; import java.util.HashSet; import java.util.Set; public class User implements { protected static final long serialVersionUID = ...

54. Criteria query on collection of Strings

I have a collection of strings defined as follows: List names and mapped as follows... sequence_id ... and I'm trying to figure out how I can use a Criteria query to return those objects which have a particular name (i.e ...

55. SQLGrammarException error when building the query string

The following SQL create this error "SQL Error: -201, SQLState: 42000" select as id0_0_, echomessag0_.msg as msg0_0_ from pfd.dba.echo_message echomessag0_ where This was a manual change that was run in the HQL editor and worked. the only change I made was replacing a "." with ":" in the string "pfd.dba.echo_message" to "pfd:dba.echo_message. below is the corrected string that worked ...

56. using strings in named query

I'd like to use some strings in a named query. [code] from Position where cusip= :cusip and dealernumber= :dealernumber [/code] This works. I'd like to add accountsource='DST' or accountsource='TDA' How can I do this with the named query? I tried this, but no good: [code] from Position where cusip= :cusip and dealernumber= :dealernumber accountsource='TDA' [/code] Even if I lose ...

57. DataException when querying with a string > than column s

Hibernate version: hibernate-core-3.3.1.GA hibernate-commons-annotations-3.1.0.GA hibernate-annotations-3.4.0.GA Mapping documents: None. Using annotations. Full stack trace of any exception that occurs: 2009-01-20 13:29:26,473 ERROR pool-1-thread-1 util.JDBCExceptionReporter DB2 SQL error: SQLCODE: -302, SQLSTATE: 22001, SQLERRMC: null 2009-01-20 13:29:26,473 ERROR pool-1-thread-1 occ.DemarcateTransaction An exception occurred. Rolling back database transaction. org.hibernate.exception.DataException: could not execute query at org.hibernate.exception.SQLStateConverter.convert( at org.hibernate.exception.JDBCExceptionHelper.convert( at org.hibernate.loader.Loader.doList( at org.hibernate.loader.Loader.listIgnoreQueryCache( at org.hibernate.loader.Loader.list( at org.hibernate.loader.criteria.CriteriaLoader.list( ...

58. Criteria query with collection of values (Set)

public class Member { private Long rowId; private Set gids = new HashSet(); public Long getRowId() { return rowId; } public void setRowId(Long rowId) { this.rowId = rowId; } public Set getGids() { return gids; } public void setGids(Set gids) { ...

59. How to pass String[] to FilterDef for IN clause?

60. Using named query parameter in string