[Dennis]

I get the following error after scaffolding.

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:583)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:501)
javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
com.opensymphony.sitemesh.webapp.SiteMeshFilter.obtainContent(SiteMeshFilter.java:129)
com.opensymphony.sitemesh.webapp.SiteMeshFilter.doFilter(SiteMeshFilter.java:77)
org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:112)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

root cause

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:637)
org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:74)
com.wn.dao.SapCustomerDAOImpl.findAllSapCustomers(SapCustomerDAOImpl.java:89)
com.wn.dao.SapCustomerDAOImpl.findAllSapCustomers(SapCustomerDAOImpl.java:77)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
ETC.

The cause is the following generated query:

SELECT sapaddress0_.CUSTOMER_NUM as CUSTOMER1_0_0_,
sapaddress0_.ADDRESS1 as ADDRESS2_0_0_,
sapaddress0_.ADDRESS2 as ADDRESS3_0_0_,
sapaddress0_.ADDRESS3 as ADDRESS4_0_0_,
sapaddress0_.CITY as CITY0_0_,
sapaddress0_.COUNTRY as COUNTRY0_0_,
sapaddress0_.LASTUPDATE as LASTUPDATE0_0_,
sapaddress0_.LASTUPDATE_BY as LASTUPDATE8_0_0_,
sapaddress0_.sapCustomer_CUSTOMER_NUM as sapCust11_0_0_,
sapaddress0_.STATE as STATE0_0_,
sapaddress0_.ZIPCODE as ZIPCODE0_0_
FROM DB2IEC1.SAP_ADDRESS sapaddress0_
WHERE sapaddress0_.sapCustomer_CUSTOMER_NUM=?

The above column does not exist: sapaddress0_.sapCustomer_CUSTOMER_NUM as sapCust11_0_0_,

This query (just modified the first one) works without that column:

SELECT sapaddress0_.CUSTOMER_NUM as CUSTOMER1_0_0_,
sapaddress0_.ADDRESS1 as ADDRESS2_0_0_,
sapaddress0_.ADDRESS2 as ADDRESS3_0_0_,
sapaddress0_.ADDRESS3 as ADDRESS4_0_0_,
sapaddress0_.CITY as CITY0_0_,
sapaddress0_.COUNTRY as COUNTRY0_0_,
sapaddress0_.LASTUPDATE as LASTUPDATE0_0_,
sapaddress0_.LASTUPDATE_BY as LASTUPDATE8_0_0_,
sapaddress0_.STATE as STATE0_0_,
sapaddress0_.ZIPCODE as ZIPCODE0_0_
FROM DB2IEC1.SAP_ADDRESS sapaddress0_
WHERE sapaddress0_.CUSTOMER_NUM = ‘200132 ‘

I tried to modify the annotations to make this work, but the IDE keeps wiping out my changes. I’m not sure how to stop this, though I’ve looked in the preferences. I shouldn’t have this problem in the first place, however.

Rgds, Dennis

Please respond soon! I’m not happy.

[davemeurer]

Hi Dennis,

You can turn off code generation after scaffolding by double clicking the Spring DSL artifact found in the Project Explorer. Go to the Code Generation tab, and you can uncheck any line item that you don’t want the generation builder to generate. These Code Generation tabs are also on all the core editors.

Another way to turn off generation, is to uncheck the builders. In the project properties > Builders, you can uncheck Skyway Code Generation Builder and Skyway Code Synchronization Builder. Do this if you plan not to scaffold anything else into your project.

Yet another way is to skip the first step of “Adding Spring Code Generation Capabilities”. A web project doesn’t need to have the Spring Code Generation Capabilities added before Scaffolding. The difference of doing only scaffolding, is that you won’t get the editors or the builders.

Regarding your 1-to-1 table issue, please send the schema and DB2 version so our support team can take a look at your scenario.

Also, which annotations are you trying to modify? The IDE should be preserving your changes.

Kind regards,
Dave

[Dennis]

CUSTOMER TABLE
Name Type Length Scale Nulls Desc Default
CUSTOMER_NUM CHARACTER 10 N
CENTRAL_BLOCK CHARACTER 1 N ‘N’
COMPANYNAME VARCHAR 40 N
COMPANYNAME2 VARCHAR 40 Y
STORE_NUMBER VARCHAR 20 Y
TERMS_SIGNED_DATE DATE 4 Y
COUNTRY CHARACTER 3 N
LINE_OF_BUSINESS CHARACTER 4 N
CMM_AGREEMENT_DATE DATE 4 Y
IS_CMM_CUST CHARACTER 1 N ‘N’
LAST_MODIFIED_ON TIMESTAMP 10 Y
LAST_MODIFIED_BY VARCHAR 20 Y
CREATED_ON TIMESTAMP 10 N
CREATED_BY VARCHAR 20 N

ADDRESS TABLE
Name Type Length Scale Nulls Desc Default Generated
CUSTOMER_NUM CHARACTER 10 N
ADDRESS1 VARCHAR 70 N
ADDRESS2 VARCHAR 50 Y
ADDRESS3 VARCHAR 50 Y
CITY VARCHAR 40 Y
STATE VARCHAR 20 Y
ZIPCODE VARCHAR 10 Y
COUNTRY CHARACTER 3 N
LASTUPDATE TIMESTAMP 10 N
LASTUPDATE_BY VARCHAR 20 N

DB2 VERSION: 8.2.3 (Don’t think this is the issue)

I was trying to change the @OneToOne Annotations in Customer and Address

[Heflin Hogan]

I’m assuming that CUSTOMER_NUM is the primary key on both tables, given the one-to-one relationship, but is the address CUSTOMER_NUM defined as a foreign key?

[Dennis]

Yes, customer_num is defined as a foreign key in address to customer and is also defined as primary key in address.

[Dennis]

Well, after much fooling around I got it to work.

I removed the @IdClass annotations in both the parent (Customer) and child (Address) classes.

I’m not sure why you generate these *PK classes when there is a single unique key?

I also changed the @OneToOne annotations and added a couple:

In Customer:

@XmlElement(name = “”, namespace = “”)
@OneToOne(optional=false, cascade = { CascadeType.ALL }, fetch = FetchType.LAZY)
@PrimaryKeyJoinColumn
SapAddress sapAddress;

In Address:

@OneToOne(mappedBy=”sapAddress”, cascade = { CascadeType.REMOVE }, fetch = FetchType.LAZY)
@PrimaryKeyJoinColumn(name=”CUSTOMER_NUM”)
@XmlElement(name = “”, namespace = “”)
SapCustomer sapCustomer;

I made so many changes I haven’t even checked what was originally gen’d. I’m new to JPA, so there are probably things wrong here, but it works.

[Heflin Hogan]

You beat me to it by about 30 seconds. You actually only need to add @PrimaryKeyJoinColumn to the relationship in address, and you don’t have to specify name. Removing the ID classes works in this case, and for multi-field keys you’d point both tables to the same ID class. I’ll defer the question on why we generate the PK classes to an architect.

It turns out that we have an open issue on this already, scheduled for the next maintenance release.

-Heflin

[davemeurer]

Regarding the PK class: It is usually only applicable for *composite* primary keys. Including the PK class also has ramifications for users trying to tie in auto generated columns. I’ve included some good resources on the topic below.

We do have a feature request to only generate the IdClass if there are composite primary keys. If your project doesn’t contain composite primary keys, I would recommend deleting these files and turning generation off for the PK class if you have the Spring DSL Editors.

http://wiki.eclipse.org/Introduction_to_EclipseLink_JPA_(ELUG)#.40IdClass
http://opensource.atlassian.com/projects/hibernate/browse/ANN-257
http://www.java2s.com/Code/Java/JPA/SetIdClassforCompoundKey.htm
http://java.sun.com/developer/technicalArticles/J2EE/jpa/ (search for IdClass)
https://jira.jboss.org/jira/browse/EJBTHREE-508

[Author]

Posts