How to insert unicode to Oracle NVARCHAR using MyBatis with Spring integration

2
votes
    INSERT INTO rbp_users
       (user_id, user_name, passwd, bank_id, descr, stat, p_exp, user_type_id, email_address)
    VALUES
       (rbp_userid_seq.NEXTVAL ,
        'any_user_name',
        'some_encrypted_password',
        1,
        N'əəııööğğşşççüü', --unicode string
        'E',
        SYSDATE,
        2,
        NULL);

Above insert into Oracle table with NVARCHAR field "descr" works fine via PL/SQL Developer. But I can't write "N" before unicode field in MyBatis DAO Mapper XML:

<insert id="addUser" parameterType="User" flushCache="true">
    INSERT INTO rbp_users
       (user_id, user_name, passwd, bank_id, descr, stat, p_exp, user_type_id, email_address)
    VALUES
       (rbp_userid_seq.NEXTVAL ,
        #{userName,            javaType=String,     jdbcType=VARCHAR},
        pswcode(#{password,    javaType=String,     jdbcType=VARCHAR}),
        #{bankId,              javaType=Integer,    jdbcType=NUMERIC},
        N#{description,         javaType=String,     jdbcType=NVARCHAR},
        #{userStatus,          javaType=String,     jdbcType=VARCHAR},
        #{passwordExpireDate,  javaType=Date,       jdbcType=DATE},
        #{userTypeId,          javaType=Integer,    jdbcType=NUMERIC},
        #{emailAddress,        javaType=String,     jdbcType=VARCHAR})

    <selectKey resultType="java.lang.Integer" keyProperty="userId">
        SELECT rbp_userid_seq.currval from dual
    </selectKey>
</insert>

This raises and error:

org.springframework.jdbc.InvalidResultSetAccessException: Error setting null for parameter #8 with JdbcType VARCHAR . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: Invalid column index
; invalid ResultSet access for SQL []; nested exception is java.sql.SQLException: Invalid column index
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:237)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:365)
    at $Proxy20.insert(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:237)
    at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:79)
    at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40)
    at $Proxy37.addUser(Unknown Source)
    at com.azercell.paymentgateway.service.UserServiceImpl.addUser(UserServiceImpl.java:156)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:42)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    at $Proxy38.addUser(Unknown Source)
    at com.azercell.paymentgateway.service.UserServiceImplTest.testAddUser(UserServiceImplTest.java:101)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231)
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:48)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:242)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:58)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:240)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:48)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:233)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:303)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174)
    at org.junit.runner.JUnitCore.run(JUnitCore.java:157)
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:76)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:195)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:63)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: java.sql.SQLException: Invalid column index
    at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4554)
    at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4541)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1283)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$StatementProxy.invoke(AbstractQueryReport.java:235)
    at $Proxy83.setNull(Unknown Source)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:66)
    at $Proxy84.setNull(Unknown Source)
    at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:39)
    at org.apache.ibatis.executor.parameter.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:91)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:77)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:58)
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:71)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:44)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:108)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:134)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:355)
    ... 51 more

If removing "N" from "N#{description, javaType=String, jdbcType=NVARCHAR}," no exception raises, but inserted string becomes corrupted.

2
javaspringjdbcibatismybatis

2 Answers

2
votes

I can fix issue after executing Tomcat with parameters below:

-Doracle.jdbc.defaultNChar=true
-Doracle.jdbc.defaultNChar=true

Source: Inserting national characters into an oracle NCHAR or NVARCHAR column does not work

1
votes

I dont think you will have to mention "N" before unicode field in the XML at all, as you are metioning the jdbcType in it.

In some environments, the system's default encoding does not play well with the unicode encoding favored by XML files. If you encounter encoding issues when parsing iBATIS XML files with a Reader as input, you can change the default encoding to match the encoding of the XML files. For example:

 String resource = “properties/sqlMap-config.xml”;
 Resources.setCharset(Charset.forName('UTF-8”)); // change the default encoding
 Reader reader = Resources.getResourceAsReader(resource);
 SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMap(reader);

The “setCharset” method will change the encoding used for all future calls to “getResourceAsReader”.