In order to facilitate SEO search, first of all, post the error content
Different versions of Oracle drivers will report different errors
1 <dependency> 2 <groupId>com.oracle</groupId> 3 <artifactId>ojdbc6</artifactId> 4 <version>1.0</version> 5 </dependency>
The error is reported as follows:
Error updating database. Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='name', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for Parameter 1 with jdbctype other. Try setting a different jdbctype for this parameter or a different JDBC typefornull configuration property. Cause: java.sql.sqlexception: invalid column type: 1111
<dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc4</artifactId> <version>1.0</version> </dependency>
The error is reported as follows:
Error updating database. Cause: org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='name', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting null for Parameter 1 with jdbctype other. Try setting a different jdbctype for this parameter or a different JDBC typefornull configuration property. Cause: java.sql.sqlexception: invalid column type
If there is an exception, trace it to mybatis. To avoid verbosity, just use ojdbc6 for debugging, because ojbc6 is more stable with the latest version of mybatis.
As for why it's not stable, take a look at my blog: Error in MyBatis+Oracle: Method oracle/jdbc/driver/OracleResultSetImpl.isClosed()Z is abstract
For source code analysis, it's better to go to Demo first.
mybatis-oracle-config.xml
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 3 "http://mybatis.org/dtd/mybatis-3-config.dtd"> 4 5 <configuration> 6 <properties> 7 <property name="driver" value="oracle.jdbc.driver.OracleDriver"/> 8 <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521/orcl"/> 9 </properties> 10 11 <environments default="dev"> 12 <environment id="dev"> 13 <dataSource type="POOLED"> 14 <property name="driver" value="${driver}"></property> 15 <property name="url" value="${url}"></property> 16 <property name="username" value="gys"></property> 17 <property name="password" value="gys"></property> 18 </dataSource> 19 </environment> 20 21 </environments> 22 <mappers> 23 <mapper resource="mapper/oracle/user.xml"></mapper> 24 </mappers> 25 </configuration>
user.xml
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 3 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 4 <mapper namespace="dao.oracle.IUserMapper"> 5 <insert id="insertUser" parameterType="model.oracle.User"> 6 insert into users 7 (name,age) 8 values 9 (#{name},#{age}) 10 </insert> 11 </mapper>
Main method entry:
1 public static void main(String[] args) throws Exception{ 2 SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder(); 3 SqlSessionFactory sqlSessionFactory=builder.build(Resources.getResourceAsStream("mybatis-oracle-config.xml"),"dev"); 4 SqlSession sqlSession=sqlSessionFactory.openSession(true); 5 IUserMapper userMapper=sqlSession.getMapper(IUserMapper.class); 6 User user=new User(); 7 //Not set here, intentionally inserted null data 8 //user.setName("gggg"); 9 user.setAge(20); 10 int count=userMapper.insertUser(user); 11 System.out.println(count == 1 ? "Insert success" : "Insert failure"); 12 sqlSession.close(); 13 }
The running result is the above error content.
Let's start with the SimpleExecutor.java executor.
If you don't know about the actuator, you can read my blog: BatchExecutor of Executor source code parsing in MyBatis not understood
stmt in this place points to OraclePreparedStatementWrapper.java;
It seems that this class is provided by Oracle driver and inherits the JDBC Statement interface
At the same time, the handler points to the RoutingStatementHandler class
Line 88 is the way to start setting sql parameters. Let's trace it in and see how it works.
Go directly to the PreparedStatementHandler class, because RoutingStatmentHandler inherits from the PreparedStatmentHandler class.
Continue to see the setParameters() source code:
1 @Override 2 public void setParameters(PreparedStatement ps) { 3 //Get the sql All parameter mapping objects in 4 List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); 5 if (parameterMappings != null) { 6 for (int i = 0; i < parameterMappings.size(); i++) { 7 ParameterMapping parameterMapping = parameterMappings.get(i); 8 //If it's not a reference 9 if (parameterMapping.getMode() != ParameterMode.OUT) { 10 Object value; 11 //Get the property name of the parameter, such as name,age 12 String propertyName = parameterMapping.getProperty(); 13 MetaObject metaObject = configuration.newMetaObject(parameterObject); 14 //Get the default value of the parameter, such as name=5,Here value That's 5. 15 value = metaObject.getValue(propertyName); 16 //Get type converter by parameter 17 TypeHandler typeHandler = parameterMapping.getTypeHandler(); 18 //Obtain jdbc Type, here is enumeration; if it is empty, return other Enumerating values, and enumerating code Property value is 1111 19 JdbcType jdbcType = parameterMapping.getJdbcType(); 20 //This line of conditions will not be implemented, because jdbcType stay build When it is empty, the default value is other enumeration 21 if (value == null && jdbcType == null) { 22 jdbcType = configuration.getJdbcTypeForNull(); 23 } 24 //Parameter setting starts to be assigned to the type converter 25 typeHandler.setParameter(ps, i + 1, value, jdbcType); 26 } 27 } 28 } 29 }
The above code removes the interference code, adds comments, and continues to track down
typeHandler points to StringTypeHandler class. There is no seParameter() method in it. Go to the parent BaseTypeHandler class to find it.
setParameter() source code
The following code removes redundant interference
1 @Override 2 public void setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException { 3 //Parameter value is empty 4 if (parameter == null) { 5 //jdbcType It's empty. It can't be empty here. At least it's the default enumeration other 6 if (jdbcType == null) { 7 throw new TypeException("JDBC requires that the JdbcType must be specified for all nullable parameters."); 8 } 9 try { 10 /** 11 i Is the parameter position. The first parameter here is 1 12 jdbcType.TYPE_CODE Is the encoded value of enumeration, where the null value is 1111· 13 **/ 14 ps.setNull(i, jdbcType.TYPE_CODE); 15 } catch (SQLException e) { 16 //Is the exception content here familiar? It's what we see in the console. It seems that the abnormality is above setNull Method is thrown 17 throw new TypeException("Error setting null for parameter #" + i + " with JdbcType " + jdbcType + " . " 18 + "Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. " 19 + "Cause: " + e, e); 20 } 21 }
//If it's not null, go straight here
else{
setNonNullParameter(ps, i, parameter, jdbcType);
}
22 }
I don't understand why I want to set the JDBC type to null yes and the encoding to 1111; does this value have any special meaning? Yes, please let me know
Continue to see setNull() method
Source code of setNull() method
Continue to T4CPreparedStatement to check the source code of setNull()
Continue to track setNullCritical() source code
Continue to trace to getInternalType() source code
1 int getInternalType(int var1) throws SQLException { 2 boolean var2 = false; 3 short var4; 4 switch(var1) { 5 case -104: 6 var4 = 183; 7 break; 8 case -103: 9 var4 = 182; 10 break; 11 case -102: 12 var4 = 231; 13 break; 14 case -101: 15 var4 = 181; 16 break; 17 case -100: 18 case 93: 19 var4 = 180; 20 break; 21 case -16: 22 case -1: 23 var4 = 8; 24 break; 25 case -15: 26 case -9: 27 case 12: 28 var4 = 1; 29 break; 30 case -14: 31 var4 = 998; 32 break; 33 case -13: 34 var4 = 114; 35 break; 36 case -10: 37 var4 = 102; 38 break; 39 case -8: 40 var4 = 104; 41 break; 42 case -7: 43 case -6: 44 case -5: 45 case 2: 46 case 3: 47 case 4: 48 case 5: 49 case 6: 50 case 7: 51 case 8: 52 var4 = 6; 53 break; 54 case -4: 55 var4 = 24; 56 break; 57 case -3: 58 case -2: 59 var4 = 23; 60 break; 61 case 0: 62 var4 = 995; 63 break; 64 case 1: 65 var4 = 96; 66 break; 67 case 70: 68 var4 = 1; 69 break; 70 case 91: 71 case 92: 72 var4 = 12; 73 break; 74 case 100: 75 var4 = 100; 76 break; 77 case 101: 78 var4 = 101; 79 break; 80 case 999: 81 var4 = 999; 82 break; 83 case 2002: 84 case 2003: 85 case 2007: 86 case 2008: 87 case 2009: 88 var4 = 109; 89 break; 90 case 2004: 91 var4 = 113; 92 break; 93 case 2005: 94 case 2011: 95 var4 = 112; 96 break; 97 case 2006: 98 var4 = 111; 99 break; 100 default: 101 SQLException var3 = DatabaseError.createSqlException(this.getConnectionDuringExceptionHandling(), 4, Integer.toString(var1)); 102 var3.fillInStackTrace(); 103 throw var3; 104 } 105 106 return var4; 107 }
Because there is no 1111 match in case, you can only enter default.
An exception class is defined in default, and it is throw n at the end. The assignment of a null value has come to an end.
This place doesn't really understand what it means. The values behind these case s represent what it means. I think only the oracle driven developers can understand it.
The design of this place is very strange;
The exception thrown by Mybatis+ojbc6 for the passed in null value is: "Cause: java.sql.SQLException: invalid column type: 1111"
1111 here is the number given in Mybatis for the ambiguous JDBC type parameter. There is no relationship with oracle driver.
At this point, the source code analysis from mybatis to ojdbc6 driver is finished.
Can java send an unassigned sql statement to oracle?
Mybatis is the encapsulation of JDBC. We kick out mybatis and directly use jdbc+Oracle driver to verify the above point of view.
1 public static void main(String[] args) throws Exception{ 2 String sql="insert into users(name,age) values(?,?)"; 3 Class.forName("oracle.jdbc.driver.OracleDriver"); 4 Connection connection=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521/orcl","gys","gys"); 5 PreparedStatement ps=connection.prepareStatement(sql); 6 ps.setInt(2,30); 7 //The first parameter is intentionally not set here 8 //ps.setString(1,null); 9 ParameterMetaData metaData=ps.getParameterMetaData(); 10 System.out.println(metaData.getParameterCount());//Number of printing parameters 11 int count=ps.executeUpdate(); 12 System.out.println(count == 1 ? "Insert success" : "Insert failure"); 13 connection.close(); 14 }
Execution result:
jdbc also can't insert an unassigned sql statement into oracle, but if you release the line 8 code comment, you can do the right operation again.
There's a question. Why are parameters not assigned to Mybatis+Oracle and JDBC+Oracle, and why are the error reports different?
Because Mybatis has made a judgment on null value, if it is null, it will be directly handed to the setNull() method of the pre compiled object of ojdbc6 for processing;
Exception is an exception thrown in the parameter processing stage, which has not yet reached the database execution stage; JDBC reports an error when the database executes sql; it belongs to sql syntax error.
We can make a change to the above JDBC code, and there will be the same abnormal error as Mybatis
1 public static void main(String[] args) throws Exception{ 2 String sql="insert into users(name,age) values(?,?)"; 3 Class.forName("oracle.jdbc.driver.OracleDriver"); 4 Connection connection=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521/orcl","gys","gys"); 5 PreparedStatement ps=connection.prepareStatement(sql); 6 ps.setInt(2,30); 7 //The first parameter is intentionally not set here 8 //ps.setString(1,null); 9 ps.setNull(1,1111); 10 ParameterMetaData metaData=ps.getParameterMetaData(); 11 System.out.println(metaData.getParameterCount()); 12 int count=ps.executeUpdate(); 13 System.out.println(count == 1 ? "Insert success" : "Insert failure"); 14 connection.close(); 15 }
Operation result:
There is no such keyword as "Cause: org.apache.ibatis.type.TypeException..." because the exception thrown by ojdbc6 is caught by mybatis, and mybatis adds some content of its own.
When mybatis is in the oracle database, it will report an error when it encounters an unassigned null value, but the MySql database will not report an error,
Simply do a source code analysis for null value processing in mysql
The part of empty value processing in mybatis is the same. The difference is that the way of processing empty value is different between mysql driver and oracle driver.
This precompiled object points to the mysql driven ClientPreparedStatement class.
The following code is msyql's handling of null values; it will enter the analysis of mysql driver source code.
setNull() source code
See the red box annotation in the screenshot: MySQL ignores sqlType. Therefore, in mybatis, the sqlType is assigned to 1111, which has no effect on mysq in resolving null values.
getCoreParameterIndex() source code
1 protected final int getCoreParameterIndex(int paramIndex) throws SQLException { 2 int parameterIndexOffset = getParameterIndexOffset(); 3 checkBounds(paramIndex, parameterIndexOffset);//This is to verify the parameters and pass the values. There is no concern about the modification of these two values, so I won't go into it 4 return paramIndex - 1 + parameterIndexOffset;//1-1+0 5 }
getParameterIndexOffset() source code
1 //Is to return 0,This is the designation. mysql Index start position of parameter resolution 2 protected int getParameterIndexOffset() { 3 return 0; 4 }
So the 1650th row of the above screenshots is called the following.
((PreparedQuery<?>) this.query).getQueryBindings().setNull(0); // MySQL ignores sqlType
Here 0 is the index position of the parameter in mysql.
From the call mode of setNull(), it can be concluded that getQueryBindings() returns a parameter object, which contains various information of the parameter and is provided to mysql database for parameter parsing;
Only mysql database can know the meaning of each field in this object (the mysql driver is also provided by mysql database)
Well, let's continue to analyze the setNull() method above.
bindValues is an array that stores parameter objects;
582 lines of code call the setNull () method of the first parameter object; set whether it is null.
As for setValue(), let's go on.
setValue() source code
1 public synchronized final void setValue(int paramIndex, String val, MysqlType type) { 2 //Convert parameter values to byte arrays 3 byte[] parameterAsBytes = StringUtils.getBytes(val, this.charEncoding); 4 setValue(paramIndex, parameterAsBytes, type); 5 }
There is also a setValue() method here
public synchronized final void setValue(int paramIndex, byte[] val, MysqlType type) { //Parameter object set byte array,In fact, the parameter value is passed to the database in the form of byte array, which is not 1 as we think.2 Or Zhang San, Li Si this.bindValues[paramIndex].setByteValue(val); //Set parameters in mysql Data types in the database, for example: varchar,int... this.bindValues[paramIndex].setMysqlType(type); }
At this point, the source code analysis from mybatis to mysql driver is finally over.
I'm curious about what the precompiler object provided by mysql looks like before performing database operations.
Directly find the PreparedStatementHandler class of myBatis source code
This ps is the precompiler object we want to see. There are too many object fields to be divided into multiple screenshots.
bindValues are the values we just saw in the source code analysis
The content shown in the figure above is completely consistent with the source code we analyzed.
It can be seen from the figure that these two parameters are stored in the precompiler in the form of two objects and passed to mysql database for analysis.
mysql can execute normally, but Oracle throws an exception;
These two different performances cause trouble for programmers, so who should be the one to throw the exception?
It's mybatis, of course. oracle and mysql provide their own implementation methods according to the jdbc interface,
As a framework that encapsulates JDBC, mybatis is not encapsulated in place, so the same method is compatible with different databases.
(ps: the free framework is used every day, and a lot of money is collected every month. I also complain about mybatis like this. I think I'm too shameful.)