Home JPA (hibernate) perform MySQL Fulltext select
Reply: 0

JPA (hibernate) perform MySQL Fulltext select

user3567
1#
user3567 Published in June 23, 2018, 4:34 am

[What I need]

MySQL Fulltext search funtionality like the following example:

CREATE TABLE books (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
isbn VARCHAR(50),
year INT,
FULLTEXT (title)
);

INSERT INTO books (title,isbn,year) VALUES
('MySQL Tricks','1223', 2000),
('How To Use MySQL Well','234g2', 2010),
('Optimizing MySQL','125632', 2012),
('1001 MySQL Tricks','12312', 2015);

SELECT * FROM books
WHERE MATCH(title) AGAINST('tricks' IN NATURAL LANGUAGE MODE);

RESULT:

MySQL Tricks  1223  2000

1001 MySQL Tricks  12312  2015

[What I have]

Entity:

@Entity
@Table(name = "books")
public class Book {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;

  private String title;
  private String isbn;
  private int year;


  public int getId() {
      return id;
  }
  public void setId(int id) {
      this.id = id;
  }
  public String getTitle() {
      return title;
  }
  public void setTitle(String title) {
      this.title = title;
  }
  public String getIsbn() {
      return isbn;
  }
  public void setIsbn(String isbn) {
      this.isbn = isbn;
  }
  public int getYear() {
      return year;
  }
  public void setYear(int year) {
      this.year = year;
  }
}

Handler:

@Singleton
public class BooksHandler {

@PersistenceContext
private EntityManager em;

public Book createBook(String title, String isbn, int year) {
    Book book = new Book();
    book.setTitle(title);
    book.setIsbn(isbn);book.setYear(year);
    em.persist(book);
    return book;
}

public List<Book> find(String titel){
    //replace with fulltext search
    String queryStr = "SELECT b FROM Book b WHERE b.title LIKE :title";
    Query q = em.createQuery(queryStr, Book.class);
    q.setParameter("title", "%"+titel+"%");
    return q.getResultList();
}
}

How can I change what I have to achieve what I need ?

I want to perform MySQL fulltext search, from JPA (hibernate). I already know, this can be achieved using NativeQuery. But how do I need to change my Entity declaration, so that 'title' is recognized as FULLETXT.

Thanks in advance

EDIT

After using hibernate, my Entity ('books') is generated in the database with the Entity class fields as columns (id, title, isbn, year). Title column is a varchar(255).

I manually exceuted following sql query in database:

ALTER TABLE books ADD FULLTEXT(title);

to make 'title' column FULLTEXT.

Then changed BooksHandler method find():

public List<Book> find(String titel){
    String queryStr = "SELECT * FROM books WHERE MATCH(title) AGAINST('"+titel+"' IN NATURAL LANGUAGE MODE);";
    Query nq = em.createNativeQuery(queryStr, Book.class);
    return nq.getResultList();
}

This solution works. But it is not optimal, because I had to alter 'title' by hand in the database.

2. EDIT

What I did based on the answer(vladwoguer) below:

added to persistence.xml:

<property name="hibernate.hbm2ddl.import_files" value="update.sql"/>

and made sure this line is set to 'create' (will not work with 'update'):

<property name="hibernate.hbm2ddl.auto" value="create"/>

And created 'update.sql' like mentioned below.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.322889 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO