How get existing tables with native sql

Question

First of all, apologize for the grammatical errors that you can make. My English is not very good.

I've created a dynamic table generator. I use that table generator to create dynamically tables with different JSON properties.

I create tables perfectly, but I want to check before to create it if already exist.

I use sql sentence that works on pgAdmin but, when I use that sentence on my method doesn't work.

I'm using sprong boot (JPA + hibernate) and Postgresql.

It returns next error:

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: information_schema.tables is not mapped

How can get all the tables that already exist??

Here is my code:

@Service
@Transactional
public class TableGeoJsonGenerator {

    private static final Logger LOGGER = LogManager.getLogger(SchemeService.class);

    @Autowired
    private EntityManager em;

    public String initTable(String name, List<String> columns, List<String> typeColumns, ArrayList<Object> rows) {
        String nameTableFinal = "";
        if (!chekifExistTable(name)) {
            nameTableFinal = name;
            createTableWithColumns(name, columns, typeColumns);
        } else {
            UUID uuid = UUID.randomUUID();
            nameTableFinal = name + "-" + uuid.toString();
            createTableWithColumns(name, columns, typeColumns);
        }
        return nameTableFinal;
    }

    private void createTableWithColumns(String name, List<String> columns, List<String> typeColumns) {
        String SQL = "CREATE TABLE IF NOT EXISTS " + name + "( name_id varchar(255) ,table_id SERIAL, " + generateSqlColumns(columns, typeColumns) +" primary key (name_id));";
        executeSQL(SQL);
    }

    private String generateSqlColumns(List<String> columns, List<String> typeColumns) {
        String DINAMIC_COLUMNS = "";
        for (int i=0; i<columns.size(); i++) {
            DINAMIC_COLUMNS += columns.get(i).toString() + " " + getPostgresqlValue(typeColumns.get(i).toString()) + ", ";
        }
        return DINAMIC_COLUMNS;
    }

    private void executeSQL(String SQL) {
        try {
            Query query = em.createNativeQuery(SQL);
            query.executeUpdate();              
        } catch(Throwable e) {
            LOGGER.error(e.getMessage());
            throw e;
        } finally {
            em.close();
        }
    }

    private boolean executeSQLWithResults(String SQL, String nameTable) {
        boolean found = false;
        try {
            TypedQuery<?> lQuery = (TypedQuery<?>) em.createQuery(SQL);
            List<?> list = lQuery.getResultList();
            for (Object result : list) {
                LOGGER.debug(result.toString());
            }
            found = true;
        } catch(Throwable e) {
            LOGGER.error(e.getMessage());
            throw e;
        } finally {
            em.close();
        }
        return found;
    }

    private boolean chekifExistTable(String name) {
        String SQL = "select table_name from information_schema.tables where table_schema = 'public'";
        return executeSQLWithResults(SQL, name);
    }

    private String getPostgresqlValue(String value) {
        if (value.equals(Integer.class.toString())) {
            return "int8";
        } 
        else if (value.equals(Double.class.toString())) {
            return "float8";
        }
        else if (value.equals(String.class.toString())) {
            return "varchar(255)";
        }
        else if (value.equals(Long.class.toString())) {
            return "bigint";
        }
        else if (value.equals(Boolean.class.toString())) {
            return "boolean";
        }
        else if (value.equals(Date.class.toString())) {
            return "timestamp";
        }
        else {
            return "numeric";
        }       
    }

}

SOLUTION SOLVED BY Amer Qarabsa:

private boolean executeSQLWithResults(String SQL, String nameTable) {
        boolean found = false;
        try {
            Query query = em.createNativeQuery(SQL);
            List<?> list = query.getResultList();
            for (Object result : list) {
                LOGGER.debug(result.toString());
            }
            found = true;
        } catch(Throwable e) {
            LOGGER.error(e.getMessage());
            throw e;
        } finally {
            em.close();
        }
        return found;
    }

have you tried this stackoverflow.com/a/19632367/2065796 ?

Sasha Shpota
– Sasha Shpota

2019-07-18 08:19:13 +00:00
Commented Jul 18, 2019 at 8:19 — Sasha Shpota
– Sasha Shpota, Commented Jul 18, 2019 at 8:19

Amer Qarabsa · Accepted Answer · 2019-07-18 08:22:28Z

2

Hibernate complains because the object you are trying to access information_schema.tables is not mapped to an entity.

The problem is in this method

 private boolean chekifExistTable(String name) {
        String SQL = "select table_name from information_schema.tables where table_schema = 'public'";
        return executeSQLWithResults(SQL, name);
    }

in executeSQLWithResults you are calling em.createQuery , createQuery expects JQL not SQL so this is why hibernate tries to map it to an entity, check the link , instead use em.createNativeQuery the same in your other method executeSQL

answered Jul 18, 2019 at 8:22

Amer Qarabsa

6,6343 gold badges25 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How get existing tables with native sql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related