Skip to content
Advertisement

org.postgresql.util.PSQLException: ERROR: column is of type date but expression is of type integer: cannot write date into postgres using java

I’m trying to insert a record inside my table but I cannot insert any values into the Date column.

This is the code I use to make an insert:

Connection connection = DatabaseConnection.getInstance().getConnection();
    ResultSet result = null;
    try
    {
        Statement statement = connection.createStatement();
        statement.executeUpdate(query,Statement.RETURN_GENERATED_KEYS);
        result = statement.getGeneratedKeys();
    } catch (SQLException e)
    {
        e.printStackTrace();
    }
    finally
    {
        return result;
    }

How I call this function:

String authorName = "Paul"
String authorSurname = "Mac"
DateTimeFormatter f = DateTimeFormatter.ofPattern( "yyyy-MM-dd" ) ; 
LocalDate date = LocalDate.parse ( "2017-09-24" , f );

"Insert into autore(nome_autore, cognome_autore, datanascita) values('"+authorName+"', '"+authorSurname+"', "+date+")")

The fullstack trace I get:

org.postgresql.util.PSQLException: ERROR: column "datanascita" is of type date but expression is of type integer
  Suggerimento: You will need to rewrite or cast the expression.
  Posizione: 90
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
    at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:1259)
    at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:1240)
    at projectRiferimentiBibliografici/com.ProjectRiferimentiBibliografici.DatabaseConnection.QueryManager.executeUpdateWithResultSet(QueryManager.java:113)
    at projectRiferimentiBibliografici/com.ProjectRiferimentiBibliografici.DAOImplementation.AuthorDaoPostgresql.insertAuthor(AuthorDaoPostgresql.java:136)
    at projectRiferimentiBibliografici/com.ProjectRiferimentiBibliografici.Main.MainCe.main(MainCe.java:43)

Advertisement

Answer

The correct solution to this problem is to use a PreparedStatement – do not concatenate parameters into SQL strings.

Your problem with the date parameter is only the tip of the iceberg.
The next problem you’ll get is, if Peter O'Donnel signs up.

So you should use something like this:

String authorName = "Paul";
String authorSurname = "Mac";
DateTimeFormatter f = DateTimeFormatter.ofPattern("yyyy-MM-dd"); 
LocalDate date = LocalDate.parse("2017-09-24", f);

String insert = "Insert into autore(nome_autore, cognome_autore, datanascita) values(?,?,?)";

PreparedStatement pstmt = connection.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
pstmt.setString(1, authorName);
pstmt.setString(2, authorSurname);
pstmt.setObject(3, date, java.sql.Types.DATE);

pstmt.executeUpdate();

Advertisement