Skip to content

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

}

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>

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 + "]";
    }

}

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>

Following is the db.properties file

test.db.url=jdbc:postgresql://localhost:5432/lunar
test.db.username=lunar
test.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>

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;

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

Create cast

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)

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

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

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

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