title: Mapping java.lang.String type to Postgres JSON Type
body: <p>My question is related to the following question <a href="https://stackoverflow.com/questions/15974474/mapping-postgresql-json-column-to-hibernate-value-type">Mapping postgreSQL JSON column to Hibernate value type</a> 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.</p>
  
  <p>I am using MyBatis to map sql to the postgres database via Spring injection.</p>
  
  <p>Here is my Java interface for MyBatis and the method I am having problems mapping to a Postgres JSON column is the updateState() method.</p>
  
  <pre><code>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&lt;Spoke&gt; getSpokes();
  
      // The close() method must exist.
      void close();
  
  }
  </code></pre>
  
  <p>Following is my mapper class:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  
  &lt;!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd"&gt;
  
  &lt;mapper namespace="receiver.spoke.SpokeDAOMyBatis"&gt;
  
      &lt;update id="updateState"&gt;
          UPDATE 
              spokes 
          SET 
              state = #{state} 
          WHERE 
              id = #{id}
      &lt;/update&gt;
  
      &lt;select id="getState" resultType="java.lang.String" 
              parameterType="long" useCache="false"&gt;
          SELECT 
              state 
          FROM 
              spokes 
          WHERE 
              id = #{id}
      &lt;/select&gt;
  
      &lt;select id="getSpokes" resultMap="Spoke" useCache="false"&gt;
          SELECT 
              id, 
              description, 
              uri, 
              updatets 
          FROM 
              spokes
      &lt;/select&gt;
  
      &lt;resultMap type="receiver.bean.Spoke" id="Spoke"&gt;
              &lt;id property="id" column="id"/&gt;
              &lt;id property="description" column="description"/&gt;
              &lt;id property="uri" column="uri"/&gt;
              &lt;id property="updatets" column="updatets"/&gt;
      &lt;/resultMap&gt;
  
  &lt;/mapper&gt;
  </code></pre>
  
  <p>Following is the Spoke bean class:</p>
  
  <pre><code>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 &gt;&gt;&gt; 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 + "]";
      }
  
  }
  </code></pre>
  
  <p>Following is the code for my applicationContext.xml file</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;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"&gt;
  
      &lt;bean id="configurer"
          class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"&gt;
          &lt;property name="locations"&gt;
              &lt;list merge="true"&gt;
                  &lt;value&gt;classpath:db.properties&lt;/value&gt;
              &lt;/list&gt;
          &lt;/property&gt;
      &lt;/bean&gt;
  
      &lt;bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"&gt;
          &lt;property name="driverClassName" value="org.postgresql.Driver" /&gt;
          &lt;property name="url" value="${test.db.url}" /&gt;
          &lt;property name="username" value="${test.db.username}" /&gt;
          &lt;property name="password" value="${test.db.password}" /&gt;
      &lt;/bean&gt;
  
      &lt;bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"&gt;
          &lt;property name="configLocation" value="classpath:mybatis-config.xml" /&gt;
          &lt;property name="dataSource" ref="dataSource" /&gt;
      &lt;/bean&gt;
  
      &lt;bean id="spokeDAOMyBatis" class="org.mybatis.spring.mapper.MapperFactoryBean"&gt;
          &lt;property name="mapperInterface" value="receiver.spoke.SpokeDAOMyBatis" /&gt;
          &lt;property name="sqlSessionFactory" ref="sqlSessionFactory" /&gt;
      &lt;/bean&gt;
  
  &lt;/beans&gt;
  </code></pre>
  
  <p>Following is the db.properties file</p>
  
  <pre><code>test.db.url=jdbc:postgresql://localhost:5432/lunar
  test.db.username=lunar
  test.db.password=lunar
  </code></pre>
  
  <p>Following is the mybatis-config.xml file</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8" ?&gt;
  
  &lt;!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"&gt;
  
  &lt;configuration&gt;
      &lt;settings&gt;
          &lt;setting name="cacheEnabled" value="false" /&gt;
          &lt;setting name="lazyLoadingEnabled" value="false" /&gt;
          &lt;setting name="defaultExecutorType" value="REUSE" /&gt;
          &lt;!-- &lt;setting name="defaultStatementTimeout" value="20000" /&gt; --&gt;
          &lt;setting name="logImpl" value="LOG4J" /&gt;
      &lt;/settings&gt;
  &lt;/configuration&gt;
  </code></pre>
  
  <p>Here is the spokes table which contains the json field</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Next create function for string to json conversion</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$
  SELECT json_in($1::cstring); 
  $$ LANGUAGE SQL IMMUTABLE;
  CREATE FUNCTION
  </code></pre>
  
  <p>Create cast</p>
  
  <pre><code>CREATE CAST (text AS json) WITH FUNCTION json_intext(text) AS IMPLICIT;
  CREATE CAST
  </code></pre>
  
  <p>This works when doing conversion from within my psql but not from the java side</p>
  
  <p>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</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Following is my test class:</p>
  
  <pre><code>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&lt;Spoke&gt; 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());
      }    
  }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>Tests run: 3, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.241 sec &lt;&lt;&lt; FAILURE!
  updateState(receiver.spoke.SpokeDAOMyBatisTest)  Time elapsed: 0.035 sec  &lt;&lt;&lt; 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
  </code></pre>