org.postgresql.util.PSQLException: ERROR: column is of type date but expression is of type integer:-CodePudding

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)

CodePudding user response：

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();

CodePudding user response：

There is a way to solve this. In the place Where is you specify the jdbc url.

Ex:

"jdbc:postgresql://host/schema"

Change above to

"jdbc:postgresql://host/schema?stringtype=unspecified"

Then your db determine type of your params not the jdbc driver.

CodePudding user response：

Here you are using direct insert sql statement. As you are appending date object to string it will be converted to date.toString() which might not be expected format in sql.

Below is the insert sql statement:

"Insert into autore(nome_autore, cognome_autore, datanascita)
values('" authorName "', '" authorSurname "', '2017-09-24')")

Note: This approach of sql query building is not recommended and open to SQL Injection. Better to use PreparedStatement or an ORM framework.