Hibernate Community • View topic - How to register and use a MySQL function programmatically

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

How to register and use a MySQL function programmatically

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

YoannCH

Post subject: How to register and use a MySQL function programmatically

Posted: Tue May 31, 2011 11:11 am

Newbie

Joined: Tue May 31, 2011 10:53 am
Posts: 3

Hello there,

I'm facing some issues using hibernate + MySQL (5.1.41), especially using a MySQL function I registered previously when I try to do a query using hibernate (and so, Hibernate Query Language).

I think that the HQL doesn't "know" my MySQL function when I try to use it in a query, for exemple when I try to do :

Code:

Query q = em.createQuery("select id from MyObject where MyFunction(firstparam, seconparam) <= condition");
List<?> l = q.getResultList();

How can I register my MySQL function to allow Hibernate to "understand" it?

I've found in the API documentation this class but nothing to allow me to use it...
The reference is talking about Database Functions here but quite succintly...

Any help would be very welcome :)

Yoann

Top

J0k3r

Post subject: Re: How to register and use a MySQL function programmatically

Posted: Tue May 31, 2011 11:44 am

Newbie

Joined: Mon May 30, 2011 7:00 am
Posts: 3

If parameters depends on entity fields, you can map your function via @Formula annotation.
For example:

Code:

@javax.persistence.Table(name = "USER_LOGINS")
@Entity
public class UserLoginEntity {
    private Long userId;

    @Column(name = "USER_ID")
    @Id
    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    private String userLogin;

    @javax.persistence.Column(name = "USER_LOGIN")
    @Basic
    public String getUserLogin() {
        return userLogin;
    }

    public void setUserLogin(String userLogin) {
        this.userLogin = userLogin;
    }

    private String upper;

    @Formula(value = "upper(USER_LOGIN)")
    public String getUpper() {
        return upper;
    }

    public void setUpper(String upper) {
        this.upper = upper;
    }

Than you can write next query:

Code:

session.createQuery("from UserLoginEntity l where l.upper like :upperLike").setString("upperLike", "TEST").list();

Top

YoannCH

Post subject: Re: How to register and use a MySQL function programmatically

Posted: Tue May 31, 2011 11:58 am

Newbie

Joined: Tue May 31, 2011 10:53 am
Posts: 3

Thanks for your answer J0k3r, but I don't want a formula to be executed on a property/method of one of my objects, I want to execute a MySQL stored function during a query, which means call a non-standard MySQL function during a HQL query as I showed before :

Code:

Query q = em.createQuery("select id from MyObject where MyFunction(firstparam, seconparam) <= condition");
List<?> l = q.getResultList();

Here the "MyFunction" isn't a standard MySQL function, but one I defined myself on my MySQL server and does some calculation that I can't do in my application.

Top

nordborg

Post subject: Re: How to register and use a MySQL function programmatically

Posted: Tue May 31, 2011 12:27 pm

Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden

There are at least two possibilities.

1) Create a custom dialect as a subclass to one of the MySQL dialects. In the constructor you can call registerFunction(). Eg.

Code:

public class MyCustomDialect extends MySQLDialect {

  public MyCustomDialect () {
    super();
    registerFunction("MyFunction", ....); // Second argument is an implementation of SQLFunction interface
  }
}

2) Get hold of your Configuration object when your app starts. How to do that depends on how you initialize Hibernate. Call Configuration.addSqlFunction() with the same parameters as above.

I have never tried 1 but I know 2 works. Check out the org.hibernate.dialect.function.SQLFunctionTemplate class for a nice implementation of the SQLFunction interface.

Top

YoannCH

Post subject: Re: How to register and use a MySQL function programmatically

Posted: Tue May 31, 2011 12:30 pm

Newbie

Joined: Tue May 31, 2011 10:53 am
Posts: 3

Thank you for your answer nordborg, I'll try the 2nd way first as I'm doing my configuration programmatically and as subclassing the dialect seems quite "too much". I'll keep you in touch if this works as expected for me.

:)

Top

Page 1 of 1

[ 5 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