How to insert unicode to Oracle NVARCHAR using MyB

问题:

    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.

回答1:

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

回答2:

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”.