Recently, due to business needs in an e-commerce system, I need to return to the product ID after inserting a product information. At the beginning, I encountered some pitfalls, so I took notes here to prevent it from being forgotten in the future.
Get the inserted primary key like the following code
User user = new User(); user.setUserName("chenzhou"); user.setPassword("xxxx"); user.setComment("Test the function of inserting data to return primary key"); System.out.println("The primary key before insertion is: "+user.getUserId()); userDao.insertAndGetId(user);//Insert operation System.out.println("The primary key after insertion is: "+user.getUserId());After querying online information, I found that there are roughly two ways.
Method 1:
In the entity class's mapping file "*Mapper.xml" is written like this:
<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 to set self-growth for the primary key
keyProperty="userId" means assigning the growing Id to the userId field in the entity class.
parameterType="com.chenzhou.mybatis.User" This property points to the passed parameter entity class
Here is a reminder that there is no resultType attribute in <insert></insert>, so don't add it randomly.
The uerId in the entity class must have the getter() and setter(); method
Since I had already set up the field self-growth when building tables in MySQL database, I finally chose the second method.
The second method:
Also in the mapping file "*Mapper.xml" of the entity class, but it should be written like this:
<!-- Insert a product --> <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:
There is no resultType attribute in <insert></insert>, but there is a <selectKey></selectKey> tag.
order="AFTER" means that the insert statement is executed first, and then the query statement is executed.
Can be set to BEFORE or AFTER.
If set to BEFORE, it will first select the primary key, set the keyProperty and then execute the insert statement.
If set to AFTER, then execute the insert statement first, and then the selectKey element - similar to Oracle database, you can embed sequence calls in the insert statement, like in Oracle database.
keyProperty="userId" means assigning the growing Id to the userId field in the entity class.
SELECT LAST_INSERT_ID() means that the self-growth Id of the record that has just been inserted is queryed in MySQL syntax.
The uerId in the entity class must have the getter() and setter(); method
To achieve the requirements, the above are enough.
If you are interested here, please continue to listen to me about a possible mistake in Mybatis.
Why does the modification of the addition method in Mybatis to have a return value, although it prompts that the insertion database is successful and the inserted data can be read, but when you open the database, you can't see the inserted data?
If you want to insert and return the primary key when implementing the above requirements, remember not to write this way.
@Override public Long insertProduct(ProductBean productBean) { // TODO Auto-generated method stub SqlSession session = MybatisJDBCUtil.currentSession(); ProductIDao productIDao = session.getMapper(ProductIDao.class); // Here *.class // It must correspond to the interface layer of DAO return productIDao.insertProduct(productBean); }Why?
Because if you write like above, the return of the primary key Id you want after execution is not the number of rows affected after executing the database statement.
Moreover, after you execute, you will find that the prompt is successful in insertion, and you can also read the inserted data with the code, but there is always only one record.
Moreover, when you open the database, you will find that no data was inserted successfully in the database.
I was depressed here for a long time and finally discovered the key point.
The difference between having a return value and not having a return value is:
The return value is only to access the database read-only mode, and there will be no modification to the database data, such as various methods of querying.
If there is no return value, the database will be accessed in read and write mode, and the data in the database will be modified, such as deletion or addition.
In addition, according to personal understanding, mybatis should first cache it to a constructed session-like collection when executing the insert statement, and then call the underlying driver to operate and modify the database.
session.commit(); MybatisJDBCUtil.closeSession();
The above two statements are not returned, which means that only after these two statements are executed, they will be truly executed and inserted into the database and modify the data in the database.
On the contrary, if there is a return value, these two statements are not executed, so the addition statement is only executed in the session constructed by itself, but it is not submitted to the database, so there is no record in the database.
This explains why after modifying the addition method in Mybatis to have a return value, although it prompts that the database is inserted successfully, you cannot see the inserted data when you open the database.
The insertion statement method in Mybatis should not have a return value, as writing like this is correct.
@Override public void insertProduct(ProductBean productBean) { // TODO Auto-generated method stub SqlSession session = MybatisJDBCUtil.currentSession(); ProductIDao productIDao = session.getMapper(ProductIDao.class);// Here *.class // It must correspond to the interface layer of DAO productIDao.insertProduct(productBean); session.commit(); MybatisJDBCUtil.closeSession(); }The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.