0

I wanna use "truncate table" statement instead of "delete" statement in spring boot project cause I need reset the auto increment id in mysql. Here is my code:

@PersistenceContext protected EntityManager em;
@Override
  public void removeAllShopeeCategory() {
    StringBuilder query = new StringBuilder("truncate table ShopeeCategoryDto shopeecategory");
    Query q = this.em.createQuery(query.toString());
    q.executeUpdate();
  }

but there is an exception like this:

nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: truncate near line 1, column 1

other operation has worked, such as insert, update or select, what's the reason and what should I modify it?

Frank
  • 977
  • 3
  • 14
  • 35
  • Your query is a **SQL** query. `createQuery()` expected a **JPQL** query. JPQL and SQL are two different languages. Like C++ and Java. You can't compile a Java program with a C++ compiler. Same here. If you want to execute SQL queries, don't use createQuery(). Use https://docs.oracle.com/javaee/7/api/javax/persistence/EntityManager.html#createNativeQuery-java.lang.String- – JB Nizet Oct 10 '19 at 06:29
  • what do you mean by that? I used createQuery() for update, select methods, it works – Frank Oct 10 '19 at 06:31
  • TRUNCATE is not part of JPQL, you need to use a native query, not a JPQL query –  Oct 10 '19 at 06:34
  • understood, let me try native query, thanks – Frank Oct 10 '19 at 06:36

2 Answers2

1

Please use https://docs.oracle.com/javaee/7/api/javax/persistence/EntityManager.html#createNativeQuery-java.lang.String- with native sql queries.

Rajeev Akotkar
  • 1,377
  • 4
  • 26
  • 46
0
 entityManager.createNativeQuery("TRUNCATE TABLE " + tableName + " CASCADE")
            .executeUpdate()
Alexander Golub
  • 51
  • 5
  • Note this will open you up to SQL injection. *Never* concatenate string to make a SQL query; use parameter binding instead, e.g. [here](https://stackoverflow.com/a/28829942/1389817) – Stewart Adam Apr 21 '22 at 21:18