使用 JDBC 向 postgresql 插入值

Insert values to postgresql using JDBC

提问by Aldres

I want to create something like task manager, using java. I've decided to use PostgreSQL for saving my tasks. On this moment, I want to save my created tasks to PostgreSQL, for this I have following code:

我想使用 java 创建类似任务管理器的东西。我决定使用 PostgreSQL 来保存我的任务。此时，我想将我创建的任务保存到 PostgreSQL，为此我有以下代码：

package TaskMgr;

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.util.Locale;
public class Task {

private String title;
private String description;
private LocalDate dateFormat;


public Task(String title, String description, String date) {
    this.title = title;
    this.description = description;
    this.dateFormat = setDate(date);
}


public LocalDate setDate(String inputDate) {
    DateTimeFormatter formatter = new DateTimeFormatterBuilder()
            .parseCaseInsensitive()
            .appendPattern("yyyy-MM-d")
            .toFormatter(Locale.ENGLISH);
    formatter = formatter.withLocale(Locale.ENGLISH);
    LocalDate outputDate = LocalDate.parse(inputDate, formatter);
    return outputDate;

}

public String getTitle() {
    return title;
}

public String getDescription() {
    return description;
}


public LocalDate getDateFormat() {
    return dateFormat;
}

public static void main(String[] args) {

}


}

And my code for database: (database with table Tasks has been already created, with fields : title of type text, description of type text and date of type date). Here's code of Database class:

和我的数据库代码：（已经创建了带有表任务的数据库，字段：文本类型的标题，文本类型的描述和日期类型的日期）。这是数据库类的代码：

package TaskMgr;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDate;
import java.time.Month;

public class Database {

private final String url = "jdbc:postgresql://localhost/tasksdb";
private final String user = "postgres";
private final String password = "31415926";

public Connection connect() {
    Connection conn = null;
    try {
        conn = DriverManager.getConnection(url, user, password);
        System.out.println("Connected to the PostgreSQL server successfully.");

    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }

    return conn;
}

public void createItem(int idCur, String title, String description, LocalDate date) {
    try {
        Statement stmnt = null;
        stmnt = connect().createStatement();
        String sql = "INSERT INTO TASKS (ID,TITLE,DESCRIPTION,DATE) "
                + "VALUES " + "(" + idCur + ", " + title + ", " + description + ", " + date + ")";
        stmnt.executeUpdate(sql);

    } catch (SQLException e) {
        System.out.println(e.getMessage());
    }

}

public static void main(String[] args) {
    Database database = new Database();
    database.connect();
    Task task = new Task("'Test2'", "'Test2 description'", "1998-01-07");
    database.createItem(2, task.getTitle(), task.getDescription(), task.getDateFormat());

}

}

}

But Intellij throws following error:

但是 Intellij 抛出以下错误：

ERROR: column "date" is of type date but expression is of type integer
TIP: You will need to rewrite or cast the expression.  

I've tried to google this error, but nothing helped. What should I do with my date?

我试图用谷歌搜索这个错误，但没有任何帮助。我该怎么处理我的约会对象？

回答by Tim Biegeleisen

You should be using prepared statements here, which would solve the problem of not knowing what exact format to use when building your query.

您应该在这里使用准备好的语句，这将解决在构建查询时不知道使用什么确切格式的问题。

PreparedStatement st = conn.prepareStatement("INSERT INTO TASKS (ID, TITLE, DESCRIPTION, DATE) VALUES (?, ?, ?, ?)");
st.setInt(1, idCur);
st.setString(2, title);
st.setString(3, description);
st.setObject(4, date);
st.executeUpdate();
st.close();

Prepared statements take care of making sure that the INSERTstatement will use date and timestamp literals in a correct format for your Postgres database.

准备好的语句负责确保该INSERT语句将以正确的格式为您的 Postgres 数据库使用日期和时间戳文字。

Beyond this, prepared statements also protect your code against SQL injection attacks. Here is a link to documentation covering Postgres' JDBC driver:

除此之外，准备好的语句还可以保护您的代码免受 SQL 注入攻击。以下是涵盖 Postgres 的 JDBC 驱动程序的文档链接：

https://jdbc.postgresql.org/documentation/head/

https://jdbc.postgresql.org/documentation/head/