将PreparedStatement插入数据库-PSQL [英] Inserting preparedstatement to database - PSQL

问题描述

这似乎是一个非常简单的问题，但是我无法弄清楚我的问题是什么.我有一个方法addTask，它将一些信息添加到我们的数据库中，如以下代码所示:

This seems like a really simple problem, but I cannot figure out what my problem is. I have a method addTask which adds some info to our database as seen in this code:

public static boolean addTask(String name, String question, int accuracy, int type){
    StringBuilder sql = new StringBuilder();
      sql.append("INSERT INTO tasks (name, question, type, accuracy) ");
      sql.append("VALUES(?, ?, ?, ?)");
      try {
        Connection c = DbAdaptor.connect();
        PreparedStatement preparedStatement = c.prepareStatement(sql.toString());
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, question);
        preparedStatement.setInt(3, type);
        preparedStatement.setInt(4, accuracy);
        preparedStatement.execute();
        preparedStatement.close();
        c.close();
            return true;
      }       
      catch (SQLException e) {
          e.printStackTrace();
          return false;
      }
}

我的问题是prepareStatement.execute()总是返回false，表示该信息尚未添加到数据库中.我可以运行psql，这确认没有任何内容写入数据库.该连接肯定会连接到正确的数据库(我将其放置在其他一些printlns中进行检查).我正在尝试插入一个新初始化的表，如下所示:

my problem is that preparedStatement.execute() always returns false, indicating the information hasnt been added to the database. I can run psql and this confirms that nothing has been written to the db. The connection definitely connects to the correct database (i put in some other printlns etc. to check this). I am trying to insert into a newly initialised table that looks like this:

CREATE TABLE tasks
(
  id SERIAL PRIMARY KEY,
  submitter INTEGER REFERENCES accounts (id),
  name VARCHAR(100) NOT NULL,
  question VARCHAR(100) NOT NULL,
  accuracy INTEGER NOT NULL,
  type INTEGER REFERENCES types (id),
  ex_time TIMESTAMP,
  date_created TIMESTAMP
); 

DbAdaptor.connect()的代码:

code for DbAdaptor.connect():

public static Connection connect(){
    try {
        Class.forName("org.postgresql.Driver");
    } catch (ClassNotFoundException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }
    Properties properties = new Properties();
      properties.setProperty("user", USER);
      properties.setProperty("password", PASSWORD);
    try {
        return DriverManager.getConnection(URL, properties);
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return null;
}

其中USER和PASSWORD是类

推荐答案

您误解了请仔细阅读 javadoc :

返回:

true，如果第一个结果是ResultSet对象； false如果第一个结果是更新计数或没有结果.

Returns:

true if the first result is a ResultSet object; false if the first result is an update count or there is no result.

因此它返回—.完全符合预期— false查询中的false.它仅对SELECT查询返回true(但是您通常希望使用

It thus returns — as fully expected — false on an INSERT query. It returns only true on a SELECT query (for which you'd however usually like to use executeQuery() instead which returns directly a ResultSet).

如果您对受影响的行感兴趣，请使用

If you're interested in the affected rows, rather use PreparedStatement#executeUpdate() instead. It returns an int as per the javadoc:

返回:

要么(1)SQL数据操作语言(DML)语句的行计数，要么(2)0什么都不返回的SQL语句

Returns:

either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

返回值1或更大将表示插入成功.

A return value of 1 or greater would then indicate a successful insert.

不相关与具体问题:您的代码正在泄漏数据库资源.请仔细阅读 Connection，Statement和在JDBC中关闭ResultSet吗?

Unrelated to the concrete problem: your code is leaking DB resources. Please carefully read How often should Connection, Statement and ResultSet be closed in JDBC?

这篇关于将PreparedStatement插入数据库-PSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！