Using JPA to call native Postgresql command

jpa native query example with parameters
spring boot jpa native query example
how to call postgresql function in jpa
spring data jpa native query mapping
jpa repository native query example with parameters
jpa native query without entity
spring boot native query without entity
jpa native query in clause

I am using a third party library to perform mass inserts into a database PgBulkInsert . It takes inserts that would normally take 30 minutes and performs them in 30 seconds. We have noticed that overtime there is disk usage leakage, but we figure out that performing a table reindex appears to corrcect the issue. I am trying to use my JPA Entity Manager to perform a native update. The below code works but contains potential SQL injection vulnerability.

@Stateless
public class ReindexService {
  @PersistenceContext(unitName = "my-ds")
  private EntityManager em;

  public void reindexTable(String table) {
    String queryStr = "REINDEX TABLE " + table;
    Query query = em.createNativeQuery(queryStr);
    query.executeUpdate();
  }
}

When I pass in string "alert" to index the alert table it yields the following SQL output

/* dynamic native SQL query */ REINDEX TABLE alert

When I attempt to use a positional parameter it yields a SQL error

String queryStr = "REINDEX TABLE ?";
Query query = em.createNativeQuery(queryStr);
query.setParameter(1, table);
query.executeUpdate();

This yields the following error output

/* dynamic native SQL query */ REINDEX TABLE ?
SQL Error: 0, SQLState: 42601
ERROR: syntax error at or near "$1"
Position: 46

I get a similar error when I try to use a name parameter

String queryStr = "REINDEX TABLE :table";
Query query = em.createNativeQuery(queryStr);
query.setParameter("table", table);
query.executeUpdate();

This yields the same error

/* dynamic native SQL query */ REINDEX TABLE ?
SQL Error: 0, SQLState: 42601
ERROR: syntax error at or near "$1"
Position: 46

Does anyone know how I can call a the native Postgresql reindex table command using my entity manager in a manner without adding a SQL injection vulnerability? I am using Hibernate 5.3.6.Final but would prefer a non-implementation specific solution.

I also tried to access the Connection and perform a JDBC call and it seems to give the error

final Session session = //get session from entity manager
session.doWork(conn -> {
  try (PreparedStatement stmt = conn.prepareCall(REINDEX TABLE ?)) {
    stmt.setString(1, table);
    stmt.execute();
  }
});

Yields the same errors as above

SQL Error: 0, SQLState: 42601
ERROR: syntax error at or near "$1"
Position: 15

Identifiers can't be passed as parameters. If you don't expect the table name to come from user input (it sounds a bit strange), you may try using an enumeration of all the tables which you want to reindex, and pass enumeration values to your service (and just concatenate strings).

If you do expect table names to come from untrusted sources, you can try enclosing identifier with double-quotes and escaping existing double-quotes.

There is also a function quote_ident in PostgreSQL which can be used to quote identifiers properly. So you can create a stored procedure which takes a regular argument from your JPA code and uses quote_ident and EXECUTE a constructed query .

Native Queries, The Java Persistence Query Language (JPQL) is the most common way to query data from a database with JPA. But it supports only a small subset of the SQL� Native Queries: How to Call Native SQL Queries With JPA JPA has its own query language and supports native SQL. You can create queries in a very similar way as JPQL queries and they can even


This is something weird ,But Just try, table is a reserved keyword in PostgreSQL. So try changing the variable name.

String queryStr = "REINDEX TABLE :tableName";
Query query = em.createNativeQuery(queryStr);
query.setParameter("tableName", "AUTH_IND");
query.executeUpdate();

From the Documentation :

"select" could be used to refer to a column or table named "select", whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected.

https://www.postgresql.org/docs/current/sql-syntax-lexical.html

Hibernate Tips: How to call a PostgreSQL function, In this Hibernate Tip, I show you 3 different ways to call standard and custom PostgreSQL As I explained in my post about using a PostgreSQL database with Hibernate, there It was introduced in JPA 2.1 and gives you a generic way to define a function call. Java 8 Support in Hibernate 5; Native Queries with Hibernate. You also learned that JPQL is not the only option when it comes to creating queries over JPA entities—in some situations using native SQL queries is more convenient. Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, databases, and SOA.


Our workaround was to create a Database Function and call it using a native query

The database function

CREATE OR REPLACE FUNCTION reindex_table(table_in text)
RETURNS void
SECURITY DEFINER
AS $$
BEGIN
  EXECUTE FORMAT('REINDEX (VERBOSE) TABLE %I', table_in);
RETURN;
END;
$$  LANGUAGE plpgsql;

Here is the Service code for calling the function

public void reindexTable(String table) {
  String queryStr = "select reindex_table(?)";
  final Session session = //obtain Hibernate Session from Entitymanager
  session.doWork(conn -> {
    try (PreparedStatement stmt = conn.prepareCall(queryStr)) {
      stmt.setString(1, table);
      stmt.execute();
    }
  });
}

Native Queries: How to Call Native SQL Queries With JPA, The Java Persistence Query Language (JPQL) is the most common way to query data from a database with JPA. But it supports only a small� Finally, we’ll write the REST APIs and test those APIs using Postman. Cool, Let’s get started! Bootstrapping the Project. You can bootstrap the Spring Boot project using Spring CLI tool by typing the following command in the terminal. $ spring init --name=postgres-demo --dependencies=web,jpa,postgresql postgres-demo


How to call PostgreSQL functions (stored procedures) with JPA and , This article is part of a series of posts related to calling various relational database systems stored procedures and database functions from� How to call PostgreSQL functions (stored procedures) with JPA and Hibernate. Using JPA and Hibernate for writing data is very convenient, especially for typical OLTP operations. However, processing large volumes of data is much more practical to be done in the database.


Querying JPA Entities with JPQL and Native SQL, Both are used to access and manipulate database data, in the long run. And both use nonprocedural statements—commands recognized by a special interpreter. Spring Boot uses Hibernate for JPA implementation, we configure MySQL5InnoDBDialect for MySQL or PostgreSQLDialect for PostgreSQL spring.jpa.hibernate.ddl-auto is used for database initialization. We set the value to update value so that a table will be created in the database automatically corresponding to defined data model.


indrabasak/jpa-postgres-advanced: Advanced JPA , JPA example with Postgres - interaction with stored procedure, native query, You have a PostgreSQL database server running on your localhost and in port� My question was - what is the MINIMAL configuration for using the JPA features together with postgresql ? > > The rest are JPA questions, and would better be served by a Java/JPA > list, I suspect. > if there would exist a postgresql jdbc4 driver that supports the features you need for the JPA extensions, i wouldnt have asked here.