Java execute() vs executeUpdate() to refresh materialized view in Postgresql

user_10320673 0
Viewed 1488

I was trying to refresh materialized views in Postgresql from my Java program:

conn = AbstractClientDao.getJdbcConnection();
pst = conn.prepareStatement("REFRESH MATERIALIZED VIEW mv_my_materialized_view;");
pst.execute();
conn.close();

As per the documentation, the execute() method can be used on any kind of SQL statement:

Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

Note: I don't need to know about the result in this part of the program, so the return value is not important.

This code is not doing anything in the database, the materialized view is not updated but no error/exception is thrown in the java program. After looking around a bit I went with the same code but using executeUpdate():

conn = AbstractClientDao.getJdbcConnection();
pst = conn.prepareStatement("REFRESH MATERIALIZED VIEW mv_my_materialized_view;");
pst.executeUpdate();
conn.close();

From the documentation, the executeUpdate() also work on any kind of SQL:

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

This code is working and correctly updates the view.

Am I missing something or the documentation is not clear enough about the differences betweend execute() and executeUpdate() ?

Note: Java8, Postgresql 9.6, Driver org.postgresql, version 9.3-1101-jdbc41

jdbc postgresql java
2 Answers

Answer: There is no difference between the 2 versions of this code. My problem was due to race conditions in my database.

user_103206730

Had similar issue. Adding WITH DATA at the end of materialized view creation script was my problem.

PostgreSQL

CREATE MATERIALIZED VIEW my_mat_view
AS
SELECT * FROM my_table
WITH DATA
;

JAVA

entityManager
.createNativeQuery("REFRESH MATERIALIZED VIEW my_mat_view;")
.executeUpdate();
user_67965240
Related