Shulou(Shulou.com)06/01 Report--

Most people do not understand the knowledge points of this article "how to use shardingsphere to solve the SQLServer pit", so the editor summarizes the following contents, detailed contents, clear steps, and has a certain reference value. I hope you can get something after reading this article. Let's take a look at this "how to use shardingsphere to solve the SQLServer pit" article.

Background: recently, a project using SQLServer has too much business and began to have an impact on the business, so users require upgrading and transformation, and technically adopt shardingsphere for sub-database and sub-table.

After a series of research, design. After a clanging operation, the knife transformation began. Pom dependencies are as follows:

Org.apache.shardingsphere sharding-jdbc-spring-boot-starter 4.0.1 com.alibaba druid-spring-boot-starter 1.2.8

After the transformation, all kinds of query and write errors are reported:

Caused by: org.apache.ibatis.type.TypeException: Error setting non null for parameter # 2 with JdbcType NVARCHAR. Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLFeatureNotSupportedException: setNString

At org.apache.ibatis.type.BaseTypeHandler.setParameter (BaseTypeHandler.java:75)

At org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters (DefaultParameterHandler.java:87)

... 47 common frames omitted

Caused by: java.sql.SQLFeatureNotSupportedException: setNString

At org.apache.shardingsphere.shardingjdbc.jdbc.unsupported.AbstractUnsupportedOperationPreparedStatement.setNString (AbstractUnsupportedOperationPreparedStatement.java:57)

At org.apache.ibatis.type.NStringTypeHandler.setNonNullParameter (NStringTypeHandler.java:31)

At org.apache.ibatis.type.NStringTypeHandler.setNonNullParameter (NStringTypeHandler.java:26)

At org.apache.ibatis.type.BaseTypeHandler.setParameter (BaseTypeHandler.java:73)

... 48 common frames omitted

Core error: Caused by: java.sql.SQLFeatureNotSupportedException: setNString

Problem analysis:

After searching for thousands of degrees on the Internet, I suddenly looked back, but I still couldn't find a problem. Finally, the debug breakpoint followed the source code:

The PreparedStatement that operates the database is ShardingPreparedStatement

Then setNString supports SQLServerPreparedStatement and does not support ShardingPreparedStatement (no problem before the transformation, the cause of the problem after the transformation)

Problem solving:

Found the problem, the following is to solve the problem, since there is no implementation of setNString, then implement one

The first step is to implement NVarcharTypeHandler:

Package cn.preserve.config.mybatis; import org.apache.ibatis.type.BaseTypeHandler;import org.apache.ibatis.type.JdbcType;import org.apache.ibatis.type.MappedJdbcTypes;import org.apache.ibatis.type.TypeException;import java.sql.CallableStatement;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException / * * converting nvarchar to varchar sharingJDBC does not support nvarchar * mainly in NStringTypeHandler, without setNString () * / @ MappedJdbcTypes (JdbcType.NVARCHAR) public class NVarcharTypeHandler extends BaseTypeHandler {@ Override public void setParameter (PreparedStatement ps, int I, String parameter, JdbcType jdbcType) throws SQLException {if (parameter = = null) {if (jdbcType = = null) {throw new TypeException ("JDBC requires that the JdbcType must be specified for all nullable parameters.") } try {ps.setNull (I, jdbcType.TYPE_CODE);} catch (SQLException var7) {throw new TypeException ("Error setting null for parameter #" + I + "with JdbcType" + jdbcType + ". "+" Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. "+" Cause: "+ var7, var7);}} else {try {this.setNonNullParameter (ps, I, parameter, jdbcType);} catch (Exception var6) {throw new TypeException (" Error setting non null for parameter # "+ I +" with JdbcType "+ jdbcType +". "+" Try setting a different JdbcType for this parameter or a different configuration property. "+" Cause: "+ var6, var6);} / * use setNString instead of setString * @ param ps * @ param I * @ param parameter * @ param jdbcType * @ throws SQLException * / @ Override public void setNonNullParameter (PreparedStatement ps, int I, String parameter, JdbcType jdbcType) throws SQLException {ps.setString (I, parameter) @ Override public String getNullableResult (ResultSet rs, String columnName) throws SQLException {return rs.getString (columnName);} @ Override public String getNullableResult (ResultSet rs, int columnIndex) throws SQLException {return rs.getString (columnIndex);} @ Override public String getNullableResult (CallableStatement cs, int columnIndex) throws SQLException {return cs.getString (columnIndex);}}

Step 2: join the database configuration:

Since I am the database implemented by the agent, all you have to do is add it to the code

@ Configurationpublic class DataSourceConfig {@ Bean (name = "sqlSessionFactory") @ Primary public SqlSessionFactory memberDb1SqlSessionFactory (DataSource dataSource) throws Exception {SqlSessionFactoryBean bean = getSqlSessionFactoryBean (dataSource); bean.setTypeHandlers (new TypeHandler [] {new NVarcharTypeHandler ()}); return bean.getObject ();} / * other implementations}

PS: if it is a configuration, just configure it in mybatis-config.xml.

After the configuration is completed, all the functions before the test are normal (# ^. ^ #)

If you find it troublesome, there is another solution: you can replace the NVARCHAR in mapper.xml from VARCHAR.

The above is about the content of this article on "how to use shardingsphere to solve the SQLServer pit". I believe we all have a certain understanding. I hope the content shared by the editor will be helpful to you. If you want to know more about the relevant knowledge, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.