Announcement Announcement Module
Collapse
No announcement yet.
Hibernate Query failes to run with MYSQL Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Hibernate Query failes to run with MYSQL

    Hi All,

    I am little new to Spring.

    I am developing a small application using SpringMVC + Hibernate + Oracle/MySQL.

    The "find" method of getJpaTemplate returns me below query:

    select
    briefcaseu0_.userName as userName0_0_,
    briefcaseu0_.confirmPassword as confirmP2_0_0_,
    briefcaseu0_.EMAIL as EMAIL0_0_,
    briefcaseu0_.FIRST_NAME as FIRST4_0_0_,
    briefcaseu0_.LAST_NAME as LAST5_0_0_,
    briefcaseu0_.PASSWORD as PASSWORD0_0_,
    briefcaseu0_1_.companyName as companyN2_1_0_,
    briefcaseu0_1_.faxNumber as faxNumber1_0_,
    briefcaseu0_1_.websiteURL as websiteURL1_0_,
    case
    when briefcaseu0_1_.userName is not null then 1
    when briefcaseu0_.userName is not null then 0
    end as clazz_0_
    from
    BRIEFCASEUSER briefcaseu0_,
    Affiliate briefcaseu0_1_
    where
    briefcaseu0_.userName=briefcaseu0_1_.userName(+)
    and briefcaseu0_.userName=?

    This query work perfectly fine, if I use Oracle 10g as the database. But when I switch to MySQL database the above query fails to execute. It throws below exception:
    <You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') and briefcaseu0_.userName=' at line 1.

    I guess, the way Hibernate generated the Outer join by using PLUS(+) sign is not working in MySQL. I one of the forum (http://www.dbforums.com/mysql/974210...t-working.html) read that MySQL does not support the oracle proprietary "plus sign" syntax for outer joins.

    Could you please help me how do I get out of this issue. How can I tell Hibernate not use PLUS signs for Outer joins.

    BTW, I am using hibernate3.jar in my application.

    Thanks in advance for you help.

    Regards,
    Prasad

  • #2
    Please use [ code][/code ] tags when posting code/xml/sql/stacktraces.

    If hibernate generates the wrong query you are using the wrong dialect... You will also need to switch to the MySQL dialect matching your MySQL version.

    Comment


    • #3
      orcacle vs mysql

      if I remember correctly

      this is oracle speciffic (+) is left join ?

      Code:
      where
      briefcaseu0_.userName=briefcaseu0_1_.userName(+)
      a mysql equivalent would be something like that

      Code:
      select * 
      from BRIEFCASEUSER briefcaseu0_ b0
      left join briefcaseu0_1_ b1
        on b0.username = b1.username
      this is not hibernate code thou, this is sql.

      Regards,
      Emil

      Comment


      • #4
        I have forgot to set database property value to MYSQL. in below code of my persistancecontext.xml file.

        Code:
        <bean id="entityManagerFactory"
        		class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        		<property name="dataSource" ref="ds" />
        		<property name="jpaVendorAdapter">
        			<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        				<property name="database" value="${jpa.database}" />
        			</bean>
        		</property>
        	</bean>
        I changed dburl, username, password, dialect when switched from Oracle to MySql, but, I somehow missed to change the database property value from Oracle to MYSQL. This was reason that Hibernate still created the Query with PLUS Sign for Outer Joins. Once, I changed the value to MYSQL, the query generated properly and executed without issues. It was a foolish mistake from my side ... Now I moved this value to .properties file.

        Thanks for all who took pain to look into this issue and replying.

        Thanks,
        Prasad

        Comment

        Working...
        X