mysql « Query « JPA Q&A

1. problem with select Double in Hibernate

I have problem when want read object with where (double variable) this is my Code : BranchBuilding Table:

@Table(name = "branchbuilding", uniqueConstraints={@UniqueConstraint(columnNames={"buildingname","branch_fk"})})//uniqueConstraints={@UniqueConstraint(columnNames={"username","buildingname"})}
public class BranchBuildingEntity implements Serializable {


2. select in HQL (hibernate)

I have a table in mysql which has just on record. when I execute following hql query in netbeans HQL I got the following answer which is correct: query:

from Customer as cust
CustomerID  ...

3. queries with hibernate

I have a database include eventType and event Tables


  - id
  - name


  - id 
  - name
  - location
  - eventType_id
  - ...

4. Hibernate disable Query Cache

Following problem: I create a Query to display all Entries of a MYSQL Table, if I edit a Field and execute the Query again I get the same (old) Result as in ...

5. How to query by page from to table by Hibernate?

I create 3 tables by Hibernate, how to return a List<task> when set task_status = ? and set create_user_id = ?, task_status in "dotask" table, create_user_id in task table. How to ...

6. large result set of mysql select query

What would be the easiest way to split an sql file of 80mb into multiple smaller sql files? thanks. dani.

7. Simple but Complicated HQL/SQL Query

I have two tables, in one to many relationship (Quizzes, Comments): a Quiz can have multiple Comments I need to display inside a page the last 5 comments for each quiz. Is it ...

8. How does this SQL query translate to JPQL?

I´m trying to get accustomed to JPQL, but i´m already stuck. How does this: SELECT * FROM Table_a INNER JOIN Table_b USING (Table_bID) Where Table_b.Column_a LIKE 'somestring' translate to JPQL ? I would ...

9. What to do if user closes page in the middle of a mysql long query?

What happens to the mysql query if the user just closes the page? Should I be doing any kind of checking for this? I'm using JPA 2.0(EclipseLink 2.0), JSF ...

10. Hibernate MySQL Query Error Unknown Column in Join On

I am getting a strange error with trying to use Hibernate with MySQL. I have the following named query:

SELECT SUM(s.plotline.value), s.character FROM Story as s WHERE s.chapter.chapterID = ?1 GROUP ...

11. Hibernate Issuing too many queries to MySQL

I am using hibernate 3.0 in spring with Mysql 5. I have configured JNDI datasource in JBOSS and using it in application context. My Problem is that Hibernate is issuing average ...

12. Does Hibernate HQL support aliasing subqueries?

I'm using Hibernate 3.2.6 and I'm attempting to make a query like so:

    (select min(date) as someAlias from B b where ...

13. Hibernate sending alien queries to database

I have a web application backed by hibernate. For last few days, I have started closely monitoring mysql and found that hibernate is sending unknown queries to the database which infact ...

14. Hibernate Native Query error

I am getting a strange error when trying to execute a native query in hibernate this is my query:

String qsf = "LEFT OUTER JOIN record_v2_record_audit rra ON r.record_id = rra.record_id "

15. Regex query in Hibernate

I want to achieve the effect of following query in Hibernate, but I'm unable to figure out a way to do this.

select e.TITLE from EVENTS e where e.TITLE REGEXP 'fri|pro';
Can someone ...

16. Problem with aliased table in a select with hibernate

I'm trying to get the daily max number of events per minute. We have the events logged in a MySQL table. The following select statement:

select MAX(counter), t1.mydate
from (select count(*) as counter, ...

17. SQL Query IN clause limit in varchar

When I run the following query in MySQL

select x.conList from user x where = '12345'
I do not see anything. It returns <LONG>. It has 2000+ chars like
When ...

18. how to structure a mysql table in order to stress the database as less as possible (with using hibernate)

Ive got one question about the structure of a database when using hibernate I have to save information (one number) for every day in the database. Very often I need to select the ...

19. Translate SQL query to JPQL

Following query:

SELECT kinder.kindID, kinder.kindName, kinder.kindVorname 
FROM kinder
    GROUP BY kindName HAVING count(kindID) > 1) dup ON kinder.kindName = dup.kindName
ORDER BY kindName
finds and displays ...

20. How do I query UUIDs stored as binary in a database (JPA/Hibernate/MySQL)

I have a Java/JPA/Hibernate/MySQL based app. I want to use UUIDs for object identity, however I want to ensure database performance does not suffer. I found this great blog posting

21. Executing multiple native queries in one go

I'm wondering if it is possible to execute several semicolon-separated SQL update queries with one SQLQuery#executeUpdate() call in Hibernate (3.2). I have string containing multiple updates like this:

String statements = "UPDATE Foo ...

22. Using JPA1.0: How to write queries

Good day, I have this weird problem: This following statement works Query q = em.createQuery("SELECT m from AccountClass as m"); whereas this following statement does not Query q = em.createQuery("SELECT m from AccountClass"); I'm trying to ...

23. Simple HQL/sql query..need help?

I have 2 tables(entity) a person entity and a vacation entity.One person can have many vacations. what I'm trying to do is the following query: Select all the persons period!, but for the ...

24. Using like query to get the desired output across two tables

Hi all i am trying to achieve below query to return result from two table based on the condition given.How to put correct query to achive the expected output?

SELECT * ...

25. A seemingly simple Hibernate query is taking 15-18 seconds

I have a seemingly innocent Hibernate query:

public List<ContentAccess> getByMemberId(
Integer memberId
) {
List<ContentAccess> contentAccess = 
return contentAccess;
contentAccess table has about 20 records and member table also has about 20 records but when ...

26. Hibernate failed to execute query afte 1 day

I am getting following error in my web application after one day. I have dont lot of search on it for couldn't fix this problem yet. Can anybody help me out?

Caused ...

27. How to write Nhibernate Query C# .net Mysql

I need some help to write some queries. For this sql query

(select * from NewsFeed where ID=10)
nHibernate query is
var set = sesssion.Query<NewsFeed>().Where(c => c.ID == 10).ToList();
These are queries I ...

28. hibernate mysql "Like"

hi all, I am having problems with issuing a HQL search query which has a "like" clause in it...the database that i am using is MySQL 5.0 for example my HQL is something like SELECT qr FROM AS qr JOIN fetch qr.quoteRequestStatus AS qrs JOIN fetch qr.usage AS u JOIN fetch qr.trackFolder AS tf JOIN fetch tf.track AS t JOIN ...

30. A problem with Hibernate and MySQL query.

Ok, I used a service class to insert value into the table... and at the same time tried to retrieve the last id with another class' method. here are the codes - public class AddEmployeeService { private Integer lastId; public Integer storeEmp(Employee e) { Session session = null; try { //creating configuration and loading the configuraton Configuration cfg = new Configuration().configure(); ...

31. MySql query redesign to JPA query

Hi, can any one please help me how to redesign this mysql query into jpql(jpa query). SELECT delivered.ls_fk_artikel, delivered.SUM_Amount_Delivered, sold.SUM_Amount_Sold, (coalesce(delivered.SUM_Amount_Delivered,0) - coalesce(sold.SUM_Amount_Sold,0)) AS Amount_Stock FROM ( SELECT Sum(ls_main.ls_menge) AS SUM_Amount_Delivered, ls_main.ls_fk_artikel, ls_main.ls_ean, ls_main.ls_artikelnr FROM ls_head Inner Join ls_main ON ls_head.ls_head_id = ls_main.fk_ls_head WHERE ls_head.fk_filialen = '2410' AND ls_head.storno IS NULL GROUP BY ls_main.ls_fk_artikel ) AS delivered left JOIN ( SELECT ...

32. Translating mySQL Query to HQL

33. Using Criteria.setMaxResult() with MySQL

I am trying to use criteria.setFirstResult(15); criteria.setMaxResults(50); with MySql DB. But it generate a wrong sql . It put 'top' keyword after "SELECT". Hibernate version I am using is hibernate-3.3.1.jar eg: select top 65 as id193_0_, this_.abbr as abbr193_0_, this_.nickname as nickname193_0_, as business193_0_, as country193_0_, as name193_0_, this_.attn as attn193_0_, this_.dept as dept193_0_, this_.street as street193_0_, ...

34. Querying GIS table (mysql)

Hello, I did not try it but I experience very compatible behaviour of HQL and SQL and so I assume you could just use something like: "SELECT p FROM Position p WHERE Contains(GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'), p.location) = 1" You first have to create Position entity class mapping to your database table. Hope this helps ...

35. How to do this MySQL query in hibernate?

36. Using MySQL specific clauses

37. Select using mySQL: Unknown column

Hello, I'm pretty new user of Hibernate and I guess it's a very simple question, but I could't find an answer :( I'm trying to use Hybernate with a mySQL database. I've got a User class: Code: @Entity(name = "user") @Table(name = "user") public class ShopUser { @Id @Column(name = "user_id") private ...

38. HQL help for SQL query. it should work in atleast mysql and

Hi All, I need help to write a HQL query for the following SQL query. The query should work in all databases. At least in MySQL and SQL Server. Please help me out. sql = select convert(varchar(11) ,billing_date,120) as billingDate, isnull(f.mul,1) mul , (isnull((select top 1 price from table1 bp1 where and billing_date>=dateadd(dd,1,dateadd(mm,-12,convert(varchar(11),bp.billing_date,120))) and billing_date

39. problem in having clause against MySQL database.

The hibernate generate the alias of field. In Having clause, it use the field name, so the database will say that there is no this field in database. warning : SQL Error: 1054, SQLState: S0022 2004-1-2 8:37:20 net.sf.hibernate.util.JDBCExceptionReporter logExceptions Hibernate: select as id0_, as id1_, as id2_, as name0_, forum0_.priority as priority0_, as name1_, category1_.priority as ...

40. Problem with MySQL Query

OK let me try again. I was just trying to follow the example from Hibernate's manual, but I get this error message. I can use this kind of HQL with PostGres without any problem. Query query = session.createQuery("from volleyball_user"); List list = query.list(); SEVERE: Could not execute query java.sql.SQLException: Syntax error or access violation, message from server: "You have an error ...

41. Subquery & mysql

Ok. Thank you about information, but i need to get this work with older version. The sql query, which i need to get work, is something like this: select * from A where a.key not in ( select key from B where B.key2 != '10') .. or something like that.. actually i don't know does it work, but that's the idea. ...

42. mysql 4.1 and subqueries

43. query.setBoolean not working with MySQL 4.0.18

I am getting unexpected results from an HQL query using Hibernate 2.1.4 and MySQL 4.0.18. I have a Status table/object (PoStatus) that has a boolean property "validForNewUse". It is defined as follows: Code: /** * Valid for new use. ...

44. Query help (mysql "left" operation)

45. MySQL and HSQLDB Inconsistency

46. [Hibernate/MySQL] : Natif SQL queries ???

47. Problem with selecting from MySql Table in Hibernate.

Full stack trace of any exception that occurs: net.sf.hibernate.exception.SQLGrammarException: Could not save object at net.sf.hibernate.exception.ErrorCodeConverter.convert( at net.sf.hibernate.exception.JDBCExceptionHelper.convert( at net.sf.hibernate.impl.SessionImpl.convert( at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier( at at com.iflex.hibernate.EventManager.DoUpdate( at com.iflex.hibernate.EventManager.main( Caused by: java.sql.SQLException: Table not found: EVENTS in statement [select max(uid) from "EVENTS"] at org.hsqldb.Trace.getError(Unknown Source) at org.hsqldb.jdbcResultSet.(Unknown Source) at org.hsqldb.jdbcConnection.executeStandalone(Unknown Source) at org.hsqldb.jdbcConnection.execute(Unknown Source) at org.hsqldb.jdbcStatement.fetchResult(Unknown Source) at org.hsqldb.jdbcStatement.executeQuery(Unknown Source) at org.hsqldb.jdbcPreparedStatement.executeQuery(Unknown Source) ...

48. mysql secondary-cache query cache

Regular Joined: Sat Feb 21, 2004 8:54 pm Posts: 60 Location: Lakewood, California hi, still tryin to get the secondary-cache to work with queries. i made simpler example of just one tabel with one extra field. i still get the following with mutable and read-write. 01:45:45,260 DEBUG SQL:237 - select as id, t10_.field1 as field1 from T1 t10_ where (t10_.field1=? ...

49. MySQL & seect count(*)

Hibernate version: 2.1.7 Hi. I am using MySQL 4.1.7 and want to count the number of query results without actually returning them. Here is the query: countsOfQueryResults = ((Integer)session.iterate("select count(*) from PERSON").next() ).intValue(); Unfortunately this results in Hibernate: select count(*) as x0_0_ from (util.JDBCExceptionReporter 57 ) SQL Error: 1064, SQLState: 42000 (util.JDBCExceptionReporter 58 ) Syntax error or access violation message from ...

50. Hibernate + Mysql = Count(*) problem

Hi, we develop using Hibernate. this is an example of a query that is launched : select count(*) from tblPostReaction this inner join tblUser user on left outer join tblUserLevel userlevel2_ on inner join tblPost post on left outer join tblPostType posttype4_ on where 1=1 order by asc tblPostReaction has 500.000 records tblUser has 17.000 records ...

51. HQL Query in MySQL 3.1.8

Regular Joined: Mon Aug 22, 2005 1:11 pm Posts: 50 Location: Pasadena, CA Im using MySQL 3.1.8 and when I try to perfom a HQL query that selects a team object based on if its client set is empty or not it generates a query that MySQL 3.1.8 does not support. the query Im using is : "FROM Team t WHERE ...

52. Query Problem with MySql

Hi I want to do a simple query but i get an error: The query is this: getHibernateTemplate().find("from Users"); but i get this error: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from' at line 1 Im using Mysql 5 whith Hibernate 2.1.3 ...

53. about mysql limit clause

54. Support for MySQL native sql Funtions in SELECT clause

// open a new Hibernate session session = HibernateFactory.newSession(); // setup the query Query ...

55. can i run "select now()" ? (mysql)

56. Mysql Query Cache not working

Hi All, We have a mysql instance running with query caching turned on. When we run for example a select statement from the mysql console the query gets cached, however none of our hibernate queries get cached. I have turned hibernate.show_sql on and taken the output from hibernate, run that from the mysql console and that get's cached. So can someone ...

57. Hibernate and MySQL 5 query cache

58. tutorial - mysql can't connect - database name not selected

what is the name of the database to be created and selected (for the Events tutorial) in the mySQL connection settings? Is there somewhere else to put the name into the build configuration? The Events Tutorial database setup doesn't specify a database name in the connection configuration template. This works for hsqldb, but mySQL needs a database name specification, as shown ...

60. Using MySql : the query

HI I m firing a query like this in my code.. List list=HibernateUtil.getSessionFactory().getCurrentSession() .createQuery("from Person where firstName = :f").setString("f",fName) .list(); i am passing fName="XYZ" as input to this.... but i m getting the following error stating tht the query is gramatically wrong. Now, if i fire the same query appending the db name in the db browser window, it runs just ...

61. Hibernate query gets bonehead error from MySQL

Hibernate version: 3.0 Name and version of the database you are using: MySQL 4.0.20 The generated SQL (show_sql=true): Actually this is the full query, via p6spy: select as id0_, as name0_0_, this_.description as descript3_0_0_, this_.startdate as startdate0_0_, this_.enddate as enddate0_0_, this_.starttime as starttime0_0_, this_.endtime as endtime0_0_, as phone0_0_, as link0_0_, this_.location as location0_0_, this_.address as address0_0_, ...

62. HQL to support MySQL and SQLServer to count from Query obj

select count(slide_id) from ( select distinct f.folder_slide_id, s.slide_id from space_slide as s left join space_folder_slide as f on (f.slide_id = s.slide_id) left join space_attribute_value as a on (a.slide_id = s.slide_id) where s.is_archived = 0 and (s.slide_id not in (select slide_id from space_folder_slide) and (s.external_ref like '%win%' or a.value like '%win %')) ...

63. Equivalent MySQL Query in Hibernate

Hi , I need help with the following query in hibernate.using Hibernate v3. MySQL query = Select * from A where qid in (Select qid from B where x = 1); What is the equivalent HSQL query? or How do you write the above query in createSQLquery() method? ANy help is appreciated Thanks in advance Gav

66. Mysql query problem

67. Timing out long queries with MySQL, C3P0, and Hibernate

We are using Hibernate, C3P0, and MySQL to back an application that uses a Coherence cache. At times, long queries will cause Coherence to barf. In an ideal world, we would be able to configure something in the db layer to time out and throw an exception if a query was taking an exceptionally long time. However, as I understand it ...