I'm new to EJB and trying to get my head around translating SQL concepts to EJB entity beans. Suppose I have two tables: PEOPLE (id, name), CONTACT(pid, phone_number). If I want to get a list of all people whether or not they have phone #s, in my EJB session bean I simply issue a SQL query via JDBC such as: SELECT PEOPLE.name, CONT...
Is it possible to use JPQL for getting random rows? For example in SQL Server I would use: select * from myTable where columnName = 4 order by newid() Thanks, Rod ...
Hi I try to use an inner select, but get only the exception "HibernateException: Errors in named queries" The both JPA entities: public class A implements Serializable { @Id @Column(nullable = false) @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; } public class B implements Serializable { @Id @...
Lets say we have this JPA-annotated class, with a property of type List. This code is currently working fine. @Entity public class Family { ... @CollectionOfElements(targetElement=java.lang.String.class) @JoinTable(name = "elements_family", joinColumns = @JoinColumn(name = "idFamily") ) @Column(name = "elemen...
I'm have entities with latitude and longitude and I would like to select those nearest to a given point. I found this example that looks like it would work SELECT *, SQRT(POW((69.1 * (locations.latitude - 27.950898)) , 2 ) + POW((53 * (locations.longitude - -82.461517)), 2)) AS distance FROM locations ORDER BY distance ASC LIMI...
I have an entity class called "Group" and NetBeans warns me "The entity table name is a reserved Java Persistence QL keyword". A similar case would be the use of reserved SQL keywords. Will this name be escaped? Would the use of a different table name solve the problem @Table(name="otherName"). Or should I rename the class? ...
I have followed a working JPA example to retrieve Category objects as such: return (ArrayList<Category>) getEntityManager().createQuery("from Category").getResultList(); The query is very shorthand - and I can't find the rules for what is optional and what isn't in any of the guides. Is this brevity acceptable? Secondly, I want to no...
I like the idea of Named Queries in JPA for static queries I'm going to do, but I often want to get the count result for the query as well as a result list from some subset of the query. I'd rather not write two nearly identical NamedQueries. Ideally, what I'd like to have is something like: @NamedQuery(name = "getAccounts", query = "SE...
Hi guys, With Hibernate I'm used to do something like the following: select n from NetworkElement n join fetch n.site s where s.active is true However, EclipseLink complains a lot about this: Caused by: Exception [EclipseLink-8024] (Eclipse Persistence Services - 2.0.0.v20091127-r5931): org.eclipse.persistence.exceptions.JPQLExcept...
I've learnt recently that it is possible to create new objects in JPQL statements as follows: select new Family(mother, mate, offspr) from DomesticCat as mother join mother.mate as mate left join mother.kittens as offspr Is this something to be avoided or rather to embrace? When is usage of this feature justified in the light ...
Is it possible to access the SQL which is generated by JPQL? I would like to use the SQL to later create a view for my purposes. I am using Hibernate if it matters. ...
Is there an equvalent of SQL TEMEDIFF functionality in JPQL? Is that possible to query with JPQL to find the records in specific time range? ...
I have to query a Message that is in a provided list of Groups and has not been Deactivated by the current user. Here is some pseudo code to illustrate the properties and entities: class Message { private int messageId; private String messageText; } class Group { private String groupId; private int messageId; } class Deactiva...
We need to make sure only results within the last 30 days are returned for a JPQL query. An example follows: Date now = new Date(); Timestamp thirtyDaysAgo = new Timestamp(now.getTime() - 86400000*30); Query query = em.createQuery( "SELECT msg FROM Message msg "+ "WHERE msg.targetTime < CURRENT_TIMESTAMP AND msg.targetTime > {ts, '...
Hi, I have a table with users and are trying to get a list with the people who have birthday today so the app can send an email. The User is defined as @Entity public class User { @Size(max = 30) @NotNull private String name; [...] @Temporal(TemporalType.DATE) @DateTimeFormat(style = "S-") protected Date ...
I have a one-to-one relation where I use CascadeType.PERSIST. This has over time build up a huge amount of child records that has not been deleted, to such an extend that it is reflected in the performance. Now I wish to add some code that cleans up the database removing all the child records that are not referenced by a parent. At the m...
In hibernate I want to run this JPQL / HQL query: select new org.test.userDTO( u.id, u.name, u.securityRoles) FROM User u WHERE u.name = :name userDTO class: public class UserDTO { private Integer id; private String name; private List<SecurityRole> securityRoles; public UserDTO(Integer id, String name, List<SecurityRole>...
My simple query looks like this on JPQL: SELECT COUNT(r) FROM org.domain.Resource r WHERE r._parent = :parent AND r._metadata[:metadataKey] is not null But Hibernate SQL output looks like this (both for H2 and MySQL): select count(resource0_.id) as col_0_0_ from resources resource0_, resou...
I need to limit number of entities returned by a query to some certain value inside a JPA query (through JPQL). Particularly: select m from Manual m //constraint e.g. in sql (mysql syntax) I would do it like: select * from Manual limit 1 The only solution that comes up is simply to get all entities and then choose first one, which ...
I would like to select every column of a table, but want to have distinct values on a single attribute of my rows (City in the example). I don't want extra columns like counts or anything, just a limited number of results, and it seems like it is not possible to directly LIMIT results in a JPQL query. Original table: ID | Name |...