Skip to main content

WHERE CLAUSE NOT WORKING

9 replies [Last post]
evenden
Offline
Joined: 2007-10-30

When I try a where clause in an entity managers createquery method the list is not filled. Can you help?

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
evenden
Offline
Joined: 2007-10-30

I have the answer.

The query string must surround the numerical id with quotes.

"select * from Section where pageid='1'"

mb124283
Offline
Joined: 2005-06-17

Hi,

this is interesting. What is the type of the page_id database column? Is it VARCHAR? If yes, I propose to change the the type of the pageId field to String:
private String pageId;

Then you might want to try the Java Persistence query again and pass a the id value as a string to setParameter.

Regards Michael

mb124283
Offline
Joined: 2005-06-17

Hi,

what entity classes and properties are you using and (more important) what is the query that returns an unexpected result?

Regards Michael

evenden
Offline
Joined: 2007-10-30

I am using Toplink's javax.persistence. I have a table which passes to a method when a row is selected. The result is bound to another table displaying two columns. The method code is:

public void getSection(java.lang.Short id){
sectionQuery = entityManager2.createNativeQuery("SELECT * from Section s WHERE s.page_id=?1",Section.class);
sectionQuery.setParameter(1, id);
sectionList = (java.util.List

)sectionQuery.getResultList();
abonding = org.jdesktop.swingbinding.SwingBindings.createJTableBinding(org.jdesktop.beansbinding.AutoBinding.UpdateStrategy.READ_WRITE, sectionList, jTable3);
org.jdesktop.swingbinding.JTableBinding.ColumnBinding columnBinding;
columnBinding = abonding.addColumnBinding(org.jdesktop.beansbinding.ELProperty.create("${id}"));
columnBinding.setColumnName("id");
columnBinding.setColumnClass(Short.class);
columnBinding = abonding.addColumnBinding(org.jdesktop.beansbinding.ELProperty.create("${align}"));
columnBinding.setColumnName("Alignment");
columnBinding.setColumnClass(String.class);
abonding.bind();
}

mb124283
Offline
Joined: 2005-06-17

Hi,

any special reason why you are using a native SQL query? As far as I understood your example, Section is a entity class (meaning it is annotated with @Entity). Then I propose to use a Java Persistence query:

Query q = em.createQuery("SELECT s FROM Section s WHERE s.id = :id");
q.setParameter("id", id);
List

The above assumes class Section has a field or property called id that is mapped to the column page_id in the database.

Regards Michael

evenden
Offline
Joined: 2007-10-30

Hi,

I have tried a normal query, a named query and lastly a native query. My Section class looks like this:

public class Section implements Serializable {
@Id
@Column(name = "id", nullable = false)
private Short id;
@Column(name = "page_id", nullable = false)
private short pageId;
@Column(name = "align")
private String align;
..........
My native query works fine without the where clause. I am lost as to what the problem may be.

Regards Nigel

mvatkina
Offline
Joined: 2005-04-04

Does your query return expected results when you execute it either from a jdbc call or directly in the database?

thanks,
-marina

evenden
Offline
Joined: 2007-10-30

Yes, JDBC and Database queries work ok. Strange.

Nigel

mvatkina
Offline
Joined: 2005-04-04

Did you try to turn on TopLink logging?
(See https://glassfish.dev.java.net/javaee5/persistence/entity-persistence-su... for the options).

Regards,
-marina