Annotation configuration for MyBatis

Single table addition, deletion, modification and query based on annotation

1. Since annotation is needed, delete the previous xml based interface and create a new interface under mapper

package com.mybatis.mapper; import java.util.List; import org.apache.ibatis.annotations.Delete; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.Update; import com.mybatis.pojo.UserInfo; public interface UserInfoMapper { //Query users according to user number @Select("select * from user_info where id=#") public UserInfo findUserInfoById(int id); //Fuzzy query user based on user name @Select("select * from user_info where userName like CONCAT(CONCAT('%',#),'%')") public List<UserInfo> findUserInfoByUserName(String userName); //Add user @Insert("insert into user_info(userName,password) values (#,#)") public int addUserInfo(UserInfo ui); //Modify user @Update("update user_info set userName=#,password=# where id=#") public int updateUserInfo(UserInfo ui); //delete user @Delete("delete from user_info where id=#") public int deleteUserInfo(int id); }

Reference interface configuration in mybatis-config.xml file

<!-- Reference interface file --> <mappers> <mapper/> </mappers>

Modify the method of test class, all of which are implemented by interfaces, just to give two examples

The running result is the same as before. There's nothing to say. It's a lot simpler and saves the process of configuring xml (in fact, it doesn't feel much simpler, but I need to learn another chapter and remember more things...)

Annotation based one-to-one association mapping

Take idcard and person data tables as examples to realize one-to-one association mapping between the two tables based on annotation configuration:
1. Write the IdcardMapper interface and PersonMapper interface

package com.mybatis.mapper; import org.apache.ibatis.annotations.Select; import com.mybatis.pojo.Idcard; public interface IdcardMapper { //Query id card information according to id @Select("select * from idcard where id=#") public Idcard findIdcardById(int id); }

package com.mybatis.mapper; import org.apache.ibatis.annotations.One; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import com.mybatis.pojo.Person; public interface PersonMapper { //Query personal information according to personal id number @Select("select * from person where id = #") //But person also has an associated attribute, Idcard, so we need to manually complete the mapping here @Results({@Result(column = "cid",property = "idcard",one = @One(select = "com.mybatis.mapper.IdcardMapper.findIdcardById"))}) public Person findPersonById(int id); }

Note here: the basic attributes in the person object can be mapped automatically, while the associated object attribute idcard needs to be mapped manually. Here, an @ Result annotation is used in the @ Results annotation to map the associated Results. In the @ Result annotation, the property attribute is used to specify the association attribute, and the One attribute is used to specify the association relation of the data table. The @ One annotation indicates that the data table idcard and person are One-to-One association relations.
In the @ One annotation, the select attribute is used to specify that the value of the associated attribute idcard is obtained through the findIdcardById method defined in the IdcardMapper interface
@The column property of the Result annotation is used to specify the parameter name of the passed findIdardById(int id) method. Here is cid, which represents the value of the cid field queried from the data table person

2. Add reference to interface file

<!-- Reference interface file --> <mappers> <mapper/> <mapper/> <mapper/> </mappers>

3. Write test methods

//Test one-to-one association mapping @Test public void testOne2One() { PersonMapper pm = sqlSession.getMapper(PersonMapper.class); Person person = pm.findPersonById(1); System.out.println(person.toString()); }

That's how it works

Annotation based one to many association mapping

0. Entity classes are as follows


1. Create interface ProductInfoMapper interface and TypeMapper interface

package com.mybatis.mapper; import java.util.List; import org.apache.ibatis.annotations.One; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import com.mybatis.pojo.ProductInfo; public interface ProductInfoMapper { //Query all products according to product type number @Select("select * from product_info where tid=#") List<ProductInfo> findProductInfoByTid(int tid); //Query product information according to product number @Select("select * from product_info where id = #") @Results({@Result(column = "tid",property = "type",one = @One(select = "com.mybatis.mapper.TypeMapper.findTypeById"))}) ProductInfo findProductInfoById(int id); }

package com.mybatis.mapper; import org.apache.ibatis.annotations.Many; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import com.mybatis.pojo.Type; public interface TypeMapper { //Query commodity type information according to commodity type number @Select("select * from type where id=#") @Results({@Result(id = true,column = "id",property = "id"), @Result(column = "name",property = "name"), @Result(column = "id",property = "pis",many = @Many(select = "com.mybatis.mapper.ProductInfoMapper.findProductInfoByTid"))}) Type findTypeById(int id); }

2. Reference interface documents

3. Write test method

Test testOne2Many results partial interception

DEBUG [main] - ==> Preparing: select * from type where id=? DEBUG [main] - ==> Parameters: 1(Integer) DEBUG [main] - ====> Preparing: select * from product_info where tid=? DEBUG [main] - ====> Parameters: 1(Integer) DEBUG [main] - <==== Total: 6 DEBUG [main] - <== Total: 1 Type [id=1, name=Computer, pis=[ProductInfo [id=1, code=1378538, name=AppleMJVE2CH/A], ProductInfo [id=2, code=1309456, name=ThinkPadE450C(20EH0001CD)], ProductInfo [id=3, code=1999938, name=Lenovo300Classic version], ProductInfo [id=4, code=1466274, name=ASUS FX50JX], ProductInfo [id=5, code=1981672, name=ASUS FL5800], ProductInfo [id=6, code=1904696, name=association G50-70M]]] DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@352c1b98]

Partial interception of test testMany2One results

DEBUG [main] - ==> Preparing: select * from product_info where id = ? DEBUG [main] - ==> Parameters: 1(Integer) DEBUG [main] - ====> Preparing: select * from type where id=? DEBUG [main] - ====> Parameters: 1(Integer) DEBUG [main] - ======> Preparing: select * from product_info where tid=? DEBUG [main] - ======> Parameters: 1(Integer) DEBUG [main] - <====== Total: 6 DEBUG [main] - <==== Total: 1 DEBUG [main] - <== Total: 1 ProductInfo [id=1, code=1378538, name=AppleMJVE2CH/A] Type [id=1, name=Computer, pis=[ProductInfo [id=1, code=1378538, name=AppleMJVE2CH/A], ProductInfo [id=2, code=1309456, name=ThinkPadE450C(20EH0001CD)], ProductInfo [id=3, code=1999938, name=Lenovo300Classic version], ProductInfo [id=4, code=1466274, name=ASUS FX50JX], ProductInfo [id=5, code=1981672, name=ASUS FL5800], ProductInfo [id=6, code=1904696, name=association G50-70M]]] DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@352c1b98]

Annotation based many to many association mapping

It's like one to many
Two interfaces:

package com.mybatis.mapper; import java.util.List; import org.apache.ibatis.annotations.Select; import com.mybatis.pojo.Functions; public interface FunctionMapper { //Get the function permission list according to the administrator id @Select("select * from functions where id in (select fid from powers where aid = #)") List<Functions> findFunctionsByAid(int aid); }

package com.mybatis.mapper; import org.apache.ibatis.annotations.Many; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import com.mybatis.pojo.AdminInfo; public interface AdminInfoMapper { //Get administrator information according to administrator id @Select("select * from admin_info where id = #") @Results({@Result(id = true,column = "id",property = "id"), @Result(column = "name" ,property = "name"), @Result(column = "id",property = "fs",many = @Many(select = "com.mybatis.mapper.FunctionMapper.findFunctionsByAid"))}) public AdminInfo findAdminInfoById(int id); }

Configuration interface

test method

//Test many to many association mapping @Test public void testM2M() { AdminInfoMapper aim = sqlSession.getMapper(AdminInfoMapper.class); AdminInfo adminInfo = aim.findAdminInfoById(1); System.out.println(adminInfo.toString()); }

Operation result

DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@258d79be] DEBUG [main] - ==> Preparing: select * from admin_info where id = ? DEBUG [main] - ==> Parameters: 1(Integer) DEBUG [main] - ====> Preparing: select * from functions where id in (select fid from powers where aid = ?) DEBUG [main] - ====> Parameters: 1(Integer) DEBUG [main] - <==== Total: 10 DEBUG [main] - <== Total: 1 AdminInfo [id=1, name=admin, fs=[Functions [id=1, name=E-mall management background], Functions [id=2, name=Commodity management], Functions [id=3, name=List of commodities], Functions [id=4, name=List of commodity types], Functions [id=5, name=Order management], Functions [id=6, name=Enquiry order], Functions [id=7, name=Create order], Functions [id=8, name=user management], Functions [id=9, name=User list], Functions [id=11, name=Exit system]]] DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@258d79be]

Annotation based dynamic SQL

The corresponding operation result effect is shown in the previous page. This is realized in a different way. The purpose and effect are the same. If you can save it here, you can save it. Go to the chapter of dynamic sql.

Data table refers to the notes in the chapter of dynamic SQL. It uses the same database and data table.
1. Modify the UserInfoMapper interface

package com.mybatis.mapper; import java.util.List; import java.util.Map; import org.apache.ibatis.annotations.SelectProvider; import com.mybatis.pojo.UserInfo; public interface UserInfoMapper { @SelectProvider(type = UserInfoDynaSqlProvider.class,method = "selectWithParam") List<UserInfo> findUserInfoByCond(Map<String, Object> param); }

2. Write the UserInfoDynaSqlProvider class in the unified package, add methods and speak statements

package com.mybatis.mapper; import java.util.Map; import org.apache.ibatis.jdbc.SQL; public class UserInfoDynaSqlProvider { public String selectWithParam(Map<String, Object> param) { return new SQL() { { SELECT("*"); FROM("user_info"); if (param.get("id")!=null) { WHERE("id = #"); } if (param.get("userName") !=null) { WHERE("userName=#"); } if (param.get("password") !=null) { WHERE("password=#"); } } }.toString(); } }

This format is quite complicated. I checked it several times and finally got it done. I think it's more troublesome than using dynamic sql directly
3. Add reference to interface file

<mappers> <mapper /> </mappers>

4. Add test method

@Test public void testFindUserInfoByCond() { UserInfoMapper uim = sqlSession.getMapper(UserInfoMapper.class); Map<String, Object> param = new HashMap<String, Object>(); param.put("userName", "tom"); param.put("password", "123456"); List<UserInfo> list = uim.findUserInfoByCond(param); for (UserInfo userInfo : list) { System.out.println(userInfo.toString()); } }

5. Operation results:

DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7e19ebf0] DEBUG [main] - ==> Preparing: SELECT * FROM user_info WHERE (userName=? AND password=?) DEBUG [main] - ==> Parameters: tom(String), 123456(String) DEBUG [main] - <== Total: 1 UserInfo [id=1, userName=tom, password=123456]

1. Add new methods and comments to the interface UserInfoMapper

@InsertProvider(type = UserInfoDynaSqlProvider.class,method = "insertUserInfo") @Options(useGeneratedKeys = true,keyProperty = "id") int insertUserInfo(UserInfo ui);

Where @ Options(useGeneratedKeys = true,keyProperty = "id")
In this way, when inserting data into user info, the self increment of the primary key id will be automatically assigned to the attribute id of the object ui
Add a new method in UserInfoDynaSqlProvider class

public String insertUserInfo(UserInfo ui) { return new SQL() { { INSERT_INTO("user_info"); if (ui.getUserName() != null) { VALUES("userName","#"); } if (ui.getPassword() != null) { VALUES("password","#"); } } }.toString(); }

Add test class

@Test public void testInsertUserInfo() { UserInfoMapper uim = sqlSession.getMapper(UserInfoMapper.class); UserInfo ui = new UserInfo(); ui.setUserName("tt"); ui.setPassword("xx"); uim.insertUserInfo(ui); System.out.println(ui.getId()); }

Operation result

If @ Options(useGeneratedKeys = true,keyProperty = "id") is not added, the database is inserted successfully, but the id value is not obtained here, and 0

@UpdateProvider(type = UserInfoDynaSqlProvider.class,method = "updateUserInfo") int updateUserInfo(UserInfo ui);

public String updateUserInfo(UserInfo ui) { return new SQL() { { UPDATE("user_info"); if (ui.getUserName() != null) { SET("userName = #"); } if (ui.getPassword() != null) { SET("password = #"); } WHERE("id=#"); } }.toString(); }

@Test public void testUpdateUserInfo() { UserInfoMapper uim = sqlSession.getMapper(UserInfoMapper.class); Map<String, Object> param = new HashMap<String, Object>(); param.put("id", 1); UserInfo uInfo = uim.findUserInfoByCond(param).get(0);//0 is the first element taken out uInfo.setPassword("55555"); uim.updateUserInfo(uInfo); }

@DeleteProvider(type = UserInfoDynaSqlProvider.class,method = "deleteUserInfo") void deleteUserInfo(Map<String, Object> param);

public String deleteUserInfo(Map<String, Object> param) { return new SQL() { { DELETE_FROM("user_info"); if (param.get("id")!=null) { WHERE("id = #"); } if (param.get("userName")!=null) { WHERE("userName = #"); } if (param.get("password")!=null) { WHERE("password = #"); } } }.toString(); }

@Test public void testDeleteUserInfo() { UserInfoMapper uim = sqlSession.getMapper(UserInfoMapper.class); Map<String, Object> param = new HashMap<String, Object>(); param.put("userName", "tt"); param.put("password", "xx"); uim.deleteUserInfo(param); }

It was deleted.
Actually, it's convenient to use it

• Give the thumbs-up
• Collection
• share
• • Article report

8 February 2020, 05:30 | Views: 7759