Mapping java.lang.String type to Postgres JSON Type

My question is related to the following question Mapping postgreSQL JSON column to Hibernate value type and although the answer works when I test passing a string to psql in postgres it doesn’t work when passing a string from my Java code.

I am using MyBatis to map sql to the postgres database via Spring injection.

Here is my Java interface for MyBatis and the method I am having problems mapping to a Postgres JSON column is the updateState() method.

package receiver.spoke;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import receiver.bean.Spoke;

public interface SpokeDAOMyBatis extends SpokeDAO {

    void updateState(@Param("id") long spokeId, @Param("state") String state);

    String getState(@Param("id") long spokeId);

    List<Spoke> getSpokes();

    // The close() method must exist.
    void close();

}

JavaScript
​x
 
package receiver.spoke;​import java.util.List;​import org.apache.ibatis.annotations.Param;​import receiver.bean.Spoke;​public interface SpokeDAOMyBatis extends SpokeDAO {​    void updateState(@Param("id") long spokeId, @Param("state") String state);​    String getState(@Param("id") long spokeId);​    List<Spoke> getSpokes();​    // The close() method must exist.    void close();​}​

Following is my mapper class:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="receiver.spoke.SpokeDAOMyBatis">

    <update id="updateState">
        UPDATE 
            spokes 
        SET 
            state = #{state} 
        WHERE 
            id = #{id}
    </update>

    <select id="getState" resultType="java.lang.String" 
            parameterType="long" useCache="false">
        SELECT 
            state 
        FROM 
            spokes 
        WHERE 
            id = #{id}
    </select>

    <select id="getSpokes" resultMap="Spoke" useCache="false">
        SELECT 
            id, 
            description, 
            uri, 
            updatets 
        FROM 
            spokes
    </select>

    <resultMap type="receiver.bean.Spoke" id="Spoke">
            <id property="id" column="id"/>
            <id property="description" column="description"/>
            <id property="uri" column="uri"/>
            <id property="updatets" column="updatets"/>
    </resultMap>

</mapper>

JavaScript
 
<?xml version="1.0" encoding="UTF-8"?>​<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">​<mapper namespace="receiver.spoke.SpokeDAOMyBatis">​    <update id="updateState">        UPDATE             spokes         SET             state = #{state}         WHERE             id = #{id}    </update>​    <select id="getState" resultType="java.lang.String"             parameterType="long" useCache="false">        SELECT             state         FROM             spokes         WHERE             id = #{id}    </select>​    <select id="getSpokes" resultMap="Spoke" useCache="false">        SELECT             id,             description,             uri,             updatets         FROM             spokes    </select>​    <resultMap type="receiver.bean.Spoke" id="Spoke">            <id property="id" column="id"/>            <id property="description" column="description"/>            <id property="uri" column="uri"/>            <id property="updatets" column="updatets"/>    </resultMap>​</mapper>​

Following is the Spoke bean class:

package receiver.bean;

import java.sql.Timestamp;

public class Spoke {

    private long id;
    private String description;
    private String uri;
    private Timestamp updatets;
//    private String state;

    public long getId() {
        return this.id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getDescription() {
        return this.description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getUri() {
        return this.uri;
    }

    public void setUri(String uri) {
        this.uri = uri;
    }

    public Timestamp getUpdatets() {
        return updatets;
    }

    public void setUpdatets(Timestamp updatets) {
        this.updatets = updatets;
    }

//  public String getState() {
//      return (String) state;
//  }

//  public void setState(String state) {
//      this.state = state;
//  }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((this.description == null) ? 0 : this.description.hashCode());
        result = prime * result + (int) (this.id ^ (this.id >>> 32));
        result = prime * result + ((this.uri == null) ? 0 : this.uri.hashCode());
        result = prime * result + ((this.updatets == null) ? 0 : this.updatets.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Spoke other = (Spoke) obj;
        if (this.description == null) {
            if (other.description != null)
                return false;
        } else if (!this.description.equals(other.description))
            return false;
        if (this.id != other.id)
            return false;
        if (this.uri == null) {
            if (other.uri != null)
                return false;
        } else if (!this.uri.equals(other.uri))
            return false;
        if (this.updatets == null) {
            if (other.updatets != null)
                return false;
        } else if (!this.updatets.equals(other.updatets))
            return false;
        return true;
    }

    @Override
    public String toString() {
        return "SpokeData [id=" + this.id + ", description=" + this.description + ", uri=" + this.uri + "]";
    }

}

JavaScript
 
package receiver.bean;​import java.sql.Timestamp;​public class Spoke {​    private long id;    private String description;    private String uri;    private Timestamp updatets;//    private String state;​    public long getId() {        return this.id;    }​    public void setId(long id) {        this.id = id;    }​    public String getDescription() {        return this.description;    }​    public void setDescription(String description) {        this.description = description;    }​    public String getUri() {        return this.uri;    }​    public void setUri(String uri) {        this.uri = uri;    }​    public Timestamp getUpdatets() {        return updatets;    }​    public void setUpdatets(Timestamp updatets) {        this.updatets = updatets;    }​//  public String getState() {//      return (String) state;//  }​//  public void setState(String state) {//      this.state = state;//  }​    @Override    public int hashCode() {        final int prime = 31;        int result = 1;        result = prime * result + ((this.description == null) ? 0 : this.description.hashCode());        result = prime * result + (int) (this.id ^ (this.id >>> 32));        result = prime * result + ((this.uri == null) ? 0 : this.uri.hashCode());        result = prime * result + ((this.updatets == null) ? 0 : this.updatets.hashCode());        return result;    }​    @Override    public boolean equals(Object obj) {        if (this == obj)            return true;        if (obj == null)            return false;        if (getClass() != obj.getClass())            return false;        Spoke other = (Spoke) obj;        if (this.description == null) {            if (other.description != null)                return false;        } else if (!this.description.equals(other.description))            return false;        if (this.id != other.id)            return false;        if (this.uri == null) {            if (other.uri != null)                return false;        } else if (!this.uri.equals(other.uri))            return false;        if (this.updatets == null) {            if (other.updatets != null)                return false;        } else if (!this.updatets.equals(other.updatets))            return false;        return true;    }​    @Override    public String toString() {        return "SpokeData [id=" + this.id + ", description=" + this.description + ", uri=" + this.uri + "]";    }​}​

Following is the code for my applicationContext.xml file

<?xml version="1.0" encoding="UTF-8"?>
<beans
    xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://www.springframework.org/schema/beans     http://www.springframework.org/schema/beans/spring-beans.xsd"
     default-lazy-init="false">

    <bean id="configurer"
        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="locations">
            <list merge="true">
                <value>classpath:db.properties</value>
            </list>
        </property>
    </bean>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="org.postgresql.Driver" />
        <property name="url" value="${test.db.url}" />
        <property name="username" value="${test.db.username}" />
        <property name="password" value="${test.db.password}" />
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="configLocation" value="classpath:mybatis-config.xml" />
        <property name="dataSource" ref="dataSource" />
    </bean>

    <bean id="spokeDAOMyBatis" class="org.mybatis.spring.mapper.MapperFactoryBean">
        <property name="mapperInterface" value="receiver.spoke.SpokeDAOMyBatis" />
        <property name="sqlSessionFactory" ref="sqlSessionFactory" />
    </bean>

</beans>

JavaScript
 
<?xml version="1.0" encoding="UTF-8"?><beans    xmlns="http://www.springframework.org/schema/beans"    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xsi:schemaLocation="http://www.springframework.org/schema/beans     http://www.springframework.org/schema/beans/spring-beans.xsd"     default-lazy-init="false">​    <bean id="configurer"        class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">        <property name="locations">            <list merge="true">                <value>classpath:db.properties</value>            </list>        </property>    </bean>​    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">        <property name="driverClassName" value="org.postgresql.Driver" />        <property name="url" value="${test.db.url}" />        <property name="username" value="${test.db.username}" />        <property name="password" value="${test.db.password}" />    </bean>​    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">        <property name="configLocation" value="classpath:mybatis-config.xml" />        <property name="dataSource" ref="dataSource" />    </bean>​    <bean id="spokeDAOMyBatis" class="org.mybatis.spring.mapper.MapperFactoryBean">        <property name="mapperInterface" value="receiver.spoke.SpokeDAOMyBatis" />        <property name="sqlSessionFactory" ref="sqlSessionFactory" />    </bean>​</beans>​

Following is the db.properties file

test.db.url=jdbc:postgresql://localhost:5432/lunar
test.db.username=lunar
test.db.password=lunar

JavaScript
 
test.db.url=jdbc:postgresql://localhost:5432/lunartest.db.username=lunartest.db.password=lunar​

Following is the mybatis-config.xml file

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <settings>
        <setting name="cacheEnabled" value="false" />
        <setting name="lazyLoadingEnabled" value="false" />
        <setting name="defaultExecutorType" value="REUSE" />
        <!-- <setting name="defaultStatementTimeout" value="20000" /> -->
        <setting name="logImpl" value="LOG4J" />
    </settings>
</configuration>

JavaScript
 
<?xml version="1.0" encoding="UTF-8" ?>​<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">​<configuration>    <settings>        <setting name="cacheEnabled" value="false" />        <setting name="lazyLoadingEnabled" value="false" />        <setting name="defaultExecutorType" value="REUSE" />        <!-- <setting name="defaultStatementTimeout" value="20000" /> -->        <setting name="logImpl" value="LOG4J" />    </settings></configuration>​

Here is the spokes table which contains the json field

CREATE TABLE spokes (
    id BIGSERIAL NOT NULL,
    insertts TIMESTAMPTZ NOT NULL,
    updatets TIMESTAMPTZ NOT NULL,
    rowversion BIGINT NOT NULL,
    description TEXT NOT NULL,
    uri TEXT NOT NULL,
    state JSON
);

-- create primary key
ALTER TABLE spokes ADD CONSTRAINT pk_spokes PRIMARY KEY (id);

-- triggers
CREATE TRIGGER b_spokes_audit BEFORE INSERT OR UPDATE ON spokes FOR EACH ROW EXECUTE PROCEDURE trigger_function_aud$

-- grants
GRANT ALL ON TABLE spokes TO lunar;

JavaScript
 
CREATE TABLE spokes (    id BIGSERIAL NOT NULL,    insertts TIMESTAMPTZ NOT NULL,    updatets TIMESTAMPTZ NOT NULL,    rowversion BIGINT NOT NULL,    description TEXT NOT NULL,    uri TEXT NOT NULL,    state JSON);​-- create primary keyALTER TABLE spokes ADD CONSTRAINT pk_spokes PRIMARY KEY (id);​-- triggersCREATE TRIGGER b_spokes_audit BEFORE INSERT OR UPDATE ON spokes FOR EACH ROW EXECUTE PROCEDURE trigger_function_aud$​-- grantsGRANT ALL ON TABLE spokes TO lunar;​

Next create function for string to json conversion

CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$
SELECT json_in($1::cstring); 
$$ LANGUAGE SQL IMMUTABLE;
CREATE FUNCTION

JavaScript
 
CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$SELECT json_in($1::cstring); $$ LANGUAGE SQL IMMUTABLE;CREATE FUNCTION​

Create cast

CREATE CAST (text AS json) WITH FUNCTION json_intext(text) AS IMPLICIT;
CREATE CAST

JavaScript
 
CREATE CAST (text AS json) WITH FUNCTION json_intext(text) AS IMPLICIT;CREATE CAST​

This works when doing conversion from within my psql but not from the java side

Following are some tests showing the function and cast working via psql for a table called xxx and also the fact that invalid json is not accepted

PREPARE test(text) AS INSERT INTO xxx(id,state) VALUES (1,$1);
PREPARE
execute test('{}');
INSERT 0 1
select json_in('a');
ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: a
select json_in('a'::cstring);
ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: a
select json_in('{}');
 json_in 
---------
 {}
(1 row)

select json_in('{');
ERROR:  invalid input syntax for type json
DETAIL:  The input string ended unexpectedly.
CONTEXT:  JSON data, line 1: {
select json_in('{}');
 json_in 
 ---------
  {}
(1 row)

JavaScript
 
PREPARE test(text) AS INSERT INTO xxx(id,state) VALUES (1,$1);PREPAREexecute test('{}');INSERT 0 1select json_in('a');ERROR:  invalid input syntax for type jsonDETAIL:  Token "a" is invalid.CONTEXT:  JSON data, line 1: aselect json_in('a'::cstring);ERROR:  invalid input syntax for type jsonDETAIL:  Token "a" is invalid.CONTEXT:  JSON data, line 1: aselect json_in('{}'); json_in --------- {}(1 row)​select json_in('{');ERROR:  invalid input syntax for type jsonDETAIL:  The input string ended unexpectedly.CONTEXT:  JSON data, line 1: {select json_in('{}'); json_in  ---------  {}(1 row)​

Following is my test class:

package receiver.spoke;

import static org.junit.Assert.*;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import receiver.bean.Spoke;
import receiver.spoke.SpokeDAOMyBatis;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = { "/applicationContext.xml" })
public class SpokeDAOMyBatisTest {

    @Autowired 
    @Qualifier("spokeDAOMyBatis")
    private SpokeDAOMyBatis spokeDAOMyBatis;

    @Autowired
    @Qualifier("dataSource")
    private BasicDataSource datasource;

    @Before
    public void connectionTest() throws SQLException {     
        datasource.getConnection();
    }

    @Test
    public void updateState() {

        assertNotNull(spokeDAOMyBatis);
        spokeDAOMyBatis.updateState(1L, "{}");
        assertTrue("Returned wrong state", spokeDAOMyBatis.getState(1L).equals("{}"));
    }

    @Test
    public void getState() {

        assertNotNull(spokeDAOMyBatis);
        String str1 = spokeDAOMyBatis.getState(1L);
        String str2 = spokeDAOMyBatis.getState(2L);
        assertTrue("Returned wrong state", spokeDAOMyBatis.getState(1L).equals(str1));
        assertTrue("Returned wrong state", spokeDAOMyBatis.getState(2L).equals(str2));
    }

    @Test
    public void getAllSpokesTest() {

        assertNotNull(spokeDAOMyBatis);
        List<Spoke> list = spokeDAOMyBatis.getSpokes();
        System.out.println(list.toString());

        assertNotNull("List of spokes returned null", list);
        assertTrue("List of spokes is empty", !list.isEmpty());
        assertNotNull("Invalid spoke", list.get(0));
        assertNotNull("id not loaded", list.get(0).getId());
        assertNotNull("description not loaded", list.get(0).getDescription());
        assertNotNull("uri not loaded", list.get(0).getUri());
        assertNotNull("updatets not loaded", list.get(0).getUpdatets());
    }    
}

JavaScript
 
package receiver.spoke;​import static org.junit.Assert.*;​import java.sql.SQLException;import java.util.List;​import org.apache.commons.dbcp.BasicDataSource;import org.junit.Before;import org.junit.Test;import org.junit.runner.RunWith;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Qualifier;import org.springframework.test.context.ContextConfiguration;import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;​import receiver.bean.Spoke;import receiver.spoke.SpokeDAOMyBatis;​@RunWith(SpringJUnit4ClassRunner.class)@ContextConfiguration(locations = { "/applicationContext.xml" })public class SpokeDAOMyBatisTest {​    @Autowired     @Qualifier("spokeDAOMyBatis")    private SpokeDAOMyBatis spokeDAOMyBatis;​    @Autowired    @Qualifier("dataSource")    private BasicDataSource datasource;​    @Before    public void connectionTest() throws SQLException {             datasource.getConnection();    }​    @Test    public void updateState() {​        assertNotNull(spokeDAOMyBatis);        spokeDAOMyBatis.updateState(1L, "{}");        assertTrue("Returned wrong state", spokeDAOMyBatis.getState(1L).equals("{}"));    }​    @Test    public void getState() {​        assertNotNull(spokeDAOMyBatis);        String str1 = spokeDAOMyBatis.getState(1L);        String str2 = spokeDAOMyBatis.getState(2L);        assertTrue("Returned wrong state", spokeDAOMyBatis.getState(1L).equals(str1));        assertTrue("Returned wrong state", spokeDAOMyBatis.getState(2L).equals(str2));    }​    @Test    public void getAllSpokesTest() {​        assertNotNull(spokeDAOMyBatis);        List<Spoke> list = spokeDAOMyBatis.getSpokes();        System.out.println(list.toString());​        assertNotNull("List of spokes returned null", list);        assertTrue("List of spokes is empty", !list.isEmpty());        assertNotNull("Invalid spoke", list.get(0));        assertNotNull("id not loaded", list.get(0).getId());        assertNotNull("description not loaded", list.get(0).getDescription());        assertNotNull("uri not loaded", list.get(0).getUri());        assertNotNull("updatets not loaded", list.get(0).getUpdatets());    }    }​

So it all seems to be working fine but when I try to run the Java side I get the cast exception as shown in the Stack trace below:

Tests run: 3, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.241 sec <<< FAILURE!
updateState(receiver.spoke.SpokeDAOMyBatisTest)  Time elapsed: 0.035 sec  <<< ERROR!
org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: column "state" is of type json but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 38
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE     spokes    SET     state = ?    WHERE     id = ?
### Cause: org.postgresql.util.PSQLException: ERROR: column "state" is of type json but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 38
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "state" is of type json but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 38
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399)
    at com.sun.proxy.$Proxy17.update(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:269)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:55)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
    at com.sun.proxy.$Proxy24.updateState(Unknown Source)
    at receiver.spoke.SpokeDAOMyBatisTest.updateState(SpokeDAOMyBatisTest.java:41)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:254)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193)
    at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:252)
    at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:141)
    at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:112)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:189)
    at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:165)
    at org.apache.maven.surefire.booter.ProviderFactory.invokeProvider(ProviderFactory.java:85)
    at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:115)
    at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:75)
Caused by: org.postgresql.util.PSQLException: ERROR: column "state" is of type json but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 38
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:413)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
    at com.sun.proxy.$Proxy23.execute(Unknown Source)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73)
    at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:51)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:386)
    ... 42 more

3601 [Thread-0] INFO  org.springframework.context.support.GenericApplicationContext - Closing org.springframework.context.support.GenericApplicationContext@4fcd19b3: startup date [Mon Jan 11 11:10:54 GMT 2016]; root of context hierarchy

JavaScript
 
Tests run: 3, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.241 sec <<< FAILURE!updateState(receiver.spoke.SpokeDAOMyBatisTest)  Time elapsed: 0.035 sec  <<< ERROR!org.springframework.jdbc.BadSqlGrammarException: ### Error updating database.  Cause: org.postgresql.util.PSQLException: ERROR: column "state" is of type json but expression is of type character varying  Hint: You will need to rewrite or cast the expression.  Position: 38### The error may involve defaultParameterMap### The error occurred while setting parameters### SQL: UPDATE     spokes    SET     state = ?    WHERE     id = ?### Cause: org.postgresql.util.PSQLException: ERROR: column "state" is of type json but expression is of type character varying  Hint: You will need to rewrite or cast the expression.  Position: 38; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "state" is of type json but expression is of type character varying  Hint: You will need to rewrite or cast the expression.  Position: 38    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399)    at com.sun.proxy.$Proxy17.update(Unknown Source)    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:269)    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:55)    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)    at com.sun.proxy.$Proxy24.updateState(Unknown Source)    at receiver.spoke.SpokeDAOMyBatisTest.updateState(SpokeDAOMyBatisTest.java:41)    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)    at java.lang.reflect.Method.invoke(Method.java:497)    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)    at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:254)    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89)    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193)    at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:252)    at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:141)    at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:112)    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)    at java.lang.reflect.Method.invoke(Method.java:497)    at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:189)    at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:165)    at org.apache.maven.surefire.booter.ProviderFactory.invokeProvider(ProviderFactory.java:85)    at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:115)    at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:75)Caused by: org.postgresql.util.PSQLException: ERROR: column "state" is of type json but expression is of type character varying  Hint: You will need to rewrite or cast the expression.  Position: 38    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:413)    at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)    at java.lang.reflect.Method.invoke(Method.java:497)    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)    at com.sun.proxy.$Proxy23.execute(Unknown Source)    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:45)    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73)    at org.apache.ibatis.executor.ReuseExecutor.doUpdate(ReuseExecutor.java:51)    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170)    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)    at java.lang.reflect.Method.invoke(Method.java:497)    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:386)    ... 42 more​3601 [Thread-0] INFO  org.springframework.context.support.GenericApplicationContext - Closing org.springframework.context.support.GenericApplicationContext@4fcd19b3: startup date [Mon Jan 11 11:10:54 GMT 2016]; root of context hierarchy​

Answer

Adding stringtype=unspecified to my jdbc connection string as https://stackoverflow.com/users/1499698/pozs suggested worked:

jdbc:postgresql://localhost:5432/dbname?stringtype=unspecified

JavaScript
 
jdbc:postgresql://localhost:5432/dbname?stringtype=unspecified​

Doing this I could also remove the function and cast from a string to json.

I also tested the change by passing invalid json to the JSON column.

As hoped when doing so I got a DataIntegrityViolationException as I would expect from that test

Advertisement

Answer