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

yellowood 0
Viewed 37

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)
java postgresql
3 Answers

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

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')")
SaiNageswar S0

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.

YJR0
Related