Java MyBatis insertion into the database returns the primary key

Recently, due to business requirements, I needed to insert a piece of product information and return the product Id in an e-commerce system. I just met some pitfalls at the beginning. Please make notes here in case I forget in the future.

Get the primary key after insertion in code like this 1

User user = new User(); 
user.setUserName("chenzhou"); 
user.setPassword("xxxx"); 
user.setComment(" Test the insert data return primary key function "); 
 
System.out.println(" The primary key before insertion is: "+user.getUserId()); 
userDao.insertAndGetId(user);// The insert  
System.out.println(" The primary key after insertion is: "+user.getUserId()); 

After the search of online information, found that there are generally two ways.

Method 1:

In the entity class mapping file "* Mapper.xml ", write:

<insert id="insertAndGetId" useGeneratedKeys="true" keyProperty="userId" parameterType="com.chenzhou.mybatis.User">
  insert into user(userName,password,comment)
  values(#{userName},#{password},#{comment})
</insert>

Tips:

useGeneratedKeys="true" means set the primary key to self-grow

keyProperty="userId" means assigning Id since growth to the userId field in the entity class.

parameterType = "com chenzhou. mybatis. User" this attribute points to pass the parameters of the entity class

And just to remind you, < insert > < /insert > There is no resultType attribute in ".

The entity class uerId has getter() and setter(); methods

Since I had set the field self-growth when I built the table in MySQL database, I finally chose the second method.

The second way:

Also in the entity class mapping file "* Mapper.xml "but write:

  <!--  insert 1 A commodity  -->
  <insert id="insertProduct" parameterType="domain.model.ProductBean" >
    <selectKey resultType="java.lang.Long" order="AFTER" keyProperty="productId">
     SELECT LAST_INSERT_ID()
   </selectKey>
    INSERT INTO t_product(productName,productDesrcible,merchantId)values(#{productName},#{productDesrcible},#{merchantId});
  </insert>

Tips:

< insert > < /insert > There is no resultType property in, but < selectKey > < /selectKey > There are labels.

order="AFTER" means that the insert statement is executed first and the query statement is executed later.

Can be set to BEFORE or AFTER.

If set to BEFORE, it will first select the primary key, set keyProperty, and then execute the insert statement.

If set to AFTER, the insert statement is executed first, followed by the selectKey element - this is similar to the Oracle database, where sequence calls can be embedded in the insert statement
keyProperty="userId" means assigning Id since growth to the userId field in the entity class.

SELECT LAST_INSERT_ID() means that the record just inserted in the MySQL syntax is queried as Id.

The entity class uerId has getter() and setter(); methods

Implement the requirements, and that's enough.

If you're interested here, please continue to listen to me talk about a possible stray pit in Mybatis.

Why is it that when the add method in Mybatis is modified to return a value, you are prompted to insert the database successfully and the inserted data can be read, but you cannot see the inserted data when you open the database?

Do not write this if you want to return the primary key after insertion while implementing the above requirements.

  @Override
  public Long insertProduct(ProductBean productBean) {
    // TODO Auto-generated method stub
    SqlSession session = MybatisJDBCUtil.currentSession();

    ProductIDao productIDao = session.getMapper(ProductIDao.class);//  Here, *.class
                                    //  Must correspond to DAO The interface layer 
    return productIDao.insertProduct(productBean);
    
  }

Why?

Because if you write like this, you will not return the primary key Id you want, but the number of rows affected after executing the database statement.

Also, when you execute it you will notice that the insert was successful and you can read the inserted data with the code, but there is always only one record.

Also, if you open the database, you will find that no data was successfully inserted into the database.

I have been depressed here for a long time, and finally found the key.

The difference between a return value and no return value is:

Only access to the database in read-only mode is returned, and no changes are made to the database data, such as various queries.

Those with no return value will access the database in read-write mode and modify the data in the database, such as deleting and adding.

In addition, according to my understanding, mybatis will be cached in a set similar to session when executing insert statements, and then the underlying driver will be called to modify the database.

session.commit();    
MybatisJDBCUtil.closeSession();

There is no return value for the above two statements, that is, the execution of the two statements will actually be inserted into the database to modify the database data.

On the contrary, those with return values did not execute these two statements, so they just executed the add statement in session constructed by themselves, but did not commit to the database, so there is no record in the database.

This explains why when the add method in Mybatis is modified to return a value, it is prompted to insert the database successfully, but you cannot see the inserted data when you open the database.

The insert statement method in Mybatis does not have a return value; it is correct to write it like this.

  @Override
  public void insertProduct(ProductBean productBean) {
    // TODO Auto-generated method stub
    SqlSession session = MybatisJDBCUtil.currentSession();

    ProductIDao productIDao = session.getMapper(ProductIDao.class);//  Here, *.class
                                   //  Must correspond to DAO The interface layer 
    productIDao.insertProduct(productBean);
    
    session.commit(); 
    
    MybatisJDBCUtil.closeSession();  
  }