Java 如何从 JDBC 截断 Postgresql 的表

How to truncate a Postgresql's table from JDBC

提问by Olivier Grégtheitroade

I have a Postgresql database and I want to truncate some tables using JDBC. How do I do that?

我有一个 Postgresql 数据库，我想使用 JDBC 截断一些表。我怎么做？

This is what I tried, but none worked... without even any error being reported:

这是我尝试过的，但没有成功......甚至没有报告任何错误：

Using CallableStatement.

使用CallableStatement.

try (Connection connection = getConnection();
     CallableStatement statement = connection.prepareCall("TRUNCATE " + tableName)) {
  return statement.execute();
}

Using Statement.

使用Statement.

try (Connection connection = getConnection();
     Statement statement  = connection.createStatement()) {
  return statement.execute("TRUNCATE " + tableName);
}

Using PreparedStatement.

使用PreparedStatement.

try (Connection connection = getConnection();
     PreparedStatement statement = connection.prepareStatement("TRUNCATE " + tableName)) {
  return statement.execute();
}

采纳答案by Olivier Grégtheitroade

After the truncate, I need to commit:

截断后，我需要提交：

try (Connection connection = getConnection();
     Statement statement = connection.createStatement()) {
  int result = statement.executeUpdate("TRUNCATE " + tableName);
  connection.commit();
  return result;
}

From the documentation:

从文档：

TRUNCATE is transaction-safe with respect to the data in the tables: the truncation will be safely rolled back if the surrounding transaction does not commit.

TRUNCATE 对于表中的数据是事务安全的：如果周围的事务没有提交，截断将安全回滚。

回答by John Hogan

You may run into issues if the table has dependencies. If so, truncate the parent tables first, and also use the CASCADE option.

如果表有依赖关系，您可能会遇到问题。如果是这样，请先截断父表，并使用 CASCADE 选项。

Connection connection = getConnection();
try {
    PreparedStatement statement  = connection.prepareStatement("TRUNCATE " + parentTable1, parentTable2, ... + " CASCADE");
    try {
        return statement.execute();
    } finally {
        statement.close();
    }
} finally {
    connection.close();
}

回答by Tihamer

First, if you are truncating a table, you probably want to also RESTART IDENTITY (in addition to possibly doing CASCADE, as John Hogan mentioned).

首先，如果您要截断一个表，您可能还想重新启动 IDENTITY（除了可能执行 CASCADE，正如 John Hogan 提到的那样）。

Second, as far as doing a connection.commit(), the assumption is that you have autocommit set to OFF. My Postgres was set up with it set to ON (apparently, that is sometimes the default). If it is set to ON, then calling the commit is unnecessary, and will result in the error: "org.postgresql.util.PSQLException: Cannot commit when autoCommit is enabled."

其次，就执行 connection.commit() 而言，假设您已将自动提交设置为 OFF。我的 Postgres 设置为 ON（显然，有时这是默认设置）。如果设置为ON，则不需要调用提交，并会导致错误：“org.postgresql.util.PSQLException：启用自动提交时无法提交。”

Third, you may not have permission to truncate a table (or restart identity). In that case, you will need to:

第三，您可能没有截断表（或重新启动身份）的权限。在这种情况下，您需要：

DELETE from your_table
SELECT setval('your_table_id', 1)

The following worked for me:

以下对我有用：

public String truncateTable(String tableName, boolean cascadeFlag) {
    String message = "";
    try {
        connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();
        String truncation = "TRUNCATE TABLE yourSchema." + tableName + " RESTART IDENTITY" + (cascadeFlag ? " CASCADE" : "");
        System.out.println("truncateTable: Executing query '" + truncation + "'.");
        int result = statement.executeUpdate(truncation);
        // connection.commit();  // If autocommit is enabled (which it is for our DB), then throws exception after truncating the table.
        statement.close();
        connection.close();
    } catch (SQLException sqlex) {
        message = "Could not truncate table " + tableName + ". " + sqlex.getMessage();
        System.err.println(message);
        sqlex.printStackTrace();
    }
    return message;
}

Also:

还：

public int deleteResetTable(String tableName, String fieldName) {
        int affectedRows = 0;
        try {
            connection = DriverManager.getConnection(url, username, password);
            String sql = "DELETE FROM yourSchema." + tableName;
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            affectedRows = preparedStatement.executeUpdate();
            System.out.println("Deleted " + affectedRows+ " rows from table " + tableName + ".");
            sql = "SELECT setval('yourSchema." + fieldName + "', 1)";
            preparedStatement = connection.prepareStatement(sql);
            affectedRows = preparedStatement.executeUpdate();
            System.out.println("Reset " + affectedRows+ " values from table " + tableName + ".");
        } catch (SQLException ex) {
            System.out.println("Failed to delete rows from " + tableName + " " + ex.getMessage());
        }
        return affectedRows;
}