Introduction: In MyBatis, when you want to insert data in Oracle, return the primary key value, not the number of inserted bars ...
Environment: MyBatis 3.2, Oracle, Spring 3.2
SQL Snippet in XML Configuration:
<insert id= "insertselective" parametertype= "Com.jxxx.p2pp.model.UUserInfo" > <selectkey resulttype= " Java.math.BigDecimal "order=" before "keyproperty=" id "> select U_user_info_seq. Nextval as ID from DUAL </selectKey> Insert to U_user_info <trim prefix= "(" suffix= ")" Suffixoverrides= "," > <if test= "id! = NULL" > ID, </if> <if test= "UserName! = NULL" & gt; User_name, </if> <if test= "Realname! = null" > real_name, </if> ..... </insert>
The point is that Selectkey is used here to define the return of the newly generated PrimaryKey, which applies only to Oracle.
It is important to note that the integer type is used in Java code, but in the MyBatis mapping file, the Java.math.BigDecimal type is used, otherwise the type conversion or mismatch error is reported.
Other scenarios, such as MySQL or SQL Server, apply to the following scenarios:
<insert id= "Insert" parametertype= "spares" usegeneratedkeys= "true" keyproperty= "id" > INSERT INTO Spares (spares_id,spares_name, spares_type_id,spares_spec) values (#{id},#{name},#{typeid},#{spec}) </insert>
The return of the newly generated primary key is accomplished by using Usegeneratedkeys/keyproperty to implement the insertion of the data.
Where exception information is resolved:
Exception information:
Org.springframework.jdbc.UncategorizedSQLException:Error getting generated key or setting result to Parameter object. Cause:java.sql.SQLException: Invalid column type: Getbigdecimal not implemented for class Oracle.jdbc.driver.T4CRowidAccessor
; Uncategorized SQLException for SQL []; SQL state [99999]; Error code [17004]; Invalid column type: Getbigdecimal not implemented for class Oracle.jdbc.driver.T4CRowidAccessor; Nested exception is java.sql.SQLException:
Invalid column type: Getbigdecimal not implemented for class Oracle.jdbc.driver.T4CRowidAccessor
Problem solving:
The problem is to set the returned primary key data type in Java code, where the data type returned is Java.lang.Integer, not bigdecimal and long. However, the type in the mapping file in MyBatis is Java.math.BigDecimal.