Insert values to postgresql using JDBC [duplicate]

Question

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:

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:

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?

Answer 1

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 INSERT statement will use date and timestamp literals in a correct format for your Postgres database.

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

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