时间戳

首页 > 解决方案 > 无法使用 Java 对我的 PostgreSQL 数据库进行提交

java - 无法使用 Java 对我的 PostgreSQL 数据库进行提交

问题描述

我对 PostgreSQL 和 Java 有疑问。我无法对我的数据库进行提交。这是我编写的方法的源代码：

@Override
    public void addToTable(T t, int i) {
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection(connectionUrl, connectionUser, connectionPwd);
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            stmt = c.createStatement();
            int id = this.getId(t.getName());
            String sql = "INSERT INTO app.my_table_name VALUES (\'" +
                    i + "\', \'" + id + "\');";
            stmt.executeUpdate(sql);

            stmt.close();
            c.commit();
            c.close();
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage());
            for (StackTraceElement error : e.getStackTrace()) {
                System.out.println(error);
            }
            System.exit(0);
        }
        System.out.println("Row inserted successfully");
    }

我只得到了控制台的反馈：

org.postgresql.util.PSQLException: This connection has been closed.

所以我没有得到这么多的信息，所以我试着找出堆栈跟踪，但这也没有给我太多：

org.postgresql.util.PSQLException: This connection has been closed.
org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:865)
org.postgresql.jdbc.PgConnection.commit(PgConnection.java:851)
main.PostgreSQL.addToTable(PostgreSQL.java:87)
main.scene.Table.lambda$setInitialData$1(Table.java:90)
javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
javafx.base/javafx.event.Event.fireEvent(Event.java:198)
javafx.graphics/javafx.scene.Node.fireEvent(Node.java:8879)
javafx.controls/javafx.scene.control.Button.fire(Button.java:200)
javafx.controls/com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:206)
javafx.controls/com.sun.javafx.scene.control.inputmap.InputMap.handle(InputMap.java:274)
javafx.base/com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
javafx.base/javafx.event.Event.fireEvent(Event.java:198)
javafx.graphics/javafx.scene.Scene$MouseHandler.process(Scene.java:3851)
javafx.graphics/javafx.scene.Scene$MouseHandler.access$1200(Scene.java:3579)
javafx.graphics/javafx.scene.Scene.processMouseEvent(Scene.java:1849)
javafx.graphics/javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2588)
javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:397)
javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
java.base/java.security.AccessController.doPrivileged(Native Method)
javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$2(GlassViewEventHandler.java:434)
javafx.graphics/com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:390)
javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:433)
javafx.graphics/com.sun.glass.ui.View.handleMouseEvent(View.java:556)
javafx.graphics/com.sun.glass.ui.View.notifyMouse(View.java:942)
javafx.graphics/com.sun.glass.ui.gtk.GtkApplication._runLoop(Native Method)
javafx.graphics/com.sun.glass.ui.gtk.GtkApplication.lambda$runLoop$11(GtkApplication.java:277)
java.base/java.lang.Thread.run(Thread.java:834)

堆栈跟踪指向问题所在的这一行：

c.commit();

为什么会出现这个问题？使用几乎相同的代码对我的数据库执行插入操作时，我没有其他问题。我已经尝试获取输出，int id = this.getId(t.getName());它给出了所需的输出。与int ias 参数相同。

编辑：

我完整的类文件：

package main;

import main.model.Drink;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

public class PostgreSQL implements IDatabase {
    private Connection c;
    private Statement stmt;
    private final String connectionUrl = "jdbc:postgresql://localhost:5432/db";
    private final String connectionUser = "postgres";
    private final String connectionPwd = "test1234";

    public PostgreSQL() {
        c = null;
        stmt = null;
    }

    @Override
    public void addTable(String tableName) {
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection(connectionUrl, connectionUser, connectionPwd);
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            stmt = c.createStatement();
            String sql = "INSERT INTO app.tables VALUES (DEFAULT, \'" + tableName + "\');";
            stmt.executeUpdate(sql);

            c.commit();

            stmt.close();
            c.close();
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
            System.exit(0);
        }
        System.out.println("Row inserted successfully");
    }

    @Override
    public void addDrink(Double price, String name, String category) {
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection(connectionUrl, connectionUser, connectionPwd);
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            stmt = c.createStatement();
            String sql = "INSERT INTO app.drinks VALUES (DEFAULT, \'" +
                    price + "\', \'" + name + "\', \'" + category + "\');";
            stmt.executeUpdate(sql);

            c.commit();

            stmt.close();
            c.close();
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
            System.exit(0);
        }
        System.out.println("Row inserted successfully");
    }

    @Override
    public void addDrinkToTable(Drink drink, int tableId) {
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection(connectionUrl, connectionUser, connectionPwd);
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            stmt = c.createStatement();
            int drinkId = this.getDrinkId(drink.getName());
            String sql = "INSERT INTO app.tables_drinks VALUES (\'" +
                    tableId + "\', \'" + drinkId + "\');";
            stmt.executeUpdate(sql);

            c.commit();
            stmt.close();
            c.close();
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage());
            for (StackTraceElement error : e.getStackTrace()) {
                System.out.println(error);
            }
            System.exit(0);
        }
        System.out.println("Row inserted successfully");
    }

    @Override
    public void addReceipt(String server, String date, int tableId) {
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection(connectionUrl, connectionUser, connectionPwd);
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            stmt = c.createStatement();

            // Creates receipt
            String sql = "INSERT INTO app.receipts VALUES (DEFAULT, " +
                    "\'" + server + "\', \'" + date + "\');";
            stmt.executeUpdate(sql);

            // Gets the newly created receipt id
            Statement stmtQuery = c.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY
            );

            String sql2 = "SELECT * FROM app.receipts ORDER BY id DESC LIMIT 1";
            ResultSet rs = stmtQuery.executeQuery(sql2);
            rs.last();
            int receiptId = rs.getInt("id");

            // Creates row in receipts_tables with receipt id and table id
            String sql3 = "INSERT INTO app.receipts_tables VALUES (" + receiptId + ", " + tableId + ")";
            stmt.executeUpdate(sql3);

            c.commit();

            stmtQuery.close();
            stmt.close();
            c.close();
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
            System.exit(0);
        }
        System.out.println("Row inserted successfully");
    }

    @Override
    public LinkedHashMap<Integer, String> getTables() {
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection(connectionUrl, connectionUser, connectionPwd);
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            // Gets the newly created receipt id
            Statement stmtQuery = c.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY
            );

            LinkedHashMap<Integer, String> allTables = new LinkedHashMap<>();
            String sql = "SELECT * FROM app.tables;";
            ResultSet rs = stmtQuery.executeQuery(sql);
            while (rs.next()) {
                allTables.put(rs.getInt("id"), rs.getString("table_name"));
            }

            c.commit();

            stmtQuery.close();
            c.close();

            return allTables;
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
            System.exit(0);
        }
        System.out.println("Row inserted successfully");

        return null;
    }

    @Override
    public LinkedHashMap<String, List<Drink>> getDrinks() {
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection(connectionUrl, connectionUser, connectionPwd);
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            // Gets the newly created receipt id
            Statement stmtQuery = c.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY
            );

            LinkedHashMap<String, List<Drink>> allDrinks = new LinkedHashMap<>();
            String sql = "SELECT * FROM app.drinks;";
            ResultSet rs = stmtQuery.executeQuery(sql);
            List<Drink> drinkList = new ArrayList<>();
            while (rs.next()) {
                Drink d = new Drink(rs.getString("drink_name"),
                        rs.getString("drink_category"),
                        rs.getDouble("drink_price")
                );
                drinkList.add(d);
            }

            allDrinks.put("allDrinks", drinkList);

            c.commit();

            stmtQuery.close();
            c.close();

            return allDrinks;
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
            System.exit(0);
        }
        System.out.println("Row inserted successfully");

        return null;
    }

    @Override
    public LinkedHashMap<String, List<Drink>> getTableDrinks(int tableId) {
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection(connectionUrl, connectionUser, connectionPwd);
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            // Gets the newly created receipt id
            Statement stmtQuery = c.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY
            );

            LinkedHashMap<String, List<Drink>> allDrinks = new LinkedHashMap<>();
            String sql = "SELECT d.drink_name, d.drink_category, d.drink_price " +
                    "FROM app.drinks d, app.tables_drinks td " +
                    "JOIN app.tables tb ON tb.id = td.table_id " +
                    "WHERE d.id = td.drink_id AND td.table_id = '" + tableId + "';";
            ResultSet rs = stmtQuery.executeQuery(sql);
            List<Drink> drinkList = new ArrayList<>();
            while (rs.next()) {
                Drink d = new Drink(
                        rs.getString("drink_name"),
                        rs.getString("drink_category"),
                        rs.getDouble("drink_price")
                );
                drinkList.add(d);
            }

            allDrinks.put("allDrinks", drinkList);

            c.commit();

            stmtQuery.close();
            c.close();

            return allDrinks;
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
            System.exit(0);
        }
        System.out.println("Row inserted successfully");

        return null;
    }

    @Override
    public int getTableId(String tableName) {
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection(connectionUrl, connectionUser, connectionPwd);
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            // Gets the newly created receipt id
            Statement stmtQuery = c.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY
            );

            int tableId = 0;
            String sql = "SELECT tl.id FROM app.tables tl WHERE tl.table_name = " +
                    "'" + tableName + "'" + "LIMIT 1;";
            ResultSet rs = stmtQuery.executeQuery(sql);
            while (rs.next()) {
                tableId = rs.getInt("id");
            }

            c.commit();

            stmtQuery.close();
            c.close();

            return tableId;
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
            System.exit(0);
        }
        System.out.println("Row inserted successfully");

        return 0;
    }

    @Override
    public int getDrinkId(String drinkName) {
        try {
            Class.forName("org.postgresql.Driver");
            c = DriverManager
                    .getConnection(connectionUrl, connectionUser, connectionPwd);
            c.setAutoCommit(false);
            System.out.println("Opened database successfully");

            // Gets the newly created receipt id
            Statement stmtQuery = c.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY
            );

            int tableId = 0;
            String sql = "SELECT dr.id FROM app.drinks dr WHERE dr.drink_name = " +
                    "'" + drinkName + "'" + "LIMIT 1;";
            ResultSet rs = stmtQuery.executeQuery(sql);
            while (rs.next()) {
                tableId = rs.getInt("id");
            }

            c.commit();

            stmtQuery.close();
            c.close();

            return tableId;
        } catch (Exception e) {
            System.err.println( e.getClass().getName()+": "+ e.getMessage() );
            System.exit(0);
        }
        System.out.println("Row inserted successfully");

        return 0;
    }
}

标签： javapostgresql

解决方案

这实际上取决于您尝试使用该代码的上下文。

如果直接在一个基本的 Java 项目中，也许它可以运行。我没有看很长时间你的代码，所以我不能保证你犯了任何错误。

如果直接在 Web 项目中，也许您必须添加一些数据库配置以维护与您的 postgresql 数据库（或任何其他数据库提供程序，事实上）的池连接。因为 db 连接在您发送 SQL 请求之前释放，所以它不起作用。合乎逻辑。

在某些 Web 框架中，您必须在 web.xml 文件中添加一些 Web 过滤器才能处理该工作。至少在 2007 年以来的 spring-web-mvc 应用程序中，如果我记得很清楚的话。

推荐阅读