-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 posts ] 
  Previous topic | Next topic 
Author Message
rodney757
 Post subject: Hibernate native query throwing SQLException column not foun
PostPosted: Mon Dec 12, 2016 3:51 pm 
Newbie

Joined: Mon Dec 12, 2016 3:45 pm
Posts: 16
I am trying to issue a native query using hibernate 5.0 and spring data jpa.

My orm.xml file is as following:
Code:
<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_1.xsd"
                 version="2.1">
    <sql-result-set-mapping name="OAuthToken">
        <entity-result entity-class="biocode.fims.entities.OAuthToken">
            <field-result name="oAuthTokenId" column="t.oAuthTokenId"/>
            <field-result name="token" column="t.token"/>
            <field-result name="refreshToken" column="t.refreshToken"/>
            <field-result name="ts" column="t.ts"/>
            <field-result name="oAuthClient" column="t.clientId"/>
            <field-result name="user" column="t.userId"/>
        </entity-result>
        <entity-result entity-class="biocode.fims.entities.OAuthClient">
            <field-result name="clientId" column="cId"/>
            <field-result name="clientSecret" column="c.clientSecret"/>
            <field-result name="callback" column="c.callback"/>
        </entity-result>
        <entity-result entity-class="biocode.fims.entities.User">
            <field-result name="userId" column="uId"/>
            <field-result name="username" column="u.username"/>
            <field-result name="password" column="u.password"/>
            <field-result name="hasSetPassword" column="u.hasSetPassword"/>
            <field-result name="email" column="u.email"/>
            <field-result name="institution" column="u.institution"/>
            <field-result name="enabled" column="u.enabled"/>
            <field-result name="admin" column="u.admin"/>
            <field-result name="firstName" column="u.firstName"/>
            <field-result name="lastName" column="u.lastName"/>
            <field-result name="passwordResetToken" column="u.passwordResetToken"/>
            <field-result name="passwordResetExpiration" column="u.passwordResetExpiration"/>
        </entity-result>
    </sql-result-set-mapping>
</entity-mappings>


I am executing the query as follows:

Code:
return (OAuthToken) em.createNativeQuery("SELECT t.oAuthTokenId, t.token, t.refreshToken, t.ts, t.clientId, t.userId, " +
                    "c.clientSecret, c.callback, c.clientId as cId, " +
                    "u.userId as uId, u.username, u.password, u.hasSetPassword, u.email, u.institution, u.enabled, u.admin, u.firstName, " +
                    "u.lastName, u.passwordResetToken, u.passwordResetExpiration " +
                    "from oAuthTokens t " +
                    "inner join users u on t.userId = u.userId " +
                    "inner join oAuthClients c on c.clientId = t.clientId " +
                    "where t.refreshToken = ?1 and TIMESTAMPDIFF(SECOND, t.ts, CURRENT_TIMESTAMP) <= ?2", "OAuthToken")
                    .setParameter(1, refreshToken)
                    .setParameter(2, expirationInterval)
                    .getSingleResult();


However I keep getting the exception:

Caused by: java.sql.SQLException: Column 'oAuthCli1_4_1_' not found.

The sql hibernate logs is:

Code:
SELECT
        t.oAuthTokenId,
        t.token,
        t.refreshToken,
        t.ts,
        t.clientId,
        t.userId,
        c.clientSecret,
        c.callback,
        c.clientId as cId,
        u.userId as uId,
        u.username,
        u.password,
        u.hasSetPassword,
        u.email,
        u.institution,
        u.enabled,
        u.admin,
        u.firstName,
        u.lastName,
        u.passwordResetToken,
        u.passwordResetExpiration
    from
        oAuthTokens t
    inner join
        users u
            on t.userId = u.userId
    inner join
        oAuthClients c
            on c.clientId = t.clientId
    where
        t.refreshToken = ?
        and TIMESTAMPDIFF(SECOND, t.ts, CURRENT_TIMESTAMP) <= ?


Why is hibernate looking for the "oAuthCli1_4_1_" table when parsing the ResultsSet? How can I fix this?
Top
 Profile  
 
vlad
 Post subject: Re: Hibernate native query throwing SQLException column not foun
PostPosted: Mon Dec 12, 2016 4:29 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
That's not the query that causes the issue. There might be some other query that doesn't get logged because of the exception.

Try logging it with datasource-proxy or p6spy as explained in this article.
Top
 Profile  
 
rodney757
 Post subject: Re: Hibernate native query throwing SQLException column not foun
PostPosted: Mon Dec 12, 2016 6:08 pm 
Newbie

Joined: Mon Dec 12, 2016 3:45 pm
Posts: 16
so I setup datasource-proxy and the only query logged was the query i posted earlier. Here's the logs from the query:

Code:
Name:CUSTOM_DS_LOGGER, Time:13, Success:True, Type:Prepared, Batch:False, QuerySize:1, BatchSize:0, Query:["SELECT t.oAuthTokenId, t.token, t.refreshToken, t.ts, t.clientId, t.userId, c.clientSecret, c.callback, c.clientId, u.userId, u.username, u.password, u.hasSetPassword, u.email, u.institution, u.enabled, u.admin, u.firstName, u.lastName, u.passwordResetToken, u.passwordResetExpiration from oAuthTokens t inner join users u on t.userId = u.userId inner join oAuthClients c on c.clientId = t.clientId where t.refreshToken = ? and TIMESTAMPDIFF(SECOND, t.ts, CURRENT_TIMESTAMP) <= ?"], Params:[(1=myParam,2=86400)]
2016-12-12 14:04:36,649 WARN  (qtp1264213713-34) [SqlExceptionHelper(logExceptions:129)] SQL Error: 0, SQLState: S0022
2016-12-12 14:04:36,652 ERROR (qtp1264213713-34) [SqlExceptionHelper(logExceptions:131)] Column 'oAuthCli1_4_1_' not found.


Not sure if this is helpful, but here's the stacktrace for the cause:

Code:
Caused by: java.sql.SQLException: Column 'oAuthCli1_4_1_' not found.
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
   at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1076)
   at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2580)
   at org.hibernate.type.descriptor.sql.IntegerTypeDescriptor$2.doExtract(IntegerTypeDescriptor.java:62)
   at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
   at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:235)
   at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:231)
   at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:222)
   at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:296)
   at org.hibernate.loader.Loader.extractKeysFromResultSet(Loader.java:789)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:714)
   at org.hibernate.loader.Loader.processResultSet(Loader.java:972)
   at org.hibernate.loader.Loader.doQuery(Loader.java:930)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
   at org.hibernate.loader.Loader.doList(Loader.java:2610)


It looks like the query is executing successfully, but this is maybe a bug in hibernate result mapping?
Top
 Profile  
 
vlad
 Post subject: Re: Hibernate native query throwing SQLException column not foun
PostPosted: Mon Dec 12, 2016 6:39 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
It's because Hibernate requires you to map the Native SQL query result set to the expected entity. Check out our User Guide for more details:

http://docs.jboss.org/hibernate/orm/5.2 ... tity-query
Top
 Profile  
 
rodney757
 Post subject: Re: Hibernate native query throwing SQLException column not foun
PostPosted: Mon Dec 12, 2016 6:57 pm 
Newbie

Joined: Mon Dec 12, 2016 3:45 pm
Posts: 16
I thought I was doing that? in the orm.xml I have:

Code:
<sql-result-set-mapping name="OAuthToken">
  ... see 1st post


Then I call:

Code:
(OAuthToken) em.createNativeQuery("sql string", "OAuthToken").getSingleResult() // see 1st post for entire query


I have also tried the following:

Code:
return (OAuthToken) em.createNamedQuery("OAuthToken.getOAuthToken")
                    .setParameter("refreshToken", refreshToken)
                    .setParameter("expirationInterval", expirationInterval)
                    .getSingleResult();

orm.xml:
Code:
    <named-native-query name="OAuthToken.getOAuthToken" result-set-mapping="OAuthToken">
        <query>SELECT t.oAuthTokenId, t.token, t.refreshToken, t.ts, t.clientId, t.userId,
            c.clientSecret, c.callback, c.clientId as cId,
            u.userId as uId, u.username, u.password, u.hasSetPassword, u.email, u.institution, u.enabled, u.admin, u.firstName,
            u.lastName, u.passwordResetToken, u.passwordResetExpiration
            from oAuthTokens t
            inner join users u on t.userId = u.userId
            inner join oAuthClients c on c.clientId = t.clientId
            where t.refreshToken = :refreshToken and TIMESTAMPDIFF(SECOND, t.ts, CURRENT_TIMESTAMP) &lt;= :expirationInterval
        </query>
    </named-native-query>

    <sql-result-set-mapping name="OAuthToken" >
        <entity-result entity-class="biocode.fims.entities.OAuthToken">
            <field-result name="oAuthTokenId" column="t.oAuthTokenId"/>
            <field-result name="token" column="t.token"/>
            <field-result name="refreshToken" column="t.refreshToken"/>
            <field-result name="ts" column="t.ts"/>
            <field-result name="oAuthClient" column="t.clientId"/>
            <field-result name="user" column="t.userId"/>
        </entity-result>
        <entity-result entity-class="biocode.fims.entities.OAuthClient">
            <field-result name="clientId" column="cId"/>
            <field-result name="clientSecret" column="c.clientSecret"/>
            <field-result name="callback" column="c.callback"/>
        </entity-result>
        <entity-result entity-class="biocode.fims.entities.User">
            <field-result name="userId" column="uId"/>
            <field-result name="username" column="u.username"/>
            <field-result name="password" column="u.password"/>
            <field-result name="hasSetPassword" column="u.hasSetPassword"/>
            <field-result name="email" column="u.email"/>
            <field-result name="institution" column="u.institution"/>
            <field-result name="enabled" column="u.enabled"/>
            <field-result name="admin" column="u.admin"/>
            <field-result name="firstName" column="u.firstName"/>
            <field-result name="lastName" column="u.lastName"/>
            <field-result name="passwordResetToken" column="u.passwordResetToken"/>
            <field-result name="passwordResetExpiration" column="u.passwordResetExpiration"/>
        </entity-result>
    </sql-result-set-mapping>


I looked at the link and am still not seeing what I'm doing wrong.

Thanks for the help!
Top
 Profile  
 
vlad
 Post subject: Re: Hibernate native query throwing SQLException column not foun
PostPosted: Tue Dec 13, 2016 2:35 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
The easiest way to figure out what HHH-10746 stands for is to execute a JPQL query against OAuthClient entity.

What SQL query is executed for the following JPQL?

Code:
select c
from OAuthClient c
where c.clientId = :clientId


I think you are not selecting all columns from OAuthClient entity.
Top
 Profile  
 
rodney757
 Post subject: Re: Hibernate native query throwing SQLException column not foun
PostPosted: Tue Dec 13, 2016 12:15 pm 
Newbie

Joined: Mon Dec 12, 2016 3:45 pm
Posts: 16
You were correct, I was missing a column.

Now I'm still running into a problem, but I suspect I know what it is.

In my OAuthClient class, I have 2 lazy loaded collections. OAuthNonces and OAuthTokens that are both "mapped by" OAuthClient.

I am getting the following exception:
Code:
Caused by: java.sql.SQLException: Column 'clientId3_4_1_' not found.


However it is finding the OAuthClient.clientId class from my provided alias. It appears that the above exception is coming from the lazy loaded properties. The hibernate persister has the property name when the exception is being thrown as

Code:
_biocode_fims_entities_OAuthToken_oAuthClient


Do I need to do something in my sqlResultSetMapping to account for the lazy collections? I do not want to fetch the collections however?
Top
 Profile  
 
vlad
 Post subject: Re: Hibernate native query throwing SQLException column not foun
PostPosted: Tue Dec 13, 2016 1:05 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
But if the collections are lazy, why does Hibernate want to fetch them?

I would have expected this behavior if the associations were EAGER. Not that @ManyToOne or @OneToOne associations are EAGER by default, as explained by this article.
Top
 Profile  
 
rodney757
 Post subject: Re: Hibernate native query throwing SQLException column not foun
PostPosted: Tue Dec 13, 2016 1:44 pm 
Newbie

Joined: Mon Dec 12, 2016 3:45 pm
Posts: 16
hmm. Do you think that something else might be causing the problem?

OAuthClient has the following collection mappings:

Code:
    @OneToMany(mappedBy = "oAuthClient",
            fetch = FetchType.LAZY
    )
    public Set<OAuthNonce> getoAuthNonces() {
        return oAuthNonces;
    }

    @OneToMany(mappedBy = "oAuthClient",
            fetch = FetchType.LAZY
    )
    public Set<OAuthToken> getoOAuthTokens() {
        return oAuthTokens;
    }


the OAuthNonce OAuthClient mapping is:
Code:
    @ManyToOne
    @JoinColumn(name = "clientId",
            foreignKey = @ForeignKey(name = "FK_oAuthNonces_clientId"),
            referencedColumnName = "clientId")
    public OAuthClient getoAuthClient() {
        return oAuthClient;
    }


The exact error I am getting is:
Code:
Caused by: java.sql.SQLException: Column 'clientId3_4_1_' not found.


The oAuthClient sql portion looks like:

Code:
SELECT ...   c.clientSecret, c.callback, c.clientId, c.oAuthClientId, ...


c.oAuthClientId was the missing column from earlier, and the hibernate persister seems to have the correct property name "c.clientId" for the OAuthClient.clientId property.

When I fetch the OAuthClient by itself, the collections are lazily loaded.

not sure if this is helpful, but when debugging, if I call persister.getPropertyNames() I get 7 properties:

Code:
callback
clientId
clientSecret
oAuthNonces
oOAuthTokens
_biocode_fims_entities_OAuthToken_oAuthClient
_biocode_fims_entities_OAuthNonce_oAuthClient


the matching SuffixedPropertyAliases from the call getEntityAliases()[i].getSuffixedPropertyAliases( persister ):

Code:
c.callback
c.clientId
c.clientSecret
c.clientId
empty String[]
clientId3_4_1_
clientId3_4_1_



The "clientId3_4_1_" propertyAliases seem to correspond with the "_biocode_fims_entities_OAuthToken_oAuthClient" and "_biocode_fims_entities_OAuthNonce_oAuthClient" propertyNames. I'm not sure if this is relevant or not.
Top
 Profile  
 
vlad
 Post subject: Re: Hibernate native query throwing SQLException column not foun
PostPosted: Tue Dec 13, 2016 2:25 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
You didn't read that article that I provided you.

The @ManyToOne oAuthClient association is fetched eagerly, not lazily.

Anyway, why don't you fetch a DTO projection? Why do you use a native query to fetch entities?
Top
 Profile  
 
rodney757
 Post subject: Re: Hibernate native query throwing SQLException column not foun
PostPosted: Tue Dec 13, 2016 2:44 pm 
Newbie

Joined: Mon Dec 12, 2016 3:45 pm
Posts: 16
right, I want the @ManyToOne to be fetched eagerly. That's why I'm doing a join.

I tried removing the OAuthClient from OAuthToken and I was able to fetch the OAuthToken and joined User with no problems.

So I started comparing the OAuthClient relationships.

The @JoinColumn for OAuthClient is not the @ID column, whereas with my User entity it is.

I moved the @Id to the clientId field in OAuthClient and everything worked.

Is this a bug in hibernate? or is this expected? This was a legacy db schema that we migrated. It look like I should remove the oAuthClientId column as we never use it

Thanks for the help!
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 11 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.