Check if table exist in spring boot with JPA+Hibernate

Question

I am trying to create a custom repository in spring boot using JPA + Hibernate. I am new to JPA and Hibernate, so till now I managed to create a table in the DB and push some data to it.

I would like to know if a table exist before creating it or number of rows in a table but I am not able to find any material on it.

Following is the class to create table

package repository;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

import org.springframework.stereotype.Repository;

@Repository
public class CustomRepository {

    EntityManagerFactory entityManagerFactory = Persistence.createEntityManagerFactory("TestPersistence");

    public void createTable(String tableName) {
        EntityTransaction txn = null;
        try {
            EntityManager entityManager = entityManagerFactory.createEntityManager();
            txn = entityManager.getTransaction();
            txn.begin();

            entityManager.createNativeQuery("CREATE TABLE " + tableName + " (PersonID int, LastName varchar(255))")
                    .executeUpdate();
            txn.commit();
        } catch (Throwable e) {
            if (txn != null && txn.isActive()) {
                txn.rollback();
            }
            throw e;
        }
    }

}

I tried

String sqlQuery = "SELECT COUNT (*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'my_table'";

or

String sqlQuery = "SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES"

With entityManager.createQuery(sqlQuery).getResultList(); as well but they are giving error.

Any suggestion on how to achieve this would be helpful.

Hibernate is not a great tool for this task. If you are looking for schema management, take a look at Flyway. — chrylis -cautiouslyoptimistic-, Jul 07 '21 at 17:51
another option for schema management is [Liquibase](https://www.liquibase.org/) — Tommy Brettschneider, Jul 07 '21 at 19:27
The two queries you describe in the `sqlQuery` string are native, so you have to call `createNativeQuery` instead of `createQuery`. — coladict, Jul 08 '21 at 08:14

score 0 · Answer 1 · answered Jul 07 '21 at 17:54

You can achive this by hibernate hbm2ddl function. You can set it to validate and on startup hibernate will check if the schema generated matches one in database. What are the possible values of the Hibernate hbm2ddl.auto configuration and what do they do If you want to check how many rows are in given table you would need to perform count in select statemant like SELECT COUNT(*) FROM TableName. Something like here SQL count rows in a table

Check if table exist in spring boot with JPA+Hibernate

1 Answers1