JPA + DB2 can't execute simple query

Question

Edit1: When Hibernate connected to MS-SQL, all columns and tables are scanning. Logs

But when Hibernate connected to DB2, it is trying to make out (render) again all tables and columns which include ''i'' letter. Logs

After I scanned the tables and columns I realised that all letters was big.In fact , every letter was big on DB2. Hibernate uses small letters for inquiry and because of DB2's big letter sensitivity it does not realise column names. For that reason, it gives an alarm which is in below,

WARN SqlExceptionHelper: SQL Error: -99999, SQLState: 42703 
15:15:22,025 ERROR SqlExceptionHelper: An undefined column name was detected.

How can I solve this problem?

I have to retrieve data from a table in db2 using jpa. When I try to execute a query using the entity manager I get errors don't know where is the problem exactly. My code running on MS-SQL and HSQL-DB... But I connect DB2 the message error : *

Query qry = em.createQuery("from Holding h where h.RDeleted=:arg1");-

13:26:38,135 DEBUG SQL: select holding0_.HoldingId as HoldingId1_, holding0_.RDeleted as RDeleted1_, holding0_.InsertDate as InsertDate1_, holding0_.SavesUserId as SavesUse4_1_, holding0_.UpdateDate as UpdateDate1_, holding0_.Updater as Updater1_, holding0_.Description as Descript7_1_, holding0_.HoldingName as HoldingN8_1_ from Holding holding0_ where holding0_.RDeleted=? Hibernate: select holding0_.HoldingId as HoldingId1_, holding0_.RDeleted as RDeleted1_, holding0_.InsertDate as InsertDate1_, holding0_.SavesUserId as SavesUse4_1_, holding0_.UpdateDate as UpdateDate1_, holding0_.Updater as Updater1_, holding0_.Description as Descript7_1_, holding0_.HoldingName as HoldingN8_1_ from Holding holding0_ where holding0_.RDeleted=? 13:26:38,428 WARN SqlExceptionHelper: SQL Error: -99999, SQLState: 42703 13:26:38,428 ERROR SqlExceptionHelper: An undefined column name was detected.

But it query works:

Select h.holdingId, h.holdingName, h.description from Holding h

My Datasource:

<bean class="org.apache.commons.dbcp.BasicDataSource" id="dataSourceDB2_JT400" destroy-method="close">
        <property value="com.ibm.as400.access.AS400JDBCDriver" name="driverClassName"/> 
        <property value="jdbc:as400://192.168.1.1/GULERP" name="url"/> 
        <property value="user" name="username"/> 
        <property value="PW" name="password"/> 
        <property value="5" name="initialSize"/>
    </bean>

My entityManager:

<bean id="entityManagerFactory"
        class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="persistenceUnitName" value="erp" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="showSql" value="true" />
                <property name="generateDdl" value="false" />
                <property name="databasePlatform" value="org.hibernate.dialect.DB2400Dialect" />
            </bean>
        </property>
        <property name="dataSource" ref="dataSourceDB2_JT400"/>
    </bean>

and My domain:

@Entity
@AccessType("field")
@Table(name = "Holding", uniqueConstraints = {@UniqueConstraint(columnNames={"HoldingName"})})
public class Holding extends BaseClass implements Serializable {

    transient static final long serialVersionUID = 5473887143181971744L;

    @Id
    @Column(name = "HoldingId", nullable = false, length=36)
    @GeneratedValue(generator="system-uuid")
    @GenericGenerator(name = "system-uuid", strategy = "uuid")
    private String holdingId;

    @Basic(optional = false)
    @Column(name = "HoldingName", nullable = false, length = 100)
    private String holdingName;

    @Column(name = "Description", length = 210)
    private String description;

    @OneToMany(mappedBy = "holdingId", fetch = FetchType.LAZY)
    private List<Company> companyList;

Compare all the column names in the query with all the column names in the table, and find which one is wrong or missing. — JB Nizet, Jul 09 '12 at 11:38
I checked, column names are all correct. Query executed, if I write the column names manually. And I checked hibernate query on DB2 client, query are executed. — SaRPaRDa, Jul 09 '12 at 11:48
Show us the code where you execute the query, and the complete mapping of the entity. — JB Nizet, Jul 09 '12 at 11:55
` @Override @SuppressWarnings("unchecked") @PreAuthorize("hasRole('ROLE_ADMIN')") public List getHoldingList() { List holdLst = null; try { Query qry = em.createQuery("Select m from Holding m"); //qry.setParameter("arg1", 0); holdLst = qry.getResultList(); } catch(NoResultException e) { e.printStackTrace(); } catch (Exception e) { throw new RuntimeException(e); } return holdLst; }` — SaRPaRDa, Jul 09 '12 at 12:57

score 1 · Answer 1 · answered Jul 09 '12 at 13:28

1

Try executing query "from Holding h". If it will still drop:

ERROR SqlExceptionHelper: An undefined column name was detected.

then it means that you mapped your column names with property names wrong.

You mentioned, that following query works:

Select h.holdingId, h.holdingName, h.description from Holding h

you can track down the column causing exception by adding all columns one by one into select.

answered Jul 09 '12 at 13:28

d1e

6,372
2
28
41

if I write all column name into query, hibernate runnig. But new error message: http://chopapp.com/#lzijtk2d – SaRPaRDa Jul 09 '12 at 13:57
NumberFormatException solved. Query qry = em.createQuery("from Holding h where h.RDeleted=:arg1",Holding.class); , – SaRPaRDa Jul 09 '12 at 21:08
please check my MS-SQL and DB2 logs. MSSQL: http://chopapp.com/#8kvl5v3a DB2: http://chopapp.com/#bg4x8aki – SaRPaRDa Jul 10 '12 at 11:56

SaRPaRDa · Accepted Answer · 2012-09-27T04:39:42.667

Solution:

    @Override
    @PreAuthorize("hasRole('ROLE_ADMIN')")
    public List<HoldingDto> getHoldingList()
    {
        List<HoldingDto> holdLst = null;
        try
        {
            String aa = (String) q.getSingleResult();

            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<HoldingDto> q = cb.createQuery(HoldingDto.class);
            Root<Holding> h = q.from(Holding.class);
            q.select(cb.construct(HoldingDto.class, h.get("holdingId"), h.get("holdingName"), h.get("description"), h.get("savesUserId"), h.get("insertDate"),
                    h.get("updateDate"), h.get("updater"), h.get("RDeleted")));
            holdLst = em.createQuery(q).getResultList();
        } 
        catch(NoResultException e)
        {
            e.printStackTrace();
        }
        catch (Exception e)
        {
            throw new RuntimeException(e);
        }

        return holdLst;
    }

score 0 · Answer 3 · answered Jul 09 '12 at 13:10

0

Query qry = em.createQuery("from Holding h where h.RDeleted=:arg1");-

shouldn't it be

Query qry = em.createQuery("select h from Holding h where h.RDeleted=:arg1") ?

answered Jul 09 '12 at 13:10

Repoker

202
3
12

1

Hibernate does not care whether "select h" is specified, it will automatically select root entity. – d1e Jul 09 '12 at 13:22

JPA + DB2 can't execute simple query

3 Answers3