postgresql « Query « JPA Q&A

1. Hibernate 3: unable to query PostgreSQL database

I am setting up a project using Hibernate 3.3.1 GA and PostgreSQL 8.3. I've just created a database, the first table, added one row there and now configuring Hibernate. However, even the ...

2. Hibernate is calling public methods on the entities after a query, why?

I'm using hibernate 3.0 (with posgre 8.3), Java 1.6 and Netbeans 6.5. I've created one native query to return all the unique most recent entries like this:

String query = "SELECT DISTINCT ...

3. HQL: group by month

I'm trying to group by month some rows using HQL, but I'm kind of new to that API and can't seem to get it to work. Here is my code:


4. Hibernate Native Query problem with named parameters

I have a problem with Hibernate Native Query. I have one SELECT that selects array slice (PostgreSQL database). The problem is that hibernate recognizes the following part: ":300" from "SELECT my_array[1:300]..." as ...

5. Query problems with Hibernate (JPA) and PostgreSQL

I'm trying to use PostgreSQL as the database for Hibernate/JPA. However, I get an error when executing a simple query. My query is as follows:


6. Hibernate postgresql comparing date

I'm using Hibernate and PostgreSQL 8.4 database in a Java application. I have the following query:

Query q = session.createQuery("from User where validStartDate < "  + getDate() +" and validEndDate >" ...

7. Hibernate Insert Query in Java

Can anyone tell me how to perform an insert query with Hibernate and back end postgresql I am using the following implementation but not working

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

User A= new User();

8. Error: column "this_.phitorsionangle" must appear in the GROUP BY clause or be used in an aggregate function

I'm having some trouble with an sql query. I'm using Hibernate Criteria to build the query. I create some bins from a database by rounding the values with certain intervals (the ...

9. Escaping colons in hibernate createSQLQuery

I am confused on how I can create an SQL statement containing colons. I am trying to create a view and I am using (notice the double colons):

create view MyView ...

10. Using Postgres aggregate functions with NHibernate

I have the following query:

SELECT title_id, title, array_agg(
FROM title t
INNER JOIN title_genre tg USING(title_id)
INNER JOIN genre g USING (genre_id)
GROUP BY title_id, title
ORDER BY title_id
Sample output from this query:
5527;"The Burbs";"{Suspense,"Dark Humor ...

11. Select next position

Is it possible to express this query (from Postgres) as in HQL or as a hibernate criteria query?

SELECT id, least(abs(gene_start - ?) , abs(gene_end - ?)) as div_pos
FROM arraydata.gene WHERE chromosomeref ...

12. Selecting rows with the highest date

I have some values which are repeating in my table, I want to select only those with the latest/highest date i.e :

ID   Type     Name  ...

13. Control order of updates in hibernate

I have a table with a unique column, "token", enforced by a unique constraint in the database. In a certain case, I need to change an existing row to have the ...

14. Query is not working in Postgres

Hi I want to construct a query My table structure is

Nodeid         Nodename   Nodetype Parentid   nodedes
1     ...

15. DataException could not execute query

Hi I'm getting DataException while executing the following query :

 GET_USERLIST_BY_GROUP1 =   "   SELECT usr.user_id,
FROM user_info usr
WHERE usr.user_id IN

16. Unexpected row count when using PostgreSQL trigger and hibernate

I have an application which uses Hibernate and I have to include a trigger to copy to a history table all rows modified or deleted in a table. After including the ...

17. PostgreSQL query function

18. mysql vs. postgresql "group by"

Hi, I just ran into an exception while migrating an application from Mysql to Postgresql. It happens in the following query: final String QUERY = "select o from " + Order.class.getName() + " as o, " + PrintJob.class.getName() + " as p" + " where = ?" + " and p.status != ?" + " group by o.objId" + " ...

19. [HQL/PostGreSql] Aggregation GROUP BY or DISTINCT

Postgres is very good at following SQL standards, and the use of GROUP BY is illegal because there are no aggregation function was specified in the query (like count, sum, etc. ). select distinct ( table.address, from table1 table WHERE table.IdInstitution=2 should do that you need. Note 1: you will get back list of arrays. Note 2: that select distinct ...

20. Hibernate and Postgresql TSearch2 Query

Hello, I am having a little problem. I want to get a Pojo returned from hibernate through the Query when doing a postgresql fulltext search with TSearch2. The SQL search query looks like this: "select * from image as i WHERE (i.idxfti @@ 'car'::tsquery)" but Hibernate chokes on the Syntax with the @@. Is there any way to grant this kind ...

21. PostgreSQL: INSERT with RETURNING clause

22. PostgreSQL ilike in where clause