{"id":142,"date":"2017-02-14T12:26:29","date_gmt":"2017-02-14T11:26:29","guid":{"rendered":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/?p=142"},"modified":"2022-12-04T12:27:05","modified_gmt":"2022-12-04T11:27:05","slug":"spooky-exceptions-5-caused-by-org-postgresql-util-psqlexception-error-column-date_start-is-of-type-date-but-expression-is-of-type-character-varying","status":"publish","type":"post","link":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/?p=142","title":{"rendered":"Spooky Exceptions (5) Caused by: org.postgresql.util.PSQLException: ERROR: column &#8222;date_start&#8220; is of type date but expression is of type character varying"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Umgebung<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Wildfly 10<\/li>\n\n\n\n<li>EE7<\/li>\n\n\n\n<li>Postgres<\/li>\n\n\n\n<li>Eclipselink<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Situation<\/h2>\n\n\n\n<p>Wenn wir versuchen ein abh\u00e4ngiges Objekt \u00fcber Datenbankattribute vom Typ Date zu referenzieren (mindestens eines), kann dies zu Problemen f\u00fchren. Wir haben folgende Abh\u00e4ngigkeit<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> \n    @JoinColumns({\n        @JoinColumn(name = \"mandator_id\", referencedColumnName = \"mandator_id\", insertable = true, updatable = true),\n        @JoinColumn(name = \"date_start\", referencedColumnName = \"date_start\", insertable = true, updatable = true)\n    })\n    @ManyToOne\n    private AccountingPeriod accountingPeriodEnd = null;\n<\/pre>\n\n\n\n<p>Es wird also auf ein Objekt vom Typ AccountingPeriod verwiesen. Beide Attribute f\u00fcr die Verbindung sind schreibend definiert. Versucht man nun diesen Wert mit null zu persistieren, kann es zu folgender Fehler-Meldung kommen<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> \nCaused by: org.postgresql.util.PSQLException: ERROR: column \"date_start\" is of type date but expression is of type character varying\n<\/pre>\n\n\n\n<p>Innerhalb des Mappings erkennt die JPA nicht den korrekten Typ. Je nach Datenbank, kann dies zu Problemen f\u00fchren.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">L\u00f6sung<\/h2>\n\n\n\n<p>Es muss das Date-Attribut &#8222;ausgelagert&#8220; werden. Hierf\u00fcr definieren wir<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> \n    @Temporal(TemporalType.TIMESTAMP)\n    @Column(name = \"date_start\")\n    private Date dateEnd = null;\n\n    @JoinColumns({\n        @JoinColumn(name = \"mandator_id\", referencedColumnName = \"mandator_id\", insertable = true, updatable = true),\n        @JoinColumn(name = \"date_start\", referencedColumnName = \"date_start\", insertable = false, updatable = false)\n    })\n    @ManyToOne\n    private AccountingPeriod accountingPeriodEnd = null;\n<\/pre>\n\n\n\n<p>Im entsprechneden Setter m\u00fcssen wir das korrekte Bef\u00fcllen des Date-Attribits sicherstellen<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"> \n    public AccountingPeriod getAccountingPeriodEnd() {\n        return this.accountingPeriodEnd;\n    }\n    public void setAccountingPeriodEnd(AccountingPeriod value) {\n        this.accountingPeriodEnd = value;\n        if (value != null) {\n            this.setDateEnd(value.getDateStart());\n        } else {\n            this.setDateEnd(null);\n        }\n    }\n\n    protected Date getDateEnd() {\n        return this.dateEnd;\n    }\n    private void setDateEnd(Date value) {\n        this.dateEnd = value;\n    }\n<\/pre>\n\n\n\n<p>Hierdurch ist der Typ eindeutig definiert und JPA hat keine Probleme mehr.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Umgebung Situation Wenn wir versuchen ein abh\u00e4ngiges Objekt \u00fcber Datenbankattribute vom Typ Date zu referenzieren (mindestens eines), kann dies zu Problemen f\u00fchren. Wir haben folgende Abh\u00e4ngigkeit @JoinColumns({ @JoinColumn(name = &#8222;mandator_id&#8220;, referencedColumnName = &#8222;mandator_id&#8220;, insertable = true, updatable = true), @JoinColumn(name = &#8222;date_start&#8220;, referencedColumnName = &#8222;date_start&#8220;, insertable = true, updatable = true) }) @ManyToOne private AccountingPeriod [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,7],"tags":[],"class_list":["post-142","post","type-post","status-publish","format-standard","hentry","category-jee","category-wildfly"],"_links":{"self":[{"href":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/index.php?rest_route=\/wp\/v2\/posts\/142","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=142"}],"version-history":[{"count":1,"href":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/index.php?rest_route=\/wp\/v2\/posts\/142\/revisions"}],"predecessor-version":[{"id":143,"href":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/index.php?rest_route=\/wp\/v2\/posts\/142\/revisions\/143"}],"wp:attachment":[{"href":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=142"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.schoenberg-solutions.de\/arndtblog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}